Unit 2

SQL
o SQL stands for Structured Query Language. It is used for storing and managing data
in relational database management system (RDMS).
o It is a standard language for Relational Database System. It enables a user to create,
read, update and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL
as their standard database language.
o SQL allows users to query the database in a number of ways, using English-like
statements.
o
Rules:
SQL follows the following rules:
o Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase.
o Statements of SQL are dependent on text lines. We can use a single SQL statement on
one or multiple text line.
o Using the SQL statements, you can perform most of the actions in a database.
o SQL depends on tuple relational calculus and relational algebra.
SQL process:
o When an SQL command is executing for any RDBMS, then the system figure out the
best way to carry out the request and the SQL engine determines that how to interpret
the task.
o In the process, various components are included. These components can be
optimization Engine, Query engine, Query dispatcher, etc.
o All the non-SQL queries are handled by the classic query engine, but SQL query
engine won't handle logical files.
Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate it when needed.
o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a database.
o SQL allows users to set permissions on tables, procedures, and views.
Advantages of SQL
There are the following advantages of SQL:
High speed
Using the SQL queries, the user can quickly and efficiently retrieve a large amount of records
from a database.
No coding needed
In the standard SQL, it is very easy to manage the database system. It doesn't require a
substantial amount of code to manage the database system.
Well defined standards

Long established are used by the SQL databases that are being used by ISO and ANSI.
Java Try Catch
Portability
SQL can be used in laptop, PCs, server and even some mobile phones.
Interactive language
SQL is a domain language used to communicate with the database. It is also used to receive
answers to the complex questions in seconds.
Multiple data view

Using the SQL language, the users can make different views of the database structure.
SQL Datatype
o SQL Datatype is used to define the values that a column can contain.
o Every column is required to have a name and data type in the database table.
Datatype of SQL:
1. Binary Datatypes
There are Three types of binary Datatypes which are given below:
Data Type Description
binary It has a maximum length of 8000 bytes. It contains fixed-length binary data.
varbinary It has a maximum length of 8000 bytes. It contains variable-length binary data.
image It has a maximum length of 2,147,483,647 bytes. It contains variable-length binary data.
2. Approximate Numeric Data-type:

The subtypes are given below:
Data type From To Description
float -1.79E + 308 1.79E + 308 It is used to specify a floating-point value e.g. 6.2, 2.9 etc.
real -3.40e + 38 3.40E + 38 It specifies a single precision floating point number

3. Exact Numeric Datatype
Data type Description
int It is used to specify an integer value.
smallint It is used to specify small integer value.
bit It has the number of bits to store.
decimal It specifies a numeric value that can have a decimal number.
numeric It is used to specify a numeric value.
4. Character String Datatype

OOPs Concepts in Java
Data Description
type
char It has a maximum length of 8000 characters. It contains Fixed-length non-unicode

characters.
varchar It has a maximum length of 8000 characters. It contains variable-length non-unicode

characters.
text It has a maximum length of 2,147,483,647 characters. It contains variable-length non-

unicode characters.
5. Date and time Datatypes

Datatype Description
date It is used to store the year, month, and days value.
time It is used to store the hour, minute, and second values.
timestamp It stores the year, month, day, hour, minute, and the second value.
SQL Commands
o SQL commands are instructions. It is used to communicate with the database. It is also used
to perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the table, modify
the table, set permission for users.
Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
1. Data Definition Language (DDL)

o DDL changes the structure of the table like creating a table, deleting a table, altering a table,
etc.
o All the command of DDL are auto-committed that means it permanently save all the changes
in the database.
Here are some commands that come under DDL:
o CREATE
o ALTER
o DROP
o TRUNCATE
a. CREATE It is used to create a new table in the database.
Syntax:
15.2M
343
How to find Nth Highest Salary in SQL
1. CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Example:
1.
CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100),

DOB DATE);
b. DROP: It is used to delete both the structure and record stored in the table.
Syntax
1. DROP TABLE table_name;
Example
1. DROP TABLE EMPLOYEE;
c. ALTER: It is used to alter the structure of the database. This change could be either to
modify the characteristics of an existing attribute or probably to add a new attribute.
Syntax:
To add a new column in the table
1. ALTER TABLE table_name ADD column_name COLUMN-definition;
To modify existing column in the table:
1. ALTER TABLE table_name MODIFY(column_definitions....);
EXAMPLE
1. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

2. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));
d. TRUNCATE: It is used to delete all the rows from the table and free the space containing
the table.
Syntax:
1. TRUNCATE TABLE table_name;
Example:
1. TRUNCATE TABLE EMPLOYEE;
2. Data Manipulation Language
o DML commands are used to modify the database. It is responsible for all form of changes in
the database.
o The command of DML is not auto-committed that means it can't permanently save all the
changes in the database. They can be rollback.
Here are some commands that come under DML:
o INSERT
o UPDATE
o DELETE
a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a
table.
Syntax:
1. INSERT INTO TABLE_NAME

2. (col1, col2, col3,.... col N)
3. VALUES (value1, value2, value3, .... valueN);
Or

2. VALUES (value1, value2, value3, .... valueN);
For example:
1. INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");
b. UPDATE: This command is used to update or modify the value of a column in the table.
Syntax:
1.
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [W

HERE CONDITION]
For example:
1. UPDATE students
2. SET User_Name = 'Sonoo'
3. WHERE Student_Id = '3'
c. DELETE: It is used to remove one or more row from a table.
Syntax:
1. DELETE FROM table_name [WHERE condition];
For example:
1. DELETE FROM javatpoint

2. WHERE Author="Sonoo";
3. Data Control Language

DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:

o Grant
o Revoke
a. Grant: It is used to give user access privileges to a database.
Example
1.
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USE

R;
b. Revoke: It is used to take back permissions from the user.
Example
1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE
only.
These operations are automatically committed in the database that's why they cannot be used
while creating tables or dropping them.
Here are some commands that come under TCL:
o COMMIT
o ROLLBACK
o SAVEPOINT
a. Commit: Commit command is used to save all the transactions to the database.
Syntax:
1. COMMIT;
Example:
1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. COMMIT;
b. Rollback: Rollback command is used to undo transactions that have not already been
saved to the database.
Syntax:
1. ROLLBACK;
Example:
1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. ROLLBACK;
c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back
the entire transaction.
Syntax:
1. SAVEPOINT SAVEPOINT_NAME;
5. Data Query Language

