MySQL Assignment Questions

MySQL Assignment Questions

Q1. SELECT clause with WHERE, AND, DISTINCT, Wild Card (LIKE)

a. Fetch the employee number, first name and last name of those employees who are working as Sales Rep reporting to employee with employee number 1102 (Refer employee table)

Expected output:

b. Show the unique productline values containing the word cars at the end from the products table.

Expected output:

]

Q2. CASE STATEMENTS for Segmentation

.a. Using a CASE statement, segment customers into three categories based on their country:(Refer Customers table)

• "North America" for customers from USA or Canada
• "Europe" for customers from UK, France, or Germany
• "Other" for all remaining countries
• Select the customerNumber, customerName, and the assigned region as "CustomerSegment".

Expected output:

Q3. Group By with Aggregation functions and Having clause, Date and Time functions

a. Using the Order Details table, identify the top 10 products (by productCode) with the highest total order quantity across all orders.

Expected output:

b. Company wants to analyse payment frequency by month. Extract the month name from the payment date to count the total number of payments for each month and include only those months with a payment count exceeding 20. Sort the results by total number of payments in descending order. (Refer Payments table).

Expected output:

Q4. CONSTRAINTS: Primary, key, foreign key, Unique, check, not null, default

Create a new database named and Customers_Orders and add the following tables as per the description

a. Create a table named Customers to store customer information. Include the following columns:

• customer_id: This should be an integer set as the PRIMARY KEY and AUTO_INCREMENT.
• first_name: This should be a VARCHAR(50) to store the customer's first name.
• last_name: This should be a VARCHAR(50) to store the customer's last name.
• email: This should be a VARCHAR(255) set as UNIQUE to ensure no duplicate email addresses exist.
• phone_number: This can be a VARCHAR(20) to allow for different phone number formats.

Add a NOT NULL constraint to the first_name and last_name columns to ensure they always have a value.

b. Create a table named Orders to store information about customer orders. Include the following columns:

• order_id: This should be an integer set as the PRIMARY KEY and AUTO_INCREMENT.
• customer_id: This should be an integer referencing the customer_id in the Customers table  (FOREIGN KEY).
• order_date: This should be a DATE data type to store the order date.
• total_amount: This should be a DECIMAL(10,2) to store the total order amount.

Constraints:

1. Set a FOREIGN KEY constraint on customer_id to reference the Customers table.
2. Add a CHECK constraint to ensure the total_amount is always a positive value.

Q5. JOINS

a. List the top 5 countries (by order count) that Classic Models ships to. (Use the Customers and Orders tables)

Expected output:

Q6. SELF JOIN

a. Create a table project with below fields.

• Employee ID : integer set as the PRIMARY KEY and AUTO_INCREMENT.
• Full Name: varchar(50) with no null values
• Gender : Values should be only ‘Male’  or ‘Female’
• Manager ID: integer 

Add below data into it.

Find out the names of employees and their related managers.

Expected output:

Q7. DDL Commands: Create, Alter, Rename

a. Create table facility. Add the below fields into it.

• Facility_ID
• Name
• State
• Country

1. Alter the table by adding the primary key and auto increment to Facility_ID column.
2. Add a new column city after name with data type as varchar which should not accept any null values.

Q8. Views in SQL

a. Create a view named product_category_sales that provides insights into sales performance by product category. This view should include the following information:

• productLine: The category name of the product (from the ProductLines table).
• total_sales: The total revenue generated by products within that category (calculated by summing the order Details.quantity * order Details.priceEach for each product in the category).
• number_of_orders: The total number of orders containing products from that category.
• (Hint: Tables to be used: Products, orders, orderdetails and productlines)

The view when read should show the output as:

Q9. Stored Procedures in SQL with parameters

a. Create a stored procedureGet_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:

Q10. Window functions - Rank, dense_rank, lead and lag

a) Using customers and orders tables, rank the customers based on their order frequency

b) 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:

Q11. Subqueries and their applications

a. 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:

Q12. ERROR HANDLING in SQL

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.

Q13. TRIGGERS

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),  
('Marco', 'Doctor', '2020-10-04', 14),  
('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.