exxx

DATABASE SOLUTION…..
Question no 1

1. Create a Relational database in 3 NF with appropriate data types and Constraints. Constraints: - Primary Key, ename should not be NULL Consider the following entities and their relationships.
   Emp(eno ,ename ,designation ,salary, Date_Of_Joining)
   Dept(dno,dname ,loc)

i. Add column phone_No into Emp table with data type int
ii. Delete the details of Employee whose designation is ‘Manager’
iii. Display the name of employee who is ‘Manager’ of “Account Department”.
iv. Display the name of department whose location is “Pune” and “Mr. ABC” is working in it.
v. Update Dateofjoining of employee to ‘15/06/2019’ whose department is ‘computer science’ and name is “Mr. Roy’.

Solution……
-- Create database
CREATE DATABASE IF NOT EXISTS CompanyDB;
USE CompanyDB;

-- Create Emp table
CREATE TABLE IF NOT EXISTS Emp (
eno INT PRIMARY KEY,
ename VARCHAR(100) NOT NULL,
designation VARCHAR(100),
salary DECIMAL(10, 2),
Date_Of_Joining DATE,
phone_No INT
);

-- Create Dept table
CREATE TABLE IF NOT EXISTS Dept (
dno INT PRIMARY KEY,
dname VARCHAR(100),
loc VARCHAR(100)
);

-- Add column phone_No into Emp table with data type int
ALTER TABLE Emp ADD COLUMN phone_No INT;

-- Insert values into Emp table
INSERT INTO Emp (eno, ename, designation, salary, Date_Of_Joining, phone_No) VALUES
(101, 'John Doe', 'Engineer', 50000.00, '2020-01-15', 123456789),
(102, 'Jane Smith', 'Manager', 70000.00, '2019-05-20', 987654321),
(103, 'Alice Johnson', 'Technician', 45000.00, '2021-03-10', 654321987),
(104, 'Bob Williams', 'Developer', 55000.00, '2018-11-30', 789456123),
(105, 'Charlie Brown', 'Analyst', 60000.00, '2022-02-25', 321654987);

-- Insert values into Dept table
INSERT INTO Dept (dno, dname, loc) VALUES
(201, 'Accounting', 'New York'),
(202, 'HR', 'Los Angeles'),
(203, 'IT', 'Chicago'),
(204, 'Marketing', 'San Francisco'),
(205, 'Operations', 'Pune');

-- Delete the details of Employee whose designation is 'Manager'
DELETE FROM Emp WHERE designation = 'Manager';

-- Display the name of employee who is 'Manager' of "Account Department"
SELECT ename
FROM Emp
WHERE designation = 'Manager';

-- Display the name of department whose location is "Pune" and "Mr. ABC" is working in it
SELECT dname
FROM Dept
WHERE loc = 'Pune';

-- Update Dateofjoining of employee to '15/06/2019' whose department is 'IT' and name is "Mr. Roy'
UPDATE Emp
SET Date_Of_Joining = '2019-06-15'
WHERE ename = 'Mr. Roy' AND dno = (SELECT dno FROM Dept WHERE dname = 'IT');

-- Print all values in Emp table
SELECT * FROM Emp;

-- Print all values in Dept table
SELECT * FROM Dept;

Question no 2
. Create a Relational database in 3 NF with appropriate data types and Constraints. Constraints: - Primary Key, ordDate should not be NULL
Sales_order (ordNo, ordDate)
Client (clientNo, ClientName, addr)
i. Add column amount into Sales_order table with data type int.
ii. Delete the details of the clients whose names start with ‘A’ character.
iii. Delete sales order details of client whose name is “Patil” and order date is “09/08/2019”
iv. Change order date of client_No ‘CN001’ ‘18/03/2019’.
v. Update the address of client to “Pimpri” whose name is ‘Mr. Roy’

Solution…..
-- Create database
CREATE DATABASE IF NOT EXISTS SalesDB;
USE SalesDB;

-- Create Sales_order table
CREATE TABLE IF NOT EXISTS Sales_order (
ordNo INT PRIMARY KEY,
ordDate DATE NOT NULL,
amount INT
);

-- Create Client table
CREATE TABLE IF NOT EXISTS Client (
clientNo INT PRIMARY KEY,
ClientName VARCHAR(100) NOT NULL,
addr VARCHAR(255)
);

-- Add column amount into Sales_order table with data type int
ALTER TABLE Sales_order ADD COLUMN amount INT;

-- Insert values into Sales_order table
INSERT INTO Sales_order (ordNo, ordDate, amount) VALUES
(1, '2021-01-01', 500),
(2, '2021-02-05', 700),
(3, '2021-03-10', 1000),
(4, '2021-04-15', 800),
(5, '2021-05-20', 1200);

-- Insert values into Client table
INSERT INTO Client (clientNo, ClientName, addr) VALUES
(101, 'John Doe', 'New York'),
(102, 'Jane Smith', 'Los Angeles'),
(103, 'Alice Johnson', 'Chicago'),
(104, 'Bob Williams', 'San Francisco'),
(105, 'Mr. Roy', 'Pune'),
(106, 'Mr. Patil', 'Mumbai');

-- Delete the details of the clients whose names start with 'A' character
DELETE FROM Client WHERE ClientName LIKE 'A%';

-- Delete sales order details of client whose name is "Patil" and order date is "09/08/2019"
DELETE FROM Sales_order
WHERE ordNo IN (SELECT ordNo FROM Client WHERE ClientName = 'Mr. Patil')
AND ordDate = '2019-09-08';

-- Change order date of client_No 'CN001' to '18/03/2019'
UPDATE Sales_order
SET ordDate = '2019-03-18'
WHERE ordNo = 1; -- Assuming 'CN001' corresponds to ordNo 1

-- Update the address of client to "Pimpri" whose name is 'Mr. Roy'
UPDATE Client
SET addr = 'Pimpri'
WHERE ClientName = 'Mr. Roy';

-- Print all values in Sales_order table
SELECT * FROM Sales_order;

-- Print all values in Client table
SELECT * FROM Client;

Question no 3
Create a Relational database in 3 NF with appropriate data types and Constraints.
Hospital (hno ,hname , city, Est_year, addr)
Doctor (dno , dname , addr, Speciality)
i. Delete addr column from Hospital table.
ii. Display doctor name, Hospital name and specialty of doctors from “Pune City” .
iii. Display the names of the hospitals which are located at “Pimpri” city.
iv. Display the names of doctors who are working in “Birla” Hospital and city name is “Chinchwad”
v. Display the specialty of the doctors who are working in “Ruby” hospital.
vi. Give the count of doctor’s hospital wise which are located at “Pimple Gurav”.
vii. Update an address of Doctor to “Pimpri” whose hospital is “Ruby clinic”

Solution….
-- Create database
CREATE DATABASE IF NOT EXISTS HospitalDB;
USE HospitalDB;

-- Create Hospital table
CREATE TABLE IF NOT EXISTS Hospital (
hno INT PRIMARY KEY,
hname VARCHAR(100) NOT NULL,
city VARCHAR(100),
Est_year INT
);

-- Create Doctor table
CREATE TABLE IF NOT EXISTS Doctor (
dno INT PRIMARY KEY,
dname VARCHAR(100) NOT NULL,
addr VARCHAR(255),
Speciality VARCHAR(100)
);

-- Insert values into Hospital table
INSERT INTO Hospital (hno, hname, city, Est_year) VALUES
(1, 'ABC Hospital', 'Pune', 1990),
(2, 'Birla Hospital', 'Chinchwad', 1985),
(3, 'Ruby Clinic', 'Pimpri', 2000),
(4, 'XYZ Hospital', 'Pune', 2005);

