NCERT Chapter 9 Structured Query Language

SQL(Structured Query Language)

❑ The Structured Query Language (SQL) is a language that enables you to create and operate on
relational databases, which are sets of related information stored in tables.
❑ SQL is the set of commands that is recognized by nearly all RDBMSs.
❑ SQL is case insensitive. For example, the column names ‘salary’ and ‘SALARY’ are the same for SQL.
❑ SQL statements always end with a semicolon (;).

What Can SQL do?

•SQL can execute queries against a database

•SQL can retrieve data from a database
•SQL can insert records in a database
•SQL can update records in a database
•SQL can delete records from a database
•SQL can create new databases
•SQL can create new tables in a database

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
CLASSIFICATION OF SQL STATEMENTS

SQL provides many different types of commands used for different purposes. SQL
commands can be mainly divided into following categories :

i) Data Definition Language ( DDL )Commands : Commands that allow you to perform
tasks related to structure of a table e.g. create , alter , drop ,truncate etc.

ii ) Data Manipulation Language (DML) Commands : Commands that allow you to perform
data manipulation e.g., retrieval, insertion, deletion and modification of data stored in a
database e.g. select , insert , update , delete etc.

iiii ) Transactional Control Language (TCL) Commands : Commands that allow you to
manage and control the transactions e.g. commit , rollback , save point etc.

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Create command

The Create statement is used to create a database and its tables (relations).

❑CREATE Database:- To create a database, we use the CREATE DATABASE statement as

shown in the following syntax:

CREATE DATABASE database_name ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Show command
The show statement is used to know the names of existing databases and tables.

Show Databases: To see a list of all available databases, use the following SQL
command:

show databases;

This command will display a list of database names that exist on your MySQL server.

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Use command
The Use statement is used to access the specific database.

Use a Database: To switch to a specific database and start working within it, use the
following SQL command:

USE database_name:

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Show command
The show statement is used to know the names of existing databases and tables.

Show Tables: To see a list of all available tables, use the following SQL command:

show tables;

This command will display a list of all table within your selected database.

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Create command

To create a table within a database, you'll need to specify the table name and define its
columns along with their data types. Here's an example of how you can create a simple
table in MySQL:

Syntax:

CREATE TABLE table_name

( column-name1 datatype constraint ,
column-name2 datatype constraint ,
:
column-nameN datatype constraint ) ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Data Types
DATA TYPE:- Data type of an attribute indicates the type of data value that an attribute can have.
Commonly used data types in MySQL are numeric types, date and string types as shown in Table

Data type Description

CHAR(n) Specifies character type data of length n where n could be any value from 0 to
255. CHAR is of fixed length.

VARCHAR(n) Specifies character type data of length where n could be any value from 0 to 65535. But
VARCHAR(n) is a variable-length data type.

INT INT specifies an integer value. Each INT value occupies 4 bytes of storage. The range of
unsigned values allowed in a 4 byte integer type are 0 to 4,294,967,295. For values larger
than that, we have to use BIGINT, which occupies 8 bytes.
FLOAT Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.
DATE The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is the 4 digit year, MM is
the 2 digit month and DD is the 2 digit date.

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
CONSTRAINTS
Constraints are the certain types of restrictions on the data values that an attribute can have. Table lists
some of the commonly used constraints in SQL. They are used to ensure correctness of data. However, it
is not mandatory to define constraints for each attribute of a table.

Constraint Description
NOT NULL Ensures that a column cannot have NULL values where NULL means missing/
unknown/not applicable value.
UNIQUE Ensures that all the values in a column are distinct/unique
DEFAULT A default value specified for the column if no value is provided
PRIMARY The column which can uniquely identify each row/record in a table.
KEY
FOREIGN The column which refers to value of an attribute defined as primary key in
KEY another table

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Desc Command

Desc command is used to retrieve information about the structure of a database table.
Specifically, it is used to obtain details about the columns (attributes) of a table, such as
their names, data types, constraints, and default values.

The syntax for the DESC command is straightforward:

desc table_name;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Insert into Command

INSERT INTO statement is used to insert new records/Tuples in a table/Relation.

