GATE DA 2024 Notes

GATE
Data Science & AI
SQL
For GATE DA Course & Test
Series

Piyush Wairale
www.piyushwairale.com
Preparing for GATE DA 2025???

www.piyushwairale.com
 Database Management System
by Piyush Wairale

Instructions:
• Kindly go through the lectures/videos on our website www.piyushwairale.com
• Read this study material carefully and make your own handwritten short notes. (Short notes must not be
more than 5-6 pages)

• Attempt the mock tests available on portal.

• Revise this material at least 5 times and once you have prepared your short notes, then revise your short
notes twice a week
• If you are not able to understand any topic or required a detailed explanation and if there are any typos or
mistake in study materials. Mail me at piyushwairale100@gmail.com

1
Contents
1 Datatypes 4
1.1 1. Numeric Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.2 2. Character Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.3 3. Date and Time Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.4 4. Binary Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.5 5. Other Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

2 Constraints 6
2.1 NOT NULL Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.1.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.2 UNIQUE Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.2.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.3 PRIMARY KEY Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.3.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.4 FOREIGN KEY Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.4.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.5 CHECK Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.5.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.6 DEFAULT Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.6.1 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

3 SQL Sub-Languages 8
3.1 Data Manipulation Language (DML) Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.2 Data Definition Language (DDL) Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.3 Data Control Language (DCL) Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
3.4 Querying Data Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
3.5 Joining Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
3.6 Subqueries in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
3.7 Aggregate Functions Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3.8 Set Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3.9 Transaction Control Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

4 EXISTS and NOT EXISTS Operator 13

4.1 EXISTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
4.2 NOT EXISTS Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

5 Subqueries 15
5.1 Single-Row Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.2 Multiple-Row Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.3 Correlated Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.4 Nested Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
5.5 Scalar Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

6 Views 17

7 Triggers 18
7.1 Creating a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
7.2 Trigger Example Explanation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
7.3 Dropping a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

8 Cursors 19
8.1 Declaring a Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
8.2 Cursor Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
8.3 Explanation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
8.4 Calling the Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

2
9 Stored Procedures 20
9.1 Creating a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
9.2 Calling the Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

10 SQL Practice 21

11 GATE CSE PYQs 24

LinkedIn

Youtube Channel

Instagram

Telegram Group

Facebook

Download Andriod App

SQL
Structured Query Language (SQL) is a standard programming language specifically designed for managing
and manipulating relational databases. SQL allows users to create, read, update, and delete database records. It
is widely used in various database systems like MySQL, Oracle, SQL Server, and PostgreSQL.

1 Datatypes
Data types in SQL define the type of data that can be stored in a column of a table. Each column in a table must
have a data type specified, and this data type dictates the kind of data that can be stored and the operations that
can be performed on that data.

Categories of Data Types

SQL data types can generally be categorized into the following groups:

1.1 1. Numeric Data Types

Numeric data types are used to store numerical values. Common numeric data types include:

Data Type Description

INT An integer type that can store whole num-
bers, typically ranging from -2,147,483,648 to
2,147,483,647.
FLOAT A floating-point number that can store deci-
mal values. The precision may vary based on
the implementation.
DOUBLE A double-precision floating-point number,
which can store larger decimal values com-
pared to FLOAT.
DECIMAL(p, s) A fixed-point number that can store exact nu-
meric values with a defined precision (p) and
scale (s).

1.2 2. Character Data Types

Character data types are used to store text values. Common character data types include:

Data Type Description

CHAR(n) A fixed-length string that always contains n
characters, padded with spaces if the input
string is shorter.
VARCHAR(n) A variable-length string that can store up to
n characters. The length can vary for each
entry.
TEXT A variable-length string that can store large
amounts of text data, typically up to 65,535
characters or more, depending on the imple-
mentation.
1.3 3. Date and Time Data Types
These data types are used to store date and time values. Common date and time data types include:

Data Type Description

DATE Stores date values in the format YYYY-MM-DD.
TIME Stores time values in the format HH:MM:SS.
DATETIME Combines date and time values, typically
stored in the format YYYY-MM-DD HH:MM:SS.
TIMESTAMP Stores timestamp values, which represent a
specific point in time. The format is usually
similar to DATETIME.

