UNIT – 5

TOPIC

Unit-05/Lecture-01

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

What is table?

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

Remember, a table is the most common and simplest form of data storage in a relational database. Following is the example of a CUSTOMERS table:

+----+----------+-----+-----------+----------+
| ID | NAME  | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai  | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
 
+----+----------+-----+-----------+----------+

What is field?

Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

A field is a column in a table that is designed to maintain specific information about every record in the table.

What is record or row?

A record, also called a row of data, is each individual entry that exists in a table. For example there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table:

+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+

A record is a horizontal entity in a table.

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would consist of the following:

+-----------+
| ADDRESS |
+-----------+
| Ahmedabad |
| Delhi |
| Kota |
| Mumbai |
| Bhopal |
| MP |
| Indore |
+----+------+

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

· NOT NULL Constraint: Ensures that a column cannot have NULL value.

· DEFAULT Constraint: Provides a default value for a column when none is specified.

· UNIQUE Constraint: Ensures that all values in a column are different.

· PRIMARY Key: Uniquely identified each rows/records in a database table.

· FOREIGN Key: Uniquely identified a rows/records in any another database table.

· CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

· INDEX: Use to create and retrieve data from the database very quickly.

Data Integrity:

The following categories of the data integrity exist with each RDBMS:

· Entity Integrity: There are no duplicate rows in a table.

· Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.

· Referential integrity: Rows cannot be deleted, which are used by other records.

· User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain or referential integrity.

References:

1. Korth, Silbertz,Sudarshan, “Fundamental of Database System”, McGraw Hill

2. Elmasri, Navathe, “Fundamentals Of Database Systems”, Pearson Educations

S.NO

RGPV QUESTIONS

Year

Marks

Unit-5/Lecture-02

MySQL

MySQL is an open source SQL database, which is developed by Swedish company MySQL AB. MySQL is pronounced "my ess-que-ell," in contrast with SQL, pronounced "sequel."

MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.

MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user, and robust SQL database server.

History:

· Development of MySQL by Michael Widenius & David Axmark beginning in 1994.

· First internal release on 23 May 1995.

· Windows version was released on 8 January 1998 for Windows 95 and NT.

· Version 3.23: beta from June 2000, production release January 2001.

· Version 4.0: beta from August 2002, production release March 2003 (unions).

· Version 4.01: beta from August 2003, Jyoti adopts MySQL for database tracking.

· Version 4.1: beta from June 2004, production release October 2004.

· Version 5.0: beta from March 2005, production release October 2005.

· Sun Microsystems acquired MySQL AB on 26 February 2008.

· Version 5.1: production release 27 November 2008.

Features:

· High Performance.

· High Availability.

· Scalability and Flexibility Run anything.

· Robust Transactional Support.

· Web and Data Warehouse Strengths.

· Strong Data Protection.

· Comprehensive Application Development.

· Management Ease.

· Open Source Freedom and 24 x 7 Support.

· Lowest Total Cost of Ownership.

MS SQL Server

MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are:

· T-SQL.

· ANSI SQL.

History:

· 1987 - Sybase releases SQL Server for UNIX.

· 1988 - Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.

· 1989 - Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2.

· 1990 - SQL Server 1.1 is released with support for Windows 3.0 clients.

· Aston-Tate drops out of SQL Server development.

· 2000 - Microsoft releases SQL Server 2000.

· 2001 - Microsoft releases XML for SQL Server Web Release 1 (download).

· 2002 - Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server).

· 2002 - Microsoft releases SQLXML 3.0.

· 2005 - Microsoft releases SQL Server 2005 on November 7th, 2005.

Features:

· High Performance.

· High Availability.

· Database mirroring.

· Database snapshots.

· CLR integration.

· Service Broker.

· DDL triggers.

· Ranking functions.

· Row version-based isolation levels.

· XML integration.

· TRY...CATCH.

· Database Mail.

References:

1. Korth, Silbertz,Sudarshan, “Fundamental of Database System”, McGraw Hill

2. Elmasri, Navathe, “Fundamentals Of Database Systems”, Pearson Educations

S.NO	RGPV QUESTIONS	Year	Marks
Q.1
Q.2
Q.3

Unit-05/Lecture-03

ORACLE

It is a very large and multi-user database management system. Oracle is a relational database management system developed by 'Oracle Corporation'.

Oracle works to efficiently manage its resource, a database of information, among the multiple clients requesting and sending data in the network.

