Question

Use SQL statements to create this table and populate it with this data.

Also design and clearly explain at least ten query scenarios that may be useful on your database.

Write SQL syntax to answer each query. A query like this: select count(*) from tableOne, can

only be used as one of the ten. Strongly suggested to include some queries using Table Joins,

Aggregates, Group By, Order By, and Having. At least one query must use a view

Order Details

Order_Quantity	Order_Number	Amount_to_be_paid	Invoice_number	Date	Time	Item_Name	Custome_ID
3	101	$15	11	2018-10-05	2:00	BlueBerries	7989
4	102	$28	12	2018-10-05	4:56	Chicken Breast	8997
10	104	$30	13	2018-10-06	5:54	Pork Chop	8978
20	106	$40	14	2018-10-07	7:45	Carrots	2354
5	1025	$39	15	2018-10-08	4:15	Apples	6523
16	1027	$45	16	2018-10-06	2:31	Steak	1274
30	109	$350	17	2018-10-05	3:45	Sour cream	24656
8	105	$45	18	2018-10-07	2:12	Milk	75757
7	453	$50	19	2018-10-09	3:56	Oatmeal	45645
2	754	$10	20	2018-10-05	9:32	Soap	45671

Answer 1

CREATE TABLE `Order_Details` (

`Order_Quantity` int(11) NOT NULL,

`Order_Number` int(11) NOT NULL,

`Amount_to_be_paid` varchar(11) NOT NULL,

`Order_Number` int(11) NOT NULL,

`Invoice_number` int(11) NOT NULL auto_increment,

`Item_Name` varchar(255) NOT NULL,

`Date` DATE NOT NULL,

`Time` TIME NOT NULL,

`Custome_ID` int(11) NOT NULL,

PRIMARY KEY (`Invoice_number`)

);

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('3','101','$15','2018-10-05','2:00','BlueBerries','7989');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('4','102','$28','2018-10-05','4:56','Chicken Breast','8997');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('10','104','$30','2018-10-06','5:54','Pork Chop','8978');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('20','106','$40','2018-10-07','7:45','Carrots','7989');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('5','1025','$39','2018-10-08','4:15','Apples','7989');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('16','1027','$45','2018-10-06','2:31','Steak','7989');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('30','109','$350','2018-10-05','3:45','Sour cream','7989');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('8','105','$54','2018-10-07','2:12','Milk','7989');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('7','453','$50','2018-10-09','3:56','Oatmeal','7989');

INSERT INTO `Order_Details`(Order_Quantity,Order_Number,Amount_to_be_paid,Order_Number,Item_Name,Date,Time,Custome_ID)

VALUES ('2','754','$10','2018-10-05','9:32','Soap','7989');

select count(*) from Order_Details;

select Order_Quantity from Order_Details where Custome_ID='45671';

SELECT COUNT(*) FROM Order_Details group by Custome_ID;

select MAX(Amount_to_be_paid) from Order_Details where Custome_ID='45671';

select MIN(Amount_to_be_paid) from Order_Details where Custome_ID='45671';

select AVG(Amount_to_be_paid) from Order_Details where Custome_ID='45671';

SELECT COUNT(*) FROM Order_Details having '45671';

select * from Order_Details order by Invoice_number desc;

select sum(Amount_to_be_paid) from Order_Details where Custome_ID='45671';

delete from Order_Details where Custome_ID='45671';