UNIT-2: Basics of SQL

Introduction
SQL fully abbreviated as Structured Query Language can be defined as a domain-
specific language used to manage the relational databases and performs different operations
on the data stored in them. SQL is used as their standard database language by all the
relational database management systems like Oracle, Informix, Posgres, SQL server, MySQL,
MS Access, and Sybase.

Characteristics of SQL
 SQL is easy to learn.
 SQL is used to access data from relational database management systems.
 SQL can execute queries against the database.
 SQL is used to describe the data.
 SQL is used to define the data in the database and manipulate it when needed.
 SQL is used to create and drop the database and table.
 SQL is used to create a view, stored procedure, function in a database.
 SQL allows users to set permissions on tables, procedures, and views.

Advantages of SQL: There are the following advantages of SQL:

 High speed: Using the SQL queries, the user can quickly and efficiently retrieve a large
amount of records from a database.
 No coding needed: In the standard SQL, it is very easy to manage the database system. It
doesn't require a substantial amount of code to manage the database system.
 Well defined standards: Long established are used by the SQL databases that are being
used by ISO and ANSI.
 Portability: SQL can be used in laptop, PCs, server and even some mobile phones.
 Interactive language: SQL is a domain language used to communicate with the database.
It is also used to receive answers to the complex questions in seconds.
 Multiple data view: Using the SQL language, the users can make different views of the
database structure.

SQL DATA TYPE

SQL Data type is used to define the values that a column can contain. Every column is required
to have a name and data type in the database table. Different data types in SQL Are as follow.
1. Binary data types
2. Exact Numeric Data type
3. Approximate Numeric Data type
4. Character String Data type
5. Date and time Data type

1. Binary Data types: There are Three types of binary Datatypes which are given below
 Data Type Description
Binary It has a maximum length of 8000 bytes. It contains fixed-length binary data.
varbinary It has a maximum length of 8000 bytes. It contains variable-length binary data.
Image It has a maximum length of 2,147,483,647 bytes.

2. Approximate Numeric Data type: The subtypes are given below:

Data type From To Description

float -1.79E + 308 1.79E + 308 It is used to specify a floating-point value e.g. 6.2, 2.9 etc.
real -3.40e + 38 3.40E + 38 It specifies a single precision floating point number

3. Exact Numeric Data type: The subtypes are given below:

Data type Description

int It is used to specify an integer value.
smallint It is used to specify small integer value.
bit It has the number of bits to store.
decimal It specifies a numeric value that can have a decimal number.
numeric It is used to specify a numeric value.

4. Character String Data type: The subtypes are given below:

Data
Description
type
It has a maximum length of 8000 characters. It contains Fixed-length non-unicode
char
characters.
It has a maximum length of 8000 characters. It contains variable-length non-
varchar
unicode characters.
It has a maximum length of 2,147,483,647 characters. It contains variable-length
text
non-unicode characters.

5. Date and time Data type: The subtypes are given below:

Data type Description

date It is used to store the year, month, and days value.
time It is used to store the hour, minute, and second values.
timestamp It stores the year, month, day, hour, minute, and the second value.

Prof. Dnyandeo Khemnar Page 2

 MySQL Data Types: A Data Type specifies a particular type of data, like integer, floating
points, Boolean etc. It also identifies the possible values for that type, the operations that can
be performed on that type and the way the values of that type are stored. MySQL supports a
lot number of SQL standard data types in various categories. It uses many different data types
broken into mainly three categories: numeric, date and time, and string types.

Numeric Data Type

Data Type Description

A normal-sized integer that can be signed or unsigned. If signed, the allowable
INT range is from -2147483648 to 2147483647. If unsigned, the allowable range
is from 0 to 4294967295. You can specify a width of up to 11 digits.
A very small integer that can be signed or unsigned. If signed, the allowable
TINYINT range is from -128 to 127. If unsigned, the allowable range is from 0 to 255.
You can specify a width of up to 4 digits.
A small integer that can be signed or unsigned. If signed, the allowable range
SMALLINT is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535.
You can specify a width of up to 5 digits.
A medium-sized integer that can be signed or unsigned. If signed, the
MEDIUMINT allowable range is from -8388608 to 8388607. If unsigned, the allowable
range is from 0 to 16777215. You can specify a width of up to 9 digits.
A large integer that can be signed or unsigned. If signed, the allowable range is
BIGINT from -9223372036854775808 to 9223372036854775807. If unsigned, the
allowable range is from 0 to 18446744073709551615. width up to 20 digits.
A floating-point number that cannot be unsigned. You can define the display
FLOAT(m,d) length (m) and the number of decimals (d). This is not required and will
default to 10,2, where 2 is the number of decimals and 10 is the total number

Prof. Dnyandeo Khemnar Page 3

 of digits. Decimal precision can go to 24 places for a float.
