-- Insert EMPSALARY data
INSERT INTO EMPSALARY VALUES (1001, 10000, 3000, 'Manager');
INSERT INTO EMPSALARY VALUES (1002, 2000, 1000, 'Salesman');
INSERT INTO EMPSALARY VALUES (1004, 5000, 500, 'Director');
INSERT INTO EMPSALARY VALUES (1015, 6000, 1200, 'Clerk');
INSERT INTO EMPSALARY VALUES (1016, 8000, 1000, 'Clerk');
INSERT INTO EMPSALARY VALUES (1020, 6000, 1200, 'Salesman');
-- Q1. Employees in Paris
SELECT FIRSTNAME, LASTNAME, ADDRESS, CITY
FROM EMPLOYEE
WHERE CITY = 'Paris';
-- Q2. Total count of employees, order by FIRSTNAME descending
SELECT COUNT(*) AS TOTAL_EMPLOYEES FROM EMPLOYEE;
SELECT * FROM EMPLOYEE ORDER BY FIRSTNAME DESC;
-- Q3. FIRSTNAME and SALARY of Salesman
SELECT [Link], [Link]
FROM EMPLOYEE E
JOIN EMPSALARY S ON [Link] = [Link]
WHERE [Link] = 'Salesman';
-- Q4. FIRSTNAME, LASTNAME, TOTAL SALARY
SELECT [Link], [Link], ([Link] + [Link]) AS TOTAL_SALARY
FROM EMPLOYEE E
JOIN EMPSALARY S ON [Link] = [Link];
-- Q5. Employees > 1 year
SELECT FIRSTNAME, LASTNAME
FROM EMPLOYEE
WHERE DATEDIFF(CURDATE(), HIRE_DATE) > 365;
-- Q6. Count by designation
SELECT DESIGNATION, COUNT(*) AS TOTAL
FROM EMPSALARY
GROUP BY DESIGNATION;
-- Q7. Names with exactly 6 letters
SELECT FIRSTNAME
FROM EMPLOYEE
WHERE LENGTH(FIRSTNAME) = 6;
-- Q8. Add Phone column
ALTER TABLE EMPLOYEE ADD PHONE VARCHAR(15);
-- Q9. Update Phone number
UPDATE EMPLOYEE SET PHONE = '9876543210' WHERE EMPID = 1001;
-- Q10. Employees hired between two dates
SELECT FIRSTNAME, HIRE_DATE
-- Drop existing tables if they exist FROM EMPLOYEE
DROP TABLE IF EXISTS EMPSALARY; WHERE HIRE_DATE BETWEEN '2007-06-15' AND '2008-06-15';
DROP TABLE IF EXISTS EMPLOYEE;
-- Q11. Salary slip with calculations
-- Create EMPLOYEE table SELECT
CREATE TABLE EMPLOYEE ( [Link],
EMPID INT PRIMARY KEY, [Link],
FIRSTNAME VARCHAR(50), [Link],
LASTNAME VARCHAR(50), [Link] * 0.50 AS HRA,
HIRE_DATE DATE, [Link] * 0.30 AS DA,
ADDRESS VARCHAR(100), [Link] * 0.12 AS PF,
CITY VARCHAR(50) ([Link] + [Link] + ([Link] * 0.50) + ([Link] * 0.30) - ([Link] * 0.12)) AS GROSS
); FROM EMPLOYEE E
JOIN EMPSALARY S ON [Link] = [Link]
-- Create EMPSALARY table ORDER BY GROSS DESC;
CREATE TABLE EMPSALARY (
EMPID INT PRIMARY KEY,
SALARY INT,
BENEFITS INT,
DESIGNATION VARCHAR(50),
FOREIGN KEY (EMPID) REFERENCES EMPLOYEE(EMPID)
);
-- Insert EMPLOYEE data
INSERT INTO EMPLOYEE VALUES (1001, 'George', 'Smith', '2006-05-11', '83 first street', 'Paris');
INSERT INTO EMPLOYEE VALUES (1002, 'Mary', 'Jones', '2008-02-25', '842 Vine Ave', 'Losantville');
INSERT INTO EMPLOYEE VALUES (1004, 'Peter', 'Thomas', '2008-09-12', '32 Elm St.', 'Paris');
INSERT INTO EMPLOYEE VALUES (1015, 'Peter', 'Thompson', '2006-12-19', '116 Red Road', 'Paris');
INSERT INTO EMPLOYEE VALUES (1016, 'Sarah', 'Sharma', '2007-07-24', '400 MG Road', 'New Delhi');
INSERT INTO EMPLOYEE VALUES (1020, 'Monika', 'Gupta', '2008-07-01', '9 Bandra', 'Mumbai');
-- Drop tables if they exist
DROP TABLE IF EXISTS Issued; -- 10. Sort by Book name ascending, then price descending
DROP TABLE IF EXISTS Books; SELECT Book_Name, Author_Name, Price
FROM Books
-- Create Books table ORDER BY Book_Name ASC, Price DESC;
CREATE TABLE Books (
Book_Id VARCHAR(10) PRIMARY KEY,
Book_Name VARCHAR(100),
Author_Name VARCHAR(100),
Publishers VARCHAR(50),
Price INT,
Type VARCHAR(20),
Quantity INT
);
-- Create Issued table
CREATE TABLE Issued (
Book_Id VARCHAR(10),
Quantity_Issued INT,
FOREIGN KEY (Book_Id) REFERENCES Books(Book_Id)
);
-- Insert into Books
INSERT INTO Books VALUES ('C0001', 'The Klone and I', 'Lata Kapoor', 'EPP', 355, 'Novel', 5);
INSERT INTO Books VALUES ('F0001', 'The Tears', 'William Hopkins', 'First Publ.', 650, 'Fiction', 10);
INSERT INTO Books VALUES ('T0001', 'My First C++', 'Brain & Brooke', 'ERP', 350, 'Text', 10);
INSERT INTO Books VALUES ('T0002', 'C++ Brainworks', '[Link]', 'TDH', 350, 'Text', 11);
INSERT INTO Books VALUES ('F0002', 'Thunderbolts', 'Ana Roberts', 'First Publ.', 750, 'Fiction', 50);
-- Insert into Issued
INSERT INTO Issued VALUES ('T0001', 4);
INSERT INTO Issued VALUES ('C0001', 5);
INSERT INTO Issued VALUES ('F0001', 2);
INSERT INTO Issued VALUES ('T0002', 3);
INSERT INTO Issued VALUES ('F0002', 8);
-- 1. Show Book name, Author name and price of books of First Publ.
SELECT Book_Name, Author_Name, Price
FROM Books
WHERE Publishers = 'First Publ.';
-- 2. Books with quantity > 8 and price < 500
SELECT Book_Id, Book_Name, Publishers
FROM Books
WHERE Quantity > 8 AND Price < 500;
-- 3. Books NOT published by ERP and price between 300 and 700
SELECT Book_Id, Book_Name, Author_Name
FROM Books
WHERE Publishers <> 'ERP' AND Price BETWEEN 300 AND 700;
-- 4. Bill with Book Id, Book Name, Publishers, Price, Quantity, 4% VAT, Total
SELECT
Book_Id, Book_Name, Publishers, Price, Quantity,
(Price * 0.04) AS VAT,
(Price + (Price * 0.04)) AS Total
FROM Books;
-- 5. Book details with Book IDs in list
SELECT *
FROM Books
WHERE Book_Id IN ('C0001', 'F0001', 'T0002', 'F0002');
-- 6. Books not of type Novel or Fiction
SELECT *
FROM Books
WHERE Type NOT IN ('Novel', 'Fiction');
-- 7. Author names starting with 'A'
SELECT *
FROM Books
WHERE Author_Name LIKE 'A%';
-- 8. Author names starting with 'T' AND ending with 's'
SELECT *
FROM Books
WHERE Author_Name LIKE 'T%' AND Author_Name LIKE '%s';
-- 9. Join Books and Issued
SELECT
B.Book_Id, B.Book_Name, B.Author_Name, [Link], I.Quantity_Issued
FROM Books B
JOIN Issued I ON B.Book_Id = I.Book_Id;