-- Insert values into Doctor table
INSERT INTO Doctor (dno, dname, addr, Speciality) VALUES
(101, 'Dr. John', 'Pune', 'Cardiologist'),
(102, 'Dr. Smith', 'Chinchwad', 'Orthopedic'),
(103, 'Dr. Alice', 'Pimpri', 'Pediatrician'),
(104, 'Dr. Bob', 'Pune', 'Dermatologist'),
(105, 'Dr. Jane', 'Pimpri', 'Oncologist');

-- i. Delete addr column from Hospital table
ALTER TABLE Hospital DROP COLUMN addr;

-- ii. Display doctor name, Hospital name and specialty of doctors from “Pune City”
SELECT d.dname AS Doctor_Name, h.hname AS Hospital_Name, d.Speciality
FROM Doctor d
INNER JOIN Hospital h ON d.addr = h.city
WHERE h.city = 'Pune';

-- iii. Display the names of the hospitals which are located at “Pimpri” city
SELECT hname AS Hospital_Name
FROM Hospital
WHERE city = 'Pimpri';

-- iv. Display the names of doctors who are working in “Birla” Hospital and city name is “Chinchwad”
SELECT dname AS Doctor_Name
FROM Doctor
WHERE addr = 'Chinchwad' AND dno IN (SELECT dno FROM Hospital WHERE hname = 'Birla Hospital');

-- v. Display the specialty of the doctors who are working in “Ruby” hospital
SELECT Speciality
FROM Doctor
WHERE dno IN (SELECT dno FROM Hospital WHERE hname = 'Ruby Clinic');

-- vi. Give the count of doctor’s hospital wise which are located at “Pimple Gurav”
SELECT h.hname AS Hospital_Name, COUNT(*) AS Doctor_Count
FROM Doctor d
INNER JOIN Hospital h ON d.addr = h.city
WHERE h.city = 'Pimple Gurav'
GROUP BY h.hname;

-- vii. Update an address of Doctor to “Pimpri” whose hospital is “Ruby clinic”
UPDATE Doctor
SET addr = 'Pimpri'
WHERE dno IN (SELECT dno FROM Hospital WHERE hname = 'Ruby Clinic');

-- Print all values in Hospital table
SELECT * FROM Hospital;

-- Print all values in Doctor table
SELECT * FROM Doctor;

Question no 4
Create a Relational database in 3 NF with appropriate data types and Constraints. Constraints: - Primary key, Addr should not be null.
Patient (PCode, Name, Addr, Disease)
Bed (Bed_No, RoomNo, loc)
i. Display the details of patients who are from “Pimple Gurav”
ii. Delete the details of patient whose Bed_No is 1 and RoomNo is 105.
iii. Display the names of patients who are admitted in room no 101.
iv. Display the disease of patient whose bed_No is 1
v. Display the room_no and bed_no of patient whose name is “Mr Roy”
vi. Give the details of Patient who is admitted on 2nd flr in roomno 102.

Solution….
-- Create database
CREATE DATABASE IF NOT EXISTS HospitalDB;
USE HospitalDB;

-- Create Patient table
CREATE TABLE IF NOT EXISTS Patient (
PCode INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Addr VARCHAR(255) NOT NULL,
Disease VARCHAR(100)
);

-- Create Bed table
CREATE TABLE IF NOT EXISTS Bed (
Bed_No INT PRIMARY KEY,
RoomNo INT,
loc VARCHAR(100)
);

-- Insert values into Patient table
INSERT INTO Patient (PCode, Name, Addr, Disease) VALUES
(101, 'John Doe', 'Pimple Gurav', 'Fever'),
(102, 'Jane Smith', 'Pune', 'Cough'),
(103, 'Alice Johnson', 'Pimple Gurav', 'Headache'),
(104, 'Bob Williams', 'Mumbai', 'Fracture'),
(105, 'Mr. Roy', 'Pune', 'Diabetes');

-- Insert values into Bed table
INSERT INTO Bed (Bed_No, RoomNo, loc) VALUES
(1, 101, '1st flr'),
(2, 102, '2nd flr'),
(3, 103, '3rd flr'),
(4, 104, '4th flr'),
(5, 105, '5th flr');

-- i. Display the details of patients who are from “Pimple Gurav”
SELECT * FROM Patient WHERE Addr = 'Pimple Gurav';

-- ii. Delete the details of patient whose Bed_No is 1 and RoomNo is 105
DELETE FROM Patient WHERE PCode IN (SELECT PCode FROM Bed WHERE Bed_No = 1 AND RoomNo = 105);

-- iii. Display the names of patients who are admitted in room no 101
SELECT Name FROM Patient WHERE PCode IN (SELECT PCode FROM Bed WHERE RoomNo = 101);

-- iv. Display the disease of patient whose bed_No is 1
SELECT Disease FROM Patient WHERE PCode IN (SELECT PCode FROM Bed WHERE Bed_No = 1);

-- v. Display the room_no and bed_no of patient whose name is “Mr Roy”
SELECT RoomNo, Bed_No FROM Bed WHERE loc = (SELECT Addr FROM Patient WHERE Name = 'Mr. Roy');

-- vi. Give the details of Patient who is admitted on 2nd flr in roomno 102
SELECT * FROM Patient WHERE PCode IN (SELECT PCode FROM Bed WHERE RoomNo = 102 AND loc = '2nd flr');

Question no 5

---

Create a Relational database in 3 NF with appropriate data types and Constraints
Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)

i. Add Phone_No column in customer table with data type int.
ii. Delete the details of customer whose loan_amt<1000.
iii. Find details of all customers whose loan_amt is greater than 10 lack.
iv. List all customers whose name starts with 'D' character.
v. Display customer details having maximum loan amount
vi. Update the address of customer whose name is “Mr. Patil” and loan_amt is greater than 100000.

Solution…
-- Create database
CREATE DATABASE IF NOT EXISTS BankingDB;
USE BankingDB;

-- Create Customer table
CREATE TABLE IF NOT EXISTS Customer (
cust_no INT PRIMARY KEY,
cust_name VARCHAR(100) NOT NULL,
address VARCHAR(255) NOT NULL,
city VARCHAR(100),
Phone_No INT
);

-- Create Loan table
CREATE TABLE IF NOT EXISTS Loan (
loan_no INT PRIMARY KEY,
loan_amt DECIMAL(10, 2)
);

-- Insert values into Customer table
INSERT INTO Customer (cust_no, cust_name, address, city, Phone_No) VALUES
(1, 'John Doe', '123 Main St', 'New York', 1234567890),
(2, 'Jane Smith', '456 Elm St', 'Los Angeles', 9876543210),
(3, 'Alice Johnson', '789 Oak St', 'Chicago', 2345678901),
(4, 'Bob Williams', '101 Pine St', 'San Francisco', 3456789012),
(5, 'Mr. Patil', '222 Maple St', 'Mumbai', 4567890123);

-- Insert values into Loan table
INSERT INTO Loan (loan_no, loan_amt) VALUES
(101, 5000.00),
(102, 8000.00),
(103, 1200000.00),
(104, 3000.00),
(105, 150000.00);

-- i. Add Phone_No column in customer table with data type int
ALTER TABLE Customer ADD COLUMN Phone_No INT;

-- ii. Delete the details of customer whose loan_amt<1000
DELETE FROM Customer WHERE cust_no IN (SELECT cust_no FROM Loan WHERE loan_amt < 1000.00);

