Notes
Notes - notes.io |
insert into STUDENT values('®no',&rollno,'&name','&address',&phno,&yearofadm,'&deptcode',&year,'&birthdate');
select * from STUDENT;
select name,address,yearofadm from STUDENT;
select name,year from STUDENT where deptcode like 'cse';
select name,deptcode from STUDENT where year=3;
select name from STUDENT where name like '_a%';
select name from STUDENT order by name;
select name,address from STUDENT where yearofadm=2004;
select name from STUDENT where phno is null;
alter table STUDENT add marksobtained number(10);
insert into STUDENT values('012349', 123049, 'Gita', 'Kasba', 24428682, 2003, 'MCA' ,3, '14-Apr-81');
commit;
3.update STUDENT set address='girishpark' where rollno=123001 and name='Ashish';
2.select deptcode, count(deptcode) from STUDENT group by deptcode;
4.alter table STUDENT add(college_ph_no number(10));
update STUDENT set college_ph_no=25739607;
5.alter table STUDENT modify (name varchar2(15));
6.alter table STUDENT add(marksobtainted number(10));
7.update STUDENT set marksobtainted=290 where regno='012315';
8.alter table STUDENT drop column marksobtainted;
10.alter table STUDENT drop column year;
alter table STUDENT add(year number(1) check(year in(1,2,3,4)));
ASSIGNMENT-E3:
1.create table DEPARTMENT(deptcode varchar2(4) primary key,deptname varchar2(15) NOT NULL,hod varchar2(4),check(deptcode in('CSE','IT','CA','CHEM','MTHS','PHYS','HUM','BBA')));
create table FACULTY(facultycode varchar2(4) primary key check(facultycode like 'f%'),facultyname varchar2(15) NOT NULL,dateofjoin date NOT NULL,deptcode varchar2(4) f references DEPARTMENT);
2.insert into DEPARTMENT values('CSE','computer sc','P.R');
insert into DEPARTMENT values('IT','info tech','B.C');
insert into DEPARTMENT values('CA','computer app','S.R');
insert into DEPARTMENT values('CHEM','chemistry','K.S');
insert into DEPARTMENT values('MTHS','maths','S.B');
insert into DEPARTMENT values('PHYS','physics','P.B');
insert into DEPARTMENT values('HUM','human studies',R.S');
insert into DEPARTMENT values('BBA','buiseness add','N.R');
insert into FACULTY values('f100','samir','01-jun-01','CSE');
insert into FACULTY values('f101''palash','19-sep-98','CSE');
insert into FACULTY values('f200','rajiv','10-aug-93','CA');
insert into FACULTY values('f201','puja','10-jan-95','CA');
insert into FACULTY values('f300','payel','05-may-99','CHEM');
create table STUDENT(regno varchar2(6) primary key,rollno number(6),name varchar2(10),address varchar2(15),phno number(10),yearofadm number(4),deptcode varchar2(4),year number(1),birthdate date);
insert into STUDENT values('®no',&rollno,'&name','&address',&phno,&yearofadm,'&deptcode',&year,'&birthdate');
select * from STUDENT;
select name,address,yearofadm from STUDENT;
select name,year from STUDENT where deptcode like 'cse';
select name,deptcode from STUDENT where year=3;
select name from STUDENT where name like '_a%';
select name from STUDENT order by name;
select name,address from STUDENT where yearofadm=2004;
select name from STUDENT where phno is null;
alter table STUDENT add marksobtained number(10);
insert into STUDENT values('012349', 123049, 'Gita', 'Kasba', 24428682, 2003, 'MCA' ,3, '14-Apr-81');
commit;
3.update STUDENT set address='girishpark' where rollno=123001 and name='Ashish';
2.select deptcode, count(deptcode) from STUDENT group by deptcode;
4.alter table STUDENT add(college_ph_no number(10));
update STUDENT set college_ph_no=25739607;
5.alter table STUDENT modify (name varchar2(15));
6.alter table STUDENT add(marksobtainted number(10));
7.update STUDENT set marksobtainted=290 where regno='012315';
8.alter table STUDENT drop column marksobtainted;
10.alter table STUDENT drop column year;
alter table STUDENT add(year number(1) check(year in(1,2,3,4)));
ASSIGNMENT-E3:
1.create table DEPARTMENT(deptcode varchar2(4) primary key,deptname varchar2(15) NOT NULL,hod varchar2(4),check(deptcode in('CSE','IT','CA','CHEM','MTHS','PHYS','HUM','BBA')));
create table FACULTY(facultycode varchar2(4) primary key check(facultycode like 'f%'),facultyname varchar2(15) NOT NULL,dateofjoin date NOT NULL,deptcode varchar2(4) f references DEPARTMENT);
2.insert into DEPARTMENT values('CSE','computer sc','P.R');
insert into DEPARTMENT values('IT','info tech','B.C');
insert into DEPARTMENT values('CA','computer app','S.R');
insert into DEPARTMENT values('CHEM','chemistry','K.S');
insert into DEPARTMENT values('MTHS','maths','S.B');
insert into DEPARTMENT values('PHYS','physics','P.B');
insert into DEPARTMENT values('HUM','human studies',R.S');
insert into DEPARTMENT values('BBA','buiseness add','N.R');
insert into FACULTY values('f100','samir','01-jun-01','CSE');
insert into FACULTY values('f101''palash','19-sep-98','CSE');
insert into FACULTY values('f200','rajiv','10-aug-93','CA');
insert into FACULTY values('f201','puja','10-jan-95','CA');
insert into FACULTY values('f300','payel','05-may-99','CHEM');
insert into FACULTY values('f400','mousumi','05-feb-93','IT',12000);
4.select facultyname from FACULTY where deptcode='CSE';
5.select count(deptcode)from FACULTY where deptcode='CA';
6.select hod,deptname from DEPARTMENT;
8.select count(facultyname) from FACULTY where month(dateofjoin)='aug';
9.alter table FACULTY add(salary number(8,2));
10.update FACULTY set salary=5000 where facultycode='f100';
update FACULTY set salary=9000 where facultycode='f101';
update FACULTY set salary=8200 where facultycode='f200';
update FACULTY set salary=6000 where facultycode='f201';
update FACULTY set salary=10000 where facultycode='f300';
11.select facultyname,salary from FACULTY where salary>8000;
12.select facultyname,deptcode from FACULTY where salary between 8000 and 12000;
13.select max(salary) from FACULTY;
Assignment:E3
1.create table SUBJECT(subjectcode varchar2(4) primary key,subjectname varchar2(15) NOT NULL,facultycode varchar2(4) references FACULTY(facultycode));
insert into SUBJECT values('s101','numerical','f100');
insert into SUBJECT values('s102','datastructure','f101');
insert into SUBJECT values
create table STUDENT(regno varchar2(6) primary key,rollno number(6),name varchar2(10),address varchar2(15),phno number(10),yearofadm number(4),deptcode varchar2(4),year number(1),birthdate date);
insert into STUDENT values('®no',&rollno,'&name','&address',&phno,&yearofadm,'&deptcode',&year,'&birthdate');
select * from STUDENT;
select name,address,yearofadm from STUDENT;
select name,year from STUDENT where deptcode like 'cse';
select name,deptcode from STUDENT where year=3;
select name from STUDENT where name like '_a%';
select name from STUDENT order by name;
select name,address from STUDENT where yearofadm=2004;
select name from STUDENT where phno is null;
4.select facultyname from FACULTY where deptcode='CSE';
5.select count(deptcode)from FACULTY where deptcode='CA';
6.select hod,deptname from DEPARTMENT;
8.select count(facultyname) from FACULTY where to_char (dateofjoin,'mon')='aug';
9.alter table FACULTY add(salary number(8,2));
10.update FACULTY set salary=5000 where facultycode='f100';
update FACULTY set salary=9000 where facultycode='f101';
update FACULTY set salary=8200 where facultycode='f200';
update FACULTY set salary=6000 where facultycode='f201';
update FACULTY set salary=10000 where facultycode='f300';
11.select facultyname,salary from FACULTY where salary>8000;
12.select facultyname,deptcode from FACULTY where salary between 8000 and 12000;
13.select max(salary) from FACULTY;
Assignment:E4
1.create table SUBJECT(subjectcode varchar2(4) primary key,subjectname varchar2(15) NOT NULL,facultycode varchar2(4) references FACULTY(facultycode));
insert into SUBJECT values('s101','numerical','f100');
insert into SUBJECT values('s200','datastructure','f101');
insert into SUBJECT values('s300','isad','f200');
2.select deptcode, count(deptcode) from STUDENT group by deptcode;
3.update FACULTY set salary=salary+500;
4.select name,facultyname from STUDENT,FACULTY where name like 'S%'and facultyname like 's%';
5.select name from STUDENT where address like'Kaikhali';
6.select facultyname from FACULTY where deptcode='IT';
7.select FACULTY.faultyname from FACULTY,DEPARTMENT where faculty.deptcode=department.deptcode and department.hod='f001';
8.alter table SUBJECT add(department varchar2(4),year varchar2(1));
9.update SUBJECT set department='cse',year=2 where subjectcode='s101';
update SUBJECT set department='mca',year=3 where subjectcode='s200';
update SUBJECT set department='it',year=1 where subjectcode='s300';
10.select facultyname from FACULTY where salary>(select avg(salary) from FACULTY);
11.select * from FACULTY where salary>(select avg(salary) from FACULTY and deptcode='cse');
12.select * from FACULTY where salary=(select max(salary),min(salary) from FACULTY);
13.select max(salary) from FACULTY where salary not in(select max(salary) from FACULTY);
14.select * from FACULTY where facultycode not in(select hod from DEPARTMENT);
15.select subjectname from SUBJECT where department='cse' and year='3';
16.SELECT FACULTYNAME,DEPTCODE FROM FACULTY WHERE DEPTCODE=(SELECT DEPTCODE FROM FACULTY GROUP BY DEPTCODE HAVING COUNT(DEPTCODE)=(SELECT MAX(COUNT(DEPTCODE)) FROM FACULTY GROUP BY DEPTCODE));
Assignment-E5
CREATION:
CREATE TABLE SUPPLIER
(
SNO VARCHAR2(5) PRIMARY KEY,
NAME VARCHAR2(15),
STATUS NUMBER(2),
CITY VARCHAR2(10)
);
CREATE TABLE PART
(
PNO VARCHAR2(5) PRIMARY KEY,
PNAME VARCHAR2(10),
COLOR VARCHAR2(10),
WEIGHT NUMBER(6,2),
CITY VARCHAR2(10)
);
CREATE TABLE PROJECT
(
JNO VARCHAR2(5) PRIMARY KEY,
JNAME VARCHAR2(10),
CITY VARCHAR2(10)
);
CREATE TABLE SHIPMENT
(
SNO VARCHAR2(5)REFERENCES SUPPLIER(SNO),
PNO VARCHAR2(5) REFERENCES PART(PNO),
JNO VARCHAR2(5) REFERENCES PROJECT(JNO),
QTY NUMBER(5),
PRIMARY KEY(SNO,PNO,JNO)
);
INSERTION:
INSERT INTO SUPPLIER (SNO,NAME,STATUS,CITY) VALUES
('S1','KRUNAL',10,'LONDON');
INSERT INTO SUPPLIER (SNO,NAME,STATUS,CITY) VALUES
('S2','RAMESH',5,'INDIA');
INSERT INTO SUPPLIER (SNO,NAME,STATUS,CITY) VALUES
('S3','VIVEK',4,'LONDON');
INSERT INTO SUPPLIER (SNO,NAME,STATUS,CITY) VALUES
('S4','VIMAL',3,'JAPAN');
INSERT INTO SUPPLIER (SNO,NAME,STATUS,CITY) VALUES
('S5','HEMAL',10,'KORIA');
INSERT INTO SUPPLIER (SNO,NAME,STATUS,CITY) VALUES
('S6','RAJU',2,'CHINA');
INSERT INTO SUPPLIER (SNO,NAME,STATUS,CITY) VALUES
('S7','VINU',3,'CHINA');
INSERT INTO SUPPLIER (SNO,NAME,STATUS,CITY) VALUES
('S10','SUVO',66,'INDIA');
INSERT INTO PART(PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P1','PARTA','RED',45,'NEW YORK');
INSERT INTO PART(PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P2','PARTB','WHITE',4,'LONDON');
INSERT INTO PART(PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P3','PARTC','GREY',24,'CHINA');
INSERT INTO PART(PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P4','PARTD','CYCAN',16,'CHINA');
INSERT INTO PART(PNO,PNAME,COLOR,WEIGHT,CITY) VALUES
('P5','PARTE','BLACK',58,'NEW YORK');
INSERT INTO PROJECT(JNO,JNAME,CITY) VALUES
('J1','PRJ1','LONDON');
INSERT INTO PROJECT(JNO,JNAME,CITY) VALUES
('J2','PRJ2','CHINA');
INSERT INTO PROJECT(JNO,JNAME,CITY) VALUES
('J3','PRJ3','CHINA');
INSERT INTO PROJECT(JNO,JNAME,CITY) VALUES
('J4','PRJ4','INDIA');
INSERT INTO SHIPMENT(SNO,PNO,JNO,QTY) VALUES
('S1','P2','J1',300);
INSERT INTO SHIPMENT(SNO,PNO,JNO,QTY) VALUES
('S1','P2','J2',800);
INSERT INTO SHIPMENT(SNO,PNO,JNO,QTY) VALUES
('S3','P4','J3',115);
INSERT INTO SHIPMENT(SNO,PNO,JNO,QTY) VALUES
('S4','P2','J4',130);
INSERT INTO SHIPMENT(SNO,PNO,JNO,QTY) VALUES
('S1','P3','J4',75);
1.select name from supplier,shipment where supplier.sno=shipment.sno and shipment.pno='P2';
2.select sno,status from supplier where status<(select max(status) from supplier);
3.select sno from supplier where sno in(select sno from shipment where pno not in(select pno from shipment where sno='S2'));
4.select sno from shipment where pno='p1' and qty>(select avg(qty)from shipment where pno='p1');
5.select sno,pno from supplier s,part p where p.city=s.city and status<20;
6.select sno,qty from shipment;
7.select s1.pno from shipment s1,shipment s2 where s1.qty=s2.qty and s1.sno <> s2.sno
ASSIGNMENT-E6
create table emp
(
emp_id varchar2(7) primary key,
emp_name varchar2(10),
street varchar2(15),
city varchar2(10));
create table company
(
comp_id varchar2(6) primary key,
comp_name varchar2(10),
mgr_name varchar2(10),
city varchar2(10));
create table works
(
emp_id varchar2(7) references emp(emp_id),
comp_id varchar2(6)references company(comp_id),
salary number(10,2),
primary key(emp_id,comp_id));
create table manages
(
mgr_id varchar2(5) primary key,
emp_id varchar2(7) references emp(emp_id));
insert values:
insert into emp(emp_id,emp_name,street,city) values
('e1','krunal','ad road','india');
insert into emp(emp_id,emp_name,street,city) values
('e2','ramesh','mg road','china');
insert into emp(emp_id,emp_name,street,city) values
('e3','vivek','sd road','england');
insert into emp(emp_id,emp_name,street,city) values
('e4','suresh','ar road','china');
insert into emp(emp_id,emp_name,street,city) values
('e5','nikhil','gt road','usa');
insert into emp(emp_id,emp_name,street,city) values
('e6','mousumi','bidon street','india');
insert into company values('c1','nokia','payel','bangalore');
insert into company values('c2','samsung','atindra','chicago');
insert into company values('c3','lg','dinendra','india');
insert into company values('c4','reliance','koushik','kerala');
insert into company values('c5','samsung','arnab','hyderabad');
insert into company values('c6','FBC','raj','kolkata');
insert into works values('e1','c1',1800);
insert into works values('e2','c2',2550);
insert into works values('e3','c3',7777);
insert into works values('e4','c4',1800);
insert into works values('e5','c5',3566);
insert into works values('e6','c6',3898);
insert into manages values('m1','e1');
insert into manages values('m2','e2');
insert into manages values('m3','e5');
insert into manages values('m4','e3');
insert into manages values('m5','e6');
insert into manages values('m6','e4');
1.select emp_name from emp where emp_id in(select emp_id from works where comp_id in(select comp_id from company where comp_name='FBC'));
2.select emp_name,city from emp where emp_id in(select emp_id from works where comp_id in(select comp_id from company where comp_name='FBC'));
3.select emp_name,street,city from emp where emp_id in(select emp_id from works where salary>10000 and comp_id in(select comp_id from company where comp_name='FBC));
4.select emp_name from emp where emp_id in(select emp.emp_id from works,emp,company where emp.emp_id=works.emp_id and company.comp_id=works.comp_id and emp.city=company.city);
5.select e.emp_name from emp e,emp e1, manages m where e.emp_id=m.emp_id and e1.emp_id=m.mgr_id and e.city=e1.city and e.street=e1.street;
6.select emp_name from emp where emp_id in(select emp_id from works where comp_id not in(select comp_id from company where comp_name='FBC'));
|
|
Notes.io is a web-based application for taking notes. You can take your notes and share with others people. If you like taking long notes, notes.io is designed for you. To date, over 8,000,000,000 notes created and continuing...
With notes.io;
- * You can take a note from anywhere and any device with internet connection.
- * You can share the notes in social platforms (YouTube, Facebook, Twitter, instagram etc.).
- * You can quickly share your contents without website, blog and e-mail.
- * You don't need to create any Account to share a note. As you wish you can use quick, easy and best shortened notes with sms, websites, e-mail, or messaging services (WhatsApp, iMessage, Telegram, Signal).
- * Notes.io has fabulous infrastructure design for a short link and allows you to share the note as an easy and understandable link.
Fast: Notes.io is built for speed and performance. You can take a notes quickly and browse your archive.
Easy: Notes.io doesn’t require installation. Just write and share note!
Short: Notes.io’s url just 8 character. You’ll get shorten link of your note when you want to share. (Ex: notes.io/q )
Free: Notes.io works for 12 years and has been free since the day it was started.
You immediately create your first note and start sharing with the ones you wish. If you want to contact us, you can use the following communication channels;
Email: [email protected]
Twitter: http://twitter.com/notesio
Instagram: http://instagram.com/notes.io
Facebook: http://facebook.com/notesio
Regards;
Notes.io Team
