Database Assignment Help | Product Management System | realcode4you

Updated: Dec 4, 2019


Database Assignment Help

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.


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


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.


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.


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


Problem # 6:

FIND SUM OF QUANTITY WITH RESPECT TO EACH QUANTITY.


Problem # 7:

Find the list of products which are available as on ’15-DEC-12’.


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.


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.


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.


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


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.


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.


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


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


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.


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


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


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.


Problem#10:

List the departments which does not have any employees. In select query we have DEPARTMENT_ID,DEPARTMENT_NAME


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


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


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


Problem # 4:

Display the PRODUCT_ID, PRODUCT_NAME, and DEPARTMENT_ID which is manufactured maximum with respect to sum of quantity.


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



Database and tables


create database adarsh;

use adarsh;

CREATE TABLE PMS_DEPARTMENT_DETAILS

(DEPARTMENT_ID int(2)PRIMARY KEY,DEPARTMENT_NAME VARCHAR(30)NOT NULL,

DEPARTMENT_LOCATION VARCHAR(30)NOT NULL,DEPARTMENT_EXTENSION int(3) NOT NULL);


CREATE TABLE PMS_MANAGER_DETAILS

(Manager_ID int(5) PRIMARY KEY,Manager_Name VARCHAR(30) NOT NULL,

Job VARCHAR(30) NOT NULL,Boss_Code int(5),Salary double(7,2) NOT NULL,

Commission int(5),DEPARTMENT_ID int(2));


CREATE TABLE PMS_UNIT_DETAILS

(UNIT_ID VARCHAR(2) PRIMARY KEY,UNIT_NAME VARCHAR(30) NOT NULL,

PIECE_WEIGHT VARCHAR(15) NOT NULL,TOTAL_PIECES int(3) NOT NULL,

UNIT_WEIGHT double(5,2) NOT NULL);


CREATE TABLE PMS_PRODUCT

(PRODUCT_ID VARCHAR(5) PRIMARY KEY,PRODUCT_NAME VARCHAR(30) NOT NULL,

DEPARTMENT_ID int(2));


CREATE TABLE PMS_PRODUCT_UNIT

(PRODUCT_ID VARCHAR(5),UNIT_ID VARCHAR(2));


CREATE TABLE PMS_MANUFACTURING

(MANFATURE_ID VARCHAR(5) PRIMARY KEY,PRODUCT_ID VARCHAR(5) NOT NULL,

UNIT_ID VARCHAR(5) NOT NULL,QUANTITY int(7) NOT NULL,AVAILABILITY VARCHAR(3) NOT NULL,

PRODUCT_MANFACTURE_DATE DATE NOT NULL,PRODUCT_EXPIRY_DATE DATE NOT NULL);


CREATE TABLE PMS_MANUFACTURING

(MANFATURE_ID VARCHAR(5) PRIMARY KEY,PRODUCT_ID VARCHAR(5) NOT NULL,

UNIT_ID VARCHAR(5) NOT NULL,QUANTITY int(7) NOT NULL,AVAILABILITY VARCHAR(3) NOT NULL,

PRODUCT_MANFACTURE_DATE DATE NOT NULL,PRODUCT_EXPIRY_DATE DATE NOT NULL);

ALTER TABLE PMS_MANAGER_DETAILS ADD constraint FK_DEPARTMENT_ID1 foreign key(DEPARTMENT_ID) references PMS_DEPARTMENT_DETAILS(DEPARTMENT_ID);

ALTER TABLE PMS_PRODUCT ADD constraint FK_DEPARTMENT_ID2 foreign key(DEPARTMENT_ID) references PMS_DEPARTMENT_DETAILS(DEPARTMENT_ID);

ALTER TABLE PMS_PRODUCT_UNIT ADD constraint PK_Composite21 primary key(PRODUCT_ID,UNIT_ID);

ALTER TABLE PMS_PRODUCT_UNIT ADD constraint FK_PRODUCT_ID1 foreign key(PRODUCT_ID) references PMS_PRODUCT(PRODUCT_ID);

