Categories Data Definition Language (DDL)

Data Manipulation Language (DML)

Transaction Control Language (TCL)

Data Control Language (DCL)

Function Creating, modifying, and dropping the structure of database objects

Storing, retrieving, modifying, and deleting data

Managing changes affecting the data.

Providing security to database objects

Commands CREATE, ALTER, DROP, RENAME, and TRUNCATE

SELECT, INSERT, UPDATE, and DELETE

COMMIT, ROLLBACK, and SAVEPOINT

GRANT and REVOKE

SQL Alias: To make selected columns more readable.

Aliases for Columns

SELECT first_name AS Name FROM student_details;

or

SELECT first_name Name FROM student_details;

Aliases for Tables:

SELECT s.first_name FROM student_details s;

WHEN to use Aliases?

There are more than one tables involved in a query,

Functions are used in the query,

The column names are big or not readable,

More than one columns are combined together.

SQL WHERE Clause:

Used to restrict data

Can be used along with SELECT, DELETE, UPDATE statements

Comparison Operators and Logical Operators are used in WHERE Clause

NOTE: Aliases defined for the columns in the SELECT statement cannot be used in the WHERE clause to set conditions.

SQL Operators:

Mainly used in the WHERE clause, HAVING clause to filter the data to be selected

2 types:

1. Comparison Operators

Used to compare the column data with specific values in a condition

Used along with the SELECT statement to filter data based on specific conditions

2. Logical Operators (AND, OR, NOT)

NOTE:

When logical operators are combined in a SELECT statement, then the order in which the

statement is processed is:

1) NOT

2) AND

3) OR

SQL Comparison Keywords: (All operate on Column Values)

IN

o Similar to OR Operator.

o SELECT ENAME, SAL from EMP where SAL IN(2000, 3000, 4000)

BETWEEN...AND

o Used to compare data for a range of values.

o SELECT ENAME, SAL from EMP where SAL BETWEEN 2000 AND 3000

IS NULL

o Is used to display all the rows for columns that do not have a value.

o SELECT ENAME, SAL from EMP where SAL IS NULL

LIKE

o List all rows in a table whose column values match a specified pattern

o select ENAME, SAL, COMM from EMP where SAL LIKE '2%' OR

SAL LIKE '_00%'

SQL ORDER BY:

Used in SELECT statement to sort results either in Ascending order or Descending order.

Bi-default it is Ascending order

Ascending Order

o SELECT ENAME, SAL from EMP ORDER BY SAL

Descending Order

o SELECT ENAME, SAL from EMP ORDER BY SAL DESC

NOTE:

Column Numbers can also be used in place of Column Names

o SELECT EMPNO, ENAME from EMP ORDER BY 1, 2

Aliases defined in the SELECT Statement can be used in ORDER BY Clause (as shown in the example below)

Using Expressions in ORDER BY clause

o SELECT ENAME, SAL, SAL*1.2 AS new_salary FROM EMP

WHERE SAL*1.2 > 3000

ORDER BY new_salary;

SQL GROUP Functions:

Built-in SQL functions that operate on groups of rows and return one value for the

entire group.

COUNT

o SELECT COUNT (*) from EMP where JOB = 'MANAGER'

MAX

o To get maximum value from a column (can be of any type)

SELECT MAX(JOB) from EMP

MIN

o To get minimum value from a column (can be of any type)

o SELECT MIN(SAL) from EMP

AVG

o To get average value of a numeric column

o SELECT AVG(SAL) from EMP

SUM

o To get average value of a numeric column

o SELECT SUM(SAL) from EMP

DISTINCT

o To get Number of distinct jobs

SELECT COUNT (DISTINCT JOB) from EMP

SQL GROUP BY Clause:

Used along with the group functions to retrieve data grouped according to one or

more columns

SELECT JOB, DEPTNO, SUM(SAL) from EMP Group BY DEPTNO, JOB

SQL HAVING Clause:

Used to filter data based on the group functions.

Similar to WHERE condition but is used with group functions.

