--24 page pdf(all test cases passed)

Top 4 most active users on social media USE

SocialData;

select U.user_id as userId, U.username as userName, P.no_of_post as topPost from Users U join
Posts P ON U.user_id = P.user_id order by P.no_of_post DESC limit 4;

--Employee Data more than their managers

USE EmployeeData;

SELECT E.EMPID,E.EMPNAME,E.SALARY AS EMPSALARY, M.SALARY AS MANAGERSALARY

FROM TBLEMPLOYEE E

JOIN TBLEMPLOYEE M ON

E.MANAGERID = M.EMPID

AND E.SALARY > M.SALARY ORDER

BY EMPID;

-- Employee with Highest Salary (CompanyDB)

use CompanyDb; SELECT
D.DEPARTMENT_ID,

D.DEPARTMENT_NAME,A.FIRST_NAME,A.LAST_NAME,A.SALARY

FROM DEPARTMENTS D LEFT JOIN

(SELECT E.FIRST_NAME,E.LAST_NAME,E.DEPARTMENT_ID,S.SALARY

FROM EMPLOYEES E JOIN SALARIES S ON E.EMPLOYEE_ID = S.EMPLOYEE_ID) A

ON D.DEPARTMENT_ID = A.DEPARTMENT_ID ORDER BY

D.DEPARTMENT_ID;

--Course Fees
use DB_Institute;
SELECT E.STUDENTID AS StudentID, E.STUDENTNM AS Name, E.DTENROLL AS

DateOfEnrollment, F.FEESPAID AS FeesPaid, F.FEESDUE AS FeesDue,

F.PAYMENTDATE AS PaymentDate FROM

(SELECT * FROM TBL_STUDENTS_ENROLL WHERE STUDENTID NOT IN

(SELECT E.STUDENTID FROM

TBL_STUDENTS_ENROLL E JOIN

TBL_STUDENTS_FEES F ON

E.STUDENTID=F.STUDENTID

AND E.COURSENM='PYTHON') AND COURSENM='PYTHON') E

LEFT JOIN TBL_STUDENTS_FEES F

ON E.STUDENTID = F.STUDENTID ORDER BY

E.STUDENTNM;

--order placed by the customer

use Customerdb;

SELECT A.CUSTOMER_ID,B.TOTAL_ORDERS FROM

(SELECT CUSTOMER_ID,COUNT(ORDER_ID) AS TOTAL_ORDERS

FROM ORDERS WHERE TOTAL_AMOUNT > 500.00

GROUP BY CUSTOMER_ID) A JOIN

SELECT CUSTOMER_ID,COUNT(ORDER_ID) AS TOTAL_ORDERS

FROM ORDERS

GROUP BY CUSTOMER_ID

)B

ON A.CUSTOMER_ID = B.CUSTOMER_ID AND A.TOTAL_ORDERS >= 2 ORDER BY

A.CUSTOMER_ID;
____________________________________________________________________
________________ ________________

--55 page pdf(all test cases passed)

--Environment Specification and Instruction(e-Commerce) use

EmployeeDB;

select c.category_name, p.product_name from Products p join

Categories c on p.category_id = c.category_id order by
c.category_name asc;

-- Environment Specification and Instruction(travel agency) use

EmployeeDB; select l.location_name, h.hotel_name from locations
as l left join hotels as h on l.location_id = h.location_id order by
location_name;

-- Environment Specification and Instruction(Top 3 employees with the highest Salary) use
EmployeeDB;
select * from Employees order by salary desc limit 3;

-- Environment Specification and Instruction(3rd Highest Salary from the table) use
DB_Company;
select salary as Salary from TBL_EMPLOYEE order by salary desc limit 1 offset 2;

-- Environment Specification and Instruction(retrive a list) use

CustomerDB;

SELECT c.customer_id,c.customer_name,COALESCE(COUNT(o.order_id),0) AS total_orders

FROM customer c LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id,c.customer_name ORDER BY

c.customer_id;

