Spring-2024 Database System Assignment # 3: Ofcomputer&Emergingsciencesfaisalabad-Chiniotcampus

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 3

National University

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:

 DDL (Create, Drop, Alter)


 DML (Select, Insert, Update, Delete, From, Where, Group by, Having, Order by, Built-in-
functions)
Q1: Use Oracle 11g software for answer the following Query. (60)
1. Create table Employee_roll# having attributes (id, first_name, last_name, salary, dob,
manager_id, title, joining_date) with appropriate datatypes.
2. Write a query to insert two rows in table Employee_roll#.
3. To familiarize yourself with the data in the EMPLOYEES table, create a query to display all
the data from the EMPLOYEES table. Separate each column output by a comma. Name the
column title THE_OUTPUT.
4. The HR departments needs to find high-salary and low-salary employees. Write a query to
display the last name and salary for all employees whose salary is not in the range of $5,000 to
$12,000.
5. Create a report to display the last name, title, and start date for the employees with the last
names of Matos and Taylor. Order the query in ascending order by start date.
6. Query to list the last name and salary of employees who earn between $5,000 and $12,000 and
are in department 20 or 50.
7. Create a report to display the last name and job title of all employees who do not have a
manager.
8. Members of the HR department want to have more flexibility with the queries that you are
writing. They would like a report that displays the last name and salary of employees who earn
more than an amount that the user specifies after a prompt.
9. Display the last name of all employees who have both an ‘a’ and an ‘e’ in their last name.
10. Write a query to display the current date. Label the column Date.
11. The HR department needs a report to display the employee number, last_name, salary, and
salary increased by 15.5% (expressed as a whole number) for each employee. Label the
column New Salary.
National University
OfComputer&EmergingSciencesFaisalabad-ChiniotCampus

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.

Q2: Create the following table:


(40)

CREATE TABLE PRODUCT_INFORMATION (


PRODUCT_ID INT PRIMARY KEY,
PRODUCT_NAME VARCHAR(50),
PRODUCT_CATEGORY VARCHAR(50),
PRODUCT_MANUFACTURER VARCHAR(50),
PRODUCT_COLOR VARCHAR(20),
PRODUCT_MATERIAL VARCHAR(20),
PRODUCT_WEIGHT FLOAT,
PRODUCT_LENGTH FLOAT,
PRODUCT_WIDTH FLOAT,
PRODUCT_HEIGHT FLOAT,
PRODUCT_PRICE FLOAT,
PRODUCT_RATING FLOAT,
PRODUCT_SOLD_DATE DATE,
SALES_PERSON VARCHAR(50),
PRODUCT_DESCRIPTION VARCHAR(200)
);

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.');

Answer the following Queries:


1- What is the average price of products in each category, but only for categories with more than 10
products?
2- What is the total price of products sold in each year and month, but only for months where the total
price is greater than $10,000?
3- What is the minimum price of products manufactured by each manufacturer and in each color, but
only for manufacturers and colors with more than 3 products?
4- What is the total price of products sold by each salesperson, in each category?
5- What is the total weight of products sold by each salesperson, in each category and material?
6- What is the total price of products sold by each salesperson, in each year and month, and in each
category?
7- What is the total price of products sold in each year, and in each category?
8- Find the total sales and average rating for each product category and salesperson:
9- Retrieve all products sold after January 1, 2022, ordered by their sold date in ascending order and
their price in descending order.
10- Retrieve all products in the Category 1, 2, or 3 and sold by Salesperson 1 or Salesperson 2, ordered
by their category in descending order, weight in ascending order, and price in descending order.
11- Find the total sales amount for each product category where the total sales amount is greater than
$100.
12- List the manufacturers along with the average rating of their products, but only include
manufacturers who have an average rating greater than 4.
13- Count the number of products sold by each salesperson and display only those salespeople who
have sold more than 2 products.
14- Find the average weight of products in each category, but only show categories where the average
weight is less than 2.
15- Identify the products with a rating greater than 4 and sold by Salesperson 1

Best of Luck 😉

You might also like