Unit - III Interactive SQL and Performance

Tuning(part 1)

SQL
o SQL stands for Structured Query Language. It is used for storing and managing data in
relational database management system (RDMS).

o It is a standard language for Relational Database System.

o SQL statements are used to perform different operation on database like creation,
insertion, deletion etc.

o All the RDBMS like MYSQL, Informix, Oracle, MS Access and SQL Server use SQL as their
standard database language.

SQL Data Types

Data types are used to represent the nature of the data that can be stored in the database table.
Various types of data types of SQL as follows.
CHAR(Size): It is used to specify a fixed length string that can contain numbers, letters, and
special characters. Its size can be 0 to 255 characters. Default is 1.
VARCHAR(Size): It is used to specify a variable length string that can contain numbers, letters,
and special characters. Its size can be from 0 to 65535 characters.
INT(size): It is used for the integer value. Its signed range varies from -2147483648 to
2147483647 and unsigned range varies from 0 to 4294967295. The size parameter specifies the
max display width that is 255.
FLOAT(p): It is used to specify a floating point number.
DECIMAL(size, d): It is used to specify a fixed point number. Its size parameter specifies the total
number of digits. The number of digits after the decimal parameter is specified by d parameter.
BOOLEAN: It is used to specify Boolean values true and false. Zero is considered as false, and
nonzero values are considered as true.
DATE: It is used to specify date format YYYY-MM-DD.
TIME: It is used to specify the time format. Its format is hh:mm:ss

M. U. MUN DMS
Types of SQL Commands
SQL is a structured query language, which is used to deal with structured data. Structured data is data
that is generally stored in the form of relations or tables.
Whenever we store the data in tables or relations, we need SQL commands. Moreover, these
commands are also required to retrieve the data which is stored in tables.

 data definition language.

o DDL stands for data definition language. DDL Commands deal with the schema, i.e., the
table in which our data is stored.

o All the structural changes such as creation, deletion and alteration on the table can be
carried with the DDL commands in SQL.

o Commands covered under DDL are:

1. CREATE: to create a database structure.

2. ALTER: to change the database structure.
3. DROP: to delete database instances and database structure
4. TRUNCATE: to delete database data.
5. RENAME: to rename database.

M. U. MUN DMS
 Data Manipulation Language
o DML stands for Data Manipulation Language. Using DML commands in SQL, we can make
changes in the data present in tables.

o Whenever we wish to manipulate the data or fetch the data present in SQL tables, we can
use DML commands in SQL.

o DML commands in SQL will change the data, such as inserting new records, deleting or
updating existing records from the SQL tables. We can also retrieve all the data from SQL
tables according to our requirements.

o Commands covered under DDL are:

o INSERT: to insert record into table

o SELECT: retrieve the data from database
o UPDATE: update record in table
o DELETE: delete the records from table

 Data Control Language.

o DCL stands for Data Control Language.

o Whenever we want to control the access to the data present in SQL tables, we
will use DCL commands in SQL. Only the authorized users can access the data
stored in the tables.
o Every user will have some pre-defined privileges; accordingly, the data can be
accessed by that particular user. Using the DCL commands in SQL, we can give
privileges to the user on the SQL database and tables, or we can also revoke the
given privileges from the user.
o Commands covered under DCL are:

M. U. MUN DMS
 1. GRANT: Access privileges can be assigned to a user for the databases and tables
using the GRANT command.
 The Syntax for the GRANT command is:

GRANT privilege_name ON object_name TO user_name

 privilege_name is the access right granted to the user. Some of the access
rights are ALL, EXECUTE, SELECT, UPDATE, DELETE, INSERT etc.
 object_name is the name of an database object like TABLE, VIEWand
SEQUENCE.
 user_name is the name of the user to whom an access right is being granted.
 Example:

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE_NAME TO USERNAME;

2. REVOKE: All the access privileges which are already assigned to the user can be
revoked by using the REVOKE command.

 The Syntax for the GRANT command is:

REVOKE privilege_name ON object_name FROM user_name

 Example:
REVOKE SELECT, UPDATE, INSERT, DELETE ON TABLE_NAME FROM
USERNAME;

 Transaction Control Language.