A double precision floating-point number that cannot be unsigned. You can
define the display length (m) and the number of decimals (d). This is not
DOUBLE(m,d)
required and will default to 16,4, where 4 is the number of decimals. Decimal
precision can go to 53 places for a double. Real is a synonym for double.
An unpacked floating-point number that cannot be unsigned. In unpacked
DECIMAL(m,d) decimals, each decimal corresponds to one byte. Defining the display length
(m) and the number of decimals (d) is required.

Date and Time Data Type:

Data Type Maximum Size Explanation

DATE Range from '1000-01-01' to '9999-12-31'. Displayed as 'yyyy-mm-dd'.
Values range from '1000-01-01 00:00:00' to Displayed as 'yyyy-mm-dd
DATETIME
'9999-12-31 23:59:59'. hh:mm:ss'.
Values range from '1970-01-01 00:00:01' UTC Displayed as 'YYYY-MM-DD
TIMESTAMP(m)
to '2038-01-19 03:14:07' TC. HH:MM:SS'.
TIME Values range from '-838:59:59' to '838:59:59'. Displayed as 'HH:MM:SS'.
YEAR[(2|4)] Year value as 2 digits or 4 digits. Default is 4 digits.

String Data Types:

Data Type Maximum Size

CHAR(size) Maximum size of 255 characters.
VARCHAR(size) Maximum size of 255 characters.
TINYTEXT(size) Maximum size of 255 characters.
TEXT(size) Maximum size of 65,535 characters.
MEDIUMTEXT(size) Maximum size of 16,777,215 characters.
LONGTEXT(size) Max size of 4GB or 4,294,967,295 characters.
BINARY(size) Maximum size of 255 characters.
VARBINARY(size) Maximum size of 255 characters.

Large Object Data Types (LOB) Data Types:

Data Type Syntax Maximum Size

TINYBLOB Maximum size of 255 bytes.
BLOB(size) Maximum size of 65,535 bytes.
MEDIUMBLOB Maximum size of 16,777,215 bytes.
LONGTEXT Maximum size of 4gb or 4,294,967,295 characters.

Prof. Dnyandeo Khemnar Page 4

 SQL Vs MySQL: SQL stands for Structured Query Language. It’s a standard language for
accessing and manipulating databases. MySQL is a database management system, like SQL
Server, Oracle, Informix, Postgres, etc. MySQL is an RDMS (Relational Database Management
System). When considering a utility for data management the two most popular choices are
MySQL and SQL Server. Both are efficient at keeping your data organized and readily available
through a user interface. Both technologies have the concept of the schema (that is table
storage) for data storage.

SQL OPERATOR
An 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 and to serve as conjunctions for
multiple conditions in a statement. There are various types of SQL operator:

SQL Arithmetic Operators: Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20
and 'b' contains 10.

Operator Description Example

+ It adds the value of both operands. a+b will give 30
It is used to subtract the right-hand operand from the left-hand
- a-b will give 10
operand.
* It is used to multiply the value of both operands. a*b will give 200
It is used to divide the left-hand operand by the right-hand
/ a/b will give 2
operand.
It is used to divide the left-hand operand by the right-hand
% a%b will give 0
operand and returns reminder.

SQL Comparison Operators: Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20
and 'b' contains 10.

Operator Description Example

It checks if two operands values are equal or not, if the values are (a=b) is not
=
queal then condition becomes true. true
!= It checks if two operands values are equal or not, if values are not (a!=b) is true

Prof. Dnyandeo Khemnar Page 5

 equal, then condition becomes true.
It checks if two operands values are equal or not, if values are not (a<>b) is
<>
equal then condition becomes true. true
It checks if the left operand value is greater than right operand value, (a>b) is not
>
if yes then condition becomes true. true
It checks if the left operand value is less than right operand value, if
< (a<b) is true
yes then condition becomes true.
It checks if the left operand value is greater than or equal to the right (a>=b) is not
>=
operand value, if yes then condition becomes true. true
It checks if the left operand value is less than or equal to the right (a<=b) is
<=
operand value, if yes then condition becomes true. true
It checks if the left operand value is not less than the right operand (a!<b) is not
!<
value, if yes then condition becomes true. true
It checks if the left operand value is not greater than the right
!> (a!>b) is true
operand value, if yes then condition becomes true.

SQL Logical Operators: There is the list of logical operator used in SQL

Operator Description
ALL It compares a value to all values in another value set.
Some It compares a value to some values in another value set.
AND It allows the existence of multiple conditions in an SQL statement.
ANY It compares the values in the list according to the condition.
BETWEEN It is used to search for values that are within a set of values.
IN It compares a value to that specified list value.
NOT It reverses the meaning of any logical operator.
OR It combines multiple conditions in SQL statements.
EXISTS It is used to search for the presence of a row in a specified table.
LIKE It compares a value to similar values using wildcard operator.
IS NULL The NULL operator is used to compare a value with a NULL value.
The UNIQUE operator searches every row of a specified table for uniqueness
UNIQUE
(no duplicates).