ALTER TABLE PMS_PRODUCT_UNIT ADD constraint FK_UNIT_ID1 foreign key(UNIT_ID) references PMS_UNIT_DETAILS(UNIT_ID);

ALTER TABLE PMS_MANUFACTURING ADD constraint FK_PRODUCT_ID2 foreign key(PRODUCT_ID) references PMS_PRODUCT(PRODUCT_ID);

ALTER TABLE PMS_MANUFACTURING ADD constraint FK_UNIT_ID2 foreign key(UNIT_ID) references PMS_UNIT_DETAILS(UNIT_ID);

INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(10,'MIS','HYDERABAD_ZONE_1',121);

INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(20,'GHEE SECTION','ONGOLE',122);

INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(30,'PROCESSING SECTION','RAJAMUNDRY',123);

INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(40,'BI_PRODUCTS SECTION','SECUNDERABAD',124);

INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(50,'DISPATCH SECTION','HYDERABAD_ZONE_2',125);

INSERT INTO PMS_DEPARTMENT_DETAILS VALUES(60,'CUSTOMER CARE SECTION','HYDERABAD_ZONE_2',126);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7711,'BLAKE','GENERAL MANAGER',NULL,25000,2500,10);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7722,'LARANCE','DEPUTY GENERAL MANAGER',7711,28000,1500,10);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7733,'GATES','MANAGER',7722,26750,500,20);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7744,'CALRK','MANAGER',7722,22000,1000,30);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7755,'VINCY','MANAGER',7722,17500,0,40);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7766,'GALE','MANAGER',7722,16500,0,50);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7770,'NANCY','ASSISTANT MANAGER',7733,30000,500,20);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7771,'GOUD','ASSISTANT MANAGER',7744,23000,750,30);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7772,'NAIDU','ASSISTANT MANAGER',7755,18500,0,40);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7773,'RAO','ASSISTANT MANAGER',7766,15000,3000,50);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7774,'RAJU','ASSISTANT MANAGER',7722,21050,2000,10);

INSERT INTO PMS_MANAGER_DETAILS VALUES(7775,'REDDY','ASSISTANT MANAGER',7722,28500,0,10);

INSERT INTO PMS_UNIT_DETAILS VALUES('C1','CARTON','235 ML/GMS',20,5);

INSERT INTO PMS_UNIT_DETAILS VALUES('M1','MIN_BOX','25 GMS',20,.5);

INSERT INTO PMS_UNIT_DETAILS VALUES('M2','MAX_BOX','25 GMS',40,1);

INSERT INTO PMS_UNIT_DETAILS VALUES('C2','CAN','19.7 KGS',1,20);

INSERT INTO PMS_UNIT_DETAILS VALUES('T1','TIN','30 GMS',50,1.5);

INSERT INTO PMS_UNIT_DETAILS VALUES('P1','PACK','980 ML',1,1);

INSERT INTO PMS_UNIT_DETAILS VALUES('P2','HALF_PACK','480 ML/GMS',1,0.5);

INSERT INTO PMS_UNIT_DETAILS VALUES('P3','CHOTA_PACK','235 ML/GMS',1,0.25);

INSERT INTO PMS_PRODUCT VALUES('P001','MIXED GHEE',20);

INSERT INTO PMS_PRODUCT VALUES('P002','PANNER',20);

INSERT INTO PMS_PRODUCT VALUES('P003','COOKING BUTTER',20);

INSERT INTO PMS_PRODUCT VALUES('P004','RASAGULLA',40);

INSERT INTO PMS_PRODUCT VALUES('P005','CURD',40);

INSERT INTO PMS_PRODUCT VALUES('P006','DIET MILK',30);

INSERT INTO PMS_PRODUCT VALUES('P007','TONNED MILK',30);

INSERT INTO PMS_PRODUCT VALUES('P008','FAMILY MILK',30);

INSERT INTO PMS_PRODUCT VALUES('P009','STANDERED MILK',30);

INSERT INTO PMS_PRODUCT VALUES('P010','WHOLE MILK',30);

