SQL STATEMENTS

_____________________________________________________________________
Other Important Topics in SQL

DBMS Basics
Operator and Functions
Indexing
Constraints
Views
Table Partitioning
________________________________________________________________________

Data Retrieval/Query Language (DRL/DQL):

1. SELECT: Select statement is used to retrieve data from the database.

SYNTAX: SQL> Select * from <object_name>;

EXAMPLE:

SQL> select * from tab; // ‘*’ projection operation (indicates all columns of the table)

SQL> select *  from emp;

Data Definition Language (DDL):

1. CREATE: Create statement is used to create objects in the user schema, like table, view etc.

SQL>Create table <table_name> (col1 datatype(width),col2 datatype(width),...............);

SQL>create table student(stuid number(4),stuname varchar2(10));

SQL>create table emp1 as select * from emp; // creating from existing table;

2. ALTER: alter used to modify the existing objects.

a.    ADD: use to add a new column into the existing table.

SQL>alter table emp add (empsal number(10,2));

b.    MODIFY: to modify the existing column datatype width or change the data type of the column(if the table is empty we can change data type of the column)

SQL> alter table emp modify (empsal number(8,2));

SQL> alter table emp modify (empno varchar2(10));

c.    RENAME: to rename the column name of the existing table.

SQL>create table emp rename column empno to empid;

d.    DROP: to drop or delete the column from the existing table

SQL>alter table emp drop column empsal;

3. RENAME: to name the object name(or table name);

SQL>rename emp to employee; // rename <old_name> to <new_name>;

4. DROP: used to drop the object;

SQL> drop table employee;

5. TRUNCATE: to delete the entire rows data of the table permanently.

SQL> truncate table employee;

Data Manipulation Language (DML):

1. INSERT: to insert data into the table;

SQL> insert into emp values(1,’adam’,1000); // syn1 type

SQL> insert into emp (empno,empname,empsal) values(2,’eve’,2000); //syn2 type

SQL> insert into emp values(&empno,’&empname’,&empsal); // to insert into multiple rows

Enter value for empno:3

Enter value for empname:Abraham

Enter value for empsal: 1000

SQL> /       // to enter multiple row values type ‘/’ or ‘run’ or ‘r’

SQL> insert into emp select * from emp;                   // to insert values from old table, if they have same table structure

2. DELETE: to delete the contents(single row or multiple rows) of the table;

SQL> delete from emp; // to delete all the rows from the table

SQL> delete from emp where empno=2; // to delete the specified row

3. UPDATE: to update the data into the table.

SQL> update emp set empsal=5000 where empno=3;            

SQL> update emp set empsal=5000; // entire column for empsal will be 5000, for table emp;

Data Control Language:

1. GRANT: to grant permissions to the user, to create objects and manipulate objects data.

SQL> create user user1 identified by user1; // to create new user

SQL> grant resource,connect to user1; // to grant permissions to user objects;

2. REVOKE: to revoke permissions to user, to create objects and manipulate objects data.

SQL> revoke resource,connect from user1;

Transaction Control Language:

1. SAVEPOINT: to make bookmarks for the flow of transactions.

SQL> savepoint <savepoint_name>;

2. ROLLBACK: to rollback the transactions, bookmark points created by savepoints.

SQL> Rollback to <savepoint_name>;

3. COMMIT: to save the transactions permanently.

SQL> Commit;

Data dictionary:

SQL> Select * from user_objects; // displays user created objects like tables, views etc. 

SQl> desc <table_name> //displays table columns and data types related to respective columns