SQL ASSIGNMENT – 1
Roll No: 23PA1A4244
Query 1
SELECT e.emp_name, d.dept_name, [Link]
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
Query 2
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;
Query 3
SELECT e.emp_name AS employee_name,
m.emp_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
Query 4
SELECT d.dept_id, d.dept_name, SUM([Link]) AS sum_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name;
Query 5
SELECT d.dept_id, d.dept_name, AVG([Link]) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY avg_salary DESC;
Query 6
SELECT d.dept_id, d.dept_name, AVG([Link]) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY avg_salary DESC
LIMIT 1;
Query 7
SELECT e.emp_name, [Link]
FROM employees e
WHERE [Link] > (SELECT AVG([Link]) FROM employees e2);
Query 8
SELECT e.emp_name, [Link]
FROM employees e
WHERE [Link] > (
SELECT AVG([Link])
FROM employees e2
WHERE e2.dept_id = e.dept_id
);
Query 9
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Query 10
SELECT e.emp_name, e.dept_id, [Link]
FROM employees e
WHERE [Link] = (
SELECT MAX([Link])
FROM employees e2
WHERE e2.dept_id = e.dept_id
);
Query 11
SELECT e.emp_id, e.emp_name, e.dept_id, [Link],
RANK() OVER (PARTITION BY e.dept_id ORDER BY [Link] DESC) AS salary_rank
FROM employees e;
Query 12
SELECT *
FROM (
SELECT e.emp_id, e.emp_name, e.dept_id, [Link],
DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY [Link] DESC) AS salary_rank
FROM employees e
)
WHERE salary_rank <= 3;
Query 13
SELECT e.emp_id, e.emp_name, e.hire_date, [Link],
SUM([Link]) OVER (ORDER BY e.hire_date) AS running_total_salary
FROM employees e
ORDER BY e.hire_date;
Query 14
SELECT e.emp_id, e.emp_name, [Link],
LAG([Link]) OVER (ORDER BY e.hire_date) AS previous_salary
FROM employees e
ORDER BY e.hire_date;
Query 15
SELECT c.customer_id, c.customer_name, [Link]
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
Query 16
SELECT customer_id, order_id, order_date, amount
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn
FROM orders o
)t
WHERE rn = 1;
Query 17
SELECT c.customer_name, SUM([Link]) AS total_sales
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;
Query 18
SELECT c.customer_name,
SUM([Link]) AS customer_sales,
ROUND(SUM([Link]) * 100.0 / (SELECT SUM(amount) FROM orders), 2) AS
contribution_percentage
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;
Query 19
SELECT c.customer_name, COUNT(*) AS duplicate_count
FROM customers c
GROUP BY c.customer_name
HAVING COUNT(*) > 1;
Query 20
SELECT e.emp_name AS Employee_Name,
[Link] AS Salary,
CASE
WHEN [Link] < 80000 THEN 'Low'
WHEN [Link] BETWEEN 80000 AND 120000 THEN 'Medium'
ELSE 'High'
END AS Salary_Band
FROM employees e;
Query 21
SELECT e1.emp_name, [Link]
FROM employees e1
JOIN employees e2
ON [Link] = [Link]
AND e1.emp_id <> e2.emp_id
ORDER BY [Link];
Query 22
SELECT o.customer_id, COUNT(*) AS order_count
FROM orders o
GROUP BY o.customer_id
HAVING COUNT(*) > 3;
Query 23
SELECT MONTH(order_date) AS order_month,
SUM(amount) AS total_sales
FROM orders
GROUP BY MONTH(order_date)
ORDER BY total_sales DESC
LIMIT 1;
Query 24
SELECT o.customer_id, o.order_id, o.order_date,
DATEDIFF(
o.order_date,
LAG(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date)
) AS days_between_orders
FROM orders o;
Query 25
SELECT d.dept_id, d.dept_name
FROM departments d
WHERE d.dept_id NOT IN (
SELECT DISTINCT e.dept_id
FROM employees e
WHERE e.hire_date >= DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
);