Create the tables using create statements.
create table physics(regno varchar(10),name char(10),year char(10),combination char(5)); create table computer_science(regno varchar(10),name char(10),year char(10),combination char(5));
To insert the values: Table: Physics.
insert into physics values(‘AJ00325’,’Ashwin’,’first’,’PCM’);
insert into physics values(‘AJ00225’,’Swaroop’,’second’,’PMCS’);
insert into physics values(‘AJ00385’,’sarika’,’third’,’PME’);
insert into physics values(‘AJ00388’,’Hamsa’,’first’,’PMCS’);
Table: Computer_science.
insert into computer_science values(‘AJ00225’,’Swaroop’,’second’,’PMCS’);
insert into computer_science values(‘AJ00296’,’Tejas’,’second’,’BCA’);
insert into computer_science values(‘AJ00112’,’Geetha’,’first’,’BCA’);
insert into computer_science values(‘AJ00388’,’Hamsa’,’first’,’PMCS’);
1. Select all students from physics and computer science.
select * from physics union select * from computer_science;
2. Select student common in physics and computer science.
select * from physics intersect select * from computer_science;
3. Display all student details those are studying in second year.
select * from computer_science where year=’second’;
4. Display student those who are studying both physics and computer science in second year.
select * from computer_science
where year=’second’ union
select * from physics
where year=’second’;
5. Display the students studying only physics.
select * from physics minus select * from computer_science;
6. Display the students studying only computer_science.
select * from computer_science minus select * from physics;
7. Select all student having PMCS combination.
select * from physics
where combination=’PMCS’ union select * from computer_science where combination=’PMCS’;
8. Select all student having BCA combination.
select * from physiscs
where combination=’BCA’ union select * from computer_science where combination=’BCA’;
9. Selectallstudentstudyinginthirdyear.
select * from physiscs
where year=’third’ union
select * from computer_science where year=’third’;
10 . Rename table computer_science to CS.
rename computer_science to CS;
2. Create the table using create statements. Table: Employee.
create table employee(empid number(5)primary key not null, firstname char(10),lastname char(10),hire_date date,address varchar(20),city char(15));
Table: Empsalary.
create table empsalary(salary number(5),benefits number(5),designation char(10),empid number(5),foreign key (empid)references employee(empid) on delete cascade);
Insert the values to the table. Table: Employee.
insert into employee values(1001,’George’,’smith’,11-May-06,’83 first street’,’Paris’);
insert into employee values(1002,’Mary’,’Jones’,25-Feb-08,’842 Vine Ave’,’Losantiville’);
insert into employee values(1012,’Sam’,’Tones’,12-sep-05,’33 Elm St.’,’Paris’);
insert into employee values(1015,’Peter’,’Thompson’,19-Dec-06,’11 Red Road’,’Paris’);
insert into employee values(1016,’Sarath’,’Sharma’,22-Aug-07,’440 MG Road’,’New Delhi’);
insert into employee values(1020,’Monika’,’Gupta’,07-Jun-08,’9 Bandra’,’Mumbai’);
Table: Empsalary
insert into empsalary values(10000,3000,’Manager’,1001);
insert into empsalary values(8000,1200,’salesman’,1002);
insert into empsalary values(20000,5000,’Director’,1012);
insert into empsalary values(6500,1300,’Clerk’,1015);
insert into empsalary values(6000,1000,’Clerk’,1016);
insert into empsalary values(8000,1200,’Salesman’,1020)
1. To display FIRSTNAME,LASTNAME,ADDRESS and CITY of all employees living in PARIS.
select firstname,lastname,address,city from employee where city=’Paris’;
2. To display the content of employee table in descending order of FIRSTNAME.
select * from employee order by firstname desc;
3. Select FIRSTNAME and SALARY of salesman.
select e.firstname,es.salary
from employee e,empsalary es
where e,empid=es.empid and designation=’salesman’;
4. To display the FIRSTNAME,LASTNAME and TOTAL SALARY of all employee from the table EMPLOYEE and EMPSALARY where TOTAL SALARY is calculated as SALARY+BENEFITS.
Select e.firstname,e.lastname,(es.salary+es.benefits)as total salary
from employee e,empsalary es
where e.empid=es.empid;
5. List the names of employees,who are more than 1 year old in the organisation.
Select firstname,lastname
from employee
where (hire_date –sysdate)>365;
6. Count number of distinct DESIGNATION from EMPSALARY.
select count(distinct designation) from empsalary;
8. Add new column PHONE_NO to EMPLOYEE and update the records.
Alter table employee
add phone_no number(10);
update employee
set phone_no=’1234567890’;
9. List employee names, who have joined before 15-Jun-08 and after 16-Jun-07.
select * from employee
where hire_date ’16-Jun-07’ and ’15-Jun-08’;
10. Generate salary slip with name, salary, benefits,HRA-50%,DA-30%,PF-12%, calculate gross order the result in descending order of the gross.
Select es.salary,e.firstname.es.benefits(es.salary*0.5+es.salary*0.3+es.salary*0.12)as gross
from employee e,empsalary es
where e.empid=es.empid
order by gross desc;
Create the table using create statements. Table: Books.
Create table Library(Book_Id varchar(5)primary key not null, Book_name char(20), Author_name char(15), Publishers char(10), Price number(3),Type char(10),Quantity number(2));
Insert the values to the table: Table: Library.
insert into library values(‘C0001’,’The klone and I’,’Lata kapoor’,’EPP’,355,’Novel’,5);
insert into library values(‘F0001’,’The tears’,’William Hopkins’,’First publ’,650,’Fiction’,20);
insert into library values(‘T0001’,’My First C++’,’Brain &Brook’,’ERP’,350,’Text’,10);
insert into library values(‘T0002’,’C++ Brainworks’,’A.W.Rossain’,’THD’,350,’Text’,15);
insert into library values(‘F0002’,’Thunderbolts’,’Ana Roberts’,’First publ’,750,’Fiction’,50);
1. To show first name author name and price of the books of the first publ. publisher.
Select firstname, authorname, price
from library
where publisher=’First publ’;
3. Select bookid, bookname,authorname of the books which is published by other than ERP publishers and price between 300 to 700.
Select bookid, bookname,authorname from library
where publishers not in ‘ERP’ and price between 300 and 700;
5 Display book details with bookid’s C0001, F0001, T0002, F0002 (Hint: use IN operator).
Select * from library
where bookid in(‘C0001’,’F0001’,’T0002’,’F0002’);
0 Comments