FOR FREE CONTENT

CREATE TABLES AND INSERT VALUES

1. CREATE TABLE CUSTOMER:

(CID VARCHAR2(4) CONSTRAINT PK_CUSTOMER PRIMARY KEY,

CNAME VARCHAR2(20) NOT NULL,

CITY VARCHAR2(10) NOT NULL,

DISCOUNT NUMBER (5,2) DEFAULT 0,

CHECK (CID LIKE 'C%')

);

 1. DESC CUSTOMER;

1.INSERT INTO "CUSTOMER" TABLE :

INSERT INTO CUSTOMER(CID,CNAME,CITY,DISCOUNT)

VALUES('C001','ABHISHEK','KOLKATA',10);

INSERT INTO CUSTOMER(CID,CNAME,CITY,DISCOUNT)

VALUES('C002','BHUVAN','DELHI',10);

INSERT INTO CUSTOMER(CID,CNAME,CITY,DISCOUNT)

VALUES('C003','DAVID','KOLKATA',11);

INSERT INTO CUSTOMER(CID,CNAME,CITY,DISCOUNT)

VALUES('C004','GHAJNI','MUMBAI',12);

INSERT INTO CUSTOMER(CID,CNAME,CITY,DISCOUNT)

VALUES('C005','HARISH','HARYANA',13);

SELECT * FROM TABLES :

1. SELECT *FROM CUSTOMER;

2. CREATE TABLE AGENT

(

AID VARCHAR2(4) CONSTRAINT PK_AGENT PRIMARY KEY,

ANAME VARCHAR2(20) NOT NULL,

CITY VARCHAR2(10) NOT NULL,

PERCENT NUMBER (3),

CHECK (AID LIKE 'A%')

);

2. DESC AGENT;

2.INSERT INTO "AGENT" TABLE :

INSERT INTO AGENT(AID,ANAME,CITY,PERCENT)

VALUES('A001','ADRIJA','KOLKATA',25);

INSERT INTO AGENT(AID,ANAME,CITY,PERCENT)

VALUES('A002','ADITYA','CHENNAI',36);

INSERT INTO AGENT(AID,ANAME,CITY,PERCENT)

VALUES('A003','SANKHA','DELHI',32);

INSERT INTO AGENT(AID,ANAME,CITY,PERCENT)

VALUES('A004','ROHIT','VARANASI',29);

INSERT INTO AGENT(AID,ANAME,CITY,PERCENT)

VALUES('A005','SUVANKAR','MUMBAI',25);

2. SELECT *FROM AGENT;

3. PRODUCT TABLE :

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P001','MOBILE','MUMBAI',100,850.0);

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P002','SPEAKER','KOLKATA',115,500.45);

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P003','LED LIGHT','VARANASI',600,50.45);

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P004','LAPTOPS','DELHI',60,500.50);

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P005','I/O DEVICE','HARYANA',550,500.45);

3. DESC PRODUCT;

3.INSERT INTO "PRODUCT" TABLE :

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P001','MOBILE','MUMBAI',100,850.0);

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P002','SPEAKER','KOLKATA',115,500.45);

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P003','LED LIGHT','VARANASI',600,50.45);

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P004','LAPTOPS','DELHI',60,500.50);

INSERT INTO PRODUCT (PID,PNAME,CITY,QTY,PRICE)

VALUES ('P005','I/O DEVICE','HARYANA',550,500.45);

3. SELECT *FROM PRODUCT;

4. CREATE TABLE ORDERS

(

ORDNO NUMBER (4) CONSTRAINT PK_ORDERS PRIMARY KEY,

MONTH NUMBER (2) CHECK (MONTH>=1 AND MONTH<=12),

AID VARCHAR2(4) CONSTRAINT FK_AID_ORDERS REFERENCES AGENT(AID) NOT NULL,

CID VARCHAR2(4) CONSTRAINT FK_CID_ORDERS REFERENCES CUSTOMER(CID) NOT NULL,

PID VARCHAR2(4) CONSTRAINT FK_PID_ORDERS REFERENCES PRODUCT(PID) NOT NULL,

QTY NUMBER (3) NOT NULL,

RUPEES NUMBER (5,2) NOT NULL

);

4. DESC ORDERS;

4. INSERT INTO "ORDERS" TABLE :

INSERT INTO ORDERS(ORDNO,MONTH,AID,CID,PID,QTY,RUPEES)

VALUES(0001,1,'A001','C001','P001',50,850.69);

INSERT INTO ORDERS(ORDNO,MONTH,AID,CID,PID,QTY,RUPEES)

VALUES(0002,5,'A002','C002','P002',120,855.62);

INSERT INTO ORDERS(ORDNO,MONTH,AID,CID,PID,QTY,RUPEES)

VALUES(0003,6,'A003','C003','P003',95,900.50);

INSERT INTO ORDERS(ORDNO,MONTH,AID,CID,PID,QTY,RUPEES)

VALUES(0004,2,'A004','C004','P004',105,860.00);

INSERT INTO ORDERS(ORDNO,MONTH,AID,CID,PID,QTY,RUPEES)

VALUES(0005,3,'A005','C005','P005',45,840.75);

4. SELECT *FROM ORDERS;

QUERIES :

4. ALL CID,AID AND PID FOR CUSTOMER, AGENT AND PRODUCT COMBINATION THAT ARE ALL IN THE SAME CITY :

SELECT AID,CID,PID FROM AGENT,CUSTOMER,PRODUCT WHERE AGENT.CITY = CUSTOMER.CITY AND CUSTOMER.CITY=PRODUCT.CITY;

5. THE NAMES OF AGENTS WHO PLACE ORDERS FOR ALL PRODUCTS ORDER BY CUSTOMER C003:

SELECT A.ANAME FROM AGENT A,ORDERS WHERE A.AID=ORDERS.AID AND ORDERS.CID='C003';

6. CUSTOMER ID OF CUSTOMER WHO HAVE LARGEST DICOUNT SEPARATELY FIND THOSE HAVE SMALLEST DISCOUNT :      

SELECT CID FROM CUSTOMER WHERE DISCOUNT=(SELECT MAX(DISCOUNT) FROM CUSTOMER);