Need help with problems 11-15

Note: This assignment is in continuation with assignment-03. Use the schema and data from assignment-03 (not 04) to complete the following tasks.

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";

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). 11. Find the email-ids of the customers who have placed at-least one order of amount greater than $3000. Solve using correlated subquery approach (use: EXISTS). 12. Find customer names who have not placed any order. Solve using correlated subquery approach (use: NOT EXISTS). 13. Find customer names who have placed exactly one order. Solve using correlated subquery approach (use: UNIQUE). // 'unique' is not supported in oracle, so ignore the error. 14. Find customer names who have placed more than one order. Solve using correlated subquery approach (use: NOT UNIQUE). // 'unique' is not supported in oracle, so ignore the error. 15. With clause Find email_id, order_total and order_datetime of customers having age >35. a. Here, use the With-clause to write a select query where customers' age >35. Store this result in temp table. b. Join the above temp table with orders table to retrieve the required data