o TCL stands for Transaction Control Language. TCL commands are generally used in
transactions.

o Using TCL commands in SQL, we can save our transactions to the database and roll them
back to a specific point in our transaction. We can also save a particular portion of our
transaction using the SAVEPOINT command.

o Commands covered under TCL are:

M. U. MUN DMS
1. COMMIT:
To save all the operations executed in a particular transaction, we need to execute a
commit command just after the transaction completion.

2. ROLLBACK

Using the rollback command in SQL, you can roll to the last saved state of a transaction.

3. SAVEPOINT

Using the SAVEPOINT command, you can assign a name to a specific part of the
transaction.

M. U. MUN DMS
DML COMMANDS
Following are the four main DML commands in SQL:

1. SELECT Command
2. INSERT Command
3. UPDATE Command
4. DELETE Command

1. INSERT DML Command

o INSERT is important data manipulation command in Structured Query Language,
which allows users to insert data in database tables.
o Syntax of INSERT Command (when user want to insert all column values)

INSERT INTO TABLE_NAME VALUES (value_1, value_2, value_3,...... value_N ) ;

o Syntax of INSERT Command (when user want to insert specific column values)

INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , column_Name3 , ..... col

umn_NameN ) VALUES (value_1, value_2, value_3, ..... value_N ) ;

o Examples of INSERT Command

o Let's take the following student table, which consists of only 2 records of the
student.
ID NAME MARKS
101 RAM 60
102 SHYAM 80

o Suppose, you want to insert a new record into the student table. For this, you
have to write the following DML INSERT command:

INSERT INTO Student VALUES (104, Anmol, 89);

M. U. MUN DMS
 o Suppose, you want to insert a new record with specific column values into the
student table. For this, you have to write the following DML INSERT command:

INSERT INTO Student(ID, NAME) VALUES (105, Ankita);

o After executing above two queries, student table consist 4 records as follows:
ID NAME MARKS
101 RAM 60
102 SHYAM 80
104 Anmol 89
105 Ankita null

2. UPDATE DML Command

o UPDATE is another most important data manipulation command in Structured
Query Language, which allows users to update or modify the existing data in
database tables.
o Syntax of UPDATE Command

UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_N]

WHERE CONDITION;

o Here, 'UPDATE', 'SET', and 'WHERE' are the SQL keywords, and 'Table_name' is
the name of the table whose values you want to update.
o Examples of the UPDATE command
o Let's take a Student table consisting of the following records:
ID NAME MARKS
101 RAM 60
102 SHYAM 80
104 Anmol 89
105 Ankita null

M. U. MUN DMS
o Suppose, you want to update Marks of student whose Id is 104. To do this, you have
to write the following DML Update command:

UPDATE Student SET Marks = 90 WHERE Id = 104;

3. DELETE DML Command

o DELETE is a DML command which allows SQL users to remove single or
multiple existing records from the database tables. We use the WHERE clause
with the DELETE command to select specific rows from the table.
o Syntax of DELETE Command

DELETE FROM Table_Name WHERE condition;

o Examples of DELETE Command

o Let's take a Student table consisting of the following records:

ID NAME MARKS
101 RAM 60
102 SHYAM 80
104 Anmol 90
105 Ankita null

o Suppose, you want to delete the record of those students whose Marks is greater
than 70. To do this, you have to write the following DML Update command:

DELETE FROM Student WHERE Marks > 70 ;

4. SELECT DML Command

o SELECT is the most important data manipulation command in Structured Query

Language. The SELECT command shows the records of the specified table. It also
shows the particular record of a particular column by using the WHERE clause.

M. U. MUN DMS
o If we want to retrieve the data from all the columns of the table, we have to
use the following SELECT command:
o Syntax of SELECT DML command

SELECT * FROM table_name;

o If we want to retrieve the data from specific columns of the table, we have to
use the following SELECT command:

SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM Name_of_

table;

o Here, column_Name_1, column_Name_2, ….., column_Name_N are the names