-------------------------------------------------------------------------------------------------
--problem statement --- Write the query that combines the data from the students and teacher's
table (SchoolDB Database Name) SELECT first_name, last_name, 'Student' AS role
FROM students UNION ALL

SELECT first_name, last_name, 'Teacher' AS role

FROM teachers

ORDER BY first_name;

--problem statement --- Write a sql query to fetch class which has 3 or more students assigned to
it.
SELECT Class FROM Courses

GROUP BY Class HAVING COUNT(Class) >= 3 ORDER BY

Class;

--problem statement --- Louis is working in the HR Department He wants to prepare

Data (DB_Company Databse Name)
SELECT d.Department, COUNT(e.EmpID) AS No_of_Employees

FROM TBL_Employee e

JOIN TBL_Department d ON e.DepartmentID = d.ID

GROUP BY d.Department

ORDER BY d.Department;

-- (Sample output is Biology and Maths) Retrive the most repeated Class from the student table
SELECT Class, COUNT(*) AS NumberOfStudents FROM Student

GROUP BY Class HAVING COUNT(*) > 1 ORDER BY

Class asc;

--Sample output(emily davis , jane smith , john doe , mike johnson , sarah williams)
SELECT e.name AS employee_name FROM Employee e

JOIN Departments d ON e.department_id = d.id

WHERE d.name IN ('Sales', 'Marketing') ORDER BY

e.name;

--problem statement --- Write a sql query to execute the username (part before @) and domain
(part after the '@' symbol)
SELECT

SUBSTRING(email_address, 1, INSTR(email_address, '@') - 1) AS username,

SUBSTRING(email_address, INSTR(email_address, '@') + 1) AS domain

FROM Email_Data

ORDER BY username asc;

--problem statement--- Write an SQL query to find the most expensive products in each product
category
SELECT category,product_name AS most_expensive_product,price AS max_price FROM products
WHERE(category,price) IN(SELECT category,MAX(price)

FROM products GROUP BY category) ORDER BY

max_price;

__________________________________________________________________________________
__ ___________

--38 page pdf(pawan pdf)

--problem statement --- write a query to categorize employees into three groups based on their
salary use EmpDb;
SELECT employee_id,first_name,last_name,salary,

CASE
WHEN salary >= 50000 THEN 'High Salary'

WHEN salary BETWEEN 30000 AND 49999 THEN 'Medium Salary'

ELSE 'Low Salary'

END AS salary_category FROM employees ORDER BY

employee_id;

--problem statement --- Construct a Query to display the Publication, Language and count of
books(CountofBooks).... use DB_Publishers;
SELECT Publication,Language,COUNT(*) AS CountofBooks FROM TBL_Books

GROUP BY Publication,Language ORDER BY

Publication ASC;

__________________________________________________________________________________
__ __________________

--21page pdf(mahesh pdf)

--problem tatement --- Find the total salary expenditure for the company(test case pass)
select sum(salary) as TotalExpenditure from EMP;

--problem statement --- write a sql query to display the top 10 genre names as "Name"

SELECT g.Name AS Name FROM Track t

JOIN Genre g ON t.GenreID = g.GenreId

GROUP BY g.Name

ORDER BY AVG(t.UnitPrice) DESC, g.Name LIMIT 10;

--problem statement--- the hr department of a company wants to analyze increments in salary

SELECT

e1.EmployeeID,

e1.Year AS Year,

e1.CurrentSalary,

COALESCE(e2.CurrentSalary, 'NULL') AS PreviousYearSalary,

CASE

WHEN e2.CurrentSalary IS NULL THEN NULL

ELSE ROUND(((e1.CurrentSalary - e2.CurrentSalary) / e2.CurrentSalary) * 100, 6)

END AS PercentageIncrement

FROM

EmployeeSalaries e1

LEFT JOIN

EmployeeSalaries e2 ON

e1.EmployeeID = e2.EmployeeID

AND e1.Year = e2.Year + 1 ORDER

BY

e1.EmployeeID,

e1.Year;

--ER Diagram Question

SELECT firstName as FirstName, lastName as LastName, title as Title from Employee where reportsto
is null;
__________________________________________________________________________________
__ _____