SQL COMMAND
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. SQL can perform various tasks like
create a table, add data to tables, drop the table, modify the table, set permission for users.

Prof. Dnyandeo Khemnar Page 6

 Structured Query Language(SQL) as we all know is the database language by the use of which
we can perform certain operations on the existing database and also we can use this language
to create a database. SQL uses certain commands like Create, Drop, Insert etc. to carry out the
required tasks. These SQL commands are mainly categorized into four categories as discussed
below:

DDL (Data Definition Language) : DDL or Data Definition Language actually consists ofthe
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 database.

1. CREATE – is used to create the database or its objects (like table, index, function, views,
store procedure and triggers).
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[, ... ]);
Example:
CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

2. DROP – is used to delete objects from the database.

Syntax
DROP TABLE ;
Example
DROP TABLE EMPLOYEE;

3. TRUNCATE–is used to remove all records from a table, including all spaces allocated for
the records are removed, it does not disturb the structure of table, i.e after truncate your table
with all attributes will be exist but all the rows in table will be deleted.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;

Prof. Dnyandeo Khemnar Page 7

 4. COMMENT –is used to add comments to the data dictionary. This is similar to comment
we add in program block.
/* add comment here*/

5. RENAME –is used to rename an objects like database, tables, views etc. existing in the
database.
Syntax: Rename Object old_name to new_name;
Example: Rename table emp to employee;

6. ALTER-is used to alter the structure of the table, It is used to alter the structure of the
table. This change could be either to modify the characteristics of an existing attribute or
probably to add a new attribute or to remove the existing attributes.
Syntax:

 ALTER TABLE command to add a New Column in an existing table is as follows.

ALTER TABLE table_name ADD (column_name datatype);

 ALTER TABLE command to DROP COLUMN in an existing table is as follows.

ALTER TABLE table_name DROP COLUMN column_name;

 ALTER TABLE command to RENAME ATTRIBUTE in an existing table is as follows.

ALTER TABLE table_name Rename old-column_name to new-column-name;

 ALTER TABLE command to change the DATA TYPE of a column in a table is as follows.

ALTER TABLE table_name MODIFY (column_name datatype);

 ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows.

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

 ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1,
column2...);

 ALTER TABLE command to ADD CHECK CONSTRAINT to a table is as follows.

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

 ALTER TABLE command to ADD PRIMARY KEY constraint to a table is as follows.

ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1,

column2...);

 ALTER TABLE command to DROP CONSTRAINT from a table is as follows.

ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

DML (Data Manipulation Language) : The SQL commands that deals with the manipulation
of data present in database belong to DML or Data Manipulation Language and this includes
most of the SQL statements.
Prof. Dnyandeo Khemnar Page 8
 1. SELECT – is Data Manipulation Language command used to retrieve data from the tables
of selected database. It retrieves the data and displays the retrieved data on console.
Syntax:
SELECT expressions FROM TABLES WHERE conditions;
For example:
SELECT emp_name FROM employee WHERE age > 20;

2. INSERT – is Data Manipulation Language command used to insert data into a table. It can
insert a single record at a time in a table.
Syntax:
INSERT INTO TABLE_NAME (col1, col2, …... col N) VALUES (value1, value2, ….. valueN);

Or INSERT INTO TABLE_NAME VALUES (value1, value2, value3, ..... valueN);

For example:
INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

3. UPDATE – is Data Manipulation Language command used to update existing data within a
table. It can modify the any values or attributes for the particular tuple or row based on
specified condition.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE
CONDITION]
For example:
UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'

4. DELETE – is Data Manipulation Language command used to delete records from a

database table. It deletes the one or more record from tables depends on a conditions.
Syntax: DELETE FROM table_name [WHERE condition];
For example: DELETE FROM JBook WHERE Author="Sonoo";

DCL (Data Control Language) : DCL includes commands such as GRANT and REVOKEwhich
mainly deals with the rights, permissions and other controls of the database system.

1. GRANT-gives user’s access privileges to database. It is also used to create a database user
with some privileges.
Example
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

2. REVOKE-withdraw user’s access privileges given by using the GRANT command.

Example:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
TCL (transaction Control Language) : TCL commands deals with the transactionwithin the
database.
1. COMMIT– commits a Transaction, is used to save all the transactions to the database.
Syntax:
COMMIT;
Example:
Prof. Dnyandeo Khemnar Page 9
 DELETE FROM CUSTOMERS WHERE AGE = 25;
COMMIT;

2. ROLLBACK– rollbacks a transaction in case of any error occurs. Rollback command is used
to undo transactions that have not already been saved to the database. It can rollback all
transaction which are not yet committed.
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS WHERE AGE = 25;
ROLLBACK;

3. SAVEPOINT–sets a savepoint within a transaction.

4. SET TRANSACTION–specify characteristics for the transaction.

