Week 1

Creating and modifying database table using DDL and DML commands.

Data Definition Language (DDL) in SQL

Questions 1:
(a) Create a Table
Create a table named Students with the following fields:
StudentID (Primary Key), Name, Age, Class, Email.
(b) Alter a Table
Add a column PhoneNumber to the Students table.
Modify the data type of Age to TINYINT.
Drop the column Email from the Students table.
(c ) Drop a Table
Drop the Students table from the database.
(d ) Rename a Table
Rename the Students table to StudentRecords.

(a) Create a Table

CREATE TABLE Students (

StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Class VARCHAR(50),
Email VARCHAR(100)
);

(b) Alter a Table

1. Add a column PhoneNumber to the Students table:

ALTER TABLE Students

ADD PhoneNumber VARCHAR(15);

2. Modify the data type of Age to TINYINT:

ALTER TABLE Students

MODIFY Age TINYINT;

3. Drop the column Email from the Students table:

ALTER TABLE Students
DROP COLUMN Email;

(c) Drop a Table

DROP TABLE Students;

(d) Rename a Table

RENAME TABLE Students TO StudentRecords;

Question 2:
(a) Create a Table with Constraints
Create a table named Courses with the following fields:
CourseID (Primary Key), CourseName (Unique), Credits (Default value 3),
InstructorID (Foreign Key referencing Instructors table).

(b) Check Constraint

Create a table Employees with a check constraint to ensure the Salary is
greater than 5000.
(c) Unique Constraint
Add a unique constraint on the Email column of the Employees table.
(a) Create a Table with Constraints

CREATE TABLE Courses (

CourseID INT PRIMARY KEY,
CourseName VARCHAR(100) UNIQUE,
Credits INT DEFAULT 3,
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
b) Check Constraint

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10, 2),
CONSTRAINT chk_Salary CHECK (Salary > 5000)
);

(c) Unique Constraint

ALTER TABLE Employees

ADD CONSTRAINT unique_email UNIQUE (Email);

Question 3:
(a) Design and Create Tables for a Library Database
Create tables Books, Members, and Transactions with appropriate fields
and relationships.
(b) Implement Referential Integrity
Create a Department table and an Employee table.
Ensure referential integrity such that every Employee belongs to a valid
Department.
(c) Create a Table with a Composite Primary Key
Create a Marks table with a composite primary key on StudentID and
SubjectCode.
(d) Perform DDL Statements for Migration
Write DDL commands to copy the structure of a table OldData into a new
table
NewData without copying its data.

(a) Design and Create Tables for a Library Database

CREATE TABLE Books (

BookID INT PRIMARY KEY,
Title VARCHAR(255),
Author VARCHAR(100),
Genre VARCHAR(50),
PublishedYear INT
);

CREATE TABLE Members (

MemberID INT PRIMARY KEY,
FullName VARCHAR(100),
DateOfBirth DATE,
Email VARCHAR(100) UNIQUE,
PhoneNumber VARCHAR(15)
);

