Untitled

1)  employee manger salary:


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 E.EMPID;

2) (MOST ACTIVE USER ON SOCIAL MEDIA )Select top 4 users with the highest number of posts

SELECT u.userid AS UserId, u.username AS UserName, p.postid AS topPost
FROM users u
JOIN posts p ON u.userid = p.userid
ORDER BY p.no_of_post DESC
LIMIT 4;


3 ) EMPLOYEES WITH highest salary  ( Write Query to Retrieve the Required Data)

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;

4) ORDER PLACED BY CUSTOMER 
Step 3: Query to Find Customers with More Than 2 Orders and Total Amount > 500.

SELECT A.Customer_ID, B.Total_Orders
FROM (
  SELECT Customer_ID, COUNT(Order_ID) AS Total_Orders
  FROM Orders
  WHERE Total_Amount > 500
  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  
WHERE A.Total_Orders >= 2
ORDER BY A.Customer_ID;


5) Course Fee :

-- Query to Display the Required Information
SELECT 
  E.StudentID AS StudentID,
  E.Name AS Name,
  E.DateOfEnroll AS DateOfEnrollment,
  F.FeesPaid AS FeesPaid,
  F.FeesDue AS FeesDue,
  F.PaymentDate AS PaymentDate
FROM 
  TBL_Students_Enroll E
LEFT JOIN 
  TBL_Students_Fees F
ON 
  E.StudentID = F.StudentID
WHERE 
  E.Course = 'Python'
ORDER BY 
  E.StudentID;

6.) Printing the number of employee living a particular address

select * from EMP;    
SELECT Emp_Add, COUNT(EmpID) AS No_of_Employees
FROM EMP
GROUP BY Emp_Add
ORDER BY Emp_Add;

7.) ye hai salaryDB ka maximum salary of backend and ui

SELECT Dept_Name, MAX(Salary) AS Max_Sal
FROM Department
GROUP BY Dept_Name
ORDER BY Max_Sal DESC;

8) totalExpenditure

SELECT SUM(salary) AS TotalExpenditure
FROM EMP;

9)   subject wala hai biology and math out put.
SELECT Class
FROM Courses
GROUP BY Class
HAVING COUNT(Student) >= 3
ORDER BY Class;

10) database name EMPDb this is generated email wala hai
use EmpDb;

SELECT 
  student_id,
  student_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;
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


1) increasing order of 'max_price' in product db 
USE ProductDb;

-- Query to find the most expensive products in each category
SELECT 
  category,
  product_name AS most_expensive_product,
  price AS max_price
FROM 
  products p1
WHERE 
  price = (
      SELECT MAX(price) 
      FROM products p2 
      WHERE p1.category = p2.category
  )
ORDER BY 
  max_price ASC;

 


2) “MANAGERSALARY
-- Switch to the EmployeeData database
USE EmployeeData;

-- Query to find employees who earn more than their managers
SELECT 
  e.EMPID,
  e.EMPNAME,
  e.SALARY AS EMPSALARY,
  m.SALARY AS MANAGERSALARY
FROM 
  tblemployee e
JOIN 
  tblemployee m
ON 
  e.MANAGERID = m.EMPID
WHERE 
  e.SALARY > m.SALARY
ORDER BY 
  e.EMPID ASC;

 


3) Grade 
-- Switch to the GradeDb database
USE GradeDb;

-- Query to categorize students based on their grades
SELECT 
  student_id,
  first_name AS student_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;
 

 


4)  find distinct department IDs based on the salary conditions


-- Switch to the EmployeeDB database
USE EmployeeDB;

-- Query to find distinct department IDs based on the salary conditions
SELECT DISTINCT department_id
FROM Employees
WHERE salary < 50000
 OR salary >= 70000
ORDER BY department_id ASC;

1) ui backend wala hai ye use salary db

SELECT Dept_Name, MAX(Salary) AS Max_Sal
FROM Department
GROUP BY Dept_Name
ORDER BY Max_Sal DESC;

 

2) emp db wala question employee table wala high medium low
-- Switch to the EmpDb database
USE EmpDb;

-- Query to categorize salaries and display the required columns
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 
  employee
ORDER BY 
  employee_id ASC;

 


3) Daily revenue wala
USE EmployeeDB;

SELECT 
  RecordDate,
  Revenue AS CurrentDayRevenue,
  LAG(Revenue) OVER (ORDER BY RecordDate) AS PreviousDayRevenue,
  Revenue - LAG(Revenue) OVER (ORDER BY RecordDate) AS Difference
FROM 
  DailyRevenue;

 

4) Schhol databse

USE SchoolDb;

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;

 


5) social media top post


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;


6) concat comma  wala with job id


use EmployeeDB;

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


7)  500 dollar  jisme btana hai total order


use Customerdb;

select customer_id, count(*) total_orders
from orders
group by customer_id
having sum(total_amount) > 500 and total_orders > 2
order by customer_id;