DQL is used to fetch the data from the database.
It uses only one command:
o SELECT
a. SELECT: This is the same as the projection operation of relational algebra. It is used to
select the attribute based on the condition described by WHERE clause.
Syntax:
1. SELECT expressions
2. FROM TABLES
3. WHERE conditions;
For example:
1. SELECT emp_name
2. FROM employee
3. WHERE age > 20;
SQL Operator
There are various types of SQL operator:
SQL Arithmetic Operators

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.
Operator Description Example
+ It adds the value of both operands. a+b will give 30
- It is used to subtract the right-hand operand from the left-hand a-b will give 10
operand.
* It is used to multiply the value of both operands. a*b will give 200
/ It is used to divide the left-hand operand by the right-hand a/b will give 2
operand.
% It is used to divide the left-hand operand by the right-hand a%b will give 0
operand and returns reminder.
SQL Comparison Operators:

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.
Exception Handling in Java - Javatpoint
Operator Description Example

= It checks if two operands values are equal or not, if the values are queal (a=b) is not
then condition becomes true. true
!= It checks if two operands values are equal or not, if values are not equal, (a!=b) is true
then condition becomes true.
<> It checks if two operands values are equal or not, if values are not equal (a<>b) is true
> It checks if the left operand value is greater than right operand value, if yes (a>b) is not
then condition becomes true. true
< It checks if the left operand value is less than right operand value, if yes (a<b) is true
>= It checks if the left operand value is greater than or equal to the right (a>=b) is not
operand value, if yes then condition becomes true. true
<= It checks if the left operand value is less than or equal to the right operand (a<=b) is true
value, if yes then condition becomes true.
!< It checks if the left operand value is not less than the right operand value, (a!=b) is not
if yes then condition becomes true. true
!> It checks if the left operand value is not greater than the right operand (a!>b) is true
value, if yes then condition becomes true.
SQL Logical Operators

There is the list of logical operator used in SQL:
Operator Description
ALL It compares a value to all values in another value set.
AND It allows the existence of multiple conditions in an SQL statement.
ANY It compares the values in the list according to the condition.
BETWEEN It is used to search for values that are within a set of values.
IN It compares a value to that specified list value.
NOT It reverses the meaning of any logical operator.
OR It combines multiple conditions in SQL statements.
EXISTS It is used to search for the presence of a row in a specified table.
LIKE It compares a value to similar values using wildcard operator.
SQL Table
o SQL Table is a collection of data which is organized in terms of rows and columns. In
DBMS, the table is known as relation and row as a tuple.
o Table is a simple form of data storage. A table is also considered as a convenient
representation of relations.
Let's see an example of the EMPLOYEE table:
EMP_ID EMP_NAME CITY PHONE_NO
1 Kristen Washington 7289201223
2 Anna Franklin 9378282882
3 Jackson Bristol 9264783838
4 Kellan California 7254728346
5 Ashley Hawaii 9638482678
In the above table, "EMPLOYEE" is the table name, "EMP_ID", "EMP_NAME", "CITY",
"PHONE_NO" are the column names. The combination of data of multiple columns forms a
row, e.g., 1, "Kristen", "Washington" and 7289201223 are the data of one row.
Operation on Table
1. Create table
2. Drop table
3. Delete table
4. Rename table
SQL Create Table

SQL create table is used to create a table in the database. To define the table, you should
define the name of the table and also define its columns and column's data type.
Syntax
Features of Java - Javatpoint
1. create table "table_name"

2. ("column1" "data type",
3. "column2" "data type",
4. "column3" "data type",
5. ...
6. "columnN" "data type");
Example
1. SQL> CREATE TABLE EMPLOYEE (

2. EMP_ID INT NOT NULL,
3. EMP_NAME VARCHAR (25) NOT NULL,
4. PHONE_NO INT NOT NULL,
5. ADDRESS CHAR (30),
6. PRIMARY KEY (ID)
7. );
If you create the table successfully, you can verify the table by looking at the message by the
SQL server. Else you can use DESC command as follows:
SQL> DESC EMPLOYEE;
Field Type Null Key Default Extra
EMP_ID int(11) NO PRI NULL
EMP_NAME varchar(25) NO NULL
PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)
o 4 rows in set (0.35 sec)
Now you have an EMPLOYEE table in the database, and you can use the stored information
related to the employees.
Drop table
A SQL drop table is used to delete a table definition and all the data from a table. When this
command is executed, all the information available in the table is lost forever, so you have to
very careful while using this command.
Syntax
1. DROP TABLE "table_name";
Firstly, you need to verify the EMPLOYEE table using the following command:
1. SQL> DESC EMPLOYEE;
Field Type Null Key Default Extra
EMP_ID int(11) NO PRI NULL
EMP_NAME varchar(25) NO NULL
PHONE_NO NO int(11) NULL
ADDRESS YES NULL char(30)
o 4 rows in set (0.35 sec)
This table shows that EMPLOYEE table is available in the database, so we can drop it as
follows:
1. SQL>DROP TABLE EMPLOYEE;
Now, we can check whether the table exists or not using the following command:
1. Query OK, 0 rows affected (0.01 sec)

As this shows that the table is dropped, so it doesn't display it.
SQL DELETE table

In SQL, DELETE statement is used to delete rows from a table. We can use WHERE
condition to delete a specific row from a table. If you want to delete all the records from the
table, then you don't need to use the WHERE clause.
Syntax
1. DELETE FROM table_name WHERE condition;
Example
Suppose, the EMPLOYEE table having the following records:
EMP_ID EMP_NAME CITY PHONE_NO SALARY
1 Kristen Chicago 9737287378 150000
2 Russell Austin 9262738271 200000
3 Denzel Boston 7353662627 100000
4 Angelina Denver 9232673822 600000
5 Robert Washington 9367238263 350000
6 Christian Los angels 7253847382 260000
The following query will DELETE an employee whose ID is 2.
1. SQL> DELETE FROM EMPLOYEE

2. WHERE EMP_ID = 3;
Now, the EMPLOYEE table would have the following records.

2 Russell Austin 9262738271 200000
If you don't specify the WHERE condition, it will remove all the rows from the table.
1. DELETE FROM EMPLOYEE;
Now, the EMPLOYEE table would not have any records.
SQL SELECT Statement