1.4 4. Binary Data Types

Binary data types are used to store binary data, such as images or files. Common binary data types include:

Data Type Description

BINARY(n) A fixed-length binary string that always con-
tains n bytes.
VARBINARY(n) A variable-length binary string that can store
up to n bytes.
BLOB A binary large object that can store large
amounts of binary data, such as images or files.

1.5 5. Other Data Types

Other specialized data types include:

Data Type Description

ENUM A string object with a value chosen from a list
of permitted values, effectively an enumerated
type.
SET A string object that can have zero or more
values, each of which must be chosen from a
list of permitted values.
JSON A data type used to store JSON (JavaScript
Object Notation) formatted data.
2 Constraints
Constraints are rules applied to table columns to enforce data integrity and ensure the accuracy and reliability of
the data in the database. Constraints can be applied at the column level or the table level.

Types of Constraints
The main types of constraints in SQL are as follows:

2.1 NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain a NULL value. This is used when a field must have
a valid value.

2.1.1 Example
CREATE TABLE employees (
employee_id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
hire_date DATE
);
In this example, the employee id and first name columns cannot contain NULL values.

2.2 UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. A table can have multiple columns with
unique constraints.

2.2.1 Example
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
phone_number VARCHAR(15) UNIQUE
);
Here, both email and phone number columns must contain unique values.

2.3 PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each row in a table. A primary key must contain unique values
and cannot contain NULL values. Each table can have only one primary key.

2.3.1 Example
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
);
In this example, product id serves as the primary key for the products table.

2.4 FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to link two tables together. It ensures that the value in a column (or a set of
columns) matches a value in another table’s primary key or unique column.
2.4.1 Example
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Here, customer id in the orders table references the customer id in the customers table.

2.5 CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition. It can be used to enforce
domain integrity.

2.5.1 Example
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 18)
);

In this case, the age column must have a value greater than or equal to 18.

2.6 DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is specified during an insert operation.

2.6.1 Example
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
stock INT DEFAULT 0
);

Here, if no stock value is provided when inserting a new product, it will default to 0.
3 SQL Sub-Languages
SQL is divided into several sub-languages, each serving different purposes:

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Control Language (DCL)
• Transaction Control Language (TCL)

3.1 Data Manipulation Language (DML) Commands

Command Description Syntax Example

SELECT The SELECT command retrieves SELECT column1, column2 SELECT first name, last name
data from a database. FROM table name; FROM customers;
INSERT The INSERT command adds new INSERT INTO table name (col- INSERT INTO customers
records to a table. umn1, column2) VALUES (value1, (first name, last name) VAL-
value2); UES (’Mary’, ’Doe’);
UPDATE The UPDATE command is used to UPDATE table name SET col- UPDATE employees SET em-
modify existing records in a table. umn1 = value1, column2 = value2 ployee name = ’John Doe’, depart-
WHERE condition; ment = ’Marketing’;
DELETE The DELETE command removes DELETE FROM table name DELETE FROM employees
records from a table. WHERE condition; WHERE employee name = ’John
Doe’;

3.2 Data Definition Language (DDL) Commands

Command Description Syntax Example

CREATE The CREATE command creates a CREATE TABLE table name CREATE TABLE employees
new database and objects, such as (column1 datatype1, column2 (employee id INT PRIMARY
a table, index, view, or stored pro- datatype2, ...); KEY, first name VARCHAR(50),
cedure. last name VARCHAR(50), age
INT);
ALTER The ALTER command adds, ALTER TABLE table name ADD ALTER TABLE customers ADD
deletes, or modifies columns in an column name datatype; email VARCHAR(100);
existing table.
DROP The DROP command is used DROP TABLE table name; DROP TABLE customers;
to drop an existing table in a
database.
TRUNCATE The TRUNCATE command is TRUNCATE TABLE table name; TRUNCATE TABLE customers;
used to delete the data inside a ta-
ble, but not the table itself.
3.3 Data Control Language (DCL) Commands

Command Description Syntax Example

