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


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



4. Get supplier numbers for suppliers with status value less the current max status value.

-->select sno from supplier where status<(select max(status) from supplier);

Output

SN
S1
S2
S4


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

5. Get the Project numbers, whose parts are not in Red Color, from London.

--> select jno from projects where jno not in (select jno from spj where pno in (select pno from parts where color='Red' and city='London'));

Output

JN
J5
J6


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

6. Get the suppliers located from the same city.

--> Select distinct s.sno,s.city from supplier s,supplier s1 where s.city>s1.city;

Output

SN CITY
S2 Paris
S3 Paris
S4 London
S1 London


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

7. Get the suppliers, who does not supply any part.

-->select sno,sname from supplier where sno not in(select sno from sp);

Output

No rows selected

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

8. Find the pnames of parts supplied by London Supplier and by no one else.

--> select pname from parts where pno in (select pno from sp where sno in(select sno from supplier where city='London'));

Output

PNAME
Nut
Cog


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

9. Find the sno’s of suppliers who charge more for some part than the average cost of all the parts.

--> select sno from supplier where sno in (select sno from sp where pno in (select pno from parts where cost>(select avg(cost) from parts)));

Output:
SN
S3
S5
S2


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

10. Find the sid’s of suppliers who supply only red parts.

--> select sno from sp minus select sno from sp where pno not in (select pno from parts where color='Red');

Output

SN
S1
S4


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


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