-- iii. Find details of all customers whose loan_amt is greater than 10 lakh
SELECT * FROM Customer WHERE cust_no IN (SELECT cust_no FROM Loan WHERE loan_amt > 1000000.00);

-- iv. List all customers whose name starts with 'D' character
SELECT * FROM Customer WHERE cust_name LIKE 'D%';

-- v. Display customer details having maximum loan amount
SELECT c.*
FROM Customer c
JOIN Loan l ON c.cust_no = l.loan_no
WHERE l.loan_amt = (SELECT MAX(loan_amt) FROM Loan);

-- vi. Update the address of customer whose name is “Mr. Patil” and loan_amt is greater than 100000
UPDATE Customer
SET address = 'New Address'
WHERE cust_name = 'Mr. Patil' AND cust_no IN (SELECT cust_no FROM Loan WHERE loan_amt > 100000);

Question no 6
Create a Relational database in 3 NF with appropriate data types and Constraints
Salesman(salesman_id, name, city, commission)
Customer(customer_id, cust_name, city, grade, salesman_id)
i. From the following tables write a SQL query to display the customer name, customer city, grade, salesman, salesman city
ii. From the following tables write a SQL query to find those customers with a grade less than 300. Return cust_name, customer city, grade, Salesman, salesmancity.

Solution…
-- Create database
CREATE DATABASE IF NOT EXISTS SalesDB;
USE SalesDB;

-- Create Salesman table
CREATE TABLE IF NOT EXISTS Salesman (
salesman_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(100),
commission DECIMAL(10, 2)
);

-- Create Customer table
CREATE TABLE IF NOT EXISTS Customer (
customer_id INT PRIMARY KEY,
cust_name VARCHAR(100) NOT NULL,
city VARCHAR(100),
grade INT,
salesman_id INT,
FOREIGN KEY (salesman_id) REFERENCES Salesman(salesman_id)
);

-- Insert values into Salesman table
INSERT INTO Salesman (salesman_id, name, city, commission) VALUES
(1, 'John Doe', 'New York', 0.15),
(2, 'Jane Smith', 'Los Angeles', 0.12),
(3, 'Alice Johnson', 'Chicago', 0.10);

-- Insert values into Customer table
INSERT INTO Customer (customer_id, cust_name, city, grade, salesman_id) VALUES
(101, 'Customer1', 'New York', 200, 1),
(102, 'Customer2', 'Los Angeles', 350, 2),
(103, 'Customer3', 'Chicago', 250, 3),
(104, 'Customer4', 'New York', 400, 1),
(105, 'Customer5', 'Chicago', 300, 3);

-- i. Display the customer name, customer city, grade, salesman, and salesman city
SELECT c.cust_name AS Customer_Name, c.city AS Customer_City, c.grade AS Grade,
s.name AS Salesman, s.city AS Salesman_City
FROM Customer c
JOIN Salesman s ON c.salesman_id = s.salesman_id;

-- ii. Find those customers with a grade less than 300. Return cust_name, customer city, grade, Salesman, and salesmancity
SELECT c.cust_name AS Customer_Name, c.city AS Customer_City, c.grade AS Grade,
s.name AS Salesman, s.city AS Salesman_City
FROM Customer c
JOIN Salesman s ON c.salesman_id = s.salesman_id
WHERE c.grade < 300;

Question no 7:
Create a Relational database in 3 NF with appropriate data types and Constraints
Room (roomno, desc, rate)
Guest (gno, gname, no_of_days)
i. Update the rate of room to 5000 whose type is “AC”
ii. Display the name of guest who is staying 2 days in roomno 101
iii. Display room details according to its rates in ascending order
iv. Display the roomno in which “Mr. Advait” is staying for 7 days
v. Find no. of AC rooms.
Find names of guest with maximum room charges

Solution….
-- Create database
CREATE DATABASE IF NOT EXISTS HotelDB;
USE HotelDB;

-- Create Room table
CREATE TABLE IF NOT EXISTS Room (
roomno INT PRIMARY KEY,
description VARCHAR(100),
rate DECIMAL(10, 2)
);

-- Create Guest table
CREATE TABLE IF NOT EXISTS Guest (
gno INT PRIMARY KEY,
gname VARCHAR(100) NOT NULL,
no_of_days INT
);

-- Insert values into Room table
INSERT INTO Room (roomno, description, rate) VALUES
(101, 'AC', 4500.00),
(102, 'Non-AC', 3000.00),
(103, 'AC', 5000.00),
(104, 'Non-AC', 3500.00),
(105, 'AC', 4800.00);

-- Insert values into Guest table
INSERT INTO Guest (gno, gname, no_of_days) VALUES
(1, 'John Doe', 3),
(2, 'Jane Smith', 2),
(3, 'Alice Johnson', 5),
(4, 'Bob Williams', 7),
(5, 'Mr. Advait', 7);

-- i. Update the rate of room to 5000 whose type is "AC"
UPDATE Room SET rate = 5000 WHERE description = 'AC';

-- ii. Display the name of guest who is staying 2 days in roomno 101
SELECT gname FROM Guest WHERE no_of_days = 2 AND gno IN (SELECT gno FROM Room WHERE roomno = 101);

-- iii. Display room details according to its rates in ascending order
SELECT * FROM Room ORDER BY rate ASC;

-- iv. Display the roomno in which "Mr. Advait" is staying for 7 days
SELECT roomno FROM Guest WHERE gname = 'Mr. Advait' AND no_of_days = 7;

-- v. Find no. of AC rooms
SELECT COUNT(*) AS AC_Rooms FROM Room WHERE description = 'AC';

-- vi. Find names of guest with maximum room charges
SELECT gname
FROM Guest
WHERE gno IN (SELECT gno FROM Room WHERE rate = (SELECT MAX(rate) FROM Room));

Question no 8
Create a Relational database in 3 NF with appropriate data types and Constraints
Book (Book_no, title, author, price, year_published) Customer (cid, cname, addr)
Display the name of book whose author is “Mr. Gadre”.
i. Add column EMailId into customer table.
ii. Display customer details from 'Mumbai'.
iii. Display book names having price between 100 and 200 and published year is 2019.
iv. Update the title of book to “DBMS” whose author is “Mr. Talore”.

Solution….
-- Create database
CREATE DATABASE IF NOT EXISTS LibraryDB;
USE LibraryDB;

-- Create Book table
CREATE TABLE IF NOT EXISTS Book (
Book_no INT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(100) NOT NULL,
price DECIMAL(10, 2),
year_published INT
);

-- Create Customer table
CREATE TABLE IF NOT EXISTS Customer (
cid INT PRIMARY KEY,
cname VARCHAR(100) NOT NULL,
addr VARCHAR(255),
EMailId VARCHAR(255)
);

-- Insert values into Book table
INSERT INTO Book (Book_no, title, author, price, year_published) VALUES
(1, 'Book1', 'Mr. Gadre', 150.00, 2019),
(2, 'Book2', 'Mr. Talore', 120.00, 2018),
(3, 'Book3', 'Mr. Gadre', 180.00, 2020),
(4, 'Book4', 'Mr. Patil', 220.00, 2019),
(5, 'Book5', 'Mr. Talore', 190.00, 2019);

-- Insert values into Customer table
INSERT INTO Customer (cid, cname, addr, EMailId) VALUES
(101, 'John Doe', 'Mumbai', '[email protected]'),
(102, 'Jane Smith', 'New York', '[email protected]'),
(103, 'Alice Johnson', 'Chicago', '[email protected]'),
(104, 'Bob Williams', 'Los Angeles', '[email protected]'),
(105, 'Mr. Patel', 'Mumbai', '[email protected]');

