TABLE CREATION

create table dept_master

( dept_no varchar2(5) NOT NULL PRIMARY KEY CHECK(dept_no like 'd%'),
department_name varchar2(25),
department_location varchar2(30));

insert into dept_master values('d1','management','saket');
insert into dept_master values('d2','technical','saket;');
insert into dept_master values('d3','marketing','vaishali;');
insert into dept_master values('d4','accounts','samalka');


create table employee_master
( employee_no varchar2(6) check(employee_no like 'e%'),
employee_name varchar2(30),
adress1 varchar(30),
adress2 varchar2(30),
dateofbirth date,
basic number(6,2),
commission number(6,2),
dept_no varchar2(5) REFERENCES dept_master(dept_no),
designation varchar2(6) default 'mstaff' CHECK (designation in ('cman','vp','mgr','slman','mstaff'))
);


insert into employee_master values('e1','roma','saket','samalka','15-nov-1971',9000,5000,'d1','cman');
insert into employee_master values('e2','raj','vaishali','samalka','15-nov-1981',7000,2000,'d3','slman');
insert into employee_master values('e3','priya','vaishali','rohini','15-nov-1987',7000,2000,'d1','mgr');
insert into employee_master values('e4','anu','vaishali','rithala','11-oct-1991',6000,1000,'d4','mstaff);
insert into employee_master values('e5','anyaa','kaushambhi','rohini','10-nov-1987',7500,2000,'d2','vp');



create table vender_master
(
vender_id varchar2(6) NOT NULL PRIMARY KEY CHECK(vender_id like 'v%'),
vender_name varchar2(25),
adress1 varchar2(25),
address2 varchar2(25),
city varchar2(20),
state varchar2(15),
pincode number(6),
phone number(10)
);

insert into vender_master values('v1','ram','ramnagar','rohini','west delhi','delhi',110043,011236458);
insert into vender_master values('v2','raj','rajnagar','rithala','east delhi','delhi',110048,011233458);
insert into vender_master values('v3','rohan','raknagar','saket','north delhi','delhi',110088,011233488);
insert into vender_master
values('v4','mohan','gajnagar','vaishali','banasthali','rajasthan',110848,011233498);

create table material_master
(
material_id varchar2(6) NOT NULL PRIMARY KEY
CHECK(material_id like 'm%'),
material_desc varchar2(25),
unit_price number(6,2),
unit_measure varchar2(6),
stock number(3)
);

insert into material_master values('m1','gold',2253.5,'gram',123);
insert into material_master values('m2','platinum',2534.5,'gram',223);
insert into material_master values('m3','silk',2534.5,'metre',723);
insert into material_master values('m4','silver',2734.5,'gram',223);


create table order_master
(
vender_id varchar2(6) REFERENCES vender_master(vender_id),
material_id varchar2(6) REFERENCES material_master(material_id),
quantity_ordered number(4) CHECK (quantity_ordered>0),
date_ordered date DEFAULT sysdate,
order_id varchar2(6) NOT NULL PRIMARY KEY
CHECK(order_id like 'o%'), sell_price number(6,2) CHECK(sell_price>0),
employee_no varchar2(6),
delivery_status char(1) default 'p'
CHECK(delivery_status='f' OR delivery_status='p'),
qty_delivered number(4),
check (qty_delivered<quantity_ordered AND qty_delivered>0)
);
insert into order_master values('v1','m1',4,'4-apr-2013','o1',2564.25,'e1','p',3);
insert into order_master values('v2','m2',100,'05-dec-2012','o2',2023.85,'e2','f',95);
insert into order_master values('v3','m3',1200,'17-aug-2012','o3',9856.85,'e3','p',1125);
insert into order_master values('v4','m4',830,'05-may-2013','o4',7859.75,'e4','f',825);


QUERIES:
Q1:SQL> select employee_name,dateofbirth,dept_no from employee_master where substr(
dateofbirth,8,8)>73;

Q2: SQL> select employee_no,employee_name,dept_no from employee_master where substr(
dateofbirth,4,3) like 'JUN';
no rows selected.
Q3: SQL> select employee_no,employee_name,dept_no from employee_master where substr(
dateofbirth,4,3) between 'JUN' and 'DEC';
no rows selected.
Q4:Q2
Q5: SQL> update employee_master set dateofbirth='23-aug-67' where employee_no='e1';

Q6: SQL> alter table employee_master ADD constraint e1 PRIMARY KEY(employee_no);
Table altered.

Q7: SQL> select vender_name,pincode,adress1 from vender_master where vender_na
e '%ab%';
no rows selected.
Q8: SQL> select vender_id,vender_name,city,phone from vender_master where state='raj
asthan';

Q9: SQL> select vender_id,vender_name,state from vender_master where adress1 like '%-%';
no rows selected
Q10: SQL> select count(*) RESULT from vender_master where state='delhi' group by state having
count(*)>2;

Q11: SQL> alter table order_master ADD constraint e1 FOREIGN KEY(employee_no) REFERENCES
employee_master(employee_no);
Table altered.
Q12: SQL> select * from vender_master where state <> ALL('himachal pradesh','rajastha
n');

Q13:select * from material_master where stock>5;

Q14: SQL> select material_id,material_desc,stock from material_master where unit_price between 900
and 1800;
no rows selected
Q15: SQL> select * from material_master where unit_price in(550.25,600,750,900);
no rows selected
Q16: SQL> update vender_master set adress1='A-4 Pashim vihar' where vender_id like 'v1';
1 row updated.

Q16: SQL> select employee_name,department_name,department_location from employee_master
e,dept_master d where e.dept_no=d.dept_no and e.designation='slman';

Q17: SQL> select employee_name from employee_master where commission > 0.15*basic and
designation='slman';

Q18: SQL> select employee_name,employee_no from employee_master where basic>(basic/12);

Q19: SQL> select avg(basic) sav,min(basic) smi,max(basic) smx from employee_master;

Q20:
Q21: SQL> select employee_name,designation,department_name,department_location from
mployee_master e,dept_master d where e.dept_no=d.dept_no and department_locatio
='delhi';
No rows selected.