of those columns whose data we want to retrieve from the table.
o If we want to retrieve the data from all the columns of the table with specific
conditions, we have to use the following SELECT command:

SELECT * FROM table_name WHERE condition;

o Examples of SELECT Command

o Example 1: This example shows all the values of every column from the table.

SELECT * FROM Student;

o This SQL statement displays the following values of the student table:
ID NAME MARKS
101 RAM 60
102 SHYAM 80
104 Anmol 89
105 Ankita null

M. U. MUN DMS
o Example 2: This example shows all the values of a specific column from the
table.

SELECT id, name FROM Student;

o This SQL statement displays the following values of the student table:
ID NAME
101 RAM
102 SHYAM
104 Anmol
105 Ankita

o Example 3: This example describes how to use the WHERE clause with the SELECT
DML command.

SELECT * FROM Student WHERE marks> 70;

o This SQL statement displays the following values of the student table:
ID NAME MARKS
102 SHYAM 80
104 Anmol 89

M. U. MUN DMS
SQL Operators
 An SQL operator is a reserved word or a character used primarily in an SQL
statement's WHERE clause to perform operation(s), such as comparisons and
arithmetic operations. These Operators are used to specify conditions in an SQL
statement.
 An SQL operator can be either a unary or binary operator. A unary operator uses
only one operand to perform the unary operation, whereas the binary operator
uses two operands to perform the binary operation.

 Types of SQL Operators

 SQL Arithmetic Operators
 SQL Comparison / Relational Operators
 SQL Logical Operators
 SQL Set Operators

1. SQL Arithmetic Operators

 The Arithmetic Operators perform the mathematical operation on the
numerical data of the SQL tables. These operators perform addition, subtraction,
multiplication, and division operations on the numerical operands.
 Following are the various arithmetic operators performed on the SQL data:

1. SQL Addition Operator (+):The Addition Operator in SQL performs the

addition on the numerical data of the database table. In SQL, we can easily add
the numerical values of two columns of the same table by specifying both the
column names as the first and second operand. We can also add the numbers to
the existing numbers of the specific column.

M. U. MUN DMS
 EXAMPLE: LET’S consider following employee table

ID NAME salary

102 SHYAM 80000

104 Anmol 90000
103 RAM 70000
105 PUJA 60000

o Suppose, we want to add 20,000 to the salary of each employee specified in the
table. Then, we have to write the following query in the SQL:

SELECT Salary + 20000 as Emp_New_Salary FROM Employee;

2. SQL Subtraction Operator (-):The Subtraction Operator in SQL performs the

subtraction on the numerical data of the database table. In SQL, we can easily
subtract the numerical values of two columns of the same table by specifying
both the column names as the first and second operand. We can also subtract
the number from the existing number of the specific table column.

EXAMPLE: LET’S consider following employee table

ID NAME salary
102 SHYAM 80000
104 Anmol 90000
103 RAM 70000
105 PUJA 60000

o Suppose, we want to SUBTRACT 5000 FROM the salary of each employee

specified in the table. Then, we have to write the following query in the SQL:

SELECT Salary -5000 as Emp_New_Salary FROM Employee;

M. U. MUN DMS
3. SQL Multiplication Operator (*):The Multiplication Operator in SQL performs
the Multiplication on the numerical data of the database table. In SQL, we can
easily multiply the numerical values of two columns of the same table by
specifying both the column names as the first and second operand.

EXAMPLE: LET’S consider following employee table

ID NAME salary
102 SHYAM 80000
104 Anmol 90000
103 RAM 70000
105 PUJA 60000

o Suppose, we want to double the salary of each employee specified in the table.
Then, we have to write the following query in the SQL:

SELECT Salary *2 as Emp_New_Salary FROM Employee;

4. SQL Division Operator (/):The Division Operator in SQL divides the operand on
the left side by the operand on the right side.

EXAMPLE: LET’S consider following employee table

ID NAME salary
102 SHYAM 80000

104 Anmol 90000

103 RAM 70000
105 PUJA 60000

o Suppose, we want to half the salary of each employee specified in the table. Then,
we have to write the following query in the SQL:

M. U. MUN DMS
 SELECT Salary *2 as Emp_New_Salary FROM Employee;

