03 Sep Demonstrate Your Knowledge of SQL
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 were 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 name of every student, along with the grade received in English if the student has ever enrolled in that class. You need only include the Enrollment and Student tables, and may specify the class_id value of 102 for the English class.
5. Write a query to return the total number of students who have ever been enrolled in each of the classes.
6. Write a statement to modify 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 modify Robert Smith’s grade in English, 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, as long as 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 name of all students who have not enrolled in any class. Use a correlated subquery against the Enrollment table.
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 against the Enrollment table.
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 against the Enrollment table.
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 |
| 1 | 121 | 01-15-2019 |
| 2 | 234 | 07-24-2019 |
| 3 | 336 | 05-02-2020 |
| 4 | 121 | 01-15-2019 |
| 5 | 336 | 03-19-2020 |
| 6 | 234 | 07-24-2019 |
| 7 | 121 | 01-15-2019 |
| 8 | 336 | 06-12-2020 |
Prompt 2 Questions
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.
4. Write a query to retrieve each unique customer (cust_id) whose lowest order number (order_num) is at least 3. 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 which 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 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 |
7. 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 against the Customer_Order table.
7. Write a query to return the name of each customer, along with the total number of orders for each customer. Include all customers, regardless of whether or not they have orders. Use a scalar, correlated subquery to generate the number of orders.
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.