Oracle) Structured Query Language(SQL)

What is Structured Query Language (SQL)?
As a DB standard language (specified by ISO) with its syntax, most databases use SQL to query, enter, modify, and delete data.

 

Types of SQL

• Data Definition Word (DDL): A database administrator or application programmer is stored in a data dictionary as the language for defining the logical structure of the database.
• Data manipulation words (DML): As the language used to manipulate data stored in the database, perform data retrieval (Retrieval), addition (Insert), deletion, and update operations.
• Data Control Language (DCL): Language used to manage transactions on database systems, such as granting access to data

SQL Statements

Type	Statement	Use
DQL(Data Query Language)	SELECT	To search the data
DML(Data Manipulation Language)	INSERT UPDATE DELETE	To change the data
DDL(Data Definition Language)	CREATE ALTER DROP RENAME TRUNCATE	To create and change objects
TCL(Transaction Control Language)	COMMIT ROLLBACK SAVEPOINT	To process transaction
DCL(Data Control Language)	GRANT REVOKE	To control the data

*CRUD

In computer programming, create, read, update, and delete(CRUD) are the four basic operations of persistent storage. They correspond to 'insert', 'select', 'update', and 'delete' in SQL so it is vital to remember the statements.

 

<DDL>

o Create a table

create table table-name (
				column name data type,
				Column name data type, ……….);

create table member01(
				id varchar2(20),
				name varchar2(20),
				address varchar2(50),
				phone varchar2(20));

o Table list output

select * from tab;

o Table structure output

describe member01;

o Rename the table

alter table old table name rename to new table name;
alter table member01 rename to member02;

The table name can also be changed.

 rename member01 to member02;

o Add a new column to the table (add operator)

alter table member01 add (password varchar2(30));

o Modify the column of the table (modify operator)

 alter table member01 modify (password varchar2(50) not null);

Other data types if the column already has data cannot be changed too.

o Delete columns in the table (drop operator)

alter table member01 drop column column_name;
alter table member01 drop column password;

o Delete Constraints

 alter table member01 drop primary key;

o Deleting a table

drop table member01;

How do I delete this temporary table parmanently?

purge recyclebin;

How do I delete a table completely from scratch?

 drop table member01 purge;

 

<DML>

 

1. INSERT(Data Input)
Format: insert into tablename (column1, column2,...) values (data1, data2,...);
insert into table name values (data1, data2,...);

insert into dept01(deptno, dname, loc)
			values(10,'ACCOUNTING', 'NEW_YORK');
            
insert into dept01(dname, loc, deptno)
			values('RESEARCH', 'DALLAS', 20);
            
insert into dept01 values(30, 'SALES', 'CHICAGO');
insert into dept01 values(40, 'OPERATIONS','BOSTON');

Following is an example of creating a row by using 'insert'. It has to be '' not "" when we insert the values. SQL is not sensitive with Uppercase/Lowercase, but it has to be distinguished in terms of values. As you see below, here are 2 ways to insert the values. Depending on the situation, one can be chosen.

2. UPDATE (Data Modification)
Format: update table name set column 1 = Value to modify 1,
Column 2 = Value to modify 2,...
where conditional;

If you don't use 'where', the address of the whole table will be changed, so be careful!

After being updated, the address value of table 'test2' is changed.

3. DELETE (Data Deletion)
format: delete from table name where conditional;

To delete it is relatively easy. After deleting from the table, you can see no rows selected.