5. SQL Modulus Operator (%):The Modulus Operator in SQL provides the

remainder when the operand on the left side is divided by the operand on the
right side.

EXAMPLE: LET’S consider following employee table

ID NAME salary
102 SHYAM 80000
104 Anmol 90000
103 RAM 70000
105 PUJA 60000

o Suppose, we want to get the remainder by dividing the VALUE of SALARY column
by the value of ID operand column, Then, we have to write the following query in
the SQL:
SELECT salary%ID as reminder FROM Employee;

2. SQL Comparison Operators

 The Comparison Operators in SQL compare two different data of SQL table and
check whether they are the same, greater, and lesser. The SQL comparison
operators are used with the WHERE clause in the SQL queries
 Following are the various comparison operators which are performed on
the data stored in the SQL database tables:

1. SQL Equal Operator (=)

2. SQL Not Equal Operator (!=)

M. U. MUN DMS
 3. SQL Greater Than Operator (>)
4. SQL Greater Than Equals to Operator (>=)
5. SQL Less Than Operator (<)\
6. SQL Less Than Equals to Operator (<=)

 EXAMPLE: LET’S consider following employee table

ID NAME salary
102 SHYAM 80000
104 Anmol 90000
103 RAM 70000
105 PUJA 60000
106 jay 80000

1. SQL Equal Operator (=)

o This operator is highly used in SQL queries. The Equal Operator in SQL shows
only data that matches the specified value in the query.
o This operator returns TRUE records from the database table if the value of both
operands specified in the query is matched.
o Suppose, we want to access all the records of those employees from
the Employee table whose salary is 80000. Then, we have to write the following
query in the SQL database:

SELECT * FROM Employee WHERE Salary = 80000;

o In this example, we used the SQL equal operator with WHERE clause for getting
the records of those employees whose salary is 80000.

M. U. MUN DMS
2. SQL Equal Not Operator (!=)
o The Equal Not Operator in SQL shows only those data that do not match the
query's specified value.
o This operator returns those records or rows from the database views and tables
if the value of both operands specified in the query is not matched with each
other.
o Suppose, we want to access all the records of those employees from
the Employee table whose salary is not 45000. Then, we have to write the
following query in the SQL database:

SELECT * FROM Employee WHERE Salary != 45000;

o In this example, we used the SQL equal not operator with WHERE clause for
getting the records of those employees whose salary is not 45000.

3. SQL Greater Than Operator (>)

o The Greater Than Operator in SQL shows only those data which are greater
than the value of the right-hand operand.
o Suppose, we want to access all the records of those employees from
the Employee table whose employee id is greater than103. Then, we have to
write the following query in the SQL database:

SELECT * FROM Employee WHERE Id > 103;

o Here, SQL greater than operator displays the records of those employees from
the above table whose Employee Id is greater than 103.

4. SQL Greater Than Equals to Operator (>=)

o The Greater Than Equals to Operator in SQL shows those data from the table
which are greater than and equal to the value of the right-hand operand.
o Suppose, we want to access all the records of those employees from
the Employee table whose employee id is greater than and equals to 103. For
this, we have to write the following query in the SQL database:

SELECT * FROM Employee WHERE Id >= 103;

o Here,'SQL greater than equals to operator' with WHERE clause displays the
rows of those employees from the table whose Employee Id is greater than and
equals to 103.

M. U. MUN DMS
5. SQL Less Than Operator (<)
o The Less Than Operator in SQL shows only those data from the database tables
which are less than the value of the right-side operand.
o This comparison operator checks that the left side operand is lesser than the
right side operand. If the condition becomes true, then this operator in SQL
displays the data which is less than the value of the right-side operand.
o Suppose, we want to access all the records of those employees from
the Employee table whose employee id is less than 104. For this, we have to
write the following query in the SQL database:

SELECT * FROM Employee WHERE Id < 104;

o Here,SQL less than operator with WHERE clause displays the records of those
employees from the above table whose Employee Id is less than 104.

6. SQL Less Than Equals to Operator (<=)