-- i. Add column EMailId into customer table
ALTER TABLE Customer ADD COLUMN EMailId VARCHAR(255);

-- ii. Display customer details from 'Mumbai'
SELECT * FROM Customer WHERE addr = 'Mumbai';

-- iii. Display book names having price between 100 and 200 and published year is 2019
SELECT title FROM Book WHERE price BETWEEN 100 AND 200 AND year_published = 2019;

-- iv. Update the title of book to “DBMS” whose author is “Mr. Talore”
UPDATE Book SET title = 'DBMS' WHERE author = 'Mr. Talore';

Question no 9
Create a Relational database in 3 NF with appropriate data types and Constraints
Property (pno, desc, area, rate)
Owner (owner_name, addr, phno)
i. Display area of property whose rate is less than 100000
ii. Display property owned by 'Mr.Patil' having minimum rate.
iii. Delete all properties from “pune” owned by “Mr. Joshi”.
iv. Update the phone Number of “Mr. Joshi” to 9922112233 who is having property at “Uruli Kanchan”

Solution…
-- Create database
CREATE DATABASE IF NOT EXISTS RealEstateDB;
USE RealEstateDB;

-- Create Property table
CREATE TABLE IF NOT EXISTS Property (
pno INT PRIMARY KEY,
description VARCHAR(255),
area DECIMAL(10, 2),
rate DECIMAL(10, 2)
);

-- Create Owner table
CREATE TABLE IF NOT EXISTS Owner (
owner_name VARCHAR(100) PRIMARY KEY,
addr VARCHAR(255),
phno VARCHAR(15)
);

-- Insert values into Property table
INSERT INTO Property (pno, description, area, rate) VALUES
(1, 'House', 1200.00, 95000.00),
(2, 'Apartment', 800.00, 85000.00),
(3, 'Villa', 2000.00, 150000.00),
(4, 'Land', 5000.00, 80000.00),
(5, 'Commercial Building', 3000.00, 200000.00);

-- Insert values into Owner table
INSERT INTO Owner (owner_name, addr, phno) VALUES
('Mr. Patil', 'Pune', '9876543210'),
('Mr. Joshi', 'Uruli Kanchan', '1234567890'),
('Mr. Shah', 'Mumbai', '8765432109'),
('Mr. Deshpande', 'Pune', '7890123456'),
('Mr. Kumar', 'Delhi', '9012345678');

-- i. Display area of property whose rate is less than 100000
SELECT area FROM Property WHERE rate < 100000;

-- ii. Display property owned by 'Mr. Patil' having minimum rate
SELECT * FROM Property WHERE rate = (SELECT MIN(rate) FROM Property WHERE pno IN (SELECT pno FROM Owner WHERE owner_name = 'Mr. Patil'));

-- iii. Delete all properties from “Pune” owned by “Mr. Joshi”
DELETE FROM Property WHERE pno IN (SELECT pno FROM Owner WHERE owner_name = 'Mr. Joshi' AND addr = 'Pune');

-- iv. Update the phone Number of “Mr. Joshi” to 9922112233 who is having property at “Uruli Kanchan”
UPDATE Owner SET phno = '9922112233' WHERE owner_name = 'Mr. Joshi' AND addr = 'Uruli Kanchan';

Question no 10:
Create a Relational database in 3 NF with appropriate data types and Constraints
Employee (emp_no, name, skill, payrate)
Position (posting_no, skill)
i. Update the posting of employee to 220 whose skill is “Manager”.
ii. Find the names and rate of pay of all employees who has allocated a duty.
iii. Give employee number who is working at posting_no. 201, but don’t have the skill of waiter
iv. Display a list of names of employees who have skill of chef and who has assigned a duty.
v. Update payrate of employees to 20000 whose skill is waiter.

Solution….
-- Create database
CREATE DATABASE IF NOT EXISTS CompanyDB;
USE CompanyDB;

-- Create Employee table
CREATE TABLE IF NOT EXISTS Employee (
emp_no INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
skill VARCHAR(100),
payrate DECIMAL(10, 2)
);

-- Create Position table
CREATE TABLE IF NOT EXISTS Position (
posting_no INT PRIMARY KEY,
skill VARCHAR(100)
);

-- Insert values into Employee table
INSERT INTO Employee (emp_no, name, skill, payrate) VALUES
(101, 'John Doe', 'Manager', 25000.00),
(102, 'Jane Smith', 'Chef', 18000.00),
(103, 'Alice Johnson', 'Waiter', 15000.00),
(104, 'Bob Williams', 'Waiter', 15000.00),
(105, 'Mr. Patel', 'Chef', 20000.00);

-- Insert values into Position table
INSERT INTO Position (posting_no, skill) VALUES
(201, 'Manager'),
(202, 'Chef'),
(203, 'Waiter'),
(204, 'Cleaner');

-- i. Update the posting of employee to 220 whose skill is “Manager”
UPDATE Employee SET emp_no = 220 WHERE skill = 'Manager';

-- ii. Find the names and rate of pay of all employees who has allocated a duty
SELECT e.name, e.payrate
FROM Employee e
JOIN Position p ON e.skill = p.skill;

-- iii. Give employee number who is working at posting_no. 201, but don’t have the skill of waiter
SELECT emp_no
FROM Employee
WHERE emp_no IN (SELECT emp_no FROM Position WHERE posting_no = 201) AND skill != 'Waiter';

-- iv. Display a list of names of employees who have skill of chef and who has assigned a duty
SELECT name
FROM Employee
WHERE skill = 'Chef' AND emp_no IN (SELECT emp_no FROM Position);

-- v. Update payrate of employees to 20000 whose skill is waiter
UPDATE Employee SET payrate = 20000 WHERE skill = 'Waiter';

Question no 11:
Create a Relational database in 3 NF with appropriate data types and Constraints
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)

i. Display the tableno whose dish_desc is “Veg”.
ii. Display the special menu of Monday.
iii. Display receipt which includes bill_no with Dish description, price, quantity and total amount of each menu.
iv. Find total amount collected by hotel on date 09/07/2019.
v. Count number of menus of billno 301
vi. Display menu details having price between 100 and 500.
vii. Display the tableno and day whose bill amount is zero.

Solution…

-- Create database
CREATE DATABASE IF NOT EXISTS RestaurantDB;
USE RestaurantDB;

-- Create Bill table
CREATE TABLE IF NOT EXISTS Bill (
billno INT PRIMARY KEY,
day DATE,
tableno INT,
total DECIMAL(10, 2)
);

-- Create Menu table
CREATE TABLE IF NOT EXISTS Menu (
dish_no INT PRIMARY KEY,
dish_desc VARCHAR(255),
price DECIMAL(10, 2)
);

-- Insert values into Bill table
INSERT INTO Bill (billno, day, tableno, total) VALUES
(301, '2019-07-09', 101, 1200.00),
(302, '2019-07-09', 102, 1500.00),
(303, '2019-07-10', 103, 800.00),
(304, '2019-07-10', 101, 0.00),
(305, '2019-07-11', 102, 2500.00);

-- Insert values into Menu table
INSERT INTO Menu (dish_no, dish_desc, price) VALUES
(1, 'Veg', 150.00),
(2, 'Non-Veg', 200.00),
(3, 'Salad', 100.00),
(4, 'Soup', 120.00),
(5, 'Dessert', 80.00);

-- i. Display the tableno whose dish_desc is “Veg”
SELECT tableno FROM Bill WHERE billno IN (SELECT billno FROM Menu WHERE dish_desc = 'Veg');