GRANT The GRANT command is used to GRANT SELECT, INSERT ON GRANT SELECT, INSERT ON
give specific privileges to users or table name TO user name; employees TO ’John Doe’;
roles.
REVOKE The REVOKE command is used REVOKE SELECT, INSERT ON REVOKE SELECT, INSERT ON
to take away privileges previously table name FROM user name; employees FROM ’John Doe’;
granted to users or roles.

3.4 Querying Data Commands

Command Description Syntax Example

SELECT The SELECT statement is the pri- SELECT column1, column2 SELECT first name, last name
Statement mary command used to retrieve FROM table name; FROM customers;
data from a database.
WHERE The WHERE clause is used to fil- SELECT * FROM table name SELECT * FROM customers
Clause ter rows based on a specified con- WHERE condition; WHERE age > 30;
dition.
ORDER The ORDER BY clause is used to SELECT * FROM table name SELECT * FROM products OR-
BY Clause sort the result set in ascending or ORDER BY column name DER BY price DESC;
descending order based on a spec- ASC—DESC;
ified column.
GROUP The GROUP BY clause groups SELECT column name, SELECT category, COUNT(*)
BY Clause rows based on the values in a spec- COUNT(*) FROM table name FROM products GROUP BY cat-
ified column. It is often used with GROUP BY column name; egory;
aggregate functions like COUNT,
SUM, AVG, etc.
HAVING The HAVING clause filters SELECT column name, SELECT category, COUNT(*)
Clause grouped results based on a COUNT(*) FROM table name FROM products GROUP BY cat-
specified condition. GROUP BY column name HAV- egory HAVING COUNT(*) > 5;
ING condition;
3.5 Joining Commands

Command Description Syntax Example

INNER The INNER JOIN command re- SELECT * FROM table1 INNER SELECT * FROM employees IN-
JOIN turns rows with matching values in JOIN table2 ON table1.column = NER JOIN departments ON em-
both tables. table2.column; ployees.department id = depart-
ments.id;
LEFT The LEFT JOIN command re- SELECT * FROM table1 LEFT SELECT * FROM employees
JOIN/LEFT turns all rows from the left table JOIN table2 ON table1.column = LEFT JOIN departments ON em-
OUTER (first table) and the matching rows table2.column; ployees.department id = depart-
JOIN from the right table (second table). ments.id;
RIGHT The RIGHT JOIN command re- SELECT * FROM table1 RIGHT SELECT * FROM employees
JOIN/RIGHTturns all rows from the right ta- JOIN table2 ON table1.column = RIGHT JOIN departments ON
OUTER ble (second table) and the match- table2.column; employees.department id = de-
JOIN ing rows from the left table (first partments.id;
table).
FULL The FULL JOIN command re- SELECT * FROM table1 FULL SELECT * FROM employees
JOIN/- turns all rows when there is a JOIN table2 ON table1.column = FULL JOIN departments ON em-
FULL match in either the left table or the table2.column; ployees.department id = depart-
OUTER right table. ments.id;
JOIN
CROSS The CROSS JOIN command com- SELECT * FROM table1 CROSS SELECT * FROM employees
JOIN bines every row from the first table JOIN table2; CROSS JOIN departments;
with every row from the second ta-
ble, creating a Cartesian product.
SELF The SELF JOIN command joins a SELECT * FROM table1 t1, ta- SELECT * FROM employ-
JOIN table with itself. ble1 t2 WHERE t1.column = ees t1, employees t2 WHERE
t2.column; t1.employee id = t2.employee id;
NATURAL The NATURAL JOIN command SELECT * FROM table1 NATU- SELECT * FROM employees
JOIN matches columns with the same RAL JOIN table2; NATURAL JOIN departments;
name in both tables.

3.6 Subqueries in SQL

Command Description Syntax Example

IN The IN command is used to deter- SELECT column(s) FROM table SELECT * FROM customers
mine whether a value matches any WHERE value IN (subquery); WHERE city IN (SELECT city
value in a subquery result. It is FROM suppliers);
often used in the WHERE clause.
ANY The ANY command is used to SELECT column(s) FROM ta- SELECT * FROM prod-
compare a value to any value re- ble WHERE value < ANY (sub- ucts WHERE price < ANY
turned by a subquery. It can query); (SELECT unit price FROM
be used with comparison operators supplier products);
like =, >, <,etc.
ALL The ALL command is used to com- SELECT column(s) FROM table SELECT * FROM orders
pare a value to all values returned WHERE value > ALL (subquery); WHERE order amount > ALL
by a subquery. It can be used with (SELECT total amount FROM
comparison operators like =, >, <, previous orders);
etc.
3.7 Aggregate Functions Commands

