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
//4//
select p.product_id,p.product_name,ud.unit_id,ud.unit_weight
from pms_product p join
pms_manufacturing m join
pms_unit_details ud on
p.product_id=m.product_id and
m.unit_id=ud.unit_id where
p.product_name like '%milk';
//5//
select
p.PRODUCT_NAME,ud.UNIT_NAME,ud.TOTAL_PIECES,ud.UNIT_WEIGHT
from pms_product p join
pms_unit_details ud join
pms_product_unit pu on
pu.product_id=p.product_id and
pu.unit_id=ud.unit_id
order by ud.unit_weight;
//6//
average
select product_id,sum(quantity) total_quantity
from pms_manufacturing
where availability='yes'
group by product_id
having sum(quantity)>1500;
//7//
select p.product_id,p.product_name,p.department_id,count(m.unit_id) number_varieties
from pms_product p join
pms_manufacturing m on
p.product_id=m.product_id
where
m.availability='yes'and
m.product_expiry_date>='2012-12-15'
group by m.product_id;
//8//
select manager_id,manager_name from
pms_manager_details
where salary>(select avg(salary) from pms_manager_details m join pms_department_details d
on m.department_id=d.department_id where
department_location!='ONGOLE');
//9//
SELECT MANAGER_ID,MANAGER_NAME,job,salary
from pms_manager_details
where salary>(select avg(salary) from pms_manager_details
group by department_id
having count(manager_id)=(select count(manager_id) from
pms_manager_details
group by department_id
limit 1));
//10//
select department_id,department_name
from pms_department_details
where department_id not in(select department_id from pms_manager_details
group by department_id);
complex
//1//
select manager_id,manager_name,job,salary,department_id
from pms_manager_details
where salary=(select salary from pms_manager_details
order by salary desc
limit 6,1);
//2//
select S.MANAGER_ID,S.MANAGER_NAME,S.JOB,S.SALARY,S.DEPARTMENT_ID,
B.MANAGER_ID,B.MANAGER_NAME,B.SALARY
from pms_manager_details s join
pms_manager_details b on
s.boss_code=b.manager_id
where
s.salary>b.salary;
//4//
select PRODUCT_ID, PRODUCT_NAME,DEPARTMENT_ID
from pms_product
where product_id=
(select product_id from
pms_manufacturing
group by product_id
order by sum(quantity) desc
limit 1);
//5//
select p.product_id,product_name,max(m.quantity)from
pms_product p join
pms_manufacturing m on
p.product_id=m.product_id
group by p.product_id;
Are need help in any database assignment help, database project help, MySQL assignment help, etc.
Then contact us at here