DBMS Lab Cycle 3 (customer,item,sale)

Create the tables given below and execute the queries:
Customer(Cust id : integer, cust_name: string)
Item(item_id: integer, item_name: string, price: integer)
Sale(bill_no: integer, bill_date: date, cust_id: integer, item_id: integer, qty_sold: integer)  

CREATE TABLE customer(
cust_id INTEGER NOT NULL,
cust_name VARCHAR2(100) NOT NULL,
PRIMARY KEY(cust_id)
);

INSERT INTO customer
VALUES(501,'ARUN JOJI');

INSERT INTO customer
VALUES(502,'CHRISTIAN CHACKO');

INSERT INTO customer
VALUES(503,'DENNY JOSE');

INSERT INTO customer
VALUES(504,'FAZIL HABEEB');

INSERT INTO customer
VALUES(505,'JITHU SEBASTIAN');

INSERT INTO customer
VALUES(506,'KISHORE THOMAS');

INSERT INTO customer
VALUES(507,'LEBIN KURIAKOSE');

INSERT INTO customer
VALUES(508,'MANUAL CHERIAN');

INSERT INTO customer
VALUES(509,'NIRMAL THOMAS');

INSERT INTO customer
VALUES(510,'JEETHU MATHEW');



CREATE TABLE item(
item_id INTEGER NOT NULL,
item_name VARCHAR2(50) NOT NULL,
price INTEGER NOT NULL,
PRIMARY KEY(item_id)
);

INSERT INTO item
VALUES(601,'REDMI NOTE 4',12999);

INSERT INTO item
VALUES(602,'LG Q6',14999);

INSERT INTO item
VALUES(603,'MOTO G5 PLAY',16999);

INSERT INTO item
VALUES(604,'SAMSUNG GALAXY S8',75000);

INSERT INTO item
VALUES(605,'ONE PLUS 3t',25999);

INSERT INTO item
VALUES(606,'ONE PLUS 5',32999);

INSERT INTO item
VALUES(607,'MI A1',14999);

INSERT INTO item
VALUES(608,'HONOR 8 PRO',29999);

INSERT INTO item
VALUES(609,'APPLE IPHONE 7',46999);

INSERT INTO item
VALUES(610,'NOKIA 3',9380);

CREATE TABLE sales(
bill_no INTEGER,
bill_date date,
cust_id INTEGER REFERENCES customer(cust_id),
item_id INTEGER REFERENCES item(item_id),
qty_sold INTEGER,
PRIMARY KEY(bill_no)
);

INSERT INTO sales
VALUES(701,'26-JAN-16',501,609,6);

INSERT INTO sales
VALUES(702,'02-FEB-16',506,608,9);

INSERT INTO sales
VALUES(703,'17-MAR-16',505,610,4);

INSERT INTO sales
VALUES(704,'25-DEC-16',507,601,5);

INSERT INTO sales
VALUES(705,'01-JAN-17',501,605,2);

INSERT INTO sales
VALUES(706,'07-JUL-17',508,602,6);

INSERT INTO sales
VALUES(707,'15-AUG-17',507,606,3);

INSERT INTO sales
VALUES(708,'08-SEP-17',510,608,5);

INSERT INTO sales
VALUES(709,'12-SEP-17',509,607,7);

INSERT INTO sales
VALUES(710,'19-SEP-17',503,601,1);

--> List all the bills for the current date with the customer names and item numbers.

SELECT s.bill_no,s.bill_date,s.cust_id,s.item_id,s.qty_sold,c.cust_name,i.item_name
FROM sales s
LEFT OUTER JOIN item i ON i.item_id=s.item_id
LEFT OUTER JOIN customer c ON c.cust_id=s.cust_id
where s.bill_date = to_date(sysdate);

--> List the total Bill details with the quantity sold, price of the item and the final amount.

SELECT
s.bill_no,
s.qty_sold,
i.price,
s.qty_sold * i.price "Total Price"
FROM sales s,item i
where s.item_id=i.item_id
order by s.bill_no asc;

--> List the details of the customer who have bought a product which has a price>2000

SELECT unique
c.cust_id,
c.cust_name
FROM customer c,sales s,item i
where i.price>20000 AND
s.item_id=i.item_id AND
s.cust_id=c.cust_id;

--> Give a count of how many products have been bought by each customer.

select c.cust_name,s.cust_id,count(s.cust_id) "No.of prods bought"
from sales s,customer c
where s.cust_id=c.cust_id
group by s.cust_id,cust_name
order by s.cust_id;

--> Give a list of products bought by a customer having cust_id as 505.

select i.item_id,i.item_name
from item i
left outer join sales s on s.item_id = i.item_id
where s.cust_id=505;

--> List the item details which are sold as of today.

select i.item_id,i.item_name,i.price
from item i
left outer join sales s on s.item_id = i.item_id
where s.bill_date=to_date(sysdate);