In SQL, the SELECT statement is used to query or retrieve data from a table in the database.
The returns data is stored in a table, and the result table is known as result-set.
Syntax
1. SELECT column1, column2, ...

2. FROM table_name;
Here, the expression is the field name of the table that you want to select data from.
Use the following syntax to select all the fields available in the table:
Hello Java Program for Beginners
1. SELECT * FROM table_name;
Example:
EMPLOYEE
2 Russell Austin 9262738271 200000

To fetch the EMP_ID of all the employees, use the following query:
1. SELECT EMP_ID FROM EMPLOYEE;
Output
EMP_ID
To fetch the EMP_NAME and SALARY, use the following query:
1. SELECT EMP_NAME, SALARY FROM EMPLOYEE;
EMP_NAME SALARY
Kristen 150000
Russell 200000
Angelina 600000
Robert 350000
Christian 260000
To fetch all the fields from the EMPLOYEE table, use the following query:
1. SELECT * FROM EMPLOYEE
Output

2 Russell Austin 9262738271 200000
SQL INSERT Statement

The SQL INSERT statement is used to insert a single or multiple data in a table. In SQL, You
can insert the data in two ways:
1. Without specifying column name

2. By specifying column name
Sample Table
EMPLOYEE
EMP_ID EMP_NAME CITY SALARY AGE
1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36
1. Without specifying column name

If you want to specify all column values, you can specify or ignore the column values.
Syntax
History of Java

2. VALUES (value1, value2, value 3, .... Value N);
Query
1. INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000, 48);
Output: After executing this query, the EMPLOYEE table will look like:
6 Marry Canada 600000 48
2. By specifying column name

To insert partial column values, you must have to specify the column names.
Syntax

2. [(col1, col2, col3,.... col N)]
3. VALUES (value1, value2, value 3, .... Value N);
Query
1.
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7, 'Jack', 40)
;
Output: After executing this query, the table will look like:

7 Jack null null 40
SQL Update Statement

The SQL UPDATE statement is used to modify the data that is already in the database. The
condition in the WHERE clause decides that which row is to be updated.
Syntax
1. UPDATE table_name
2. SET column1 = value1, column2 = value2, ...
3. WHERE condition;
Sample Table
EMPLOYEE
Updating single record

Update the column EMP_NAME and set the value to 'Emma' in the row where SALARY is
500000.
HTML Tutorial
Syntax
2. SET column_name = value
3. WHERE condition;
Query
1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Emma'
3. WHERE SALARY = 500000;
4 Emma Washington 500000 29
Updating multiple records

If you want to update multiple columns, you should separate each field assigned with a
comma. In the EMPLOYEE table, update the column EMP_NAME to 'Kevin' and CITY to
'Boston' where EMP_ID is 5.
Syntax
2. SET column_name = value1, column_name2 = value2
3. WHERE condition;
Query
1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Kevin', City = 'Boston'
3. WHERE EMP_ID = 5;
Output
5 Kevin Boston 200000 36
Without use of WHERE clause

If you want to update all row from a table, then you don't need to use the WHERE clause. In
the EMPLOYEE table, update the column EMP_NAME as 'Harry'.
Syntax
2. SET column_name = value1;
Query
1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Harry';
Output
1 Harry Chicago 200000 30

2 Harry Austin 300000 26
3 Harry Denver 100000 42
4 Harry Washington 500000 29
5 Harry Los angels 200000 36
6 Harry Canada 600000 48
SQL DELETE Statement

The SQL DELETE statement is used to delete rows from a table. Generally, DELETE
statement removes one or more records form a table.
Syntax
1. DELETE FROM table_name WHERE some_condition;
Sample Table
EMPLOYEE
Deleting Single Record

Delete the row from the table EMPLOYEE where EMP_NAME = 'Kristen'. This will delete
only the fourth row.
Query
1. DELETE FROM EMPLOYEE

2. WHERE EMP_NAME = 'Kristen';
Deleting Multiple Record

Delete the row from the EMPLOYEE table where AGE is 30. This will delete two rows(first
and third row).
Query
1. DELETE FROM EMPLOYEE WHERE AGE= 30;

Delete all of the records
Delete all the row from the EMPLOYEE table. After this, no records left to display. The
EMPLOYEE table will become empty.
Syntax
1. DELETE * FROM table_name;

2. or
3. DELETE FROM table_name;
Query
1. DELETE FROM EMPLOYEE;
Views in SQL
o Views in SQL are considered as a virtual table. A view also contains rows and columns.
o To create the view, we can select the fields from one or more tables present in the database.
o A view can either have specific rows based on certain condition or all the rows of a table.
Sample table:
Student_Detail
STU_ID NAME ADDRESS
1 Stephan Delhi
2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram
Student_Marks
STU_ID NAME MARKS AGE
1 Stephan 97 19
2 Kathrin 86 21
3 David 74 18
4 Alina 90 20
5 John 96 18
1. Creating view
A view can be created using the CREATE VIEW statement. We can create a view from a
single table or multiple tables.
Syntax:
Triggers in SQL (Hindi)
1. CREATE VIEW view_name AS

2. SELECT column1, column2.....
3. FROM table_name
4. WHERE condition;
2. Creating View from a single table

In this example, we create a View named DetailsView from the table Student_Detail.
Query:
1. CREATE VIEW DetailsView AS

2. SELECT NAME, ADDRESS
3. FROM Student_Details
4. WHERE STU_ID < 4;
Just like table query, we can query the view to view the data.
1. SELECT * FROM DetailsView;

Output:
NAME ADDRESS
Stephan Delhi
Kathrin Noida
David Ghaziabad
3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the SELECT
statement.
In the given example, a view is created named MarksView from two tables Student_Detail
and Student_Marks.
Query:
1. CREATE VIEW MarksView AS

2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
3. FROM Student_Detail, Student_Mark
4. WHERE Student_Detail.NAME = Student_Marks.NAME;
To display data of View MarksView:
1. SELECT * FROM MarksView;
NAME ADDRESS MARKS
Stephan Delhi 97
Kathrin Noida 86
David Ghaziabad 74
Alina Gurugram 90
4. Deleting View
A view can be deleted using the Drop View statement.
Syntax
1. DROP VIEW view_name;
Example:
If we want to delete the View MarksView, we can do this as:
1. DROP VIEW MarksView;
SQL Index
o Indexes are special lookup tables. It is used to retrieve data from the database very fast.
o An Index is used to speed up select queries and where clauses. But it shows down the data
input with insert and update statements. Indexes can be created or dropped without affecting
the data.
o An index in a database is just like an index in the back of a book.
o For example: When you reference all pages in a book that discusses a certain topic, you first
have to refer to the index, which alphabetically lists all the topics and then referred to one or
more specific page numbers.
1. Create Index statement

