DBMS LAB : Cycle-III

Lab Cycle - III Queries



1. For each pilot who is certified for more than three aircraft, find the eid’s and the maximum cruising range of the aircraft that he (or She) certified for.

--> select c.eid,max(a.crusingrange) from certified c,aircraft a where c.aid=a.aid group by c.eid having count(eid)>3;

Output

EID MAX(A.CRUSINGRANGE)
142519864 8430
269734834 8430
567354612 8430


=============================================================================

2. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.

--> select ename from employees where salary<(select min(price) from flights where origin='Los Angeles' and destination='Honolulu');

Output

ENAME
Milo Brooks


=============================================================================

3. Find the name of the pilots certified from some Boeing aircraft.

-->select ename from employees where eid in (select eid from certified where aid in (select aid from aircraft where aname like ‘Boeing%’));

Output

ENAME
John Williams
George Wright
Larry West
Mark Young
Mary Johnson
Betty Adams
James Smith
Michael Miller
Karen Scott
Lisa Walker


=============================================================================

4. For all aircraft with cruising range over 1,000 miles, find the name of the aircraft and the average salary of all pilots certified for this aircraft.

--> select a.aname,avg(e.salary) from employees e,aircraft a, certified c where a.aid=c.aid and c.eid= e.eid and a.crusingrange>1000 group by a.aname;

Output

ANAME AVG(E.SALARY)
Boeing 737-800 191700.25
Tupolev 154 205001.25
SAAB 340 118113.5
Airbus A340-300 217597.667
Embraer ERJ-145 182838.2
Boeing 757-300 189508.571
Boeing 777-300 257973.333
British Aerospace Jetstream 41 220251.333
Airbus A319 201283.667
Boeing 747-400 244776.75
Airbus A320 243198.333
Boeing 767-400ER 209557
Lockheed L1011 242685.75
Boeing 727 273215.5


=============================================================================

5. Find the aid’s of all aircraft than can be used from Los Angels to Chicago.

--> select aid from aircraft where crusingrange>(select min(distance) from flights where origin='Los Angeles' and destination='Chicago');

Output

AID
1
2
3
6
8
9
10
11
12
13
14


=============================================================================

6. Print the enames of pilots who can operate planes with cruising range greater than 3,000 miles, but are not certified by Boeing aircraft.

--> select ename from employees where eid in (select eid from employees where eid in (select eid from certified where aid in (select aid from aircraft where crusingrange>3000)) minus (select eid from employees where eid in (select eid from certified where aid in (select aid from aircraft where aname like 'Boeing%'))));

Output

ENAME
Joseph Thompson
Angela Martinez
Lawrence Sperry
Eric Cooper
William Jones


=============================================================================

7.Find the total amount paid to employees as salaries.

-->select sum(salary) from employees;

Output

SUM(SALARY)
4147377


=============================================================================

8. Find the eid’s of employees who are certified for exactly three aircrafts.

-->select eid from certified group by eid having count(aid)=3;

Output

EID
141582651
390487451
552455318
556784565
573284895


=============================================================================

9. Find the eid’s of employee who make second highest salary.

-->select eid, salary from employees e where 1=(select count(e1.salary) from employees e1 where salary>e.salary);

Output

EID SALARY
269734834 289950


=============================================================================

10.Find the aid’s of all than can be used on non-stop flights from Los Angeles to Boston.

--> select a.aid from aircraft a, flights f where f.origin='Los Angeles' and f.destination ='Boston' and a.crusingrange>f.distance;

Output

AID
1
2
3
8
9
10
11
12


=============================================================================
NOTE : If you find any errors please make a comment

Comments

Post a Comment

Popular posts from this blog

DBMS LAB : PL/SQL Programs

1. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN NUMBER IS STRONG OR NOT. SQL> Declare n number:=&n; n1 number:=n; fact number; sum1 number; r number; i number; Begin sum1:=0; while(n>0) loop r:=mod(n,10); n:=trunc(n/10); i:=1; fact:=1; while(i<=r)loop fact:=fact*i; i:=i+1; end loop; sum1:=sum1+fact; end loop; if(sum1=n1) then dbms_output.put_line(n1||' is strong number'); else dbms_output.put_line(n1||' is not a strong number'); end if; end; / Output: Enter value for n: 145 old 9: n:=&n; new 9: n:=145; 145 is strong number PL/SQL procedure successfully completed. 2. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN STRING IS PALINDROME OR NOT. SQL> Declare len number; s1 varchar2(20) := '&s1'; s2 varchar2(20); Begin len := length(s1); for i...
Read more

COMPUTER NETWORKS PROGRAMS

1. WRITE A PROGRAM TO MANIPULATE THE IP ADDRESS OF A SYSTEM. import java.net.*; public class inetaddrs { public static void main(String args[]) throws UnknownHostException { InetAddress local=InetAddress.getLocalHost(); System.out.println("Name :"+local.getHostName()); System.out.println("Ip Address :"+local.getHostAddress()); } } 2. WRITE A PROGRAM TO OBTAIN THE INFORMATION ABOUT THE HOST, PORTNO. PROTOCOL, FILE AND PATH. import java.lang.*; import java.io.*; import java.net.*; class url { public static void main(String args[]) throws Exception { URL obj=new URL("http://www.dreamtechpress.com:80/index.html"); System.out.println("Host Name is:"+obj.getHost()); System.out.println("Port NO is:"+obj.getPort()); System.out.println("Protocol is:"+obj.getProtocol()); System.out.println("File used is:"+obj.getFile()); System.out.println("Path used is:"+obj.getPat...
Read more

DBMS LAB : Cycle-II

Lab Cycle - II Queries 1. Get Suppliers Names for Suppliers who supply at least one red part. -->select sname from supplier where sno in(select sno from sp where pno in(select pno from parts where color='Red')); Output: SNAME Blake Clark Adams Smith ============================================================================= 2.Get Suppliers Names for Suppliers who do not supply part ‘P2’ --> select sname from supplier where sno in(select sno from sp where pno in(select pno from parts where pno!='P2')); Output: SNAME Blake Clark Adams Jones Smith ============================================================================= 3. Using Group by with Having Clause, Get the part numbers for all the parts supplied by more than one supplier. -->select pno,count(sno) from sp group by pno having count(sno)>1; Output: PNO COUNT(SNO) P4 2 P1 2 P3 3 P6 2 P5 2 =====================================...
Read more