Chapter 5: SQL - Queries and Constraints
Dr. Satendra Kumar
Department of CSE
IIT Patna
CS2202 Database and Warehousing
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 1 / 28
SQL Overview and History
SQL: Structured Query Language (pronounced "sequel" or "S-Q-L")
History: Developed by IBM in 1970s, standardized as SQL-86,
SQL-92, SQL:1999, SQL:2003, SQL:2008, SQL:2011, SQL:2016
ANSI/ISO Standard: Most DBMS follow with some variations
Dialects:
Oracle: PL/SQL
Microsoft: T-SQL
PostgreSQL: PL/pgSQL
MySQL: MySQL SQL (with variations)
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 2 / 28
SQL Components
Data Definition Language (DDL): Define database structure
CREATE, ALTER, DROP tables, views, indexes
Example: CREATE TABLE Aadhaar(...)
Data Manipulation Language (DML): Manipulate data
SELECT, INSERT, UPDATE, DELETE
Example: SELECT * FROM Taxpayer WHERE Income > 1000000
Data Control Language (DCL): Control access
GRANT, REVOKE permissions
Example: GRANT SELECT ON Voter TO ElectionOfficer
Transaction Control: Manage transactions
COMMIT, ROLLBACK, SAVEPOINT
Example: COMMIT after UPI transaction
SQL has different parts for different purposes. DDL for structure, DML for
data, DCL for security.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 3 / 28
Basic SQL Query Structure
Fundamental Form:
SELECT attribute_list
FROM table_list
WHERE condition
GROUP BY grouping_attributes
HAVING group_condition
ORDER BY sort_attributes;
Example - Student Database:
SELECT Name, CGPA
FROM Student
WHERE State = ’Maharashtra’
AND CGPA > 8.5
ORDER BY CGPA DESC;
Clause Order Important: Must follow: SELECT → FROM →
WHERE → GROUP BY → HAVING → ORDER BY
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 4 / 28
SELECT Clause: Specifying Attributes
Purpose: Specify columns to retrieve
Options:
* (asterisk): All columns
Column names: Specific columns
Expressions: Computed columns
Aliases: Rename columns in output
Examples:
SELECT * FROM Customer; All columns
SELECT Name, Phone FROM Customer; Specific
SELECT Name, Salary*12 AS AnnualIncome FROM Employee;
SELECT GSTIN AS BusinessID FROM Business;
Distinct: Eliminate duplicates
SELECT DISTINCT State FROM Student; Unique states
SELECT clause determines what columns appear. Aliases make output
readable.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 5 / 28
FROM Clause: Specifying Tables
Purpose: Specify tables to query from
Single Table: Simple queries
SELECT * FROM Student;
Multiple Tables: Join operations
SELECT [Link], [Link] FROM Student,
Department WHERE [Link] = [Link];
Table Aliases: Short names for tables
SELECT [Link], [Link] FROM Student s, Department d WHERE
[Link] = [Link];
Example:
SELECT [Link], [Link] FROM Customer c, Account a WHERE
[Link] = [Link];
FROM clause specifies data sources. For joins, list all tables. Aliases (s for
Student, d for Department) simplify queries.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 6 / 28
WHERE Clause: Filtering Rows
Purpose: Filter rows based on conditions
Comparison Operators: =, <> or !=, <, >, ≤, ≥
Logical Operators: AND, OR, NOT
Pattern Matching: LIKE with % (any string) and _ (single
character)
Examples:
SELECT * FROM Student WHERE CGPA > 8.5;
SELECT * FROM Voter WHERE Age ≥ 18 AND Constituency =
’Mumbai’;
SELECT * FROM Employee WHERE Designation = ’Manager’ AND
Salary > 50000;
SELECT * FROM Business WHERE Name LIKE ’Reliance%’;
BETWEEN: Range checking
SELECT * FROM Taxpayer WHERE Income BETWEEN 500000 AND
1000000;
WHERE is crucial for filtering.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 7 / 28
NULL Values in SQL
Representation: NULL (not ’NULL’ string)
Meaning: Unknown, missing, or inapplicable value
Comparison with NULL: Always returns UNKNOWN (not TRUE or
FALSE)
Special Operators:
IS NULL: Check for NULL
IS NOT NULL: Check for non-NULL
Examples:
SELECT * FROM Student WHERE MiddleName IS NULL;
SELECT * FROM Customer WHERE Email IS NOT NULL;
Common Mistake:
WRONG: Doesn’t work as expected
SELECT * FROM Student WHERE Phone = NULL;
CORRECT:
SELECT * FROM Student WHERE Phone IS NULL;
NULL handling is critical; always use IS NULL, not = NULL.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 8 / 28
ORDER BY Clause: Sorting Results
Purpose: Sort query results
Options:
ASC: Ascending (default)
DESC: Descending
Multiple columns: Sort by first, then second, etc.
Examples:
SELECT Name, CGPA FROM Student ORDER BY CGPA DESC;
SELECT * FROM Employee ORDER BY Department, Salary DESC;
SELECT Name, Votes FROM Candidate ORDER BY Votes DESC,
Name ASC;
NULL Sorting: NULLs typically come first in ASC, last in DESC
(DBMS dependent!!)
Example: Top 10 taxpayers in Delhi
SELECT Name, Income FROM Taxpayer WHERE State=’Delhi’
ORDER BY Income DESC LIMIT 10;
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 9 / 28
Aggregate Functions
Purpose: Compute summary values over multiple rows
Common Aggregates:
COUNT(*): Count all rows
COUNT(column): Count non-NULL values
SUM(column): Sum of values
AVG(column): Average of values
MIN(column), MAX(column): Minimum, maximum
Examples:
Total voters: SELECT COUNT(*) FROM Voter;
Average income: SELECT AVG(Income) FROM Taxpayer;
Highest CGPA: SELECT MAX(CGPA) FROM Student;
Total deposits: SELECT SUM(Balance) FROM Account;
NULL Handling: Aggregate functions ignore NULL except
COUNT(*)
Election Example:
SELECT COUNT(*) AS TotalVotes FROM Vote WHERE CandidateID
= ’C001’;
Aggregates are essential for reporting. NULLs ignored in SUM/AVG.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 10 / 28
GROUP BY Clause
Purpose: Group rows for aggregate computation per group
Syntax: GROUP BY column1, column2, ...
Examples:
SELECT State, COUNT(*) FROM Student GROUP BY State;
SELECT Department, AVG(Salary) FROM Employee GROUP BY
Department;
SELECT Category, MAX(CGPA) FROM Student GROUP BY
Category;
Rule: All non-aggregated columns in SELECT must be in GROUP BY
Election Analysis:
SELECT Constituency, Party, COUNT(*) AS Votes FROM Vote v,
Candidate c WHERE [Link] = [Link] GROUP BY
Constituency, Party;
GROUP BY creates summaries by category.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 11 / 28
HAVING Clause
Purpose: Filter groups (like WHERE but for groups)
Difference from WHERE:
WHERE filters rows before grouping
HAVING filters groups after grouping
Examples:
SELECT State, COUNT(*) FROM Student GROUP BY State
HAVING COUNT(*) > 1000;
SELECT Department, AVG(Salary) FROM Employee GROUP BY
Department HAVING AVG(Salary) > 50000;
SELECT Category, AVG(CGPA) FROM Student GROUP BY Category
HAVING AVG(CGPA) > 8.0;
Can use aggregates: HAVING can use aggregate functions, WHERE
cannot
Election Example: Constituencies with close contests
SELECT Constituency FROM Vote GROUP BY Constituency HAVING
MAX(Votes) - MIN(Votes) < 1000;
HAVING filters group results.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 12 / 28
SQL Joins: INNER JOIN
Purpose: Combine related rows from multiple tables
Types:
INNER JOIN: Only matching rows
LEFT/RIGHT/FULL OUTER JOIN: Include non-matching rows
CROSS JOIN: All combinations
INNER JOIN Syntax:
SELECT columns FROM table1 INNER JOIN table2 ON [Link]
= [Link];
Examples:
SELECT [Link], [Link] FROM Student s INNER JOIN
Department d ON [Link] = [Link];
SELECT [Link], [Link], [Link] FROM Customer c INNER
JOIN Account a ON [Link] = [Link];
Traditional Syntax: Using WHERE (still common)
SELECT [Link], [Link] FROM Student s, Department d
WHERE [Link] = [Link];
JOINs are fundamental. INNER JOIN gets matches only. Modern syntax
uses JOIN ON, and traditional uses WHERE.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 13 / 28
OUTER JOINS in SQL
LEFT OUTER JOIN: All rows from left table, matching from right
SELECT [Link], [Link] FROM Customer c LEFT OUTER
JOIN Account a ON [Link] = [Link];
Shows customers without accounts too
RIGHT OUTER JOIN: All rows from right table, matching from left
SELECT [Link], [Link] FROM Product p RIGHT OUTER JOIN
Sale s ON [Link] = [Link];
Shows sales even if product deleted
FULL OUTER JOIN: All rows from both tables
SELECT [Link], [Link] FROM Student s FULL OUTER
JOIN Scholarship sc ON [Link] = [Link];
Example: Find customer names without loans
SELECT [Link] FROM Customer c LEFT OUTER JOIN Loan l ON
[Link] = [Link] WHERE [Link] IS NULL;
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 14 / 28
Nested Queries (Subqueries)
Definition: Query within another query
Types:
Scalar subquery: Returns a single value
Row subquery: Returns single row
Table subquery: Returns table (multiple rows)
Examples:
Students with above-average CGPA: SELECT Name, CGPA FROM
Student WHERE CGPA > (SELECT AVG(CGPA) FROM Student);
Products never sold: SELECT ProductName FROM Product WHERE
ProductID NOT IN (SELECT ProductID FROM Sale);
Correlated Subquery: References outer query
Employees earning more than department average: SELECT [Link],
[Link] FROM Employee e WHERE Salary > (SELECT AVG(Salary)
FROM Employee WHERE Department = [Link]);
Subqueries are powerful but can be slow. Correlated subqueries are
executed for each row and can be expensive.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 15 / 28
Set Operations: UNION, INTERSECT, EXCEPT
UNION: Combine results, remove duplicates
All students
SELECT Name FROM Undergraduate UNION SELECT Name FROM
Postgraduate;
UNION ALL: Combine with duplicates
INTERSECT: Common rows
Customers with both account types
SELECT CustID FROM SavingsAccount INTERSECT SELECT CustID
FROM LoanAccount;
EXCEPT (MINUS in Oracle): Rows in first not in second
Customers without loans
SELECT CustID FROM Customer EXCEPT SELECT CustID FROM
Loan;
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 16 / 28
Data Modification: INSERT
Purpose: Add new rows to table
Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1,
value2, ...);
Examples:
INSERT INTO Student (RollNo, Name, State, CGPA) VALUES
(’CS2023001’, ’Amit Sharma’, ’MH’, 8.7);
INSERT INTO Account (AccountNo, CustID, Balance) VALUES
(’1234567890’, ’C001’, 50000.00);
Insert Multiple Rows:
INSERT INTO Student VALUES (’CS2023001’, ’Amit’, ’MH’, 8.7),
(’CS2023002’, ’Priya’, ’TN’, 9.2), (’CS2023003’, ’Raj’, ’UP’, 8.5);
Insert through Query:
INSERT INTO Alumni (Name, GraduationYear) SELECT Name, 2023
FROM Student WHERE CGPA > 8.0;
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 17 / 28
Data Modification: UPDATE
Purpose: Modify existing rows
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;
Examples:
Give 10% raise to all Mumbai employees
UPDATE Employee SET Salary = Salary * 1.10 WHERE City =
’Mumbai’;
Update student CGPA
UPDATE Student SET CGPA = 9.0 WHERE RollNo = ’CS2023001’;
Update based on other table
UPDATE Account a SET Balance = Balance + 1000 WHERE CustID
IN (SELECT CustID FROM Customer WHERE Category =
’SeniorCitizen’);
Critical: WHERE clause! Without it, updates ALL rows
UPDATE modifies data. Test with SELECT first!
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 18 / 28
Data Modification: DELETE
Purpose: Remove rows from table
Syntax:
DELETE FROM table_name WHERE condition;
Examples:
Delete inactive accounts
DELETE FROM Account WHERE LastTransactionDate <
’2022-01-01’;
Delete failed students
DELETE FROM Student WHERE CGPA < 4.0;
Delete using subquery
DELETE FROM Cart WHERE ProductID IN (SELECT ProductID
FROM Product WHERE Discontinued = 1);
TRUNCATE: Faster, removes all rows, cannot rollback
TRUNCATE TABLE TempData; Removes all rows
Critical: WHERE clause! Without it, deletes ALL rows
DELETE removes rows use WITH care! TRUNCATE faster for deleting all
rows but no rollback.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 19 / 28
Constraints in SQL
Purpose: Enforce data integrity rules
Types:
NOT NULL: Column cannot be NULL
UNIQUE: Column values must be unique
PRIMARY KEY: Unique identifier, implies NOT NULL
FOREIGN KEY: References primary key in another table
CHECK: Custom condition
DEFAULT: Default value when not specified
Examples:
CREATE TABLE Student (
RollNo VARCHAR(10) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
CGPA DECIMAL(3,2) CHECK (CGPA BETWEEN 0 AND 10),
State VARCHAR(50) DEFAULT ’Maharashtra’
);
Constraints ensure data quality. Automatically enforced by DBMS.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 20 / 28
Constraints in SQL
Foreign Key:
CREATE TABLE Enrollment (
StID VARCHAR(10) REFERENCES Student(RollNo),
CourseID VARCHAR(10) REFERENCES Course(CourseID)
);
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 21 / 28
PRIMARY KEY Constraint
Purpose: Uniquely identify each row
Characteristics:
Values must be unique
Cannot be NULL
Only one per table (but can be composite)
Syntax:
Column level
CREATE TABLE Student (RollNo VARCHAR(10) PRIMARY KEY, ...);
Table level (composite key)
CREATE TABLE Enrollment (StID VARCHAR(10), CourseID
VARCHAR(10), PRIMARY KEY (StID, CourseID));
Examples:
CREATE TABLE Aadhaar (AadhaarNumber CHAR(12) PRIMARY
KEY, Name VARCHAR(100) NOT NULL, ...);
CREATE TABLE PAN (PAN CHAR(10) PRIMARY KEY, Name
VARCHAR(100));
The PRIMARY KEY is fundamental. DBMS creates the index
automatically.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 22 / 28
FOREIGN KEY Constraint
Purpose: Maintain referential integrity between tables
Syntax:
CREATE TABLE Enrollment (
StID VARCHAR(10),
CourseID VARCHAR(10),
FOREIGN KEY (StID) REFERENCES Student(RollNo),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 23 / 28
FOREIGN KEY Constraint
Referential Actions: ON DELETE and ON UPDATE
CREATE TABLE Account (
AccountNo VARCHAR(20) PRIMARY KEY,
CustID VARCHAR(10),
FOREIGN KEY (CustID) REFERENCES Customer(CustID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Banking Example:
CREATE TABLE Transaction (
TransID INT PRIMARY KEY,
FromAccount VARCHAR(20) REFERENCES Account(AccountNo),
ToAccount VARCHAR(20) REFERENCES Account(AccountNo),
Amount DECIMAL(12,2)
);
FOREIGN KEY maintains relationships. CASCADE deletes/updates related rows.
RESTRICT prevents if references exist.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 24 / 28
CHECK Constraint
Purpose: Enforce domain-specific rules
Syntax:
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10,2) CHECK (Salary > 0),
Age INT CHECK (Age >= 18 AND Age <= 65),
Gender CHAR(1) CHECK (Gender IN (’M’, ’F’, ’O’))
);
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 25 / 28
CHECK Constraint
Examples:
CREATE TABLE Voter (
VoterID VARCHAR(20) PRIMARY KEY,
Age INT CHECK (Age >= 18),
State VARCHAR(50) CHECK (State IN
(’Maharashtra’, ’Tamil Nadu’, ...))
);
CREATE TABLE Taxpayer (
PAN CHAR(10) PRIMARY KEY,
Income DECIMAL(12,2) CHECK (Income >= 0),
Category VARCHAR(10) CHECK (Category IN
(’Individual’, ’HUF’, ’Company’))
);
Complex CHECK: Can reference multiple columns
CHECK (EndDate > StartDate)
CHECK (RetirementAge - Age > 0)
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 26 / 28
Views in SQL
Purpose: Virtual table based on query result
Benefits:
Security: Hide sensitive columns
Simplicity: Complex query as simple table
Consistency: Standardized data access
Syntax:
CREATE VIEW view_name AS SELECT columns FROM tables
WHERE conditions;
Examples:
Secure view: Hide salary
CREATE VIEW EmployeePublic AS SELECT EmpID, Name,
Department FROM Employee;
Simplified view
CREATE VIEW StudentDetails AS SELECT [Link], [Link],
[Link] FROM Student s JOIN Department d ON [Link] =
[Link];
Views are virtual tables and can simplify complex queries.
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 27 / 28
Updatable Views
Definition: Views that allow INSERT/UPDATE/DELETE
Requirements:
Based on single table
Contains primary key
No aggregates, DISTINCT, GROUP BY, HAVING
No subqueries in certain positions
Example:
CREATE VIEW MumbaiEmployees AS SELECT EmpID, Name, Salary,
Department FROM Employee WHERE City = ’Mumbai’;
This view is updatable
Update through view
UPDATE MumbaiEmployees SET Salary = Salary * 1.10 WHERE
Department = ’Sales’;
Non-updatable Example:
CREATE VIEW DeptSalarySummary AS SELECT Department,
AVG(Salary) AS AvgSalary FROM Employee GROUP BY Department;
NOT updatable (has aggregate)
Dr. Satendra Kumar (IIT Patna) Chapter 05 CS2202 28 / 28