Group functions cannot be used in WHERE Clause but can be used in HAVING

clause

SELECT Job, SUM(Sal) from EMP GROUP BY Job HAVING SUM(Sal) > 5000

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT

statement, then:

i. WHERE clause is processed first,

ii. Then the rows that are returned after the WHERE clause are grouped based on the

GROUP BY clause.

iii. Finally, any condition(s) on the group function(s) in the HAVING clause is applied

to the grouped rows before the final output is displayed.

SQL INSERT Statement:

If data is to be inserted in all the columns from another table:

INSERT INTO Emp

Select * from temp_Emp

INSERT INTO Emp (Emp_id, Emp_name)

SELECT id, name FROM temp_Emp

NOTE:

In the above scenario, when adding a new row, the data-type of the value and the column

must match in both the tables.

SQL UPDATE Statement:

Used to modify existing rows in a table

In the Update statement, WHERE clause identifies the rows that get affected.

If the WHERE clause is not included then column values for all the rows get affected.

Update Emp SET FULL_NAME = 'LALU' where ID = 3

DELETE, DROP & TRUNCATE:

DELETE TRUNCATE DROP

1. Removes all the

rows from a table

Removes all the rows

from a table &

releases the

storage space

Removes an object

from the database i.e.

deletes the rows and

removes the table

structure

2. Can be Rolled back

(DML) until

transaction is

committed

Cannot be Rolled

back (DDL) & hence

is faster than

DELETE

Cannot be rolled back

(DDL)

3. - The table

structure remains

the same

None of these

problems will exist.

All the relationships

with other tables

will no longer be

valid.

Integrity

Constraints will be

dropped.

Grant or Access

privileges &

relationships with

other tables should

be established

again.

4. DELETE from Emp

where E_UID = ‘w’

TRUNCATE table Emp DROP table Emp

OR

DELETE from Emp

SQL CREATE TABLE Statement:

In Oracle database, the data-type for an integer column is represented as "number".

In Oracle, table can also be created this way:

CREATE table T_Emp

SELECT * from Emp

T_Emp table is created with the same number of columns and data-type as Emp table.

SQL ALTER TABLE Statement:

Used to modify the definition (structure) of a table by modifying the definition of its columns.

The ALTER command is used to perform the following functions. o Add, drop, modify table columns

o Add and drop constraints

o Enable and Disable constraints

Add a Column:

ALTER TABLE Emp ADD Emp_Name varchar2(30)

Drop a Column:

ALTER TABLE Emp DROP Emp_Name varchar2(30)

Modify a Column:

ALTER TABLE Emp MODIFY Emp_ID integer

SQL RENAME Command:

To change name of table or database object

Supported from ORACLE 8i

RENAME Emp TO UHG_Emp

OR

ALTER table EMP RENAME TO UHG_Emp

SQL Integrity Constraints:

Used to apply business rules to database tables

Constraints in SQL are of 5 types:

o Primary Key

o Foreign Key

o Not Null

o Unique

o Check

Can be defined in 2 ways:

o Column-level definition

specified immediately after the column definition

o Table-level definition

specified after all the columns are defined

PRIMARY KEY

o At Column level

Emp_ID integer CONSTRAINT p_key PRIMARY KEY

o At Table level

CREATE table Emp

(

id integer,

name varchar2(20),

CONSTRAINT p_key1 PRIMARY KEY(id)

);

FOREIGN KEY or REFERENTIAL INTEGRITY

This constraint identifies any column referencing the PRIMARY KEY in another table.

Establishes a relationship between two columns in the same table or between different tables.

For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring.

One or more columns can be defined as Foreign key.

The column which is referred must be the UNIQUE or PRIMARY key of that table.

For Example:

CREATE TABLE product

(

prod_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY,

prod_name char(20));

At Column level

CREATE TABLE order_items

(

order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,

prod_id number(5) CONSTRAINT pd_id_fk REFERENCES prod(prod_id)

prod_name char(20));

o At Table level

