Assn2_Example
.docx
keyboard_arrow_up
School
Virginia Tech *
*We aren’t endorsed by this school
Course
5504
Subject
Industrial Engineering
Date
Apr 3, 2024
Type
docx
Pages
6
Uploaded by GeneralPheasant4125
MySQL Workbench Installation
Question 3)
Execute:
> SELECT Standard_Price, Product_Description, Product_ID
FROM Product_t
+ ------------------- + ------------------------ + --------------- +
| Standard_Price | Product_Description | Product_ID |
+ ------------------- + ------------------------ + --------------- +
| 175 | End Table | 1 |
| 200 | Coffee Table | 2 |
| 375 | Computer Desk | 3 |
| 650 | Entertainment Center | 4 |
| 325 | Writers Desk | 5 |
| 750 | 8 Drawer Desk | 6 |
| 800 | Dining Table | 7 |
| 250 | Computer Desk | 8 |
| NULL | NULL | NULL |
+ ------------------- + ------------------------ + --------------- +
9 rows
4) Which products have a standard price of less than $275? (Hint: Display the product description and the standard price)
(1 point)
Execute:
> SELECT Standard_Price, Product_Description FROM Product_t where Standard_Price < 275
+ ------------------- + ------------------------ +
| Standard_Price | Product_Description |
+ ------------------- + ------------------------ +
| 175 | End Table |
| 200 | Coffee Table |
| 250 | Computer Desk |
+ ------------------- + ------------------------ +
3 rows
5) What is the average standard price for all products in the products table?
(1 point)
Execute:
> SELECT avg(Standard_Price) FROM Product_t
+ ------------------------ +
| avg(Standard_Price) |
+ ------------------------ +
| 440.6250 |
+ ------------------------ +
1 rows
6) How many different types (count) of products were ordered on order number 1004?
(1 point)
Execute:
> SELECT count(*) from order_t o join order_line_t l on l.Order_ID = o.Order_ID where o.Order_ID = 1004
+ ------------- +
| count(*) |
+ ------------- +
| 2 |
+ ------------- +
1 rows
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)
(1 point)
Execute:
> SELECT o.Order_ID, o.Order_Date from order_t o where o.Order_Date > '2004-
10-24'
+ ------------- + --------------- +
| Order_ID | Order_Date |
+ ------------- + --------------- +
| 1007 | 2004-10-27 |
| 1008 | 2004-10-30 |
| 1009 | 2004-11-05 |
| 1010 | 2004-11-05 |
| NULL | NULL |
+ ------------- + --------------- +
5 rows
8) What furniture does Pine Valley carry that isn’t made of Cherry? Output should show description and finish.
(1 point)
Execute:
> SELECT p.Product_Description, p.Product_Finish from product_t p where p.Product_Finish <> 'Cherry'
+ ------------------------ + ------------------- +
| Product_Description | Product_Finish |
+ ------------------------ + ------------------- +
| Coffee Table | Natural Ash |
| Computer Desk | Natural Ash |
| Entertainment Center | Natural Maple |
| 8 Drawer Desk | White Ash |
| Dining Table | Natural Ash |
| Computer Desk | Walnut |
+ ------------------------ + ------------------- +
6 rows
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.
(1 point)
Execute:
> SELECT c.State, c.Customer_Name, c.City from customer_t c where c.State in ('FL', 'TX', 'CA', 'HI' ) order by c.State, c.Customer_Name
+ ---------- + ------------------ + --------- +
| State | Customer_Name | City |
+ ---------- + ------------------ + --------- +
| CA | California Classics | Santa Clara |
| CA | Impressions | Sacramento |
| FL | Contemporary Casuals | Gainesville |
| FL | M and H Casual Furniture | Clearwater |
| FL | Seminole Interiors | Seminole |
| HI | Kaneohe Homes | Kaneohe |
| TX | Value Furniture | Plano |
+ ---------- + ------------------ + --------- +
7 rows
10) Find only states with more than one customer. List the state in which they reside and the number of customers in that state.
(1 point)
Execute:
> select c.State, count(*) as Cust_Count from customer_t c group by c.State having count(*) > 1
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help