Assignment 3 below:

create table customers(

customer_name char(20) primary key,

email_id char(40) not null,

phone_num char(10) unique,

height decimal(4,3) check (height >= 2 and height

weight decimal(5,2) check (weight >= 50 and weight

age int default 30

);

create table orders(

order_id int primary key,

name1 char(20),

order_items char(100) default '',

amount decimal(8,2) default 0,

order_placed_date char(10),

foreign key(name1) references customers(customer_name)

);

insert into customers values('sam', 'sam@xyz.com', 9876543210, 5.6, 183.5, 34);

insert into customers values('tom', 'tom@xyz.com', 9998887776, 5.11, 170.5, 44);

insert into customers values('kim', 'kim@xyz.com', 4443332221, 5.8, 153.5, 38);

insert into customers values('john', 'john@xyz.com', 4447771111, 6.1, 203.5, 22);

insert into customers values('may', 'may@xyz.com', 5554326789, 6.1, 203.5, 22);

select * from customers;

insert into orders values(111, 'sam', 'bread, ketchup, potatoes, tomatoes', 15.43, '2021-02-17');

insert into orders values(222, 'tom', 'mixer, tv, sofa', 3100.80, '2020-07-26');

insert into orders values(333, 'sam', 'laptop, chair, table', 2054.78, '2020-01-04');

insert into orders values(444, 'sam', 'shirt, hoodie, xbox', 981.93, '2019-03-06');

insert into orders values(555, 'kim', 'indoor cycle, watch, iphone', 1500.22, '2020-10-10');

insert into orders values(666, 'kim', 'ipod, ipad, treadmill', 3500.22, '2019-08-21');

select * from orders;

select * from customers where weight > 170 or (height between 5.5 and 5.9) order by height desc;

select * from customers c, orders o;

select count(order_id) as 'no_of_orders', sum(amount) as 'total_expense' from orders where name = 'sam';

select max(amount) as 'max', min(amount) as 'min', avg(amount) as 'avg' from orders;

select customer_name from customers where customer_name like '_o%'or customer_name like '_i%';

select distinct name from orders where length(name) = 3 or order_items = 'ipad';

SELECT customer_name, sum(amount) FROM customers c JOIN orders o ON o.name = c.customer_name GROUP BY c.customer_name;

SELECT customer_name, avg(amount) FROM customers c JOIN orders o ON o.name = c.customer_name GROUP BY c.customer_name HAVING avg(amount) > 3000;

SELECT * FROM orders WHERE name = "Sam" UNION SELECT * FROM orders WHERE name = "Tom";

Note: This assignment is in continuation with assignment-03. Use the schema and data from assignment-03 (not 04) to complete the following tasks. 2 points/task 1. Display all the orders placed by Sam except the ones placed in 2020 (use: except). 2. Delete John's record. Explain why it will throw an error if you try to delete Sam's record. 3. Find order_id having the minimum amount (use: aggregate-function) 4. Find Sam's order_id that has his highest amount (use: nested sub-query approach) 5. Find customer names whose average spending is more than the average money spent by all the customers. In other words, find customer names having their average order's amount greater than the average of all the orders in the store. (use: having, plus nested sub-query). 6. Find customer names who have placed at-least one order. Solve it using nested-subquery approach (use IN). 7. Find customer names who have not placed any order. Solve it using nested-subquery approach (use: NOT IN). 8. Find customer-names who have some of their order-amounts greater than the ones placed by Tom. Solve it using nested-subquery approach (use: SOME). 9. Find Kim's order_ids where the order's amount is greater than all the order-amounts placed by Sam. Solve it using nested-subquery approach (use: ALL). 10. Find customer names who have placed at-least one order. Solve using correlated subquery approach (use: EXISTS)