It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors.

History:

Oracle began in 1977 and celebrating its 32 wonderful years in the industry (from 1977 to 2009).

· 1977 - Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work.

· 1979 - Version 2.0 of Oracle was released and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI).

· 1981 - RSI started developing tools for Oracle.

· 1982 - RSI was renamed to Oracle Corporation.

· 1983 - Oracle released version 3.0, rewritten in C language and ran on multiple platforms.

· 1984 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc.

· 1985 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc.

· 2007 - Oracle has released Oracle11g. The new version focused on better partitioning, easy migration etc.

Features:

· Concurrency

· Read Consistency

· Locking Mechanisms

· Quiesce Database

· Portability

· Self-managing database

· SQL*Plus

· ASM

· Scheduler

· Resource Manager

· Data Warehousing

· Materialized views

· Bitmap indexes

· Table compression

· Parallel Execution

· Analytic SQL

· Data mining

· Partitioning

unit-04/Lecture-04

SQL - Syntax

SQL SELECT Statement:

SELECT column1, column2....columnN
FROM table_name;

SQL DISTINCT Clause:

SELECT DISTINCT column1, column2....columnN
FROM table_name;

SQL WHERE Clause:

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;

SQL AND/OR Clause:

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
 
 

SQL IN Clause:

SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);

SQL BETWEEN Clause:

SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;

SQL LIKE Clause:

SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };

SQL ORDER BY Clause:

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause:

SELECT SUM(column_name)
FROM table_name
WHERE  CONDITION
GROUP BY column_name;

SQL COUNT Clause:

SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

SQL HAVING Clause:

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

SQL CREATE TABLE Statement:

CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

SQL DROP TABLE Statement:

DROP TABLE table_name;

SQL CREATE INDEX Statement :

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

SQL DROP INDEX Statement :

ALTER TABLE table_name
DROP INDEX index_name;

SQL DESC Statement :

DESC table_name;

SQL TRUNCATE TABLE Statement:

TRUNCATE TABLE table_name;

SQL ALTER TABLE Statement:

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

SQL ALTER TABLE Statement (Rename) :

ALTER TABLE table_name RENAME TO new_table_name;

SQL INSERT INTO Statement:

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

SQL UPDATE Statement:

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];

SQL DELETE Statement:

DELETE FROM table_name
WHERE {CONDITION};

SQL CREATE DATABASE Statement:

CREATE DATABASE database_name;

SQL DROP DATABASE Statement:

DROP DATABASE database_name;

SQL USE Statement:

USE database_name;

SQL COMMIT Statement:

COMMIT;

SQL ROLLBACK Statement:

ROLLBACK;

References:

1. Korth, Silbertz,Sudarshan, “Fundamental of Database System”, McGraw Hill

2. Elmasri, Navathe, “Fundamentals Of Database Systems”, Pearson Educations

Unit-05/Lecture-05

SQL - Using Joins

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

Consider the following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal |  8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+

Now, let us join these two tables in our SELECT statement as follows:

SQL> SELECT ID, NAME, AGE, AMOUNT
 FROM CUSTOMERS, ORDERS
 WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+----+----------+-----+--------+
| ID | NAME | AGE | AMOUNT |
+----+----------+-----+--------+
| 3 | kaushik | 23 | 3000 |
| 3 | kaushik | 23 | 1500 |
| 2 | Khilan | 25 | 1560 |
| 4 | Chaitali | 25 | 2060 |
+----+----------+-----+--------+

Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

SQL Join Types:

There are different types of joins available in SQL:

· INNER JOIN: returns rows when there is a match in both tables.

· LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

· RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

· FULL JOIN: returns rows when there is a match in one of the tables.

· SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

· CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

Syntax:

The basic syntax of UNION is as follows:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
 
UNION
 
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here given condition could be any given expression based on your requirement.

Example:

Consider the following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+

Now, let us join these two tables in our SELECT statement as follows:

SQL> SELECT ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS
 LEFT JOIN ORDERS
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
 SELECT ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS
 RIGHT JOIN ORDERS
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+------+----------+--------+---------------------+

UNIT 5/LECTURE 6

The UNION ALL Clause:

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

The same rules that apply to UNION apply to the UNION ALL operator.

Syntax:

The basic syntax of UNION ALL is as follows:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
 
UNION ALL
 
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here given condition could be any given expression based on your requirement.