It is used to create an index on a table. It allows duplicate value.
Syntax
1. CREATE INDEX index_name

2. ON table_name (column1, column2, ...);
Example
1. CREATE INDEX idx_name

2. ON Persons (LastName, FirstName);
2. Unique Index statement

It is used to create a unique index on a table. It does not allow duplicate value.
Java Try Catch
Syntax
1. CREATE UNIQUE INDEX index_name
2. ON table_name (column1, column2, ...);
Example
1. CREATE UNIQUE INDEX websites_idx

2. ON websites (site_name);
3. Drop Index Statement

It is used to delete an index in a table.
Syntax
1. DROP INDEX index_name;
Example
1. DROP INDEX websites_idx;
SQL Sub Query

A Subquery is a query within another SQL query and embedded within the WHERE clause.
Important Rule:
o A subquery can be placed in a number of SQL clauses like WHERE clause, FROM clause,
HAVING clause.
o You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with
the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
o A subquery is a query within another query. The outer query is known as the main query, and
the inner query is known as a subquery.
o Subqueries are on the right side of the comparison operator.
o A subquery is enclosed in parentheses.
o In the Subquery, ORDER BY command cannot be used. But GROUP BY command can be
used to perform the same function as ORDER BY command.
1. Subqueries with the Select Statement

SQL subqueries are most frequently used with the Select statement.
Syntax
2
1. SELECT column_name
2. FROM table_name
3. WHERE column_name expression operator
4. ( SELECT column_name from table_name WHERE ... );
Example
Consider the EMPLOYEE table have the following records:
ID NAME AGE ADDRESS SALARY
1 John 20 US 2000.00
2 Stephan 26 Dubai 1500.00
3 David 27 Bangkok 2000.00
4 Alina 29 UK 6500.00
5 Kathrin 34 Bangalore 8500.00
6 Harry 42 China 4500.00
7 Jackson 25 Mizoram 10000.00
The subquery with a SELECT statement will be:
1. SELECT *
2. FROM EMPLOYEE
3. WHERE ID IN (SELECT ID
4. FROM EMPLOYEE
5. WHERE SALARY > 4500);
This would produce the following result:
4 Alina 29 UK 6500.00
2. Subqueries with the INSERT Statement

o SQL subquery can also be used with the Insert statement. In the insert statement, data
returned from the subquery is used to insert into another table.
o In the subquery, the selected data can be modified with any of the character, date functions.
Syntax:
1. INSERT INTO table_name (column1, column2, column3....)

2. SELECT *
3. FROM table_name
4. WHERE VALUE OPERATOR
Example
Consider a table EMPLOYEE_BKP with similar as EMPLOYEE.
Now use the following syntax to copy the complete EMPLOYEE table into the
EMPLOYEE_BKP table.
1. INSERT INTO EMPLOYEE_BKP

2. SELECT * FROM EMPLOYEE
3. WHERE ID IN (SELECT ID
4. FROM EMPLOYEE);
3. Subqueries with the UPDATE Statement

The subquery of SQL can be used in conjunction with the Update statement. When a
subquery is used with the Update statement, then either single or multiple columns in a table
can be updated.
Syntax
1. UPDATE table
2. SET column_name = new_value
4. (SELECT COLUMN_NAME
5. FROM TABLE_NAME
6. WHERE condition);
Example
Let's assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE

table. The given example updates the SALARY by .25 times in the EMPLOYEE table for all
employee whose AGE is greater than or equal to 29.
1. UPDATE EMPLOYEE
2. SET SALARY = SALARY * 0.25
3. WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
4. WHERE AGE >= 29);
This would impact three rows, and finally, the EMPLOYEE table would have the following
records.
1 John 20 US 2000.00
4 Alina 29 UK 1625.00
6 Harry 42 China 1125.00
4. Subqueries with the DELETE Statement

The subquery of SQL can be used in conjunction with the Delete statement just like any other
statements mentioned above.
Syntax
1. DELETE FROM TABLE_NAME

3. (SELECT COLUMN_NAME
4. FROM TABLE_NAME
5. WHERE condition);
Example
Let's assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE

table. The given example deletes the records from the EMPLOYEE table for all EMPLOYEE
whose AGE is greater than or equal to 29.
1. DELETE FROM EMPLOYEE

2. WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
3. WHERE AGE >= 29 );
This would impact three rows, and finally, the EMPLOYEE table would have the following
records.
1 John 20 US 2000.00
SQL Clauses
The following are the various SQL clauses:
1. GROUP BY
o SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY
statement is used with the SQL SELECT statement.
o The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes
the ORDER BY clause.
o The GROUP BY statement is used with aggregation function.
Syntax
1. SELECT column
2. FROM table_name
3. WHERE conditions
4. GROUP BY column
5. ORDER BY column
Sample table:
PRODUCT_MAST
18.2M
299
PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Cpm1 3 25 75
Item7 Com1 5 30 150
Item8 Com1 3 10 30
Item9 Com2 2 25 50
Item10 Com3 4 30 120
Example:
1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY;
Output:
Com1 5
Com2 3
Com3 2
2. HAVING
o HAVING clause is used to specify a search condition for a group or an aggregate.
o Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can
use HAVING function like a WHERE clause.
Syntax:
1. SELECT column1, column2

2. FROM table_name
3. WHERE conditions
4. GROUP BY column1, column2
5. HAVING conditions
6. ORDER BY column1, column2;
Example:

3. GROUP BY COMPANY
4. HAVING COUNT(*)>2;
Output:
Com1 5
Com2 3
3. ORDER BY
o The ORDER BY clause sorts the result-set in ascending or descending order.
o It sorts the records in ascending order by default. DESC keyword is used to sort the records in
descending order.
Syntax:
1. SELECT column1, column2

