F4 Code

 Q1: MySQL : Employee Data Database Name to be used EmployeeData

Problem Statement

Write a standard SQL query that will return the value of ‘EMPID’ and “EMPNAME” for those employees who earn more than their managers. Additionally, the query will return the salary of an employee as column name “EMPSALARY” and the salary of a manager as column name “MANAGERSALARY”.
The rows should be returned in the increasing order of EMPID.

 Column Name: EMPID, EMPNAME, EMPSALARY, MANAGERSALARY

Ans: 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; 



Q2. MySQL : Employee Data Type of Database MySQL Database
Name to be used SocialData

Problem Statement

Write a SQL query to find the top 4 most active users on a social media platform, where activity is defined as the maximum number of posts created.
The query should include a column called the user_id as userId, the username as userName, and no_of_posts as topPost that indicates the user who created the maximum number of posts. Return the output result based on topPost in descending order. 

Table Description: Users

Answer: 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; 



Q3. MySQL : Employee with Highest Salary

 Type of Database MySQL

 Database name to be used CompanyDb

 Problem Statement Write a SQL query to retrieve a list of department_id, department_name and the first_name & the last_name of the employee with their salary in each department. If a department has no employee or salary information, it should still appear in the result with NULL values. The rows should be returned in the increasing order of “department_id”.

 Column Name : department_id, department_name, first_name, last_name, salary

 Table Description departments

 Answer: 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

Q4. MySQL : Order placed by Customer

 Database : Customerdb

 Problem Statement : Write a SQL query to find the customer who has placed more than two orders with a total amount exceeding $500. Display the customer_id and the total number of orders placed by that customer a total_orders. The rows should be returned in the increasing order of “customer_id”
 
Column Name: customer_id, total_orders
 
Table Description : Orders

 Answer: 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;  



Q5. MySQL : Course Fees

ANS: 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.COUURSENM="PYTHON") AND COURSENM="PYTHON") E LEFT JOIN TBL_STUDENTS_FEES F ON E.STUDENTID=F.STUDENTID ORDER BY E.STUDENTNM  

SQL Coding Examples

1. Employee Salary Comparison:

   sql
   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 EMPID;
   

2. Order Placed by Customer:

   sql
   SELECT customer_id, COUNT(*) AS total_orders
   FROM orders
   GROUP BY customer_id
   HAVING total_orders > 2 AND SUM(amount) > 500
   ORDER BY customer_id ASC;
   

3. Course Fees:

   sql
   SELECT studentID, studentNm AS Name, DtEnroll AS DateOfEnrollment, FeesPaid, FeesDue, PaymentDate
   FROM TBL_Students_Enroll AS se
   JOIN TBL_Students_Fees AS sf ON se.studentID = sf.studentID
   WHERE courseNm = 'Python' AND (FeesPaid = 0 OR FeesPaid IS NULL)
   ORDER BY StudentNm;
   

4. Most Active Users on Social Media:

   sql
   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;
   

Let me know