Example:

Consider the following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+

Now, let us join these two tables in our SELECT statement as follows:

SQL> SELECT ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS
 LEFT JOIN ORDERS
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
 SELECT ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS
 RIGHT JOIN ORDERS
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

There are two other clauses (i.e., operators), which are very similar to UNION clause:

· SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.

· SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.

he SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.

A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

Syntax:

The basic syntax of NULL while creating a table:

SQL> CREATE TABLE CUSTOMERS(
 ID INT NOT NULL,
 NAME VARCHAR (20) NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR (25) ,
 SALARY DECIMAL (18, 2), 
 PRIMARY KEY (ID)
);

Here, NOT NULL signifies that column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL, which means these columns could be NULL.

A field with a NULL value is one that has been left blank during record creation.

Example:

The NULL value can cause problems when selecting data, however, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results.

You must use the IS NULL or IS NOT NULL operators in order to check for a NULL value.

Consider the following table, CUSTOMERS having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
|  6 | Komal | 22 | MP | |
| 7 | Muffy | 24 | Indore | |
+----+----------+-----+-----------+----------+

Now, following is the usage of IS NOT NULL operator:

SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY
 FROM CUSTOMERS
 WHERE SALARY IS NOT NULL;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
+----+----------+-----+-----------+----------+

Now, following is the usage of IS NULL operator:

SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY
 FROM CUSTOMERS
 WHERE SALARY IS NULL;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 6 | Komal | 22 | MP | |
| 7 | Muffy | 24 | Indore | |
+----+----------+-----+-----------+----------+

UNIT 5/LECTURE 7

SQL - Sub Queries

Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

There are a few rules that subqueries must follow:

· Subqueries must be enclosed within parentheses.

· A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.

· An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.

· Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.

· The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.

· A subquery cannot be immediately enclosed in a set function.

· The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.

Subqueries with the SELECT Statement:

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows:

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
 (SELECT column_name [, column_name ]
 FROM table1 [, table2 ]
 [WHERE])

Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Now, let us check following subquery with SELECT statement:

SQL> SELECT * 
 FROM CUSTOMERS 
 WHERE ID IN (SELECT ID 
 FROM CUSTOMERS 
 WHERE SALARY > 4500) ;

This would produce the following result:

+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+

Subqueries with the INSERT Statement:

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date or number functions.

The basic syntax is as follows:

INSERT INTO table_name [ (column1 [, column2 ]) ]
 SELECT [ *|column1 [, column2 ]
 FROM table1 [, table2 ]
 [ WHERE VALUE OPERATOR ]

Example:

Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to copy complete CUSTOMERS table into CUSTOMERS_BKP, following is the syntax:

SQL> INSERT INTO CUSTOMERS_BKP
 SELECT * FROM CUSTOMERS 
 WHERE ID IN (SELECT ID 
 FROM CUSTOMERS) ;

Subqueries with the UPDATE Statement:

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

The basic syntax is as follows:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
 (SELECT COLUMN_NAME
 FROM TABLE_NAME)
 [ WHERE) ]

Example:

Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.

Following example updates SALARY by 0.25 times in CUSTOMERS table for all the customers whose AGE is greater than or equal to 27:

SQL> UPDATE CUSTOMERS
 SET SALARY = SALARY * 0.25
 WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
 WHERE AGE >= 27 );

This would impact two rows and finally CUSTOMERS table would have the following records:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 125.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 2125.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Subqueries with the DELETE Statement:

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

The basic syntax is as follows:

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
 (SELECT COLUMN_NAME
 FROM TABLE_NAME)
 [ WHERE) ]

Example:

Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.

Following example deletes records from CUSTOMERS table for all the customers whose AGE is greater than or equal to 27:

SQL> DELETE FROM CUSTOMERS
 WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
 WHERE AGE > 27 );

This would impact two rows and finally CUSTOMERS table would have the following records:

+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+

References:

1. Korth, Silbertz,Sudarshan, “Fundamental of Database System”, McGraw Hill

2. Elmasri, Navathe, “Fundamentals Of Database Systems”, Pearson Educations

S.NO

RGPV QUESTION

YEAR

MARKS

UNIT 5/LECTURE 8

Cursors

Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc.

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:

· Implicit cursors

· Explicit cursors

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:

Attribute	Description
%FOUND	Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND	The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN	Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT	Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.

Example:

We will be using the CUSTOMERS table we had created and used in the previous chapters.

Select * from customers;
 
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+

The following program would update the table and increase salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected:

DECLARE 
 total_rows number(2);
BEGIN
 UPDATE customers
 SET salary = salary + 500;
 IF sql%notfound THEN
 dbms_output.put_line('no customers selected');
 ELSIF sql%found THEN
 total_rows := sql%rowcount;
 dbms_output.put_line( total_rows || ' customers selected ');
 END IF; 
END;
/

When the above code is executed at SQL prompt, it produces the following result:

6 customers selected
 
PL/SQL procedure successfully completed.

If you check the records in customers table, you will find that the rows have been updated:

Select * from customers;
 
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+

Explicit Cursors

Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is :

CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

· Declaring the cursor for initializing in the memory

· Opening the cursor for allocating memory

· Fetching the cursor for retrieving data

· Closing the cursor to release allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:

CURSOR c_customers IS
 SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open above-defined cursor as follows:

OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:

CLOSE c_customers;

Example:

Following is a complete example to illustrate the concepts of explicit cursors:

DECLARE
 c_id customers.id%type;
 c_name customers.name%type;
 c_addr customers.address%type;
 CURSOR c_customers is
 SELECT id, name, address FROM customers;
 
 
 
 
 
BEGIN
 OPEN c_customers;
 LOOP
 FETCH c_customers into c_id, c_name, c_addr;
 EXIT WHEN c_customers%notfound;
 dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
 END LOOP;
 CLOSE c_customers;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

1 Ramesh Ahmedabad 
2 Khilan Delhi 
3 kaushik Kota 
4 Chaitali Mumbai 
5 Hardik Bhopal 
6 Komal MP 
 
PL/SQL procedure successfully completed.

UNIT 5/LECTURE 9

Triggers

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:

· A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

· A database definition (DDL) statement (CREATE, ALTER, or DROP).

· A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes:

· Generating some derived column values automatically

· Enforcing referential integrity

· Event logging and storing information on table access

· Auditing

· Synchronous replication of tables

· Imposing security authorizations

· Preventing invalid transactions

Creating Triggers

The syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition) 
DECLARE
 Declaration-statements
BEGIN 
 Executable-statements
EXCEPTION
 Exception-handling-statements
END;

Where,

· CREATE [OR REPLACE] TRIGGER trigger_name: Creates or replaces an existing trigger with the trigger_name.

· {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The

· INSTEAD OF clause is used for creating trigger on a view.

· {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.

· [OF col_name]: This specifies the column name that would be updated.

· [ON table_name]: This specifies the name of the table associated with the trigger.

· [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.

· [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.

· WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.

Example:

To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters:

Select * from customers;
 
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |

The following program creates a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values:

CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
 sal_diff number;
BEGIN
 sal_diff := :NEW.salary - :OLD.salary;
 dbms_output.put_line('Old salary: ' || :OLD.salary);
 dbms_output.put_line('New salary: ' || :NEW.salary);
 dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Trigger created.

Here following two points are important and should be noted carefully:

· OLD and NEW references are not available for table level triggers, rather you can use them for record level triggers.

· If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

· Above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using DELETE operation on the table.

Triggering a Trigger

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in CUSTOMERS table, above create trigger display_salary_changes will be fired and it will display the following result:

Old salary:
New salary: 7500
Salary difference:

Because this is a new record so old salary is not available and above result is coming as null. Now, let us perform one more DML operation on the CUSTOMERS table. Here is one UPDATE statement, which will update an existing record in the table:

UPDATE customers
SET salary = salary + 500
WHERE id = 2;

When a record is updated in CUSTOMERS table, above create trigger display_salary_changes will be fired and it will display the following result:

Old salary: 1500
New salary: 2000
Salary difference: 500
 
 

References:

1. Korth, Silbertz,Sudarshan, “Fundamental of Database System”, McGraw Hill

2. Elmasri, Navathe, “Fundamentals Of Database Systems”, Pearson Educations

S.NO	RGPV QUESTION	YEAR	MARKS
Q.1
Q.2

Setting of page

1. Page no. at top in the center.

2. Theme font -Calibri

3. Main text font size-12

4. All headings in bold (12)

5. Top centre headings font size-14

6. Page A-4 size

7. Header and footer -0

8. margin -left (1.25), right (1)

9. Line spacing-1.00