VIEWS IN SQL
Views in SQL are a virtual table. A view also contains rows and columns. They have their own
existence in database But they don’t save any records in a view. To create the view, we can
select the fields from one or more tables present in the database. A view can either have
specific rows based on certain condition or all the rows of a table. we can add SQL functions,
WHERE, and JOIN statements to a view and present the data as if the data were coming from
one single table.
Student_Detail Student_Marks

STU_ID NAME ADDRESS STU_ID NAME MARKS AGE

1 Stephan Delhi 1 Stephan 97 19
2 Kathrin Noida 2 Kathrin 86 21
3 David Ghaziabad 3 David 74 18
4 Alina Gurugram 4 Alina 90 20
5 John 96 18

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 condit
ion;
2. Creating View from a single table: In this example, we create a View named DetailsView
from the table Student_Detail.
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;

Prof. Dnyandeo Khemnar Page 10

 Output:
NAME ADDRESS
Stephan Delhi
Kathrin Noida
David Ghaziabad

2. Creating View from multiple tables: View from multiple tables can be created by simply
include multiple tables in the SELECT statement.

Example, a view is created named MarksView from two tables Student_Detail and
Student_Marks.

CREATE VIEW MarksView AS SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Studen

t_Marks.MARKS FROM Student_Detail, Student_Mark WHERE Student_Detail.NAME = Student
_Marks.NAME;

To display data of View MarksView: SELECT * FROM MarksView;

NAME ADDRESS MARKS

Stephan Delhi 97
Kathrin Noida 86
David Ghaziabad 74
Alina Gurugram 90
4. Deleting View: A view can be deleted using the Drop View statement.
Syntax DROP VIEW view_name;
Example: To delete the View MarksView, we can do this as: DROP VIEW MarksView;

NESTED QUERY(subquery)
A Subquery or Inner query or a Nested query is a query within another SQL query and
embedded within the WHERE clause. A subquery is used to return data that will be used in the
main query as a condition to further restrict the data to be retrieved. Subqueries can be used
with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <,
>, >=, <=, IN, BETWEEN, etc. sub queries can retrieve the data from single or multiple tables.

There are a few rules that subqueries must follow

 Subqueries must be enclosed within parentheses.
 A subquery can have only one column in the SELECT clause, unless multiple columns are
in the main query for the subquery to compare its selected columns.
 An ORDER BY command cannot be used in a subquery, although the main query can use an
ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER
BY in a subquery.
 Subqueries that return more than one row can only be used with multiple value operators
such as the IN operator.

Prof. Dnyandeo Khemnar Page 11

  The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY,
CLOB, or NCLOB.
 A subquery cannot be immediately enclosed in a set function.
 The BETWEEN operator cannot be used with a subquery. However, the BETWEEN
operator can be used within the subquery.

1. Sub queries with the SELECT Statement: Subqueries are most frequently used with the
SELECT statement. The basic syntax is as follows

SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name

OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])
Example: Consider the CUSTOMERS table having the following records –

ID NAME AGE ADDRESS SALARY

1 Ramesh 35 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

Now, let us check the following subquery with a SELECT statement.

SQL> SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY
> 4500) ;

This would produce the following result.

ID NAME AGE ADDRESS SALARY

4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

3. Subqueries with the INSERT Statement: Sub queries also can be used with INSERT
statements. The INSERT statement uses the data returned from the subquery to insert into
another table. The selected data in the sub query can be modified with any of the character,
date or number functions. The basic syntax is as follows.

INSERT INTO table_name [(column1 [, column2 ])] Operator (SELECT [ *|column1 [, column2
] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ])

Example: Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table.

Now to copy the complete CUSTOMERS table into the CUSTOMERS_BKP table, you can use the
following syntax.

SQL> INSERT INTO CUSTOMERS_BKP SELECT * FROM CUSTOMERS WHERE ID IN (SELECT

ID FROM CUSTOMERS) ;

Prof. Dnyandeo Khemnar Page 12

 4. Subqueries with the UPDATE Statement: The subquery can be used in conjunction with
the UPDATE statement. Either single or multiple columns in a table can be updated when
using a subquery with the UPDATE statement. The basic syntax is as follows.

UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT

COLUMN_NAME FROM TABLE_NAME [ WHERE) ] )

Example: Assuming, we have CUSTOMERS_BKP table available which is backup of

CUSTOMERS table. The following example updates SALARY by 0.25 times in the CUSTOMERS
table for all the customers whose AGE is greater than or equal to 27.

SQL> UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT AGE
FROM CUSTOMERS_BKP WHERE AGE >= 27 );

This would impact two rows and finally CUSTOMERS table would have the following records.

ID NAME AGE ADDRESS SALARY

1 Ramesh 35 Ahmedabad 2500.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

5. Subqueries with the DELETE Statement: The subquery can be used in conjunction with
the DELETE statement like with any other statements mentioned above. The basic syntax is as
follows.

DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME

