Assignment Prompt & Instructions
SQL help
– 2 years ago
**files (2)**
– ## SQLLesson3Questionstocomplete.docx
– ## Lesson3ExerciseDirections.docx
**## SQLLesson3Questionstocomplete.docx**
Exercise 8: Write your SQL statement in a file named CIS276DA_Lesson3Exercise8_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise8_MEID.csv.
Exercise 9: Write your SQL statement in a file named CIS276DA_Lesson3Exercise9_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise9_MEID.csv.
Exercise 10: Write your SQL statement in a file named CIS276DA_Lesson3Exercise10_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise10_MEID.csv.
Exercise 11: Write your SQL statement in a file named CIS276DA_Lesson3Exercise11_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise11_MEID.csv .
Custom Query 1: Create a SELECT statement that uses WHERE. Save this SQL statement as CIS276DA_Lesson3Custom1_MEID.sql . Run the script and export the results to CIS276DA_Lesson3Custom1_MEID.csv.
Custom Query 2: Create a SELECT statement that uses ORDER BY and LIMIT. Save this SQL statement as CIS276DA_Lesson3Custom2_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Custom2_MEID.csv.
Place all SQL files(6) and CSV files(6) in a folder labeled "Lesson 3".
Replace "MEID" in the file names with your actual MEID.
Zip the entire "Lesson 3" folder as described in the Syllabus.
Submit the zipped assignment folder using the link below
**## Lesson3ExerciseDirections.docx**
Run some of the examples in this chapter In these exercises, you’ll use MySQL Workbench to run some of the scripts for the examples in this chapter. This assumes that you already know how to use MySQL Workbench, as described in chapter 2.
1. Start MySQL Workbench.
2. Open the script named 3‐02.sql that you should find in this directory: c:\murach\mysql\book_scripts\ch03. When it opens, you should see all of the queries for figure 3‐2. Note that each of these queries has a semicolon at the end of it.
3. Move the insertion point into the first query and press Ctrl+Enter or click on the Execute Current Statement button to run the query. This shows you the data that’s in the Invoices table that you’ll be working with in this chapter.
4. Move the insertion point into the second query and run it.
parameter
5. Open the script named 3‐05.sql in the ch03 directory. Then, run the second query. When you do, you’ll see that the result set is in sequence by the invoice_id column.
6. Delete the ORDER BY clause from the SELECT statement and run the query again. Scroll through the result set to see that the rows are no longer in a particular sequence. When you’re done, close the script without saving the changes.
7. Open and run the queries for any of the other examples in this chapter that you’re interested in reviewing.
Enter and run your own SELECT statements In these exercises, you’ll enter and run your own SELECT statements. To do that, you can open the script for an example that is similar to the statement you need to write, copy the statement into a new SQL tab, and modify the statement. That can save you both time and syntax errors.
8. Write a SELECT statement that returns three columns from the Vendors table: vendor_name, vendor_contact_last_name, and vendor_contact_first_name. Then, run this statement to make sure it works correctly. Add an ORDER BY clause to this statement that sorts the result set by last name and then first name, both in ascending sequence. Then, run this state‐ment again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.
9. Write a SELECT statement that returns one column from the Vendors table named full_name that joins the vendor_contact_last_name and vendor_contact_first_name columns. Format this column with the last name, a comma, a space, and the first name like this:
Doe, John
Sort the result set by last name and then first name in ascending sequence. Return only the contacts whose last name begins with the letter A, B, C, or E. This should retrieve 41 rows.
10. Write a SELECT statement that returns these column names and data from the Invoices table: Due Date
Invoice Total 10%
Plus 10%
The invoice_due_date column The invoice_total column
10% of the value of invoice_total The value of invoice_total plus 10%
Return only the rows with an invoice total that’s greater than or equal to 500 and less than or equal to 1000. This should retrieve 12 rows. Sort the result set in descending sequence by invoice_due_date.
11. Write a SELECT statement that returns these columns from the Invoices table: invoice_number invoice_total
The invoice_number column The invoice_total column
payment_credit_total balance_due
Sum of the payment_total and credit_total columns
The invoice_total column minus the payment_total and credit_total columns
Return only invoices that have a balance due that’s greater than $50. Sort the result set by balance due in descending sequence. Use the LIMIT clause so the result set contains only the rows with the 5 largest balances.
**## Lesson3ExerciseDirections.docx**
Run some of the examples in this chapter In these exercises, you’ll use MySQL Workbench to run some of the scripts for the examples in this chapter. This assumes that you already know how to use MySQL Workbench, as described in chapter 2.
1. Start MySQL Workbench.
2. Open the script named 3‐02.sql that you should find in this directory: c:\murach\mysql\book_scripts\ch03. When it opens, you should see all of the queries for figure 3‐2. Note that each of these queries has a semicolon at the end of it.
3. Move the insertion point into the first query and press Ctrl+Enter or click on the Execute Current Statement button to run the query. This shows you the data that’s in the Invoices table that you’ll be working with in this chapter.
4. Move the insertion point into the second query and run it.
parameter
5. Open the script named 3‐05.sql in the ch03 directory. Then, run the second query. When you do, you’ll see that the result set is in sequence by the invoice_id column.
6. Delete the ORDER BY clause from the SELECT statement and run the query again. Scroll through the result set to see that the rows are no longer in a particular sequence. When you’re done, close the script without saving the changes.
7. Open and run the queries for any of the other examples in this chapter that you’re interested in reviewing.
Enter and run your own SELECT statements In these exercises, you’ll enter and run your own SELECT statements. To do that, you can open the script for an example that is similar to the statement you need to write, copy the statement into a new SQL tab, and modify the statement. That can save you both time and syntax errors.
8. Write a SELECT statement that returns three columns from the Vendors table: vendor_name, vendor_contact_last_name, and vendor_contact_first_name. Then, run this statement to make sure it works correctly. Add an ORDER BY clause to this statement that sorts the result set by last name and then first name, both in ascending sequence. Then, run this state‐ment again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.
9. Write a SELECT statement that returns one column from the Vendors table named full_name that joins the vendor_contact_last_name and vendor_contact_first_name columns. Format this column with the last name, a comma, a space, and the first name like this:
Doe, John
Sort the result set by last name and then first name in ascending sequence. Return only the contacts whose last name begins with the letter A, B, C, or E. This should retrieve 41 rows.
10. Write a SELECT statement that returns these column names and data from the Invoices table: Due Date
Invoice Total 10%
Plus 10%
The invoice_due_date column The invoice_total column
10% of the value of invoice_total The value of invoice_total plus 10%
Return only the rows with an invoice total that’s greater than or equal to 500 and less than or equal to 1000. This should retrieve 12 rows. Sort the result set in descending sequence by invoice_due_date.
11. Write a SELECT statement that returns these columns from the Invoices table: invoice_number invoice_total
The invoice_number column The invoice_total column
payment_credit_total balance_due
Sum of the payment_total and credit_total columns
The invoice_total column minus the payment_total and credit_total columns
Return only invoices that have a balance due that’s greater than $50. Sort the result set by balance due in descending sequence. Use the LIMIT clause so the result set contains only the rows with the 5 largest balances.
**## SQLLesson3Questionstocomplete.docx**
Exercise 8: Write your SQL statement in a file named CIS276DA_Lesson3Exercise8_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise8_MEID.csv.
Exercise 9: Write your SQL statement in a file named CIS276DA_Lesson3Exercise9_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise9_MEID.csv.
Exercise 10: Write your SQL statement in a file named CIS276DA_Lesson3Exercise10_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise10_MEID.csv.
Exercise 11: Write your SQL statement in a file named CIS276DA_Lesson3Exercise11_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise11_MEID.csv .
Custom Query 1: Create a SELECT statement that uses WHERE. Save this SQL statement as CIS276DA_Lesson3Custom1_MEID.sql . Run the script and export the results to CIS276DA_Lesson3Custom1_MEID.csv.
Custom Query 2: Create a SELECT statement that uses ORDER BY and LIMIT. Save this SQL statement as CIS276DA_Lesson3Custom2_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Custom2_MEID.csv.
Place all SQL files(6) and CSV files(6) in a folder labeled "Lesson 3".
Replace "MEID" in the file names with your actual MEID.
Zip the entire "Lesson 3" folder as described in the Syllabus.
Submit the zipped assignment folder using the link below
**## Lesson3ExerciseDirections.docx**
Run some of the examples in this chapter In these exercises, you’ll use MySQL Workbench to run some of the scripts for the examples in this chapter. This assumes that you already know how to use MySQL Workbench, as described in chapter 2.
1. Start MySQL Workbench.
2. Open the script named 3‐02.sql that you should find in this directory: c:\murach\mysql\book_scripts\ch03. When it opens, you should see all of the queries for figure 3‐2. Note that each of these queries has a semicolon at the end of it.
3. Move the insertion point into the first query and press Ctrl+Enter or click on the Execute Current Statement button to run the query. This shows you the data that’s in the Invoices table that you’ll be working with in this chapter.
4. Move the insertion point into the second query and run it.
parameter
5. Open the script named 3‐05.sql in the ch03 directory. Then, run the second query. When you do, you’ll see that the result set is in sequence by the invoice_id column.
6. Delete the ORDER BY clause from the SELECT statement and run the query again. Scroll through the result set to see that the rows are no longer in a particular sequence. When you’re done, close the script without saving the changes.
7. Open and run the queries for any of the other examples in this chapter that you’re interested in reviewing.
Enter and run your own SELECT statements In these exercises, you’ll enter and run your own SELECT statements. To do that, you can open the script for an example that is similar to the statement you need to write, copy the statement into a new SQL tab, and modify the statement. That can save you both time and syntax errors.
8. Write a SELECT statement that returns three columns from the Vendors table: vendor_name, vendor_contact_last_name, and vendor_contact_first_name. Then, run this statement to make sure it works correctly. Add an ORDER BY clause to this statement that sorts the result set by last name and then first name, both in ascending sequence. Then, run this state‐ment again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.
9. Write a SELECT statement that returns one column from the Vendors table named full_name that joins the vendor_contact_last_name and vendor_contact_first_name columns. Format this column with the last name, a comma, a space, and the first name like this:
Doe, John
Sort the result set by last name and then first name in ascending sequence. Return only the contacts whose last name begins with the letter A, B, C, or E. This should retrieve 41 rows.
10. Write a SELECT statement that returns these column names and data from the Invoices table: Due Date
Invoice Total 10%
Plus 10%
The invoice_due_date column The invoice_total column
10% of the value of invoice_total The value of invoice_total plus 10%
Return only the rows with an invoice total that’s greater than or equal to 500 and less than or equal to 1000. This should retrieve 12 rows. Sort the result set in descending sequence by invoice_due_date.
11. Write a SELECT statement that returns these columns from the Invoices table: invoice_number invoice_total
The invoice_number column The invoice_total column
payment_credit_total balance_due
Sum of the payment_total and credit_total columns
The invoice_total column minus the payment_total and credit_total columns
Return only invoices that have a balance due that’s greater than $50. Sort the result set by balance due in descending sequence. Use the LIMIT clause so the result set contains only the rows with the 5 largest balances.
**## SQLLesson3Questionstocomplete.docx**
Exercise 8: Write your SQL statement in a file named CIS276DA_Lesson3Exercise8_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise8_MEID.csv.
Exercise 9: Write your SQL statement in a file named CIS276DA_Lesson3Exercise9_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise9_MEID.csv.
Exercise 10: Write your SQL statement in a file named CIS276DA_Lesson3Exercise10_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise10_MEID.csv.
Exercise 11: Write your SQL statement in a file named CIS276DA_Lesson3Exercise11_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Exercise11_MEID.csv .
Custom Query 1: Create a SELECT statement that uses WHERE. Save this SQL statement as CIS276DA_Lesson3Custom1_MEID.sql . Run the script and export the results to CIS276DA_Lesson3Custom1_MEID.csv.
Custom Query 2: Create a SELECT statement that uses ORDER BY and LIMIT. Save this SQL statement as CIS276DA_Lesson3Custom2_MEID.sql. Run the script and export the results to CIS276DA_Lesson3Custom2_MEID.csv.
Place all SQL files(6) and CSV files(6) in a folder labeled "Lesson 3".
Replace "MEID" in the file names with your actual MEID.
Zip the entire "Lesson 3" folder as described in the Syllabus.
Submit the zipped assignment folder using the link below
**## Lesson3ExerciseDirections.docx**
Run some of the examples in this chapter In these exercises, you’ll use MySQL Workbench to run some of the scripts for the examples in this chapter. This assumes that you already know how to use MySQL Workbench, as described in chapter 2.
1. Start MySQL Workbench.
2. Open the script named 3‐02.sql that you should find in this directory: c:\murach\mysql\book_scripts\ch03. When it opens, you should see all of the queries for figure 3‐2. Note that each of these queries has a semicolon at the end of it.
3. Move the insertion point into the first query and press Ctrl+Enter or click on the Execute Current Statement button to run the query. This shows you the data that’s in the Invoices table that you’ll be working with in this chapter.
4. Move the insertion point into the second query and run it.
parameter
5. Open the script named 3‐05.sql in the ch03 directory. Then, run the second query. When you do, you’ll see that the result set is in sequence by the invoice_id column.
6. Delete the ORDER BY clause from the SELECT statement and run the query again. Scroll through the result set to see that the rows are no longer in a particular sequence. When you’re done, close the script without saving the changes.
7. Open and run the queries for any of the other examples in this chapter that you’re interested in reviewing.
Enter and run your own SELECT statements In these exercises, you’ll enter and run your own SELECT statements. To do that, you can open the script for an example that is similar to the statement you need to write, copy the statement into a new SQL tab, and modify the statement. That can save you both time and syntax errors.
8. Write a SELECT statement that returns three columns from the Vendors table: vendor_name, vendor_contact_last_name, and vendor_contact_first_name. Then, run this statement to make sure it works correctly. Add an ORDER BY clause to this statement that sorts the result set by last name and then first name, both in ascending sequence. Then, run this state‐ment again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.
9. Write a SELECT statement that returns one column from the Vendors table named full_name that joins the vendor_contact_last_name and vendor_contact_first_name columns. Format this column with the last name, a comma, a space, and the first name like this:
Doe, John
Sort the result set by last name and then first name in ascending sequence. Return only the contacts whose last name begins with the letter A, B, C, or E. This should retrieve 41 rows.
10. Write a SELECT statement that returns these column names and data from the Invoices table: Due Date
Invoice Total 10%
Plus 10%
The invoice_due_date column The invoice_total column
10% of the value of invoice_total The value of invoice_total plus 10%
Return only the rows with an invoice total that’s greater than or equal to 500 and less than or equal to 1000. This should retrieve 12 rows. Sort the result set in descending sequence by invoice_due_date.
11. Write a SELECT statement that returns these columns from the Invoices table: invoice_number invoice_total
The invoice_number column The invoice_total column
payment_credit_total balance_due
Sum of the payment_total and credit_total columns
The invoice_total column minus the payment_total and credit_total columns
Return only invoices that have a balance due that’s greater than $50. Sort the result set by balance due in descending sequence. Use the LIMIT clause so the result set contains only the rows with the 5 largest balances.