INT2005-Lab02: SQL Review Solved

Task 1: Using MySQL Workbench to create an ER-diagram for the “classicmodels”.

1.1  Use the script named “classicmodels” to generate ER diagram (Reverse         Engineering technique).

Open the MySQL Workbench program
Select the menu “Database” => “Reverse Engineer…”.
Please follow the step by step instructions below:
 

         The example ER diagram for the classicmodels.

1.2  Re-arrange the ER diagram for readability and review the tables, columns, data         types and relationships among the tables.

Save a model named “classicmodels.mwb” (the default extension file is .mwb) and export a model as a .png file named  “classicmodels_er.png” stored in your computer.
Place your ER image (the png file) here.
 Note: The MSRP is “Manufacturer's suggested retail price” (ราคาขายปลกี แนะน าของผผู้ ลติ).  

Task 2: Using the “classicmodels” schema and write SQL statements to answer the following questions. 

 

The Syntax of SELECT statement:

Documentation: https://dev.mysql.com/doc/refman/8.0/en/select.html

Note: The MySQL error code 1064 is a syntax error. This means the reason there’s a problem is because MySQL doesn’t understand what you’re asking it to do.

 Switch to SQL Editor
- You should specify the classicmodels database before writing SQL statements using the following command:

USE db_name;

The USEstatement tells MySQL to use the named database as the default (current) database for subsequent statements. This statement requires some privilege for the database or some object within it.

 

2.1 List the product name and quantity in stock of all products that classified in the “Classic Cars” product line (กลุ่มของสนิ คา้/ผลติ ภณั ฑ์) and their buy prices are more than 80.

-- Capture the SQL statement + Result Screen and place here

2.2 List the customer name, city and country of all customers who live in the country named: Japan, Germany or Canada. Sort the results in descending order by country and ascending order by customer name.

2.3 List the order number and the total amount of sales for all orders. Name the total amount of sales column to “total_amount”.

2.4 List the order number and the total amount of sales of all orders that their total amount of sales is more than 55000. Name the total amount of sales column to “total_amount”.

2.5 List the customer name and the number of sales orders of all customers whose name start with the letter ‘D’. Name the number of sales orders column to “num_orders”.

2.6 List the customer name, the sales rep employee last name, and the check number of all customers who made the payment in year 2005. Name the sales rep employee last name column to “salesempname”.

2.7 List the manager last name (the employee who were reported to) and the number of employees of all managers. Name the manager last name and the number of employees columns to “mgrname” and “num_emp”, respectively.

2.8 Create your own question and the answered SQL statement should have SELECT,         FROM, WHERE, GROUP BY and HAVING clauses.

2.9 Create your own question and the answered SQL statement should have SELECT,         FROM 3 tables, WHERE and ORDER BY clauses.