o The Less Than Equals to Operator in SQL shows those data from the table
which are lesser and equal to the value of the right-side operand.
o This comparison operator checks that the left side operand is lesser and equal to
the right side operand.
o Suppose, we want to access all the records of those employees from
the Employee table whose employee id is less and equals 103. For this, we have
to write the following query in the SQL database:

SELECT * FROM Employee WHERE Id <= 103;

o Here, SQL less than equals to the operator with WHERE clause displays the
rows of those employees from the table whose Employee Id is less than and
equals 202.

3. SQL Logical Operators

o The Logical Operators in SQL perform the Boolean operations, which give two
results True and False. These operators provide True value if both operands
match the logical condition.
o Following are the various logical operators which are performed on the
data stored in the SQL database tables:

o SQL AND operator

M. U. MUN DMS
 o SQL OR operator
o SQL NOT operator
o SQL BETWEEN operator
o SQL IN operator
o SQL LIKE operator
o Lets Consider Following Employee_details Table For Logical Operations.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi
202 Ankit 45000 Chandigarh
203 Bheem 30000 Delhi
204 Ram 25000 Delhi
205 Sumit 40000 Kolkata

1. SQL AND Operator

o The AND operator in SQL would show the record from the database table if all
the conditions separated by the AND operator evaluated to True. It is also known
as the conjunctive operator and is used with the WHERE clause.
o Syntax of AND operator:

SELECT *FROM table_Name WHERE condition1 AND condition2;

o Suppose, we want to access all the records of those employees from
the Employee_details table whose salary is 25000 and the city is Delhi. For this,
we have to write the following query in SQL:
SELECT * FROM Employee_details WHERE Emp_Salary = 25000 AND Emp_City =
'Delhi';

o Here, SQL AND operator with WHERE clause shows the record of employees
whose salary is 25000 and the city is Delhi.

2. SQL OR Operator
o The OR operator in SQL shows the record from the table if any of the conditions
separated by the OR operator evaluates to True. It is also known as the
conjunctive operator and is used with the WHERE clause.

M. U. MUN DMS
o Syntax of OR operator:

SELECT *FROM table_Name WHERE condition1 OR condition2;

o Suppose, we want to access all the records of those employees from
the Employee_details table whose salary is 25000 OR the city is Delhi. For this,
we have to write the following query in SQL:
SELECT * FROM Employee_details WHERE Emp_Salary = 25000 OR Emp_City = '
Delhi';

o Here, SQL AND operator with WHERE clause shows the record of employees
whose salary is 25000 or the city is Delhi.

3. SQL NOT Operator

o The NOT operator in SQL shows the record from the table if the condition
evaluates to false. It is always used with the WHERE clause.
o Syntax of NOT operator:

SELECT * FROM table_Name WHERE NOT condition;

o Suppose, we want to show all the information of those employees from
the Employee_details table whose City is not Delhi. For this, we have to
write the following query in SQL:

SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' ;

o In this example, we used the SQL NOT operator with the Emp_City
column.
4. SQL BETWEEN Operator(RANGE SEARCHING OPRATOR)
o The BETWEEN operator in SQL shows the record within the range
mentioned in the SQL query. This operator operates on the numbers,
characters, and date/time operands.
o If there is no value in the given range, then this operator shows NULL
value.
o Syntax of BETWEEN operator:

SELECT *FROM table_Name WHERE column_name

BETWEEN value1 and value2;

M. U. MUN DMS
o Suppose, we want to access all the information of those employees from
the Employee_details table who is having salaries between 20000 and 40000.
For this, we have to write the following query in SQL:

SELECT * FROM Employee_details WHERE Emp_Salary BETWEEN 30000 AND 45

000;

o Here, we used the SQL BETWEEN operator with the Emp_Salary field.

5. SQL IN Operator
 The IN operator in SQL allows database users to specify two or more
values in a WHERE clause. This logical operator minimizes the
requirement of multiple OR conditions.
 This operator makes the query easier to learn and understand. This
operator returns those rows whose values match with any value of the
given list.
 Syntax of IN operator:

SELECT * FROM table_Name WHERE column_name IN (list_of_values);

 Suppose, we want to show all the information of those employees from
