P4 Step 6: Complex Queries (4 points)
Attached Files:
Project 4 Step 6_Example.pdf (466.771 KB)
THIS PROJECT IS BASED ON YOUR PREVIOUS PROJECT (P3 STEP 5: CREATING DATABASE), POST THE FOLLOWING INFORMATION, IN THE RESPECTIVE QUESTIONS.
- You must submit a PDF document with all the description of the project as required below
- The MySQL script creating a database and making the required queries, that will implement the design completely.
- You DO NOT have to generate MySQL script from MySQL Workbench.
File name
- Template <ID used in the Google Form> – S6 – <Project Title
>
Before starting SECTION 11, attach the screenshot of your designed detailed ERD from the previous project (P3 STEP 5: CREATING DATABASE), for the reference.
SECTION 14 COMPLEX QUERIES SCRIPT (4 points = 10*0.4 points)
Based on the database created in your previous project (P3 STEP 5: CREATING DATABASE) , perform the following 10 types of complex queries.
For each query, you must include in the PDF file the following aspects:
- A description in English of what the query is intended to do (this is what the query will be check against)
- The source of the query
- The resulted table after the execution of the query
It is very important that the query will make sense in the context of the database, i.e. one can imagine a real life scenario when such a query will be made. A query that makes no sense or does not follow the intended description will receive 0 points.
Construct ten queries, based on single and multiple tables. For queries involving more than one table use JOIN clause. Your queries must satisfy the following requirements:
- Query 1: Contains only some of the columns in one table involving count function with distinct clause
- Query 2: Contains only some of the columns in two tables with at least one aggregate function
- Query 3: Contains only some of the columns in three tables involving a subquery
- Query 4: Contains only some of the columns in three tables involving a GROUP BY statement and at least one aggregate function
- Query 5: Contains only some of the columns in four tables involving a GROUP BY statement and HAVING clause
- Query 6: Contains only some of the columns in three tables involving GROUP BY and a subquery
- Query 7: Contains only some of the columns in at least four tables involving a GROUP BY and a subquery
- Query 8: Contains only some of the columns in at least three tables involving a GROUP BY statement and HAVING clause and a subquery
- Query 9: Contains only some of the columns in at least three tables involving both WHERE and HAVING clause
- Query 10: Contains only some of the columns in at least three tables involving both WHERE and HAVING clause and a subquery
Is this the question you were looking for? Place your Order Here
