Statistics

INST327 – Assignment 2
(SELECT Queries with JOINs and UNIONs)
See Canvas for the deadline.
Be sure to complete all three questions.
Please carefully read the instructions below before proceeding to the questions.
Instructions
Use the ischool database to answer the questions. The database creation file is available on the
Canvas assignment page.
Submit your answers as one SQL file via Canvas. Name your file in the following format:
yourlastname_A2_queries.sql. For example, Lou_A2_queries.sql.
State in your SQL file what query is the answer to what question by numbering them with
comment delimiters. For example,
/*Answer 1*/
SELECT *
FROM vendors
Ensure that your queries replicate the sample result sets shown in the figures exactly, matching
all data and format aspects; otherwise, you will lose points. See the accompanying rubric for
details.
For this assignment, you are not allowed to use the following keywords: BETWEEN, LIKE,
LIMIT, IF, IFNULL, IN, REGEX, CASE, ASCII. Subqueries are also not allowed.
You are not allowed to use exact match conditions such as:
…WHERE last_name = “Smith”
or …WHERE last_name <> “Smith”
or …WHERE last_name != “Smith”
You are allowed to use inequality conditions such as > (greater than), < (less than), >= (greater
or equal to), and <= (less or equal to). However, you may not use them to filter primary key values. 2 1 You are allowed to use filtering conditions to include or exclude NULL values. For example, …WHERE date IS NOT NULL or …WHERE address IS NULL This and all remaining assignments in this course are individual work. Please work on your answers and scripts individually without help from other students in the course and other individuals. As required by University of Maryland regulations, all cases of misconduct are reported to the Office of Student Conduct irrespective of scope and circumstances. The instructor and TA are the best source for help while working on this assignment. Feel free to seek their help at office hours or Piazza. NOTE: Posting an assignment question on Chegg or any other site outside the ELMS course site to obtain assistance from anyone other than members of the instruction team of this course is NOT allowed. Late submission policy Submitting after deadline but within 3 hours of it: 10% reduction of base grade. Submitting more than 3 hours late but within 24 hours: 20% reduction of base grade. Submitting more than 24 hours late but within 48 hours: 40% reduction of base grade. Submitting more than 48 hours late: 100% reduction of base grade. SEE PAGE 3 FOR QUESTION 1 3 Q.1) SELECT query with multiple tables (30 points) Write a SELECT query that returns the full names, classifications, and departments of all those who have a classification. The result set should contain 109 rows. The query must replicate the result set shown in the figure below. Note that the result set is sorted by one column. Hint: check the bottom panel of Workbench under the response tab to see how many rows were returned. 109 rows returned. Only a sample is shown. SEE PAGE 4 FOR QUESTION 2 4 Q.2) Query with special JOIN(s) (30 points) Write a SELECT query that replicates the result set shown in the figure below. Be sure to match all data and format aspects, including column headers. The result set is not filtered and should contain 178 rows. Note that the result set is sorted by one column. 178 rows returned. Only a sample is shown. SEE PAGE 5 FOR QUESTION 3 5 Q.3) Query with special JOIN(s) and UNION(s) (40 points) Write a SELECT query that replicates the result set shown in the figure below. Be sure to match all data and format aspects, including column headers. The result set should contain 178 rows. Note that the result set is sorted by one column. The last few rows: 178 rows returned. Only a sample is shown.