Unit 3

SQL (Structured Query Language) is used to perform operations on the records stored in the
database, such as updating records, inserting records, deleting records, creating and
modifying database tables, views, etc. SQL is not a database system, but it is a query
language.

Why SQL?

Following are the reasons which explain why it is widely used:

o The basic use of SQL for data professionals and SQL users is to insert, update, and
delete the data from the relational database.
o SQL allows the data professionals and users to retrieve the data from the relational
database management systems.
o It also helps them to describe the structured data.
o It allows SQL users to create, drop, and manipulate the database and its tables.
o It also helps in creating the view, stored procedure, and functions in the relational
database.
o It allows you to define the data and modify that stored data in the relational database.
o It also allows SQL users to set the permissions or constraints on table columns, views,
and stored procedures.
Advantages of SQL:

1. Faster Query Processing –

Large amount of data is retrieved quickly and efficiently. Operations like
Insertion, deletion, manipulation of data is also done in almost no time.

2. No Coding Skills –
For data retrieval, large number of lines of code is not required. All basic
keywords such as SELECT, INSERT INTO, UPDATE, etc are used and also the
syntactical rules are not complex in SQL, which makes it a user-friendly
language.

3. Standardized Language –
Due to documentation and long establishment over years, it provides a uniform
platform worldwide to all its users.

4. Portable –
It can be used in programs in PCs, server, laptops independent of any platform
(Operating System, etc). Also, it can be embedded with other applications as per
need/requirement/use.
 5. Interactive Language –
Easy to learn and understand, answers to complex queries can be received in
seconds.

Disadvantages of SQL :
Although SQL has many advantages, still there are a few disadvantages.
Various Disadvantages of SQL are as follows:
1. Complex Interface –
SQL has a difficult interface that makes few users uncomfortable while dealing
with the database.

2. Cost –
Some versions are costly and hence, programmers cannot access it.

3. Partial Control –
Due to hidden business rules, complete control is not given to the database.

Applications of SQL:

 SQL is used by developers and DBAs (Database Administrators) in writing Data

Integration Scripts.

 It is used to deal with analytical queries to analyze the data and get instincts
from it.

 Retrieving Information

 Modification/Manipulation of data and database table such as Insertion, Deletion

and Updation.

SQL Features

 Client-server execution and remote database access – SQL commands can

control how a client application is allowed to access the database remotely.
 Security and authentication – SQL can make sure that only specific details of
the database are visible to the user while the entire database is secured by DBMS.
 Embedded SQL – SQL can embed languages like COBOL, C, Java, etc. to
query at runtime
 Transaction Control Language – TCL is used to control the transactions with
commands.
 Data Definition Language (DDL) – DDL contains commands that define data.
 Data Manipulation Language (DML): DML contains commands that
manipulate data
Characteristics of SQL

 High Performance
SQL provides high-performance programming capability for highly transactional,
heavy workload, and high usage database systems. SQL programming gives
various ways to describe the data more analytically.

 High Availability
SQL is compatible with databases like MS Access, Microsoft SQL Server,
MySQL, Oracle Database, SAP HANA, SAP Adaptive Server, etc. All of
these relational database management systems support SQL and it is easy to
create an application extension for procedural programming and various
other SQL functions which are additional features thus converting SQL into a
powerful tool.

 Scalability and Flexibility

SQL provides Scalability and Flexibility. It is very easy to create new tables and
previously created or not used tables can be dropped or deleted in a database.

 Robust Transactional Support

SQL programming can handle large records and manage numerous transactions.

 High Security
It is very easy to provide permissions on tables, procedures, and views,
hence, SQL gives security to your data.

 Comprehensive Application Development

SQL is used by many programmers to program apps to access a database. No
matter what the size of an organization, SQL works for every small or large
organization.

 Management Ease
SQL is used in almost every Relational Database Management System. “Select“,
“Create”, “Insert”, “Drop”, “Update”, and “Delete” are the standard and
 common SQL commands that help us to manage large amounts of data from a
database very quickly and efficiently.

 Open Source
SQL is an open-source programming language for building relational database
management system
SQL Commands
o SQL commands are instructions. It is used to communicate with the database. It is
also used to perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the table,
modify the table, set permission for users.
These SQL commands are mainly categorized into four categories as:
1. DDL – Data Definition Language
2. DQl – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5. TCL -- Transaction Control Language

1 DDL (Data Definition Language):

