DATA BASE QUESTION! Create SQL QUERIES using the table on the following page. Po

Question

DATA BASE QUESTION!

Create SQL QUERIES using the table on the following page. Points will not be awarded unless queries are provided. I am not looking for query results, simply the query.

1. From the items_ordered table, select a list of all items purchased for customerid 10101. Display the customerid, item, and price for this customer.

2. Select all columns from the items_ordered table for all customers who purchased a tent.

3. Select the average price of all the items ordered that were purchased in the month of January.

4. From the items_ordered table, write a query that would display the total number each item purchased, grouped by item.

5. Write a query that provides a list of customer IDs, items and quantities for all customers who purchased more than one item.

Table: items_ordered
customerid   order_date   item   quantity   price
10330   30-Jun-99   Pogo stick   1   28
10101   30-Jun-99   Raft   1   58
10298   1-Jul-99   Skateboard   1   33
10101   1-Jul-99   Life Vest   4   125
10299   6-Jul-99   Parachute   1   1250
10339   27-Jul-99   Umbrella   1   4.5
10449   13-Aug-99   Unicycle   1   180.79
10439   14-Aug-99   Ski Poles   2   25.5
10101   18-Aug-99   Rain Coat   1   18.3
10449   1-Sep-99   Snow Shoes   1   45
10439   18-Sep-99   Tent   1   88
10298   19-Sep-99   Lantern   2   29
10410   28-Oct-99   Sleeping Bag   1   89.22
10438   1-Nov-99   Umbrella   1   6.75
10438   2-Nov-99   Pillow   1   8.5
10298   1-Dec-99   Helmet   1   22
10449   15-Dec-99   Bicycle   1   380.5
10449   22-Dec-99   Canoe   1   280
10101   30-Dec-99   Hoola Hoop   3   14.75
10330   1-Jan-00   Flashlight   4   28
10101   2-Jan-00   Lantern   1   16
10299   18-Jan-00   Inflatable Mattress   1   38
10438   18-Jan-00   Tent   1   79.99
10413   19-Jan-00   Lawnchair   4   32
10410   30-Jan-00   Unicycle   1   192.5
10315   2-Feb-00   Compass   1   8
10449   29-Feb-00   Flashlight   1   4.5
10101   8-Mar-00   Sleeping Bag   2   88.7
10298   18-Mar-00   Pocket Knife   1   22.38
10449   19-Mar-00   Canoe paddle   2   40
10298   1-Apr-00   Ear Muffs   1   12.5
10330   19-Apr-00   Shovel   1   16.75

(Introduction of SQL: https://www.youtube.com/watch?v=Q61k9Gpl-PA&feature=youtu.be) may useful....

Explanation / Answer

First Create table:

create table items_ordered(
customerid int,
order_date date,
item varchar(50),
quantity int,
price int);

1. From the items_ordered table, select a list of all items purchased for customerid 10101. Display the customerid, item, and price for this customer.

select customerid||'~'||item||'~'||price from items_ordered where customerid='10101';

2.Select all columns from the items_ordered table for all customers who purchased a tent.

Query1:
select * from items_ordered where item='Tent';
Query2:
select customerid||'~'||item||'~'||price||'~'||order_date||'~'||quantity from items_ordered where item='Tent';

3.Select the average price of all the items ordered that were purchased in the month of January.
select avg(price)||'~'||item from items_ordered where to_char(order_date,'MON'='JAN' group by item;

4. From the items_ordered table, write a query that would display the total number each item purchased, grouped by item.

select count(item)||'~'||item from items_ordered group by item;

5. Write a query that provides a list of customer IDs, items and quantities for all customers who purchased more than one item.

select customerid||'~'||item||'~'||price||'~'||order_date||'~'||quantity from items_ordered where quantity>1;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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