CREATE TABLE Transactions (

TransactionID INT PRIMARY KEY,
MemberID INT,
BookID INT,
IssueDate DATE,
ReturnDate DATE,
FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

(b) Implement Referential Integrity

CREATE TABLE Department (

DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY,
FullName VARCHAR(100),
Email VARCHAR(100) UNIQUE,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);
(c) Create a Table with a Composite Primary Key

CREATE TABLE Marks (

StudentID INT,
SubjectCode VARCHAR(10),
MarksObtained DECIMAL(5, 2),
PRIMARY KEY (StudentID, SubjectCode)
);

(d) Perform DDL Statements for Migration

CREATE TABLE NewData AS

SELECT * FROM OldData WHERE 1 = 0;
Data Manipulation Language (DML) in SQL

Questions 1:

(a) Insert Data into a Table

Insert a record into the Students table with the following details:
StudentID = 1, Name = “John Doe”;, Age = 20, Class =”B.Sc”, Email =
“[email protected]”.
(b) Insert Multiple Records
Add three new students to the Students table with varying Name, Age, and
Class.
(c) Update Data in a Table
Update the Age of the student with StudentID = 1 to 21.
Change the Class of all students from “B.Sc” to “B.Sc (Hons”.
(d) Delete Records from a Table
Delete the record of the student with StudentID = 1.
Delete all students whose Age is less than 18.

(a) Insert Data into a Table

INSERT INTO Students (StudentID, Name, Age, Class, Email)

VALUES (1, 'John Doe', 20, 'B.Sc', '[email protected]');

(b) Insert Multiple Records

INSERT INTO Students (StudentID, Name, Age, Class, Email)

VALUES
(2, 'Jane Smith', 22, 'M.Sc', '[email protected]'),
(3, 'Alice Brown', 19, 'B.Sc', '[email protected]'),
(4, 'Bob Johnson', 18, 'B.Com', '[email protected]');

(c) Update Data in a Table

UPDATE Students
SET Age = 21
WHERE StudentID = 1;

UPDATE Students
SET Class = 'B.Sc (Hons)'
WHERE Class = 'B.Sc';

(d) Delete Records from a Table

DELETE FROM Students

WHERE StudentID = 1;

DELETE FROM Students

WHERE Age < 18;
Question 2:

(a) Filter Data Using WHERE Clause

Retrieve the details of all students in the Students table whose Age is
greater
than 20.
(b) Order Data
Display the students in the Students table in descending order of their
Age.
(c) Aggregate Functions
Find the average age of students in the Students table.
Count the total number of students in the table.
(d) Join Two Tables
Consider two tables: Students and Courses. Write a query to retrieve all
students and the courses they are enrolled in using an INNER JOIN.
(e) Insert Data from Another Table
Insert all records from the OldStudents table into the Students table.

(a) Filter Data Using WHERE Clause

SELECT *
FROM Students
WHERE Age > 20;

(b) Order Data

SELECT *
FROM Students
ORDER BY Age DESC;

(c) Aggregate Functions

SELECT AVG(Age) AS AverageAge

FROM Students;

SELECT COUNT(*) AS TotalStudents

FROM Students;

(d) Join Two Tables

SELECT s.StudentID, s.Name, s.Age, s.Class, c.CourseName

FROM Students s
INNER JOIN Courses c
ON s.StudentID = c.StudentID;

(e) Insert Data from Another Table

INSERT INTO Students (StudentID, Name, Age, Class, Email)

SELECT StudentID, Name, Age, Class, Email
FROM OldStudents;
Question 3:
(a) Employee Management
Create an Employees table. Insert records for 5 employees, update the
salary of one employee, and delete employees with a salary less than
20,000.
(b) E-Commerce Application
Given Products and Orders tables, write queries to:
Insert a new product into the Products table.
Update the quantity of a product in the Orders table.
Delete an order where the quantity is zero.
(c) Banking System
In a Transactions table, write queries to:
Insert a deposit transaction for a customer.
Update the balance of a customer based on the latest transaction.
Delete all transactions older than one year.
(d) Insert Default Values
Insert a record into the Students table using default values for columns
where applicable.
(e) Rollback and Commit
Write a script that performs an INSERT operation into the Students table,
then rolls it back.
Write another script that performs an INSERT operation and commits it.

(a) Employee Management

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10, 2),
Department VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, Name, Salary, Department)

VALUES
(1, 'John Smith', 25000, 'HR'),
(2, 'Jane Doe', 15000, 'Finance'),
(3, 'Alice Brown', 30000, 'IT'),
(4, 'Bob Johnson', 22000, 'Marketing'),
(5, 'Charlie White', 18000, 'Sales');

UPDATE Employees
SET Salary = 17000
WHERE EmployeeID = 2;

DELETE FROM Employees

WHERE Salary < 20000;

(b) E-Commerce Application

-- Create the Products table

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
StockQuantity INT
);

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
ProductID INT,
Quantity INT,
OrderDate DATE,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

INSERT INTO Products (ProductID, ProductName, Price, StockQuantity)

VALUES (1, 'Laptop', 1200.00, 100);

UPDATE Orders
SET Quantity = 50
WHERE OrderID = 1 AND ProductID = 1;

DELETE FROM Orders

WHERE Quantity = 0;

(c) Banking System

CREATE TABLE Transactions (

TransactionID INT PRIMARY KEY,
CustomerID INT,
Amount DECIMAL(10, 2),
TransactionType VARCHAR(50), -- 'Deposit', 'Withdrawal'
TransactionDate DATE
);

INSERT INTO Transactions (TransactionID, CustomerID, Amount, TransactionType, TransactionDate)

VALUES (1, 101, 5000.00, 'Deposit', '2025-02-19');

UPDATE Customers
SET Balance = Balance + (SELECT Amount FROM Transactions WHERE TransactionID = 1)
WHERE CustomerID = 101;

DELETE FROM Transactions

WHERE TransactionDate < CURDATE() - INTERVAL 1 YEAR;

(d) Insert Default Values

INSERT INTO Students (StudentID, Name, Class)