photo questions(test cases passed)

--Write a sql query to fetch class which has 3 or more students assigned to it.
SELECT Class FROM Courses

GROUP BY Class HAVING COUNT(Class) >= 3 ORDER BY

Class;

--problem statement---find the total number of employees living in each particular address...
(EMPLOYEEDATA table name)
SELECT EMP_ADD, COUNT(*) AS NO_OF_EMPLOYEES FROM EMP

GROUP BY EMP_ADD

ORDER BY EMP_ADD ASC;

--problem statement--- A retail store wants to track its daily revenue and compare it with the
previous day's revenue.
SELECT RecordDate,

Revenue AS CurrentDayRevenue,

LAG(Revenue) OVER (ORDER BY RecordDate) AS PreviousDayRevenue,

Revenue - LAG(Revenue) OVER (ORDER BY RecordDate) AS Difference

FROM DailyRevenue

ORDER BY RecordDate ASC;

--problem statement -- write a sql query that selects { GradeDb (Excellent,

Good,Average,NeedsImprovement) question}
SELECT STUDENT_ID,FIRST_NAME,LAST_NAME,GRADE,
CASE

WHEN GRADE >= 90 THEN 'Excellent'

WHEN GRADE >= 80 THEN 'Good'

WHEN GRADE >= 70 THEN 'Average'

ELSE 'Needs Improvement'

END as grade_category
FROM STUDENTS order by
STUDENT_ID asc;

--problem statement--- write an Sql query to find the highest salary of each department
SELECT Dept_Name, MAX(Salary) AS Max_Sal

FROM Department

GROUP BY Dept_Name

ORDER BY Max_Sal DESC;

__________________________________________________________________________________
__ _______________

--- some extra but from pdf

--You have been given an Employees table that contains info about employees working in a
company. select distinct department_id from employees where salary < 50000 union
select distinct department_id from employees where salary >= 70000 order by
department_id asc;

or(option code Both Code passes test cases )

select distinct department_id from employees where salary < 50000 or salary >= 70000 order by

department_id asc;

--Write a SQL query to select the required data from the employees table and include in a
column called generated_email.
select employee_id, first_name, last_name, email,
concat(left(first_name,3),left(last_name,4),'@example.com') as generated_email from
employees order by employee_id asc;
--Write a SQL query to display the names of employee concatinated with their job ids separated
by comma and space....

select concat(employee_name,", ",job_id) emp_name_job from Employee_Data order by

emp_name_job;

--Retrive the emp_name and salary of employees who have a salary greater than 30000 or whose
employee name starts with 'S'
select Emp_name, salary from EMP where salary > 30000 or Emp_name LIKE 'S%';

Q1

-> database name : DoSelect

Artist album track mediatype

use DoSelect;

select FirstName, LastName, Title

FROM Employee

WHERE ReportsTo IS NULL; *

OR

USE DoSelect;

SELECT firstname AS FirstName, lastname AS LastName, title AS Title

FROM Employee

WHERE reportsto IS NULL;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q2
-> database name : EMPLOYEEDATA

problem statement : find the total salary expenditure for the company

use EMPLOYEEDATA;

SELECT

SUM(salary) AS TotalExpenditure

FROM

EMP;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q3

-> database name: EmpDb

write a sql query to categorize employee into three groups based on their salary , high
salary, medium salary,low salary

use EmpDb;

SELECT

employee_id, first_name, last_name, salary,

CASE

WHEN salary >= 50000 THEN 'High Salary'

WHEN salary BETWEEN 30000 AND 49999 THEN 'Medium Salary'

ELSE 'Low Salary'

END AS salary_category

FROM

employees

ORDER BY

employee_id;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q4
-> database name: SocialDataUU

problem statement : top 4 most active users on a social media

use SocialData;

SELECT U.USER_ID AS userId, U.USERNAME as userName , P.NO_OF_POST AS

topPost

FROM USERS U JOIN POSTS P ON U.USER_ID = P.USER_ID

ORDER BY P.NO_OF_POST DESC LIMIT 4;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q5

