perform the following 10 types of complex queries

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 (-s4a1-), 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 count function with distinct clause
  • Query 2: Contains only some of the columns in one table with at least one aggregate function
  • Query 3: Contains only some of the columns in two tables involving a subquery
  • Query 4: Contains only some of the columns in two tables involving a subquery with atleast 2 aggregate functions
  • Query 5: Contains only some of the columns in two tables involving a subquery with atleast one string and aggregate function
  • Query 6: Contains only some of the columns in three tables involving a subquery
  • Query 7: Contains only some of the columns in three tables involving a subquery with atleast one string and aggregate function
  • Query 8: Contains only some of the columns in four tables involving a subquery
  • Query 9: Contains only some of the columns in four tables involving a subquery with atleast two string and aggregate functions
  • Query 10: Contains only some of the columns in four tables involving a subquery with atleast one mathematical and aggregate functions

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).

 
Looking for a similar assignment? Our writers will offer you original work free from plagiarism. We follow the assignment instructions to the letter and always deliver on time. Be assured of a quality paper that will raise your grade. Order now and Get a 15% Discount! Use Coupon Code "Newclient"

Also posted onJanuary 1, 1970 @ 12:00 am