Chapter 3: Interactive SQL and performance Tuning

What is SQL?
• SQL is Structured Query Language, which is a computer language for
storing, manipulating and retrieving data stored in a relational database.
• SQL is the standard language for Relational Database System. All the
Relational Database Management Systems (RDMS) like MySQL, MS
Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL
as their standard database language.

Why SQL?

SQL is widely popular because it offers the following advantages:

• Allows users to access data in the relational database management
systems.
• Allows users to describe the data.
• Allows users to define the data in a database and manipulate that data.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures and views.
DDL Commands:
1. CREATE: - The CREATE TABLE statement is used to create a new table in
a database.
• Syntax: - CREATE TABLE table_name ( column1 Data_type, column2
Data_type, column3 Data_type…);
• Example:- CREATE TABLE Persons ( PersonID number(10),
LastName varchar(20), FirstName varchar(20), Address varchar(20), City
varchar(20) );
2. ALTER: - The ALTER TABLE statement is used to add, delete, or modify
columns in an existing table. The ALTER TABLE statement is also used to add
and drop various constraints on an existing table.
• To add a column in a table, use the following syntax:
Syntax: - ALTER TABLE table_name ADD column_name Data_type;
Example: - ALTER TABLE Persons ADD DateOfBirth date;
• To delete a column in a table, use the following syntax
Syntax: - ALTER TABLE table_name DROP COLUMN column_name;
Example: - ALTER TABLE Persons DROP COLUMN DateOfBirth;

3. DROP: - The DROP TABLE statement is used to drop an existing table in a

database.

• Syntax:- DROP TABLE table_name;

• Example:- DROP TABLE Student;

4. TRUNCATE: - The TRUNCATE TABLE statement is used to delete the data

inside a table, but not the table itself. Syntax: - TRUNCATE TABLE
table_name;

• Example:- TRUNCATE TABLE Student;

5. RENAME:- To change the name of the table

• Syntax:- RENAME <old_table_name> To < new_table_name>;

• Example:- RENAME employee TO my employee;
6. DESC:-

• Syntax: - DESC table_name;

• Example: - DESC Student;

DML Commands:

1. INSERT: - The INSERT INTO statement is used to insert new records in a

table.

• Syntax: - INSERT INTO table_name VALUES (values1, values2…….);

• Example: - INSERT INTO persons VALUES ( ‘10’, ‘Jain’ , ‘Vikas’ , ‘

Kothrud’ , ‘Pune’);
2. UPDATE: - The UPDATE statement is used to modify the existing records in
a table.

• Syntax: - UPDATE table_name SET column1 = value1, column2 =

value2, WHERE condition;
• Example: - UPDATE Persons SET Address = 'MG Road', City=
'Mumbai' WHERE PersonID = 1;

3. DELETE: - The DELETE statement is used to delete existing records in a

table.

• Syntax: - DELETE FROM table_name WHERE condition;

• Example: - DELETE FROM Customers WHERE PersonID=2;

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

• Syntax: - SELECT column1, column2... FROM table_name;

SELECT * FROM table_name;

 • Example:- SQL> SELECT PersonID, City FROM Persons; (Selects the
"PersonID" and "City" columns from the "Persons" table)

SQL> SELECT * FROM Customers; (Selects all the columns from the
"Persons" table)

DCL (Data Control Language) Commands

• Data Control Language provides database administrators with the ability to
grant users database permissions, revoke permissions previously granted.

• Two types of DCL commands are:

1. Grant and

2. Revoke.

1. Grant: -

• To allow specified users to perform specified tasks

• SQL GRANT is a command used to provide access or privileges on the

database objects to the users.

Syntax: -

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
Where,
• Privilege_name is the access right or privilege granted to the user. Some of
the access rights are ALL, EXECUTE, SELECT, ALTER, DELETE,
INSERT, and UPDATE.
• Object_name is the name of a database object like TABLE, VIEW,
STORED PROC and SEQUENCE.
 • User_name is the name of the user to whom an access right is being
granted.
• PUBLIC is used to grant access rights to all users.
• ROLES are a set of privileges grouped together.
• WITH GRANT OPTION - allows a user to grant access rights to other
users.
2. Revoke:-
• The revoke command removes user access rights or privileges to the
database objects.

a) REVOKE SELECT ON employee FROM user1; (This command will

revoke a SELECT privilege on employee table) from user1.
b) Revoke delete privilege on student table from Prakash.
SQL>REVOKE Delete ON student FROM Prakash.
Syntax:-
REVOKE privilege_name ON object_name FROM {user_name |PUBLIC
|role_name}
Example:-

TCL (Transaction Control Language) Commands

• A Transaction begins with the first executable SQL statement after a

