Starting from:
$30
CSCI3287-Homework 3 Creating a Database Solved
This assignment will give you hands-on practice in working with MySQL and the SQL language. In this Project you will create a database and populate it with data using the scripts provided. You will use the database you create for running a variety of queries and answering a few questions.
Objectives
1. Become familiar with the SQL language & syntax for SELECT queries, DDL and DML
2. Become familiar with a tool of your choice for building and submitting queries (whether command mode or GUI.)
3. Successfully run the scripts necessary to create a sample database, verify that your database is correctly built.
4. Run SQL queries against your database to answer the assigned problems.
StepOne:Downloading and Installing MySQL
For this project assignment you will need to download and install MySQL on your computer. How you do this will depend on what type of computer you have and what Operating System it is running.
You will want to download MySQL Community Server 8.0.* The current release number changes from time to time. As of this writing, the most recent release is 8.0.17. If for some reason you don’t want to use version 8.0 – for example if you already have version 5.7 running on your computer, you can use version 5.7.x. Either version will work fine.
Do NOT download “MySQL Cluster” software -- similar name, but a VERY different DBMS product.
This page (below) contains online documentation links where you can find help with the download and installation if you need it.
The download you need can be found here: https://dev.mysql.com/downloads/mysql/
Choose the download file that matches your computer’s OS and version.
Once you have downloaded and installed MySQL, you should launch the MySQL instance so that it is running in the background on your computer.
Step Two: Choose Your Preferred Query Editor Tool
In order to create SQL queries and run them against your MySQL database, you will need a tool or a user interface through which you can create and execute queries, and then view/copy/export the answer set.
The default is the mysqld command line interface. This command line interface is installed with MySQL and is similar to using the Linux shell. If you choose to do your queries via the MySQL command line interface, you do not need to download/install any query tool.
However, managing and running queries against MySQL databases is simpler, faster and easier if you use a GUI (graphic user interface) tool. There are many available.
You can use MySQL Workbench. You have seen your instructor use MySQL Workbench in class to create an ERD (Entity Relationship Diagram) data model and then generate SQL to create tables. MySQL Workbench is free. You can use MySQL Workbench to build and submit queries against your database. It is available here: https://dev.mysql.com/downloads/workbench/ MySQL offers versions for Windows, Linux, MAC.
A great open source alternative is DBeaver. The community edition is free and it comes with versions for many different OS builds and works fine with MySQL. https://dbeaver.io/download/
Another alternative for MAC users is DataGrip. https://www.jetbrains.com/datagrip/ They offer a free 30-day trial, and a special free edition for students that you can sign up for.
Step Three: Creating the Database
Once you have selected your query editor, you need to download the “ClassicModelsCreate” script file from Moodle, unzip it and execute it. It will create your database and tables, and then load the tables with data. The script runs fine as-is without any modification.
Before you can create your database, you need to make sure that your instance of MySQL is running in the background.
Then using your query editor, you must connect to the running MySQL instance prior to running the script.
HINT: You should download and print this ERD (below) and keep it handy when you are writing your queries. It is very helpful to have table and column names in front of you when writing SQL queries.
MySQL Sample Database Schema
The MySQL sample “Classic Models” database schema consists of the following tables:
Customers
stores customer data.
Products
stores a list of scale model cars.
ProductLines
stores a list of product line categories.
Orders
stores sales orders placed by customers.
OrderDetails
stores sales order line items for each sales order.
Payments
stores payments made by customers based on their accounts
Employees
stores all employee information as well as the organization structure such as who reports to whom.
Offices
stores sales office data.
Note: GUI Query Editor Users: After you run some queries to create your tables, you might expect the new tables to immediately appear under the “object explorer” on the left side of your GUI query editor. They will eventually show up, but to see them appear right away, you will need to click on the “refresh” icon (if your tool has one.) If this icon does not appear, then click somewhere within your “object explorer” and the newly created tables should appear. (Depends on your query tool…)
If you are using the command line editor, you can enter SHOW TABLES and MySQL will show you all the tables in your database.
After running the unzipped script file to create and load your database, you should run the following “Verify” script to ensure that your database is built correctly.
Verify Script:
SELECT table_schema, table_name, table_rows
FROM information_schema.tables
WHERE TABLE_SCHEMA LIKE 'classic%';
You should see the following tables and row counts for your Classic Models database.
table_schema
table_name
table_rows
classicmodels
customers
122
classicmodels
employees
23
classicmodels
offices
7
classicmodels
orderdetails
2996
classicmodels
orders
326
classicmodels
payments
273
classicmodels
productlines
7
classicmodels
products
110
Query Problems
For this project you must create and execute queries against the ClassicModels database to fulfill the requirements listed below. For each query requirement, as a “hint”, the number of rows to expect in your answer set is listed in parentheses.
1. List the names of the cities in alphabetical order where Classic Models has offices. (7)
2. List the EmployeeNumber, LastName, FirstName, Extension for all employees working out of the Paris
office. (5)
3. List the ProductCode, ProductName, ProductVendor, QuantityInStock and ProductLine for all products with a QuantityInStock between 100 and 1000. (10)
4. (Use a SUBQUERY) List the ProductCode, ProductName, ProductVendor, BuyPrice and MSRP for the least expensive (lowest MSRP) product sold by ClassicModels. (“MSRP” is the Manufacturer’s Suggested
Retail Price.) (1)
5. What is the ProductName and Profit of the product that has the highest profit (profit = MSRP minus BuyPrice). (1)
6. List the country and the number of customers from that country for all countries having more than ten customers. List the countries sorted in descending order from highest to lowest number of customers. Title the column heading for the count of customers as “Customers”. (3)
7. List the ProductCode, ProductName, and number of orders for the product with the most orders. Title the column heading for the count of orders as “OrderCount”. (1)
8. List the EmployeeNumber, Firstname + Lastname (concatenated into one column in the answer set, separated by a blank and referred to as ‘name’) for all the employees reporting to Diane Murphy or William Patterson. (5)
9. List the EmployeeNumber, LastName, FirstName of the president of the company (the one employee with no boss.) (1)
10. List the ProductName for all products in the “Classic Cars” product line from the 1970’s. (7)
11. List the month name and the total number of orders for the month in 2005 in which ClassicModels customers placed the most orders. (1)
12. List the firstname, lastname of employees who are Sales Reps who have no assigned customers. (2)
13. List the customername of customers from Germany with no orders. (10)
14. List the customername and total quantity of products ordered for customers who have ordered more than 1700 products across all their orders. (6)
15. Create a NEW table named “TopCustomers” with three columns: CustomerNumber (integer),
ContactDate (DATE) and OrderTotal (a decimal number with 9 digits in total having two decimal places).
None of these columns can be NULL. Include a PRIMARY KEY constraint named “TopCustomer_PK” on
CustomerNumber. (no answer set)
16. Populate the new table “TopCustomers” with the CustomerNumber, today’s date, and the total value of all their orders (PriceEach * quantityOrdered) for those customers whose order total value is greater than $140,000. (inserted 10 rows, no answer set)
17. List the contents of the TopCustomers table in descending OrderTotal sequence. (10)
18. Add a new column to the TopCustomers table called OrderCount (integer). (No answer set)
19. Update the Top Customers table, setting the OrderCount column to a random number (from 0 to 18). (Should update 10 rows) HINT: use the RAND() and FLOOR() functions.
20. List the contents of the TopCustomers table in descending OrderCount sequence. (10)
21. Drop the TopCustomers table. (no answer set)
Objectives
1. Become familiar with the SQL language & syntax for SELECT queries, DDL and DML
2. Become familiar with a tool of your choice for building and submitting queries (whether command mode or GUI.)
3. Successfully run the scripts necessary to create a sample database, verify that your database is correctly built.
4. Run SQL queries against your database to answer the assigned problems.
StepOne:Downloading and Installing MySQL
For this project assignment you will need to download and install MySQL on your computer. How you do this will depend on what type of computer you have and what Operating System it is running.
You will want to download MySQL Community Server 8.0.* The current release number changes from time to time. As of this writing, the most recent release is 8.0.17. If for some reason you don’t want to use version 8.0 – for example if you already have version 5.7 running on your computer, you can use version 5.7.x. Either version will work fine.
Do NOT download “MySQL Cluster” software -- similar name, but a VERY different DBMS product.
This page (below) contains online documentation links where you can find help with the download and installation if you need it.
The download you need can be found here: https://dev.mysql.com/downloads/mysql/
Choose the download file that matches your computer’s OS and version.
Once you have downloaded and installed MySQL, you should launch the MySQL instance so that it is running in the background on your computer.
Step Two: Choose Your Preferred Query Editor Tool
In order to create SQL queries and run them against your MySQL database, you will need a tool or a user interface through which you can create and execute queries, and then view/copy/export the answer set.
The default is the mysqld command line interface. This command line interface is installed with MySQL and is similar to using the Linux shell. If you choose to do your queries via the MySQL command line interface, you do not need to download/install any query tool.
However, managing and running queries against MySQL databases is simpler, faster and easier if you use a GUI (graphic user interface) tool. There are many available.
You can use MySQL Workbench. You have seen your instructor use MySQL Workbench in class to create an ERD (Entity Relationship Diagram) data model and then generate SQL to create tables. MySQL Workbench is free. You can use MySQL Workbench to build and submit queries against your database. It is available here: https://dev.mysql.com/downloads/workbench/ MySQL offers versions for Windows, Linux, MAC.
A great open source alternative is DBeaver. The community edition is free and it comes with versions for many different OS builds and works fine with MySQL. https://dbeaver.io/download/
Another alternative for MAC users is DataGrip. https://www.jetbrains.com/datagrip/ They offer a free 30-day trial, and a special free edition for students that you can sign up for.
Step Three: Creating the Database
Once you have selected your query editor, you need to download the “ClassicModelsCreate” script file from Moodle, unzip it and execute it. It will create your database and tables, and then load the tables with data. The script runs fine as-is without any modification.
Before you can create your database, you need to make sure that your instance of MySQL is running in the background.
Then using your query editor, you must connect to the running MySQL instance prior to running the script.
HINT: You should download and print this ERD (below) and keep it handy when you are writing your queries. It is very helpful to have table and column names in front of you when writing SQL queries.
MySQL Sample Database Schema
The MySQL sample “Classic Models” database schema consists of the following tables:
Customers
stores customer data.
Products
stores a list of scale model cars.
ProductLines
stores a list of product line categories.
Orders
stores sales orders placed by customers.
OrderDetails
stores sales order line items for each sales order.
Payments
stores payments made by customers based on their accounts
Employees
stores all employee information as well as the organization structure such as who reports to whom.
Offices
stores sales office data.
Note: GUI Query Editor Users: After you run some queries to create your tables, you might expect the new tables to immediately appear under the “object explorer” on the left side of your GUI query editor. They will eventually show up, but to see them appear right away, you will need to click on the “refresh” icon (if your tool has one.) If this icon does not appear, then click somewhere within your “object explorer” and the newly created tables should appear. (Depends on your query tool…)
If you are using the command line editor, you can enter SHOW TABLES and MySQL will show you all the tables in your database.
After running the unzipped script file to create and load your database, you should run the following “Verify” script to ensure that your database is built correctly.
Verify Script:
SELECT table_schema, table_name, table_rows
FROM information_schema.tables
WHERE TABLE_SCHEMA LIKE 'classic%';
You should see the following tables and row counts for your Classic Models database.
table_schema
table_name
table_rows
classicmodels
customers
122
classicmodels
employees
23
classicmodels
offices
7
classicmodels
orderdetails
2996
classicmodels
orders
326
classicmodels
payments
273
classicmodels
productlines
7
classicmodels
products
110
Query Problems
For this project you must create and execute queries against the ClassicModels database to fulfill the requirements listed below. For each query requirement, as a “hint”, the number of rows to expect in your answer set is listed in parentheses.
1. List the names of the cities in alphabetical order where Classic Models has offices. (7)
2. List the EmployeeNumber, LastName, FirstName, Extension for all employees working out of the Paris
office. (5)
3. List the ProductCode, ProductName, ProductVendor, QuantityInStock and ProductLine for all products with a QuantityInStock between 100 and 1000. (10)
4. (Use a SUBQUERY) List the ProductCode, ProductName, ProductVendor, BuyPrice and MSRP for the least expensive (lowest MSRP) product sold by ClassicModels. (“MSRP” is the Manufacturer’s Suggested
Retail Price.) (1)
5. What is the ProductName and Profit of the product that has the highest profit (profit = MSRP minus BuyPrice). (1)
6. List the country and the number of customers from that country for all countries having more than ten customers. List the countries sorted in descending order from highest to lowest number of customers. Title the column heading for the count of customers as “Customers”. (3)
7. List the ProductCode, ProductName, and number of orders for the product with the most orders. Title the column heading for the count of orders as “OrderCount”. (1)
8. List the EmployeeNumber, Firstname + Lastname (concatenated into one column in the answer set, separated by a blank and referred to as ‘name’) for all the employees reporting to Diane Murphy or William Patterson. (5)
9. List the EmployeeNumber, LastName, FirstName of the president of the company (the one employee with no boss.) (1)
10. List the ProductName for all products in the “Classic Cars” product line from the 1970’s. (7)
11. List the month name and the total number of orders for the month in 2005 in which ClassicModels customers placed the most orders. (1)
12. List the firstname, lastname of employees who are Sales Reps who have no assigned customers. (2)
13. List the customername of customers from Germany with no orders. (10)
14. List the customername and total quantity of products ordered for customers who have ordered more than 1700 products across all their orders. (6)
15. Create a NEW table named “TopCustomers” with three columns: CustomerNumber (integer),
ContactDate (DATE) and OrderTotal (a decimal number with 9 digits in total having two decimal places).
None of these columns can be NULL. Include a PRIMARY KEY constraint named “TopCustomer_PK” on
CustomerNumber. (no answer set)
16. Populate the new table “TopCustomers” with the CustomerNumber, today’s date, and the total value of all their orders (PriceEach * quantityOrdered) for those customers whose order total value is greater than $140,000. (inserted 10 rows, no answer set)
17. List the contents of the TopCustomers table in descending OrderTotal sequence. (10)
18. Add a new column to the TopCustomers table called OrderCount (integer). (No answer set)
19. Update the Top Customers table, setting the OrderCount column to a random number (from 0 to 18). (Should update 10 rows) HINT: use the RAND() and FLOOR() functions.
20. List the contents of the TopCustomers table in descending OrderCount sequence. (10)
21. Drop the TopCustomers table. (no answer set)
1 file (218.9KB)