2. FROM table_name
3. WHERE condition
4. ORDER BY column1, column2... ASC|DESC;
Where
ASC: It is used to sort the result set in ascending order by expression.
DESC: It sorts the result set in descending order by expression.
Example: Sorting Results in Ascending Order

Table:
CUSTOMER
CUSTOMER_ID NAME ADDRESS
12 Kathrin US
23 David Bangkok
34 Alina Dubai
45 John UK
56 Harry US
Enter the following SQL statement:
1. SELECT *
2. FROM CUSTOMER
3. ORDER BY NAME;
Output:
34 Alina Dubai
23 David Bangkok
56 Harry US
45 John UK
12 Kathrin US
Example: Sorting Results in Descending Order

Using the above CUSTOMER table
1. SELECT *
2. FROM CUSTOMER
3. ORDER BY NAME DESC;
Output:
12 Kathrin US
45 John UK
56 Harry US
23 David Bangkok
34 Alina Dubai
SQL Aggregate Functions

o SQL aggregation function is used to perform the calculations on multiple rows of a single
column of a table. It returns a single value.
o It is also used to summarize the data.
Types of SQL Aggregation Function

1. COUNT FUNCTION
o COUNT function is used to Count the number of rows in a database table. It can work on both
numeric and non-numeric data types.
o COUNT function uses the COUNT(*) that returns the count of all the rows in a specified
table. COUNT(*) considers duplicate and Null.
Syntax
1. COUNT(*)
2. or
3. COUNT( [ALL|DISTINCT] expression )
Sample table:
PRODUCT_MAST
PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Cpm1 3 25 75
Item7 Com1 5 30 150
Item8 Com1 3 10 30
Item9 Com2 2 25 50
Item10 Com3 4 30 120

Example: COUNT()
1. SELECT COUNT(*)
2. FROM PRODUCT_MAST;
Output:
10
Example: COUNT with WHERE
1. SELECT COUNT(*)
3. WHERE RATE>=20;
Output:
Example: COUNT() with DISTINCT
1. SELECT COUNT(DISTINCT COMPANY)

Output:
Example: COUNT() with GROUP BY

Output:
Com1 5
Com2 3
Com3 2
Example: COUNT() with HAVING

3. GROUP BY COMPANY
4. HAVING COUNT(*)>2;
Output:
Com1 5
Com2 3
2. SUM Function
Sum function is used to calculate the sum of all selected columns. It works on numeric fields
only.
Syntax
1. SUM()
2. or
3. SUM( [ALL|DISTINCT] expression )
Example: SUM()
1. SELECT SUM(COST)
Output:
670
Example: SUM() with WHERE
1. SELECT SUM(COST)
3. WHERE QTY>3;
Output:
320
Example: SUM() with GROUP BY
1. SELECT SUM(COST)
3. WHERE QTY>3
Output:
Com1 150
Com2 170
Example: SUM() with HAVING
1. SELECT COMPANY, SUM(COST)

3. GROUP BY COMPANY
4. HAVING SUM(COST)>=170;
Output:
Com1 335
Com3 170
3. AVG function
The AVG function is used to calculate the average value of the numeric type. AVG function
returns the average of all non-Null values.
Syntax
1. AVG()
2. or
3. AVG( [ALL|DISTINCT] expression )
Example:
1. SELECT AVG(COST)
Output:
67.00
4. MAX Function
MAX function is used to find the maximum value of a certain column. This function
determines the largest value of all selected values of a column.
Syntax
1. MAX()
2. or
3. MAX( [ALL|DISTINCT] expression )
Example:
1. SELECT MAX(RATE)
30
5. MIN Function
MIN function is used to find the minimum value of a certain column. This function
determines the smallest value of all selected values of a column.
Syntax
1. MIN()
2. or
3. MIN( [ALL|DISTINCT] expression )
Example:
1. SELECT MIN(RATE)
Output:
10
SQL JOIN
As the name shows, JOIN means to combine something. In case of SQL, JOIN means "to
combine two or more tables".
In SQL, JOIN clause is used to combine the records from two or more tables in a database.
Types of SQL JOIN

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
Sample Table
EMPLOYEE
PROJECT
10 Sec
SQL CREATE TABLE
PROJECT_NO EMP_ID DEPARTMENT
101 1 Testing
102 2 Development
103 3 Designing
104 4 Development
1. INNER JOIN
In SQL, INNER JOIN selects records that have matching values in both tables as long as the
condition is satisfied. It returns the combination of all rows from both the tables where the
condition satisfies.
Syntax
1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. INNER JOIN table2
4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. INNER JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Output
EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
2. LEFT JOIN
The SQL left join returns all the values from left table and the matching values from the right
table. If there is no matching join value, it will return NULL.
Syntax

2. FROM table1
3. LEFT JOIN table2
Query

2. FROM EMPLOYEE
3. LEFT JOIN PROJECT
Output
EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL
3. RIGHT JOIN
In SQL, RIGHT JOIN returns all the values from the values from the rows of right table and
the matched values from the left table. If there is no matching in both tables, it will return
NULL.
Syntax

2. FROM table1
3. RIGHT JOIN table2
Query

2. FROM EMPLOYEE
3. RIGHT JOIN PROJECT
Output
EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
4. FULL JOIN
In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join
tables have all the records from both tables. It puts NULL on the place of matches not found.
Syntax

2. FROM table1
3. FULL JOIN table2
Query

2. FROM EMPLOYEE
3. FULL JOIN PROJECT
Output
EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL
PL/SQL Cursor
When an SQL statement is processed, Oracle creates a memory area known as context area.
A cursor is a pointer to this context area. It contains all information needed for processing the
statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information
on a select statement and the rows of data accessed by it.
A cursor is used to referred to a program to fetch and process the rows returned by the SQL
statement, one at a time. There are two types of cursors:
o Implicit Cursors
o Explicit Cursors
1) PL/SQL Implicit Cursors