FROM TABLE_NAME [ WHERE) ])
Example: Assuming, we have a CUSTOMERS_BKP table available which is a backup of the
CUSTOMERS table. The following example deletes the records from the CUSTOMERS table for
all the customers whose AGE is greater than or equal to 27.

SQL> DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
This would impact two rows and finally the CUSTOMERS table would have the following
records.

ID NAME AGE ADDRESS SALARY

2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
Prof. Dnyandeo Khemnar Page 13
 SQL INDEX
Indexes are special lookup tables. It is used to retrieve data from the database very fast. An
Index is used to speed up select queries and where clauses. But it slow down the data input
with insert and update statements. Indexes can be created or dropped without affecting the
data. An index in a database is just like an index in the back of a book.
For example: When you reference all pages in a book that discusses a certain topic, you first
have to refer to the index, which alphabetically lists all the topics and then referred to one or
more specific page numbers. An index is used for several different reasons. The main reason is
to speed up querying so that you can get rows or sort rows faster. Another reason is to define
a primary-key or unique index which will guarantee that no other columns have the same
values.
First we need to understand how normal (without indexing) query runs. It basically
traverse each rows one by one and when it finds the data it returns. Refer the following image.

So suppose query is to find 50 , it will have to read 49 records as a linear search. Refer the
following image.

When we apply indexing, the query will quickly find out the data without
reading each one of them just by eliminating half of the data in each traversal like a binary
search. The mysql indexes are stored as B-tree where all the data are in leaf node.

Prof. Dnyandeo Khemnar Page 14

 Indexes are used to retrieve data from the database very fast. The users cannot see the
indexes, they are just used to speed up searches/queries.
Indexes in a database are analogous to indexes that you find in a book. If a book has an index,
and I ask you to find a chapter in that book, you can quickly find that with the help of the
index. On the other hand, if the book does not have an index, you will have to spend more time
looking for the chapter by looking at every page from the start to the end of the book. In a
similar fashion, indexes in a database can help queries find data quickly.
Clustered Index: A clustered index defines the order in which data is physically stored in a
table. Table data can be sorted in only way, therefore, there can be only one clustered index
per table. In SQL Server, the primary key constraint automatically creates a clustered index
on that particular column.

Ex. CREATE DATABASE schooldb

CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, gender VAR
CHAR(50) NOT NULL, DOB datetime NOT NULL, total_score INT NOT NULL, city VARCHAR(5
0) NOT NULL)

in the “student” table we have set primary key constraint on the “id” column. This
automatically creates a clustered index on the “id” column. To see all the indexes on a
particular table execute “sp_helpindex” stored procedure. This stored procedure accepts the
name of the table as a parameter and retrieves all the indexes of the table. The following
query retrieves the indexes created on student table.

USE schooldb
EXECUTE sp_helpindex student

Non-Clustered Indexes: A non-clustered index doesn’t sort the physical data inside the table.
In fact, a non-clustered index is stored at one place and table data is stored in another place.
This is similar to a textbook where the book content is located in one place and theindex
is located in another. This allows for more than one non-clustered index per table. It is
important to mention here that inside the table the data will be sorted by a clustered index.
However, inside the non-clustered index data is stored in the specified order. The index
contains column values on which the index is created and the address of the record that the
column value belongs to. When a query is issued against a column on which the index is
created, the database will first go to the index and look for the address of the corresponding
row in the table. It will then go to that row address and fetch other column values. It is due to
this additional step that non-clustered indexes are slower than clustered indexes.
use schooldb

CREATE NONCLUSTERED INDEX IX_tblStudent_Name ON student(name ASC)

The above script creates a non-clustered index on the “name” column of the student table. The
index sorts by name in ascending order. As we said earlier, the table data and index will be
stored in different places. The table records will be sorted by a clustered index if there is one.
The index will be sorted according to its definition and will be stored separately from the
table.Create Index statement: It is used to create an index on a table. It allows duplicate
value.

Prof. Dnyandeo Khemnar Page 15

 Syntax: CREATE INDEX index_name ON table_name (column1, column2, ...);
Example: CREATE INDEX idx_name ON Persons (LastName, FirstName);
1. Unique Index statement: It is used to create a unique index on a table. It does not allow
duplicate value.
Syntax: CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
Example: CREATE UNIQUE INDEX websites_idx ON websites (site_name);
2. Drop Index Statement: It is used to delete an index in a table.
Syntax: DROP INDEX index_name;
Example: DROP INDEX websites_idx;

ALTER command to add and drop INDEX: There are four types of statements for adding indexes
to a table.
 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) − This statement adds
a PRIMARY KEY, which means that the indexed values must be unique and cannot be NULL.

 ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − This statement

creates an index for which the values must be unique (except for the NULL values, which may
appear multiple times).

 ALTER TABLE tbl_name ADD INDEX index_name (column_list)− This adds an ordinary
index in which any value may appear more than once.

 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a

