assgn2.sql

CREATE TABLE USERDATA (
	userid varchar2(6) primary key,
	email varchar2(20) unique,
	fullname varchar2(18) not null,
	gender char(1),
	address varchar2(35)
);
CREATE TABLE ITEMCATEGORY( 
	catid char(3) primary key,
	catname varchar2(15),
	description varchar2(30)
);
CREATE TABLE ITEM(
	itemid char(3) primary key,
	itemtype char(5),
	itemname varchar2(50) unique,
	unitprice number(6,2) not null,
	Foreign key (itemtype) references ITEMCATEGORY(catid) 
);
CREATE TABLE ORDERLIST(
	orderid number GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	userid varchar2(6),
	itemid char(3),
	qty number(2) not null,
	amount number(8,2) not null,
	orderdate date,
	paymode char(4)
);
/*2A*/
ALTER TABLE USERDATA 
RENAME TO ALLUSER;
RENAME USERDATA TO ALLUSER;
DESC USERDATA;
ERROR:
ORA-04043: object USERDATA does not exist
DESC ALLUSER;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID 				   NOT NULL VARCHAR2(6)
 EMAIL						    VARCHAR2(20)
 FULLNAME				   NOT NULL VARCHAR2(18)
 GENDER 					    CHAR(1)
 ADDRESS					    VARCHAR2(35)

/*2B*/
ALTER TABLE ITEMCATEGORY 
RENAME COLUMN description TO descr;
ALTER TABLE ITEMCATEGORY MODIFY 
DESCR VARCHAR2(50);
ALTER TABLE ITEMCATEGORY MODIFY 
CATNAME NOT NULL;
DESC ITEMCATEGORY;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CATID					   NOT NULL CHAR(3)
 CATNAME				   NOT NULL VARCHAR2(15)
 DESCR						    VARCHAR2(50)
/*2C*/
ALTER TABLE ORDERLIST 
MODIFY FOREIGN KEY(userid) references ALLUSER(userid);

ALTER TABLE ORDERLIST 
MODIFY FOREIGN KEY(itemid) references ITEM(itemid);

/*2D*/

ALTER TABLE ALLUSER
DROP COLUMN GENDER;
DESC ALLUSER;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID 				   NOT NULL VARCHAR2(6)
 EMAIL						    VARCHAR2(20)
 FULLNAME				   NOT NULL VARCHAR2(18)
 ADDRESS					    VARCHAR2(35)


ALTER TABLE ALLUSER ADD
phone char(10) unique;


 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID 				   NOT NULL VARCHAR2(6)
 EMAIL						    VARCHAR2(20)
 FULLNAME				   NOT NULL VARCHAR2(18)
 ADDRESS					    VARCHAR2(35)
 PHONE						    CHAR(10)
/*3a*/
INSERT INTO ITEMCATEGORY values
	('DRT','Desert', 'Usually taken after main course.');
INSERT INTO ITEMCATEGORY values
	('MNC', 'Main Course', 'Can be taken as lunch or dinner.');
INSERT INTO ITEMCATEGORY values
	('DRT','Desert', 'Usually taken after main course.');
INSERT INTO ITEMCATEGORY values
	('BVG','Beverages', 'Can be taken at any time.');
INSERT INTO ITEMCATEGORY values
	('FTF', 'Fast Food', 'Can be taken as breakfast or tiffin.');


/*3B*/ 

ALTER TABLE ITEM MODIFY 
itemtype char(3);
INSERT INTO ITEM VALUES
	('F01', 'FTF', 'Paneer Tikka Wrap Combo', 130.75);
INSERT INTO ITEM VALUES
	('M01', 'MNC', 'Rajma Chawal Bowl', 189.25);
INSERT INTO ITEM VALUES
	('M02','MNC', 'Hyderabadi Chicken Biryani', 220.50);
INSERT INTO ITEM VALUES
	('B01', 'BVG', 'Mint Chaas', 89.00);
INSERT INTO ITEM VALUES
	('M03', 'MNC', 'Kadai Paneer-Thali',209.75);
INSERT INTO ITEM VALUES
	('F02', 'FTF', 'Chicken Tikka Sandwich', 139.25);
