Untitled
unknown
plain_text
5 months ago
10 kB
3
Indexable
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;
Editor is loading...
Leave a Comment