The implicit cursors are automatically generated by Oracle while an SQL statement is
executed, if you don't use an explicit cursor for the statement.
These are created by default to process the statements when DML statements like INSERT,
UPDATE, DELETE etc. are executed.
10 Sec
Orcale provides some attributes known as Implicit cursor's attributes to check the status of
DML operations. Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and
%ISOPEN.
For example: When you execute the SQL statements like INSERT, UPDATE, DELETE
then the cursor attributes tell whether any rows are affected and how many have been
affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute
can be used to find out whether any row has been returned by the SELECT statement. It will
return an error if there no data is selected.
The following table soecifies the status of the cursor with each of its attribute.
Attribute Description
%FOUND Its return value is TRUE if DML statements like INSERT, DELETE and
UPDATE affect at least one row or more rows or a SELECT INTO statement
returned one or more rows. Otherwise it returns FALSE.
%NOTFOUND Its return value is TRUE if DML statements like INSERT, DELETE and
UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise
it returns FALSE. It is a just opposite of %FOUND.
%ISOPEN It always returns FALSE for implicit cursors, because the SQL cursor is
automatically closed after executing its associated SQL statements.
%ROWCOUNT It returns the number of rows affected by DML statements like INSERT,
DELETE, and UPDATE or returned by a SELECT INTO statement.
PL/SQL Implicit Cursor Example

Create customers table and have records:
1 Ramesh 23 Allahabad 20000
2 Suresh 22 Kanpur 22000
3 Mahesh 24 Ghaziabad 24000
4 Chandan 25 Noida 26000
5 Alex 21 Paris 28000
6 Sunita 20 Delhi 30000
Let's execute the following program to update the table and increase salary of each customer
by 5000. Here, SQL%ROWCOUNT attribute is used to determine the number of rows
affected:
Create procedure:
1. DECLARE
2. total_rows number(2);
3. BEGIN
4. UPDATE customers
5. SET salary = salary + 5000;
6. IF sql%notfound THEN
7. dbms_output.put_line('no customers updated');
8. ELSIF sql%found THEN
9. total_rows := sql%rowcount;
10. dbms_output.put_line( total_rows || ' customers updated ');
11. END IF;
12. END;
13. /
Output:
6 customers updated
PL/SQL procedure successfully completed.
Now, if you check the records in customer table, you will find that the rows are updated.
1. select * from customers;
2) PL/SQL Explicit Cursors

The Explicit cursors are defined by the programmers to gain more control over the context
area. These cursors 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.
Following is the syntax to create an explicit cursor:
Syntax of explicit cursor

Following is the syntax to create an explicit cursor:
1. CURSOR cursor_name IS select_statement;;

Steps:
You must follow these steps while working with an explicit cursor.
1. Declare the cursor to initialize in the memory.
2. Open the cursor to allocate memory.
3. Fetch the cursor to retrieve data.
4. Close the cursor to release allocated memory.
1) Declare the cursor:

It defines the cursor with a name and the associated SELECT statement.
Syntax for explicit cursor decleration
1. CURSOR name IS
2. SELECT statement;
2) Open the cursor:
It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the
SQL statements into it.
Syntax for cursor open:
1. OPEN cursor_name;
3) Fetch the cursor:
It is used to access one row at a time. You can fetch rows from the above-opened cursor as
follows:
Syntax for cursor fetch:
1. FETCH cursor_name INTO variable_list;

4) Close the cursor:
It is used to release the allocated memory. The following syntax is used to close the above-
opened cursors.
Syntax for cursor close:
1. Close cursor_name;
PL/SQL Explicit Cursor Example
Explicit cursors are defined by programmers to gain more control over the context area. It is
defined in the declaration section of the PL/SQL block. It is created on a SELECT statement
which returns more than one row.
Let's take an example to demonstrate the use of explicit cursor. In this example, we are using
the already created CUSTOMERS table.
Create customers table and have records:
Create procedure:
Execute the following program to retrieve the customer name and address.
1. DECLARE
2. c_id customers.id%type;
3. c_name customers.name%type;
4. c_addr customers.address%type;
5. CURSOR c_customers is
6. SELECT id, name, address FROM customers;
7. BEGIN
8. OPEN c_customers;
9. LOOP
10. FETCH c_customers into c_id, c_name, c_addr;
11. EXIT WHEN c_customers%notfound;
12. dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
13. END LOOP;
14. CLOSE c_customers;
15. END;
16. /
Output:
1 Ramesh Allahabad
2 Suresh Kanpur
3 Mahesh Ghaziabad
4 Chandan Noida
5 Alex Paris
6 Sunita Delhi
PL/SQL Exception Handling

What is Exception
An error occurs during the program execution is called Exception in PL/SQL.
PL/SQL facilitates programmers to catch such conditions using exception block in the
program and an appropriate action is taken against the error condition.
There are two type of exceptions:
o System-defined Exceptions
o User-defined Exceptions
PL/SQL Exception Handling

Syntax for exception handling:
Nested Structure in C
Keep Watching
Following is a general syntax for exception handling:
1. DECLARE
2. <declarations section>
3. BEGIN
4. <executable command(s)>
5. EXCEPTION
6. <exception handling goes here >
7. WHEN exception1 THEN
8. exception1-handling-statements
13. ........
14. WHEN others THEN
16. END;
Example of exception handling

Let's take a simple example to demonstrate the concept of exception handling. Here we are
using the already created CUSTOMERS table.
SELECT* FROM COUSTOMERS;
1. DECLARE
2. c_id customers.id%type := 8;
5. BEGIN
6. SELECT name, address INTO c_name, c_addr
7. FROM customers
8. WHERE id = c_id;
9. DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
10. DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
11. EXCEPTION
12. WHEN no_data_found THEN
13. dbms_output.put_line('No such customer!');
15. dbms_output.put_line('Error!');
16. END;
17. /
After the execution of above code at SQL Prompt, it produces the following result:
No such customer!
The above program should show the name and address of a customer as result whose ID is
given. But there is no customer with ID value 8 in our database, so the program raises the
run-time exception NO_DATA_FOUND, which is captured in EXCEPTION block.
Note: You get the result "No such customer" because the customer_id used in the above
example is 8 and there is no cutomer having id value 8 in that table.
If you use the id defined in the above table (i.e. 1 to 6), you will get a certain result. For a
demo example: here, we are using the id 5.
1. DECLARE
2. c_id customers.id%type := 5;
5. BEGIN
6. SELECT name, address INTO c_name, c_addr
7. FROM customers
8. WHERE id = c_id;
9. DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
10. DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
11. EXCEPTION
12. WHEN no_data_found THEN
13. dbms_output.put_line('No such customer!');
15. dbms_output.put_line('Error!');
16. END;
17. /
After the execution of above code at SQL prompt, you will get the following result:
Name: alex
Address: paris
Raising Exceptions
In the case of any internal database error, exceptions are raised by the database server
automatically. But it can also be raised explicitly by programmer by using command RAISE.
Syntax for raising an exception:
1. DECLARE
2. exception_name EXCEPTION;
3. BEGIN
4. IF condition THEN
5. RAISE exception_name;
6. END IF;
7. EXCEPTION
8. WHEN exception_name THEN
9. statement;
10. END;
PL/SQL User-defined Exceptions

