The simplest form of select statement has the following general syntax:
select [distinct] expression {, expression}
from tablename [corr_name] {, tablename [corr_name]}
[where search_condition];
(1) Find aid values and names of agents that are
based in New York.
SQL> select aid, aname
from agents
where city = 'New York';
AID ANAME
--- -------------
a01 Smith
a04 Gray
(2) Print all rows in Customers table.
SQL> select *
from customers;
CID CNAME CITY DISCNT
---- ------------- -------------------- ----------
c001 Tiptop Duluth 10
c002 Basics Dallas 12
c003 Allied Dallas 8
c004 ACME Duluth 8
c006 ACME Kyoto 0
(3) Get pid values for parts for which orders have
been placed. Eliminate duplicate answers.
SQL> select distinct pid
from orders;
PID
---
p01
p02
p03
...
...
7 rows selected.
(4) String Comparisons: Get customers whose name begin with
letter "A".
SQL> select *
from customers
where cname like 'A%';
CID CNAME CITY DISCNT
---- ------------- -------------------- ----------
c003 Allied Dallas 8
c004 ACME Duluth 8
c006 ACME Kyoto 0
Underscore(_) : wildcard for any single character
Percent(%) : wildcard for any sequence of zero or more
characters.
(5) Get cname, aname pairs such that the customer
has placed an order through the agent.
SQL> select distinct cname, aname
from customers, orders, agents
where customers.cid = orders.cid and
orders.aid = agents.aid;
CNAME ANAME
------------- -------------
ACME Brown
ACME Smith
...
...
10 rows selected.
(6) For each order, get ordno, cid, aid, pid
values along with profit made on that order.
Profit is calculated by selling price
minus customer discount and agent commission.
SQL>
select ordno, x.cid, x.aid, x.pid,
.40*(x.qty*p.price) -
.01*(c.discnt+a.percent)*(x.qty*p.price)
profit
from orders x, customers c, agents a, products p
where c.cid = x.cid and
a.aid = x.aid and
p.pid = x.pid;
ORDNO CID AID PID PROFIT
---------- ---- --- --- ----------
1011 c001 a01 p01 120
1012 c001 a01 p01 120
1019 c001 a02 p02 48
1025 c001 a05 p07 200
1017 c001 a06 p03 150
...
...
16 rows selected.
(7) Get all pairs of cids for customers based in
the same city.
SQL> select c1.cid, c2.cid
from customers c1, customers c2
where c1.city = c2.city and c1.cid < c2.cid;
CID CID
---- ----
c002 c003
c001 c004
(8) Get pid values for products that have been
ordered by at least two customers.
SQL> select distinct x1.pid
from orders x1, orders x2
where x1.pid = x2.pid and x1.cid < x2.cid;
PID
---
p01
p03
p05
p07
(9) Get cid values for customers who order a product
for which an order is placed by agent "a06".
SQL> select distinct y.cid
from orders x, orders y
where y.pid = x.pid and x.aid = 'a06';
CID
----
c001
c002
c004
c006
(10) Get cid values of customers whose discount is between
5 and 10.
SQL> select cid
from customers
where discnt between 5 and 10;
CID
----
c001
c003
c004