Commit, Rollback or Connection made to the Oracle engine.
• All changes made to an Oracle table data via a transaction are made
or undo at one instance.
• SQL Transaction Control Language commands are used for
managing changes affecting the data.
• These commands are COMMIT, ROLLBACK and SAVEPOINT.
1. COMMIT: -
• Commit command is used to permanently save any transaction into
database.
• The COMMIT command is used to save changes invoked by a
transaction to the database.
• The COMMIT command saves all transactions to the database since
the last COMMIT command.
• The syntax for COMMIT command is as follows:
• SQL> COMMIT;

2. ROLLBACK: -
• This command restores the database to last committed state.
• The ROLLBACK command is used to undo transactions that have
not already been saved to the database.
• The ROLLBACK command can only be used to undo transactions
since the last COMMIT or ROLLBACK command was issued.
• We can either rollback the entire transaction or till a particular save
point transaction can be rolled back.
• The syntax for ROLLBACK is:
ROLLBACK TO SAVEPOINT_NAME; OR

ROLLBACK;
• Example: - ROLLBACK TO sv1; OR ROLLBACK;

3. SAVEPOINT: -
• Savepoint command is used to temporarily save a transaction so that
you can rollback to that point whenever necessary.
• Syntax: SAVEPOINT <Save_Point_Name>
• Example: - SAVEPOINT A;
SELECT DISTINCT

• Inside a table, a column often contains many duplicate values; and

sometimes you only want to list the different (distinct) values.
 • The distinct keyword is used to return only distinct (different) values.

• Syntax: - SELECT DISTINCT column1, column2 ...FROM

table_name;
• Example:- SELECT DISTINCT Country FROM Customers;

WHERE Clause
• The WHERE clause is used to filter records.
• The WHERE clause is used to extract only those records that
fulfill a specified condition.
• Syntax: - SELECT column1, column2... FROM table_name
WHERE condition;
• Example:- SELECT * FROM Customers WHERE
CustomerID=1;
 SQL Operators
Arithmetic Operators (Q. List and explain any 4
arithmetic operators in SQL with example.)
• Arithmetic operators are used to perform mathematical
functions in SQL—the same as in most other languages.
There are four conventional operators for mathematical
functions:

+ (addition)
- (subtraction)
* (multiplication)
/ (division)
Operator Description Example
+ (addition) Addition is performed SELECT SALARY + BONUS
through the use of the FROM EMP;
plus (+) symbol.
- (subtraction) Subtraction is performed SELECT SALARY - BONUS
using the minus FROM EMP;
(-) symbol.
* (multiplication) Multiplication is SELECT SALARY * 10 FROM
performed by using the EMP;
asterisk (*) symbol.
/ (division) Division is performed SELECT SALARY / 10 FROM
through the use of the EMP;
―/‖ symbol.
Comparison Operators:-
Operator Description Example
= Checks if the values of two operands (a = b) is not true.
are equal or not, if yes then condition
becomes true.
!= Checks if the values of two operands (a != b) is true.
are equal or not, if values are not
equal then condition becomes true.
<> Checks if the values of two operands (a <> b) is true.
are equal or not, if values are not
equal then condition becomes true.
> Checks if the value of left operand is (a > b) is not true.
greater than the value of right
operand, if yes then condition
becomes true.
< Checks if the value of left operand is (a < b) is true.
less than the value of right operand, if
yes then condition becomes true.
>= Checks if the value of left operand is (a >= b) is not true.
greater than or equal to the value of
right operand, if yes then condition
becomes true.
<= Checks if the value of left operand is (a <= b) is true.
less than or equal to the value of right
operand, if yes then condition
becomes true.
!< Checks if the value of left operand is (a !< b) is false.
not less than the value of right
operand, if yes then condition
becomes true.
!> Checks if the value of left operand is (a !> b) is true
not greater than the value of right
operand, if yes then condition
becomes true.
Example 1. SQL> SELECT * from customers where salary >15000;
SQL> Select *from customers where PersonID=15;
 Logical Operators:

Operat Description Example

or
AND The AND operator allows the SQL> Select *from Persons
existence of multiple Where FirstName = ’Vikas’
conditions in an SQL AND LastName =’Jain’;
statement's WHERE clause.
OR The OR operator is used to SQL> Select *from Persons
combine multiple conditions in Where FirstName = ’Vikas’
an SQL statement's WHERE OR LastName =’Jain’;
clause.
NOT The NOT operator reverses the SQL> Select FirstName,
meaning of the logical operator LastName from Persons
with which it is used. E.g.: Where NOT
NOT EXISTS, NOT Games=’football’;
BETWEEN, NOT IN, etc.
This is a negate operator.

Other Comparison Operators:-