INSERT INTO ITEM VALUES
	('M04','MNC','Chana Masala Pulao', 179.25);
INSERT INTO ITEM VALUES
	('F03','FTF','Chilli Corn 65', 179.25);
INSERT INTO ITEM VALUES
	('B02','BVG', 'Masala Lemonade', 89.00);
	
/*4*/

INSERT INTO ALLUSER VALUES
	('doro77','dorothy77@gmail.com','Dorothy Jones', '4D/1, Fern Road, Kolkata','4578895623');	
INSERT INTO ALLUSER VALUES
	('eve80','evelyn@gmail.com','Evelyn Simon', NULL, '7788778877');
INSERT INTO ALLUSER VALUES
	('harry','harry_t@yahoo.com','Harry Thompson', '14, BB Ganguly St., Kolkata', NULL);	
INSERT INTO ALLUSER VALUES
	('raja.h','rhasan@hotmail.com', 'Hasan Raja ',' AE34, Laketown, Kolkata',NULL);	
INSERT INTO ALLUSER VALUES
	('james5', 'james5@rediff.com','James Andy',NULL, NULL);
	
/*4f*/	
UPDATE ALLUSER SET address='Flat 207, Block K,
New Town' WHERE fullname='Evelyn Simon';


SELECT * FROM ALLUSER;

USERID EMAIL		    FULLNAME	       ADDRESS				   PHONE
------ -------------------- ------------------ ----------------------------------- ----------
doro77 dorothy77@gmail.com  Dorothy Jones      4D/1, Fern Road, Kolkata 	   4578895623
eve80  evelyn@gmail.com     Evelyn Simon       Flat 207, Block K,		   7788778877
					       New Town

harry  harry_t@yahoo.com    Harry Thompson     14, BB Ganguly St., Kolkata
raja.h rhasan@hotmail.com   Hasan Raja		AE34, Laketown, Kolkata		   3222548522
james5 james5@rediff.com    James Andy
/*4g*/
UPDATE ALLUSER SET address='255, Jessore Road,
Barasat', phone='2200560087' WHERE fullname='James Andy';
	

 SELECT * FROM ALLUSER;

USERID EMAIL		    FULLNAME	       ADDRESS				   PHONE
------ -------------------- ------------------ ----------------------------------- ----------
doro77 dorothy77@gmail.com  Dorothy Jones      4D/1, Fern Road, Kolkata 	   4578895623
eve80  evelyn@gmail.com     Evelyn Simon       Flat 207, Block K,		   7788778877
					       New Town

harry  harry_t@yahoo.com    Harry Thompson     14, BB Ganguly St., Kolkata
raja.h rhasan@hotmail.com   Hasan Raja		AE34, Laketown, Kolkata 	   3222548522
james5 james5@rediff.com    James Andy	       255, Jessore Road,		   2200560087
					       Barasat

/*5a*/
ALTER TABLE ORDERLIST
DROP COLUMN amount; 

INSERT INTO ORDERLIST VALUES 
	(DEFAULT,'doro77','F02', 3, TO_DATE('2022-Jan-02','YYYY-MON-DD'),'UPI');
INSERT INTO ORDERLIST VALUES 
	(DEFAULT,'harry', 'M01', 1, TO_DATE('2022-Jan-04','YYYY-MON-DD'),'NETB');
INSERT INTO ORDERLIST VALUES 
	harryB0212022-Jan-04NETB
doro77F0232022-Jan-08UPIdoro77B0222022-Jan-08UPI
raja.hM0232022-Jan-18UPI
eve80M0112022-Jan-21NETB
eve80B0212022-Jan-21NETB
doro77M0422022-Feb-03CRDC
james5M0312022-Feb-03NETB
james5B0222022-Feb-03NETB
doro77M0222022-Feb-07UPI
doro77B0142022-Feb-09UPI
james5F0222022-Feb-12DEBT
eve80M04102022-Feb-20CRDC
eve80B0162022-Feb-20CRDC
eve80F0222022-Feb-20CRDC
harryM0332022-Feb-23NETB
raja.hF0242022-Feb-26DEBT
harryB0222022-Feb-28NETB