Assignment 4
1.What are sub queries? Explain existence test with example.
2.What are correlated sub queries?
3.Explain NULLIF and COALESCE.
4.What is aggregation? Give examples to explain aggregate functions.
5.Explain the operators – ANY, ALL, Exists & NOT EXISTS with ex.
6.What are the functions of sub queries?
7.Explain ORDER BY, GROUP BY clause with ex.
8.Explain the functions of having clause. Also explain how it is used with   syntax and diagram.
9.Consider the schema-
  Suppliers( sid: integer,
	     sname: varchar,
	     address: varchar)

  parts( pid: integer,
         pname: varchar,
         color: varchar)

  catalogue ( sid: integer,
	      pid: integer,
	      cost: money)

Write a select statement for the following queries-
a)Find the name of the suppliers who supply some red part.
b)Find the sid of the suppliers who supply some green or red part.
c)Find sid of suppliers who supply some red part or who are in 221 Packer     Street.
d)Find the sid of the supplier who supply every part.
e)Find the sid of the supplier who supply red parts & some green parts.
f)Find the most expensive part supplied by any of the supplier.
g)Find all the parts supplied by a supplier 1002.
h)Find the parts which are the most expensive ones.
i)Find the parts which have no supplier.
j)Find the parts of the most common color.
k)Find the parts with the max number of suppliers.
l)Find the supplier who supply max no of parts.
m)Find the cheapest part supplied by a given suppler 1005.
n)Find the supplier who supplied at least 5 parts.
o)Find the supplier ho supplies no parts.