SQL Questions: Product Management System

Problem Scenario:   A Dairy Milk Organization wants to develop a data base Product  Management System (PMS) which maintains about the various products processed  and their stock details along with this it also has to maintain the various departments  and their manager details.

List of Tables: Table #1:  PMS_DEPARTMENT_DETAILS Table #2:  PMS_MANAGER_DETAILS Table #3:  PMS_UNIT_DETAILS Table #4:  PMS_PRODUCT Table #5:  PMS_PRODUCT_UNIT Table #6:  PMS_MANUFACTURING

Simple Questions:
Problem # 1:
List the Managers and their respective Boss Names. In select list we have Manager_ID, Manager_Name, Job, and Boss_Name.
Hint:  PMS_MANAGER_DETAILS alias as Manager
        PMS_MANAGER_DETAILS alias as Boss
          Boss_Name as an alias in the select query.
Solution:

Problem # 2:
Find the 2nd Highest Salary Earner of all the Managers. In select list we have Manager_ID,Manager_Name,Job,Salary,Commission,Department_ID
Solution:

Problem # 3:
Find out the Employees whose name has letter ‘A’ in the 2nd position & are earning more than the salary whose name starts with ‘V’. In select list we have MANAGER_ID, MANAGER_NAME, JOB, and SALARY.
Solution:

Problem # 4:
Add 7.5% of salary as performance bonus for each employee and display the net yearly salary of each employee. (Do not update the database.). In select list we have MANAGER_ID, MANAGER_NAME, JOB, and YEARLY_SALARY.
YEARLY_SALARY as an alias for the expression used in select query.
Solution:
Problem # 5:
Display the Manufactured products details that are belong to ‘GHEE SECTION’. In select list we have MANFATURE_ID, PRODUCT_NAME, UNIT_ID, QUANTITY, PRODUCT_MANFACTURE_DATE, and PRODUCT_EXPIRY_DATE.

Hint:   PMS_MANUFACTURING                     TABLE ALIAS AS                 M
            PMS_PRODUCT                                    TABLE ALIAS AS                  P

Solution:

Problem # 6:
FIND SUM OF QUANTITY WITH RESPECT TO EACH QUANTITY.
Solution:

Problem # 7:
Find the list of products which are available as on ’15-DEC-12’.
Solution:

Problem # 8:
Find the list of products along with their count which are not available as on ’15-DEC-12’. In select query COUNT_PRODUCT as an alias for count field.
Solution:

Problem # 9:
Find the employees with higher salary than the average salary of ‘MANAGER’ and those are all not MANAGER’S. In select list we have Manager_ID, Manager_Name and their Job details.
Solution:

Problem # 10:
Display the manager details who are drawing the salary more than 20000 Rs/-. And we need to display the manager name in proper case and order by department wise. In select list we have Manager_ID, Manager_Name and Department_ID.
Solution:

Average Questions:
Problem # 1:
Write a query to display the  Department_Name, Department_Location  whose number of employees are more than or equal to  4
Solution:

Problem # 2:
Find the Persons with a Job which has the highest average salary.  In select list we have MANAGER_ID, MANAGER_NAME, JOB, and SALARY.
Solution:

Problem # 3:
List the employees who earn more than their own department’s average salary and display them in Department_ID order. In select list we have MANAGER_ID, MANAGER_NAME, JOB, SALARY, and DEPARTMENT_ID.
Solution:

Problem # 4:
Display the Product details that are ends with MILK. In select list we have to display PRODUCT_ID, PRODUCT_NAME, UNIT_ID, and UNIT_WEIGHT.
Hint:  PMS_PRODUCT                  Table Alias as                          P
          PMS_PRODUCT_UNIT       Table Alias as                         U
          PMS_UNIT_DETAILS          Table Alias as                         D
Solution:

Problem # 5:
Display the Product Name’s along with their possible packing details in the order of Weight.  In select list we have to display PRODUCT_NAME, UNIT_NAME, TOTAL_PIECES, and UNIT_WEIGHT.
Hint:  PMS_PRODUCT                  Table Alias as                          P
          PMS_PRODUCT_UNIT       Table Alias as                         U
          PMS_UNIT_DETAILS          Table Alias as                         D
Solution:

Problem # 6:
Display the product_id and sum of quantity whose available status is ‘yes’. And sum of quantity greater than 1500.
Hint:  TOTAL_QUANTITY is an Alias name used in the select query.
Solution:

Problem # 7:
Display Product_ID, Product_Name, Department_ID and the number of varieties of units available as on ’15-dec-12’.
Hint:          NUMBER_VARIETIES                  as an Alias name for Count field in select query.
                   PMS_MANUFACTURING          Table Alias as     M
                   PMS_PRODUCT                        Table Alias as       P
Solution:

Problem # 8:
List the employees earning more than the average salary of employees based out of ONGOLE. In select list we have MANAGER_ID and MANAGER_NAME
Solution:

Problem#9:
List the MANAGER’s who have salary higher than that of the department with highest number of employees. In select list we have MANAGER_ID, MANAGER_NAME, JOB and SALARY.
Solution:

Problem#10:
List the departments which does not have any employees. In select query we have DEPARTMENT_ID,DEPARTMENT_NAME
Solution:

Complex Questions:
Problem # 1:
Display the details of manager who is drawing the 7th highest salary among all the other managers. In select list we have  MANAGER_ID,MANAGER_NAME,JOB,SALARY and DEPARTMENT_ID

Solution:

Problem # 2:
Write a query to find the list of all the sub ordinates whose salary is greater than the respective boss salary. In select list we have  S.MANAGER_ID,S.MANAGER_NAME,S.JOB,S.SALARY,S.DEPARTMENT_ID,B.MANAGER_ID,B.MANAGER_NAME,B.SALARY

Hint: PMS_MANAGER_DETAILS       Table Alias as     S
          PMS_MANAGER_DETAILS      Table Alias as      B

Solution:

Problem # 3:
Find the number of batches manufactured greater than 5 in each month
 Displaying the Month name and Number of batches.
Hint:   MONTH AS AN ALIAS NAME FOR MONTH NAME
            NUMBER_BATCHES  AS AN ALIAS NAME FOR COUNT FIELD IN THE SELECT QUERY
Solution:

Problem # 4:
Display the PRODUCT_ID, PRODUCT_NAME, and DEPARTMENT_ID which is manufactured maximum with respect to sum of quantity.
Solution:

Problem # 5:
Find the maximum quantity manufactured with respect to each product and display the product id, product name and Quantity from each product.
Hint: PMS_MANUFACTURING  Table Alias as M
          PMS_PRODUCT   Table Alias as  PP
Solution:

SHARE