SQL is the official standard language used to access data held in the databases.
SQL organizes data as tables , indexes , views etc. SQL is the tool for organizing , managing and retrieving data stored in the database.
SQL provides various features like portability , client-server architecture ,dynamic data definition , multiple views of data etc.
SQL Statements:
SQL statements are divided into
1. Data Definition language(DDL)
2. Data Manipulation Language(DML)
3. Data Control Language (DCL)
4. Transaction Processing Language (TPL)
5. Data Retrieval Language (DRL)
Data Definition Language (DDL): These statements define the structure of the database. DDL consists of create, alter and drop statements.
Data Manipulation Language (DML): These statements are basically required to manipulate the records of the tabled consists of insert, delete, and update statements.
Data Control Language (DCL): These statements are basically required to control the tables among several users by giving access or by taking back the access to the tables. DCL consists of grant and revoke statements.
Transaction control language (TCL): These statements are basically related to various transactions like insert, delete, and update.
Data retrieval language (DRL): These statements are basically required to retrieve the records for the table. DRL consists of select statements.
Data types in SQL:
1.Varchar2(size):
It is a variable length string with a max length of ‘size’ bytes.
2. Char (size):
It specifies a fixed length character string. Max size is 255
3. Number (n):
It specifies integer type of data with max of n digits.
4.Number(p,s):
It specifies floating number with p as total no of digits and specifies the number of digits to the right of decimal points.
5. Date: It specifies the date in DD-MM-YY format.
Data definition language (DDL):
Create statement: It is used to create and define a table.
Syntax:
Create table
( [column constraints],
[column constraints],
.
.
.
[column constraints]) ;
Column constraints :
1. Primary Key: It will not allow null and duplicate values corresponding to that column.
2. Not Null : It will allow null values corresponding to that column
3. Unique: It will not allow duplicate values corresponding to that column.
4. Check: It will impose the constraints based on the condition being mentioned on the column.
5. Reference( foreign key): It will impose the constraints based on the condition being mentioned on the column
6. Reference (foreign key): It will refer to other column values that are acting as primary key in other table.
7. On delete cascade: It will delete the values in the column in one table, corresponding values in other table will be deleted automatically, and it will work only references only.
Create dept table with deptno as primary key and dept name as not null.
SQL>Create table Dept
(Deptno number(3) primary key,
Dname varchar2(10) not null);
Example 3: Create a customer table.
SQL>Create table Customer
(Cust_id number(5) primary key,
First_name varcahr2(20) not null,
Last_name varchar2(20) not null,
Address varchar2(40),
City varchar2(20),
State varchar2(20),
Phone_no number(9) unique);
Table Level Constraints: 4
Imposing constraints after declaring all the columns of the table, then we call it as table level constraints.
Example 4: Create project table.
SQL>Create table Project
(Emp_id number(5),
Proj_id number(5),
Payment number(8),
Primary key(Emp_id,Proj_id);
Create with Select:
To create a table from taking the records from existing table. When creating a table based on existing table then it is going to transfer only not null constraint, other constraints will not be transferred.
Example 1:
SQL>Create table Emp
As select Empno, Ename, Salary from Employee;
Create with select using where clause:
Example 2: Create a table empno containing the details in deptno 10 only
SQL>Create table Empd
As select * from Employee
Where deptno=10;
Alter Statement: It is used to alter the definition of a table in the database
Syntax:
Alter table
Add column_name data_type
[Modify column_name data_type ];
Add: is used when ever you want to add a new column to the table.
Example 1: Adding Phno as a new field into Employee table
SQL>Alter table Employee
Add(Phno number(7));
Example 2: Adding empno as primary key to Emp table
SQL>Alter table Emp
Add(Primary key(empno));
Modify: is used to change the size of the column of the same data type.Modify will not decrease the column size.Whenever it comes to increase in the size of column it is going to allow only when the field is empty.
Example 1:
Modify the salary field by increasing its size.
SQL>Alter table Emp
Modify(sal number(9,2));
Droping the Constraints:
Example1:
Dropping Primary key constraint we must see that no references are there from other tables.
SQL>Alter table Emp
Drop Primary key;
Drop command: is used to drop the table.
Syntax:
Drop table;
Example 1: Dropping emp table.
SQL>Drop table emp;
Describe command: is used to display the structure of the table.Desc can also be used
Data Manipulation Language (DML):
Insert command: is used to add rows(or records) to the table. The number
and sequence should match that of columns in the table.If the number of data values is less, then specify the column names into which data is being entered. To insert null values, NULL can be used.
Syntax:
Insert intovalues(datavalue_1,………..,datavalue_n);
Insert into(column_1,column_3)
Values(datavalue_1,datavalue_3);
Example 1: Insert 100th record into employee table.
SQL>insert into employee
Values(100,’James’,6000,’17-Mar-03’);
Example 2: Inserting 101th record into employee table with salary and date
Of joining as null values.
SQL>Insert into employee
Values(101,’James’,null,null);
Example 3: Inserting 1001th record into employee table with only empno and name.
SQL>Insert into employee(empno,ename)
Values(1001,’John’);
Insert with select: is used to transfer the data from one table to another table.
Syntax:
Insert into
Select ,…… from
Where;
Example 1:
SQL>Insert into emp
Select empno,ename,sal from employee
Where deptno=10;
Delete Command: is used to delete the records from the specified table.
Syntax:
Delete
Where;
Example 1: Deleting the employee holding empno1001
SQL> Delete employee where empno = 1001;
Example 2 : Delete all the records of the employee table
SQL> Delete employee;
Example 3: Delete the employees who are working as clerks.
SQL> Delete employee where job =’clerk’
Update command: is used to modify the records of the table
Syntax:
Update
Set =
Where ;
Example1: Increase all the employees salary by Rs. 500
SQL> update employee set sal=sal+500;
Example2: Give an increment of 20% salary and commission by 5%of salary
SQL> update employee set sal = sal* 1.2, comm = (sal* 0.05) +comm;
Example3: Promote the manager as director of the company
SQL> update employee set job =’director ‘ where job= ‘manager’;
Example4: Give an increment of 10% salary whose salary is less than Rs. 1000
SQL> update employee set sal = sal*1.1 where sal <1000;
Transaction control language(TCL);
The DML transactions will not be saved implicitly based on the following reasons.
1. Improper shut down
2. System crash
3. When working under SQL environment DML transactions will not saved.
To handle DML transactions explicitly we require a set of commands which are commit, roll back and save point.
Commit command: is used to save all the transactions up to the last command explicitly.
SQL> insert into employee( empno, ename) values (200, ‘smith’);
SQL> commit ;
Roll back command: is used to undo the transactions up to the last commit.
SQL> Rollback;
Savepoint: is used to minimize roll back only to certain transactions
Example:
SQL> insert ………
SQL> savepoint ins;
SQL> update …….
SQL> savepoint upd
SQL> delete ….
SQL> savepoint del;
SQL> Rollback;
SQL> commit;
SQL> Rollback to del;(Once you commit and then give rollback, will remove all save points)
Autocommit command: is used to make all DML transactions to save implicitly after the execution of the command.
SQL> set autocommit on;
SQL> set autocommit off;
To see the status of autocommit we use show command.
SQL> show autocommit;
Data Retrieval language(DRL);
Select command: is used to retrieve the records from the table.
Syntax:
Select < field_name_1>, …….. , from
where ;
Example 1: To display the deptno,dname,loc.
Example 2: To display all the employee details,who are working as manager.
SQL>Select * from emp where job-‘manager’;
Logical Operators:
• And
• Or
• Not
Example 1: To display the employee information who are working in deptno
10 and salary greater than 2000.
SQL>select * from emp
Where deptno=10 and sal>2000;
Example 2: To display the employee information who are working as manager corresponding to deptno 20.
SQL>select * from emp
Where job=’manager’ and depno=20;
Example 3: To display the employee information who are working in deptno 10,20.
SQL>select * from emp
Where deptno=10 or dept=20;
Example 4: To display the employee information who are working as managers corresponding to deptno 10 as well as the employees who are receiving salary more than 2000 corresponding to deptno 20.
SQL>select * from emp
Where job=’manager’ and deptno=10;
Special Operators:
Is null
In
Between
like
is null: is used to check the null values corresponding to the column.
Example 1: To display the employee information who are not receiving any commission.
SQL>select * from emp
Where comm Is null;
Between: Whenever we want to frame the condition in the range of values then we use the between operator.
Example1: To display the employee information whose salary is greater than 1000 and less than 2000.
SQL>select * from emp
Where sal between 1000 and 2000;
Example 2: To display the employee information for those who are working for deptno 10 with their first alphabet of the name is coming in the range of ‘e’ to ‘j’.
SQL>select * from emp
Where deptno=10 and(ename between ‘E’ and ‘J’);
In: When you have multiple conditions among which any one has to be
selected we use in operator.
Example 1: To display the employee details who are working as managers ,clerks ,analyst.
SQL>select * from emp
Where job in(‘manager’,’clerk’,’analyst’);
Example 2:To display the employee who are working in the deptno 10,20,30.
SQL>select * from emp
Where deptno in (10,20,30);
Like: is used when you want to frame a condition based on a particular pattern, for which we need wild card characters, which are ‘%’ and ‘_’.
‘%’ is used to replace any no.of characters in the pattern.
‘_’ is used to replace only single characters in the pattern.
Example 1: To display the employee details whose names are ending with ‘s’.
SQL>select * from emp
Where ename like’%s’;
Example 2: To diplay the employee names have onlay 5 characters.
SQL>select * from emp
Where ename like’_____’;
Example 3: To display the employee details whose name has ‘a’ as second character and ‘r’ as last character.
SQL>select * from emp
Where ename like’_a%r’;
Special operators with not:
Example1: To display the employee information who are receiving any commission.
SQL>select * from emp
Where comm. Is not null;
Example 2:To display employee information for whose who are working as managers and receiving salary other than the range 2000 to 3000.
SQL>select * from emp
Where job=’manager’ and sal not between 2000 and 3000;
Example 3:To display the employee details who are working in dept other than 20,30.
SQL>select * from emp
Where deptno not in (20,30);
Example 4:To display the employee details who are working in deptno 10,20 for their names not ending with ‘s’.
SQL>select * from emp
Where deptno in(10,20) and ename not like ‘%s’;
