DBMS : Lab Assignment No. 3 Q&A

Q&A.

Ques 1) Write a query to copy all the record of EMPLOYEE table to EMPLOYEEINFO table.

create table EMPLOYEE as select * from EMPLOYEEINFO

Ques 2) Update salary of Employee in the EMPLOYEEINFO whose empid is 7900.

update EMPLOYEEINFO set sal = sal + 500 where empid = 7900

Ques 3) Add a column phoneno to the EMPLOYEEINFO table.

alter table employee add PHONENO NUMBER(10)

Ques 4) Update phoneno to the entire Employee as per empid in EMPLOYEEINFO Table.

update employeeinfo set phoneno = 12345 where empid = 7839
(SAME STAEMENT FOR ALL EMPLOYEES.)

Ques 5) Update salary to all Employee in the EMPLOYEEINFO table whose job is clerk.

update employeeinfo set sal = sal + 500 where job like 'CLERK'

Ques 6) Update salary to all Employees whose salary is less than 1500 in the EMPLOYEEINFO table.

update employeeinfo set sal = sal + 500 where sal < 1500

Ques 7) Alter the size of ENAME in the EMPLOYEEINFO table.

alter table employeeinfo modify ENAME VARCHER2(10)

Ques 8) Rename EMPLOYEEINFO to EMPLOYEERECORD.

rename EMPLOYEEINFO to EMPLOYEERECORD

Ques 9) Delete employee from EMPLOYEERECORD whose deptid is 10.

delete from employeerecord where deptid = 10

Ques 10) Delete all record from EMPLOYEERECORD whose salary is less than 2000.

delete from employeerecord where sal < 2000

Ques 11) Delete all the record from the EMPLOYEERECORD whose deptid is 30 and 40.

delete from employeerecord where deptid IN(30, 40)

Ques 12) Delete all the record from the EMPLOYEERECORD who has comm.

delete from employeerecord where comm is not NULL

Ques 13) Drop the EMPLOYEERECORD table.

drop table employeerecord

Ques 14) Create a copy of EMPLOYEE table with the name EMPGDCA.

create table EMPGDCA as select * from EMPLOYEE

Ques 15) Delete the records of CLERKs from EMPGDCA.

delete from EMPGDCA where job like 'CLERK'

Ques 16) Delete all records from EMPGDCA.

delete EMPGDCA

CLIENT_MASTER
Column Name	Data Type	Size	Attribute Contraints
CLIENTNO	VARCHAR2	6	Primary Key/ First Latter must start with ‘C’
NAME	VARCHAR2	20	NOTNULL
ADDRESS1	VARCHAR2	30
ADDRESS2	VARCHAR2	30
CITY	VARCHAR2	15
PINCODE	NUMBER	8
STATE	VARCHAR2	15

Ques 17) Create Table: - CLIENT_MASTER

create table CLIENT_MASTERE(
CLIENTNO VARCHAR2(6) CHECK(CLIENTNO LIKE 'C%') PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
ADDRESS1 VARCHAR2(30),
ADDRESS2 VARCHAR2(30),
CITY VARCHAR2(15),
PINCODE NUMBER(8),
STATE VARCHAR2(15) )

PRODUCT_MASTER
Column Name	Data Type	Size	Attribute Contraints
PRODUCTNO	VARCHAR2	6	Primary Key/ First Latter must start with ‘P’
DESCRIPTION	VARCHAR2	15	NOTNULL
PROFITPERCENT	NUMBER	4,2	NOTNULL
UNITMEASURE	VARCHAR2	10	NOTNULL
QTYONHAND	NUMBER	8	NOTNULL
REORDERLVL	NUMBER	8	NOTNULL
SELLPRICE	NUMBER	8,2	NOTNULL, Cannot be 0
COSTPRICE	NUMBER	8,2	NOTNULL, Cannot be 0

Ques 18) Create Table: - PRODUCT_MASTER

create table PRODUCT_MASTER(
PRODUCTNO VARCHAR2(6) CHECK(PRODUCTNO LIKE 'P%') PRIMARY KEY,
DISCRIPTION VARCHAR2(15) NOT NULL,
PROFITPERCENT NUMBER(4,2) NOT NULL,
UNITMEASURE VARCHAR2(10) NOT NULL,
QTYONHAND NUMBER(8) NOT NULL,
REORDERVL NUMBER(8) NOT NULL,
SELLPRICE NUMBER(8,2) CHECK(SELLPRICE > 0) NOT NULL,
COSTPRICE NUMBER(8,2) CHECK(COSTPRICE > 0) NOT NULL )

SALESMAN_MASTER
Column Name	Data Type	Size	Attribute Contraints
SALESMANNO	VARCHAR2	6	Primary Key/ First Latter must start with ‘S’
SALESMANNAME	VARCHAR2	20	NOTNULL
ADDRESS1	VARCHAR2	30	NOTNULL
ADDRESS2	VARCHAR2	30
CITY	VARCHAR2	20
PINCODE	NUMBER	8
STATE	VARCHAR2	8
SALAMT	NUMBER	8,2	NOTNULL, Cannot be 0
TGTTOGET	NUMBER	6,2	NOTNULL, Cannot be 0
YTDSALES	NUMBER	6,2	NOTNULL
REMARKS	VARCHAR2	60

Ques 19) Create Table: - SALESMAN_MASTER

create table SALESMAN_MASTER(
SALESMANNO VARCHAR2(6) CHECK(SALESMANNO LIKE 'S%') PRIMARY KEY,
SALESMANNAME VARCHAR2(20) NOT NULL,
ADDRESS1 VARCHAR2(30) NOT NULL,
ADDRESS2 VARCHAR2(30),
CITY VARCHAR2(20),
PINCODE NUMBER(8),
STATE VARCHAR2(8),
SALAMT NUMBER(8,2) NOT NULL CHECK(SALAMT > 0),
TGTTOGET NUMBER(6,2) NOT NULL CHECK(TGTTOGET > 0),
YTDSALES NUMBER(6,2) NOT NULL,
REMARKS VARCHAR2(60) )

SALES_ORDER
Column Name	Data Type	Size	Attribute Contraints
ORDERNO	VARCHAR2	6	Primary Key/ First Latter must start with ‘O’
CLIENTNO	VARCHAR2	6	Foreign key references ClientNo of Client_Master table
ORDERDATE	DATE		NOTNULL
DELYADDRESS	VARCHAR2	25
SALESMANNO	VARCHAR2	6	Foreign key references SalesmanNo of Salesman_Master table
DELTYPE	CHAR	1	Delivery:- Part(P)/ Full(F)
BILLYN	CHAR	1
DELYDATE	DATE		Can not be less than 31-Dec-2012
ORDERSTATUS	VARCHAR2	10	Values(‘In Process’, ’Fulfilled’, ’BackOrder’, ’Cancelled’)

Ques 20) Create Table: - SALES_ORDER

create table SALES_ORDER(
ORDERNO VARCHAR2(6) CHECK(ORDERNO LIKE 'O%') PRIMARY KEY,
CLIENTNO VARCHAR2(6) FOREIGN KEY REFERENCES CLIENT_MASTER,
ORDERDATE DATE NOT NULL,
DELYADDRESS VARCHAR2(25),
SALESMANNO VARCHAR2(6) FOREIGN KEY REFERENCES SALESMAN_MASTER,
DELYDATE DATE CHECK(DELYDATE >= '31-DEC-2012'),
DELTYPE CHAR(1),
BILLYN CHAR(1),
ORDERSTATUS VARCHAR2(10) CHECK(ORDERSTATUS IN('InProcess','Fulfilled','BackOrder','Cancelled') )