construct-ten-queries-based-on-single-and-multiple-tables

THIS HOMEWORK ASSIGNMENT IS BASED ON YOUR PREVIOUS HOMEWORK ASSIGNMENT (S3 A1: SMALL DESIGN IMPLEMENTATION), POST THE FOLLOWING INFORMATION, IN THE RESPECTIVE QUESTIONS.

  1. You must submit a PDF document with all the description of the homework as required below
  2. The MySQL script creating a database and making the required queries, that will implement the design completely.

Start all your file names with your email id (e.g. mboicu for me), followed by (-s5a1-), and you may add a name for your database.

Before starting SECTION 1, attach the screenshot of your designed ERD from the previous homework assignment (S3 A1: SMALL DESIGN IMPLEMENTATION) along with the required model, for the reference.

SECTION 1 COMPLEX QUERIES SCRIPT (3 points = 10*0.30 points)

Based on the database created in your previous homework assignment (S3 A1: SMALL DESIGN IMPLEMENTATION), 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 a GROUP BY statement and at least one aggregate function
  • Query 2: Contains only some of the columns in two tables involving a GROUP BY statement and at least one aggregate function
  • Query 3: Contains only some of the columns in two tables involving a GROUP BY statement and HAVING clause
  • Query 4: Contains only some of the columns in two tables involving a string function,GROUP BY statement and HAVING clause
  • Query 5: Contains only some of the columns in three tables involving a mathematical function and GROUP BY statement
  • Query 6: Contains only some of the columns in three tables involving a GROUP BY statement and HAVING clause
  • Query 7: Contains only some of the columns in three tables involving both WHERE and HAVING clause
  • Query 8: Contains only some of the columns in four tables involving a GROUP BY statement and HAVING clause
  • Query 9: Contains only some of the columns in four tables involving both WHERE and HAVING clause
  • Query 10: Contains only some of the columns in four tables involving a WHERE clause and GROUP BY statement

Note: SQL SCRIPT (a text file) should NOT be generated by MySQL Workbench. If it is generated by MySQL Workbench (No points will be given for the script file) . Image or file is not signed -0.3 points (-0.30 points).