-- ii. Display the special menu of Monday
SELECT * FROM Menu WHERE dish_no IN (SELECT dish_no FROM Bill WHERE day = '2019-07-08');

-- iii. Display receipt which includes bill_no with Dish description, price, quantity and total amount of each menu
SELECT b.billno, m.dish_desc, m.price, 1 AS quantity, b.total
FROM Bill b
JOIN Menu m ON b.billno = m.dish_no;

-- iv. Find total amount collected by hotel on date 09/07/2019
SELECT SUM(total) AS total_amount_collected
FROM Bill
WHERE day = '2019-07-09';

-- v. Count number of menus of billno 301
SELECT COUNT(*) AS menu_count
FROM Menu
WHERE dish_no IN (SELECT dish_no FROM Bill WHERE billno = 301);

-- vi. Display menu details having price between 100 and 500
SELECT * FROM Menu WHERE price BETWEEN 100 AND 500;

-- vii. Display the tableno and day whose bill amount is zero
SELECT tableno, day
FROM Bill
WHERE total = 0.00;

Question no 12
Create a Relational database in 3 NF with appropriate data types and Constraints
Driver (driver_id, driver_name, address)
Car (license_no, model, year)
i. Display the name of driver whose license no is “DPU123”.
ii. Delete the details of car whose model is “swift”.
iii. Display details of all persons who are driving ‘Alto’ car
iv. Update model of car to “SUV300” whose manufactured year is 2019.
v. Display car details manufactured before year 2000.
vi. In which day ‘Mr. Ram’ drives maximum number of cars.
vii. Display total number of drivers who drives car in each year.

Solution….
-- Create database
CREATE DATABASE IF NOT EXISTS TransportDB;
USE TransportDB;

-- Create Driver table
CREATE TABLE IF NOT EXISTS Driver (
driver_id INT PRIMARY KEY,
driver_name VARCHAR(100) NOT NULL,
address VARCHAR(255)
);

-- Create Car table
CREATE TABLE IF NOT EXISTS Car (
license_no VARCHAR(20) PRIMARY KEY,
model VARCHAR(100),
year INT
);

-- Insert values into Driver table
INSERT INTO Driver (driver_id, driver_name, address) VALUES
(1, 'John Doe', '123 Main St'),
(2, 'Jane Smith', '456 Elm St'),
(3, 'Alice Johnson', '789 Oak St'),
(4, 'Bob Williams', '101 Pine St'),
(5, 'Mr. Ram', '202 Cedar St');

-- Insert values into Car table
INSERT INTO Car (license_no, model, year) VALUES
('DLU123', 'Alto', 2018),
('DPU123', 'Swift', 2019),
('KLI456', 'Alto', 2017),
('MNO789', 'SUV', 2020),
('XYZ101', 'Alto', 2015);

-- i. Display the name of driver whose license no is “DPU123”
SELECT driver_name FROM Driver WHERE driver_id = (SELECT driver_id FROM Car WHERE license_no = 'DPU123');

-- ii. Delete the details of car whose model is “swift”
DELETE FROM Car WHERE model = 'Swift';

-- iii. Display details of all persons who are driving ‘Alto’ car
SELECT d.*
FROM Driver d
JOIN Car c ON d.driver_id = c.driver_id
WHERE c.model = 'Alto';

-- iv. Update model of car to “SUV300” whose manufactured year is 2019
UPDATE Car SET model = 'SUV300' WHERE year = 2019;

-- v. Display car details manufactured before year 2000
SELECT * FROM Car WHERE year < 2000;

-- vi. In which day ‘Mr. Ram’ drives maximum number of cars
SELECT DAYNAME(MAX(date)) AS max_driving_day
FROM (
SELECT COUNT(*) AS num_cars, driver_id, DATE_FORMAT(day, '%Y-%m-%d') AS date
FROM Car
GROUP BY driver_id, DATE_FORMAT(day, '%Y-%m-%d')
HAVING driver_id = (SELECT driver_id FROM Driver WHERE driver_name = 'Mr. Ram')
) AS temp;

-- vii. Display total number of drivers who drives car in each year
SELECT YEAR(day) AS year, COUNT(DISTINCT driver_id) AS num_drivers
FROM Car
GROUP BY YEAR(day);

Question no 13
Create a Relational database in 3 NF with appropriate data types and Constraints
Salesman(salesman_id, name, city, commission)
Customer(customer_id, cust_name, city, grade, salesman_id)
i. From the following tables write a SQL query to find the salesperson and customer who reside in the same city. Return Salesman, cust_name and city.
ii. From the following tables write a SQL query to locate those salespeople who do not live in the same city where their customers live and have received a commission of more than 12% from the company. Return Customer Name, customer city, Salesman, salesman city, commission.

Solution….
-- Create database
CREATE DATABASE IF NOT EXISTS SalesDB;
USE SalesDB;

-- Create Salesman table
CREATE TABLE IF NOT EXISTS Salesman (
salesman_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(100),
commission DECIMAL(5, 2)
);

-- Create Customer table
CREATE TABLE IF NOT EXISTS Customer (
customer_id INT PRIMARY KEY,
cust_name VARCHAR(100) NOT NULL,
city VARCHAR(100),
grade INT,
salesman_id INT,
FOREIGN KEY (salesman_id) REFERENCES Salesman(salesman_id)
);

-- Insert values into Salesman table
INSERT INTO Salesman (salesman_id, name, city, commission) VALUES
(1, 'John Doe', 'New York', 0.15),
(2, 'Jane Smith', 'Los Angeles', 0.12),
(3, 'Alice Johnson', 'Chicago', 0.14),
(4, 'Bob Williams', 'Houston', 0.13),
(5, 'Mr. Patel', 'Los Angeles', 0.16);

-- Insert values into Customer table
INSERT INTO Customer (customer_id, cust_name, city, grade, salesman_id) VALUES
(101, 'ABC Corp', 'New York', 1, 1),
(102, 'XYZ Inc', 'Los Angeles', 2, 2),
(103, 'LMN Enterprises', 'Chicago', 1, 3),
(104, 'PQR Ltd', 'Houston', 3, 4),
(105, 'JKL Co', 'Miami', 2, 5);

-- i. Find the salesperson and customer who reside in the same city
SELECT s.name AS Salesman, c.cust_name AS Customer, s.city AS City
FROM Salesman s
JOIN Customer c ON s.city = c.city;

-- ii. Locate those salespeople who do not live in the same city where their customers live and have received a commission of more than 12% from the company
SELECT c.cust_name AS Customer_Name, c.city AS Customer_City, s.name AS Salesman, s.city AS Salesman_City, s.commission AS Commission
FROM Salesman s
JOIN Customer c ON s.salesman_id = c.salesman_id
WHERE s.city != c.city AND s.commission > 0.12;

Question no- 14
Create a Relational database in 3 NF with appropriate data types and Constraints
Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)
i. Find details of all customers whose loan is greater than 10 lakhs.
ii. List all customers whose name starts with 'ba'.
iii. Calculate total of all loan amount
iv. List names of all customers in descending order who has taken a loan in Nasik city.

Solution….
-- Create database
CREATE DATABASE IF NOT EXISTS BankDB;
USE BankDB;

-- Create Customer table
CREATE TABLE IF NOT EXISTS Customer (
cust_no INT PRIMARY KEY,
cust_name VARCHAR(100) NOT NULL,
address VARCHAR(255),
city VARCHAR(100)
);