DDL or Data Definition Language actually consists of the SQL commands that can be used
to define the database schema. It simply deals with descriptions of the database schema and
is used to create and modify the structure of database objects in the database. DDL is a set
of SQL commands used to create, modify, and delete database structures but not data.
These commands are normally not used by a general user, who should be accessing the
database via an application.
List of DDL commands:
 CREATE: This command is used to create the database or its objects (like table,
index, function, views, store procedure, and triggers).
 DROP: This command is used to delete objects from the database.
 ALTER: This is used to alter the structure of the database.
 TRUNCATE: This is used to remove all records from a table, including all
spaces allocated for the records are removed.
 COMMENT: This is used to add comments to the data dictionary.
 RENAME: This is used to rename an object existing in the database.

2 DQL (Data Query Language):

DQL statements are used for performing queries on the data within schema objects. The
purpose of the DQL Command is to get some schema relation based on the query passed to
it. We can define DQL as follows it is a component of SQL statement that allows getting
data from the database and imposing order upon it. It includes the SELECT statement. This
command allows getting the data out of the database to perform operations with it. When a
SELECT is fired against a table or tables the result is compiled into a further temporary
table, which is displayed or perhaps received by the program i.e. a front-end.
List of DQL:
 SELECT: It is used to retrieve data from the database.
3 DML (Data Manipulation Language):
The SQL commands that deals with the manipulation of data present in the database belong
to DML or Data Manipulation Language and this includes most of the SQL statements. It is
the component of the SQL statement that controls access to data and to the database.
Basically, DCL statements are grouped with DML statements.
List of DML commands:
 INSERT : It is used to insert data into a table.
 UPDATE: It is used to update existing data within a table.
 DELETE : It is used to delete records from a database table.
 LOCK: Table control concurrency.
 CALL: Call a PL/SQL or JAVA subprogram.
 EXPLAIN PLAN: It describes the access path to data.

4 DCL (Data Control Language):

DCL includes commands such as GRAzNT and REVOKE which mainly deal with the
rights, permissions, and other controls of the database system.
List of DCL commands:
 GRANT: This command gives users access privileges to the database.
 REVOKE: This command withdraws the user’s access privileges given by
using the GRANT command.

5 TCL (Transaction Control Language):

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE
only. These operations are automatically committed in the database that's why they cannot be
used while creating tables or dropping them.
List of DML commands:
 COMMIT: Commits a Transaction.
 ROLLBACK: Rollbacks a transaction in case of any error occurs.
 SAVEPOINT: Sets a save-point within a transaction.
 SET TRANSACTION: Specify characteristics for the transaction.

SQL Data Types

SQL Data Type is an attribute that specifies the type of data of any object. Each column,
variable and expression has a related data type in SQL.

Data types are used to represent the nature of the data that can be stored in the database table.
For example, in a particular column of a table, if we want to store a string type of data then
we will have to declare a string data type of this column.

Data types mainly classified into three categories for every database.
o String Data types
o Numeric Data types
o Date and time Data types
Data Types in MySQL,

1. MySQL String Data Types

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.

BINARY(Size) It is equal to CHAR() but stores binary byte strings. Its size
parameter specifies the column length in the bytes. Default is 1.

VARBINARY(Size) It is equal to VARCHAR() but stores binary byte strings. Its size
parameter specifies the maximum column length in bytes.

TEXT(Size) It holds a string that can contain a maximum length of 255

characters.

TINYTEXT It holds a string with a maximum length of 255 characters.

MEDIUMTEXT It holds a string with a maximum length of 16,777,215.

LONGTEXT It holds a string with a maximum length of 4,294,967,295

characters.

ENUM(val1, val2, It is used when a string object having only one value, chosen
val3,...) from a list of possible values. It contains 65535 values in an
ENUM list. If you insert a value that is not in the list, a blank
value will be inserted.

SET( val1,val2,val3, It is used to specify a string that can have 0 or more values,
....) chosen from a list of possible values. You can list up to 64
values at one time in a SET list.

BLOB(size) It is used for BLOBs (Binary Large Objects). It can hold up to

65,535 bytes.

2. MySQL Numeric Data Types

BIT(Size) It is used for a bit-value type. The number of bits per value is
specified in size. Its size can be 1 to 64. The default value is 1.

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.
INTEGER(size) It is equal to INT(size).

FLOAT(size, d) It is used to specify a floating point number. Its size parameter

specifies the total number of digits. The number of digits after the
decimal point is specified by d parameter.

FLOAT(p) It is used to specify a floating point number. MySQL used p

parameter to determine whether to use FLOAT or DOUBLE. If p
is between 0 to24, the data type becomes FLOAT (). If p is from
25 to 53, the data type becomes DOUBLE().

DOUBLE(size, It is a normal size floating point number. Its size parameter

d) specifies the total number of digits. The number of digits after the
decimal is specified by d parameter.

DECIMAL(size, It is used to specify a fixed point number. Its size parameter

d) specifies the total number of digits. The number of digits after the
decimal parameter is specified by d parameter. The maximum
value for the size is 65, and the default value is 10. The maximum
value for d is 30, and the default value is 0.

DEC(size, d) It is equal to DECIMAL(size, d).

BOOL It is used to specify Boolean values true and false. Zero is

considered as false, and nonzero values are considered as true.

3. MySQL Date and Time Data Types

DATE It is used to specify date format YYYY-MM-DD. Its supported
range is from '1000-01-01' to '9999-12-31'.

DATETIME(fsp) It is used to specify date and time combination. Its format is

YYYY-MM-DD hh:mm:ss. Its supported range is from '1000-01-
01 00:00:00' to 9999-12-31 23:59:59'.

TIMESTAMP(fsp) It is used to specify the timestamp. Its value is stored as the number
of seconds since the Unix epoch('1970-01-01 00:00:00' UTC). Its
format is YYYY-MM-DD hh:mm:ss. Its supported range is from
'1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.

TIME(fsp) It is used to specify the time format. Its format is hh:mm:ss. Its
supported range is from '-838:59:59' to '838:59:59'

YEAR It is used to specify a year in four-digit format. Values allowed in

four digit format from 1901 to 2155, and 0000.
Misc Data Types

SQL Create Database

Syntax of Create Database statement in SQL
1. CREATE DATABASE Database_Name;

Following are the most important points which are required to learn while creating a
database:
o The database we want to create should be a simple and unique name, which can be
easily identified.
o Database name should be no more than 128 characters.
Syntax of Drop Database Statement in SQL
1. DROP DATABASE Database_Name;
Syntax of Rename Database in SQL
1. ALTER DATABASE old_database_name MODIFY NAME = new_database_name;
2. EXEC sp_renamedb'old_database_name' , 'new_database_name'
Syntax of Rename Database in MySQL
3. RENAME DATABASE old_database_name TO new_database_name;

SQL CREATE TABLE