Command Description Syntax Example

COUNT() The COUNT command counts the SELECT COUNT(column name) SELECT COUNT(age) FROM
number of rows or non-null values FROM table name; employees;
in a specified column.
SUM() The SUM command is used to cal- SELECT SUM(column name) SELECT SUM(revenue) FROM
culate the sum of all values in a FROM table name; sales;
specified column.
AVG() The AVG command is used to cal- SELECT AVG(column name) SELECT AVG(price) FROM
culate the average (mean) of all FROM table name; products;
values in a specified column.
MIN() The MIN command returns the SELECT MIN(column name) SELECT MIN(price) FROM
minimum (lowest) value in a spec- FROM table name; products;
ified column.
MAX() The MAX command returns the SELECT MAX(column name) SELECT MAX(price) FROM
maximum (highest) value in a FROM table name; products;
specified column.

3.8 Set Operations

Command Description Syntax Example

UNION The UNION operator combines SELECT column1, column2 SELECT first name, last name
the result sets of two or more SE- FROM table1 UNION SELECT FROM customers UNION SE-
LECT statements into a single re- column1, column2 FROM table2; LECT first name, last name
sult set. FROM employees;
INTERSECT The INTERSECT operator re- SELECT column1, column2 SELECT first name, last name
turns the common rows that ap- FROM table1 INTERSECT SE- FROM customers INTERSECT
pear in both result sets. LECT column1, column2 FROM SELECT first name, last name
table2; FROM employees;
EXCEPT The EXCEPT operator returns SELECT column1, column2 SELECT first name, last name
the distinct rows from the left re- FROM table1 EXCEPT SELECT FROM customers EXCEPT
sult set that are not present in the column1, column2 FROM table2; SELECT first name, last name
right result set. FROM employees;
3.9 Transaction Control Commands

Command Description Syntax Example

COMMIT The COMMIT command is used COMMIT; BEGIN TRANSACTION;
to save all the changes made dur-
ing the current transaction and
make them permanent.
-- SQL statements and
changes within the
transaction
INSERT INTO employees (name,
age) VALUES (’Alice’, 30);
UPDATE products SET price
= 25.00 WHERE category =
’Electronics’;
COMMIT;
ROLLBACK The ROLLBACK command is ROLLBACK; BEGIN TRANSACTION;
used to undo all the changes made
during the current transaction and
discard them.
-- SQL statements and
changes within the
transaction
INSERT INTO employees (name,
age) VALUES (’Bob’, 35);
UPDATE products SET price
= 30.00 WHERE category =
’Electronics’;
ROLLBACK;
SAVEPOINT The SAVEPOINT command is SAVEPOINT save- BEGIN TRANSACTION;
used to set a point within a trans- point name;
action to which you can later roll
back.
INSERT INTO employees (name,
age) VALUES (’Carol’, 28);
SAVEPOINT before update;
UPDATE products SET price
= 40.00 WHERE category =
’Electronics’;
ROLLBACK TO before update;
COMMIT;
ROLLBACK TO The ROLLBACK TO SAVE- ROLLBACK TO BEGIN TRANSACTION;
SAVEPOINT POINT command is used to roll SAVEPOINT save-
back to a specific savepoint within point name;
a transaction.
INSERT INTO employees (name,
age) VALUES (’David’, 42);
SAVEPOINT before update;
UPDATE products SET price
= 50.00 WHERE category =
’Electronics’;
ROLLBACK TO SAVEPOINT
before update;
COMMIT;
 SET TRANSAC- The SET TRANSACTION com- SET TRANSACTION BEGIN TRANSACTION;
