Day wise Assignment Questions

Note: -
1. The tables that are mentioned in the questions for the references are available in the
classic model database.

2. In the questions, if they specifically mention to create the tables, then you need to create the
tables as per given specifications.

Day 1

No questions

Day 2

No questions

Day 3x

1) Show customer number, customer name, state and credit limit from customers table for below
conditions. Sort the results by highest to lowest values of creditLimit.

● State should not contain null values

● credit limit should be between 50000 and 100000

Expected output:
 2) xShow the unique productline values containing the word cars at the end from products table.
Expected output:

Day 4

1) xShow the orderNumber, status and comments from orders table for shipped status only. If some
comments are having null values then show them as “-“.

Expected output:

2) xSelect employee number, first name, job title and job title abbreviation from employees table based
on following conditions.
If job title is one among the below conditions, then job title abbreviation column should show below
forms.
● President then “P”

● Sales Manager / Sale Manager then “SM”

● Sales Rep then “SR”

● Containing VP word then “VP”

Day 5:
x
1) For every year, find the minimum amount value from payments table.

Expected output:

2) xFor every year and every quarter, find the unique customers and total orders from orders table.
Make sure to show the quarter as Q1,Q2 etc.

Expected output:

3) Show the formatted amount in thousands unit (e.g. 500K, 465K etc.) for every month (e.g. Jan, Feb
etc.) with filter on total amount as 500000 to 1000000. Sort the output by total amount in
 descending mode. [ Refer. Payments Table]

Expected output:

Day 6:

1) Create a journey table with following fields and constraints.

● Bus_ID (No null values)

● Bus_Name (No null values)

● Source_Station (No null values)

● Destination (No null values)

● Email (must not contain any duplicates)

2) Create vendor table with following fields and constraints.

● Vendor_ID (Should not contain any duplicates and should not be null)

● Name (No null values)

● Email (must not contain any duplicates)

● Country (If no data is available then it should be shown as “N/A”)

3) Create movies table with following fields and constraints.

● Movie_ID (Should not contain any duplicates and should not be null)

● Name (No null values)

● Release_Year (If no data is available then it should be shown as “-”)

● Cast (No null values)

● Gender (Either Male/Female)

● No_of_shows (Must be a positive number)

a. Product
✔ product_id - primary key

✔ product_name - cannot be null and only unique values are allowed

✔ description

✔ supplier_id - foreign key of supplier table

b. Suppliers
✔ supplier_id - primary key

✔ supplier_name

✔ location

c. Stock
✔ id - primary key

✔ product_id - foreign key of product table

✔ balance_stock

Day 7

1) Show employee number, Sales Person (combination of first and last names of employees),
unique customers for each employee number and sort the data by highest to lowest unique
customers.

Tables: Employees, Customers

Expected output:
2) Show total quantities, total quantities in stock, left over quantities for each product and each
customer. Sort the data by customer number.

Tables: Customers, Orders, Orderdetails, Products

Expected output:

3) Create below tables and fields. (You can add the data as per your wish)

● Laptop: (Laptop_Name)

● Colours: (Colour_Name)

Perform cross join between the two tables and find number of rows.

Expected output:
4) Create table project with below fields.

● EmployeeID

● FullName

● Gender

● ManagerID

Add below data into it.

INSERT INTO Project VALUES(1, 'Pranaya', 'Male', 3);

INSERT INTO Project VALUES(2, 'Priyanka', 'Female', 1);

INSERT INTO Project VALUES(3, 'Preety', 'Female', NULL);

INSERT INTO Project VALUES(4, 'Anurag', 'Male', 1);

INSERT INTO Project VALUES(5, 'Sambit', 'Male', 1);

INSERT INTO Project VALUES(6, 'Rajesh', 'Male', 3);

INSERT INTO Project VALUES(7, 'Hina', 'Female', 3);

Find out the names of employees and their related managers.

Expected output:
Day 8

Create table facility. Add the below fields into it.

● Facility_ID

● Name

● State

● Country

i) Alter the table by adding the primary key and auto increment to Facility_ID column.

ii) Add a new column city after name with data type as varchar which should not accept any null
values.

Expected output:

Day 9

Create table university with below fields.

● ID

● Name

Add the below data into it as it is.

INSERT INTO University

VALUES (1, " Pune University "),

(2, " Mumbai University "),

(3, " Delhi University "),

(4, "Madras University"),

(5, "Nagpur University");

Remove the spaces from everywhere and update the column like Pune University etc.

Expected output:
Day 10

Create the view products status. Show year wise total products sold. Also find the percentage of total
value for each year. The output should look as shown in below figure.

Expected output:

Day 11

1) Create a stored procedure GetCustomerLevel which takes input as customer number and
gives the output as either Platinum, Gold or Silver as per below criteria.

Table: Customers

● Platinum: creditLimit > 100000

● Gold: creditLimit is between 25000 to 100000

● Silver: creditLimit < 25000

2) Create a stored procedure Get_country_payments which takes in year and country as inputs
and gives year wise, country wise total amount as an output. Format the total amount to
nearest thousand unit (K)

Tables: Customers, Payments

Expected output:
Day 12

1) Calculate year wise, month name wise count of orders and year over year (YoY) percentage
change. Format the YoY values in no decimals and show in % sign.

Table: Orders

Expected output:

2) Create the table emp_udf with below fields.

● Emp_ID

● Name

● DOB

Add the data as shown in below query.

INSERT INTO Emp_UDF(Name, DOB)

VALUES ("Piyush", "1990-03-30"), ("Aman", "1992-08-15"), ("Meena", "1998-07-28"),

Create a user defined function calculate_age which returns the age in years and months (e.g.
30 years 5 months) by accepting DOB column as a parameter.
 Expected output:

Day 13

1) Display the customer numbers and customer names from customers table who have not
placed any orders using subquery

Table: Customers, Orders

Expected output:

2) Write a full outer join between customers and orders using union and get the customer
number, customer name, count of orders for every customer.

Table: Customers, Orders

Expected output:
3) Show the second highest quantity ordered value for each order number.

Table: Orderdetails

Expected output:

4) For each order number count the number of products and then find the min and max of the
values among count of orders.

Table: Orderdetails

Expected output:
 5) Find out how many product lines are there for which the buy price value is greater than the
average of buy price value. Show the output as product line and its count.

Expected output:

Day 14

Create the table Emp_EH. Below are its fields.

● EmpID (Primary Key)

● EmpName

● EmailAddress

Create a procedure to accept the values for the columns in Emp_EH. Handle the error using
exception handling concept. Show the message as “Error occurred” in case of anything wrong.

Day 15

Create the table Emp_BIT. Add below fields in it.

● Name

● Occupation

● Working_date

● Working_hours

Insert the data as shown in below query.

INSERT INTO Emp_BIT VALUES

('Robin', 'Scientist', '2020-10-04', 12),

('Warner', 'Engineer', '2020-10-04', 10),

('Peter', 'Actor', '2020-10-04', 13),

('Brayden', 'Teacher', '2020-10-04', 12),

('Antonio', 'Business', '2020-10-04', 11);

Create before insert trigger to make sure any new value of Working_hours, if it is negative, then it
should be inserted as positive.