Its syntax is:

INSERT INTO table-name

VALUES ( value1 ,value 2 ,. );

Here, value1 corresponds to attribute1, value2 corresponds to attribute2 and so on.

my sql > INSERT INTO STUDENT

VALUES ( 1 , ' Neeraj Ahu ja ' , ' 2003 - 05 - 15 ‘ , 78) ;

OR

my sql > INSERT INTO STUDENT( RollNumber , SName, SDateofBirth ,Marks )

VALUES ( 1 , ' Neeraj Ahu j a ' , ' 2003 - 05 - 15 ‘ , 78 ) ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Select Command

The SQL statement SELECT is used to retrieve data from the tables in a database and is also called a
query statement and the output is also displayed in tabular form.
Syntax:

SELECT attribute1, attribute2, ...

FROM table _name;

Here, attribute1, attribute2, ... are the column names of the table table_name from which we want
to retrieve data.
The FROM clause is always written with SELECT clause as it specifies the name of the table from
which data is to be retrieved.

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Select Command
The following query retrieves the name and marks of students:

my sql > SELECT sname , marks

FROM class12a;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Select Command

To select all the data available in a table, we use the following select statement:

SELECT *
FROM table_name ;

The following query retrieves all details of the students:

my sql > SELECT *

FROM class12a;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Select Command
Renaming of columns

In case we want to rename any column while displaying the output, it can be done by using the alias 'AS'.

my sql > SELECT sname as Name

FROM class12a;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Distinct Keyword

❑ By default, SQL shows all the data retrieved through query as output. However, there can be duplicate values.
❑ The DISTINCT keyword eliminates duplicates rows from the results of a Select statement.
❑ For example, while retrieving a department number from employee relation, there can be duplicate values as
many employees are assigned to the same department.
❑ To select unique department number for all the employees, we use DISTINCT as shown below:

EMPLOYEE
my sql > SELECT DISTINCT DeptId
FROM EMPLOYEE ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Where Clause

In SQL, the "WHERE" clause is used to filter rows from a table based on a specified condition. It allows you to extract
only the rows that meet the given criteria. The basic syntax of the "WHERE" clause is as follows:

mysql > SELECT column1, column2, ...

FROM table_name
WHERE condition;

Following query gives salaries of the employees working in the department number D01:
my sql > SELECT Salary EMPLOYEE
FROM EMPLOYEE
WHERE Deptid= ' D01 ' ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Relational Operators

Relational operators are used to perform comparisons and establish relationships between data values in a database.
These operators are typically used in the WHERE clause.

Here are the common relational operators used in SQL:

Equal to: =
Not equal to: <> or !=
Less than: <
Less than or equal to: <=
Greater than: >
Greater than or equal to: >=
BETWEEN: Used to specify a range between two values (inclusive).
LIKE: Used for pattern matching with wildcards.
IN: Used to check if a value is within a list of values.
IS NULL: Used to check for NULL values.

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Relational Operators employee

Use of = , <> or != , < , < , >= ,<=

1. SELECT Salary
FROM employee
WHERE Deptid= ' D01 ' ;

2. SELECT Salary
FROM employee
WHERE Deptid <>' D01 ' ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Relational Operators employee

3. SELECT Ename
FROM employee
WHERE Salary > 60000 ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Logical Operators

The logical operators AND(&&), OR(||), and NOT(!) are used to combine multiple conditions.
Display all the details of those employees of D01 department who earn employee
more than 50000.

my sql > SELECT * FROM EMPLOYEE

- WHERE Salary > 5000 AND DeptId =' D04 ' ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Logical Operators

The following query selects details of all the employees who work in the
departments having deptid D03 or D04.
my s q l > SELECT * employee
FROM EMPLOYEE
WHERE DeptId =' D03 ' OR DeptId =' D04 ' ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Logical Operators

The following query selects records of all the employees except Kalpana.
employee
my sql > SELECT * FROM EMPLOYEE
WHERE NOT Ename =' Kalpana' ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
BETWEEN Operators

The BETWEEN operator defines a range of values that the column values must fall into make the
condition True.

The following query selects the name and employee number employee
of all those employees who are earning salary between 20000
and 60000 (both values inclusive).
SELECT Ename , EmpNo
FROM EMPLOYEE
WHERE Salary BETWEEN 20000 AND 60000 ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
IN Operators

To specify a list of values, IN operator is used. The IN operator selects values that match any value in a given List.

The following query selects name of all the employees who work in the
employee
departments having deptid D01, D02 or D04.

SELECT Ename FROM EMPLOYEE

WHERE DeptId IN ( ' D01 ' ,' D02 ',' D04 ' ) ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Substring Pattern Matching

SQL provides a LIKE operator that can be used with the WHERE clause to search for a specified pattern in a column.

The LIKE operator makes use of the following two wild card characters:
❑% (per cent) - used to represent zero, one, or multiple characters
❑_ (underscore) - used to represent exactly a single character

The following query selects details of all those employees whose name starts with 'K'.
my s q l > SELECT * FROM EMPLOYEE
WHERE Ename like ' K% ' ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Substring Pattern Matching

The following query selects details of all those employees whose name ends with 'a', and gets a salary more than
45000.
my s q l > select * from employee where Ename like '%a' and Salary>45000;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Substring Pattern Matching

The following query selects details of all those employees whose name consists of exactly 5 letters and starts with any
letter but has ‘ANYA’ after that.

my s q l > SELECT * FROM EMPLOYEE

WHERE Ename like ' _ANYA ' ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Substring Pattern Matching

The following query selects names of all employees containing 'se' as a substring in name.
my s q l > SELECT Ename FROM EMPLOYEE
WHERE Ename like ' % se% ' ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

ORDER BY CLAUSE

❑ ORDER BY clause is used to display data in an ordered form with respect to a specified column.
❑ By default, ORDER BY displays records in ascending order of the specified column’s values.
❑ To display the records in descending order, the DESC (means descending) keyword needs to be written with that
column.
employee
The following query selects details of all the employees in Descending
order of their salaries.

my sql > Select * from employee

ORDER BY Salary DESC;
OUTPUT:

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

SQL Aggregate Functions

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

Count() min()

Sum() avg() max()

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
• 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.
❑If you specify the asterisk(*) , COUNT(*) ,this function returns all rows , including
duplicates and Nulls.
Syntax:
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )

Select count(*) from employee;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
2. SUM Function
❑ Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax:
SUM()
or
SUM( [ALL|DISTINCT] expression )

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

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.

Syntax:
avg([ALL | DISTINCT] EXPRESSION)

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

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.

Syntax:
max([ALL | DISTINCT] EXPRESSION)

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

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.

Syntax:
min([ALL | DISTINCT] EXPRESSION)

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Group By Clause

❑ Group By clause combines all those records that have identical values in a particular field or group of fields.
❑ In other words , the Group by clause is used in select statements to divide the table into groups.
❑ Grouping can be done by a column name , or with aggregate functions in which case the aggregate produces
a value for each group.

Syntax

SELECT column1, column2

FROM table_name
GROUP BY column1, column2

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Group By Clause

Calculate the number of employee in each department.

Select Deptid ,count(*)
From employee
Group by Deptid;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Group By Clause

Display the name of jobs in which employees are working.

Select Job ,count(*)
From employee
Group by Job;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Group By Clause

Select Deptid ,sum(Salary)

From employee
Group by Deptid;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Having Clause

❑ Having clause places condition on groups in contrast to where clause that places conditions
on individual rows.

SYNTAX:

SELECT column1, function_name(column2)

FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Having Clause

To Calculate the average Salary for employees belonging to ‘D01’ Department.

Select avg(Salary)
From employee
Group by Deptid
Having Deptid=‘D01’;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Having Clause

To Display the jobs where the number of employees is less than 3.

Select Job,Count(*)
From employee
Group by Job
Having Count(*)<3;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Having Clause

Select Job , avg(salary)

From employee
Group by Job
Having Job IN (‘Clerk’, ‘Salesman’);

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Modify Data with UPDATE Command

Sometimes you need to change some or all of the values in an existing row. This can be done using the UPDATE command
Of SQL.
Syntax:

UPDATE table _name

SET attribute1 = value1, attribute2 = value2 , ...
WHERE condition ;

UPDATE employee
SET Salary=55000
WHERE Empno=104;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

Deleting Data with DELETE Command

DELETE statement is used to delete/remove one or more row / records from a table.

Syntax:

DELETE FROM table_name

WHERE condition ;

DELETE FROM employee

WHERE Empno=110;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

ALTER TABLE

After creating a table, we may realize that we need to add/remove an attribute or to modify the datatype of an
existing attribute or to add constraint in attribute.
In all such cases, we need to change or alter the structure (schema) of the table by using the alter statement.

1. Add an attribute to an existing table Sometimes, we may need to add an additional attribute in a
table. It can be done using the ADD attribute statement as shown in the following Syntax:

ALTER TABLE table_name

ADD attribute_name DATATYPE ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

ALTER TABLE
2. Modify datatype of an attribute:- We can change data types of the existing attributes of a table
using the following ALTER statement.
Syntax:
ALTER TABLE table _name
MODIFY attribute DATATYPE ;

Suppose we need to change the size of the attribute Deptid from VARCHAR(30) to VARCHAR(40) of the employee
table. The MySQL statement will be:
my sql > ALTER TABLE employee
MODIFY Depitd VARCHAR ( 40 ) ;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

ALTER TABLE

3. Remove an attribute Using ALTER, we can remove attributes from a table, as shown in the
following syntax:

ALTER TABLE table _name DROP attribute ;

To remove the attribute Salary from table employee ,write the following MySQL statement:
my sql > ALTER TABLE employee
DROP Salary;

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

DROP Statement

✓ Sometimes a table in a database or the database itself needs to be removed.

✓ We can use a DROP statement to remove a database or a table permanently from the system.
✓ However, one should be very cautious while using this statement as it cannot be undone.

Syntax to drop a table:

DROP TABLE table _name ;

Syntax to drop a database:

DROP DATABASE database _name ;

Note: Using the DROP statement to remove a database will ultimately remove all the tables within it.

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language
Cartesian Product (X)
Cartesian product operation combines tuples from two relations. It results in all pairs of rows from the two input
relations, regardless of whether or not they have the same values on common attributes. It is denoted as ‘X’.
Display all possible combinations of tuples of relations Uniform and Cost
mysql> SELECT * FROM Uniform, Cost; UCode UName UColor UCode Size Price
1 Shirt white 1 L 600
Uniform 1 Shirt white 1 M 680
UCode UName UColor 1 Shirt white 2 L 500
1 Shirt white 1 Shirt white 4 L 550
2 Pant gray 2 Pant gray 1 L 600
3 Tie red 2 Pant gray 1 M 680
Cost 2 Pant gray 2 L 500
UCode Size Price 2 Pant gray 4 L 550
1 L 600 3 Tie red 1 L 600
1 M 680 3 Tie red 1 M 680
2 L 500 3 Tie red 2 L 500
4 L 550 3 Tie red 4 L 550

Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

JOINS
A join is a query that combines rows from two or more tables.
In a join query , more than one table are listed in FROM Clause.
JOIN operation combines tuples from two tables on specified conditions.
EQUI-JOIN
EQUI-JOIN is a simple join which use the equal sign( = ) in comparison operator.
UCode UName UColor Select *
from Uniform , Cost
1 Shirt white
Uniform where [Link]=[Link]
2 Pant gray
3 Tie red

UCode Size Price

1 L 600
Cost 1 M 680
2 L 500
4 L 550
Subscribe Data Mining Hub for Class 11-12 Computer Science with Python
 NCERT Chapter 9 Structured Query Language

NATURAL-JOIN

NATURAL JOIN works similar to EQUI-JOIN clause in SQL but removes the redundant attribute.
Select *
from Uniform NATURAL JOIN Cost;

UCode UName UColor

1 Shirt white
Uniform
2 Pant gray
3 Tie red

UCode Size Price

1 L 600
Cost 1 M 680
2 L 500
4 L 550
Subscribe Data Mining Hub for Class 11-12 Computer Science with Python