SQL CREATE TABLE statement is used to create table in a database.
 create table "tablename" ("column1" "data type", "column2" "data type", "column3" "d
ata type", ... "columnN" "data type");

CREATE TABLE Employee

( EmployeeID int, FirstName varchar(255), LastName varchar(255),
Email varchar(255),
AddressLine varchar(255), City varchar(255) );

Create a Table using another table

We can create a copy of an existing table using the create table command. The new table gets
the same column signature as the old table. We can select all columns or some specific
columns.

If we create a new table using an old table, the new table will be filled with the existing value
from the old table.
CREATE TABLE table_name AS SELECT column1, column2,... FROM old_table_
name WHERE ..... ;
The following SQL creates a copy of the employee table.
CREATE TABLE EmployeeCopy AS SELECT EmployeeID, FirstName, Email
FROM Employee;

SQL SELECT Statement

The SELECT statement is the most commonly used command in Structured Query Language.
It is used to access the records from one or more database tables and views. It also retrieves
the selected data that follow the conditions we want. By using this command, we can also
access the particular record from the particular column of the table. The table which stores
the record returned by the SELECT statement is called a result-set table.
Syntax of SELECT Statement in SQL
SELECT Column_Name_1, Column_Name_2, ....., Column_Name_N FROM Table_Na
me;
Eg.
SELECT * FROM table_name;
SELECT * FROM Name_of_Table WHERE [condition];

SQL INSERT Statement

The SQL INSERT INTO Statement is used to add new rows of data to a table in the
database. There are two ways to insert data in a table:
1. By SQL insert into statement
o By specifying column names
 o Without specifying column names
Syntax;
INSERT INTO table_name
VALUES (value1, value2, value3....);

2. By SQL insert into select statement

Syntax:
INSERT INTO table_name
[(column1, column2, .... column)]
SELECT column1, column2, .... Column N
FROM table_name [WHERE condition];

SQL DROP TABLE

A SQL DROP TABLE statement is used to delete a table definition and all data from a table.

This is very important to know that once a table is deleted all the information available in the
table is lost forever, so we have to be very careful when using this command.

Eg: DROP TABLE "table_name";

SQL DELETE TABLE

The DELETE statement is used to delete rows from a table. If you want to remove a specific
row from a table you should use WHERE condition.

1. DELETE FROM table_name [WHERE condition];

But if you do not specify the WHERE condition it will remove all the rows from the table.

1. DELETE FROM table_name;

SQL TRUNCATE TABLE

A truncate SQL statement is used to remove all rows (complete data) from a table. It is
similar to the DELETE statement with no WHERE clause.

1. TRUNCATE TABLE table_name;

Syntax of RENAME statement in SQL

 1. RENAME old_table _name To new_table_name ;
o Suppose, you want to change the name of the above table into
the "Coding_Employees". For this, you have to type the following RENAME
statement in SQL:

RENAME Employee To Coding_Employees ;

SQL ALTER TABLE

The ALTER TABLE statement in Structured Query Language allows you to add, modify,
and delete columns of an existing table. This statement also allows database users to add and
remove various SQL constraints on the existing tables.

Syntax of ALTER TABLE ADD, Modify, Drop and Rename Column statement in SQL

 ALTER TABLE table_name ADD column_name column-definition;

 ALTER TABLE table_name MODIFY column_name column-definition;
 ALTER TABLE table_name DROP Column column_name ;
 ALTER TABLE table_name RENAME COLUMN old_name to new_name;

o Examples: Suppose, you want to add the new column Car_Model in the Car table.
For this, we have to type the following query in the SQL:

ALTER TABLE Cars ADD Car_Model Varchar(20);

o Suppose, you want to modify the datatype of the Car_Color column of the Car table.
For this, you have to type the following query in the SQL:

ALTER TABLE Cars ADD Car_Color Varchar(50);

SQL Operators

The SQL reserved words and characters are called operators, which are used with a WHERE
clause in a SQL query. In SQL, an operator can either be a unary or binary operator. The
unary operator uses only one operand for performing the unary operation, whereas the binary
operator uses two operands for performing the binary operation.
Syntax of SQL Operator
1. Operator SQL_Operand
2. Operand1 SQL_Operator Operand2

Types of Operator

SQL operators are categorized in the following categories:

 1. SQL Arithmetic Operators
2. SQL Comparison Operators
3. SQL Logical Operators
4. SQL Set Operators

1. SQL Arithmetic Operators

Assume 'variable a' holds 10 and 'variable b' holds 20, then −
Operator Description Example

Adds values on either side of the operator. a + b will

+ (Addition)
give 30

Subtracts right hand operand from left hand a - b will give

- (Subtraction)
operand. -10

* Multiplies values on either side of the operator. a * b will

(Multiplication) give 200

Divides left hand operand by right hand operand. b / a will give

/ (Division)
2

Divides left hand operand by right hand operand b % a will

% (Modulus)
and returns remainder. give 0

Syntax of SQL Addition Operator:

1. SELECT operand1 + operand2;
Let's understand the below example which explains how to execute different Operator
in SQL query:
This example consists of an Employee_details table, which has four columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_Monthlybonus, Penalty.

Emp Id Emp Name Emp Salary Emp Penalty

Monthlybonus

101 Tushar 25000 4000 200

102 Anuj 30000 200 500

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 Emp_Salary + 20000 as Emp_New_Salary FROM Employee_de
tails;
o Suppose, we want to add the Salary and monthly bonus columns of the above table,
then we have to write the following query in SQL:
SELECT Emp_Salary + Emp_Monthlybonus as Emp_Total_Salary FRO
M Employee_details;

o Suppose we want to subtract 5,000 from the salary of each employee given in
the Employee_details table. Then, we have to write the following query in the SQL:
SELECT Emp_Salary - 5000 as Emp_New_Salary FROM Employee_deta
ils;
o If we want to subtract the penalty from the salary of each employee, then we have to
write the following query in SQL:
SELECT Emp_Salary - Penalty as Emp_Total_Salary FROM Employee_
details;
o Suppose, we want to double the salary of each employee given in
the Employee_details table. Then, we have to write the following query in the SQL:
SELECT Emp_Salary * 2 as Emp_New_Salary FROM Employee_details;
o If we want to multiply the Emp_Id column to Emp_Salary column of that employee
whose Emp_Id is 202, then we have to write the following query in SQL:
SELECT Emp_Id * Emp_Salary as Emp_Id * Emp_Salary FROM Employee_de
tails WHERE Emp_Id = 202;
o Suppose, we want to half the salary of each employee given in the Employee_details
table. For this operation, we have to write the following query in the SQL:
SELECT Emp_Salary / 2 as Emp_New_Salary FROM Employee_details;
Modulus Operator in SQL query:
Example consists of a Division table, which has three columns Number,
First_operand, and Second_operand.
 o If we want to get the remainder by dividing the numbers of First_operand column by
the numbers of Second_operand column, then we have to write the following query in
SQL:
SELECT First_operand % Second_operand as Remainder FROM Employee_de
tails;

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.
Assume 'variable a' holds 10 and 'variable b' holds 20, then –

This E.g.
example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, and Emp_Salary.
 o Suppose, we want to access all the records of those employees from
the Employee_details table whose salary is 30000. Then, we have to write the
following query in the SQL database:
SELECT * FROM Employee_details WHERE Emp_Salary = 30000;
o Suppose, we want to access all the records of those employees from
the Employee_details table whose salary is not 45000. Then, we have to write the
following query in the SQL database:
SELECT * FROM Employee_details WHERE Emp_Salary != 45000;
o Suppose, we want to access all the records of those employees from
the Employee_details table whose employee id is greater than and equals to 202.
Then, we have to write the following query in the SQL database:
SELECT * FROM Employee_details WHERE Emp_Id >= 202;

3. SQL Logical Operators

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.

Following are the various logical operators which are performed on the data stored in
the SQL database tables:
1. SQL ALL operator
2. SQL AND operator
3. SQL OR operator
4. SQL BETWEEN operator
5. SQL IN operator
6. SQL NOT operator
7. SQL ANY operator
8. SQL LIKE operator
i). ALL Operator

