example Simple Retrievals in sql

- Project all supply number, name, status and location
listed in the supplier (S) table
SELECT *
From s

- change the order of the projected attributes as supply
number, name, location and status
SELECT sno, sname, city , status
From s

- Get Part Numbers for all parts
SELECT pno
FROM p;

- Get the Part number for all parts currently being
supplied
SELECT pno
FROM spj;

- To remove duplicates
SELECT DISTINCT pno
FROM spj;

Qualified Retrievals

- Get supply numbers for suppliers in Kandy
SELECT sno
FROM s
WHERE city='Kandy‘
- Find the Parts which are located in Colombo and
weight greater than 15
SELECT pno,city,weight
FROM p
WHERE city=‘Colombo' and weight > 15

-Find the suppliers who have status value between 10 and 20
SELECT sname, status
FROM s
WHERE status between 10 and 20

-Get the details of suppliers who are either
located in Colombo or Jaffna
SELECT *
FROM s
WHERE city='colombo' or city='jaffna'
Or
SELECT *
FROM s
WHERE city in ('colombo' , city='jaffna')

- Get the name and its weight in kg for each parts in Colombo
SELECT pname,weight/1000 AS 'Weight( kg)'
FROM p
WHERE city = 'colombo'

Retrieval with Ordering

-Get the supplier number and the status for suppliers in Kandy and arrange them on descending order of status
SELECT sno, status
FROM s
WHERE city ='kandy'
ORDER BY status desc
Note: The default order is the ascending order
DESC – descending order
ASC - ascending order