PL/SQL facilitates their users to define their own exceptions according to the need of the
program. A user-defined exception can be raised explicitly, using either a RAISE statement
or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
Syntax for user define exceptions
1. DECLARE
2. my-exception EXCEPTION;
PL/SQL Pre-defined Exceptions

There are many pre-defined exception in PL/SQL which are executed when any database rule
is violated by the programs.
For example: NO_DATA_FOUND is a pre-defined exception which is raised when a

SELECT INTO statement returns no rows.
Following is a list of some important pre-defined exceptions:
Exception Oracle SQL Description

Error Code
ACCESS_INTO_NULL 06530 -6530 It is raised when a NULL object is automatically

assigned a value.
CASE_NOT_FOUND 06592 -6592 It is raised when none of the choices in the "WHEN"
clauses of a CASE statement is selected, and there is
no else clause.
COLLECTION_IS_NULL 06531 -6531 It is raised when a program attempts to apply

collection methods other than exists to an
uninitialized nested table or varray, or the program
attempts to assign values to the elements of an
uninitialized nested table or varray.
DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate values are attempted to be

stored in a column with unique index.
INVALID_CURSOR 01001 -1001 It is raised when attempts are made to make a cursor
operation that is not allowed, such as closing an
unopened cursor.
INVALID_NUMBER 01722 -1722 It is raised when the conversion of a character string

into a number fails because the string does not
represent a valid number.
LOGIN_DENIED 01017 -1017 It is raised when s program attempts to log on to the

database with an invalid username or password.
NO_DATA_FOUND 01403 +100 It is raised when a select into statement returns no

rows.
NOT_LOGGED_ON 01012 -1012 It is raised when a database call is issued without

being connected to the database.
PROGRAM_ERROR 06501 -6501 It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor fetches value in a variable

having incompatible data type.
SELF_IS_NULL 30625 -30625 It is raised when a member method is invoked, but

the instance of the object type was not initialized.
STORAGE_ERROR 06500 -6500 It is raised when PL/SQL ran out of memory or

memory was corrupted.
TOO_MANY_ROWS 01422 -1422 It is raised when a SELECT INTO statement returns

more than one row.
VALUE_ERROR 06502 -6502 It is raised when an arithmetic, conversion,

truncation, or size-constraint error occurs.
ZERO_DIVIDE 01476 1476 It is raised when an attempt is made to divide a

number by zero.
PL/SQL Trigger
Trigger is invoked by Oracle engine automatically whenever a specified event occurs.Trigger
is stored into database and invoked repeatedly, when specific condition match.
Triggers are stored programs, which are automatically executed or fired when some event
occurs.
Triggers are written to be executed in response to any of the following events.
o A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

o A database definition (DDL) statement (CREATE, ALTER, or DROP).
o 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.
Advantages of Triggers
These are the following advantages of Triggers:
o Trigger generates some derived column values automatically

o Enforces referential integrity
o Event logging and storing information on table access
o Auditing
o Synchronous replication of tables
o Imposing security authorizations
o Preventing invalid transactions
Creating a trigger:
Syntax for creating trigger:
1. CREATE [OR REPLACE ] TRIGGER trigger_name
2. {BEFORE | AFTER | INSTEAD OF }
3. {INSERT [OR] | UPDATE [OR] | DELETE}
4. [OF col_name]
5. ON table_name
6. [REFERENCING OLD AS o NEW AS n]
7. [FOR EACH ROW]
8. WHEN (condition)
9. DECLARE
10. Declaration-statements
11. BEGIN
12. Executable-statements
13. EXCEPTION
14. Exception-handling-statements
15. END;
Here,
o CREATE [OR REPLACE] TRIGGER trigger_name: It creates or replaces an existing trigger