special FULLTEXT index that is used for text-searching purposes.

Displaying INDEX Information: You can use the SHOW INDEX command to list out all
the indexes associated with a table

SQL CLAUSES
Clauses are used to apply some condition or constraints on the query The following are the
various SQL clauses:
Where Clause
Order By Clause
Group By Clause
Having Clause
Distinct Clause

MySQL WHERE Clause: MySQL WHERE Clause is used with SELECT, INSERT, UPDATE and
DELETE clause to filter the results. It specifies a specific position where you have to do the
operation.
Syntax: WHERE conditions;
Conditions: It specifies the conditions that must be fulfilled for records to be selected.

Prof. Dnyandeo Khemnar Page 16

 SELECT * FROM officers WHERE address = 'Mau';

MySQL WHERE Clause with AND condition: In this example, we are retrieving data from the
table "officers" with AND condition.

SELECT * FROM officers WHERE address = 'Lucknow' AND officer_id < 5;

ORDER BY: The ORDER BY clause sorts the result-set in ascending or descending order. It
sorts the records in ascending order by default. DESC keyword is used to sort the records in
descending order.
Syntax: ELECT column1, column2 FROM table_name WHERE condition ORDER BY column1,
column2... ASC|DESC;
Where ASC: It is used to sort the result set in ascending order by expression. DESC: It sorts
the result set in descending order by expression.
Example: Sorting Results in Ascending Order
SELECT * FROM CUSTOMER ORDER BY NAME;
Example: Sorting Results in Descending Order
SELECT * FROM CUSTOMER ORDER BY NAME DESC;
GROUP BY Clause: The MYSQL GROUP BY Clause is used to collect data from multiple records
and group the result by one or more column. It is generally used in a SELECT statement. You
can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG etc. on the grouped
column.
Syntax: SELECT expression1, expression2,... expression_n, aggregate_function (expression)
FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
Parameters: expression1, expression2, ... expression_n: It specifies the expressions that
are not encapsulated within an aggregate function and must be included in the GROUP BY
clause.
Aggregate function: It specifies a function such as SUM, COUNT, MIN, MAX, or AVG etc.
tables: It specifies the tables, from where you want to retrieve the records. There must be at
least one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the
records to be selected.

 SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY
statement is used with the SQL SELECT statement.
 The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes
the ORDER BY clause.
 The GROUP BY statement is used with aggregation function.

Syntax: ELECT column FROM table_name WHERE conditions GROUP BY column ORDER BY
column

MySQL HAVING Clause: MySQL HAVING Clause is used with GROUP BY clause. Italways
returns the rows where condition is TRUE.

Prof. Dnyandeo Khemnar Page 17

 Syntax:

SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM ta

bles [WHERE conditions] GROUP BY expression1, expression2, ... expression_n HAVING cond
ition;
Parameters
Aggregate function: It specifies any one of the aggregate function such as SUM, COUNT, MIN,
MAX, or AVG.
expression1, expression2, ... expression_n: It specifies the expressions that are not
encapsulated within an aggregate function and must be included in the GROUP BY clause.
WHERE conditions: It is optional. It specifies the conditions for the records to be selected.
HAVING condition: It is used to restrict the groups of returned rows. It shows only those
groups in result set whose conditions are TRUE.
Execute the following query:
SELECT emp_name, SUM(working_hours) AS "Total working hours" FROM employees GROU
P BY emp_name HAVING SUM(working_hours) > 5;

MySQL Distinct Clause: MySQL DISTINCT clause is used to remove duplicate records from the
table and fetch only the unique records. The DISTINCT clause is only used with the SELECT
statement.
Syntax:
SELECT DISTINCT expressions FROM tables [WHERE conditions];
Parameters
expressions: specify the columns or calculations that you want to retrieve.
tables: specify the name of the tables from where you retrieve records. There must be at least
one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be met for the records
to be selected.
Note:
 If you put only one expression in the DISTINCT clause, the query will return the unique
values for that expression.
 If you put more than one expression in the DISTINCT clause, the query will retrieve unique
combinations for the expressions listed.
 In MySQL, the DISTINCT clause doesn't ignore NULL values. So if you are using the
DISTINCT clause in your SQL statement, your result set will include NULL as a distinct
value.
Use the following query: SELECT DISTINCT address FROM officers;

SET OPERATIONS:
SQL supports few Set operations which can be performed on the table data. These are used to get
Prof. Dnyandeo Khemnar Page 18
 meaningful results from data stored in the table, under different special conditions. Or
The SQL Set operation is used to combine the two or more SQL SELECT statements. Four different types of SET
operations, are

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS
The First table, The Second table,
ID Name ID Name
1 Abhi 2 adam
2 Adam 3 Chester

UNION Operation: UNION is used to combine the results of two or more SELECT statements.
However it will eliminate duplicate rows from its resultset. In case of union, number of
columns and datatype must be same in both the tables, on which UNION operation is being
applied.
Select * from First Union Select * from Second
The result set table will look like,
ID NAME
1 abhi
2 adam
3 Chester

