Overview
This assignment involves querying four tables (Customer, Order, Order Line, and Product) in a MySQL database. The table contents are listed below. Instructions on how to operate MySQL and how to create tables manually are described below. The tables should be in your MySQL database prior to executing these queries.
Submission Requirements
1) Download and install a free version of MySQL
Obtain the latest version of MySQL free from the MySQL website. (https://dev.mysql.com/downloads/mysql/5.1.html (Links to an external site.))
Installing MySQL (on Windows)
The "Windows (x86)" version of MySQL 5.1 is provided in a Zip archive, which contains one file called "Setup.exe". Unzip the archive and run "Setup.exe". When the setup program begins, it will ask you whether you want a "Typical", "Complete", or "Custom" installation. I suggest choosing a "Typical" installation, since that suffices for our course, and will save you from having to answer questions that you may not be prepared to answer.
Next, you'll be asked to sign up for a MySQL.com account. This is up to you, and it's not a requirement.
After that, you'll be told that the setup wizard is finished, and you'll be offered the chance to "Configure the MySQL Server now". Make sure this checkbox is checked (it should be by default) and click "Finish".
You'll now be taken into a new wizard that will configure your installation of MySQL. What follows is a list of choices that I suggest you make as you work your way through each page of the wizard:
a. Select "Standard Configuration" and click "Next".
b. Make sure the checkboxes "Install As Windows Service", "Launch the MySQL Server automatically", and "Include Bin Directory in Windows PATH" are all checked, and use "MySQL" as the service name. Click "Next".
c. Make sure the "Modify Security Settings" checkbox is checked and that the others are not. Choose a password and enter it into the "New root password" and "Confirm" boxes. Don't forget this password! Click "Next".
d. Click "Execute" to start the configuration process. When it's done, click "Finish".
Congratulations! MySQL 5.0 is now installed on your computer and ready to use for your assignments (or whatever else).
The "server" will be running in the background whenever your computer is running, so you can connect to it anytime using the mysql command-line program (use only the command line program for this assignment).
It's possible from time to time that you may need to know what port MySQL Server is running on. By default, it runs on port 3306.
During the installation process, MySQL will ask that you designate a root password to log in to each MySQL session. Please assign a password and remember it as you will need it every time you use MySQL.
Using your MySQL password and creating a simple test database
The command-line parameters tell mysql to connect to the server using the username "root" (which was created for you during the installation process and has full rights to do anything) and to allow you to type the root password (the password for the "root" username) when the program starts. Without the --password option, you simply won't be able to connect to the server, so be sure to include this option.
When asked, type the root password you specified during installation, and you should see something like this:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.18-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
The prompt mysql> is where you type SQL commands. To verify that things are working correctly, go through the following steps to create a simple database and query it. In the text below, boldfaced text indicates something that you should type, while normal-weight text indicates something that the mysql program will print in response.
mysql> CREATE DATABASE test184;
Query OK, 1 row affected (0.00 sec)
mysql> USE test184;
Database changed
mysql> CREATE TABLE customer(
->customer_id INTEGER,
->customer_name CHAR(30),
->customer_city CHAR(20),
-> PRIMARY KEY(customer_id));
Query OK, 0 rows affected (0.16 sec)
mysql> INSERT INTO customer VALUES (1, 'Ann', 'Irvine');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO customer VALUES (2, 'Joe', 'Mission Viejo');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM customer
-> WHERE customer_city = 'Irvine';
+-------------+---------------+---------------+
| customer_id | customer_name | customer_city |
+-------------+---------------+---------------+
| 1 | Ann | Irvine |
+-------------+---------------+---------------+
1 row in set (0.00 sec)
mysql> DROP DATABASE test184;
Query OK, 1 row affected (0.09 sec)
mysql> \q
Bye
Creating assignment
2) Create and populate the following tables in your MySQL database
Ok, now you are ready to create the Pine Valley Furniture database that you will use for assignment #2. Based on the example above, use the SQL commands below to create the database and tables for this assignment.
CREATE DATABASE PineValleyFurniture;
USE PineValleyFurniture;
The Pine Valley Furniture Company has the following entities. As manager of a retail location, you need to query your database to determine certain trends and relationships in customer, product, and order activity.
Use these CREATE TABLE statements for your database.
CREATE TABLE Customer_t (
Customer_ID INTEGER,
Customer_Name CHAR (30),
Customer_Address VARCHAR (30),
City CHAR (30),
State CHAR (30),
Postal_Code VARCHAR (5),
PRIMARY KEY (Customer_ID));
CREATE TABLE Product_t (
Product_ID INTEGER,
Product_Description CHAR (30),
Product_Finish CHAR (30),
Standard_Price INTEGER,
Product_Line_ID INTEGER,
PRIMARY KEY (Product_ID));
CREATE TABLE Order_t (
Order_ID INTEGER,
Order_Date DATE,
Customer_ID INTEGER,
PRIMARY KEY (Order_ID));
CREATE TABLE Order_Line_t (
Order_ID INTEGER,
Product_ID INTEGER,
Quantity_Ordered INTEGER);
Use these INSERT statements to populate your tables.
INSERT INTO Customer_t VALUES (1, "Contemporary Casuals", "1355 S. Hines Blvd.", "Gainesville", "FL", 32601);
INSERT INTO Customer_t VALUES (2, "Value Furniture", "15145 S.W. 17th St.", "Plano", "TX", 75094);
INSERT INTO Customer_t VALUES (3, "Home Furnishings", "1900 Allard Ave.", "Albany", "NY", 12209);
INSERT INTO Customer_t VALUES (4, "Eastern Furniture", "1925 Beltline Rd.", "Carteret", "NJ", 07008);
INSERT INTO Customer_t VALUES (5, "Impressions", "5595 Westcott Ct.", "Sacramento", "CA", 94206);
INSERT INTO Customer_t VALUES (6, "Furniture Gallery", "325 Flatiron Dr.", "Boulder", "CO", 80514);
INSERT INTO Customer_t VALUES (7, "Period Furniture", "394 Rainbow Dr.", "Seattle", "WA", 97954);
INSERT INTO Customer_t VALUES (8, "California Classics", "816 Peach Rd.", "Santa Clara", "CA", 69615);
INSERT INTO Customer_t VALUES (9, "M and H Casual Furniture", "3709 First Street", "Clearwater", "FL", 34620);
INSERT INTO Customer_t VALUES (10, "Seminole Interiors", "2400 Rocky Point Dr.", "Seminole", "FL", 34646);
INSERT INTO Customer_t VALUES (11, "American Euro Lifestyles", "2424 Missouri Ave N.", "Prospect Park", "NJ", 07508);
INSERT INTO Customer_t VALUES (12, "Battle Creek Furniture", "345 Capitol Ave SW", "Battle Creek", "MI", 49015);
INSERT INTO Customer_t VALUES (13, "Heritage Furnishings", "66789 College Ave", "Carlisle", "PA", 17013);
INSERT INTO Customer_t VALUES (14, "Kaneohe Homes", "112 Kiowai St.", "Kaneohe", "HI", 96744);
INSERT INTO Customer_t VALUES (15, "Mountain Scenes", "4132 Main Street", "Ogden", "UT", 84403);
*****
INSERT INTO Product_t VALUES (1, "End Table", "Cherry", 175, 1);
INSERT INTO Product_t VALUES (2, "Coffee Table", "Natural Ash", 200, 2);
INSERT INTO Product_t VALUES (3, "Computer Desk", "Natural Ash", 375, 2);
INSERT INTO Product_t VALUES (4, "Entertainment Center", "Natural Maple", 650, 3);
INSERT INTO Product_t VALUES (5, "Writers Desk", "Cherry", 325, 1);
INSERT INTO Product_t VALUES (6, "8 Drawer Desk", "White Ash", 750, 2);
INSERT INTO Product_t VALUES (7, "Dining Table", "Natural Ash", 800, 2);
INSERT INTO Product_t VALUES (8, "Computer Desk", "Walnut", 250, 3);
*****
INSERT INTO Order_Line_t VALUES (1001, 1, 2);
INSERT INTO Order_Line_t VALUES (1001, 2, 2);
INSERT INTO Order_Line_t VALUES (1001, 4, 1);
INSERT INTO Order_Line_t VALUES (1002, 3, 5);
INSERT INTO Order_Line_t VALUES (1003, 3, 3);
INSERT INTO Order_Line_t VALUES (1004, 6, 2);
INSERT INTO Order_Line_t VALUES (1004, 8, 2);
INSERT INTO Order_Line_t VALUES (1005, 4, 4);
INSERT INTO Order_Line_t VALUES (1006, 4, 1);
INSERT INTO Order_Line_t VALUES (1006, 5, 2);
INSERT INTO Order_Line_t VALUES (1006, 7, 2);
INSERT INTO Order_Line_t VALUES (1007, 1, 3);
INSERT INTO Order_Line_t VALUES (1007, 2, 2);
INSERT INTO Order_Line_t VALUES (1008, 3, 3);
INSERT INTO Order_Line_t VALUES (1008, 8, 3);
INSERT INTO Order_Line_t VALUES (1009, 4, 2);
INSERT INTO Order_Line_t VALUES (1009, 7, 3);
INSERT INTO Order_Line_t VALUES (1010, 8, 10);
*****
INSERT INTO Order_t VALUES (1001, "2004-10-21", 1);
INSERT INTO Order_t VALUES (1002, "2004-10-21", 8);
INSERT INTO Order_t VALUES (1003, "2004-10-22", 15);
INSERT INTO Order_t VALUES (1004, "2004-10-22", 5);
INSERT INTO Order_t VALUES (1005, "2004-10-24", 3);
INSERT INTO Order_t VALUES (1006, "2004-10-24", 2);
INSERT INTO Order_t VALUES (1007, "2004-10-27", 11);
INSERT INTO Order_t VALUES (1008, "2004-10-30", 12);
INSERT INTO Order_t VALUES (1009, "2004-11-05", 4);
INSERT INTO Order_t VALUES (1010, "2004-11-05", 1);
Once all the tables are created and populated you are ready to start querying the Pine Valley Furniture database. For each question include your SQL SELECT statement and the results of the query. You can send screen output to a file using the tee command (mysql> tee c:\path\filename.txt) or copy/paste or screen capture placed into an MS Word document.
Question 3)
SELECT Standard_Price, Product_Description, Product_ID
FROM Product_t;
Standard_Price Product_Description Product_ID
--------------------- -------------------------------------------------- -----------
175.00 End Table 1
200.00 Coffee Table 2
375.00 Computer Desk 3
650.00 Entertainment Center 4
325.00 Writers Desk 5
750.00 8-Drawer Desk 6
800.00 Dining Table 7
250.00 Computer Desk 8
(8 row(s) affected)
3) List the standard price, product description, and product ID for all products in the product table
4) Which products have a standard price of less than $275? (Hint: Display the product description and the standard price)
5) What is the average standard price for all products in the products table?
6) How many different types (count) of products were ordered on order number 1004?
7) Display the order ID and the order date for orders have been placed since 10/24/2004? (Hint: This is exclusive, meaning everything after 10/24)
8) What furniture does Pine Valley carry that isn't made of Cherry? Output should show description and finish.
9) List all customers who live in warmer states (Define warmer states to be Florida, Texas, California, and Hawaii). List the customers alphabetically first by state, then by customer name. Add the attribute City (from Customer_t) as an additional column for more detail.
10) Find only states with more than one customer. List the state in which they reside and the number of customers in that state.
11) List the product finish and the average standard price for that finish for every product finish whose average standard price is less than $750. (Hint: Display product finish and price) (Hint 2: Cherry has an average price of $250)
12) List the Product ID, Description, Finish, and Standard price from the Products table for those products that have a price less than the average price for ALL products. (Hint: If the average price for all products is $10, then select all products which have price less than $10.)
13) Create a VIEW named Product_V that consists of product description, finish, and standard price. Use the Product view to list the product description, finish, and standard price for all desks and/or tables that cost more than $300.
Hint #1 -- Need to see SQL code for both the creation of the VIEW and the querying of the newly created VIEW with output)
Hint #2 - Create the VIEW that contains the data of interest, then query the entire VIEW like a table
Hint #3 - Your output will not include the entertainment center (it's neither a desk nor a table), but your query will not necessarily exclude it.
14) Create an INDEX named Furniture on the Product_Finish column of the Product_t table. Use this index to retrieve records from the product_t table.
Submit DDL for the creation of the INDEX and the SQL query on the products table that uses the index, i.e., includes the Product_Finish column in the WHERE clause.