Structured
Query
Language
SQL
M S . Navajyothi Katela
What is • SQL stands for Structured Query Language.
SQL? • It is used for accessing, manipulating, and
managing data in databases.
• SQL was developed in the 1970s at IBM
• Use cases
o SQL is used for querying data, updating
records, creating and modifying database
structures, and controlling access to data.
2
SQL Standards
• American National Standards Institute (ANSI)
• International Organization for Standardization
(ISO) affiliated with International Electrotechnical Commission (IEC)
• SQL is a common language for all relational databases
3
Key Features • Declarative Language
of SQL • Relational Model
• Portability
• Standardization
• Comprehensive
4
SQL Query
Types Data Definition Language
Data Manipulation Language
Data Control Language
Transaction Control Language
5
SQL Query Types
• Data Definition Language (DDL):
o Used to define and modify database structures.
o Commands include CREATE, ALTER, DROP
• Data Manipulation Language (DML):
o Used to manipulate data within existing structures.
o Commands include SELECT, INSERT, UPDATE, DELETE.
6
SQL Query Types
• Data Control Language (DCL):
o Used to control access to data in the database.
o Commands include GRANT, REVOKE.
• Transaction Control Language (TCL):
o Used to manage transactions in the database.
o Commands include COMMIT, ROLLBACK, SAVEPOINT.
7
Data Definition Language (DDL): Creating Tables
• For new entities and relationships, new database table (relation) can
be created using CREATE TABLE command
• Syntax:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
columnN datatype constraints
);
8
Data Types in SQL
• INTEGER: Whole numbers.
• FLOAT/DOUBLE: Floating-point numbers.
• VARCHAR(size): Variable-length character strings.
• CHAR(size): Fixed-length character strings.
• DATE: Date values.
• BOOLEAN: True/false values
9
Common Constraints in SQL
• PRIMARY KEY: Uniquely identifies each record in the table.
• FOREIGN KEY: Ensures referential integrity by linking to the primary key of
another table.
• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column are unique.
• CHECK: Ensures that values in a column meet a specific condition.
• DEFAULT: Sets a default value for a column when no value is specified.
10
Example - Creating a Simple Table
CREATE TABLE employees (
employee_idINT PRIMARY KEY,
first_nameVARCHAR(50),
last_nameVARCHAR(50),
hire_dateDATE,
age INT
); 11
Example - Table with Constraints
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_nameVARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE,
enrollment_date DATE DEFAULT CURRENT_DATE,
grade FLOAT CHECK (grade >= 0 AND grade <= 100)
);
12
Example - Creating Tables (Result)
13
Data Definition Language (DDL): Altering Tables
• ALTER TABLE command is used to add, modify, or delete columns in an
existing table.
• Syntax:
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
***The specific syntax can vary slightly depending on the database system (e.g., MySQL, PostgreSQL, SQL Server)
14
Example - Altering Table
ALTER TABLE employees ADD Email varchar(255);
ALTER TABLE students DROP COLUMN enrollment_date;
ALTER TABLE employees ALTER COLUMN age float;
ALTER TABLE employees RENAME COLUMN hire_date to DOJ;
15
Data Definition Language (DDL): Dropping Tables
• DROP TABLE command is used to delete an existing table
• Syntax:
DROP TABLE table_name;
• Example:
DROP TABLE students;
16
Data Definition Language (DDL): Dropping Tables
• TRUNCATE TABLE command is used remove all records from a table, but not
the table itself
• Syntax:
TRUNCATE TABLE table_name;
• Example:
TRUNCATE TABLE employees;
17
Set operations
Set Operations combine the results of two or more SELECT statements into a
single result. They are based on Mathematical Set Theory.
SQL Set Operators:
UNION
UNION ALL
INTERSECT
EXCEPT (or MINUS in Oracle)
Conditions for Set Operations
Before using set operations:
✔ Number of columns must be the same.
✔ Data types should be compatible.
✔ Column order should be the same.
Set operator
Example
Query 1
SELECT Student_ID, Name
FROM Student;
Query 2
SELECT Student_ID, Name
FROM Alumni;
UNION Operator
Definition
Combines two result sets and removes duplicate rows.
Syntax:
SELECT column_list
FROM Table1
UNION
SELECT column_list
FROM Table2;
Student Table Alumni Table
Student_ID Name Student_ID Name
101 Amit 102 Priya
102 Priya 103 Rahul
Query:
SELECT Name Result:
FROM Student Amit
Priya
UNION Rahul
SELECT Name
FROM Alumni; Duplicate "Priya" is removed.
UNION ALL
Returns all rows including duplicates .
Result:
Syntax:
SELECT Name
Amit
FROM Student
Priya
Priya
UNION ALL
Rahul
SELECT Name
Duplicates are NOT removed.
FROM Alumni;
INTERSECT Operator
Definition
Returns only common rows from both queries.
Syntax:
SELECT Name
FROM Student
INTERSECT
SELECT Name
FROM Alumni;
Output:
Priya
Only common records are displayed.
EXCEPT Operator(Minus)
Returns rows from the first query that are not present in the second query.
Syntax:
SELECT Name
FROM Student
EXCEPT
SELECT Name
FROM Alumni;
Output:
Amit
Data Manipulation Language (DML) Commands
• INSERT: Add new records to a table
• UPDATE: Modify existing records in a table
• DELETE: Remove records from a table
• SELECT: Retrieve records from a table
18
Data Manipulation Language (DML) Commands
• INSERT INTO
o It is used to add new rows to a table
o It allows for both single and multiple row inserts
o This command is essential for populating tables with data.
19
Data Manipulation Language (DML) Commands
• Syntax: for inserting single row of data
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
• Syntax: for inserting multiple rows of data
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...),
(value3, value4, ...);
20
Example – Inserting Single Row
• Let's consider this table Employee
INSERT INTO Employees (ID, Name, Position, Age)
VALUES (1, 'John Doe', 'Manager', 38);
21
Example – Inserting Multiple Rows
INSERT INTO Employees (ID, Name, Position)
VALUES (2, 'Jane Smith', 'Developer'),
(3, 'Jim Brown', 'Analyst');
22
Data Manipulation Language (DML) Commands
• Syntax: for inserting without specifying columns
INSERT INTO table_name
VALUES (value1, value2, ...);
• Note: The order of values must match the table's column order
25
Example - Inserting without specifying columns
INSERT INTO Employees
VALUES (4, 'Emma Green', 'Designer', 25)
(5, 'Bode Locke', 'Manager', 36);
26
Data Manipulation Language (DML) Commands
• Syntax: Inserting Default values
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, DEFAULT, value2, ...);
o Note: The order of default values must match with the
corresponding default column order of the table.
27
Example - Inserting default values
INSERT INTO EmployeesInfo (ID, Name, Salary)
VALUES (10, 'Sam White', DEFAULT);
28
Data Manipulation Language: Selecting Rows
• The SELECT statement is used to query the database and retrieve data.
• Syntax:
SELECT column1, column2, ...
FROM table_name;
• Selecting specific columns from a a table:
SELECT movie, genre, releaseyear
FROM MovieMania;
29
Data Manipulation Language: Selecting Rows
• Selecting all columns
SELECT *
FROM table_name;
Example
SELECT *
FROM MovieMania;
30
Data Manipulation Language: Selecting Rows
• SQL Clauses
1. WHERE
2. ORDER BY
3. GROUP BY
4. HAVING
31
Data Manipulation Language: Selecting Rows
• WHERE Clause filters data
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example
SELECT movie, releaseyear, genre
FROM MovieMania
WHERE actor = ‘Aamir Khan’;
32
Data Manipulation Language: Selecting Rows
• WHERE Clause
o AND, OR, NOT operators are used to combine multiple conditions to filter
data more precisely.
SELECT movie, releaseyear, genre
FROM MovieMania
WHERE actor = ‘Aamir Khan’ AND releaseyear >1990;
33
Data Manipulation Language: Selecting Rows
• WHERE Clause
o AND, OR, NOT operators are used to combine multiple conditions to filter
data more precisely.
SELECT movie, releaseyear, genre
FROM MovieMania
WHERE actor = ‘Aamir Khan’ AND releaseyear >1990;
34
Data Manipulation Language: Selecting Rows
• WHERE Clause
• More complex clauses can be constructed by joining numerous AND or OR logical keywords
• some useful operators that you can use for numerical data
35
Data Manipulation Language: Selecting Rows
SELECT movie, actor, genre
FROM MovieMania
WHERE releaseyear BETWEEN 2000 AND 2005;
SELECT movie, actor, genre
FROM MovieMania
WHERE releaseyear IN (2000, 2001, 2003, 2004);
36
Data Manipulation Language: Selecting Rows
• WHERE Clause
• When writing WHERE clauses with columns containing text data, SQL supports a
number of useful operators
• All strings must be quoted so that the query parser can distinguish words in the
string from SQL keywords.
37
38
Data Manipulation Language: Selecting Rows
SELECT movie, releaseyear SELECT movie, releaseyear
FROM MovieMania FROM MovieMania
WHERE actor = ‘Aamir Khan’; WHERE actor != ‘Aamir Khan’;
SELECT movie, releaseyear SELECT movie, releaseyear
FROM MovieMania FROM MovieMania
WHERE actor LIKE ‘Aamir Khan’; WHERE actor NOT LIKE ‘Aamir Khan’;
39
Data Manipulation Language: Selecting Rows
SELECT movie, actor, genre SELECT movie, releaseyear
FROM MovieMania FROM MovieMania
WHERE movie LIKE ‘%an%’; WHERE actor IN (‘Aamir Khan’, ‘Tom
Cruise’, ‘Tom Hanks’);
SELECT movie, actor, genre
SELECT movie, releaseyear
FROM MovieMania
FROM MovieMania
WHERE movie LIKE ‘%an_’;
WHERE actor NOT IN (‘Aamir Khan’,
‘Tom Cruise’, ‘Tom Hanks’);
40
Data Manipulation Language: Selecting Rows
• ORDER BY Clause
• Sorts the retrieved data by column in ascending order.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC];
• Example
SELECT movie, releaseyear
FROM MovieMania
ORDER BY releaseyear ASC;
41
Data Manipulation Language: Selecting Rows
• GROUP BY Clause
• Groups rows that have the same values in the specified column and counts them.
SELECT column1, aggregate_function(column2,...)
FROM table_name
GROUP BY column1;
• Example
SELECT genre, count(*)
FROM MovieMania
GROUP BY genre;
42
Data Manipulation Language: Selecting Rows
• HAVING Clause
• Filters the groups based on a condition after grouping.
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING condition;
• Example
SELECT genre, count(*)
FROM MovieMania
GROUPBY genre
HAVING count(*)>10;
43
Data Manipulation Language: Selecting Rows
• Limiting no. of rows returned
SELECT column1, column2,…
FROM table_name
LIMIT number;
• Example
SELECT movie, releaseyear, genre
FROM MovieMania
LIMIT 10;
Retrieves only the first 10 rows from the MovieMania table.
44
Data Manipulation Language: Updating Rows
• What is an UPDATE Query?
• The UPDATE query is used to modify existing records in a table.
• It allows you to change values in one or more columns based on specified
conditions.
• Why Use UPDATE?
• Correct data entry errors.
• Update outdated information.
• Reflect changes in data over time.
45
Data Manipulation Language: Updating Rows
• Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
• Example
UPDATE employees
SET salary = 50000
WHERE employee_id = 101;
Note: Without the WHERE clause, all records in the table will be updated!
46
Data Manipulation Language: Updating Rows
• Using Multiple Columns and Conditions
• Multiple columns can be updated [Link] can be combined using AND,
OR, and other logical operators.
UPDATE employee
SET position = ‘Manager’, salary = 100000
WHERE id = 4;
UPDATE employee
SET position = ‘Manager’, salary = 100000
WHERE id > 4 AND age > 35 ;
47
Data Manipulation Language: Updating Rows
• Returning Updated Rows:
• Some SQL databases support returning updated rows using RETURNING or
similar clauses.
UPDATE employee
SET salary = 65000
WHERE id = 4
RETURNING id, salary;
48
Data Manipulation Language: Deleting Rows
• What is a DELETE Query?
• The DELETE query is used to remove records from a table in a database.
• It can delete one or multiple records based on specified conditions.
• Why Use DELETE?
• To remove obsolete or incorrect data.
• To maintain data integrity and reduce database size.
49
Data Manipulation Language: Deleting Rows
• Syntax
DELETE FROM table_name
WHERE condition;
• Example
DELETE FROM employee
WHERE id = 101;
Note: Without the WHERE clause, all records in the table will be deleted!
50
Data Manipulation Language: Deleting Rows
• Deleting multiple records
DELETE FROM employee
WHERE position = ‘Manager’;
• Using multiple conditions
DELETE FROM employee
WHERE position = ‘Manager’ AND age > 35;
51
Data Control Language: Controlling Access
• What is DCL?
o Data Control Language (DCL) consists of commands used to control access to data in a database.
o DCL focuses on permissions and access rights.
• Why Use DCL?
o Manage user privileges.
o Ensure data security and integrity.
• Primary DCL Commands:
o GRANT: Assign privileges.
o REVOKE: Remove privileges.
52
Data Control Language: Granting Access
• Assign specific privileges to users or roles.
• Syntax
GRANT privilege_name ON object_name TO user_name [WITH
GRANT OPTION];
• Privileges:
• Examples include SELECT, INSERT, UPDATE, DELETE, etc.
53
Data Control Language: Granting Access
• Example
GRANT SELECT ON employee TO John;
• Grants the SELECT privilege on the employee table to the user John.
• Best Practice: Grant only necessary privileges.
• Granting Multiple Privileges
GRANT SELECT, INSERT ON employee TO John;
54
Data Control Language: Granting Access
• WITH GRANT Option
Allows the grantee to grant the same privileges to others.
• Syntax
GRANT SELECT ON employees TO john WITH GRANT OPTION;
• This option must be used cautiously to avoid uncontrolled privilege
propagation.
55
Data Control Language: Removing Access
• REVOKE
Removes specific privileges from users or roles.
• Syntax
REVOKE privilege_name ON object_name FROM user_name;
• This option is used when a user no longer needs access to certain data.
56
Data Control Language: Removing Access
• Example
REVOKE SELECT ON employee FROM John;
• Revoking multiple privileges
REVOKE SELECT, INSERT ON employee FROM John;
• Revoking Privileges from Multiple Users
REVOKE SELECT ON employees FROM John, Jane;
57
Explain the GRANT command in SQL. How is it used to provide permissions to users
or roles? Provide an example of granting SELECT and INSERT permissions.
SQL Aggregation
• Aggregation in SQL allows users to perform calculations on multiple rows of a
dataset.
• Aggregate functions are often used with GROUP BY to perform operations like
counting, summing, or averaging within each group.
• Syntax for Group By
SELECT column1, aggregate_function(column2)
FROM table
WHERE condition
GROUP BY column1;
58
SQL Aggregation
• Common Aggregate Functions:
o COUNT(): Counts the number of rows.
o SUM(): Sums the values.
o AVG(): Calculates the average.
o MAX(): Finds the maximum value.
o MIN(): Finds the minimum value.
59
SQL Aggregation
• Example
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
60
SQL Aggregation
• The HAVING clause is used to filter groups based on conditions. It is similar to the
WHERE clause but is used after the grouping operation.
• Syntax for Group By
SELECT column1, aggregate_function(column2)
FROM table
WHERE condition
GROUP BY column1
HAVING aggregate_function(column2) condition;
69
SQL Aggregation
• Example
SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department
HAVING COUNT(employee_id) > 10;
69
SQL Aggregation
• Multiple Columns in GROUP BY
SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY department, job_title;
69
[Link] table students_academics_details with the following columns.
Rollno, Student_name, Class, Division, Faculty, Marks_subject1, Marks_subject2,
Marks_subject3.following queries:
[Link] average marks in each subject faculty wise.
[Link] minimum, maximum and average marks of m1, m2, m3 subject.
Q2. Consider table students_academics_details with the following [Link], Student_name, Class,
Division, Faculty, Marks_subject1, Marks_subject2, Marks_subject3.
following queries:
[Link] the number of students in each division of each class.
[Link] sum of all the m1, m2, m3 subject marks.
[Link] are aggregate functions in SQL, and what is their primary purpose? Explain with examples
[Link] and explain all Aggregate function.
[Link] the following database Employee(emp_id, emp_name, emp_city, emp_addr, emp_dept,
join_date).i) Display the emp_id, of employee who live in city 'Pune' or 'Nagpur'.ii)ii) Change employee
name, 'Aayush' to 'Aayan'.
[Link] importance of clauses in Data manipulation language.
Q6. Consider the following database Employee(emp_id, emp_name,
emp_city, emp_addr, emp_dept, join_date).i) Display the emp_id, of
employee who live in city 'Pune' or 'Nagpur'.ii)ii) Change employee name,
'Aayush' to 'Aayan’.
Q7. How does the UPDATE command work in SQL, and what precautions should
be taken to avoid unintended modifications? Provide an example of updating
specific rows in a table.