UNION ALL: This operation is similar to Union. But it also shows the duplicate rows.

Select * from First Union All Select * from Second

The result set table will look like,
ID NAME
1 abhi
2 adam
2 adam
3 Chester
INTERSECT: Intersect operation is used to combine two SELECT statements, but it only
returns the records which are common from both SELECT statements. In case of
Intersect the number of columns and datatype must be same. But remember MySQL does
not support INTERSECT operator.

Select * from First INTERSECT Select * from Second

The result set table will look like

ID NAME
2 adam

Prof. Dnyandeo Khemnar Page 19

 MINUS: The Minus operation combines results of two SELECT statements and return only those
in the final result, which belongs to the first set of the result.

Select * from First MINUS Select * from Second

The result set table will look like

ID NAME
2 adam

3.2 SET MEMBERSHIP: To Check whether any

values is a member of some set or not set membership is used. In SQL set membership is used
with IN and NOT IN operator which allows you to easily test if an expression matches any
value in a list of values. The IN operator checks a value within a set of values separated by
commas and retrieve the rows from the table which are matching. The IN returns 1 when the
search value present within the range otherwise returns 0. Consider Example with Relations
SavingAccount(Laccno, SCust_Name, Balance)
Loan Account (Laccno, LCust_Name, Balance)
1. Find list of all customer name which are having Loan account as well saving account
Select * from SavingAccount where SCust_Name IN (Select * from LoanAccount)
2. Find list of Customer Belonging to any ne of following city.
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

SET COMPARISON: Set comparison is SQL is used to compare the values in two set. Using
arithmetic comparison operator we can just compare two values but if we want to compare
more than one elements of two sets then set comparison can be used with SOME and ALL
operator.
First we will understand how All and Some operatorwork. Remember SOME operator will
match any element of Set A with SOME elements of Set B. and ALL Operator will match any
element of Set A with ALL elements of Set B. Consider two Sets
X={100, 200, 300} And Y = {300, 400, 500}

Condition for Some Result for Some Condition for All Result for All
Operator Operator Operator Operator
A < SOME B TRUE A < ALL B TRUE
A <= SOME B TRUE A <= ALL B TRUE
A >= SOME B TRUE A >= ALL B FALSE
A > SOME B FALSE A > ALL B FALSE
A <> SOME B TRUE A <> ALL B FALSE
A = SOME B TRUE A = ALL B FALSE

Consider the example with following relations

Branch(bname, bcity, assets)
1. Find the name of all branches that have assets greater than those of some branches
located in nashik.

Prof. Dnyandeo Khemnar Page 20

 Select bname from branch where assets > SOME (Select assets from Branch where
bcity=’Nashik’)

2. Find the name of all branches that have assets greater than those of ALL branches
located in nashik.

Select bname from branch where assets > ALL (Select assets from Branch where
bcity=’Nashik’)

SQL JOINS
The SQL Joins clause is used to combine records from two or more tables in a database. A
JOIN is a means for combining fields from two tables by using values common to each. A SQL
Join statement is used to combine data or rows from two or more tables based on a common
field between them.

Why SQL JOIN is used?

 If you want to access more than one table through a select statement.
 If you want to combine two or more table then SQL JOIN statement is used .it combines
rows of that tables in one table and one can retrieve the information by a SELECT
statement.
 The joining of two or more tables is based on common field between them.
 SQL INNER JOIN also known as simple join is the most common type of join.
 JOINS allow us to combine data from more than one table into a single result set.
 JOINS have better performance compared to sub queries
 INNER JOINS only return rows that meet the given criteria.
 OUTER JOINS can also return rows where no matches have been found. The unmatched
rows are returned with the NULL keyword.

Let us Discus Different types of Joins with two tables Order & Customer
1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
Table 1: Customer

customer_id first_name last_name address city

1 George Washington 3200 Mt Vernon Hwy Mount Vernon
2 John Adams 1250 Hancock St Quincy
3 Thomas Jefferson 931 Thomas Jefferson Pkwy Charlottesville
4 James Madison 11350 Constitution Hwy Orange
5 James Monroe 2050 James Monroe Parkway Charlottesville

Table 2: Order
order_id order_date amount customer_id
1 07/04/1776 $234.56 1
2 03/14/1760 $78.50 3
3 05/23/1784 $124.00 2

Prof. Dnyandeo Khemnar Page 21

 4 09/03/1790 $65.50 3
5 07/21/1795 $25.50 10
6 11/27/1787 $14.40 9

1. INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the
condition satisfies. 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 same.
Syntax: Select t1.attribute, t2.attribute from Table1 t1 INNER JOIN Table t2 ON
t1.matching_Column= t2.matching_Column

Select c.first_name, c.last_name,o. order_date,

o.order_amount from customers c inner join orders o
on c.customer_id = o.customer_id

