For additional reference, click here.
| 2 .DML – SELECT, INSERT, UPDATE and DELETE statements. |
|
| The SELECT statement |
|
| Retrieves rows from one or more tables according to the conditions provided. |
|
| 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 * <-----------------* -denotes all attributes in the table
FROM items; |
|
| 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 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; <-------------------- |
The records in the result set are displayed in the ascending order of custname |
| Result |
|
|
|
|
Query 15:
SELECT *
FROM ord_items
ORDER BY item#, qty DESC; <------------- |
Displays 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 |
|
|
| Nested SELECT statements |
|
| 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
Examples of Numeric Functions
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;
Result
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:
SELECT item#, SUM(qty)
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
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. |
Query 35: Insert values of item# & descr columns for a new row
INSERT INTO items (item#, descr) <----------
VALUES ('HW4', '132-DMPrinter'); |
Attribute list mentioned since
values are not given for all attributes in the tuple. Here Price
column for the newly inserted tuple takes NULL value. |
Query 36: Inserts a new row which includes a date field
INSERT INTO ord_aug
VALUES(106, '31-AUG-94', 005);
Query 37: Inserts a new row with the date field being specified in non DD-MON-YY format
INSERT INTO ord_aug
VALUES (106, TO_DATE('310894','DDMMYY'), 005);
The UPDATE statement
Updates values of one or more attributes of one or more tuples in a table.
General form:
UPDATE <table-name>
SET <attribute-1 = value-1[, attribute-2 = value-2,...attribute-n = value-n]
[ WHERE <conditional expression>];
Sample UPDATE statements from the Case Example
Query 38: Changes price of item SW1 to 6000
UPDATE items
SET price = 6000
WHERE item# ='SW1';
Query 39:
Changes a wrongly entered item# from HW2 to SW2
UPDATE ord_items
SET item# = 'SW2'
WHERE ord#=104 AND item# = 'HW2';
The DELETE statement
Deletes one or more tuples in a table according to given conditions
General form:
DELETE FROM <table-name>
[ WHERE <conditional expression>];
Sample DELETE statements from the Case Example
Query 40:
Deletes Customer record with Customer Number 004
DELETE FROM customers
WHERE cust# = 004;
DELETE FROM Ord_Items; <---------------------------------------
|
Deletes all rows in Ord_Items Table. The table remains empty after the DELETE operation. |
|
| 3. DDL – CREATE, ALTER, and DROP statements. |
|
DDL statements are those
which are used to create, modify and drop the definitions or
structures of various tables, views, indexes and other elements of the
DBMS.
The CREATE TABLE statement
Creates a new table.
General form:
CREATE TABLE <table-name>
(<table-element (comma)list>*);
* - table element may be attribute with its data-type and size or any integrity constraint on attributes.
Some CREATE TABLE statements on the Case Example
Query:
CREATE TABLE customers
( cust# NUMBER(6) NOT NULL,
custname CHAR(30) ,
city CHAR(20));
- This query Creates a table CUSTOMERS with 3 fields - cust#, custname and city. Cust# cannot be null
Query:
CREATE TABLE ord_sep <--------------
AS SELECT * from ord_aug; |
Creates a new table ord_sep, which has the same
structure of ord_aug. The data in ord_aug is copied to the new table
ord_sep.
|
- This query Creates table ORD_SEP as a cpy of ORD-AUG. Copies structure as well as data.
Query:
CREATE TABLE ord_sep <-------------
AS SELECT * from ord_aug
WHERE 1 = 2; |
Creates
a new table ord_sep, which has the same structure of ord_aug. No data
in ord_aug is copied to the new table since there is no row which
satisfies the 'always false' condition 1 = 2.
|
- This query Creates table ORD_SEP as a copy of
ORD_AUG, but does not copy any data as the WHERE clause is never
satisfied.
The ALTER TABLE statement
Alters the structure of an existing table.
General form:
ALTER TABLE <table-name>
ADD | MODIFY (<table-element (comma)list);
Examples of ALTER TABLE statement.
Query:
ALTER TABLE customers
MODIFY custname CHAR(35); <-------- |
Modifies the data type/size of an attribute in the table |
| - This query changes the custname field to a character field of length 35. Used for modifying field lengths and attributes. |
Query:
ALTER TABLE customers
ADD (phone number(8), <-----------------------
credit_rating char(1)); |
Adds two new
attributes to the Customers table. Here, for existing tuples (if any),
the new attribute will take NULL values since no DEFAULT value is
mentioned for the attribute.
|
- This query adds two new fields - phone & credit_rating to the customers table.
The DROP TABLE statement
DROPS an existing table.
General form:
DROP TABLE <table-name>;
Example:
Query:
DROP TABLE ord_sep;
- The above query drops table ORD_SEP from the database
Creating & Dropping Views
A view is a virtual relation created with attributes from one or more base tables.
SELECT * FROM myview1; at any given time will
evaluate the view-defining query in the CREATE VIEW statement and
display the result.
Query:
CREATE VIEW myview1
AS SELECT
ord#, orddate, ord_aug.cust#, custname
FROM ord_aug, customers
WHERE ord_aug.cust# = customers.cust#;
- This query defines a view consisting of ord#, cust#, and custname using a join of ORD_AUG and CUSTOMERS tables.
Query:
CREATE VIEW myview2 (ItemNo, Quantity)
AS SELECT item#, qty
FROM ord_items;
- This query defines a view with columns item# and
qty from the ORD_ITEMS table, and renames these columns as ItemNo. and
Quantity respectively.
Query:
CREATE VIEW myview3
AS SELECT item#, descr, price
FROM items
WHERE price < 1000
WITH CHECK OPTION; <------------------- |
WITH CHECK OPTION in a CREATE VIEW statement
indicates that INSERTs or UPDATEs on the view will be rejected if they
violate any integrity constraint implied by the view-defining query.
|
- This query defines the view as defined. WITH CHECK
OPTION ensures that if this view is used for updation, the updated
values do not cause the row to fall outside the view.
Query:
DROP VIEW myview1; <---- To drop a view
- this query drops the view MYVIEW1
Creating & Dropping Indexes
Query:
CREATE INDEX i_city <----------------
ON customers (city); |
Creates a new index named i_city. The new index file(table) will
have the values of city column of Customers table
|
Query:
CREATE UNIQUE INDEX i_custname <---
ON customers (custname); |
Creates an index which allows only unique values for custnames
|
Query:
CREATE INDEX i_city_custname <---------
ON customers (city, custname); |
Creates an index based on two fields : city and custname
|
Query:
DROP INDEX i_city; <--------------------
| 4 DCL – GRANT and REVOKE statements. |
|
DCL statements are those
which are used to control access permissions on the tables, indexes,
views and other elements of the DBMS.
GRANTING & REVOKING PRIVILEGES
Query:
GRANT ALL <------------------------
ON customers
TO ashraf; |
Grants all permissions on the table customers to the user who logs in as 'ashraf'.
|
Query:
GRANT SELECT <------------------
ON customers
TO sunil; |
Grants SELECT
permission on the table customers to the user 'sunil'. User 'sunil'
does not have permission to insert, update, delete or perform any
other operation on customers table.
|
Query:
GRANT SELECT
ON customers
TO sunil
WITH GRANT OPTION; <-------------------- |
Enables user 'sunil' to give SELECT permission on customers table to other users. |
Query:
REVOKE DELETE <-------------------------
ON customers
FROM ashraf;
| 5. Recovery and Concurrency |
|
ry DBMS, Recovery and Concurrency are part of the general topic of
transaction management. We shall now discuss the fundamental notion of a
transaction.
|
 |
|
| 5.1 Transaction |
|
| A transaction is a logical unit of work. |
|
| Consider the example below: |
|
| The following is the procedure to transfer an amount of Rs. 100/- from the account of one customer to another: |
|
 |
|
Here,
please note that the single operation "amount transfer" involves two
database updates - updating the record of from_cust and updating the
record of to_cust. Between the two updates, the database would be in
an inconsistent (incorrect; in this example) state. i.e., if only one of
the updates is performed, you cannot look at the database contents
and ascertain whether the amount transfer operation has been completed.
Hence to guarantee the database consistency, it needs to be ensured that
either both updates are performed or none are performed. If any
problem; like a system crash, an overflow error or a violation of an
integrity constraint occurs after one update and before the next update,
then the first update also needs to be undone.
|
|
This is
the case with all transactions. Any transaction would take the database
from one consistent state to another. However, it might not necessarily
preserve the consistency of the database at all intermediate points.
Hence, it is important to ensure that either a transaction executes in
its entirety or is totally cancelled. The set of programs which handles
this is the transaction manager in the DBMS. The transaction manager
uses COMMIT and ROLLBACK operations for ensuring the atomicity of
transactions.
|
|
COMMIT
- The COMMIT operation indicates successful completion of a transaction
which means that the database is in a consistent state and all
updates made by the transaction can now be made permanent. If a
transaction successfully commits, then the system will guarantee that
its updates will be permanently installed in the database even if the
system crashes immediately after the COMMIT.
|
|
ROLLBACK
- The ROLLBACK operation indicates that the transaction has been
unsuccessful which means that all updates done by the transaction till
then need to be undone to bring the database back to a consistent
state. To help to undo the updates that have been done once, a system
log or journal is maintained by the transaction manager. The before-
and after-images of the updated tuples are recorded in the log.
|
|
| The basic properties of transactions could be summarised using the ACID mnemonic- ACID: Atomicity, Consistency, Isolation and Durability. |
|
| Atomicity: |
|
| Every transaction is atomic.
This means that either all the operations in the transaction have to be
performed or none should be performed. |
|
| Consistency: |
|
Transactions
preserve database consistency. This means that a transaction helps to
transform one consistent state of the database into another without
necessarily preserving the consistency at all intermediate points.
|
|
| Isolation: |
|
Transactions
are isolated from one another. In other words, the updates to one
transaction are concealed from all other transactions until it commits
(or rolls back).
|
|
| Durability: |
|
Once a transaction commits, its updates survive in the database even if there is a subsequent system crash.
To read more about transactions, click here. |
| 5.2 Recovery from System Failures |
|
System
failures (also called soft crashes) are those failures like power
outage which affect all transactions in progress, but do not
physically damage the database.
|
|
During a
system failure, the contents of the main memory are lost. Thus the
contents of the database buffers which contain the updates of
transactions are lost. (Note: Transactions do not directly write on to
the database. The updates are written to database buffers and, at
regular intervals, transferred to the database.) At restart, the
system has to ensure that the ACID properties of transactions are
maintained and the database remains in a consistent state. To attain
this, the strategy to be followed for recovery at restart is as
follows:
|
|
 |
Transactions which were in progress at the time of failure have to be undone
at the time of restart. This is needed because the precise state of
such a transaction which was active at the time of failure is no
longer known and hence cannot be successfully completed.
|
|
|
Transactions which had
completed prior to the crash but could not get all their updates
transferred from the database buffers to the physical database have to
redone at the time of restart.
|
|
|
| This recovery procedure is carried out with the help of: |
|
|
An online logfile or journal:
The logfile maintains the before- and after-images of the tuples
updated during a transaction. This helps in carrying out the UNDO and
REDO operations as required. Typical entries made in the logfile are :
|
|
|
|
|
|
Taking a checkpoint at specific intervals - This involves the following two operations: |
|
|
| 1. |
Physically
writing the contents of the database buffers out to the physical
database. Thus during a checkpoint the updates of all transactions,
including both active and committed transactions, will be written to
the physical database.
|
|
| 2. |
Physically writing a
special checkpoint record to the physical log. The checkpoint record
has a list of all active transactions at the time of taking the
checkpoint.
|
|
|
|
|
For additional reference, click here.
| 5.3 Recovery : An Example |
|
 |
|
At the time of restart, T3 and T5 must be undone and T2 and T4 must be redone.
T1 does not enter the recovery procedure at all since
it updates were all written to the database at time tc as part of the
checkpoint proces |
| 5.4 Concurrency |
|
Concurrency
refers to multiple transactions accessing the same database at the
same time. In a system that allows concurrency, there has to be some
control mechanism in place to ensure that concurrent transactions do not
interfere with each other.
|
|
| Three typical problems that can occur due to concurrency are explained here. |
|
|
|
For additional reference, click here.
| 5.5 Locking |
|
| Locking is a solution to problems arising due to concurrency. |
|
Locking of
records can be used as a concurrency control technique to prevent the
above mentioned problems. A transaction acquires a lock on a record
if it does not want the record values to be changed by some other
transaction during a period of time. The transaction releases the lock
after this time.
|
|
| Locks are of two types: |
|
| 1. |
shared (S lock)
|
|
| 2. |
and exclusive (X Lock) |
|
|
|
|
|
| The following figure shows the Lock Compatibility matrix: |
|
 |
|
Normally, locks are implicit.
A FETCH request is an implicit request for a shared lock whereas an
UPDATE request is an implicit request for an exclusive lock.
Explicit lock requests need to be issued if a different
kind of lock is required during an operation. For example, if an X
lock is to acquired before a FETCH it has to be explicitly requested
for. |
|
| 6. Query Optimization |
|
|
|
| 6.1 Overview |
|
When
compared to other database systems, query optimization is a strength of
the relational systems. It can be said so since relational systems by
themselves do optimization to a large extent unlike the other systems
which leave optimization to the programmer. Automatic optimization
done by the relational systems will be much more efficient than manual
optimization due to several reasons like :
|
|
|
|
| In this chapter we shall look into the process of automatic query optimization done by the relational systems. |
|
|
|
|
|
|
|
Takes away DELETE permission on customers table from user 'ashraf'. |
|
|
Drops index i_city |
|
|
No comments:
Post a Comment
Thank for visting this blog . Please Pass on this information to all of Our friends to utilize this oppurtunity.