The ALL operator in SQL compares the specified value to all the values of a column from the
sub-query in the SQL database.

This operator is always used with the following statement:

1. SELECT,
2. HAVING, and
3. WHERE
Syntax of ALL operator:
1. SELECT column_Name1, ...., column_NameN FROM table_Name WHERE column
Comparison_operator ALL (SELECT column FROM tablename2)

Eg: This example consists of an Employee_details table, which has three columns Emp_Id,
Emp_Name, Emp_Salary, and Emp_City.

o If we want to access the employee id and employee names of those employees from
the table whose salaries are greater than the salary of employees who lives in Jaipur
city, then we have to type the following query in SQL.
SELECT Emp_Id, Emp_Name FROM Employee_details WHERE Emp_Salary >A
LL ( SELECT Emp_Salary FROM Employee_details WHERE Emp_City = Jaipur)

ii). AND Operator

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.

Syntax of AND operator:

SELECT column1, ...., columnN FROM table_Name WHERE condition1 AND condition2 A
ND condition3 AND ....... AND conditionN;

E.g. queries:

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'

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

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.
o If 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 = 'D
elhi';
SQL BETWEEN Operator

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. If there is
no value in the given range, then this operator shows NULL value.
Syntax of BETWEEN operator:
SELECT column_Name1, column_Name2 ...., column_NameN FROM table_Name WHERE
column_nameBETWEEN value1 and value2;
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 A
ND 45000;
iv) 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 column_Name1, column_Name2 ...., column_NameN FROM table_Name WHER
E column_name IN (list_of_values);
o 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);
 o Suppose, we want to show all the information of those employees from
the Employee_details table whose Employee Id is not equal to 202 and 205. For this,
we have to write the following query in SQL:
SELECT * FROM Employee_details WHERE Emp_Id NOT IN (202,205);

v). NOT Operator

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.
Syntax of NOT operator:
SELECT column1, column2 ...., columnN FROM table_Name WHERE NOT conditi
on;
o Suppose, we want to show all the information of those employees from
the Employee_details table whose Cityis not Delhi. For this, we have to write the
following query in SQL:
SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' ;
o Suppose, we want to show all the information of those employees from
the Employee_details table whose Cityis not Delhi and Chandigarh. For this, we have
to write the following query in SQL:
SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' AND N
OT Emp_City = 'Chandigarh';

vi). SQL ANY Operator

ANY compares a value to each value in a list or results from a query and evaluates to true if
the result of an inner query contains at least one row.
 ANY return true if any of the subqueries values meet the condition.
 ANY must be preceded by comparison operators.
Syntax:
SELECT column1, column2 ...., columnN FROM table_Name WHERE column_nam
e comparison_operator ANY ( SELECT column_name FROM table_name WHERE c
ondition(s)) ;
Example:
Consider the following Products Table and OrderDetails Table,
 o Find the Distinct CategoryID of the products which have any record in OrderDetails
Table.
SELECT DISTINCT CategoryID FROM Products WHERE ProductID = ANY
(SELECT ProductID FROM OrderDetails);

o Finds any records in the OrderDetails table that Quantity = 9

SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT
ProductID FROM OrderDetails WHERE Quantity = 9);
o Find the name of the all the product.

SELECT ALL ProductName FROM Products WHERE TRUE;

o Find the name of the product if all the records in the OrderDetails has Quantity either
equal to 6 or 2.
SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT
ProductId FROM OrderDetails WHERE Quantity = 6 OR Quantity = 2);
vii). LIKE 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 (%) sign is a wildcard which is used in conjunction with this logical operator.