-> database name : EMPLOYEEDATA

Problem statement : Retrive emp_name and salary of employees who have a salary greater than
$30000 or whose employee name starts with 'S'

* use EMPLOYEEDATA;

SELECT Emp_Name, Salary

FROM EMP

where Salary > 30000 or Emp_Name LIKE 'S%'

ORDER BY Salary ASC;

OR

select Emp_name , Salary from EMP where Salary > 30000 or Emp_name like "S%" order by
Salary;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q6

-> Database name : DB_Institute

Problem Statement : construct a query that display StudentID,Name,

*

use DB_Institute;

SELECT E.STUDENTID AS StudentID, E.STUDENTNM AS Name , E.DTENROLL AS

DateOfEnrollment , F.FEESPAID AS FeesPaid, F.FEESDUE AS FeesDue , F.

PAYMENTDATE as PaymentDate FROM

(SELECT * FROM TBL_STUDENTS_ENROLL where STUDENTID NOT IN

(SELECT E.STUDENTID

FROM TBL_STUDENTS_ENROLL E JOIN

TBL_STUDENTS_FEES F ON

E.STUDENTID = F.STUDENTID

AND E.COURSENM = 'PYTHON') AND COURSENM = 'PYTHON') E

LEFT JOIN

TBL_STUDENTS_FEES F

ON E.STUDENTID = F.STUDENTID

ORDER BY E.STUDENTNM

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q7

->Database Name : CustomerDb

problem statement : write a sql query to retrive a list of all customers and the total number
orders they have placed

output : customer_id, customer_name, total_orders

use CustomerDb;

select c.customer_id, c.customer_name,count (o.order_id) as total_orders

from customers as c

left join orders as o

on c.customer_id = o.customer_id

group by c.customer_id

order by c.customer_id;
--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q8

-> Database name : EmployeeDB

problem statement : The HR Department of a company wants to analyze the increments in

salaries that employees receive each year

use EmployeeDB;

select e1.EmployeeID , e1.Year, e1.Salary as CurrentSalary , e2.Salary as

PreviousYearSalary, ((e1.Salary - e2.Salary) * 100/e2.Salary) as

PercentageIncrement

from EmployeeSalaries as e1

left join EmployeeSalaries as e2

on e1.EmployeeID = e2.EmployeeID and e1.Year-1 = e2.Year

order by e1.EmployeeID, e1.Year*

OR

SELECT e1.EmployeeID, e1.Year,

e1.Salary AS CurrentSalary,

e2.Salary AS PreviousYearSalary,

((e1.Salary - e2.Salary)*100/e2.Salary) AS PercentageIncrement

FROM Employeesalaries as e1

LEFT JOIN EmployeeSalaries as e2

ON e1.EmployeeID=e2.EmployeeID AND e1.Year-1=e2.Year

ORDER BY e1.EmployeeID, e1.Year;

OR

SELECT d.department_id,d.department_name,e.first_name,e.last_name,s.salary FROM

departments d

LEFT JOIN employees e ON d.department_id=e.department_id

LEFT JOIN salaries s ON e.employee_id=s.employee_id

ORDER BY d.department_id;

OR
SELECT e1.EmployeeID, e1.Year,

e1.Salary AS CurrentSalary,

e2.Salary AS PreviousYearSalary,

((e1.Salary - e2.Salary)/e2.Salary*100) AS PercentageIncrement

FROM Employeesalaries e1

JOIN EmployeeSalaries e2

ON e1.EmployeeID=e2.EmployeeID AND e1.Year=e2.Year + 1

ORDER BY e1.EmployeeID, e1.Year;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q9

-> Database Name : EmployeeDB

problem statement : You work for an ecommerce company that sells products in multiple
categorize the company has two tables categorize and products

output : category_name, product_name

use EmployeeDB;

select c.category_name,p.product_name from Products as p LEFT JOIN Categories as c

on p.category_id = c.category_id where c.category_id is not null order by

category_name ASC;

OR

SELECT

Categories.category_name AS category_name,