-- Create Loan table
CREATE TABLE IF NOT EXISTS Loan (
loan_no INT PRIMARY KEY,
loan_amt DECIMAL(10, 2)
);

-- Insert values into Customer table
INSERT INTO Customer (cust_no, cust_name, address, city) VALUES
(1, 'John Doe', '123 Main St', 'Mumbai'),
(2, 'Jane Smith', '456 Elm St', 'Pune'),
(3, 'Alice Johnson', '789 Oak St', 'Nasik'),
(4, 'Bob Williams', '101 Pine St', 'Nasik'),
(5, 'Mr. Patel', '202 Cedar St', 'Mumbai');

-- Insert values into Loan table
INSERT INTO Loan (loan_no, loan_amt) VALUES
(101, 1500000.00),
(102, 800000.00),
(103, 1200000.00),
(104, 500000.00),
(105, 10000000.00);

-- i. Find details of all customers whose loan is greater than 10 lakhs
SELECT *
FROM Customer
WHERE cust_no IN (SELECT cust_no FROM Loan WHERE loan_amt > 1000000);

-- ii. List all customers whose name starts with 'ba'
SELECT *
FROM Customer
WHERE cust_name LIKE 'ba%';

-- iii. Calculate total of all loan amount
SELECT SUM(loan_amt) AS total_loan_amount
FROM Loan;

-- iv. List names of all customers in descending order who has taken a loan in Nasik city
SELECT c.cust_name
FROM Customer c
JOIN Loan l ON c.cust_no = l.loan_no
WHERE c.city = 'Nasik'
ORDER BY c.cust_name DESC;

Question no 15
Create a Relational database in 3 NF with appropriate data types and Constraints
Salesman(salesman_id, name, city, commission)
Customer(customer_id, cust_name, city, grade, salesman_id)
i. From the following tables write a SQL query to find the salesperson(s) and the customer(s) he represents. Return Customer Name, city, Salesman, commission.
From the following tables write a SQL query to find salespeople who received commissions of more than 12 percent from the company. Return Customer Name, customer city, Salesman, commission.
Solution…
-- Create database
CREATE DATABASE IF NOT EXISTS SalesDB;
USE SalesDB;

-- Create Salesman table
CREATE TABLE IF NOT EXISTS Salesman (
salesman_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(100),
commission DECIMAL(5, 2)
);

-- Create Customer table
CREATE TABLE IF NOT EXISTS Customer (
customer_id INT PRIMARY KEY,
cust_name VARCHAR(100) NOT NULL,
city VARCHAR(100),
grade INT,
salesman_id INT,
FOREIGN KEY (salesman_id) REFERENCES Salesman(salesman_id)
);

-- Insert values into Salesman table
INSERT INTO Salesman (salesman_id, name, city, commission) VALUES
(1, 'John Doe', 'New York', 0.15),
(2, 'Jane Smith', 'Los Angeles', 0.12),
(3, 'Alice Johnson', 'Chicago', 0.14),
(4, 'Bob Williams', 'Houston', 0.13),
(5, 'Mr. Patel', 'Los Angeles', 0.16);

-- Insert values into Customer table
INSERT INTO Customer (customer_id, cust_name, city, grade, salesman_id) VALUES
(101, 'ABC Corp', 'New York', 1, 1),
(102, 'XYZ Inc', 'Los Angeles', 2, 2),
(103, 'LMN Enterprises', 'Chicago', 1, 3),
(104, 'PQR Ltd', 'Houston', 3, 4),
(105, 'JKL Co', 'Miami', 2, 5);

-- i. Find the salesperson(s) and the customer(s) he represents
SELECT c.cust_name AS Customer_Name, c.city AS Customer_City, s.name AS Salesman, s.commission AS Commission
FROM Customer c
JOIN Salesman s ON c.salesman_id = s.salesman_id;

-- ii. Find salespeople who received commissions of more than 12 percent from the company
SELECT c.cust_name AS Customer_Name, c.city AS Customer_City, s.name AS Salesman, s.commission AS Commission
FROM Customer c
JOIN Salesman s ON c.salesman_id = s.salesman_id
WHERE s.commission > 0.12;

Question no 16
Create a Relational database in 3 NF with appropriate data types and Constraints
Department (dept_no, dept_name, location)
Employee (emp_no, emp_name, address, salary, designation)
i. Find total salary of all computer department employees.
ii. Find the name of department whose salary is above 10000.
iii. Count the number of employees in each department.
iv. Display the maximum salary of each department.
v. Display department wise employee list.

Solution…
-- Create database
CREATE DATABASE IF NOT EXISTS CompanyDB;
USE CompanyDB;

-- Create Department table
CREATE TABLE IF NOT EXISTS Department (
dept_no INT PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL,
location VARCHAR(100)
);

-- Create Employee table
CREATE TABLE IF NOT EXISTS Employee (
emp_no INT PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
address VARCHAR(255),
salary DECIMAL(10, 2),
designation VARCHAR(100),
dept_no INT,
FOREIGN KEY (dept_no) REFERENCES Department(dept_no)
);

-- Insert values into Department table
INSERT INTO Department (dept_no, dept_name, location) VALUES
(1, 'HR', 'New York'),
(2, 'Finance', 'Los Angeles'),
(3, 'Computer', 'Chicago'),
(4, 'Sales', 'Houston'),
(5, 'Marketing', 'Miami');

-- Insert values into Employee table
INSERT INTO Employee (emp_no, emp_name, address, salary, designation, dept_no) VALUES
(101, 'John Doe', '123 Main St', 12000.00, 'Manager', 3),
(102, 'Jane Smith', '456 Elm St', 9000.00, 'Analyst', 3),
(103, 'Alice Johnson', '789 Oak St', 11000.00, 'Developer', 3),
(104, 'Bob Williams', '101 Pine St', 10000.00, 'Analyst', 2),
(105, 'Mr. Patel', '202 Cedar St', 13000.00, 'Manager', 2),
(106, 'Mr. Jones', '303 Maple St', 8000.00, 'Sales Executive', 4),
(107, 'Ms. Brown', '404 Walnut St', 9500.00, 'Marketing Executive', 5),
(108, 'Mr. Smith', '505 Cherry St', 8500.00, 'Sales Executive', 4),
(109, 'Mr. Kumar', '606 Peach St', 10500.00, 'Marketing Manager', 5);

-- i. Find total salary of all computer department employees
SELECT SUM(salary) AS total_salary
FROM Employee
WHERE dept_no = (SELECT dept_no FROM Department WHERE dept_name = 'Computer');

-- ii. Find the name of department whose salary is above 10000
SELECT dept_name
FROM Department
WHERE dept_no IN (SELECT dept_no FROM Employee WHERE salary > 10000);

-- iii. Count the number of employees in each department
SELECT d.dept_name, COUNT(e.emp_no) AS num_employees
FROM Department d
LEFT JOIN Employee e ON d.dept_no = e.dept_no
GROUP BY d.dept_name;

-- iv. Display the maximum salary of each department
SELECT d.dept_name, MAX(e.salary) AS max_salary
FROM Department d
LEFT JOIN Employee e ON d.dept_no = e.dept_no
GROUP BY d.dept_name;

-- v. Display department wise employee list
SELECT d.dept_name, e.emp_name
FROM Department d
LEFT JOIN Employee e ON d.dept_no = e.dept_no
ORDER BY d.dept_name, e.emp_name;

Question no 17****
Create a Relational database in 3 NF with appropriate data types and Constraints
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD)
i. List the project name and department details worked in projects that are ‘Complete’.
ii. Display total budget of each department.
iii. Display incomplete project of each department
iv. Find the names of departments that have budget greater than 50000
v. Display all project working under 'Mr.Desai'.

