DML – SELECT, INSERT, UPDATE and DELETE statements.

The SELECT statement

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

SELECT [ ALL | DISTINCT ] <attribute (comma)list> FROM <table (comma)list>

[ WHERE <conditional expression>] [ ORDER BY [DESC] <attribute list> [ GROUP BY <attribute (comma)list>] [ HAVING <conditional expression>]

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" <----

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

FROM ord_aug;

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

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

SQL allows nesting of SELECT statements. In a nested SELECT statement the inner SELECT is evaluated first and is replaced by its result to evaluate the outer SELECT statement.

Query 20:

SELECT item#, descr, price <--- FROM items

WHERE price > (SELECT AVG(price) FROM items); <---

Outer SELECT statement

Inner SELECT statement

Result Query 21:

SELECT cust#, custname <--- FROM customers

WHERE city = ( SELECT city FROM customers WHERE custname='Shah');

Here the outer SELECT is evaluated as SELECT cust#, custname

FROM customers

WHERE city = "BOMBAY"

Result Arithmetic Expressions + - * / ( )

Arithmetic functions are allowed in SELECT and WHERE clauses. Query 22:

SELECT descr, price, price*0.1 "discount" FROM items

WHERE price >= 4000 ORDER BY 3;

Result Query 23: SELECT descr

FROM items, ord_items WHERE price*qty > 250000

and items.item# = ord_items.item#; Result

Numeric Functions Query 24:

SELECT qty, ROUND(qty/2,0) "qty supplied" FROM ord_items

WHERE item#='HW2'; Result

Query 25:

SELECT qty, TRUNC(qty/2,0) "qty supplied" FROM ord_items

WHERE item#='HW2'; Result

MOD(n,m) SQRT(n) ROUND(n,m) TRUNC(n,m)

'm' indicates the number of digits after decimal points in the result. Date Arithemetic

Date + No. of days Date - No. of days

Date – Date

Query 26:

SELECT ord#, orddate+15 "Supply by" FROM ord_aug;

Result

Date Functions

MONTHS_BETWEEN(date1, date2) ADD_MONTHS(date, no. of months) SYSDATE

Returns system date. Query 27:

SELECT ord#,

MONTHS_BETWEEN(SYSDATE,orddate) FROM ord_aug;

Query 28:

SELECT TO_CHAR(orddate,' DD/MM/YYYY') <---

FROM ord_aug;

Converts the value of the date field orddate to character string of the format DD/MM/YYYY

Result Note:

DD - day of month (1-31) D - day of week (1-7) DAY - name of day MM - month (01-12) MONTH - name of month

MON - abbreviated name of month HH:MI:SS - hours:minutes:seconds fm - fill mode : suppress blank padding

Character Expressions & Functions || - Concatenate operator

Query 29:

SELECT custname || ' - ' || city FROM customers;

Result

Examples of Character Functions: INITCAP(string)

UPPER(string) LOWER(string)

SUBSTR(string,start,no. of characters) Group Functions

Group functions are functions which act on the entire column of selected rows.

Query 30:

SELECT SUM(qty), AVG(qty) <--- FROM ord_items

WHERE item#='SW1';

SUM and AVG are examples of Group Functions. They compute the sum/average of qty values of all rows where

item#='SW1'.

Result

Examples of Group Functions: SUM AVG COUNT MAX MIN Query 31:

SELECT item#, SUM(qty) FROM ord_items

GROUP BY item#; <---

GROUP BY clause used to group rows according to the value of item# in the result. SUM function acts individually on each group of rows.

Result Query 32:

FROM ord_items GROUP BY item#

HAVING SUM(qty)>100; <---

HAVING clause used to apply the condition to be applied on the grouped rows and display the final result.

Result Query 33:

SELECT item#, SUM(qty) FROM ord_items

GROUP BY item# HAVING COUNT(*)>2; Result

The INSERT statement

Inserts one or more tuples in a table. General forms:

To insert a single tuple

INSERT INTO <table-name> [<attribute (comma)list>] VALUES <value list>;

To insert multiple tuples

INSERT INTO <table-name> [<attribute (comma)list>] SELECT [ ALL | DISTINCT ] <attribute (comma)list> FROM <table (comma)list>*

[ WHERE <conditional expression>]; * - list of existing tables

Sample INSERT statements from the Case Example Query 34: Insert all values for a new row

INSERT INTO customers <--- VALUES (006, 'Krishnan', 'Madras');

Inserts a single row in Customers Table. Attribute list need not be mentioned if values are given for all attributes in the tuple.