TION mand is used to configure prop- ISOLATION LEVEL
erties for the current transaction, READ COMMITTED
such as isolation level and transac- — SERIALIZABLE;
tion mode.
-- Set the isolation level
to READ COMMITTED
SET TRANSACTION ISOLATION
LEVEL READ COMMITTED;
INSERT INTO employees (name,
age) VALUES (’Emily’, 35);
UPDATE products SET price
= 60.00 WHERE category =
’Electronics’;
COMMIT;

4 EXISTS and NOT EXISTS Operator

4.1 EXISTS
The EXISTS operator checks whether a subquery returns any rows. If the subquery returns one or more rows, the
EXISTS condition is true; otherwise, it is false.

Syntax
SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

Example
Suppose you have two tables: employees and departments. You want to find all employees who belong to a
department that has more than 10 employees.

SELECT e.first_name, e.last_name

FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.id = e.department_id
AND d.employee_count > 10
);

In this example, the outer query selects employee names only if there exists at least one department (in the
subquery) with more than 10 employees.

4.2 NOT EXISTS Operator

The NOT EXISTS operator checks whether a subquery returns no rows. If the subquery does not return any rows,
the NOT EXISTS condition is true; otherwise, it is false.

Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT EXISTS (subquery);
Example
Using the same employees and departments tables, suppose you want to find all employees who do not belong to
any department.

SELECT e.first_name, e.last_name

FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM departments d
WHERE d.id = e.department_id
);

In this case, the outer query selects employee names only if there does not exist a corresponding department in
the departments table for that employee.

Key Points
• EXISTS is used to determine if a subquery returns any results, while NOT EXISTS is used to determine if a
subquery returns no results.

• Both operators are often used in correlated subqueries, where the subquery references columns from the outer
query.
• Using EXISTS and NOT EXISTS can improve the readability of SQL queries, especially when dealing with
complex conditions involving multiple tables.
5 Subqueries
A subquery, also known as an inner query or nested query, is a query within another SQL query. Subqueries can
return a single value, a single row, or a set of rows and can be used in various clauses of the main query.

Types of Subqueries
Subqueries can be classified into several types based on their structure and the context in which they are used:

5.1 Single-Row Subquery

A single-row subquery returns only one row and one column. It is typically used with comparison operators (like
=, <, >, etc.).

Example
SELECT employee_id, first_name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = ’Sales’
);

In this example, the subquery returns the department ID of the Sales department, and the outer query retrieves
employees from that department.

5.2 Multiple-Row Subquery

A multiple-row subquery can return multiple rows and is typically used with the IN, ANY, or ALL operators.

Example
SELECT first_name, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1000
);

Here, the subquery retrieves department IDs for locations with ID 1000, and the outer query retrieves employee
names from those departments.

5.3 Correlated Subquery

A correlated subquery references columns from the outer query. It cannot be executed independently of the outer
query because it depends on the outer query’s row context.

Example
SELECT employee_id, first_name
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
In this case, the subquery calculates the average salary for the department of each employee in the outer query.

5.4 Nested Subquery

A nested subquery is a subquery within another subquery. This can be used to perform more complex queries.

Example
SELECT first_name, last_name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE location_id = (
SELECT location_id
FROM locations
WHERE city = ’San Francisco’
)
);

Here, the inner subquery retrieves the location ID for San Francisco, which is then used in the second subquery to
find the department ID, allowing the outer query to retrieve the employees from that department.

5.5 Scalar Subquery

A scalar subquery returns a single value (one row and one column) and can be used anywhere an expression can
be used.

