..ooo Sprint F 12:44 AM T 12%. nsu blackboard com SQ Worksheet/EXAM Review Use t

Question

..ooo Sprint F 12:44 AM T 12%. nsu blackboard com SQ Worksheet/EXAM Review Use the database schema below and the descriptions of the 5 tables (vendors, Products, orders orderitems) to answer the given questions. orders Table: Stores customer orders. Each order is uniquely numbered by order num. Onderitems Table: Stores the item in each order, for every rowin Ondersthere are one or more rows Orderitems table ustomer table: stores all the customer information. 3, C 4. Products Table: contains information on each product 5. The vendors table: Stores information on the vendors who sellproduct Part 1: Retrieving Data Write queries to do the following 1. Retrieve the data for prod numin the products table. 2. Retrieve the product id,product name and product price for al products in the products return the vendor id of all vendors inthe product table,however you do not want duplicate id's listed Part 2: Sorting Data 1. Display the names of the products in the products table sorted in ascendingorder 2. Redo question 1 but sort in descending order 3. Display the product id, product name and product price sorted by product price and within the product price, sorted by product 4. Redo question 3 however the data should be sorted in descending order on the product price Part 3: Filtering Data

Explanation / Answer

Part 1:

1. select prod_num from Product;

2.select prod_id, prod_name, prod_price from Product;

3.select distinct vender_id from Product;

Part 2:

1. select Product_name from product order by ASC;

2.select Product_name from product order by DESC;

3.select prod_id, prod_name, prod_price from Product order by prod_price,prod_name;

4.select prod_id, prod_name, prod_price from Product order by prod_price DESC ,prod_name ASC;

Part 3:

1.select prod_name, prod_price from Product where prod_price>3.50;

2.select vender_id, prod_name from product where vender_id !=DLL01;

3.select prod_name, prod_price from Product where prod_price BETWEEN $5 AND $10;

4. select prod_name from Product where prod_price=NULL;

5. select cust_name from Customer where cust_emailid=NULL;

6.select prod_name, prod_price from Product where prod_price<=$5 AND vender_id=DLL01;

7.select prod_name, prod_price from Product where vender_id=DLL01 OR vender_id=BRS01;

8.select prod_name, prod_price from Product where prod_price>= 10 AND (vender_id=DLL01 OR vender_id=BRS01);

9.select prod_name, prod_price from Product where vender_id !=DLL01 AND prod_name LIKE 'fish%';

10.select prod_name, prod_price from Product where prod_name= 'bean bag';

11. select prod_id,quantity,prod_price, sum(prod_price) from Product, order where order_id= 20007;

12. select UCASE(vender_name) as vender name in upper case from product;

13. select AVG(prod_price) as average price from Product;

14. select count(cust_name) from Customer where cust_emailid !=NULL;

15. Select prod_name, prod_price from Product where prod_price = MAX(prod_price) from Product;

16.Select prod_name, prod_price from Product where prod_price = MIN(prod_price) from Product;

17. select count(order_id) from order where order_id=20005;

Part 4:

1. select vender_id ,count( product_id) as numb products from vender group by (vender_id);

2.select vender_id ,count( product_id) from vender where count( product_id)>=2 AND sum(prod_price)>=$4 group by vender_id;

3.select order_num , count(order_num) from orderitems where count(order_num)>=3 group by order_num;

3.select order_num , count(order_num) as c from orderitems group by order_num order by c;

Schema that is given is not at all clear but i have answered all of the queries in best possible way till part 4 by guessing the schema

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.