Notes

Assignment 9:

CREATE TABLE customer(
custid NUMBER PRIMARY KEY,
custtype CHAR(10) CHECK(custtype IN('Regular', 'Privileged')),
custname CHAR(50) NOT NULL,
gender CHAR(1) CHECK(gender IN('M','F')),
spouse REFERENCES customer(custid),
emailid VARCHAR2(50),
mobileno NUMBER(10) NOT NULL,
resno NUMBER(10),
workno NUMBER(10),
address VARCHAR2(50)
);


Assignment 10:
ALTER TABLE customer MODIFY mobileno VARCHAR2(10);


Assignment 11:
CREATE TABLE item(
itemcode NUMBER PRIMARY KEY,
itemtype CHAR(10),
descr VARCHAR2(50) NOT NULL,
price NUMBER,
reorderlevel NUMBER,
qtyonhand INTEGER,CHECK(qtyonhand > 0),
category CHAR(1) CHECK(category IN('A','B','C'))
);

Assignment 12:
CREATE TABLE retailoutlet(
roid VARCHAR2(10) PRIMARY KEY,
location CHAR(10) NOT NULL,
managerid VARCHAR2(10)
);

Assignment 13:
CREATE TABLE purchasebill(
billid NUMBER(10) PRIMARY KEY,
retailoutlet VARCHAR2(10) REFERENCES retailoutlet(roid),
custid NUMBER(10) REFERENCES customer(custid),
itemcode NUMBER REFERENCES item(itemcode),
billamount NUMBER,
billdate DATE,
quantity NUMBER
);

Assignment 15:
CREATE TABLE supplier(
supplierid VARCHAR2(6) PRIMARY KEY,
sname VARCHAR2(30),
scontactno VARCHAR2(15),
semailid VARCHAR2(30)
);

Assignment 16:
ALTER TABLE supplier ADD supplier_city CHAR(10);