SQL Queries With Solution

SQL QUERIES 

1- Get full details of all projects.

     

SELECT *
FROM   J;

2- Get full details of all projects in London.

     

SELECT *
FROM   J
WHERE  city='London';

3- Get supplier numbers for suppliers who supply project J1.

   The attributes on the SELECT line are used

   like the PROJECT operator in algebra.

     

-- The attributes on the SELECT line are used
-- like the PROJECT operator in algebra

SELECT snum
FROM   SPJ
WHERE  jnum='J1';

4- Get all shipments where quantity is in the range

   300 to 750 inclusive.

   a handy operator

     

-- a handy operator

SELECT *
FROM   SPJ
WHERE  qty BETWEEN 300 AND 750;

5- Get all part-color/part-city combinations.

   assuming this refers to actual parts

   in the database

     

-- assuming this refers to actual parts
-- in the database
-- we use DISTINCT to remove duplicate rows

SELECT DISTINCT color, city
FROM   P;

6- Get all snum/pnum/jnum triples such that the supplier,

   part and project are co-located.

   note this has nothing to do with shipments

   Tables involved are on the FROM line

     

SELECT snum,pnum,jnum
FROM   S, P, J
WHERE  s.city = p.city
AND    p.city = j.city;

Home                            All Queries                       Next page