Products.product_name AS product_name

FROM

Products

INNER JOIN
Categories

ON

Products.category_id = Categories.category_id

ORDER BY

Categories.category_name ASC;

OR

SELECT c.category_name, p.product_name

FROM Products as p

LEFT JOIN Categories as c

ON p.category_id=c.category_id

WHERE c.category_id is not null

ORDER BY category_name ASC;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q10

-> Database Name : Employee Database

problem statement : find the total no of employees living in each particular

address . Display the result in the increasing order of Emp_Add

use EMPLOYEEDATA;

select Emp_Add,count(Emp_NO) as No_of_Employees from EMP group by Emp_ADD order by

Emp_Add;*

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q11

-> Database Name : GradeDb

problem statement :

excellent for grade

good for grade 80 and 89

average 70 and 79

use GradeDb;

SELECT student_id,first_name,last_name,grade,

CASE WHEN grade >= 90 THEN 'Excellent'

WHEN grade>=80 and grade<=89 THEN 'Good'

WHEN grade>=70 and grade<=79 THEN 'Average'

ELSE 'Needs Improvement';

END AS grade_category

from students

order by student_id;

OR

select student_id, first_name, last_name, grade,

case

when grade >= 90 then 'Excellent'

when grade between 80 and 89 then 'Good'

when grade between 70 and 79 then 'Average'

else 'Needs Improvement'

end as grade_category

from students

order by student_id asc;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

Q12. To display the top 10 genre names with the highest average unit price from the Track table

-> use DoSelect;

select g.name as Name ,avg(t.UnitPrice) as AvgUnitPrice

from Genre as g

join Track as t
on g.genreid = t.genreid

group by g.name

order by avg(t.UnitPrice) DESC, g.name ASC

limit 10;

OR

SELECT g.genre_name AS "Name", AVG(t.unit_price) AS avg_unit_price

FROM Track t

JOIN Genre g ON t.genre_id = g.genre_id

GROUP BY g.genre_name

ORDER BY avg_unit_price DESC, g.genre_name ASC

LIMIT 10;

--------------------------------------------------------------------

13.Employee db keywords (username domain)

SELECT

SUBSTRING_INDEX(email_address, '@', 1) AS username,

SUBSTRING_INDEX(email_address, '@', -1) AS domain

FROM Email_Data

ORDER BY username ASC;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

14. Hotel name and location

SELECT L.location_name, H.hotel_name

FROM Locations L

LEFT JOIN Hotels H ON L.location_id = H.location_id

ORDER BY L.location_name ASC, H.hotel_name ASC;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
15. EmployeeData Job id

SELECT CONCAT(employee_name, ', ', job_id) AS emp_name_job

FROM EmployeeData

ORDER BY emp_name_job ASC;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

16. Employee data( total expenditure)

SELECT SUM(CAST(Salary AS DECIMAL)) AS Total_Expenditure

FROM Emp;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

17. 4 topmost active users

SELECT U.USER_ID AS userId, U.USERNAME AS username, P.NO_OF_POST AS topPost

FROM USERS U

JOIN POSTS P ON U.USER_ID = P.USER_ID

ORDER BY P.NO_OF_POST DESC

LIMIT 4;

OR

SELECT U.USER_ID AS userId, U.USERNAME as userName, P.NO_OF_POST as topPost

FROM USERS U JOIN POSTS P ON U.USER_ID = P.USER_ID

ORDER BY P.NO_OF_POST DESC LIMIT 4;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
18. Company Db =to retrieve a list of department_id, dept._name, and the first_name

USE CompanyDb;

SELECT d.department_id, d.department_name, e.first_name, e.last_name, s.salary

FROM departments AS d

LEFT JOIN employees AS e ON d.department_id = e.department_id

LEFT JOIN salaries AS s ON e.employee_id = s.employee_id

ORDER BY d.department_id ASC;

--------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------

19. TBL_employee

USE DB_Company;

SELECT DISTINCT salary

FROM TBL_Employee

ORDER BY salary DESC

LIMIT 1 OFFSET 2;