Example
SELECT first_name, last_name,
(SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;

In this example, the scalar subquery retrieves the maximum salary from the employees table and includes it in the
result set of the outer query.
6 Views
A View is a virtual table based on the result-set of an SQL statement. Views simplify complex queries, enhance
security by restricting access to certain data, and can present aggregated data.

Creating a View
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
CREATE VIEW HighEarners AS
SELECT Name, Salary
FROM Employee
WHERE Salary > 70000;

Using a View

SELECT * FROM HighEarners;

Updating a View
Some views are updatable, allowing DML operations on them.
Example:
UPDATE HighEarners
SET Salary = Salary + 5000
WHERE Name = ’Alice’;

Dropping a View

DROP VIEW HighEarners;

 Note: Triggers, Cursors Stored Procedures are optional for GATE DA exam

7 Triggers
A Trigger is a stored procedure in the database that automatically invokes (fires) in response to certain events
on a particular table or view. Triggers can be used for enforcing complex business rules, auditing changes, and
maintaining derived data.

7.1 Creating a Trigger

Syntax (MySQL example):
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- Trigger logic
END;
Example: Creating a trigger that logs changes to the Employee table.
First, create a log table:
CREATE TABLE Employee_Log (
Log_ID INT AUTO_INCREMENT PRIMARY KEY,
Emp_ID INT,
Action VARCHAR(10),
Action_Time DATETIME
);
Then, create the trigger:
CREATE TRIGGER Employee_Audit
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
INSERT INTO Employee_Log (Emp_ID, Action, Action_Time)
VALUES (NEW.Emp_ID, ’INSERT’, NOW());
END;

7.2 Trigger Example Explanation

- AFTER INSERT: The trigger activates after an INSERT operation. - FOR EACH ROW: The trigger
executes for each row inserted. - NEW: A reference to the new row being inserted. - NOW(): A function that
returns the current date and time.

7.3 Dropping a Trigger

Syntax:
DROP TRIGGER trigger_name;
Example:
DROP TRIGGER Employee_Audit;
8 Cursors
A Cursor is a database object used to retrieve, manipulate, and navigate through a result set one row at a time.
Cursors are useful when you need to perform row-by-row processing.

8.1 Declaring a Cursor

Syntax:
DECLARE cursor_name CURSOR FOR
SELECT statement;

8.2 Cursor Example

Suppose we want to process each employee’s record individually.
Stored Procedure with Cursor (MySQL example):
DELIMITER //

CREATE PROCEDURE ProcessEmployees()

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE empName VARCHAR(50);
DECLARE empSalary DECIMAL(10,2);
DECLARE cur CURSOR FOR
SELECT Name, Salary FROM Employee;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
FETCH cur INTO empName, empSalary;
IF done THEN
LEAVE read_loop;
END IF;
-- Process each employee
SELECT CONCAT(’Processing ’, empName, ’ with salary ’, empSalary);
END LOOP;

CLOSE cur;
END //

DELIMITER ;

8.3 Explanation
- DECLARE cur CURSOR FOR: Defines the cursor with a SELECT statement. - OPEN cur;: Opens the
cursor. - FETCH cur INTO: Retrieves the next row and stores it into variables. - LOOP: A loop to process
each row fetched. - LEAVE: Exits the loop when there are no more rows. - CLOSE cur;: Closes the cursor after
processing.

8.4 Calling the Procedure

CALL ProcessEmployees();
9 Stored Procedures
A Stored Procedure is a set of SQL statements that can be stored in the database and executed repeatedly.

9.1 Creating a Stored Procedure

Example:
DELIMITER //

CREATE PROCEDURE GetEmployeeByDept (

IN deptName VARCHAR(30)
)
BEGIN
SELECT * FROM Employee
WHERE Dept = deptName;
END //

DELIMITER ;

9.2 Calling the Stored Procedure

CALL GetEmployeeByDept(’HR’);
10 SQL Practice
Consider the schema given below:
1. Find the titles of courses in the Comp. Sci. department that have 3 credits.

SELECT title
FROM course
WHERE dept_name = ’Comp. Sci.’
AND credits = 3;

2. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no
duplicates in the result. This query can be answered in several different ways. One way is as follows:

SELECT DISTINCT student.ID

FROM (student JOIN takes USING(ID))
JOIN (instructor JOIN teaches USING(ID))
USING (course.id, sec.id, semester, year)
WHERE instructor.name = ’Einstein’;

As an alternative to the JOIN ... USING syntax above, the query can be written by enumerating relations
in the FROM clause and adding the corresponding join predicates on ID, course.id, section.id, semester,
and year to the WHERE clause. Note that using natural join in place of JOIN ... USING would result in
equating student ID with instructor ID, which is incorrect.
3. Find the highest salary of any instructor.

SELECT MAX(salary)
FROM instructor;

4. Find all instructors earning the highest salary (there may be more than one with the same salary).

SELECT ID, name

FROM instructor
WHERE salary = (SELECT MAX(salary) FROM instructor);
5. Find the enrollment of each section that was offered in Autumn 2009. One way of writing the query is as
follows:

SELECT course.id, sec.id, COUNT(ID)

FROM section NATURAL JOIN takes
WHERE semester = ’Autumn’
AND year = 2009
GROUP BY course.id, sec.id;

Note that if a section does not have any students taking it, it would not appear in the result. One way of
ensuring such a section appears with a count of 0 is to replace natural join by the natural left outer join
operation, covered later in Chapter 4. Another way is to use a subquery in the SELECT clause, as follows.
6. Find the maximum enrollment, across all sections, in Autumn 2009. One way of writing this query is as
follows:

SELECT max(enrollment)
FROM (SELECT count(ID) AS enrollment
FROM section NATURAL JOIN takes
WHERE semester = ’Autumn’
AND year = 2009
GROUP BY course.id, sec.id);

As an alternative to using a nested subquery in the FROM clause, it is possible to use a WITH clause, as
illustrated in the answer to the next part of this question. A subtle issue in the above query is that if no
section had any enrollment, the answer would be empty, not 0. We can use the alternative using a subquery,
from the previous part of this question, to ensure the count is 0 in this case.

7. Find the sections that had the maximum enrollment in Autumn 2009. The following answer uses a WITH
clause to create a temporary view, simplifying the query.

WITH sec_enrollment AS (
SELECT course.id, sec.id, count(ID) AS enrollment
FROM section NATURAL JOIN takes
WHERE semester = ’Autumn’
AND year = 2009
GROUP BY course.id, sec.id
)
SELECT course.id, sec.id
FROM sec_enrollment
WHERE enrollment = (SELECT max(enrollment) FROM sec_enrollment);

It is also possible to write the query without the with clause, but the subquery to find enrollment would get
repeated twice in the query.
11 GATE CSE PYQs
(Note: Solve the below questions, and you can discuss the answers in our course discussion group.)

Q1
[MCQ] [GATE-2008 : 2M]
Consider the following relational schema:

• Student (school-id, sch-roll-no, sname, saddress)

• School (school-id, name, sch-addres, sch-phone)
• Enrolment (school-id, sch-rollno, erollno, examname)
• ExamResult (erollno, examname, marks)

What does the following SQL query output?

SELECT sch-name, COUNT(*)
FROM School C, Enrolment E, ExamResult R
WHERE E.school-id = C.school-id
AND E.examname = R.examname
AND E.erollno = R.erollno
AND R.marks = 100 AND C.school-id IN
(SELECT school-id FROM student
GROUP BY school-id
HAVING COUNT(*) > 200)
GROUP BY school-id;

Options
(a) for each school with more than 200 students appearing in exams, the name of the school and the number of
100s scored by its students.
(b) for each school with more than 200 students in it, the name of the school and the number of 100s scored by
its students.
(c) for each school with more than 200 students in it, the name of the school and the number of its students
scoring 100 in at least one exam.
(d) nothing; the query has a syntax error.

Q2. [MCQ]
Consider the following relational schema:
• Suppliers(sid: integer, sname: string, city: string, street: string)
• Parts(pid: integer, pname: string, color: string)
• Catalog(sid: integer, pid: integer, cost: real)
Consider the following relational query on the above database:
SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (
SELECT C.sid
FROM Catalog C
WHERE C.pid NOT IN (
 SELECT P.pid
FROM Parts P
WHERE P.color <> ’blue’
)
);
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct
interpretation of the above query?
(a) Find the names of all suppliers who have supplied a non-blue part.
(b) Find the names of all suppliers who have not supplied a non-blue part.
(c) Find the names of all suppliers who have supplied only blue parts.
(d) Find the names of all suppliers who have not supplied only blue parts.

Q3
[MCQ] [GATE-2011 : 2M]
Database table by name Loan Records is given below:
Borrower Bank Manager Loan Amount
Ramesh Sunderajan 10000.00
Suresh Ramgopal 5000.00
Mahesh Sunderajan 7000.00

What is the output of the following SQL query?

SELECT COUNT(*) FROM
((SELECT Borrower, Bank_Manager FROM
Loan_Records) AS S NATURAL JOIN
(SELECT Bank_Manager, Loan_Amount FROM
Loan_Records) AS T);
Options:
(a) 3
(b) 9
(c) 5
(d) 6

Q4
[MCQ] [GATE-2011 : 2M]
Consider a database table T containing two columns X and Y each of type integer. After the creation of the table,
one record (X=1, Y=1) is inserted in the table. Let MX and MY denote the respective maximum values of X and Y
among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128
times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion,
values of MX and MY change.
What will be the output of the following SQL query after the steps mentioned above are carried out?
SELECT Y FROM T WHERE X = 7;
Options:
(a) 127
(b) 255
(c) 129
(d) 257
Q5
[MCQ] [GATE-2012 : 1M]
Which of the following statements are TRUE about an SQL query?
P: An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause.
Q: An SQL query can contain a HAVING clause only if it has a GROUP BY clause.
R: All attributes used in the GROUP BY clause must appear in the SELECT clause.
S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause.
Options:
(a) P and R
(b) P and S
(c) Q and R
(d) Q and S

Q6
[MCQ] [GATE-2012 : 2M]
Consider the following relations A, B, and C:
A
Id Name Age
12 Arun 60
15 Shreya 24
99 Rohit 11

B
Id Name Age
15 Shreya 24
25 Hari 40
98 Rohit 20
99 Rohit 11

C
Id Phone Area
10 2200 02
99 2100 01

How many tuples does the result of the following SQL query contain?
SELECT A.Id
FROM A
WHERE A.Age > ALL (SELECT B.Age
FROM B
WHERE B.Name = ’Arun’);
Options:
(a) 4
(b) 0
(c) 1
(d) 3
Q7
[MCQ] [GATE-2014 : 2M]
SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins.
Which one of the following queries always gives the same answer as the nested query shown below:

Select * from R where a in (select S.a from S)

Options:
(a) select R.* from R, S where R.a = S.a

(b) select distinct R.* from R, S where R.a = S.a

(c) select R.* from R, (select distinct a from S) as S1 where R.a = S1.a
(d) select R.* from R, S where R.a = S.a and is unique R

Q8
[MCQ] [GATE-2014 : 2M]
Given the following schema:
• employees(emp-id, first-name, last-name, hire-date, dept-id, salary)

• departments(dept-id, dept-name, manager-id, location-id)

You want to display the last names and hire dates of all latest hires in their respective departments in the
location ID 1700. You issue the following query:
SELECT last-name, hire-date
FROM employees
WHERE (dept-id, hire-date) IN
(SELECT dept-id, MAX(hire-date)
FROM employees JOIN departments USING(dept-id)
WHERE location-id=1700
GROUP BY dept-id);

What is the outcome?

(a) It executes but does not give the correct result.
(b) It executes and gives the correct result.

(c) It generates an error because of pairwise comparison.

(d) It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.

Q9
[NAT] GATE-2015 : 2M
Consider the following relations:

Roll No Student Name

1 Raj
2 Rohit
3 Raj
 Roll No Course Marks
1 Math 80
1 English 70
2 Math 75
2 English 80
2 Physics 65
3 Math 90

Consider the following SQL query:

SELECT S.Student_Name, SUM(P.Marks)

FROM Student S, Performance P
WHERE S.Roll_No = P.Roll_No
GROUP BY S.Student_Name;
The number of rows that will be returned by the SQL query is:

Q10
[MCQ] [GATE-2014 : 2M]
Consider the following relational schema:

• employee(empId, empName, empDept)

• customer(custId, custName, salesRepId, rating)
salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a
sale to at least one customer.
What does the following query return?
SELECT empName
FROM employee E
WHERE NOT EXISTS
(SELECT custId FROM customer C
WHERE C.salesRepId = E.empId
AND C.rating <> ’GOOD’);
Options:
(a) Names of all the employees with at least one of their customers having a ’GOOD’ rating.

(b) Names of all the employees who at most one of their customers having a ’GOOD’ rating.
(c) Names of all the employees with none of their customers having a ’GOOD’ rating.
(d) Names of all the employees with all their customers having a ’GOOD’ rating