Operato Description Example
r
LIKE The LIKE operator is used to SQL> Select FirstName
compare a value to similar From Persons Where
values using wildcard FirstName LIKE ‘_i%’;
operators.
IN The IN operator is used to SQL> Select FirstName
compare a value is equal to any From Student Where
one of specified set of values. subject IN (‘Maths’,
‘Science’);
BETW The BETWEEN operator is SQL> Select FirstName
EEN used to search for values that From Student Where age
…..AN are within a set of values, given BETWEEN 10 AND 15 ;
D the minimum value AND the
maximum value.
IS The NULL operator is used to SQL> Select FirstName
NULL compare a value with a NULL From Student Where
value. games IS NULL;

%: The percent sign represents zero, one, or multiple

characters
_: The underscore represents a single character
Set operator
Clauses
JOIN
SQL Join operation combines data or rows from two or more tables
based on a common field between them.
Consider the two tables below as follows:

Student:

StudentCourse :

Both these tables are connected by one common key (column) i.e
ROLL_NO.
We can perform a JOIN operation using the given SQL query:
SELECT s.roll_no, s.name, s.address, s.phone, s.age, sc.course_id
FROM Student s
JOIN StudentCourse sc ON s.roll_no = sc.roll_no;
Output:

ROLL_NO NAME ADDRESS PHONE AGE COURSE_ID

1 HARSH DELHI XXXXXXXXXX 18 1

2 PRATIK BIHAR XXXXXXXXXX 19 2

3 RIYANKA SILGURI XXXXXXXXXX 20 2

4 DEEP RAMNAGAR XXXXXXXXXX 18 3

5 SAPTARHI KOLKATA XXXXXXXXXX 19 1

Types of JOIN in SQL

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• NATURAL JOIN
• SQL INNER JOIN
• The INNER JOIN keyword selects all rows from both the tables as
long as the condition is satisfied. This keyword will create the result-
set by combining all rows from both the tables where the condition
satisfies i.e value of the common field will be the same.
• Syntax:
• The syntax for SQL INNER JOIN is:
• SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
•
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM
Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

LEFT JOIN
SQL LEFT JOIN
LEFT JOIN returns all the rows of the table on the left side of the join and
matches rows for the table on the right side of the join. For the rows for which
there is no matching row on the right side, the result-set will contain null. LEFT
JOIN is also known as LEFT OUTER JOIN.
Syntax
The syntax of LEFT JOIN in SQL is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

SQL RIGHT JOIN

RIGHT JOIN returns all the rows of the table on the right side of the join
and matching rows for the table on the left side of the join.
It is very similar to LEFT JOIN For the rows for which there is no matching
row on the left side, the result-set will contain null.
RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax:
The syntax of RIGHT JOIN in SQL is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

SQL FULL JOIN

FULL JOIN creates the result-set by combining results of both LEFT JOIN
and RIGHT JOIN. The result-set will contain all the rows from both
tables. For the rows for which there is no matching, the result-set will
contain NULL values.
Syntax
The syntax of SQL FULL JOIN is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
NAME COURSE_ID

HARSH 1

PRATIK 2

RIYANKA 2

DEEP 3

SAPTARHI 1

DHANRAJ NULL

ROHIT NULL

NIRAJ NULL

NULL 4

NULL 5

NULL 4
Nested Query
In SQL, a nested query involves a query that is placed within another query.
Output of the inner query is used by the outer query. A nested query has two
SELECT statements: one for the inner query and another for the outer query.

Types of Nested Queries in SQL

Subqueries can be either correlated or non-correlated

Non-correlated (or Independent) Nested Queries

Non-correlated (or Independent) Nested Queries : Non-correlated (or

Independent) subqueries are executed independently of the outer query. Their
results are passed to the outer query.

Correlated Nested Queries

Correlated subqueries are executed once for each row of the outer query. They
use values from the outer query to return results.
Examples

Consider the following sample table to execute nested queries on these.

Table: employees table

emp_id emp_name dept_id

Table: departments table
1 John 1
dept_id dept_name
2 Mary 2
1 Sales
3 Bob 1
2 Marketing
4 Alice 3
3 Finance
5 Tom 1

Table: sales table

sale_id emp_id sale_amt

1 1 1000

2 2 2000

3 3 3000

4 1 4000

5 5 5000

6 3 6000

7 2 7000
Example 1: Find the names of all employees in the Sales department.

SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id

FROM departments WHERE dept_name = 'Sales');

Output

emp_name

John

Bob

Tom

Example 2: Find the names of all employees who have made a sale

Required query

SELECT emp_name FROM employees WHERE EXISTS (SELECT emp_id

FROM sales WHERE employees.emp_id = sales.emp_id);

Output

emp_name

John

Mary

Bob

Alice

Tom
view