DBMS LAB INTERNAL 1
QUESTION 1
SOLUTUON
CREATE DATABASE MyDb1 ;
USE MyDb1 ;
-- Create the EMPLOYEE table
CREATE TABLE EMPLOYEE (
Fname VARCHAR(255),
Minit VARCHAR(1),
Lname VARCHAR(255),
San VARCHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(255),
Sex VARCHAR(1),
Salary DECIMAL(10,2),
Super_ssn VARCHAR(9),
Dno INT,
PRIMARY KEY (San)
);
-- Insert data into the EMPLOYEE table
INSERT INTO EMPLOYEE VALUES
('John', 'D', 'Doe', '123456789', '1990-05-15', '123 Main St', 'M', 50000.00, '987654321', 1),
('Jane', 'A', 'Smith', '987654321', '1985-08-22', '456 Oak St', 'F', 60000.00, NULL, 2),
('Bob', 'J', 'Johnson', '555555555', '1992-11-10', '789 Pine St', 'M', 45000.00, '123456789', 1),
('Alice', 'M', 'Williams', '111111111', '1988-04-03', '101 Elm St', 'F', 55000.00, '987654321', 3),
('Charlie', 'R', 'Brown', '999999999', '1995-09-20', '202 Maple St', 'M', 70000.00, '123456789', 2);
-- Show all data in the EMPLOYEE table
SELECT * FROM EMPLOYEE;
-- Create the DEPARTMENT table
CREATE TABLE DEPARTMENT (
Dname VARCHAR(255),
Dnumber INT NOT NULL,
Mgr_san VARCHAR(9),
Mgr_start_date DATE,
PRIMARY KEY (Dnumber),
FOREIGN KEY (Mgr_san) REFERENCES EMPLOYEE(San)
);
-- Insert data into the DEPARTMENT table
INSERT INTO DEPARTMENT VALUES
('HR', 1, '123456789', '2020-01-01'),
('IT', 2, '987654321', '2019-10-15'),
('Finance', 3, '555555555', '2022-03-05');
-- Show all data in the DEPARTMENT table
SELECT * FROM DEPARTMENT;
-- Create the DEPT_LOCATIONS table
CREATE TABLE DEPT_LOCATIONS (
Dnumber INT NOT NULL,
Dlocation VARCHAR(255),
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)
);
-- Insert data into the DEPT_LOCATIONS table
INSERT INTO DEPT_LOCATIONS VALUES
(1, 'New York'),
(2, 'San Francisco'),
(3, 'Chicago');
-- Show all data in the DEPT_LOCATIONS table
SELECT * FROM DEPT_LOCATIONS;
-- Create the PROJECT table
CREATE TABLE PROJECT (
Pname VARCHAR(255),
Pnumber INT NOT NULL,
Plocation VARCHAR(255),
Dnum INT,
PRIMARY KEY (Pnumber),
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)
);
-- Insert data into the PROJECT table
INSERT INTO PROJECT VALUES
('ProjectA', 101, 'New York', 1),
('ProjectB', 102, 'San Francisco', 2),
('ProjectC', 103, 'Chicago', 3);
-- Show all data in the PROJECT table
SELECT * FROM PROJECT;
-- Create the WORKS_ON table
CREATE TABLE WORKS_ON (
Essn VARCHAR(9) NOT NULL,
Pho INT NOT NULL,
Hours DECIMAL(5,2),
PRIMARY KEY (Essn, Pho),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(San),
FOREIGN KEY (Pho) REFERENCES PROJECT(Pnumber)
);
-- Insert data into the WORKS_ON table
INSERT INTO WORKS_ON VALUES
('123456789', 101, 20.5),
('987654321', 102, 15.0),
('555555555', 103, 25.5);
-- Show all data in the WORKS_ON table
SELECT * FROM WORKS_ON;
-- Create the DEPENDENT table
CREATE TABLE DEPENDENT (
Essn VARCHAR(9) NOT NULL,
Dependent_name VARCHAR(255),
Sex VARCHAR(1),
Bdate DATE,
Relationship VARCHAR(255),
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(San)
);
-- Insert data into the DEPENDENT table
INSERT INTO DEPENDENT VALUES
('123456789', 'Mary Doe', 'F', '2000-05-12', 'Daughter'),
('987654321', 'John Smith Jr.', 'M', '2018-02-28', 'Son'),
('555555555', 'Emma Johnson', 'F', '2015-09-10', 'Daughter');
-- Show all data in the DEPENDENT table
SELECT * FROM DEPENDENT;
QUESTION 2
1. To Create a table called reserves table and answer the following queries
Reserves(sid: integer, bid: integer, day: date)
2. Add column time to the reserves table.
3. Alter the column day data type to date
4. Drop the column time in the table.
5. Delete the row of the table with some condition.
6. Delete table reserves
SOLUTUON
CREATE TABLE reserves (
sid INTEGER,
bid INTEGER,
day DATE
);
SELECT * FROM reserves; -- To show all data in the "reserves" table
ALTER TABLE reserves
ADD COLUMN time TIME;
INSERT INTO reserves (sid, bid, day)
VALUES (2, 30, '2024-02-20'),
(3, 18, '2024-03-05'),
(3, 18, '2024-03-05'),
(123, 18, '2024-03-05'),
(654, 18, '2024-03-05'),
(21, 18, '2024-03-05');
SELECT * FROM reserves; -- To show all data in the "reserves" table
ALTER TABLE reserves
DROP COLUMN time;
SELECT * FROM reserves; -- To show all data in the "reserves" table
DELETE FROM reserves
WHERE sid = 123; -- Replace 123 with your desired condition
SELECT * FROM reserves; -- To show all data in the "reserves" table
DROP TABLE reserves;
QUESTION 3
USE COMPANY DATABASE
AVG(), BETWEEN, HAVING, GROUP BY, COUNT:
Insert relevant data into the tables to execute the queries:
1. Write a Query to display total salary needed to each Job in Employee Table;
select sum(salary, job)
from employee
group by Job;
2. Write a Query to display the hire date on which at least 3 Employees were hired;
select hiredate
from employee
group by hiredate
haing count(*)>=3;
3. Write a query to display the department Number which has more than 2 Employees
and total amount required to pay the monthly salaries of all the employees in that
Department should be more than 9000
select depNo, sum(sal)
from employee
where sal>9000
group by deptNo
having count(*)>2;
4. Write a query to display the salaries which has repetitions in the SAL(Salary) column
of the EMPLOYEE table.
Select count(*), avg(sal)
from employee
where sal<comm
group by DeptNo;
5. Write a query to display the department numbers whose average salary is between
2500 and 3000
select Dept_number,avg(sal)
from employee
where sal between 2500 adn 3000
group by deptNo;
QUESTION 4
BOOK (Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS (Book_id, Author_Name)
PUBLISHER (Name, Address, Phone)
BOOK_COPIES (Book_id, Branch_id, Noof_Copies)
BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH (Branch_id, Branch_Name, Address)
Write SQL queries to
1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in
each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun
2017
3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation
operation.
SOLUTUON
-- Create tables
CREATE TABLE BOOK (
Book_id INT PRIMARY KEY,
Title VARCHAR(255),
Publisher_Name VARCHAR(255),
Pub_Year INT
);
-- Insert sample data
INSERT INTO BOOK VALUES
(1, 'Book1', 'Publisher1', 2015),
(2, 'Book2', 'Publisher2', 2018),
(3, 'Book3', 'Publisher3', 2020);
-- Show content after inserting data into BOOK
SELECT * FROM BOOK;
CREATE TABLE BOOK_AUTHORS (
Book_id INT,
Author_Name VARCHAR(255),
PRIMARY KEY (Book_id, Author_Name),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id)
);
-- Insert sample data into BOOK_AUTHORS
INSERT INTO BOOK_AUTHORS VALUES
(1, 'Author1'),
(2, 'Author2'),
(2, 'Author3'),
(3, 'Author4');
-- Show content after inserting data into BOOK_AUTHORS
SELECT * FROM BOOK_AUTHORS;
CREATE TABLE PUBLISHER (
Name VARCHAR(255) PRIMARY KEY,
Address VARCHAR(255),
Phone VARCHAR(20)
);
-- Insert sample data into PUBLISHER
INSERT INTO PUBLISHER VALUES
('Publisher1', 'Address1', '123-456-7890'),
('Publisher2', 'Address2', '987-654-3210'),
('Publisher3', 'Address3', '111-222-3333');
-- Show content after inserting data into PUBLISHER
SELECT * FROM PUBLISHER;
CREATE TABLE BOOK_COPIES (
Book_id INT,
Branch_id INT,
Noof_Copies INT,
PRIMARY KEY (Book_id, Branch_id),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id)
);
-- Insert sample data into BOOK_COPIES
INSERT INTO BOOK_COPIES VALUES
(1, 1, 5),
(1, 2, 3),
(2, 1, 2),
(3, 2, 4);
-- Show content after inserting data into BOOK_COPIES
SELECT * FROM BOOK_COPIES;
CREATE TABLE BOOK_LENDING (
Book_id INT,
Branch_id INT,
Card_No INT,
Date_Out DATE,
Due_Date DATE,
PRIMARY KEY (Book_id, Branch_id, Card_No),
FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id)
);
-- Insert sample data into BOOK_LENDING
INSERT INTO BOOK_LENDING VALUES
(1, 1, 123, '2021-01-15', '2021-02-15'),
(2, 1, 456, '2017-05-10', '2017-06-10'),
(3, 2, 789, '2018-03-20', '2018-04-20');
-- Show content after inserting data into BOOK_LENDING
SELECT * FROM BOOK_LENDING;
CREATE TABLE LIBRARY_BRANCH (
Branch_id INT PRIMARY KEY,
Branch_Name VARCHAR(255),
Address VARCHAR(255)
);
-- Insert sample data into LIBRARY_BRANCH
INSERT INTO LIBRARY_BRANCH VALUES
(1, 'Branch1', 'Branch1 Address'),
(2, 'Branch2', 'Branch2 Address');
-- Show content after inserting data into LIBRARY_BRANCH
SELECT * FROM LIBRARY_BRANCH;
-- Query 1: Retrieve details of all books in the library
SELECT
B.Book_id,
[Link],
B.Publisher_Name,
BA.Author_Name,
BC.Branch_id,
BC.Noof_Copies
FROM
BOOK B
JOIN
BOOK_AUTHORS BA ON B.Book_id = BA.Book_id
JOIN
BOOK_COPIES BC ON B.Book_id = BC.Book_id;
-- Query 2: Get particulars of borrowers who have borrowed more than 3 books from Jan 2017 to Jun 2017
SELECT
BL.Card_No,
COUNT(BL.Book_id) AS Books_Borrowed
FROM
BOOK_LENDING BL
WHERE
BL.Date_Out BETWEEN '2017-01-01' AND '2017-06-30'
GROUP BY
BL.Card_No
HAVING
COUNT(BL.Book_id) > 3;
-- Query 3: Delete a book in the BOOK table and update related tables
-- For example, let's delete Book with ID 2
BEGIN
-- Check if Book_id = 2 is in use in child tables
IF EXISTS (SELECT 1 FROM BOOK_AUTHORS WHERE Book_id = 2)
OR EXISTS (SELECT 1 FROM BOOK_COPIES WHERE Book_id = 2)
OR EXISTS (SELECT 1 FROM BOOK_LENDING WHERE Book_id = 2)
BEGIN
-- If in use, show message and rollback
PRINT 'Cannot delete Book with ID 2. It is referenced in other tables.';
END
ELSE
BEGIN
-- If not in use, delete from BOOK table
DELETE FROM BOOK WHERE Book_id = 2;
-- Delete from BOOK_AUTHORS, BOOK_COPIES, BOOK_LENDING if needed
DELETE FROM BOOK_AUTHORS WHERE Book_id = 2;
DELETE FROM BOOK_COPIES WHERE Book_id = 2;
DELETE FROM BOOK_LENDING WHERE Book_id = 2;
END;
END;
-- Show content after deletion
SELECT * FROM BOOK;
SELECT * FROM BOOK_AUTHORS;
SELECT * FROM BOOK_COPIES;
SELECT * FROM BOOK_LENDING;
QUESTION 5
Consider the following schema for Order Database:
SALESMAN (Salesman_id, Name, City, Commission)
CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
Write SQL queries to
1. Count the customers with grades above Bangalore’s average.
2. Find the name and numbers of all salesmen who had more than one customer.
3. List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION
operation.)
SOLUTUON
-- Create tables
CREATE TABLE SALESMAN (
Salesman_id INT PRIMARY KEY,
Name VARCHAR(255),
City VARCHAR(255),
Commission DECIMAL(5, 2)
);
CREATE TABLE CUSTOMER (
Customer_id INT PRIMARY KEY,
Cust_Name VARCHAR(255),
City VARCHAR(255),
Grade INT,
Salesman_id INT,
FOREIGN KEY (Salesman_id) REFERENCES SALESMAN(Salesman_id)
);
CREATE TABLE ORDERss (
Ord_No INT PRIMARY KEY,
Purchase_Amt DECIMAL(10, 2),
Ord_Date DATE,
Customer_id INT,
Salesman_id INT,
FOREIGN KEY (Customer_id) REFERENCES CUSTOMER(Customer_id),
FOREIGN KEY (Salesman_id) REFERENCES SALESMAN(Salesman_id)
);
-- Insert sample data
-- SALESMAN
INSERT INTO SALESMAN VALUES
(1, 'John Doe', 'Bangalore', 0.10),
(2, 'Jane Smith', 'Mumbai', 0.15),
(3, 'Bob Johnson', 'Delhi', 0.12),
(4, 'Alice Brown', 'Bangalore', 0.08);
-- CUSTOMER
INSERT INTO CUSTOMER VALUES
(101, 'Customer1', 'Bangalore', 2, 1),
(102, 'Customer2', 'Mumbai', 3, 2),
(103, 'Customer3', 'Delhi', 1, 3),
(104, 'Customer4', 'Bangalore', 2, 4);
-- ORDERS
INSERT INTO ORDERss VALUES
(1, 500.00, '2023-01-10', 101, 1),
(2, 700.00, '2023-02-15', 102, 2),
(3, 300.00, '2023-03-20', 103, 3),
(4, 450.00, '2023-04-25', 104, 4);
-- Now, let's proceed with the queries
-- Query 1: Count the customers with grades above Bangalore’s average
SELECT COUNT(*) AS Above_Average_Grades
FROM CUSTOMER
WHERE Grade > (SELECT AVG(Grade) FROM CUSTOMER WHERE City = 'Bangalore');
-- Query 2: Find the name and numbers of all salesmen who had more than one customer
SELECT [Link], S.Salesman_id, COUNT(C.Customer_id) AS Customer_Count
FROM SALESMAN S
JOIN CUSTOMER C ON S.Salesman_id = C.Salesman_id
GROUP BY S.Salesman_id, [Link]
HAVING COUNT(C.Customer_id) > 1;
-- Query 3: List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation)
-- Salesmen with customers
SELECT DISTINCT S.Salesman_id, [Link], [Link]
FROM SALESMAN S
JOIN CUSTOMER C ON S.Salesman_id = C.Salesman_id
UNION
-- Salesmen without customers
SELECT S.Salesman_id, [Link], [Link]
FROM SALESMAN S
WHERE NOT EXISTS (SELECT 1 FROM CUSTOMER C WHERE C.Salesman_id = S.Salesman_id);