A) Student(Stud_no : integer, Stud_name: string, year int ,Dept: string, gender:
char, DOB: date)
Book_(book_no: integer, book_name:string, author: string, price: float)
lss_rec_(iss_no:integer, Stud_no: integer, book_no: integer, iss_date: date)
Create tables with at least 4 constraints and insert values.
CREATE TABLE Student (
Stud_no INT PRIMARY KEY,
Stud_name VARCHAR(255) NOT NULL,
year INT CHECK (year >= 1 AND year <= 4),
Dept VARCHAR(50) NOT NULL,
gender CHAR(1) CHECK (gender IN ('M', 'F')),
DOB DATE,
CONSTRAINT chk_dob CHECK (DOB <= CURRENT_DATE),
CONSTRAINT chk_dept CHECK (Dept IN ('CSE', 'ECE',
'ME', 'IT')),
CONSTRAINT unq_stud_name_dept UNIQUE (Stud_name,
Dept)
);
INSERT INTO Student VALUES
(1, 'NARESH', 3, 'IT', 'M', '2000-01-15'),
(2, 'Naresh', 2, 'IT', 'M', '2001-05-22'),
(3, 'Jessie', 4, 'ME', 'F', '1999-11-10');
List the names of all boys
SELECT Stud_name
FROM Student
WHERE gender = 'M';
List the students department-wise
SELECT DISTINCT Dept, COUNT(*)
FROM Student
GROUP BY Dept;
List the details of students who borrowed more than 2 books
SELECT Student.*
FROM Student, lss_rec_
WHERE Student.Stud_no = lss_rec_.Stud_no
GROUP BY Student.Stud_no
HAVING COUNT(lss_rec_.iss_no) > 2;
List the books issued in the month of July 2023
SELECT Book_.*
FROM Book_, lss_rec_
WHERE Book_.book_no = lss_rec_.book_no
AND MONTH(lss_rec_.iss_date) = 7
AND YEAR(lss_rec_.iss_date) = 2023;
Create a view to display the book name and price
CREATE VIEW BookDetails AS
SELECT book_name, price
FROM Book_;
Retrieve the books issued to Krishna
SELECT Book_.*
FROM Book_, lss_rec_, Student
WHERE Book_.book_no = lss_rec_.book_no
AND lss_rec_.Stud_no = Student.Stud_no
AND Student.Stud_name = 'Krishna';
Create a trigger to log every new book insertion
CREATE TRIGGER log_new_book_insert
AFTER INSERT ON Book_
FOR EACH ROW
INSERT INTO BookLog (book_no, book_name, author,
price, insertion_date)
VALUES (NEW.book_no, NEW.book_name, NEW.author,
NEW.price, NOW());
Write a procedure to fetch the book details, given a book number
CREATE PROCEDURE GetBookDetails(IN book_number INT)
BEGIN
SELECT * FROM Book_ WHERE book_no = book_number;
END;
B) Emplyoee (emp_id,F_name, l_name, email,mobile, DOJ, salary, mgr_id,
job_id,dept_id)
Department(dept_id, dept_name, mgr_id)
Job_history(emp_id, start_date, end_date,dept_id,job_id)
List the employees whose last name contains “kumar”
SELECT *
FROM Employee
WHERE l_name LIKE '%kumar%';
List the ID and names of employees who joined after June 1, 2018
SELECT emp_id, F_name + ' ' + l_name
FROM Employee
WHERE DOJ > '2018-06-01';
List the employees who earn a salary more than a the minimum salary
SELECT *
FROM Employee
WHERE salary > (SELECT MIN(salary) FROM Employee);
List the names of managers
SELECT DISTINCT F_name + ' ' + l_name
FROM Employee
WHERE emp_id IN (SELECT DISTINCT mgr_id FROM
Department);
Display the number of employees in each department
SELECT dept_id, COUNT(*)
FROM Employee
GROUP BY dept_id;
List the managers who have more than 2 employees reporting them
SELECT mgr_id, COUNT(*)
FROM Employee
WHERE mgr_id IS NOT NULL
GROUP BY mgr_id
HAVING COUNT(*) > 2;
Write a procedure to retrieve the job history of a given Employee ID
CREATE PROCEDURE GetJobHistory(IN emp_id INT)
BEGIN
SELECT * FROM Job_history WHERE emp_id = emp_id;
END;
Create an index for the salary field of employee
CREATE INDEX idx_salary ON Employee(salary);
C) Customer(cust_no: integer,cust_name: string, DOB date, Gender char)
Cassette(cass_no:integer, cass_name:string, Language: String)
Iss_rec(iss_no: integer, iss_date: date, cust_no: integer, cass_no: integer)
List the details of customers whose names starts with ‘A’
SELECT *
FROM Customer
WHERE cust_name LIKE 'A%';
List the cassette numbers issued to “Rani”
SELECT Iss_rec.iss_no, Iss_rec.cust_no,
Iss_rec.cass_no, Cassette.cass_name,
Cassette.Language
FROM Iss_rec, Cassette, Customer
WHERE Iss_rec.cust_no = Customer.cust_no
AND Iss_rec.cass_no = Cassette.cass_no
AND Customer.cust_name = 'Rani';
List the cassettes issued between January and February, 2022
SELECT Cassette.*
FROM Iss_rec, Cassette
WHERE Iss_rec.cass_no = Cassette.cass_no
AND Iss_rec.iss_date BETWEEN '2022-01-01' AND
'2022-02-29';
List the details of customers who has not borrowed any cassette
SELECT *
FROM Customer
WHERE cust_no NOT IN (SELECT cust_no FROM Iss_rec);
Write a procedure to display the details of cassettes in ‘Telugu’ language
CREATE PROCEDURE DisplayTeluguCassettes()
BEGIN
SELECT *
FROM Cassette
WHERE Language = 'Telugu';
END;
Create a view ADULT with the details of customers who born after 2004
CREATE VIEW ADULT AS
SELECT *
FROM Customer
WHERE YEAR(DOB) > 2004;
Create a trigger to log every new customer
CREATE TRIGGER log_new_customer_insert
AFTER INSERT ON Customer
FOR EACH ROW
INSERT INTO CustomerLog (cust_no, cust_name, DOB,
Gender, insertion_date)
VALUES (NEW.cust_no, NEW.cust_name, NEW.DOB,
NEW.Gender, NOW());
D) Department(dep_id int,name string )
Employee(id int,name string ,designation string , grade int, DOJ date, salary
decimal, dep_id int )
Project (proj_id int, emp_id int, hours int)
List the employees whose designation contains “Assistant”
SELECT *
FROM Employee
WHERE designation LIKE '%Assistant%';
List the details of employees who earn the maximum salary
SELECT *
FROM Employee
WHERE salary = (SELECT MAX(salary) FROM Employee);
Display the details of employees who work in more than 2 projects
SELECT E.*
FROM Employee E, Project P
WHERE E.id = P.emp_id
GROUP BY E.id
HAVING COUNT(P.proj_id) > 2;
Increase the salary of each employee by 10%
UPDATE Employee
SET salary = salary * 1.1;
Write a query to calculate the years of experience of each employee.
SELECT id, (YEAR(NOW()) - YEAR(DOJ))
FROM Employee;
Write a procedure to display the details of employees who work in a given
department number
CREATE PROCEDURE DisplayEmployeesInDepartment(IN
dept_number INT)
BEGIN
SELECT *
FROM Employee
WHERE dep_id = dept_number;
END;
Create a view SIMPLE_EMP to display the Employee_id, Department_name,
Salary
CREATE VIEW SIMPLE_EMP AS
SELECT E.id, D.name, E.salary
FROM Employee E, Department D
WHERE E.dep_id = D.dep_id;
Create a trigger to log deletion of an employee
CREATE TRIGGER log_employee_deletion
AFTER DELETE ON Employee
FOR EACH ROW
INSERT INTO EmployeeDeletionLog (id, name,
deleted_date)
VALUES (OLD.id, OLD.name, NOW());
E) Movie(movie_id int ,title string ,date_of_release date, language string,
genre_id int)
Rating (movie_id int, reviewer id, stars int)
Reviewer(id int,name string) Genre(id int, type string )
List the movie details released in the year 1950
SELECT *
FROM Movie
WHERE YEAR(date_of_release) = 1950;
List the “Comedy” movies in Tamil language
SELECT movie_id, title, language
FROM Movie, Genre
WHERE Movie.genre_id = Genre.id
AND Genre.type = 'Comedy'
AND language = 'Tamil';
Display the count of movie in each genre.
SELECT Genre.type, COUNT(*)
WHERE Movie.genre_id = Genre.id
GROUP BY Genre.type;
List the movie ids that are rated 3 and above by “Ajay”
SELECT R.movie_id
FROM Rating R, Reviewer RV
WHERE R.reviewer_id = RV.id
AND RV.name = 'Ajay'
AND R.stars >= 3;
List the movie titles categorized as “Thriller”
SELECT title
FROM Movie, Genre
WHERE Movie.genre_id = Genre.id
AND Genre.type = 'Thriller';
Create a view to store movie name, genre name and language.
CREATE VIEW MovieDetails AS
SELECT M.title, G.type, M.language
FROM Movie M, Genre G
WHERE M.genre_id = G.id;
Write a procedure to list the movies reviewed by a given reviewer_id
CREATE PROCEDURE ListMoviesReviewedByReviewer(IN
reviewer_id INT)
BEGIN
SELECT M.title
FROM Movie M, Rating R
WHERE M.movie_id = R.movie_id
AND R.reviewer_id = reviewer_id;
END;
Write a trigger to log the insertion in Reviewer table
CREATE TRIGGER log_reviewer_insert
AFTER INSERT ON Reviewer
FOR EACH ROW
INSERT INTO ReviewerLog (id, name, insertion_date)
VALUES (NEW.id, NEW.name, NOW());
F) Salesman (SID int, name: string, city string, commission float)
Customer (CID int, name string, city string, SID int)
Orders(OrderNo int, Pur_amount float, order_date date, CID int,SID int)
List the customers in Chennai.
SELECT *
FROM Customer
WHERE city = 'Chennai';
Increase the commission of the salesman in city ‘Kanchi’ by 2%
UPDATE Salesman
SET commission = commission * 1.02
WHERE city = 'Kanchi';
Find all the customers along with the salesperson who works for them.
SELECT C.*, S.name
FROM Customer C, Salesman S
WHERE C.SID = S.SID;
Find the sales people who has not done any order
SELECT S.*
FROM Salesman S
WHERE S.SID NOT IN (SELECT DISTINCT SID FROM Orders
WHERE SID IS NOT NULL);
List the orders made between 25th December 2021 and 1st Jan 2022
SELECT *
FROM Orders
WHERE order_date BETWEEN '2021-12-25' AND '2022-01-
01';
Find those orders where order amount exists between 500 and 2000. Return
ord_no, purch_amt, cust_name, city.
SELECT O.OrderNo, O.Pur_amount, C.name, C.city
FROM Orders O, Customer C
WHERE O.CID = C.CID
AND O.Pur_amount BETWEEN 500 AND 2000;
Write a procedure to display the orders made on a given date
CREATE PROCEDURE DisplayOrdersOnDate(IN given_date
DATE)
BEGIN
SELECT *
FROM Orders
WHERE order_date = given_date;
END;
Write a trigger to log any customer deletion
CREATE TRIGGER log_customer_deletion
AFTER DELETE ON Customer
FOR EACH ROW
INSERT INTO CustomerDeletionLog (CID, name,
deleted_date)
VALUES (OLD.CID, OLD.name, NOW());
G) Salesman (SID int, name: string, city string, commission float, DOJ date)
Customer (CID int, name string, city string, SID int)
Orders(OrderNo int, Pur_amount float, order_date date, CID int, SID int)
List the salesman who joined after 1-1-2023
SELECT *FROM Salesman
WHERE DOJ > '2023-01-01';
Increase the commission of salesmen in “Vellore” by 5%
UPDATE Salesman
SET commission = commission * 1.05
WHERE city = 'Vellore';
Display the details of salesmen in Chennai or Vellore or Kanchi.
SELECT *FROM Salesman
WHERE city IN ('Chennai', 'Vellore', 'Kanchi');
Create a view to list the salesman and their customers
CREATE VIEW SalesmanCustomers AS
SELECT S.*, C.*
FROM Salesman S, Customer C
WHERE S.SID = C.SID;
List the orders made at “Chennai” exceeding 100000 in October 2023
SELECT O.*
FROM Orders O, Customer C, Salesman S
WHERE O.CID = C.CID
AND O.SID = S.SID
AND C.city = 'Chennai'
AND O.Pur_amount > 100000
AND MONTH(O.order_date) = 10
AND YEAR(O.order_date) = 2023;
List the salesman who do not have any customer
SELECT S.*
FROM Salesman S
WHERE S.SID NOT IN (SELECT SID FROM Customer WHERE
SID IS NOT NULL);
Write a procedure to fetch the order details for a given Order_id
CREATE PROCEDURE FetchOrderDetails(IN order_id INT)
BEGIN
SELECT *
FROM Orders
WHERE OrderNo = order_id;
END;
Write a trigger to log deletion of a salesman
CREATE TRIGGER log_salesman_deletion
AFTER DELETE ON Salesman
FOR EACH ROW
INSERT INTO SalesmanDeletionLog (SID, name,
deleted_date)
VALUES (OLD.SID, OLD.name, NOW());
H) Emplyoee (emp_id int,F_name string , l_name string , email string , mobile
string, DOJ date, salary float, mgr_id int, job_id int ,dept_id int)
Department(dept_id, dept_name, mgr_id)
Job_history(emp_id , start_date, end_date,dept_id,job_id)
List the employees who joined this year
SELECT *
FROM Employee
WHERE YEAR(DOJ) = YEAR(NOW());
List the employees with their manager’s names
SELECT E.*, M.F_name, M.l_name
FROM Employee E, Employee M
WHERE E.mgr_id = M.emp_id;
List the employees who earn a salary equal to the average salary
SELECT *FROM Employee
WHERE salary = (SELECT AVG(salary) FROM Employee);
Count the number of employees in each department
SELECT dept_id, COUNT(*)
FROM Employee
GROUP BY dept_id;
List the employees who work in same job_id for more than an year
SELECT E1.*
FROM Employee E1, Employee E2
WHERE E1.job_id = E2.job_id
AND E1.DOJ - E2.DOJ > 365;
Create a view SIMPLE_EMP with Emp_ID, Department name, salary
CREATE VIEW SIMPLE_EMP AS
SELECT emp_id, D.dept_name, salary
FROM Employee E, Department D
WHERE E.dept_id = D.dept_id;
Write a procedure to fetch the employee details for a given dept_id
CREATE PROCEDURE FetchEmployeeDetailsForDept(IN
dept_id INT)
BEGIN
SELECT *
FROM Employee
WHERE dept_id = dept_id;
END;
Write a trigger to log insertion of an employee
CREATE TRIGGER log_employee_insertion
AFTER INSERT ON Employee
FOR EACH ROW
INSERT INTO EmployeeInsertionLog (emp_id, F_name,
l_name, insertion_date)
VALUES (NEW.emp_id, NEW.F_name, NEW.l_name, NOW());
