CHAPTER 9 : STRUCTURED QUERY LANGUAGE (SQL)
SQL is a query language used to store, access and manipulate data in relational databases.
SQL stands for Structured Query Language. SQL was developed in 1970's by IBM computer
scientists.
There are many RDBMS MySQL, ORACLE , SQLserver, PostgreSQL, etc that allow us to create a
database consisting of relations.
SQL is a query language that communicates with databases which are stored in DBMS/RDBMS
software.
RDBMS also allows us to store, retrieve and manipulate data on that database through queries.
Installing MySQL:
MySQL is an open-source RDBMS software which can be easily downloaded from the official
website: https://dev.mysql.com/downloads
Important Points to Note While Using SQL:
SQL is case-insensitive. For example, the column names ‘sum’ and ‘SUM’ are the same for SQL.
SQL statements always end with a semicolon (;).
For multiline SQL statements, press the Enter key to continue on the next line.
The prompt mysql> then changes to “->”, indicating that statement is continued to the next line.
After the last line, put “;” and press enter.
Data Types and Constraints in MySQL
Data type of an attribute is the type of data value that an attribute can have and the operations that
can be performed on it.
Commonly used data types in MySQL are:
Data type Description
CHAR(n) This data type specifies character type data of length n
where n could be any value from 0 to 255.
CHAR(n) is fixed length data type
This means, declaring CHAR (10), reserve spaces for 10 characters.
If data does not have 10 characters (e.g., ‘city’ has four characters),
The remaining 6 characters are filled with spaces on the right.
VARCHAR(n) This specifies character type data of length n.
Here, n could be any value from 0 to 65535.
VARCHAR (n) is a variable-length data type.
This means, actual allocated bytes will depend on the length of entered string.
For example ,‘city’ in VARCHAR (30) will occupy space needed to
store 4 characters only.
1
Chapter 9: SQL
INT This specifies character type data of length n.
Here, n is any value from 0 to 65535.
VARCHAR (n) is a variable-length data type.
FLOAT This 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.
The supported range is '1000-01-01' to '9999-12-31'.
Constraints: Constraints are restrictions on data values an attribute can have. They are
used to ensure correctness of data.
Common SQL constraints are:
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 KEY The column which can uniquely identify each row/record in a
table.
FOREIGN KEY The column which refers to value of an attribute defined as
primary key in another table
SQL statements or queries or commands: SQL provides DDL, DML and DQL commands and
statements to create and manage database.
Data Definition Language (DDL)
This is the collection of SQL statements/ queries for defining, modifying and removing relation
schemas.
Create, alter, drop are the commands, statements used in DDL.
Data Manipulation Language (DML)
This is the collection of SQL statements/ queries for insertion of new data, removal of existing
data or modification of existing data in the database.
Insert, update, delete are the commands, statements used in DML.
2
Chapter 9: SQL
Data Query Language (DQL)
This is the collection of SQL statements/queries for retrieving data from the tables in a
database and is also called a query statement.
select is the command or statement used in DQL
SQL for data definition
Data Definition Language (DDL): create, alter, and drop are the commands, statements used in DDL.
A database is a collection of tables. Data are stored in relations or tables in a database.
The Create statement is used to create a database and its tables (relations).
Before creating a database, programmer must know about the number of tables in the
database, the columns (attributes) in each table, data type of each column, and its
constraint.
a) Create command: It is a DDL command used to create database and table (relation).
1) CREATE Database
To create a database, we use the CREATE DATABASE statement as shown in the following
Syntax: CREATE DATABASE databasename;
Example: CREATE DATABASE StudentAttendance;
A DBMS can manage multiple databases on one computer. Therefore, we need to select the database
that we want to use. Note: A DBMS can manage multiple databases, therefore user need to select the
database that they want to use.
a) Following statement is used to know the names of all existing databases.
mysql> SHOW DATABASES;
b) Following statement is used to select the particular database.
mysql> USE databasename;
Example: mysql> USE StudentAttendance;
c) Following statement is used to check the tables present in a database. mysql>
SHOW TABLES;
3
Chapter 9: SQL
2) CREATE TABLE Command
Syntax:
CREATE TABLE <TableName>
(
attribute1 datatype(size) [constraint],
attribute2 datatype(size) [constraint],
...
);
Example:
CREATE TABLE STUDENT
(
RollNumber INT PRIMARY KEY,
SName VARCHAR(20),
SDateofBirth DATE,
GUID CHAR (12),
);
Here,
The number of columns (attributes) in a table defines the degree of that relation, which
is denoted by N.
Attribute name specifies the name of the column in the table.
Datatype specifies the type of data that an attribute can hold.
Constraint indicates the restrictions imposed on the values of an attribute.
By default, each attribute can take NULL values except for the primary key.
DESCRIBE Table : DESSCRIBE statement or DESC statement is used to view the structure of an already
created table.
4
Chapter 9: SQL
Syntax:
DESCRIBE tablename;
OUTPUT:
OR
DESC tablename;
Example :
DESCRIBE STUDENT;
OR
DESC STUDENT;
SHOW TABLES: SHOW TABLES statement to see the tables in the StudentAttendance database.
mysql> SHOW TABLES;
OUTPUT:
ALTER TABLE : This command is used to change, modify the structure or schema of a table.
Alter command or statement is used for following purpose
d) Add PRIMARY KEY to a relation
e) Add FOREIGN KEY to a relation
f) Add constraint UNIQUE to an existing attribute
g) Add an attribute to an existing table
h) Modify datatype of an attribute
i) Modify constraint of an attribute
j) Add default value to an attribute
k) Remove an attribute
l) Remove primary key from the table
(A) Add primary key to a relation : The following MySQL statement adds a primary key to the
GUARDIAN relation:
Example: ALTER TABLE GUARDIAN
ADD PRIMARY KEY (GUID);
5
Chapter 9: SQL
Adding composite primary key to a table
Syntax: ALTER TABLE tablename
ADD PRIMARY KEY (attributename1, attributename2);
ALTER TABLE student
Example:
ADD PRIMARY KEY (AttendanceDate, RollNumber);
(B) Add foreign key to a relation: Following points need to be observed while adding foreign key
to a relation:
• The referenced relation must be already created.
• The referenced attribute(s) must be part of the primary key of the referenced relation.
• Data types and size of referenced and referencing attributes must be the same.
Syntax: ALTER TABLE table_name ADD FOREIGN KEY(attribute name)
REFERENCES referenced_table_name (attribute name);
Example:
ALTER TABLE STUDENT ADD FOREIGN KEY(GUID)
REFERENCES GUARDIAN(GUID);
(C) Add constraint UNIQUE to an existing attribute : In GUARDIAN table, the attribute GPhone has
a constraint UNIQUE which means no two values in that column should be the same.
Syntax: ALTER TABLE table_name
ADD UNIQUE (attribute name);
Example : ALTER TABLE GUARDIAN
ADD UNIQUE(GPhone);
(D) 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.
Syntax: ALTER TABLE table_name
ADD attribute name DATATYPE;
Example: ALTER TABLE GUARDIAN
ADD income INT;
6
Chapter 9: SQL
(E) Modify datatype of an attribute : We can change data types of the existing attributes of a table
using ALTER statement.
Syntax: ALTER TABLE table_name
MODIFY attribute DATATYPE;
Example: ALTER TABLE GUARDIAN
MODIFY GAddress VARCHAR(40);
(F) Modify constraint of an attribute : When we create a table, by default each attribute takes
NULL value except for the attribute defined as primary key. We can change an attribute’s
constraint from NULL to NOT NULL using an alter statement.
Syntax: ALTER TABLE table_name
MODIFY attribute DATATYPE NOT NULL;
Example: ALTER TABLE STUDENT
MODIFY SName VARCHAR(20) NOT NULL;
Note: We have to specify the data type of the attribute along with constraint NOT NULL while using
MODIFY.
(G) Add default value to an attribute : We can specify default value for an attribute by using the
following statement.
Syntax: ALTER TABLE table_name
MODIFY attribute DATATYPE DEFAULT default_value;
Example: ALTER TABLE STUDENT
MODIFY SDateofBirth DATE DEFAULT ‘2000-05-15’;
Note: We have to specify the data type of the attribute along with DEFAULT while using MODIFY.
(H) Remove an attribute : We can remove attributes from a table by using the following statement.
Syntax: ALTER TABLE table_name
DROP attribute;
Example: ALTER T ABLE GUARDIAN
DROP income;
7
Chapter 9: SQL
(I) Remove primary key from the table :Sometime there may be a requirement to remove primary
key constraint from the table. In that case, Alter table command can be used in the following way:
Syntax: ALTER TABLE table_name
DROP PRIMARY KEY;
Example: ALTER TABLE GUARDIAN
DROP PRIMARY KEY;
DROP Statement : 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: DROP TABLE table_name;
Example: DROP TABLE STUDENT;
Syntax to drop a database: DROP DATABASE database_name;
Example: DROP DATABASE StudentAttendance;
Note: Using the DROP statement to remove a database will ultimately remove all the tables within
it.
SQL FOR D ATA MANIPULATION:
Data manipulation means either insertion of new data, removal of existing data or modification of
existing data.
When a table is created, only its structure is defined but table has no data (rows).
Insert, update, delete commands are used to manipulate data or rows of database table.
The commands under DML are as follows:
INSERT
DELETE
UPDATE
INSERTION of Records: INSERT statement is used to insert new records in a table.
Syntax is:
INSERT INTO tablename
VALUES(value 1, value 2,. ......... );
Here,
Value1 corresponds to attribute1, value2 corresponds to attribute2 and so on.
No need to mention attribute names, if values are inserting for all attributes in the table.
8
Chapter 9: SQL
Example:
INSERT INTO GUARDIAN
VALUES (444444444444, 'Amith Ahuja', 9911492685, ‘New Delhi');
Inserting values for selected or few columns
If we want to insert values only for some of the attributes in a table then we shall
specify the attribute names .
Syntax:
INSERT INTO tablename
(column1, column2,.... )
VALUES (value1, value2,…);
Example:
INSERT INTO SGUARDIAN
(GUID, GName, GAddress)
VALUES (333333333333, 'Danny Dsouza', ‘ Mumbai' );
SQL FOR DATA QUERY:
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
WHERE condition;
Here,
attribute1, attribute2, are the column names of the table table_name.
The FROM clause is written to specify the name of the table from which data is to be
retrieved.
The WHERE clause is optional.
9
Chapter 9: SQL
i) To select all the data available in a table, we use the following select statement:
Syntax:
SELECT * FROM table_name;
Example 1:
SELECT * FROM student;
Example 2:
SELECT * FROM employee;
QUERYING using EMPLOYEE table
Records to be inserted into the EMPLOYEE table
EmpNo Ename Salary Bonus Deptld
101 Aaliya 10000 234 D02
102 Kritika 60000 123 D01
103 Sharan 45000 566 D01
104 Gurpreet 19000 565 D04
105 Joseph 34000 875 D03
106 Sanya 48000 695 D02
107 Vergese 15000 D01
108 Nishchal 29000 D05
109 Darshan 42000 D04
110 Tanya 50000 467 D05
(A) Retrieve selected columns:
The following query selects employee numbers and name of all the employees:
SELECT EmpNo, Ename FROM EMPLOYEE;
B) Renaming of columns: Any column can be renamed while displaying by using the
alias ‘AS’.
Example1:
SELECT EName as Name FROM EMPLOYEE;
Example 2:
SELECT Ename AS Name, Salary*12 AS 'Annual Income’ FROM EMPLOYEE;
C) Distinct Clause:
By default, SQL shows all the data retrieved through query as output.
If there are duplicate values, the SELECT statement combined with DISTINCT clause, returns
records without repetition(distinct records).
10
Chapter 9: SQL
Example: To select unique department number for all the employees, the statement is
as follows:
SELECT DISTINCT DeptId FROM EMPLOYEE;
OUTPUT:
D) WHERE Clause: The WHERE clause is used to retrieve data that meet some specified
conditions.
Example1 : Query to give distinct salaries of the employees of department number D01:
SELECT DISTINCT Salary
FROM EMPLOYEE OUTPUT +- - - - -+
| Salary |
WHERE Deptid='D01'; +- - - - +
| 60000 |
| 45000 |
| 15000 |
+- - - - -+
Example2 :To display all the details of those employees of D04 department who earn
more than 5000.
SELECT * FROM EMPLOYEE
WHERE Salary > 5000 AND DeptId = 'D04';
Example 3: The following query selects records of all the employees except Aaliya.
SELECT * FROM EMPLOYEE
WHERE NOT Ename = 'Aaliya';
Example 4 : Query to select the name and department number of all those employees who
draw salary between 20000 and 50000 (both values inclusive).
SELECT Ename, DeptId FROM EMPLOYEE
WHERE Salary>=20000 AND Salary<=50000;
OR
SELECT Ename, DeptId FROM EMPLOYEE
WHERE Salary BETWEEN 20000 AND 50000;
11
Chapter 9: SQL
Example 5 : Query to select details of all the employees who work in the departments
having deptid D01, D02 or D04.
SELECT * FROM EMPLOYEE
WHERE DeptId = 'D01' OR DeptId = 'D02' OR DeptId = 'D04';
(This query can be written using membership operator IN as follows)
(E) Membership operator IN: The IN operator compares a value with a set of values and
returns true if the value belongs to that set.
Example 1: SELECT * FROM VEMPLOYEE
WHERE DeptId IN ('D01','D02','D04');
Example 2: Query to select details of all the employees except those working in
department number D01 or D02.
SELECT * FROM EMPLOYEE
WHERE DeptId NOT IN('D01','D02');
(F) 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 is used.
Example 1:The following query selects details of all the employees in ascending order
of their salaries.
SELECT * FROM EMPLOYEE ORDER BY Salary;
Example 2: Select details of all the employees in descending order of their salaries.
SELECT * FROM EMPLOYEE ORDER BY Salary DESC;
(G) Handling NULL Values:
SQL supports a special value called NULL to represent a missing or unknown value.
It is important to note that NULL is different from 0 (zero).
Any arithmetic operation performed with NULL value gives NULL.
For example: 5 + NULL = NULL because NULL is unknown hence the result is also unknown.
In order to check for NULL value in a column, we use IS NULL operator.
12
Chapter 9: SQL
Example 1: The following query selects details of all those employees who have not been
given a bonus.
SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
Example 2: The following query selects names of all employees who have been given
a bonus (i.e., Bonus is not null) and works in the department D01.
SELECT EName FROM EMPLOYEE
WHERE Bonus IS NOT NULL AND DeptID = ‘D01’;
(H)Substring pattern matching: The process to find matching of only a few characters or
values in column, instead of exact text or value is called as substring pattern matching.
The = operator cannot be used for substring pattern matching as it is used for exact match.
SQL provides a LIKE operator that can be used with the WHERE clause to search for a specified
pattern in a column.
Wild card characters: The LIKE operator makes use of two wild card characters:
• % (per cent)- used to represent zero, one, or multiple characters
• _ (underscore)- used to represent exactly a single character
Example 1: The following query selects details of all those employees whose name starts
with 'K'
SELECT * FROM EMPLOYEE WHERE Ename like 'K%';
Example 2: The following query selects details of all those employees whose name ends
with 'a', and gets a salary more than 45000.
SELECT * FROM EMPLOYEE WHERE Ename like '%a' AND Salary > 45000;
Example 3: 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.
SELECT * FROM EMPLOYEE WHERE Ename like '_ANYA';
Example 4: The following query selects names of all employees containing 'se' as a
substring in name.
SELECT Ename FROM EMPLOYEE WHERE Ename like '%se%;
13
Chapter 9: SQL
Example 5: The following query selects names of all employees containing 'a' as
the second character.
SELECT EName FROM EMPLOYEE WHERE Ename like '_a%';
DATA UPDATION AND DELETION: Updation and deletion of data are also part of SQL Data
Manipulation Language (DML).
DATA UPDATION: Update statement is used to change the value of one or more columns of existing
records in a table .
Syntax:
UPDATE tablename
SET attribute 1 = value1 attribute2 =value2, ...
WHERE condition;
Example 1:
UPDATE STUDENT SET GUID=333333333333
WHERE Rollnumber=3;
Caution: If we miss the WHERE clause then GUID of all the records will be changed to
333333333333.
Example 2: To update values of two columns Gaddress = ‘ New Delhi’ and Gphone = 9971448855
the following command is used:
UPDATE GUARDIAN SET Gaddress =’New Delhi’ , Gphone = 991448855
WHERE GUID = ‘9971448 855’;
EXAMPLE2: To calculate total marks in a table marks the update statement is as follows:
UPDATE marks
SET total= m1 + m2 + m3 + m4 + m5 + m6;
Data deletion: DELETE statement is used to delete / remove one or more records from a table.
Syntax:
DELETE FROM tablename WHERE condition;
Example: If a student with Rollnumber 2 has left the school. To delete his record following
statement is used:
DELETE FROM student WHERE Rollnumber = 2;
14
Chapter 9: SQL
ASSIGNMENT QUESTIONS:
1. Mention the DDL and DML commands of SQL.
2. Explain the data types used in SQL.
3. Explain different constraints used in SQL.
4. Write the syntax and example for the following commands:
CREATE, DROP, DELETE, INSERT and UPDATE a table.
5. Write the syntax of ALTER command:
a) To add new attribute, d) to modify the value of an attribute
b)to add PRIMARY KEY e) to remove an attribute
c) to modify datatype of an attribute f) to remove PRIMARY KEY.
5. Write the a)Basic syntax and example of SELECT command.
b)Command to display all the contents of the table.
7.Consider the following MOVIE table and write the SQL
queries based on it.
MovieID MovieName Category ReleaseDate ProductionCost BusinessCost
001 Hindi_Movie Musical 2018-04- 124500 130000
23
002 Tamil_Movie Action 2016-05-17 112000 118000
003 English_Movie Horror 2017-08-06 245000 360000
004 Bengali_Movie Adventur 2017-01- 72000 100000
e 04
005 Telugu_Movie Action - 100000 -
006 Punjabi_Movie Comedy - 30500 -
a) Display all the information from the Movie table.
b) List business done by the movies showing
c) only MovieID, MovieName and Total_Earning. Total_ Earning to be calculated as the sum of
ProductionCost and BusinessCost.
d) List the different categories of movies.
e) Find the net profit of each movie showing its MovieID, MovieName and NetProfit. Net Profit
is to be calculated as the difference between Business Cost and Production Cost.
f) List MovieID, MovieName and Cost for all movies with ProductionCost
greater than 10,000 and less than 1,00,000.
g) List details of all movies which fall in the category of comedy or action.
h) List details of all movies which have not been released yet.
15
Chapter 9: SQL
16
Chapter 9: SQL