This operator is used in the WHERE clause with the following three statements:
1. SELECT statement
2. UPDATE statement
3. DELETE statement
Syntax of LIKE operator:
SELECT column_Name1, column_Name2 ...., column_NameN FROM table_Name WHERE
column_name LIKE pattern;
 o 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%' ;
o 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' ;
o 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' ;

4. 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:
1. SQL Union Operator
2. SQL Union ALL Operator
3. SQL Intersect Operator
4. SQL Minus Operator
i). 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.

Suppose we used two tables. Both tables have four columns Emp_Id, Emp_Name,
Emp_Salary, and Emp_City. Table: Employee_details1 and Table: Employee_details2.
o Suppose, we want to see the employee name and employee id of each employee from
both tables in a single output. For this, we have to write the following query in SQL:
SELECT Emp_ID, Emp_Name FROM Employee_details1
UNION
 SELECT Emp_ID, Emp_Name FROM Employee_details2 ;

ii). Union ALL Operator

The SQL Union Operator is the same as the UNION operator, but the only difference is that it
also shows the same record.
iii). 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.

o Suppose, we want to see a common record of the employee from both the tables in a
single output. For this, we have to write the following query in SQL:

SELECT Emp_Name FROM Employee_details1

INTERSECT
SELECT Emp_Name FROM Employee_details2 ;

iv). Minus Operator

The SQL Minus Operator combines the result of two or more SELECT statements and shows
only the results from the first data set.

o Suppose, we want to see the name of employees from the first result set after the
combination of both tables. For this, we have to write the following query in SQL:
SELECT Emp_Name FROM Employee_details1
MINUS
SELECT Emp_Name FROM Employee_details2 ;

Precedence of SQL Operator

The precedence of SQL operators is the sequence in which the SQL evaluates the different
operators in the same expression. Structured Query Language evaluates those operators first,
which have high precedence.
In the following table, the operators at the top have high precedence, and the operators
that appear at the bottom have low precedence.
For Example,
1. UPDATE employee
2. SET salary = 20 - 3 * 5 WHERE Emp_Id = 5;

In the above SQL example, salary is assigned 5, not 85, because the * (Multiplication)

Operator has higher precedence than the - (subtraction) operator, so it first gets multiplied
with 3*5 and then subtracts from 20.

Constraints in SQL
Constraints are the rules that we can apply on the type of data in a table. That is, we can
specify the limit on the type of data that can be stored in a particular column in a table
using constraints.
The available constraints in SQL are:

 NOT NULL: This constraint tells that we cannot store a null value in a column. That is,
if a column is specified as NOT NULL then we will not be able to store null in this
particular column any more.
Syntax:
CREATE TABLE TableName (ColumnName1 datatype NOT NULL, ColumnName2
datatype,…., ColumnNameN datatype);

 UNIQUE: This constraint when specified with a column, tells that all the values in the
column must be unique. That is, the values in any row of a column must not be
repeated.
Syntax:
CREATE TABLE TableName (ColumnName1 datatype UNIQUE, ColumnName2 da
tatype,…., ColumnNameN datatype);
 Syntax to apply the UNIQUE constraint on an existing table's column:
ALTER TABLE TableName ADD UNIQUE (ColumnName);

 PRIMARY KEY: A primary key is a field which can uniquely identify each row in a
table. And this constraint is used to specify a field in a table as primary key.
Syntax:
CREATE TABLE TableName (ColumnName1 datatype PRIMARY KEY, ColumnNa
me2 datatype,…., ColumnNameN datatype);
Syntax to apply the primary key constraint on an existing table's column:
ALTER TABLE TableName ADD PRIMARY KEY (ColumnName);

 FOREIGN KEY: A Foreign key is a field which can uniquely identify each row in a
another table. And this constraint is used to specify a field as Foreign key.
Syntax:
CREATE TABLE tablename(ColumnName1 Datatype(SIZE) PRIMARY KEY, Colu
mnNameN Datatype(SIZE), FOREIGN KEY( ColumnName ) REFERENCES PARE
NT_TABLE_NAME(Primary_Key_ColumnName));

 CHECK: This constraint helps to validate the values of a column to meet a particular
condition. That is, it helps to ensure that the value stored in a column meets a specific
condition.
Syntax:
CREATE TABLE TableName (ColumnName1 datatype CHECK (ColumnName1 Co
ndition), ColumnName2 datatype,…., ColumnNameN datatype);
Syntax to apply check constraint on multiple columns:
CREATE TABLE TableName (ColumnName1 datatype, ColumnName2 datatype CHECK (
ColumnName1 Condition AND ColumnName2 Condition),…., ColumnNameN datatype);

 DEFAULT: This constraint specifies a default value for the column when no value is