with the trigger_name.
o {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed.
The INSTEAD OF clause is used for creating trigger on a view.
o {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
o [OF col_name]: This specifies the column name that would be updated.
o [ON table_name]: This specifies the name of the table associated with the trigger.
o [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.
o [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.
o WHEN (condition): This provides a condition for rows for which the trigger would fire. This
clause is valid only for row level triggers.
PL/SQL Trigger Example

Let's take a simple example to demonstrate the trigger. In this example, we are using the
following CUSTOMERS table:
Create table and have records:
Create trigger:
Let's take a program to create 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:
1. CREATE OR REPLACE TRIGGER display_salary_changes

2. BEFORE DELETE OR INSERT OR UPDATE ON customers
3. FOR EACH ROW
4. WHEN (NEW.ID > 0)
5. DECLARE
6. sal_diff number;
7. BEGIN
8. sal_diff := :NEW.salary - :OLD.salary;
9. dbms_output.put_line('Old salary: ' || :OLD.salary);
10. dbms_output.put_line('New salary: ' || :NEW.salary);
11. dbms_output.put_line('Salary difference: ' || sal_diff);
12. END;
13. /
After the execution of the above code at SQL Prompt, it produces the following result.
Trigger created.
Check the salary difference by procedure:
Use the following code to get the old salary, new salary and salary difference after the trigger
created.
1. DECLARE
2. total_rows number(2);
3. BEGIN
4. UPDATE customers
5. SET salary = salary + 5000;
6. IF sql%notfound THEN
7. dbms_output.put_line('no customers updated');
8. ELSIF sql%found THEN
9. total_rows := sql%rowcount;
10. dbms_output.put_line( total_rows || ' customers updated ');
11. END IF;
12. END;
13. /
Output:
Old salary: 20000

New salary: 25000
Salary difference: 5000
Old salary: 22000
New salary: 27000
Old salary: 24000
New salary: 29000
Old salary: 26000
New salary: 31000
Old salary: 28000
New salary: 33000
Old salary: 30000
New salary: 35000
6 customers updated
Note: As many times you executed this code, the old and new both salary is incremented by
5000 and hence the salary difference is always 5000.
After the execution of above code again, you will get the following result.
Old salary: 25000

New salary: 30000
Old salary: 27000
New salary: 32000
Old salary: 29000
New salary: 34000
Old salary: 31000
New salary: 36000
Old salary: 33000
New salary: 38000
Old salary: 35000
New salary: 40000
6 customers updated
Important Points
Following are the two very important point and should be noted carefully.
o OLD and NEW references are used for record level triggers these are not avialable for table
level triggers.
o 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.
Introduction to Relational Calculus in DBMS

Relational calculus in RDBM is referring to the non-procedural query language
that emphasizes on the concept of what to for the data management rather how
to do those. The relational calculus provides descriptive information about the
queries to achieve the required result by using mathematical predicates calculus
notations. It is an integral part of the relational data model. The relational calculus
in DBMS uses specific terms such as tuple and domain to describe the queries.
Some of the other related common terminologies for relational calculus are
variables, constant, Comparison operators, logical connectives, and quantifiers. It

creates the expressions that are also known as formulas with unbound formal
variables.
Types of Relational Calculus in DBMS

In this section, we will discuss the types of relational calculus in DBMS based on
the terms and process of the mathematical description of queries functionalities.
Tuple and domain are the major components of relational calculus. A result tuple
is an assignment of constants to these
Variables that make the formula evaluate to be true. There are two types of
relational calculus available in DBMS
 Tuple relational calculus (TRC)
 Domain relational calculus (DRC)
Both the types of relational calculus are semantically similar for operating in
DBMS data retrieval definitions. We will discuss each type of relational calculus
with some database table examples to represent the syntax and its uses.
TRC
Tuple relational calculus works on filtering the tuples based on the specified
conditions.TRC is the variable range over the tuples and is a type of simple subset
of the first-order logic.TRC considers tuples as equal status as variables, and field
referencing can be used to select the tuple parts. It is represented using letter ‘T’
and conditions with the pipe symbol and enclosing curly braces.
Syntax of TRC:
{T | Conditions)
The TRC syntax supports to denote the Table names or relation names, defining
the tuple variables, and the column names. It uses the ‘.’ operator symbol to
specify the column names with the table name.
TRC specifies the relation names with the Tuple variable name such as ’T’. Syntax
of Relation definition in TRC:
Relation(T)
For example, if the Product is the relation name, it can be denoted as Product(T).
Similarly, TRC has the provision to specify the conditions. The condition is
applicable for a particular attribute or the column.
For instance, if the data need to be represented for the particular product id of
value 10, it can be denoted as T.product_id=10, where T is the tuple variable that
represents the row of the table.
Let us assume the Product table in the database as follows:
Product_id Product Category Product Name Product Unit Price
8 New TV Unit 1 $100

12 Existing TV Cabinet $77
Now to represent the relational calculus to return the product name that has the
product id value as 10 from the product table, it can be denoted as with the tuple
variable T.
T.Product Name | Product(T) AND T.Product_id = 10
This relational calculus predicate describes what to do for getting the resultant
tuple from the database. The result of the tuple relational calculus for the Product
table will be:
Product_id Product Name
10 TV Unit 2
DRC
The domain regional calculus works based on the filtering of the domain and the
related attributes.DRC is the variable range over the domain elements or the filed
values. It is a type of simple subset of first-order logic. It is domain-dependent
compared to TRC is tuple dependent. In DRC the formal variables are explicit for
the relational calculus representations. The domain attributes in DRC can be
represented as C1, C2,…, Cn and the condition related to the attributes can be
denoted as the formula defining the condition for fetching the F(C1, C2, …Cn )
Syntax of DRC in DBMS

{c1, c2,...,cn| F(c1, c2,... ,cn)}
Let us assume the same Product table in the database as follows:
Product_id Product Category Product Name Product Unit Price
12 Existing TV Cabinet $77
DRC for the product name attribute from the Product table needs where the
product id is 10, It will be demoted as:
{< Product Name, Product_id> | ∈ Product ∧ Product_id> 10}
The result of the domain relational calculus for the Product table will be
Product_id Product Name
10 TV Unit 2
Some of the commonly used logical operator notations for DRC are ∧ for AND,∨
for OR, and ┓ for NOT. imilarly, the mathematical symbol ∈ refers to the relation
“is an element of” or known as the set membership.

Unit 2

Uploaded by

Copyright:

Available Formats

Unit 2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Unit 2

Uploaded by

Copyright:

Available Formats

SQL

Well defined standards

Java Try Catch

Multiple data view

Data Type Description

2. Approximate Numeric Data-type:

Data type From To Description

real -3.40e + 38 3.40E + 38 It specifies a single precision floating point number

Data type Description

int It is used to specify an integer value.

smallint It is used to specify small integer value.

bit It has the number of bits to store.

decimal It specifies a numeric value that can have a decimal number.

numeric It is used to specify a numeric value.

4. Character String Datatype

OOPs Concepts in Java

char It has a maximum length of 8000 characters. It contains Fixed-length non-unicode

varchar It has a maximum length of 8000 characters. It contains variable-length non-unicode

text It has a maximum length of 2,147,483,647 characters. It contains variable-length non-

5. Date and time Datatypes

time It is used to store the hour, minute, and second values.

Types of SQL Commands

1. Data Definition Language (DDL)

Here are some commands that come under DDL:

a. CREATE It is used to create a new table in the database.

How to find Nth Highest Salary in SQL

1. CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100),

1. DROP TABLE table_name;

1. DROP TABLE EMPLOYEE;

1. ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

1. ALTER TABLE table_name MODIFY(column_definitions....);

1. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

1. TRUNCATE TABLE table_name;

1. TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

Here are some commands that come under DML:

1. INSERT INTO TABLE_NAME

1. INSERT INTO TABLE_NAME

1. INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [W

c. DELETE: It is used to remove one or more row from a table.

1. DELETE FROM table_name [WHERE condition];

1. DELETE FROM javatpoint

3. Data Control Language

Here are some commands that come under DCL:

a. Grant: It is used to give user access privileges to a database.

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USE

b. Revoke: It is used to take back permissions from the user.

1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4. Transaction Control Language

Here are some commands that come under TCL:

1. DELETE FROM CUSTOMERS

1. DELETE FROM CUSTOMERS

5. Data Query Language

It uses only one command:

SQL Arithmetic Operators

Operator Description Example