CREATE TABLE order_items

(

order_id number(5),

prod_id number(5),

prod_name char(20),

CONSTRAINT od_id_pk PRIMARY KEY (order_id),

CONSTRAINT pd_id_fk FOREIGN KEY (prod_id) REFERENCES prod(prod_id));

NOT NULL

Ensures all rows in the table contain a definite value for the column which is specified as not null

Emp_ID integer CONSTRAINT nn_key NOT NULL

UNIQUE

Ensures that a column or a group of columns in each row have a distinct value.Column can have a null value but the values cannot be duplicated.

o At Column level

Emp_ID integer CONSTRAINT u_key UNIQUE

o At Table level

CREATE table Emp

(

id integer,

name varchar2(20),

CONSTRAINT u_key1 UNIQUE(id)

);

CHECK

Defines business rule on a column

All rows must satisfy this rule

Constraints can be applied on a single column or group of columns

o At Column level

Emp_Gender char(1) CHECK (Emp_Gender IN(‘M’, ‘F’))

o At Table level

CREATE table Emp

(

id integer,

Emp_Gender char(1),

CONSTRAINT gender_chk CHECK (Emp_Gender IN(‘M’,’F’)));

NOTE:

The integrity constraints can be defined at column level or table level.

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

DESCRIBE Emp or DESC Emp

SQL Joins:

Used to relate information in different tables. Part of the SQL query that retrieves rows from two or more tables. SQL Join condition is used in the SQL WHERE Clause of select, update, delete

statements. If a SQL join condition is omitted or if it is invalid the join operation will result in a

Cartesian product. [For e.g., if the first table has 10 rows and the second table has 20 rows, the result will be 10 * 20, or 200 rows i.e. this query will take a long time to execute.]

2 TYPES:

Equi Joins

Uses equal sign as comparison operator

o SQL OUTER JOIN

o SQL INNER JOIN

Non Equi Joins

Equi Joins

I. SQL INNER JOINAll the rows returned by the SQL query which satisfy the SQL join condition specified.NOTE: rule must be true to avoid Cartesian product

SELECT e.*, d.Deptno

FROM Emp1 e, Dept1 d

WHERE e.Deptno = d.Deptno

II. SQL OUTER JOIN Returns all rows from both tables which satisfy the join condition along with

rows which do not satisfy the join condition from one of the tables. In Oracle, Outer Join is ( + ) and is used on one side of the join condition

only.

SELECT Emp1.Ename, Dept1.Deptno

FROM Emp1, Dept1

WHERE Dept1.Deptno (+) = Emp1.Deptno

NOTE: If the (+) operator is used in the left side of the join condition it is equivalent to left outer join. If used on the right side of the join condition it is equivalent to right outer join.

SQL Self Join: Used to join a table to itself Used when a table having FOREIGN KEY references its own PRIMARY

KEY

SELECT e1.EId, e1.Ename, e2.PersonId, e2.EAdd,

FROM Emp1 e1, Emp1 e2

WHERE e1.EId = e2.PersonId

Non Equi Joins

All comparison operators except the equal (=) operator are used (E.g. >=, <=, <, >)

SELECT e.EId, e.Ename

FROM Emp1 e

WHERE e.Ename != ‘Ankit’

SQL Views:

Selective amount of data can be seen from one or more tables Used to restrict access to database (for security) Hides complexity

CREATE VIEW ABC AS

Select Ename, Sal

FROM Emp

select SYSDATE from Dual

select SYSDATE, ADD_MONTHS(SYSDATE, 2) from DUAL

select SYSDATE, NEXT_DAY(SYSDATE, 'FRI') from DUAL

select SYSDATE, LAST_DAY(SYSDATE) from DUAL

select SYSDATE, ADD_MONTHS(SYSDATE, 2) from DUAL

select 100+ LTRIM('#####100', '#') from DUAL

select NEXT_DAY(To_DATE('11-October-1989 12:30:50', 'DD-month-

YYYY HH:MI:SS'), 'WED') from DUAL