SQL

• It is a language used to access data within oracle database. 
• Developed by IBM in mid 1970’s. 
• Oracle corporation introduced the first commercially available  implementation of SQL. 
• It is pronounced as ‘sequel’ and SQL stands for Structure Query Language. 
• It is English like language and also have nested environment to ease queering , the classification of SQL commands are 

DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
TCL (Transaction Control Language)

Data Type :Type of a data stored in a column.

1.char(size) : Fixed-length character data,size character long,Maximum size is 2000.

2.varchar2(size) : Variable length character  string have maximum size of 4000 bytes.

3. Number(size) : stores fixed and floating point numbers.

4. Date : stores date and time.

5. Long : can store up to 2GB of characters.

6. Raw : store graphics,sounds,etc..

7. Longraw : contains raw binary data.

useful commands :

1) cl scr: Clear the screen 

2) to_char : to_char function is used to convert oracle date format (dd-mon-yy) into user required date format

Syntax :
select to_char(<filedname>,’dd-mm-yyyy hh:mm:ss’) from <tablename> ;

Example :
select to_char(edoj,’dd-mm-yyyy hh:mm:ss’)date_time from emp;

3) linesize : default size is 80 we can change by using this linesize function 

sql>set linesize 120;

4) dual : It is a table that is automatically created by Oracle Database along with the data dictionary. 

DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. 

It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X.

sql>desc dual;

sql>select * from dual;

sql>select sysdate from dual;

Classification of SQL Commands :

1. Data Definition Language (DDL)
DROP
RENAME
CREATE
ALTER
TRUNCATE

2. Data Manipulation Language (DML)
SELECT
INSERT
UPDATE
DELETE

3. Data Control Language (DCL)
GRANT
REVOKE
SET ROLE

4. Transaction Control Language (TCL)
COMMIT
ROLL BACK
SAVE POINT

1.Data Definition Language(DDL):

DROP,RENAME,CREATE,ALTER,TRUNCATE are called DDL(data definition language)commands. They are called data definition since they are used for defining the data that is the structure of the data is known through DDL commands.

Drop:
DROP command completely removes a table from the database. This command will also destroy the table structure and the data stored in it. 

Syntax:

DROP TABLE <TABLE NAME>; 

Example: DROP TABLE PRODUCT; 

Rename:

RENAME command is used to set a new name for any existing table. 

Syntax: 

RENAME <OLD NAME>TO <NEW NAME>;

Example: RENAME PRODUCT TO PROD;

Create: To create objects(tables) in the database

Syntax: 

SQL>Create table <table name> (<column name><data type>, . . .);

Example:

SQL>create table student(stdno number(5),

                               stdname varchar2(10),
                               stdadd varchar2(10),
                               phno number(10));

SQL>desc student ;

Name                      Null?                       Type
stdno                                                    number(5)
stdname                                              varchar2(10)
stdadd                                                 varchar2(10)
phno                                                     number(10)

How To Create A New User:

1.C:\Users\Admin>sqlplus / as sysdba

2.SQL> show user;

    USER is "SYS"

3.SQL> create user rkdbms identified by rkdbms123;

4.SQL> conn rkdbms; // we will get error

SQL> conn rkdbms;

Enter password:

ERROR:

Warning: You are no longer connected to ORACLE.

ORA-01045: user RKDBMS lacks CREATE SESSION privilege; logon denied

5.from sql prompt we can connect to sys

SQL> conn / as sysdba

6.SQL> grant connect to rkdbms;

7.SQL> grant resource to rkdbms;

8.conn rkdbms

enter the password 

Now the user rkdbms is ready to use.

CREATE USER <USER NAME>IDENTIFIED BY <USER PWD> ;

Ex: CREATE USER RKDBMS IDENTIFIED BY RKDBMS123; 

Note1: To create a new user we have to login into the system as an admin and also login to Oracle database as admin. As a normal user we cannot create a new user. 

Note2: Now new user “RKDBMS” will be created. But we cannot use it. To make use of it we need to grant(provide) two rules namely “connect” and “resource” to user “RKDBMS”. 

Syntax : To grant connect rule

Ex: GRANT CONNECT TO RKDBMS; 

Syntax : To grant resource rule 

Ex: GRANT RESOURCE TO RKDBMS; 

