SQL Basics (DDL,DML,DCL,TCL,SQL Keywords, SQL Syntax)

SQL - Structured Query Language
INTRODUCTION
SQL is divided into the following:
1) Data Definition Language (DDL)
2) Data Manipulation Language (DML)
3) Data Retrieval Language (DRL)
4) Transaction Control Language (TCL)
5) Data Control Language (DCL)

DDL -- create, alter, drop, truncate, rename
DML -- insert, update, delete
DRL -- select
TCL -- commit, rollback, savepoint
DCL -- grant, revoke

Create Table Syntax:
Create table table_name (col1 datatype1, col2 datatype2 …coln datatypen)
Insert Syntax:
Case 1: insert into table_name values (value1, value2, value3 …. Valuen)
Case 2:insert into table_name(col1, col2, col3 … Coln) values (value1, value2, value3.. Valuen)
Select Syntax:
Select * from table_name -- here * indicates all columns
or
Select col1, col2, … coln from table_name
Update Syntax:
Update table_name set col1 = value1, col2 = value2 where condition
Delete Syntax:
Delete from table_name where condition
Truncate Syntax:
truncate table table_name
Drop Syntax:
Drop table table_name
Rename Syntax:
rename old_table_name to new_table_name

CONDITIONAL SELECTIONS AND OPERATORS
We have two clauses used in this
1. Where
2. Order by
1. Using Where:
select * from table_name where condition
The following are the different types of operators used in where clause:
a. Arithmetic operators
b. Comparison operators
c. Logical operators
Arithmetic operators -- highest precedence
+, -, *, /
Comparison operators

(1) =, !=, >, <, >=, <=, <>
(2) between, not between
(3) in, not in
(4) null, not null
(5) like
Logical operators
(1) And
(2) Or -- lowest precedence
(3) not
Using NULL
This will gives the output based on the null values in the specified column.
Syntax:
Select * from table_name where col is null
2. Using Order by:
This will be used to ordering the columns data (ascending or descending).
Syntax:
Select * from table_name order by col desc
By default database server will use ascending order.
If you want output in descending order you have to use desc keyword after the column.

SUB QUERIES
-- Nesting of queries, one within the other is termed as a subquery.
-- A statement containing a subquery is called a parent query.
-- Subqueries are used to retrieve data from tables that depend on the values in the table itself.

Types
1. Single row subqueries
2. Multi row subqueries
3. Multiple subqueries
4. Correlated subqueries

Single Row Sub Queries
In single row subquery, it will return one value.
Ex: select * from emp where sal > (select sal from emp where empno = 7566)

MultiRow Sub Queries
In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.
Ex:
select * from emp where sal > any (select sal from emp where sal between 2500 and 4000)
select * from emp where sal > all (select sal from emp where sal between 2500 and 4000)

Multiple Sub Queries
There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.
Ex:
select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from emp)) Correlated Sub Queries
A subquery is evaluated once for the entire parent statement where as a correlated subquery is evaluated once for every row processed by the parent statement.
Ex:
select distinct deptno from emp e where 5 <= (select count(ename) from emp where e.deptno = deptno) Using Exists:
Suppose we want to display the department numbers which has more than 4 employees,
select deptno,ename from emp e1 where exists (select * from emp e2 where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by deptno,ename

SET OPERATORS
Types
-- Union
-- Union all
-- Intersect
-- Minus

UNION
This will combine the records of multiple tables having the same structure.
Ex:
select * from student1 union select * from student2

UNION ALL
This will combine the records of multiple tables having the same structure but including duplicates.
Ex:
select * from student1 union all select * from student2

INTERSECT
This will give the common records of multiple tables having the same structure.
Ex:
select * from student1 intersect select * from student2

MINUS
This will give the records of a table whose records are not in other tables having the same structure.
Ex:
select * from student1 minus select * from student2

Tags: SQL Basics, SQL Introduction, DDL, DML, DRL, DCL, TCL, Database Tables, SQL Keywords