DO NOT RECORD YOUR RESPONSES HERE IN THIS DOCUMENT.
INSTEAD DOWNLOAD THIS DOCUMENT TO YOUR LOCAL SYSTEM and RECORD YOUR RESPONSES IN THAT FILE.
SECTION1 – JOINS: 10 Q’s
Q1) From the table above, find employees with their department names.
Ans – SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d. DepartmentID;
Q2) From the table above, list all employees, including those without a department.
Ans - SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;
Q3) From the table above, list all employees and departments, showing matches where possible.
Ans -
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
UNION
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;
Q4) From the table above, Generate all possible pairs of employees and projects.
Ans - SELECT e.EmployeeID, e.Name, p.ProjectID, p.ProjectName
FROM Employees e
CROSS JOIN Projects p;
Q5) From the table above, Find pairs of employees with the same department.
SELECT e1.EmployeeID AS Emp1_ID, e1.Name AS Emp1_Name,
e2.EmployeeID AS Emp2_ID, e2.Name AS Emp2_Name, e1.DepartmentID
FROM Employees e1
JOIN Employees e2
ON e1.DepartmentID = e2.DepartmentID
WHERE e1.EmployeeID < e2.EmployeeID;
Q6) From the table above, write a query to find the employees who are either not assigned to a department or work in a department that has
no associated projects.
Ans -
SELECT e.EmployeeID, e.Name
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
LEFT JOIN Projects p ON d.DepartmentID = p.DepartmentID
WHERE e.DepartmentID IS NULL OR p.ProjectID IS NULL;
Q7) Generate a list of all dates in December 2024 (HINT: Data to be created and use the appropriate join types (INNER, LEFT, RIGHT, FULL,
CROSS) to get the output something like the below:
“2024-12-01
2024-12- 02
2024-12-03"
Ans -- --- unable to solve
Q8) A company sells laptops with different configurations of processors, RAM, and storage as per the below.
Write SQL queries (CREATE, INSERT, & SELECT, etc) to generate all possible combinations.
Ans ----
CREATE TABLE PROCESSORS (
PROCESSORID INT PRIMARY KEY,
PROCESSOR_NAME VARCHAR(50));
CREATE TABLE RAM (
RAMID INT PRIMARY KEY,
RAM_SIZE VARCHAR(20));
CREATE TABLE STORAGE(
STORAGEID INT PRIMARY KEY,
STORAGE_SIZE VARCHAR(20));
-- INSERT INTO Processors (PROCESSORID, PROCESSOR_NAME) VALUES (1, 'Intel i5'), (2, 'Intel i7'), (3, 'AMD Ryzen 5');
-- RAM INSERT INTO RAM (RAMID, RAM_SIZE) VALUES (1, '8GB'), (2, '16GB');
--INSERT INTO STORAGE (STORAGEID, STORAGE_SIZE) VALUES (1, '256 GB SSD'), (2, '512 GB SSD'), (3, '1 TB SSD');
SELECT P.PROCESSOR_NAME, R.RAM_SIZE, S.STORAGE_SIZE FROM PROCESSORS P CROSS JOIN RAM R CROSS JOIN STORAGE S;
Q9) Write SQL queries (CREATE, INSERT, SELECT), A retailer wants to identify combinations of products and stores where a product isn’t
available.
ANS ----
CREATE TABLE PRODUCTS
( PRODUCT_ID VARCHAR(20) PRIMARY KEY, PRODUCT_NAME VARCHAR(50) );
CREATE TABLE STORES
( STORE_ID INT PRIMARY KEY, STORE_NAME VARCHAR(50) );
CREATE TABLE INVENTORY
( PRODUCT_ID VARCHAR(20), STORE_ID INT)
PRIMARY KEY (PRODUCT_ID, STORE_ID),
FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID),
FOREIGN KEY (STORE_ID) REFERENCES STORES(STORE_ID) );
-- INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME) VALUES ('ax890000869', 'LAPTOP'), ('py562MM220', 'TABLET'), ('00005233kh',
'SMARTPHONE');
-- INSERT INTO STORES (STORE_ID, STORE_NAME) VALUES (1, 'STOREXYZ'), (2, 'STOREABX');
-- INSERT INTO INVENTORY (PRODUCT_ID, STORE_ID) VALUES ('ax890000869', 1), ('py562MM220', 2), ('00005233kh', 1), ('00005233kh', 2);
SELECT p.PRODUCT_ID, p.PRODUCT_NAME, s.STORE_ID, s.STORE_NAME
FROM PRODUCTS p
CROSS JOIN STORES s LEFT JOIN INVENTORY i
ON p.PRODUCT_ID = i.PRODUCT_ID AND s.STORE_ID = i.STORE_ID
WHERE i.PRODUCT_ID IS NULL;
Q10) Write a query to pair every product with every other product to analyze cross-sales opportunities.
SELECT p1.PRODUCT_NAME AS Product1, p2.PRODUCT_NAME AS Product2
FROM PRODUCTS p1
CROSS JOIN PRODUCTS p2
WHERE p1.PRODUCT_ID < p2.PRODUCT_ID;
SECTION2 - Common Table Expressions (CTE): 2 Q’s
Q1) Based on the below reporting hierarchy, find their managers for each employee in an organization and the output to be as follows:
ANS--
WITH RECURSIVE EmployeeHierarchy AS
SELECT
;
Q2) Calculate a running total of sales for each product by date as per the below data
Ans ----
SELECT ProductID,SaleDate, Amount, SUM(Amount)
OVER (
PARTITION BY ProductID
ORDER BY SaleDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal
FROM Sales
ORDER BY ProductID, SaleDate;
SECTION3 - Functions (String, Date & Time, Numeric, Statistical): 12 Q’s
Q1) Write a SQL query to parse full names into First Name, and Last Name
Customer
CUSTOMERID FULL_NAME
1 Alice Johnson
2 Bob Smith
3 Charlie Brown
Ans --- need to work on logic
Q2) Find Contacts with phone numbers not in the format of (XXX) XXX-XXXX
CONTACTS
CONTACTID PHONE_NUMBER
1 (123) 456-7890
2 1234567890
3 (987) 654-3210
Ans --- unable to recollect regexp concept here
Q3) Write a SQL query to convert email addresses to lowercase and remove spaces
EMAILs
EMAIL_ID EMAIL_ADDRESS
1 User@Examp le.com
2 ADMIN@Example.ORG
SELECT EMAIL_ID, REPLACE(LOWER(EMAIL_ADDRESS), ' ') AS EMAIL_ADDRESS FROM EMAILs;
Q4) Write a SQL query to find the current age based on their birthdates.
Q5) Write a query to find the missing dates from the below table.
Q6) Write a SQL query to round a set of prices to the nearest dollar and truncate to two decimals.
Anss--- SELECT PRODUCTID, TRUNCATE(PRICE, 2) AS PRICE_TRUNCATED FROM PRICES;
Q7) Write a SQL query to compute the standard deviation and variance for a set of sales based on the table below:
Anss --- forgot the syntax
Q8) Write a SQL query to calculate rank and percentile ranks for the students below based on their scores.
Anss ---select Student_ID, Score, rank() over (order by Score desc) as RANK,
percent_rank() over (order by Score desc) as PERCENTILE_RANK
from STUDENTS;
Q9) Write a SQL query to extract the domain name from email addresses.
Q10) Write a SQL query to extract numeric values from mixed alphanumeric strings.
Q11) Extract first 3 characters from the below given product code
Ans --- SELECT PRODUCT_ID, PRODUCT_CODE, SUBSTRING(PRODUCT_CODE, 1, 3) AS FIRST_3_CHARACTERS FROM PRODUCT;
Q12) Write a query to replace all occurances of a substring in a text
SECTION4 - CASE STATEMENTS: 3Q’s
Q1) Calculate total sales for each region, but group "East" and "West" into "Domestic" and others into "International."
Ans ---
SELECT
CASE
WHEN REGION IN ('East', 'West') THEN 'Domestic'
ELSE 'International'
END AS RegionGroup,
SUM(AMOUNT) AS TotalSales
FROM SALES
GROUP BY RegionGroup;
Q2) Assign grades based on student scores as per the below.
SELECT STUDENT_ID, SCORE,
CASE
WHEN SCORE >= 90 THEN 'A'
WHEN SCORE >= 80 THEN 'B'
WHEN SCORE >= 70 THEN 'C'
ELSE 'D'
END AS GRADE
FROM GRADES;
Q3) Write a SQL query to apply a discount based on the product category
SELECT PRODUCT_ID, CATEGORY, PRICE,
CASE
WHEN CATEGORY = 'ELECTRONICS' THEN PRICE * 0.10
WHEN CATEGORY = 'FURNITURE' THEN PRICE * 0.15
ELSE PRICE * 0.59
END AS DISCOUNT
FROM PRODUCTS;
SECTION5 – Subqueries: 3Q’s
Q1) Write a query to find all customers who have spent more than the average purchase amount.
Ans -- SELECT CustomerID, PurchaseAmount
FROM PURCHASES
WHERE PurchaseAmount > (
SELECT AVG(PurchaseAmount) FROM PURCHASES;
Q2)Find total sales by customer, add a column showing total sales for each customer
Ans --
SELECT CustomerID, SUM(SalesAmount) AS TotalSales
FROM SALES
GROUP BY CustomerID;
Q3) Find top sales per customer, highest sales for each customer (refer to Q2 – SALES table of this section for your reference)
Ans -- SELECT CustomerID,MAX(SalesAmount) AS TopSale
FROM SALES
GROUP BY CustomerID;
----------------------------------------------------------------------------ANSWERS---------------------------------------------------------------------------------------