Solution
-- Create database
CREATE DATABASE IF NOT EXISTS CompanyDB;
USE CompanyDB;

-- Create Project table
CREATE TABLE IF NOT EXISTS Project (
pno INT PRIMARY KEY,
pname VARCHAR(100) NOT NULL,
start_date DATE,
budget DECIMAL(10, 2),
status VARCHAR(50)
);

-- Create Department table
CREATE TABLE IF NOT EXISTS Department (
dno INT PRIMARY KEY,
dname VARCHAR(100) NOT NULL,
HOD VARCHAR(100)
);

-- Insert values into Project table
INSERT INTO Project (pno, pname, start_date, budget, status) VALUES
(1, 'Project A', '2023-01-01', 100000.00, 'Complete'),
(2, 'Project B', '2023-02-15', 75000.00, 'Complete'),
(3, 'Project C', '2023-03-20', 60000.00, 'Incomplete'),
(4, 'Project D', '2023-04-10', 80000.00, 'Complete'),
(5, 'Project E', '2023-05-05', 45000.00, 'Incomplete');

-- Insert values into Department table
INSERT INTO Department (dno, dname, HOD) VALUES
(1, 'HR', 'Mr. Smith'),
(2, 'Finance', 'Mr. Patel'),
(3, 'IT', 'Mr. Desai'),
(4, 'Marketing', 'Ms. Jones'),
(5, 'Operations', 'Mr. Kumar');

-- i. List the project name and department details worked in projects that are ‘Complete’
SELECT p.pname AS Project_Name, d.*
FROM Project p
JOIN Department d ON p.dno = d.dno
WHERE p.status = 'Complete';

-- ii. Display total budget of each department
SELECT d.dname AS Department_Name, SUM(p.budget) AS Total_Budget
FROM Project p
JOIN Department d ON p.dno = d.dno
GROUP BY d.dname;

-- iii. Display incomplete project of each department
SELECT d.dname AS Department_Name, p.pname AS Project_Name
FROM Project p
JOIN Department d ON p.dno = d.dno
WHERE p.status = 'Incomplete';

-- iv. Find the names of departments that have budget greater than 50000
SELECT dname AS Department_Name
FROM Department
WHERE dno IN (SELECT dno FROM Project GROUP BY dno HAVING SUM(budget) > 50000);

-- v. Display all project working under 'Mr. Desai'
SELECT p.*
FROM Project p
JOIN Department d ON p.dno = d.dno
WHERE d.HOD = 'Mr. Desai';

Question no-18
Create a Relational database in 3 NF with appropriate data types and Constraints
Room (roomno, desc, rate)
Guest (gno, gname, no_of_days)
i. Find the names of guest who has allocated room for more than 3 days
ii. Find no. of AC rooms.
iii. Display total amount for NON-AC rooms.
iv. Find names of guest with maximum room charges.

Solution
-- Create database
CREATE DATABASE IF NOT EXISTS HotelDB;
USE HotelDB;

-- Create Room table
CREATE TABLE IF NOT EXISTS Room (
roomno INT PRIMARY KEY,
description VARCHAR(100),
rate DECIMAL(10, 2)
);

-- Create Guest table
CREATE TABLE IF NOT EXISTS Guest (
gno INT PRIMARY KEY,
gname VARCHAR(100),
no_of_days INT
);

-- Insert values into Room table
INSERT INTO Room (roomno, description, rate) VALUES
(101, 'Standard AC', 2000.00),
(102, 'Deluxe AC', 3000.00),
(103, 'Standard Non-AC', 1500.00),
(104, 'Deluxe Non-AC', 2500.00),
(105, 'Suite AC', 5000.00);

-- Insert values into Guest table
INSERT INTO Guest (gno, gname, no_of_days) VALUES
(1, 'John Doe', 4),
(2, 'Jane Smith', 2),
(3, 'Alice Johnson', 5),
(4, 'Bob Williams', 3),
(5, 'Mr. Patel', 7);

-- i. Find the names of guests who have allocated a room for more than 3 days
SELECT gname
FROM Guest
WHERE no_of_days > 3;

-- ii. Find the number of AC rooms
SELECT COUNT(*) AS num_ac_rooms
FROM Room
WHERE description LIKE '%AC%';

-- iii. Display total amount for NON-AC rooms
SELECT SUM(rate) AS total_amount_non_ac_rooms
FROM Room
WHERE description NOT LIKE '%AC%';

-- iv. Find names of guests with maximum room charges
SELECT gname
FROM Guest
WHERE no_of_days * (SELECT MAX(rate) FROM Room) = (SELECT MAX(no_of_days * rate) FROM Guest);

Question no 19
Create a Relational database in 3 NF with appropriate data types and Constraints
Employee (emp_no, name, skill, payrate, workdate)
Position (posting_no, skill)
i. Find the names and rate of pay all employees who allocated a duty
ii. Give employee number who are working at posting_no. 201, but don’t have the skills of waiter.
iii. Display a list of names of employees who have skill of chef and who has assigned a duty.
iv. Display emp_no and dates for all employees who are working on Tuesday and at least one other day.
v. Display shiftwise employee details.

Solution
-- Create database
CREATE DATABASE IF NOT EXISTS CompanyDB;
USE CompanyDB;

-- Create Employee table
CREATE TABLE IF NOT EXISTS Employee (
emp_no INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
skill VARCHAR(100),
payrate DECIMAL(10, 2),
workdate DATE
);

-- Create Position table
CREATE TABLE IF NOT EXISTS Position (
posting_no INT PRIMARY KEY,
skill VARCHAR(100)
);

-- Insert values into Employee table
INSERT INTO Employee (emp_no, name, skill, payrate, workdate) VALUES
(101, 'John Doe', 'Chef', 15.00, '2023-01-01'),
(102, 'Jane Smith', 'Waiter', 12.00, '2023-01-02'),
(103, 'Alice Johnson', 'Chef', 14.00, '2023-01-03'),
(104, 'Bob Williams', 'Chef', 16.00, '2023-01-04'),
(105, 'Mr. Patel', 'Waiter', 13.00, '2023-01-05');

-- Insert values into Position table
INSERT INTO Position (posting_no, skill) VALUES
(201, 'Waiter'),
(202, 'Chef'),
(203, 'Manager');

-- i. Find the names and rate of pay of all employees who have allocated a duty
SELECT name, payrate
FROM Employee
WHERE skill IS NOT NULL;

-- ii. Give employee numbers who are working at posting_no. 201, but don’t have the skills of waiter
SELECT emp_no
FROM Employee
WHERE emp_no NOT IN (SELECT emp_no FROM Employee WHERE skill = 'Waiter')
AND emp_no IN (SELECT emp_no FROM Employee WHERE workdate IS NOT NULL);

-- iii. Display a list of names of employees who have the skill of chef and have assigned a duty
SELECT name
FROM Employee
WHERE skill = 'Chef' AND workdate IS NOT NULL;

-- iv. Display emp_no and dates for all employees who are working on Tuesday and at least one other day
SELECT emp_no, workdate
FROM Employee
WHERE DAYOFWEEK(workdate) = 3
AND emp_no IN (SELECT emp_no FROM Employee WHERE workdate IS NOT NULL GROUP BY emp_no HAVING COUNT(*) > 1);

-- v. Display shiftwise employee details
SELECT *
FROM Employee
ORDER BY emp_no, workdate;