Note3: Now user “RKDBMS” is ready to use

ALTER : Alters the structure of the data base

Alter Options : Add, Modify, Drop & Rename 

Alter : ADD

Syntax:

ALTER TABLE <TABLE NAME> ADD(COUMNNAME DATATYPE(SIZE)); 

Ex: ALTER TABLE PRODUCT ADD(EMAIL VARCHAR2(15)); 

Alter : MODIFY 

Note : With modify option we can change Only Data type/ Only Size/ both data type and size. 

Changing Only Datatype 

Syntax:

ALTER TABLE <TABLE NAME> MODIFY(COLNAME new DATATYPE(new SIZE)); 

Ex: ALTER TABLE PRODUCT MODIFY(PNAME CHAR(15)); 

Changing Only Size 

Ex: ALTER TABLE PRODUCT MODIFY(PNAME CHAR(20)); 

Changing Both datatype and size 

Ex: ALTER TABLE PRODUCT MODIFY(PNAME VARCHAR2(10)); 

Alter : DROP 

Syntax:

ALTER TABLE <TABLE NAME> DROP(COLUMN NAME); 

Ex: ALTER TABLE PRODUCT DROP(EMAIL); 

Alter : RENAME 

Syntax: 
ALTER TABLE <TABLE NAME> RENAME COLUMN <Old Name> TO <New Name>; 

Ex: ALTER TABLE PRODUCT RENAME COLUMN PNAME TO PRONAME;

Truncate: 

TRUNCATE command removes all the records from a table. But table's structure remains unchanged. 

Syntax: 

TRUNCATE TABLE <TABLE NAME>; 

Ex: TRUNCATE TABLE PRODUCT;

2.Data Manipulation Language(DML):

DML commands are used for manipulating(for modifying) the data stored in the table. DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back

Select: 

The SELECT statement is used to select data from a database.

Syntax1: (To display all columns in a table)

select <column list> from <tablename>;

Ex: select pid,pname,qty from product; (or)

select * from product;

Syntax2: (To display all columns/specefied columns by using where clause)

select <column list> from <tablename> where <condition>;

EX: select * from product where pid <40;

select pname,qty from product where pid >59;

Insert: 

Note : input data should be enclosed in quotes, if the datatype of a column is char,varchar2 or date. 

Syntax1: 
It is used if user wants to input values to all existing columns in a table. 

INSERT INTO <TABLE NAME>  VALUES(VAL1,VAL2,...VALN); 

Ex: INSERT INTO PRODUCT VALUES(11,'REXONA',5); 

Syntax2: 
It is used if user want to input values to only specified columns. 

INSERT INTO <TABLE NAME>(COL2,COL5) VALUES(VAL2,VAL5); 

INSERT INTO PRODUCT(PID,QTY) VALUES(33,'LUX');

Syntax3: 
It is used to input values iteratively. 

INSERT INTO <TABLE NAME> VALUES(&COL1,&COL2....&COLN); 

(OR) 

Note: If datatype of a column is CHAR / VARCHAR / VARCHAR2/DATE then value should be enclosed in quotes. 

Ex: INSERT INTO PRODUCT VALUES(&SNO,'&SNAME','&CITY'); 

Update: 

It is used to modify the data(records) in a table. 

Syntax1:
This syntax applies / modifes all the records in a table

UPDATE <TABLE NAME> SET <COLUMN NAME>=<NEW VALUE>; 

Ex: UPDATE PRODUCT SET QTY=45; 

Syntax2: 

This syntax applies/modifies specified columns based on a given condition by using where clause

UPDATE <TABLE NAME> SET <COLUMN NAME>=<NEW VALUE> WHERE <CONDITION>; 

Ex: UPDATE PRODUCT SET QTY=45 WHERE PID=4; 

Or 

UPDATE PRODUCT SET QTY=45 WHERE PID>24; 

Or 

UPDATE PRODUCT SET QTY=45 WHERE PNAME=‘LUX’; 

Or 

UPDATE PRODUCT SET QTY=45 WHERE PNAME LIKE ‘P%’; 

Delete: 

This syntax deletes all the records(Rows) in a table, Structure remains constant.

Syntax1:
DELETE FROM <TABLE NAME>; 

