ISDS 3110: Data and Information Management LAB 4: Joining Tables SQL Queries In this lab, you will be querying the text schema on the MySQL server. The required tables are person and car. One person can sell many cars, while a car can only be sold by one person Part 1 - Examining the data The first step is to get acquainted with the contents of these two tables. Execute the following queries: USE text; #Use database text. SELECT * FROM person; "pay close attention to the format of the column names! How do these differ from what we've used so far in our queries2 SELECT * FROM car; Part II - Aggregate Functions and Group by. Create queries to answer the following questions: Remember: The five aggregate functions are MIN, MAX, AVG, COUNT, and SUM. Each of these functions returns a single value or single value per group. 1. How many different types of cars are in the car table? 2. What is the highest cost of cach type of car in the table? 3. What is the average sale price of each type of car in the table? 4. List the cost and carid of all cars that cost GREATER THAN the average cost of all cars. (HINT: use a nested query) 5. What is the total gross profit of each type of car in the table? (HINT: gross profit - sell - cost) Part III - Create queries to answer the following questions: 6. List all details of car sales, including salesperson's name and the gross profit (selling price minus cost price). (Hint: What special consideration do you have to deal with in this example? Specifically, how to handle column name with a space?). 7. List details of the sales made by Bruce Bush. 8. What is the average selling price of cars and number of cars sold by Sue Lim? 9. Report the gross profit made, number of cars sold, first name and last name by each salesperson. Part IV - Create tables according to the following ER diagram: 10. Write SQL code using data definition language (DDL) for the following ER diagram. H+ Have Department "DepNO DepName Address City Zipcode Projects "Project StartDate EndDate Desc - = Part IV - Create tables according to the following ER diagram: 10. Write SQL code using data definition language (DDL) for the following ER diagram. HT -Have Department DepNO DepName Address City Zipcode Projects ProjectNo StartDate EndDate Desc Use your own database to test the CREATE TABLE command: USE [your PAWS ID) database; Submit a single document (SQL FILE) via the Moodle. Please name your submission as "LAB4_firstname_lastname