Spring-2024 Database System Assignment # 3: Ofcomputer&Emergingsciencesfaisalabad-Chiniotcampus
Spring-2024 Database System Assignment # 3: Ofcomputer&Emergingsciencesfaisalabad-Chiniotcampus
Spring-2024 Database System Assignment # 3: Ofcomputer&Emergingsciencesfaisalabad-Chiniotcampus
OfComputer&EmergingSciencesFaisalabad-ChiniotCampus
Spring-2024
DataBase System
Assignment # 3
Submission Guideline:
1- Submit Word File with proper sql code and snip from oracle with your Roll# d
2- Plagiarism in any module will lead to an F grade in a particular course.
3- Deadline: 24-March-2024 till 1:00 PM.
4- No extension in deadline. Submission portal will be closed after deadline.
Objectives:
12. Write a query that displays the last name (with the first letter uppercase and all other letters
lowercase) and the length of the last name for all employees whose name starts with the letters
J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last
names.
13. Create a query to display the last name and salary for all employees. Format the salary to be 15
characters long, left-padded with $ symbol. Label the column SALARY.
14. Write a query to display the number of people with the same job.
15. Find the difference between the highest and lowest salaries. Label the column DIFFERENCE
16. Create a report to display the manager’s number and the salary of the lowest-paid employee for
that manager. Exclude anyone whose manager is not known. Exclude any groups where the
minimum salary is $6,000 or less. Sort the output in descending order of salary.
17. Change the salary to $1,000 for all employees with a salary less than $900.
18. Delete any one name of employee from the EMPLOYEE_Roll# table.
19. Create the EMPLOYEES2 table based on the structure of the EMPLOYEES table. Include
only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY columns. Name the
columns in your new table ID, F_name, LAST_NAME, and Sal respectively.
20. Drop table EMPOLYEES 2.
INSERT INTO PRODUCT_INFORMATION VALUES (1, 'Product A', 'Category 1', 'Manufacturer 1',
'Red', 'Metal', 1.5, 10.0, 5.0, 3.0, 10.99, 4.2, TO_DATE('2022-01-05', 'YYYY-MM-DD'), 'Salesperson 1',
'This is a description of Product A.');
National University
OfComputer&EmergingSciencesFaisalabad-ChiniotCampus
INSERT INTO PRODUCT_INFORMATION VALUES (2, 'Product B', 'Category 2', 'Manufacturer 2',
'Blue', 'Plastic', 2.5, 20.0, 10.0, 5.0, 20.99, 3.8, TO_DATE('2022-02-10', 'YYYY-MM-DD'), 'Salesperson
2', 'This is a description of Product B.');
INSERT INTO PRODUCT_INFORMATION VALUES (3, 'Product C', 'Category 1', 'Manufacturer 3',
'Green', 'Wood', 1.0, 5.0, 5.0, 2.0, 15.99, 4.5, TO_DATE('2022-03-15', 'YYYY-MM-DD'), 'Salesperson 1',
'This is a description of Product C.');
INSERT INTO PRODUCT_INFORMATION VALUES (4, 'Product D', 'Category 3', 'Manufacturer 1',
'Yellow', 'Metal', 3.0, 15.0, 5.0, 5.0, 30.99, 4.0, TO_DATE('2022-04-20', 'YYYY-MM-DD'), 'Salesperson
3', 'This is a description of Product D.');
INSERT INTO PRODUCT_INFORMATION VALUES (5, 'Product E', 'Category 2', 'Manufacturer 2',
'Purple', 'Plastic', 1.5, 10.0, 10.0, 3.0, 25.99, 4.8, TO_DATE('2022-05-25', 'YYYY-MM-DD'), 'Salesperson
2', 'This is a description of Product E.');
INSERT INTO PRODUCT_INFORMATION VALUES (6, 'Product F', 'Category 1', 'Manufacturer 3',
'Blue', 'Wood', 2.5, 10.0, 5.0, 4.0, 22.99, 3.5, TO_DATE('2022-06-30', 'YYYY-MM-DD'), 'Salesperson 1',
'This is a description of Product F.');
Best of Luck 😉