Ex: DELETE FROM PRODUCT; 

Syntax2:
This syntax deletes specified columns based on a given condition by using where clause

Ex: DELETE FROM PRODUCT WHERE PNAME LIKE ‘%S’; 

Or 

DELETE FROM PRODUCT WHERE PNAME LIKE ‘%S%’ or QTY>50;

LIKE:

There are two wildcards used in conjunction with the LIKE operator.
1. The percent sign (%) 
The percent sign represents zero, one or multiple characters.
2. The underscore (_) 
The underscore represents a single number or character. 
These symbols can be used in combinations.

3. Data Control Language(DCL):

Data control language are the commands to grant and take back authority from any database user.

Grant Privileges:
We can grant users various privileges to tables. 

These privileges can be any combination of SELECT, INSERT, UPDATE,

DELETE, REFERENCES, ALTER, INDEX, or ALL

Syntax :

GRANT privilege(s) ON object TO user; 

Ex:If we want to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called product to a user name rkdb, the command would be:

GRANT SELECT, INSERT, UPDATE, DELETE ON product TO rkdb;

We can also use ALL keyword to indicate that you wish ALL permissions to be granted for a user named rkdb

GRANT ALL ON product TO rkdb;

If we want to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword. For example:

GRANT SELECT ON product TO public;

Revoke Privileges

Once we have granted privileges, you may need to revoke some or all of these privileges.
We can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax
REVOKE privileges ON object FROM user;

If we want to revoke DELETE privilege on a table called product from a 

user named rkdb: 

REVOKE DELETE ON product FROM rkdb;

If we had granted ALL privileges to public (all users) on the product table and you wanted to revoke these privileges:

REVOKE ALL ON product FROM public;

4.Transaction Control Language:

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them. Here are some commands that come under TCL:

COMMIT

ROLLBACK

SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database.

Syntax:COMMIT;  

Example:

DELETE FROM CUSTOMERS WHERE AGE = 25;  

COMMIT;  

b. Rollback: Rollback command is used to undo transactions that have not already been saved to the database.

Syntax:ROLLBACK;  

Example:

DELETE FROM CUSTOMERS WHERE AGE = 25;  

ROLLBACK;  

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

Syntax:SAVEPOINT SAVEPOINT_NAME;  

Exercise 1: 

SCHEMA 1 : PRODUCT

1)CREATE A TABLE PRODUCT(PID,PNAME,QTY)

2)Display the structure of PRODUCT table.

3)ADD A COLUMN PRICE(alter)

4)ADD A COLUMN EMAIL(alter)5)MAKE USE OF REST OF ALL ALTER COMMANDS(MODIFY,DROP & RANAME)(Ex: alter modify, alter drop alter rename)

6)DROP COLUMN EMAIL

7)INSERT DATA INTO PRODUCT TABLE - USE 3 SYSNTAXES(Insert minimum 10rows)

8)ADD COLUMN MDT- use data type date AND add a value to MDT- use update 

9)DISPLAY ALL ROWS OF PRODUCT TABLE with and without condition(where)

10)UPDATE MULTIPLE ROWS/SINGLE ROW

11)DISPLAY ALL ROWS OF PRODUCT TABLE BASED ON VARIOUS CONDITIONS.

12)DELETE ALL ROWS/DELETE SELECTED ROWS

Exercise 2:

SCHEMA 2 : EMPLOYEE
1)Create a table employee with attributes eid,ename and sal.
2)add a new column email to employee table.
3)change the size of eid as 5 
4)add a new column city
5)change the data type of ename as char 
6)add a new column dept
7)change the city column name as PLACE
8)remove the column dept
9)add a new column dob.
10)add records to employee table use 3 syntaxes 3 per syntax.(input total 9 rows) 
11)display table structure
12)display all the records in the table - without and with condition. 13display employee name and age of all employees.
14)display employee id ,age and salary of all employees whose name starts with 's' 
15)modify all records in employee table by giving 1500 hike in their salary.
16)display all records.
17)modify all records in employee table by giving 2.5% hike, whose salary is <25000 
18)remove all records in table - delete
19)display the structure 
20)restore deleted records 
21)display all records 
22)remove all records - truncate
23)change employee table name as emp. 
24)add a new column email to employee table.