Homework answers / question archive / The questions for this assignment will test your understanding of conditions, joins, grouping, and subqueries

The questions for this assignment will test your understanding of conditions, joins, grouping, and subqueries

Computer Science

Share With

---

The questions for this assignment will test your understanding of conditions, joins, grouping, and subqueries.  You will need to create your own test database and tables using the criteria below but it's not necessary to submit the scripts for creating the database objects. There are 2 prompts, with a total of 20 questions.

Please submit your answers using only one file. The preferable format is a text file with a .sql extension. You can easily edit the file using a text editor such as Notepad ++ or Sublime (for Macs as well as Windows), which are available online for free.

Prompt 1 Tables

A community college uses the following tables to track each student’s progress:

                     Class

class_id (p)	class_name
101	Geometry
102	English
103	Physics

 

                          Student

student_id (p)	first_name	last_name
500	Robert	Smith
762	Frank	Carter
881	Joseph	Evans
933	Anne	Baker

 

                                   Enrollment

class_id (p)(f)	student_id (p)(f)	semester (p)	grade
101	500	Fall 2019
102	500	Fall 2019
103	762	Fall 2019
101	881	Spring 2020
102	881	Fall 2020
103	762	Spring 2021	null

Please note that null is not a string value, i.e., 'null'.  It is a true null and should be inserted into the table without quotes.

Note: (p) = "primary key" and (f) = "foreign key". They are not part of the column names. 

Prompt 1 Questions
(There are 12 questions for Prompt 1).

Answer the following questions by constructing a single query without using subqueries, unless otherwise instructed.

1. Write a query to retrieve all columns from the Enrollment table where the grade of A or B was assigned.

2. Write a query to return the first and last names of each student who has taken Geometry.

3. Write a query to return all rows from the Enrollment table where the student has not been given a failing grade (F).  Include any rows where the grade has not yet been assigned.

4. Write a query to return the first and last names of every student in the Student table. If a student has ever enrolled in English, please specify the grade that they received.  You need only include the Enrollment and Student tables and may specify the class_id value of 102 for the English class. The query should return one row for each student (4 rows) with nulls as grades for students who don't have a grade.

5. Write a query to return the class names and the total number of students who have ever been enrolled in each class. If a student has enrolled in the same class twice, it is OK to count him twice in your results.

6. Write a statement to update Robert Smith’s grade for the English class from a B to a B+.  Specify the student by his student ID, which is 500, and the English class by class ID 102.

7. Create an alternate statement to update Robert Smith’s grade in English to a B+, but for this version specify the student by first/last name, not by student ID.  This will require the use of a subquery.

8. A new student name Michael Cronin enrolls in the Geometry class.  Construct a statement to add the new student to the Student table. (You can pick any value for the student_id, provided it doesn’t already exist in the table).

9. Add Michael Cronin’s enrollment in the Geometry class to the Enrollment table.  You may only specify names (e.g. “Michael”, “Cronin”, “Geometry”) and not numbers (e.g. student_id, class_num) in your statement.  You may use subqueries if desired, but the statement can also be written without the use of subqueries. Use ‘Spring 2020’ for the semester value.

10. Write a query to return the first and last names of all students who have not enrolled in any class. It is important to use a correlated subquery for this question. Please DO NOT use a JOIN.

11. Return the same results as the previous question (first and last name of all students who have not enrolled in any class), but formulate your query using a non-correlated subquery. It is important to use a non-correlated subquery for this question. Please DO NOT use a JOIN.

12. Write a statement to remove any rows from the Student table where the person has not enrolled in any classes.  You may use either a correlated or non-correlated subquery. Please DO NOT use a JOIN.

 

Prompt 2 Tables

The Customer_Order table, which stores data about customer orders, contains the following data:

 

                  Customer_Order

order_num	cust_id	order_date
	121	2019-01-15
	234	2019-07-24
	336	2020-05-02
	121	2019-01-15
	336	2020-03-19
	234	2019-07-24
	121	2019-01-15
	336	2020-06-12

 

Prompt 2 Questions

(There are 8 questions for Prompt 2).

1. Write a query to retrieve each unique customer ID (cust_id) from the Customer_Order table.  There are multiple ways to construct the query, but do not use a subquery.

2. Write a query to retrieve each unique customer ID (cust_id) along with the latest order date for each customer.  Do not use a subquery.

3. Write a query to retrieve all rows and columns from the Customer_Order table, with the results sorted by order date descending (latest date first) and then by customer ID ascending.

4. Write a query to retrieve each unique customer (cust_id) whose lowest order number (order_num) is at least 3.  Please note that this is referring to the value of the lowest order number and NOT the order count.  Do not use a subquery.

5. Write a query to retrieve only those customers who had 2 or more orders on the same day.  Retrieve the cust_id and order_date values, along with the total number of orders on that date.  Do not use a subquery.

6. Along with the Customer_Order table, there is another Customer table below. Write a query that returns the name of each customer who has placed exactly 3 orders.  Do not return the same customer name more than once, and use a correlated subquery (no JOINS please) against Customer_Order to determine the total number of orders for each customer:

                            Customer

cust_id	cust_name
121	Acme Wholesalers
234	Griffin Electric
336	East Coast Marine Supplies
544	Sanford Automotive

 

1. Construct a different query to return the same data as the previous question (name of each customer who has placed exactly 3 orders) but use a non-correlated subquery (no JOINS please) against the Customer_Order table. It is important to code a non-correlated subquery for this question.

2. Write a query to return the name of each customer, along with the total number of orders for each customer.  Include all customers, regardless if they have orders or not. Use a scalar, correlated subquery (no JOINS please) to generate the number of orders.