4.2.1.1 General SELECT statements

The SELECT statement

Retrieves rows from one or more tables according to given conditions.

General form:

SELECT [ ALL | DISTINCT ]

FROM

[ WHERE ]

[ ORDER BY [DESC]

[ GROUP BY ]

[ HAVING ]

Query 1:

Some SELECT statements on the Case Example

SELECT * <----------------- FROM items;

* -denotes all attributes in the table

Result

Query 2:

SELECT cust#,custname

FROM customers;

Result

Query 3:

SELECT DISTINCT item#

FROM ord_items;

Result

Query 4:

SELECT ord# "Order ", orddate "Ordered On" <---- FROM ord_aug;

In the result set the column headings will appear as “Order” and “Ordered On” instead of ord# and orddate.

Result

Query 5:

SELECT item#, descr

FROM items

WHERE price>2000;

Result

Query 6:

SELECT custname

FROM customers

WHERE city<>'Bombay';

Result

Query 7:

SELECT custname

FROM customers

WHERE UPPER(city)<>'BOMBAY';

Result

Query 8:

SELECT * FROM ord_aug WHERE orddate > '15-AUG-94'; <-----------

Illustrates the use of 'date' fields. In SQL, a separate datatype (eg: date, datetime etc.) is available to store data which is of type date.

Result

Query 9:

SELECT *

FROM ord_items

WHERE qty BETWEEN 100 AND 200;

Result


Query 10:

SELECT custname FROM customers WHERE city IN ('Bombay', 'Madras'); <-------

The conditional expression evaluates to TRUE for those records for which the value of city field is in the list ('Bombay, 'Madras')

Result

Query 11:

SELECT custname FROM customers WHERE custname LIKE 'S%' ; <------------

LIKE 'S%' - 'S' followed by zero or more characters

Result

Query 12:

SELECT *

FROM ord_items

WHERE qty>100 AND item# LIKE 'SW%';

Result

Query 13:

SELECT custname

FROM customers

WHERE city='Bombay' OR city='Madras';

Result

Query 14:

SELECT * FROM customers WHERE city='Bombay' ORDER BY custname; <--------------------

Records in the result set is displayed in the ascending order of custname

Result

Query 15:

SELECT * FROM ord_items ORDER BY item#, qty DESC; <-------------

Display the result set in the ascending order of item#. If there are more than one records with the same item# , they will be displayed in the descending order of qty

Result

Query 16:

SELECT descr, price ORDER BY 2 FROM items ORDER BY 2; <----------------------------

ORDER BY the 2nd attribute (price) in the attribute list of the SELECT clause

Result

Query 17:

SELECT ord#, ord_aug.cust#, custname <---------------- FROM ord_aug, customers WHERE city='Delhi' AND ord_aug.cust# = customers.cust#; <----------------

SELECT statement implementing JOIN operation. JOIN condition

Result

Query 18:

SELECT ord#, customers.cust#, city

FROM ord_aug, customers

WHERE ord_aug.cust# = customers.cust#;

Result

Query 19:

SELECT ord#, customers.cust#, city FROM ord_aug, customers WHERE ord_aug.cust# = customers.cust# (+); <-----------

(+) indicates outer join. Here it is a right outer join as indicated by the (+) after the right side field.

Result