specified by the user.
Syntax:
CREATE TABLE TableName (ColumnName1 datatype DEFAULT Value, ColumnN
ame2 datatype,…., ColumnNameN datatype);
Examples:
1. Create a student table and apply a NOT NULL constraint on one of the table's
column while creating a table
CREATE TABLE student(StudentID INT NOT NULL, Student_FirstName VA
RCHAR(20), Student_LastName VARCHAR(20), Student_PhoneNumber VAR
CHAR(20), Student_Email_ID VARCHAR(40));
 2. Create a student table and apply a UNIQUE constraint on one of the table's column
while creating a table.
CREATE TABLE student(StudentID INT UNIQUE, Student_FirstName VARC
HAR(20), Student_LastName VARCHAR(20), Student_PhoneNumber VARCHA
R(20), Student_Email_ID VARCHAR(40));

3. Create a student table and apply the PRIMARY KEY constraint while creating a
table.
CREATE TABLE student(StudentID INT PRIMARY KEY, Student_FirstName V
ARCHAR(20), Student_LastName VARCHAR(20), Student_PhoneNumber VARC
HAR(20), Student_Email_ID VARCHAR(40));

4. Create an employee table and apply the FOREIGN KEY constraint while creating a
table.To create a foreign key on any table, first, we need to create a primary key on a
table.

CREATE TABLE employee (Emp_ID INT NOT NULL PRIMARY KEY, Emp_N
ame VARCHAR (40), Emp_Salary VARCHAR (40));

5. Create a student table and apply CHECK constraint to check for the age less than or
equal to 15 while creating a table.
CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Stu
dent_LastName VARCHAR(20), Student_PhoneNumber VARCHAR(20), Student_
Email_ID VARCHAR(40), Age INT CHECK( Age <= 15));

6. Create a student table and apply the default constraint while creating a table.

CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), St

udent_LastName VARCHAR(20), Student_PhoneNumber VARCHAR(20), Student
_Email_ID VARCHAR(40) DEFAULT "anuja.k8@gmail.com");

SQL Aggregate Functions

o SQL aggregation function is used to perform the calculations on multiple rows of a
single column of a table. It returns a single value.
o It is also used to summarize the data.

Various Aggregate Functions

1) Count()
2) Sum()
3) Avg()
 4) Min()
5) Max()

Now let us understand each Aggregate function with a example:

Id Name Salary
-----------------------
1 A 80
2 B 40
3 C 60
4 D 70
5 E 60
6 F Null
Count():
Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct Non Null values over the column
salary .i.e 4
Sum():
sum(salary): Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.

Avg():
Avg(salary) = Sum(salary) / count(salary) = 310/5
Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4

Min():
Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.
Views in SQL
o Views in SQL are considered as a virtual table. A view also contains rows and
columns.

Views in SQL are kind of virtual tables. A view also has rows and columns as they are
in a real table in the database. We can create a view by selecting fields from one or
more tables present in the database. A View can either have all the rows of a table or
specific rows based on certain condition.
Sample table for example:
1. Creating view

A view can be created using the CREATE VIEW statement. We can create a view from a
single table or multiple tables.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

2. Creating View from a single table

CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4;
Just like table query, we can query the view to view the data.
SELECT * FROM DetailsView

3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the SELECT
statement.
In the given example, a view is created named MarksView from two tables Student_Detail
and Student_Marks.
Query:
CREATE VIEW MarksView AS
 SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Mark
WHERE Student_Detail.NAME = Student_Marks.NAME;
To display data of View MarksView:
SELECT * FROM MarksView;

4. Deleting View: A view can be deleted using the Drop View statement.
Syntax
1. DROP VIEW view_name;
Example: If we want to delete the View MarksView, we can do this as:
DROP VIEW MarksView;

SQL INDEX

The Index in SQL is a special table used to speed up the searching of the data in the database
tables. It also retrieves a vast amount of data from the tables frequently. The INDEX requires
its own space in the hard disk. The index concept in SQL is same as the index concept in the
novel or a book.

It is the best SQL technique for improving the performance of queries. The drawback of
using indexes is that they slow down the execution time of UPDATE and INSERT
statements. But they have one advantage also as they speed up the execution time of SELECT
and WHERE statements.

o Each Index table contains only two columns. The first column is row_id, and the other
is indexed-column.
o When indexes are used with smaller tables, the performance of the index may not be
recognized.

Create an INDEX

In SQL, we can easily create the Index using the following CREATE Statement:

Syntax:
CREATE INDEX index ON TABLE column;
where the index is the name given to that index and TABLE is the name of the table on
which that index is created and column is the name of that column for which it is applied.

For multiple columns:

Syntax:
CREATE INDEX index ON TABLE (column1, column2,.....)
Unique Indexes:
Unique indexes are used for the maintenance of the integrity of the data present in the table
as well as for the fast performance, it does not allow multiple values to enter into the table.
Syntax:
CREATE UNIQUE INDEX index ON TABLE column;
Removing an Index:
To remove an index from the data dictionary by using the DROP INDEX command.
Syntax:
DROP INDEX index;

Altering an Index:
To modify an existing table’s index by rebuilding, or reorganizing the index.
Syntax:
ALTER INDEX IndexName ON TableName REBUILD;
Confirming Indexes :
You can check the different indexes present in a particular table given by the user or the
server itself and their uniqueness.
Syntax:
select * from USER_INDEXES;
It will show you all the indexes present in the server, in which you can locate your own
tables too.

Renaming an index :
You can use the system stored procedure sp_rename to rename any index in the database.
Syntax:
EXEC sp_rename index_name, new_index_name, N'INDEX';
 Example for creating an Index in SQL:
Let's take an Employee table:

What is the SQL query creates an Index 'Index_state' on the Emp_State column of
the Employee table.
CREATE INDEX index_state ON Employee (Emp_State);

Suppose we want to create an index on the combination of the Emp_city and

the Emp_State column of the above Employee table. For this, we have to use the following
query:
CREATE INDEX index_city_State ON Employee (Emp_City, Emp_State);

What is SQL query creates the unique index index_salary on the Emp_Salary column of
the Employee table.
CREATE UNIQUE INDEX index_salary ON Employee (Emp_Salary);

What is SQL query renames the index 'index_Salary' to 'index_Employee_Salary' of the

above Employee table.
ALTER INDEX index_Salary RENAME TO index_Employee_Salary;

Suppose we want to remove the above 'index_Salary' from the SQL database. For this, we
have to use the following SQL query:
DROP INDEX index_salary;

When should INDEXES not be used in SQL?

The Indexes should not be used in SQL in the foll65owing cases or situations:
o SQL Indexes can be avoided when the size of the table is small.
o When the table needs to be updated frequently.
o Indexed should not be used on those cases when the column of a table contains a large
number of NULL values.
SQL Sub Query
A Subquery is a query within another SQL query and embedded within the WHERE clause.
Important Rule:

o A subquery can be placed in a number of SQL clauses like WHERE clause, FROM
clause, HAVING clause.
o You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements
along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
o A subquery is a query within another query. The outer query is known as the main
query, and the inner query is known as a subquery.
o Subqueries are on the right side of the comparison operator.
o A subquery is enclosed in parentheses.
o In the Subquery, ORDER BY command cannot be used. But GROUP BY command
can be used to perform the same function as ORDER BY command.

1. Subqueries with the Select Statement

SELECT column_name FROM table_name WHERE column_name expression operator ( S

ELECT column_name from table_name WHERE ... );

2. Subqueries with the INSERT Statement

o SQL subquery can also be used with the Insert statement. In the insert statement, data
returned from the subquery is used to insert into another table.
o In the subquery, the selected data can be modified with any of the character, date
functions.

Syntax:
INSERT INTO table_name (column1, column2, column3....) SELECT * FROM table_nam
e WHERE VALUE OPERATOR

3. Subqueries with the UPDATE Statement

The subquery of SQL can be used in conjunction with the Update statement. When a
subquery is used with the Update statement, then either single or multiple columns in a table
can be updated.
Syntax
UPDATE table SET column_name = new_value WHERE VALUE OPERATOR (SELEC
T COLUMN_NAME FROM TABLE_NAME WHERE condition);

4. Subqueries with the DELETE Statement

The subquery of SQL can be used in conjunction with the Delete statement just like any other
statements mentioned above.

Syntax
DELETE FROM TABLE_NAME WHERE VALUE OPERATOR SELECT COLUMN_N
AME FROM TABLE_NAME WHERE condition);

Examples

Consider the EMPLOYEE table have the following records:

 The subquery with a SELECT statement will be:

SELECT * FROM EMPLOYEE WHERE ID IN (SELECT ID FROM EMPLOYEE W

HERE SALARY > 4500);

 Suppose we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE

table. The below example updates the SALARY by .25 times in the EMPLOYEE table
for all employee whose AGE is greater than or equal to 29.
UPDATE EMPLOYEE SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT A
GE FROM CUSTOMERS_BKP WHERE AGE >= 29);

 Assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE

table. The below example deletes the records from the EMPLOYEE table for all
EMPLOYEE whose AGE is greater than or equal to 29.

DELETE FROM EMPLOYEE WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BK

P
WHERE AGE >= 29 );