the Employee_details table whose Employee Id is 202, 204, and 205.
For this, we have to write the following query in SQL:

SELECT * FROM Employee_details WHERE Emp_Id IN (202, 204, 205);

 Here, we used the SQL IN operator with the Emp_Id column.

6. SQL LIKE Operator(pattern matching operator)

 The LIKE operator in SQL shows those records from the table which
match with the given pattern specified in the sub-query.
 The percentage (%) and underscore (_) sign are used in conjunction with
this logical operator.
 Syntax of LIKE operator:

SELECT * FROM table_Name WHERE column_name LIKE pattern;

M. U. MUN DMS
  If we want to show all the information of those employees from
the Employee_details whose name starts with ''s''. For this, we have to
write the following query in SQL:

SELECT * FROM Employee_details WHERE Emp_Name LIKE 's%' ;

 If we want to show all the information of those employees from
the Employee_detailswhose name ends with ''y''. For this, we have to
write the following query in SQL:

SELECT * FROM Employee_details WHERE Emp_Name LIKE '%y' ;

 If we want to show all the information of those employees from
the Employee_detailswhose name starts with ''S'' and ends with ''y''. For
this, we have to write the following query in SQL:

SELECT * FROM Employee_details WHERE Emp_Name LIKE 'S%y' ;

 If we want to show all the information of those employees from

the Employee_details whose name contain “m” as a third character. For
this, we have to write the following query in SQL:

SELECT * FROM Employee_details WHERE Emp_Name LIKE '_ _ m%' ;

4. SQL Set Operators

 The Set Operators in SQL combine a similar type of data from two or more
SQL database tables. It mixes the result, which is extracted from two or more
SQL queries, into a single result.
 Set operators combine more than one select statement in a single query and
return a specific result set.
 Following are the various set operators which are performed on the
similar data stored in the two SQL database tables:

M. U. MUN DMS
 1. SQL Union Operator
2. SQL Union ALL Operator
3. SQL Intersect Operator
4. SQL Minus Operator

 Let’s Consider Following two tables Emp1 and Emp2 set Operations.

Id Name Salary
201 Sanjay 25000
202 Ajay 45000
203 Saket 30000
Table: Emp1
Id Name Salary
203 Saket 30000
204 Saurabh 40000
205 Ram 30000
201 Sanjay 25000
Table: Emp2

1. SQL Union Operator

 The SQL Union Operator combines the result of two or more SELECT
statements and provides the single output.
 The data type and the number of columns must be the same for each
SELECT statement used with the UNION operator. This operator does not
show the duplicate records in the output table.

SELECT ID, Name FROM Emp1

UNION
SELECT ID, Name FROM Emp2 ;

 Above query will display the employee name and employee id from both
tables in a single output and exclude duplicate values.

M. U. MUN DMS
 Id Name
201 Sanjay
202 Ajay
203 Saket
204 Saurabh
205 Ram

2. SQL Union ALL Operator

 The SQL Union Operator is the same as the UNION operator, but the only
difference is that it include duplicate values..

SELECT ID, Name FROM Emp1

UNION ALL
SELECT ID, Name FROM Emp2 ;

 Above query will display the employee name and employee id from both
tables in a single output and include duplicate values.

Id Name
201 Sanjay
202 Ajay
203 Saket
203 Saket
204 Saurabh
205 Ram
201 Sanjay

3. SQL Intersect Operator

 The SQL Intersect Operator shows the common record from two or more
SELECT statements. The data type and the number of columns must be the
same for each SELECT statement used with the INTERSECT operator.

M. U. MUN DMS
 SELECT ID, Name FROM Emp1
INTERSECT
SELECT ID, Name FROM Emp2 ;

Id Name
201 Sanjay
203 Saket

4. SQL Minus Operator

 The SQL Minus Operator combines the result of two or more SELECT
statements and shows only those rows which are present in the first
Table and not present in the second Table.

SELECT ID, Name FROM Emp1

MINUS
SELECT ID, Name FROM Emp2 ;

Id Name
202 Ajay

M. U. MUN DMS