SQL Assessment 2
Adarsh Choudhary
M2022BSASS003
Step 1: Creating the Database
1.1 Create the Database
Following command to create a new database named AssessmentDB:
1. CREATE DATABASE AssessmentDB;
Switch to the newly created database:
1. USE AssessmentDB;
Step 2: Creating and Populate the Workers Table
2.1 Creating the Workers Table
The Workers table contains the following fields: Worker_ID, First_Name, Last_Name, Salary,
Joining_Date, and Department.
1. CREATE TABLE Workers (
2. Worker_ID INT PRIMARY KEY,
3. First_Name VARCHAR(50),
4. Last_Name VARCHAR(50),
5. Salary INT,
6. Joining_Date DATE,
7. Department VARCHAR(50)
8. );
2.2 Populating the Workers Table
Inserting the provided data into the Workers table:
1. INSERT INTO Workers (Worker_ID, First_Name, Last_Name, Salary, Joining_Date, Department) VALUES
2. (1, 'Monika', 'Arora', 100000, '2014-02-20', 'HR'),
3. (2, 'Niharika', 'Verma', 80000, '2016-06-11', 'Admin'),
4. (3, 'Jenisha', 'Patel', 350000, '2018-02-20', 'Programmer'),
5. (4, 'Amitabh', 'Singh', 50000, '2014-02-20', 'Admin'),
6. (5, 'Vivek', 'Bhatia', 500000, '2018-06-11', 'Admin'),
7. (6, 'Vipul', 'Diwan', 200000, '2014-06-11', 'Account'),
8. (7, 'Satish', 'Kumar', 75000, '2020-01-20', 'Account'),
9. (8, 'Geetika', 'Chauhan', 90000, '2017-04-10', 'Admin');
2.3 Verifying the Data
1. SELECT * FROM Workers;
Output:
Step 3: Executing the Queries
Question 1: Arrange Workers by Salary in Decreasing Order
This query sorts all workers by salary in descending order:
1. SELECT *
2. FROM Workers
3. ORDER BY Salary DESC;
Output:
Question 2: Retrieve the First Two Characters of Each Worker’s First Name
This query extracts the first two characters of each worker’s first name:
1. SELECT SUBSTRING(First_Name, 1, 2) AS First_Two_Chars
2. FROM Workers;
Output:
Question 3: Count Workers Who Joined Between April 10, 2017, and June 20,
2018
This query counts workers whose joining dates fall within the specified range:
1. SELECT COUNT(*) AS Worker_Count
2. FROM Workers
3. WHERE Joining_Date BETWEEN '2017-04-10' AND '2018-06-20';
Output:
Question 4: Display the Second-to-Last Record
This query retrieves the second-to-last record from the Workers table:
1. SELECT *
2. FROM Workers
3. ORDER BY Worker_ID DESC
4. LIMIT 1 OFFSET 1;
Output:
Question 5: Exclude Workers Named 'Vipul' and 'Jenisha'
This query excludes workers with the first names "Vipul" and "Jenisha":
1. SELECT *
2. FROM Workers
3. WHERE First_Name NOT IN ('Vipul', 'Jenisha');
Output:
Question 6: Group Records by Department, Arranged in Ascending Order of
Salary
This query groups workers by department and arranges them in ascending order of salary:
1. SELECT Department, First_Name, Salary
2. FROM Workers
3. ORDER BY Department ASC, Salary ASC;
Output:
Question 7: Display Only Odd Rows Based on Worker_ID
This query retrieves rows where the Worker_ID is odd:
1. SELECT *
2. FROM Workers
3. WHERE MOD(Worker_ID, 2) <> 0;
Output:
Question 8: Display Bottom 15% of Records by Salary
This query displays the bottom 15% of records when sorted by salary:
1. SELECT CEIL(0.15 * COUNT(*)) AS LimitValue FROM Workers;
2. SELECT *
3. FROM Workers
4. ORDER BY Salary ASC
5. LIMIT 2;
Output:
Question 9: Fetch Each Department Along with the Total Salaries Paid
This query calculates the total salaries paid for each department:
1. SELECT Department,
2. SUM(Salary) AS Total_Salaries
3. FROM Workers
4. GROUP BY Department;
Output:
Question 10: Find Customers Who Have Not Placed Any Orders
This query retrieves customers who have not placed any orders. First, create the Customers and
Orders tables:
Create the Customers Table:
1. CREATE TABLE Customers (
2. Customer_ID INT PRIMARY KEY,
3. Customer_Name VARCHAR(50)
4. );
Create the Orders Table:
1. CREATE TABLE Orders (
2. Order_ID INT PRIMARY KEY,
3. Customer_ID INT,
4. Order_Date DATE,
5. Order_Total INT,
6. FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
7. );
Insert Data into Customers and Orders:
1. INSERT INTO Customers (Customer_ID, Customer_Name) VALUES
2. (1, 'Alice'),
3. (2, 'Bob'),
4. (3, 'Charlie'),
5. (4, 'Diana');
6.
7. INSERT INTO Orders (Order_ID, Customer_ID, Order_Date, Order_Total) VALUES
8. (101, 1, '2023-01-01', 500),
9. (102, 3, '2023-01-05', 300);
Query to Find Customers Without Orders:
1. SELECT Customer_ID,
2. Customer_Name
3. FROM Customers
4. WHERE Customer_ID NOT IN (
5. SELECT DISTINCT Customer_ID
6. FROM Orders
7. );
Output:
