Question

data files:

customer

INSERT INTO CUSTOMER_TABLE VALUES (

1,'Contemporary Furnitures','1355 S. Twilight Blvd.','Gainesville','FL','32601-2871'

);

INSERT INTO CUSTOMER_TABLE VALUES (

2,'Budget Furnitures','15145 S.W. 20th St.','Plano','TX','75094-7743'

);

INSERT INTO CUSTOMER_TABLE VALUES (

3,'Home Furnishings','1900 Dillard Ave.','Albany','NY','12209-1125'

);

INSERT INTO CUSTOMER_TABLE VALUES (

4,'Western Furnitures','1925 Beltline Rd.','Carteret','TX','07008-3188'

);

INSERT INTO CUSTOMER_TABLE VALUES (

5,'Impressionable Furnitures','5585 Westcott Ct.','San Jose','CA','94206-4056'

);

INSERT INTO CUSTOMER_TABLE VALUES (

6,'Furniture Galleria','325 Flatiron Dr.','Boulder','CO','80514-4432'

);

INSERT INTO CUSTOMER_TABLE VALUES (

7,'Perigold Furnitures','394 Arrow Dr.','Seattle','WA','97954-5589'

);

INSERT INTO CUSTOMER_TABLE VALUES (

8,'California Classics','816 Beach Rd.','Santa Ana','CA','96915-7754'

);

INSERT INTO CUSTOMER_TABLE VALUES (

9,'Casual Furnitures','3709 First St.','Clearwater','CA','34620-2314'

);

INSERT INTO CUSTOMER_TABLE VALUES (

10,'Seminole Interiors','2400 Rocky Rd.','Seminole','FL','34646-4423'

);

INSERT INTO CUSTOMER_TABLE VALUES (

11,'American Lifestyles','2434 Missouri Ave. N','Prospect Park','NJ','07508-5621'

);

INSERT INTO CUSTOMER_TABLE VALUES (

12,'Battle Creek Home Store','345 Capitol Ave. SW','Battle Creek','MI','49015-3401'

);

INSERT INTO CUSTOMER_TABLE VALUES (

13,'Heritage Furnitures','66789 College Rd.','Carlisle','PA','17013-8834'

);

INSERT INTO CUSTOMER_TABLE VALUES (

14,'Modern Homes','112 Kiowai St.','Waikiki','HI','96744-2537'

);

INSERT INTO CUSTOMER_TABLE VALUES (

15,'Mountain Views','5132 Main Street','Ogden','UT','84403-4432'

);

product

INSERT INTO PRODUCT_TABLE VALUES (

1,'Sofa Bed','Mahogany',174.99,4

);

INSERT INTO PRODUCT_TABLE VALUES (

2,'Coffee Table','Natural Ash',200.99,2

);

INSERT INTO PRODUCT_TABLE VALUES (

3,'Computer Desk','Natural Ash',374.99,2

);

INSERT INTO PRODUCT_TABLE VALUES (

4,'Entertainment Center','Natural Maple',650.00,3

);

INSERT INTO PRODUCT_TABLE VALUES (

5,'Writers Desk','Taupe',324.98,1

);

INSERT INTO PRODUCT_TABLE VALUES (

6,'TV Stand','Dark Wood',750.00,2

);

INSERT INTO PRODUCT_TABLE VALUES (

7,'Dining Table','Natural Ash',800.00,2

);

INSERT INTO PRODUCT_TABLE VALUES (

8,'Computer Desk','Walnut',251.25,3

);

INSERT INTO PRODUCT_TABLE VALUES (

9,'Patio Table','Walnut',151.25,3

);

INSERT INTO PRODUCT_TABLE VALUES (

10,'Side Table','Walnut',151.25,3

);

INSERT INTO PRODUCT_TABLE VALUES (

11,'End Table','Chestnut',200.25,3

);

orderline

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11010,1,2

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11010,2,2

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11010,4,2

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11020,3,8

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11030,3,3

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11040,6,2

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11040,8,8

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11050,4,5

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11060,4,3

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11060,5,2

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11060,7,2

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11070,1,3

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11070,2,2

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11080,3,3

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11080,8,3

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11090,4,2

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11090,7,3

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11100,8,10

);

INSERT INTO ORDERLINE_TABLE

(OrderID, ProductID, OrderQuantity)

VALUES (

11110,8,20

);

OrderTable

INSERT INTO ORDER_TABLE VALUES (

11010,'10/11/2013',6

);

INSERT INTO ORDER_TABLE VALUES (

11020,'10/21/2013',8

);

INSERT INTO ORDER_TABLE VALUES (

11030,'10/12/2013',15

);

INSERT INTO ORDER_TABLE VALUES (

11040,'10/19/2016',5

);

INSERT INTO ORDER_TABLE VALUES (

11050,'10/14/2017',3

);

INSERT INTO ORDER_TABLE VALUES (

11060,'10/24/2014',2

);

INSERT INTO ORDER_TABLE VALUES (

11070,'10/27/2015',11

);

INSERT INTO ORDER_TABLE VALUES (

11080,'10/30/2018',12

);

INSERT INTO ORDER_TABLE VALUES (

11090,'11/6/2016',4

);

INSERT INTO ORDER_TABLE VALUES (

11100,'11/5/2016',5

);

INSERT INTO ORDER_TABLE VALUES (

11110,'11/5/2017',7

);

1. Add a new field named “Secondary Phone” in CUSTOMER table.

2. Provide a list of the product name, product ID, and unit price for all of the products in the Product table, sorted in alphabetical order by product name. (5pts)m

3. What is the full address of the customer named Modern Homes? That includes the name, street, city, state, and zip code.

4. What is the standard price and standard price if increased by 16% for every product? You will need to include identifying information, such as the ProductID and product description too. Be sure that your column headings are informative.

5. What is the average standard price for the products with a product finish of Natural Ash?

6. Provide a listing of all of the products that includes the difference between its standard price and the overall average standard price of all products. Be sure to include product identifying information, the standard price, the average standard price, and the difference. Be sure the column headings are informative.

Answer 1

Here we have the answer for above queries:

1.Ans:To add new column in table use alter command.

Alter table CUSTOMER_TABLE ADD SecondaryPhone number(20);

2.Ans:Use orderby for sorting purpose.

Select productname,productID,unitprice from PRODUCT_TABLE

order by productname;

3.Ans:To find Full address for Modern Homes we have to use where clause

Select customername,street,city,state,zipcode from CUSTOMER_TABLE where customername="Modern Homes";

4.Ans:To Find 16% increment in standard price we can perform algebric operation directly on column.

select productid,productname,standardprice,standardprice*1.16 As "standardprice_Incre" from PRODUCT_TABLE;

5.Ans: To calculate the average we have to use group function as AVG on standardprice.

select AVG(standardprice) from PRODUCT_TABLE where productfinish="Natural Ash";

6.Ans: We have to use alias for table here

select(t.standardprice - a.priceavg) AS "Difference"

from  PRODUCT_TABLE t CROSS JOIN

(select AVG (standardprice)) As "priceavg"

from PRODUCT_TABLE)a;