The Output of This Query will be

first_name last_name order_date order_amount

George Washington 07/4/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50

2. LEFT JOIN: it is also called as Left Outer Join. This join returns all the rows of the table on
the left side of the join and matching rows for the table on the right side of join. The rows for
which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also
known as LEFT OUTER JOIN.
Syntax: Select t1.attribute, t2.attribute from Table1 t1 LEFT JOIN Table t2 ON
t1.matching_Column= t2.matching_Column

Select c.first_name, c.last_name,o. order_date,

o.order_amount from customers c Left join orders o
on c.customer_id = o.customer_id

The Output of This Query will be

first_name last_name order_date order_amount

George Washington 07/04/1776 $234.56
John Adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50
James Madison NULL NULL
James Monroe NULL NULL

3. RIGHT JOIN: it is also called as Right Outer Join. RIGHT JOIN is similar to LEFT JOIN. This
join returns all the rows of the table on the right side of the join and matching rows for the

Prof. Dnyandeo Khemnar Page 22

 table on the left side of join. The rows for which there is no matching row on left side, the
result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax: Select t1.attribute, t2.attribute from Table1 t1 RIGHT JOIN Table t2 ON
t1.matching_Column= t2.matching_Column

Select c.first_name, c.last_name,o. order_date,

o.order_amount from customers c Right join orders o
on c.customer_id = o.customer_id

The Output of This Query will be

first_name last_name order_date order_amount

George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40

4. FULL JOIN: FULL JOIN creates the result-set by combining result of both LEFT JOIN and
RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which
there is no matching, the result-set will contain NULL values.
Syntax: Select t1.attribute, t2.attribute from Table1 t1 FULL JOIN Table t2 ON
t1.matching_Column= t2.matching_Column

Select c.first_name, c.last_name,o. order_date,

o.order_amount from customers c Full join orders o
On c.customer_id = o.customer_id

The Output of This Query will be

first_name last_name order_date order_amount

George Washington 07/04/1776 $234.56
Thomas Jefferson 03/14/1760 $78.50
John Adams 05/23/1784 $124.00
Thomas Jefferson 09/03/1790 $65.50
NULL NULL 07/21/1795 $25.50
NULL NULL 11/27/1787 $14.40
James Madison NULL NULL
James Monroe NULL NULL

AGGREGATE FUNCTIONS: In database management an aggregate function is a function

where the values of multiple rows are grouped together as input on certain criteriato form a
single value of more significant meaning. Different aggregate functions are as follow.

Prof. Dnyandeo Khemnar Page 23

 1. COUNT Function: COUNT function is used to Count the number of rows in a database table.
It can work on both numeric and non-numeric data types. It uses the COUNT(*) that returns
the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.
Example: SELECT COUNT(*) FROM PRODUCT_MAST;
2. SUM Function: Sum function is used to calculate the sum of all selected columns. It works
on numeric fields only.
Example: SELECT SUM(COST) FROM PRODUCT_MAST;
3. AVG function: The AVG function is used to calculate the average value of the numeric type.
AVG function returns the average of all non-Null values.
Example: SELECT AVG(COST) FROM PRODUCT_MAST;
4. MAX Function: MAX function is used to find the maximum value of a certain column. This
function determines the largest value of all selected values of a column.
Example: SELECT MAX(RATE) FROM PRODUCT_MAST;
5. MIN Function: MIN function is used to find the minimum value of a certain column. This
function determines the smallest value of all selected values of a column.
Example: SELECT MIN(RATE) FROM PRODUCT_MAST;

3.3 TUPLE VARIABLE: A tuple variable is variable that takes on tuples of a particular relation
schema as values. That is, every value assigned to a given tuple variable has the same number and type
of fields. Consider the Table

order_id order_date amount

1 07/04/1776 $234.56
Select Order_date, Sum(Amount) AS
2 07/04/1776 $78.50
3 05/23/1784 $124.00 ‘Sale_of_Day ’ from order group by
4 05/23/1784 $65.50 order_date.
5 07/21/1795 $25.50
6 11/27/1787 $14.40

order_date Sale_of_Day
07/04/1776 $313.06 The Output of this will look like this where as
05/23/1784 $189.50
Sale_of_Day is a tuple variable.
07/21/1795 $25.50
11/27/1787 $14.40

Prof. Dnyandeo Khemnar Page 24

 ORDERING OF TUPLES: Ordering of tuple is the process of ordering the rows in a table in
some order i.e. either ascending or descending order. This can be used with Order by clause.
To arrange the rows in ascending order we use the symbol ASC and for arranging the rows in
descending order we use DESC symbol along with order by clause. If we does not specify the
order sequence then by default it will arrange the rows in ascending order.

Example: Select * from Order ORDER BY order_id ASC;

OR
Select * from Order ORDER BY order_id DESC;

Prof. Dnyandeo Khemnar Page 25