The usage of SQL Programming language is used to fetch data from Database is demonstrated. There are three problem statements related to Customer orders, Employee incentives and research papers of an university.
The project is devided into three parts:
- creating tables and inserting data into Database
- Understanding attributes, relationship between entities with the help of ER diagrams
- Fetch data as per requirement for Analytical insights, the following sql syntax/functions used:
- Aggregate Functions
- Filters
- Clauses
- Sub Queries(nested,correlated)
- Joins
- Window Functions
Find all the non-local orders by looking at the salesmen that generated orders for their customers but are located elsewhere unlike their customers, and fetch the details like order_no, name of the customer, customer_id, salesman_id.
SELECT
o.order_no, c.name, o.customer_id, s.id -- required fields from tables
FROM
salesman s -- Inner Join & alliasing
INNER JOIN customer c
ON c.salesman_id = s.id
INNER JOIN orders o
ON o.customer_id = c.id -- Appropriate relations on common fields
WHERE
s.city != c.city -- for non local orders
ORDER BY
order_no -- sorting the orders
A food store is planning to give incentive to it's employees based on their monthly performance in sales.Incentive is different for each position and each position have it's max limit for incentive. Sales target to avail the incentive is a milestone. Employee makes incentive everytime he completes a milestone of sales. Milestone is different for each position. You are given the data for a month in the database store_data. Task: Find amount of incentive made by each employee.
SELECT
e.employee_id,
CASE
WHEN ((ROUND(e.emp_sale/i.sales_milestone))*i.incentive) < i.cap
THEN ((ROUND(e.emp_sale/i.sales_milestone))*i.incentive)
WHEN ((ROUND(e.emp_sale/i.sales_milestone))*i.incentive) > i.cap
THEN i.cap
END incentive_made -- use of CASE clause to categorise the incentive made
FROM
employee e INNER JOIN incentive_details i
ON e.pos_id = i.p_id
ORDER BY
e.employee_id -- sorting the orders
There are research institutes with Researchers who have written papers under the guidance of their mentors. You are given a database named ResearchInstitute for one such Institution. Task: Write an SQL Query to find subjects that contain the alphabet "b" and have papers written under the guidance of more fenale mentors.
SELECT
p_subject
FROM
(SELECT *, MAX(ct) OVER (PARTITION BY m_gender,P_subject) mc
FROM
(SELECT
m_gender, p_subject, COUNT(*) AS ct
FROM
mentors me INNER JOIN research_mentor rm -- Inner join of tables
ON me.m_id = rm.m_id
INNER JOIN researchers r
ON r.r_id = rm.r_id
INNER JOIN research_paper rp
ON rp.r_id = r.r_id
INNER JOIN papers p
ON p.p_id = rp.p_id
WHERE
p_subject LIKE '%b%' -- subject with b letter
GROUP BY m_gender, p_subject) a
) b
WHERE
m_gender = 'F' -- filtering gender
AND
ct = mc -- filtering the max paper written under female mentor
SQL, ETL, Python, Power BI...