VALUES (7, 'David Black', 'M.Sc');

(e) Rollback and Commit

START TRANSACTION;

INSERT INTO Students (StudentID, Name, Age, Class, Email)

VALUES (8, 'Emily Grey', 20, 'B.Sc', '[email protected]');

ROLLBACK;

START TRANSACTION;

INSERT INTO Students (StudentID, Name, Age, Class, Email)

VALUES (9, 'Frank Green', 21, 'B.A', '[email protected]');

COMMIT;
Week 2

(Data Integrity Constraint Implementation)

Question 1:
Create a database UniversityDB and the following tables with constraints:

• Students
o student_id (INTEGER, PRIMARY KEY)
o name (VARCHAR(100), NOT NULL)
o email (VARCHAR(100), UNIQUE)
o department_id (INTEGER, FOREIGN KEY referencing
o Departments(department_id))

• Departments
o department_id (INTEGER, PRIMARY KEY)
o department_name (VARCHAR(100), UNIQUE, NOT NULL)

(Write the SQL query to create these tables.)

Code:
Output:

Question 2:
Insert the following records into the Departments table.
INSERT INTO Departments (department_id, department_name) VALUES (101, &#39;Computer
Science&#39;);
INSERT INTO Departments (department_id, department_name) VALUES (102,&#39;Electrical
Engineering&#39;);
INSERT INTO Departments (department_id, department_name) VALUES (103, &#39;Mechanical
Engineering&#39;);
Code:

Output:

Question 3:
Insert the following records into the Students table and explain any constraint
violations that occur.
INSERT INTO Students (student_id, name, email, department_id) VALUES (1,
&#39;Alice&#39;,&#39;[email protected]&#39;, 101);
INSERT INTO Students (student_id, name, email, department_id) VALUES (2, NULL,
&#39;[email protected]&#39;, 102);
INSERT INTO Students (student_id, name, email, department_id) VALUES (3,
&#39;Charlie&#39;,&#39;[email protected]&#39;, 103);
INSERT INTO Students (student_id, name, email, department_id) VALUES (4,
&#39;David&#39;,&#39;[email protected]&#39;, 104);

• Explain why inserting NULL in name might cause an error.

Sol: Inserting NULL in the name column causes an error because the name column has a NOT NULL
constraint, which prevents NULL values from being inserted.
• Why does the duplicate email cause a problem?
Sol: The duplicate email causes an error because the email column has a UNIQUE constraint, which
enforces that all values in this column must be distinct.
• What happens when a foreign key references a non-existing department_id?
Sol: If a foreign key references a non-existing department_id, it violates the referential integrity
constraint, causing an error because the department_id must exist in the Departments table for
valid foreign key references.
Code:
Output:

Question 4:
Modify the Students table to add a constraint that ensures email cannot be NULL.
Code:

Output:

Question 5:
Drop the FOREIGN KEY constraint from the Students table that references Departments.
Code:
Output:

Question 6:
Modify the Students table so that if a department is deleted from Departments, all
students in that department are also deleted.

• Hint: Use ON DELETE CASCADE while defining the FOREIGN KEY.

• Test the effect by deleting a department:

DELETE FROM Departments WHERE department_id = 102;

• Check what happens to students in that department.

Code:

Output:

Question 7:
Can a table have a column that is both UNIQUE and NOT NULL? Justify your answer.
Solution:
Yes, a table can have a column that is both UNIQUE and NOT NULL.

• UNIQUE ensures that all values in the column are distinct.

• NOT NULL ensures that no NULL values are allowed.

Together, they enforce that every row must have a distinct, non-null value in that column.

Question 8:
Write an SQL query to find students whose email is NULL (if allowed).
Code:
Output:

Question 9:
If a table has both PRIMARY KEY and UNIQUE constraints on different columns,
what is the effect?
Solution:
If both exist on different columns:

• The PRIMARY KEY column(s) will have unique and non-null values.
• The UNIQUE column(s) must have distinct values but can contain NULLs if not explicitly NOT NULL.
Week 3
(SQL Query Writing with Functions and Sorting)
>Creating Database

1. What is the total number of products in the table?

Code:

Output:

2. What is the average price of all products?

Code:

Output:

3. What is the highest price of any product?

Code: Output:

4. What is the lowest price of any product?

Code: Output:

5. What is the total quantity of all products in stock?

Code:

Output:

6. How many different categories of products are there?

Code:

Output:

7. What is the average price of products in the "Electronics" category?

Code: Output:

8. What is the total value of all products in stock (price*quantity_in_stock)?

Code:

Output:
9. What is the number of products that have a price greater than 500?
Code: Output:

10.What is the sum of the quantities in stock for products in the "Clothing" category that cost less than
100?
Code: Output:
Week 4
(SQL Query Writing with Functions and Sorting)
>Database Creation

1. Using GROUP BY
i. Write a query to find the total sales (Quantity * Price) for each product.

Code: Output:

ii. Write a query to count the number of sales transactions for each product.

Code: Output:
iii. Write a query to find the total quantity sold for each category.

Code: Output:

iv. Write a query to find the average sale price of each product.

Code: Output:

v. Write a query to find the highest sale price recorded for each category.

Code: Output:

2. Using GROUP BY with HAVING

i. Write a query to find products where the total quantity sold is more than 10.

Code:
Output:

ii. Write a query to find categories where the total sales (Quantity * Price) exceed 5000.

Code:
Output:

iii. Write a query to find products where the average sale price is greater than 50.

Code:

Output:

iv. Write a query to find categories that have more than 2 different products sold.

Code:

Output:

v. Write a query to find products with total sales greater than 8000 but only for the "Electronics"
category.

Code:
Output:

3. Using ORDER BY
i. Write a query to display products along with their total sales (Quantity *Price), sorted in
descending order of total sales.

Code:

Output:

ii. Write a query to display categories along with their total quantity sold, sorted in ascending
order

Code:

Output:

iii. Write a query to find the average sale price per product and order it in descending order.

Code:
Output:

iv. Write a query to display the total number of transactions per category, sorted in descending
order.

Code:

Output:
v. Write a query to list products where the total sales are greater than 5000,ordered by
total sales in descending order
Code:

Output:
Week-5
Relational Algebra is a formal language for querying relational databases. It provides
operations to retrieve and manipulate data stored in relational tables. Three important set
operations in relational algebra are:
1. UNION ( ) – Combines results from two relations and removes duplicates.
2. INTERSECTION ( ) – Returns only the common tuples between two relations.
3. MINUS ( ) – Returns tuples that exist in the first relation but not in the second.

Create Table:
1. Retrieve all unique students (RollNo, Name) who have either secured a
scholarship or participated in sports.
2. Find all students (RollNo) who have either scored more than 80 marks in an
exam or have won a scholarship.

3. Retrieve students (RollNo, Name) who are either in the "Computer"

department or have participated in National/International level sports.

4. Find students (RollNo) who have both a scholarship and play sports.

5. Get students (RollNo) who have scored more than 90 marks in at least one
subject and also have a scholarship.

6. Find students (RollNo, Name) who belong to the "Electronics" department

and have both:
Played an International-level sport
Received a scholarship

7. Find students (RollNo, Name) who have a scholarship but have NOT played
any sports.

8. Retrieve students (RollNo, Name) who have played sports but never
received a scholarship.

9. Get students (RollNo, Name) who have scored below 50 marks in all their
subjects but are not involved in sports.

10. Find students from the "Mechanical" department who have not received
any scholarship and have not played any sports.

11. Find students who have played at least one sport at the "National" or
"International" level but have not scored above 70 in any exam.

12. Find students who have received a scholarship but have neither played sports
nor scored more than 60 marks in any exam.

13. Find students who have taken at least two different subjects in exams and have
either received a scholarship or played sports but not both.
Week-06

1. For all the staff members assigned to the existing departments, select all information about the staff
members and their respective departments.

2.Display the name salary, department identifier, and building and room location for every staff member
assigned to an existing department whose yearly salary exceeds $1000.
3. Display the name and title of every staff member who works in the humanities building.

4. Display the building and room of any academic department which employs a staff member whose title
begins with “EVANGLIST”.

5. For each department described in the DEPARTMENT table which employs at least one staff member,
display the department identifier followed by the number of staff members assigned to the department.

6. Form the cross product of the STAFF table and the DEPARTMENT table.
7. Assume that the dean is considering moving the administrative office of the management department. The
intention is to combine its administrative facilities with those of another department which is located in the
same building (which is unknown to the dean). To evaluate all possible options, display all information
about the management department followed by all information about any department which is located in the
same building.
Week-7
DBMS-Subqueries and Nested Queries

Questions:
Question-1: - Write a query to find the second highest salary from the
Employee table.
Query:
SELECT MAX(salary) AS second_highest_salary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
Output:

Question-2: - List employees who earn more than the average salary.
Query:
SELECT *
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);
Output:
Question-4: - Find employees working in a specific department.
Query:
SELECT *
FROM Employee
WHERE dept_id = 102;
Output:

Question-5: - Find employees whose salary is above the department's

average salary.
Query:
SELECT e.*
FROM Employee e
JOIN (
SELECT dept_id, AVG(salary) AS avg_salary
FROM Employee
GROUP BY dept_id
) dept_avg ON e.dept_id = dept_avg.dept_id
WHERE e.salary > dept_avg.avg_salary;
Output:

Question-6: - Retrieve employees whose salary is higher than their

manager's salary.
Query:
SELECT e.*
FROM Employee e
JOIN Employee m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;
Output:

Question-7: - Find employees who work in departments 101 or 103.

Query:
SELECT *
FROM Employee
WHERE dept_id IN (101, 103);
Output:

Question-8: - Find employees who have at least one manager assigned.

Query:
SELECT *
FROM Employee
WHERE manager_id IS NOT NULL;
Output:
Question-9: - Find employees who earn more than at least one employee in
department 102.
Query:
SELECT *
FROM Employee
WHERE salary > ANY (
SELECT salary
FROM Employee
WHERE dept_id = 102
);
Output:

Question-10: - Find employees who earn more than all employees in

department 101.
Query:
SELECT *
FROM Employee
WHERE salary > ALL (
SELECT salary
FROM Employee
WHERE dept_id = 101
);
Output:
WEEK-08
1. Create a view named EmployeeDepartmentView that displays the employee's first name,
last name, department name, and location.
CREATE VIEW EmployeeDepartmentView AS
SELECT e.first_name, e.last_name, d.department_name, d.location
FROM Employee e
JOIN
Departments d ON e.department_id = d.department_id;

2. Write a query to select all records from EmployeeDepartmentView.

SELECT * FROM EmployeeDepartmentView;

3. Create a view named ProjectDepartmentSummary showing the department name and the
count of projects in each department.
CREATE VIEW ProjectDepartmentSummary AS
SELECT d.department_name,
COUNT(p.project_id) AS project_count
FROM Departments d
LEFT JOIN Projects p ON d.department_id = p.department_id
GROUP BY d.department_name;
4. Write a query to retrieve departments with more than 2 projects from this
view.
SELECT * FROM ProjectDepartmentSummary
WHERE project_count > 2;

5. Create a view named HighSalaryEmployees that shows th e first name, last name and
salary of employees who earn over $50,000.
CREATE VIEW HighSalaryEmployees AS
SELECT first_name, last_name, salary
FROM Employee
WHERE salary > 50000;
6. Write a query to update the last name of an employee in the
HighSalaryEmployees view.
UPDATE Employee
SET last_name = 'Smithson'
WHERE last_name = 'Doe' AND salary > 50000;
7. Create a view named SalesEmployeeDetails that displays employee first name, last
name, and total sales amount for each employee.
CREATE VIEW SalesEmployeeDetails AS
SELECT e.first_name, e.last_name, SUM(s.amount) AS total_sales
FROM Employee e
JOIN Sales s ON e.employee_id = s.employee_id
GROUP BY e.first_name, e.last_name;

8. Write a query to return the top 5 sales persons from this view, ordered by total sale
amount descending.
SELECT * FROM SalesEmployeeDetails
ORDER BY total_sales_amount DESC
LIMIT 5;

9. Create an index named idx_employee_lastname on the last_name column

of the Employees table.
CREATE INDEX idx_employee_lastname
ON Employee (last_name);
10.Create a composite index named idx_employee_dept_salary on the department_id and
salary columns of the Employees table.

CREATE INDEX idx_employee_dept_salary ON Employee (department_id, salary);

11.Create a sequence named employee_id_seq to generate unique employee

IDs for the Employees table.
CREATE SEQUENCE employee_id_seq START WITH 111 INCREMENT BY 1 NOCACHE;

12.Write an INSERT statement to add a new employee, using the sequence

to generate the employee_id.
INSERT INTO Employee (employee_id, first_name, last_name, department_id, hire_date, salary)
VALUES (employee_id_seq.NEXTVAL, 'Daniel', 'Scott', 2, '2024-04-27', 58000);

13.Create a sequence named project_id_seq for the Projects table, starting at 1000 and
incrementing by 1.
CREATE SEQUENCE project_id_seq START WITH 1000 INCREMENT BY 1
NOCACHE;
14.Write a SQL statement to reset the employee_id_seq sequence to a
specific value.
ALTER SEQUENCE employee_id_seq RESTART WITH 200;

15.Create a sequence that cycles through the values 1,2 and 3.

CREATE SEQUENCE cycle_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 3
CYCLE;