Question no 20
Create a Relational database in 3 NF with appropriate data types and Constraints
Musician (mno, mname, addr, phno)
Album (title, copy_right_date, format)
i. Display all albums composed by ‘A R Rehman’.
ii. Display musician details who have composed Audio album.
iii. Find all musicians who have composed maximum albums.

Solution:
-- Create database
CREATE DATABASE IF NOT EXISTS MusicDB;
USE MusicDB;

-- Create Musician table
CREATE TABLE IF NOT EXISTS Musician (
mno INT PRIMARY KEY,
mname VARCHAR(100) NOT NULL,
addr VARCHAR(255),
phno VARCHAR(15)
);

-- Create Album table
CREATE TABLE IF NOT EXISTS Album (
title VARCHAR(100) PRIMARY KEY,
copy_right_date DATE,
format VARCHAR(50)
);

-- Insert values into Musician table
INSERT INTO Musician (mno, mname, addr, phno) VALUES
(1, 'A R Rehman', 'Chennai', '9876543210'),
(2, 'Hans Zimmer', 'Los Angeles', '1234567890'),
(3, 'John Williams', 'New York', '4567890123'),
(4, 'Mozart', 'Salzburg', '7890123456');

-- Insert values into Album table
INSERT INTO Album (title, copy_right_date, format) VALUES
('Roja', '1992-03-10', 'Audio'),
('Lagaan', '2001-02-15', 'Audio'),
('Gladiator', '2000-12-18', 'Audio'),
('Star Wars', '1977-05-25', 'Audio'),
('The Magic Flute', '1791-09-30', 'Audio');

-- i. Display all albums composed by 'A R Rehman'
SELECT *
FROM Album
WHERE title IN (SELECT title FROM Album WHERE title = 'Roja');

-- ii. Display musician details who have composed Audio albums
SELECT *
FROM Musician
WHERE mno IN (SELECT mno FROM Album WHERE format = 'Audio');

-- iii. Find all musicians who have composed maximum albums
SELECT mname
FROM Musician
WHERE mno IN (SELECT mno FROM Album GROUP BY mno HAVING COUNT() = (SELECT MAX(cnt) FROM (SELECT COUNT() AS cnt FROM Album GROUP BY mno) AS max_counts));

Question no 21
Create a Relational database in 3 NF with appropriate data types and Constraints
Sailor (sid, sname, age)
Boats (bid, bname, color)
i. Display details of all boats sailed by sailor ‘Ram’.
ii. Display Sailor names working on blue boat.
iii. Add bid column to sailor table.
iv. Count number of boats sailed by each sailor.
v. Find the name of sailor who sailed the boat on both Tuesday & Friday.
vi. Display details of the boats which is sailed maximum times on Sundays.

Solution…
-- Create database
CREATE DATABASE IF NOT EXISTS ShippingDB;
USE ShippingDB;

-- Create Sailor table
CREATE TABLE IF NOT EXISTS Sailor (
sid INT PRIMARY KEY,
sname VARCHAR(100) NOT NULL,
age INT,
bid INT,
FOREIGN KEY (bid) REFERENCES Boats(bid)
);

-- Create Boats table
CREATE TABLE IF NOT EXISTS Boats (
bid INT PRIMARY KEY,
bname VARCHAR(100),
color VARCHAR(50)
);

-- Insert values into Sailor table
INSERT INTO Sailor (sid, sname, age, bid) VALUES
(1, 'Ram', 25, 101),
(2, 'Shyam', 28, 102),
(3, 'Raju', 30, 103),
(4, 'Amit', 22, 101),
(5, 'Sita', 26, 102);

-- Insert values into Boats table
INSERT INTO Boats (bid, bname, color) VALUES
(101, 'Speedy', 'Blue'),
(102, 'Seaworthy', 'Red'),
(103, 'Sailor's Delight', 'Green'),
(104, 'Ocean Cruiser', 'Blue'),
(105, 'Wave Rider', 'Yellow');

-- i. Display details of all boats sailed by sailor 'Ram'
SELECT b.*
FROM Boats b
JOIN Sailor s ON b.bid = s.bid
WHERE s.sname = 'Ram';

-- ii. Display Sailor names working on blue boats
SELECT sname
FROM Sailor s
JOIN Boats b ON s.bid = b.bid
WHERE b.color = 'Blue';

-- iii. Add bid column to Sailor table
-- ALTER TABLE Sailor ADD COLUMN bid INT;

-- iv. Count number of boats sailed by each sailor
SELECT s.sname, COUNT(*) AS num_boats
FROM Sailor s
GROUP BY s.sname;

-- v. Find the name of sailor who sailed the boat on both Tuesday & Friday
SELECT s.sname
FROM Sailor s
JOIN Voyages v ON s.sid = v.sid
WHERE v.day = 'Tuesday'
AND EXISTS (SELECT * FROM Voyages WHERE sid = s.sid AND day = 'Friday');

-- vi. Display details of the boats which are sailed maximum times on Sundays
SELECT b.*
FROM Boats b
JOIN Voyages v ON b.bid = v.bid
WHERE v.day = 'Sunday'
GROUP BY b.bid
HAVING COUNT() = (SELECT MAX(cnt) FROM (SELECT COUNT() AS cnt FROM Voyages WHERE day = 'Sunday' GROUP BY bid) AS max_counts);

Question no 22
Create a Relational database in 3 NF with appropriate data types and Constraints
Supplier (sid, sname, addr)
Parts (pid, pname, pdesc)
i. Display Supplier details from 'Mumbai' city.
ii. Display all suppliers who supply part ‘wheel’ and also display its cost.
iii. Find the names of supplier who stay in “Pune”.
iv. Add pid column to the supplier table.

Solution..

-- Create database
CREATE DATABASE IF NOT EXISTS SupplyChainDB;
USE SupplyChainDB;

-- Create Supplier table
CREATE TABLE IF NOT EXISTS Supplier (
sid INT PRIMARY KEY,
sname VARCHAR(100) NOT NULL,
addr VARCHAR(255),
pid INT,
FOREIGN KEY (pid) REFERENCES Parts(pid)
);

-- Create Parts table
CREATE TABLE IF NOT EXISTS Parts (
pid INT PRIMARY KEY,
pname VARCHAR(100) NOT NULL,
pdesc VARCHAR(255)
);

-- Insert values into Supplier table
INSERT INTO Supplier (sid, sname, addr, pid) VALUES
(1, 'ABC Supplies', 'Mumbai', 101),
(2, 'XYZ Parts', 'Pune', 102),
(3, 'LMN Components', 'Mumbai', 103),
(4, 'PQR Tools', 'Pune', 104),
(5, 'EFG Hardware', 'Mumbai', 105);

-- Insert values into Parts table
INSERT INTO Parts (pid, pname, pdesc) VALUES
(101, 'Wheel', 'Round part for vehicle'),
(102, 'Engine', 'Component for power generation'),
(103, 'Screwdriver', 'Tool for fastening screws'),
(104, 'Drill Bit', 'Tool for making holes'),
(105, 'Hammer', 'Tool for pounding nails');

-- i. Display Supplier details from 'Mumbai' city
SELECT *
FROM Supplier
WHERE addr = 'Mumbai';

-- ii. Display all suppliers who supply part 'wheel' and also display its cost
SELECT s.sname, p.pname
FROM Supplier s
JOIN Parts p ON s.pid = p.pid
WHERE p.pname = 'Wheel';

-- iii. Find the names of suppliers who stay in 'Pune'
SELECT sname
FROM Supplier
WHERE addr = 'Pune';

-- iv. Add pid column to the supplier table
-- ALTER TABLE Supplier ADD COLUMN pid INT;