Question

Save your select statements as a script. Place the semicolon at the end of each SQL statement.

Please number your select statements from 1 to 8 in your script and comment out each number. Include your name and student number as a comment at the top of your script.

The name of the script has to be Assignment1_JohnSmith. Instead of JohnSmith use your First Name and Last Name. Upload your script trough Blackboard.

Use SQL Developer to create the My Guitar Shop database, to review the tables in this database, and to enter SQL statements and run them against this database.

Use SQL Developer to create the My Guitar Shop database

1. Start SQL Developer.
2. Connect as system/system.
3. Open the script file named create_mgs_user.sql from Blackboard MGS folder.
4. Execute the entire script by clicking the Run Script button in the code editor toolbar or by pressing F5. When you do, the Output window displays messages that indicate whether the script executed successfully. You must be connected as the system user for this script to execute successfully. If you get an error the first time you run this script, run it again.
5. Open the script file named create_mgs_tables.sql from the same folder.
6. Execute the entire script by clicking the Run Script button in the code editor toolbar or by pressing F5. When you do, the Output window displays messages that indicate whether the script executed successfully. If you get an error the first time you run this script, run it again.

Use SQL Developer to review the My Guitar Shop database

7. Create a connection named mgs for the user named mgs. The password for this user should be “mgs”.
8. In the Connections window, expand the node for the connection named mgs so you can see all of the database objects it contains. If this connection isn’t displayed in the Connections window, you may need to click on the Refresh button to display it.

Write the following SQL Statements:

1. Write a SELECT statement that returns one row for each customer that has orders with these columns:

The email_address from the Customers table

A count of the number of orders

The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)

Return only those rows where the customer has more than 1 order.

Save your select statements as a script. Place the semicolon at the end of each SQL statement.

Please number your select statements from 1 to 8 in your script and comment out each number. Include your name and student number as a comment at the top of your script.

The name of the script has to be Assignment1_JohnSmith. Instead of JohnSmith use your First Name and Last Name. Upload your script trough Blackboard.

Use SQL Developer to create the My Guitar Shop database, to review the tables in this database, and to enter SQL statements and run them against this database.

Use SQL Developer to create the My Guitar Shop database

1. Start SQL Developer.
2. Connect as system/system.
3. Open the script file named create_mgs_user.sql from Blackboard MGS folder.
4. Execute the entire script by clicking the Run Script button in the code editor toolbar or by pressing F5. When you do, the Output window displays messages that indicate whether the script executed successfully. You must be connected as the system user for this script to execute successfully. If you get an error the first time you run this script, run it again.
5. Open the script file named create_mgs_tables.sql from the same folder.
6. Execute the entire script by clicking the Run Script button in the code editor toolbar or by pressing F5. When you do, the Output window displays messages that indicate whether the script executed successfully. If you get an error the first time you run this script, run it again.

Use SQL Developer to review the My Guitar Shop database

7. Create a connection named mgs for the user named mgs. The password for this user should be “mgs”.
8. In the Connections window, expand the node for the connection named mgs so you can see all of the database objects it contains. If this connection isn’t displayed in the Connections window, you may need to click on the Refresh button to display it.

Write the following SQL Statements:

1. Write a SELECT statement that returns one row for each customer that has orders with these columns:

The email_address from the Customers table

A count of the number of orders

The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)

Return only those rows where the customer has more than 1 order.

Answer 1

1. Write a SELECT statement that returns one row for each customer that has orders with these columns:

• The email_address from the Customers table
• A count of the number of orders
• The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)
• Return only those rows where the customer has more than 1 order.

Answer:

SELECT email_address as Email_Address, count(orders)as No_Orders, ((price - discount_amount) * quantity) as Total_amount from Customer where orders > 1 ;