INSERT INTO PMS_PRODUCT VALUES('P011','BUTTER MILK',40);

INSERT INTO PMS_PRODUCT VALUES('P012','DOODH PEDA',40);

INSERT INTO PMS_PRODUCT VALUES('P013','MILK SHAKE',40);

INSERT INTO PMS_PRODUCT_UNIT VALUES('P001','C2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P001','P2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P001','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P001','P1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P002','C1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P002','P1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P002','P2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P002','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P006','P1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P006','P2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P006','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P007','P1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P007','P2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P007','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P008','P1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P008','P2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P008','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P009','P1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P009','P2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P009','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P010','P1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P010','P2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P010','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P003','P2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P003','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P004','T1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P005','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P011','P3');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P012','M1');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P012','M2');

INSERT INTO PMS_PRODUCT_UNIT VALUES('P013','C1');

INSERT INTO PMS_MANUFACTURING VALUES('M001','P001','C2',100,'YES','2012-08-15','2012-12-15');

INSERT INTO PMS_MANUFACTURING VALUES('M002','P001','P2',500,'YES','2012-08-10','2012-12-15');

INSERT INTO PMS_MANUFACTURING VALUES('M003','P001','P3',250,'YES','2012-08-10','2012-12-15');

INSERT INTO PMS_MANUFACTURING VALUES('M004','P001','P1',300,'NO','2012-08-15','2012-12-15');

INSERT INTO PMS_MANUFACTURING VALUES('M005','P002','C1',190,'YES','2012-08-5','2012-08-31');

INSERT INTO PMS_MANUFACTURING VALUES('M006','P002','P1',500,'YES','2012-08-5','2012-08-31');

INSERT INTO PMS_MANUFACTURING VALUES('M007','P002','P2',250,'YES','2012-08-5','2012-08-31');

INSERT INTO PMS_MANUFACTURING VALUES('M008','P002','P3',500,'YES','2012-08-5','2012-08-31');

INSERT INTO PMS_MANUFACTURING VALUES('M009','P006','P1',4500,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M010','P006','P2',7500,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M011','P006','P3',10000,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M012','P007','P1',4000,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M013','P007','P2',3000,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M014','P007','P3',2500,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M015','P008','P1',7000,'NO','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M016','P008','P2',3500,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M017','P008','P3',4500,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M018','P009','P1',1500,'NO','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M019','P009','P2',2500,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M020','P009','P3',1000,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M021','P010','P1',10000,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M022','P010','P2',25000,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M023','P010','P3',12500,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M024','P003','P2',2400,'YES','2012-08-15','2012-10-10');

INSERT INTO PMS_MANUFACTURING VALUES('M025','P003','P3',3210,'NO','2012-08-15','2012-10-10');

INSERT INTO PMS_MANUFACTURING VALUES('M026','P004','T1',750,'YES','2012-08-15','2012-10-10');

INSERT INTO PMS_MANUFACTURING VALUES('M027','P005','P3',10000,'YES','2012-08-15','2012-08-17');

INSERT INTO PMS_MANUFACTURING VALUES('M028','P011','P3',27500,'YES','2012-08-15','2012-08-16');

INSERT INTO PMS_MANUFACTURING VALUES('M029','P012','M1',2750,'YES','2012-08-15','2012-08-31');

INSERT INTO PMS_MANUFACTURING VALUES('M030','P012','M2',1850,'NO','2012-08-15','2012-08-31');

INSERT INTO PMS_MANUFACTURING VALUES('M031','P013','C1',1300,'YES','2012-08-10','2012-08-11');


Solution of some questions


Here solution of some queries if you need more halp related to database contact at realcode4you


//7//

select PRODUCT_EXPIRY_DATE,product_name

from pms_manufacturing m join

pms_product p on

m.product_id=p.product_id where

PRODUCT_EXPIRY_DATE>='2012-12-15';

//8//

select PRODUCT_EXPIRY_DATE,product_name,count(product_name) count_product

from pms_manufacturing m join

pms_product p on

m.product_id=p.product_id where

PRODUCT_EXPIRY_DATE<='2012-12-15';

average