8) employees living in a particular address

use EMPLOYEEDATA;

select Emp_Add, count(*) as No_of_Employees
from EMP
group by Emp_Add
order by Emp_Add;


9) 30,000 dollar where employee name starts with s
use EMPLOYEEDATA;

select Emp_Name, Salary
from emp
where salary > 30000 or emp_name like 'S%'
order by Salary;

10) total expenditure wala

use EMPLOYEEDATA;

select sum(salary) as TotalExpenditure from EMP;


11) generated email @ symbol domain example

SELECT 
  employee_id,
  first_name,
  last_name,
  email,
  CONCAT(LOWER(SUBSTRING(first_name, 1, 3)), LOWER(SUBSTRING(last_name, 1, 4)), '@example.com') AS generated_email
FROM 
  employees
ORDER BY 
  employee_id ASC;


12) top 3 highest salary of employees


SELECT employee_id, employee_name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 3;

13) find the most expensive product in each category


use ProductDb;

SELECT 
  category, 
  (SELECT product_name 
   FROM products AS p2 
   WHERE p1.category = p2.category 
   ORDER BY price DESC LIMIT 1) AS most_expensive_product,
  MAX(price) AS max_price
FROM products AS p1
GROUP BY category
ORDER BY max_price ASC;


14) class which has 3 or more students assigned to it

use StudentClassDB;

SELECT Class
FROM Courses
GROUP BY Class
HAVING COUNT(Student) >= 3
ORDER BY COUNT(Student) DESC;


15 ) travel agency location hotel

use EmployeeDB;

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

 


16) db institute and tvl tvl student fee
-- Query to Display the Required Information
SELECT 
  E.StudentID AS StudentID,
  E.Name AS Name,
  E.DateOfEnroll AS DateOfEnrollment,
  F.FeesPaid AS FeesPaid,
  F.FeesDue AS FeesDue,
  F.PaymentDate AS PaymentDate
FROM 
  TBL_Students_Enroll E
LEFT JOIN 
  TBL_Students_Fees F
ON 
  E.StudentID = F.StudentID
WHERE 
  E.Course = 'Python'
ORDER BY 
  E.StudentID;
 


17 ) product and catogory


use EmployeeDB;

SELECT p.product_name, c.category_name
FROM Products p
JOIN Categories c ON p.category_id = c.category_id
ORDER BY c.category_name ASC;


18) python wala student fee
-- Query to Display the Required Information
SELECT 
  E.StudentID AS StudentID,
  E.Name AS Name,
  E.DateOfEnroll AS DateOfEnrollment,
  F.FeesPaid AS FeesPaid,
  F.FeesDue AS FeesDue,
  F.PaymentDate AS PaymentDate
FROM 
  TBL_Students_Enroll E
LEFT JOIN 
  TBL_Students_Fees F
ON 
  E.StudentID = F.StudentID
WHERE 
  E.Course = 'Python'
ORDER BY 
  E.StudentID;

 

19) Math Biology 
SELECT Class
FROM Courses
GROUP BY Class
HAVING COUNT(Student) >= 3
ORDER BY Class;


20) hotel ,location wala output paris new York Tokyo

2 • You work for a travel agency that offers vacation packages in different locations. The company has two tables: "Locations" and "Hotels".

select l.location_name, h.hotel_name from Hotels h left join Locations l on h. location_id= l.location_id order by location_name asc;

 


21) distinct 50000 70000

USE EmployeeDB;

SELECT DISTINCT department_id
FROM Employees
WHERE salary < 50000 OR salary >= 70000
ORDER BY department_id ASC;

22) er diagram wala


use DoSelect;

SELECT FirstName, LastName, Title
FROM Employee
WHERE ReportsTo IS NULL;

 

23) sum of salary


use EMPLOYEEDATA;

/*
select E.Emp_NO, E.Emp_Name, E.Emp_Add, E.Emp_Phone, E.Dept_No, E.Dept_Name, 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;
*/

select sum(Salary) from EMP;

 

 


24) name start wih s salary greater than 30000


use EMPLOYEEDATA;

/*
select E.Emp_Name, E.Salary AS Salary, M.Salary AS MANAGERSALARY
from EMP E join EMP M on E.MANAGERID = M.EMPID AND E.Salary > M.Salary
ORDER BY EMPID AND Emp_Name like '%S';
*/

select Emp_Name, Salary from EMP where Emp_Name like 'S%' AND Salary >= 30000;


25) Retrieve the list of all customer
SELECT 
  c.customer_id, 
  c.customer_name, 
  COUNT(o.order_id) AS total_orders
FROM 
  customers 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;
Or

6SELECT 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
15 ONA.CUSTOMER_ID=B.CUSTOMER_ID AND A.TOTAL_ORDERS>=2
ORDER BY A.CUSTOMER_ID;