Unit-2(Part-2)

SQL
o SQL stands for Structured Query Language. It is used for storing and managing data in relational database
management system (RDMS). In RDBMS data stored in the form of the tables.
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 several ways, using English-like statements.
o SQL is mostly used by engineers in software development for data storage. Nowadays, it is also used by
data analyst for following reason:

SQL Statement Rules:

SQL follows the following rules:

o Structure query language is not case sensitive. Generally, keywords of SQL are written in uppercase.
o Every SQL statement should end with a semicolon.
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.

How does SQL process works:

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, classic, etc.
o All the non-SQL queries are handled by the classic query engine, but SQL query engine won't handle
logical files.

In the above diagrammatic representation following steps are performed:

o Parsing: In this process, Query statement is tokenized.

o Optimizing: In this process, SQL statement optimizes the best algorithm for byte code.
o From: In SQL statement, from keyword is used to specify the tables from which data fetched.
o Where: Where keyword works like conditional statement in SQL.
o Join: A Join statement is used to combine data from more than one tables based on a common field
among them.
 o Group by: It is used to group the fields by different records from table(s).
o Having: Having clause is also works like conditional statement in SQL. It is mostly used with group by
clause to filter the records.
o Order by: This clause is used to sort the data in particular order by using "ASC" for ascending and
"DESC" for descending order.
o Select: This "Data Manipulation Language" statement is used to get the data from the database.
o Limit: It is used to specify the how many rows returned by the SQL select statement.

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 Description
Type

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 Datatype :

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

The subtypes are given below:

 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

The subtypes are given below:

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

The subtypes are given below:

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 four types of SQL commands: DDL, DML, DCL, TCL.
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.

Following are the some commands that come under DDL:

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

Syntax:

1. CREATE TABLE TABLE_NAME (COLUMN_NAMES DATATYPES [ ...]);

In above statement, TABLE_NAME is the name of the table, COLUMN_NAMES is the name of the columns
and DATATYPES is used to define the type of data.

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: To DROP a table permanently from memory

1. DROP TABLE table_name [cascade constraint];

The cascade constraint is an optional parameter which is used for tables which have foreign keys that reference
the table being dropped. If cascade constraint is not specified and used attempt to drop a table that has records in
a child table, then an error will occur. So by using cascade constraints, all child table foreign keys are dropped.

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.
Following are the list of modifications that can be done using ALTER command.

o With the use of ALTER commands we can add or drop one or more columns form existing tables.
o Increase or decrease the existing column width by changing the data type
o Make an existing mandatory column to optional.
o Enable or disable the integrity constraints in a table. We can also add, modify or delete the integrity
constraints from a table.
o We can also specify a default value for existing column in a table.

Adding new columns in Table:

With the use of ALTER table command we can add new columns existing table.

Syntax: To add a new column in the table

1. ALTER TABLE table_name ADD column_name column-definition;

In the above syntax, where table_name corresponds to the name of the table, column-definition corresponds to
the valid specifications for a column name and data type.

EXAMPLE:

1. ALTER TABLE STU_DETAILS ADD (ADHAR_NUM VARCHAR2 (15));

Syntax: To ADD a multiple column from a table.

ALTER TABLE table_name ADD column_name1, column_name2;

Example:

1. ALTER TABLE STU_DETAILS ADD ADHAR_NUM, NAME;

Adding constraints in a Table:

You can also add constraints to an existing table. For example: If you forget to add a primary key constraint to a
table during creation, you can add it using the ALTER TABLE statement.

Syntax: To ADD a constraint from a table.

1. ALTER TABLE table_name ADD (column_name column-

definition CONSTRAINT constraint_name);

Example:

1. ALTER TABLE STU_DETAILS ADD (CONSTRAINT PK_STU_DETAILS PRIMARY KEY (STU_

ID);

Following points should be kept in mind while adding new columns/relationships to existing tables.

o No need for parentheses if you add only one column or constraints.

o You can add a column at any time if NULL is not specified. You can add a new column with NOT NULL
if the table is empty.

Modifying Column using ALTER:

With the use of ALTER table we can modify column and constraint in the existing table. These statements can
increase or decrease the column widths and changing a column from mandatory to optional.

Syntax:

1. ALTER TABLE table_name MODIFY (column definitions....);

Example:

1. ALTER TABLE STU_DETAILS MODIFY (ADHAR_NUM VARCHAR2 (20));

SQL does not allow column widths to be reduced even if all column values are of valid length. So the values
should be set to NULL to reduce the width of the columns. It is also not possible to reduce the width of the
ADHAR_NUM column from 18 to 12 even if all values in the ADHAR_NUM column are less than 12 characters,
unless all al values in the name column are null. You can modify the column form NULL to NOTNULL
constraints if there is no record in that column in the table.

Example:

1. ALTER TABLE STU_DETAILS MODIFY (ADHAR_NUM VARCHAR2 (20) NOT NULL);

Drop column and constraints using ALTER

You cannot only modify columns but you can also drop them entirely if it is no longer required in a table. Using
drop statement in alter command we can also remove the constraints form the table.

Syntax: To drop a column from a table.

1. ALTER TABLE table_name DROP COLUMN column_name;

Example:

1. ALTER TABLE STU_DETAILS DROP COLUMN ADHAR_NUM;

Syntax: To drop a multiple column from a table.

1. ALTER TABLE table_name DROP COLUMN column_name1, column_name2;

Example:

1. ALTER TABLE STU_DETAILS DROP COLUMN ADHAR_NUM, NAME;

Syntax: To drop a constraint from a table.

1. ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example:

1. ALTER TABLE STU_DETAILS DROP CONSTRAINT FK_STU_DETAILS;

Following points should be kept in mind while deleting columns/associations:

o You cannot drop columns in a table. If you want to drop a column from a table, the deletion is permanent
so you cannot undo the column if you accidentally drop the wrong column.
o You cannot drop a column whose username is SYS.
 o If you want to drop a primary key column unless you drop the foreign keys that belong to it then use
cascade keyword for this.

Example:

1. ALTER TABLE STU_DETAILS DROP PRIMARY KEY CASCADE;

o You can also enable or disable the key constraint in a table. It can be done in various situations such as:
when loading large amount of data into table, performing batch operations, migrating the organizations
legacy data.

Example: To disable constraint

1. ALTER TABLE STU_DETAILS DISABLE CONSTRAINT FK_STU_DETAILS;

Example: To Enable constraint

1. ALTER TABLE STU_DETAILS ENABLE CONSTRAINT FK_STU_DETAILS;

o Instead of dropping a column in a table, we can also make the column unused and drop it later on. It
makes the response time faster. After a column has been marked as unused, the column and all its contents
are no longer available and cannot be recovered in the future. The unused columns will not be retrieved
using Select statement

Example:

1. ALTER TABLE STU_DETAILS SET UNUSED COLUMN ADHAR_NUM;

RENAMING TABLE

SQL provides the facility to change the name of the table by using a ALTER TABLE statement.

Syntax:

1. ALTER TABLE <OLD_TABLENAME> Rename to <NEW_TABLENAME>;

Example:

1. ALTER TABLE STU_NAME Rename to STUDENT_NAME;

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;

e. Rename: It is used to rename the table.

Syntax:

1. Rename <OLD_TABLENAME> to <NEW_TABLENAME>;

In the above syntax, Rename is a command, <OLD_TABLENAME> is the name of the table and
<NEW_TABLENAME> is the name that you have changed.

Example:

1. Rename STU_NAME to STUDENT_NAME;

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.

Following are the some commands that come under DML:

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table. To insert a
new row into a table you must be your on schema or INSERT privilege on the table.

Following are the list of points should be considered while inserting the data into tables.

o SQL uses all the columns by default if you do not specify the column name while inserting a row.
o The number of columns in the list of column name must match the number of values that appear in
parenthesis after the word "values".
o The data type for a column and its corresponding value must match.

Syntax: To add row in a table

1. INSERT INTO TABLE_NAME

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

Or

1. INSERT INTO TABLE_NAME

2. VALUES (value1, value2, value3, .... valueN);

In the above syntax, TABLE_NAME is the name of the table in which the data will be inserted. The (col1, col2,
col3, col N) are optional and name of the columns in which values will be inserted. The value1 corresponds to the
value of be inserted in col1 and similarly value2 corresponds to the value of be inserted in col2 and so on.

For example:

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

Syntax: To add multiple rows in a table

1. INSERT INTO TABLE_NAME

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

For example:

1. INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS"), ("Raman", "DBMS"), ("Pr
iya", "DBMS");
b. UPDATE: This command is used to update or modify the value of a column in the table.

Syntax: To update record in a table

1. UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITI

ON]

In the above syntax, table_name is the name of the table, the column_name is the name of column in the table to
be modified, and value1 corresponds to the valid SQL values. The "WHERE" is a condition that restricts the rows
updated for which the specified condition is true. If condition is not specified is not defined then SQL updates all
the rows in the table. It contains comparison and logical operators etc.

The following the list of points should be remembered while executing the UPDATE statement.

o It references only one table.

o In the SET clause atleast one column must be assigned an expression for the update statement,
o In the where clause you could also give multiple conditions for update statement.

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. To delete rows from the table, it must be in your
schema or you must have delete privilege.

Syntax: To Delete a record from table

1. DELETE FROM table_name [WHERE condition];

In the above syntax, condition is used in the where clause to filter the records that are actually being removed.
You can remove zero or more rows from a table. If you do not use where condition then DELETE statement will
remove all the rows from the table. You can also use one or multiple conditions in WHERE clause.

For example:

1. DELETE FROM javatpoint

2. WHERE Author="Sonoo";

d. 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: It is used for retrieve the records from table

1. SELECT expressions
2. FROM TABLES
3. WHERE conditions;

For example:

1. SELECT emp_name
2. FROM employee
3. WHERE age > 20;

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.

Following are the some commands that come under DCL:

Syntax:

1. GRANT <obj_priv> ON <obj_name> To <username>;

In the above syntax, obj_priv> is the DML statement like Insert, Delete , update and Select and <obj_name> is a
table, view etc. and username is the name of the authorized user.

Example

1. GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

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

Syntax:

1. REVOKE <obj_priv> ON <obj_name> FROM <username>;

In the above syntax, obj_priv> is the DML statement like Insert, Delete , update and Select and <obj_name> is a
table, view etc. and username is the name of the user from whom the permission is revoked.

Example

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

4. Transaction Control Language

Transactions are atomic i.e. either every statement succeeds or none of statement succeeds. There are number of
Transaction Control statements available that allow us to control this behavior. These statements ensure data
consistency. 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.

Following are the some commands that come under TCL:

a. Commit: Commit command is used to save all the transactions to the database. It makes your changes
permanent and ends the transaction.

Syntax: To permanently save the changes

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.
Rollback also serves to end the current transaction and begin a new one.
Consider a Situation where you have completed a series of INSERT, UPDATE or DELETE statements but have
not yet explicitly committed them and yiu encounter a problem such as computer failure, then SQL will
automatically rollback any uncommitted work.

Syntax: To remove the changes

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;

In the above syntax, SAVEPOINT_NAME is the name given to savepoint.

To selectively ROLLBACK a group of statements within a large transaction use the following command is used.

1. Rollback TO <save_point_name>

Example:

1. DELETE FROM CUSTOMERS WHERE AGE = 15;

2. SAVEPOINT A;
3. DELETE FROM CUSTOMERS WHERE AGE = 35;
4. ROLLBCAK TO A;

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 operand. a-b will give
10

* 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 operand. a/b will give
2

% It is used to divide the left-hand operand by the right-hand operand and returns a%b will
reminder. give 0

SQL Comparison Operators:

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

Operator Description Example

= It checks if two operands values are equal or not, if the values are queal then (a=b) is not
condition becomes true. true

!= It checks if two operands values are equal or not, if values are not equal, then (a!=b) is true
condition becomes true.

<> It checks if two operands values are equal or not, if values are not equal then (a<>b) is
condition becomes true. 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 then (a<b) is true
condition becomes true.

>= It checks if the left operand value is greater than or equal to the right operand (a>=b) is not
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
value, if yes then condition becomes true. true

!< It checks if the left operand value is not less than the right operand value, if (a!=b) is not
yes then condition becomes true. true

!> It checks if the left operand value is not greater than the right operand value, (a!>b) is true
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.

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.

Advantages of View:

1. Complexity: Views help to reduce the complexity. Different views can be created on the same base table
for different users.
2. Security: It increases the security by excluding the sensitive information from the view.
3. Query Simplicity: It helps to simplify commands from the user. A view can draw data from several
different tables and present it as a single table.
4. Consistency: A view can present a consistent, unchanged image of the structure of the database. Views
can be used to rename the columns without affecting the base table.
5. Data Integrity: If data is accessed and entered through a view, the DBMS can automatically check the
data to ensure that it meets the specified integrity constraints.
6. Storage Capacity: Views take very little space to store the data.
7. Logical Data Independence: View can make the application and database tables to a certain extent
independent.

Disadvantages of View:

The DML statements which can be performed on a view created using single base table have certain restrictions
are:

1. You cannot INSERT if the base table has any not null column that do not appear in view.
2. You cannot INSERT or UPDATE if any of the column referenced in the INSERT or UPDATE contains
group functions or columns defined by expression.
3. You can't execute INSERT, UPDATE, DELETE statements on a view if with read only option is enabled.
4. You can't be created view on temporary tables.
5. You cannot INSERT, UPDATE, DELETE if the view contains group functions GROUP BY, DISTINCT
or a reference to a psuedocolumn rownum.
6. You can't pass parameters to the SQL server views.
7. You can't associate rules and defaults with views.
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:

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;

Significance of Views:

Views are highly significant, as they can provide advantages over tasks. Views can represent a subset of data
contained in a table. Consequently they can limit the degree of exposure of the underlying base table to the outer
world. They are used for security purpose in database and act as an intermediate between real table schemas and
programmability. They act as aggregate tables.

Types of Views:

There are two types of views.

1. Join View: A join view is a view that has more than one table or view in its from clause and it does not
use any Group by Clause, Rownum, Distinct and set operation.
2. Inline View: An inline view is a view which is created by replacing a subquery in the from clause which
defines the data source that can be referenced in the main query. The sub query must be given an alias
for efficient working.

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.

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

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:

ID NAME AGE ADDRESS SALARY

4 Alina 29 UK 6500.00

5 Kathrin 34 Bangalore 8500.00

7 Jackson 25 Mizoram 10000.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
3. WHERE VALUE OPERATOR
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.

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 1625.00

5 Kathrin 34 Bangalore 2125.00

6 Harry 42 China 1125.00

7 Jackson 25 Mizoram 10000.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

2. WHERE VALUE OPERATOR
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.

ID NAME AGE ADDRESS SALARY

1 John 20 US 2000.00

2 Stephan 26 Dubai 1500.00

3 David 27 Bangkok 2000.00

7 Jackson 25 Mizoram 10000.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
 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:

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
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:

CUSTOMER_ID NAME ADDRESS

 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:

CUSTOMER_ID NAME ADDRESS

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(*)
2. FROM PRODUCT_MAST;
3. WHERE RATE>=20;

Output:

7
Example: COUNT() with DISTINCT

1. SELECT COUNT(DISTINCT COMPANY)

2. FROM PRODUCT_MAST;

Output:

Example: COUNT() with GROUP BY

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
3. GROUP BY COMPANY;

Output:

Com1 5
Com2 3
Com3 2

Example: COUNT() with HAVING

1. SELECT COMPANY, COUNT(*)

2. FROM PRODUCT_MAST
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)
2. FROM PRODUCT_MAST;

Output:

670

Example: SUM() with WHERE

1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
 3. WHERE QTY>3;

Output:

320

Example: SUM() with GROUP BY

1. SELECT SUM(COST)
2. FROM PRODUCT_MAST
3. WHERE QTY>3
4. GROUP BY COMPANY;

Output:

Com1 150
Com2 170

Example: SUM() with HAVING

1. SELECT COMPANY, SUM(COST)

2. FROM PRODUCT_MAST
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)
2. FROM PRODUCT_MAST;

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)
2. FROM PRODUCT_MAST;
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)
2. FROM PRODUCT_MAST;

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

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

6 Marry Canada 600000 48

PROJECT

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

1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. LEFT JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. LEFT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

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

1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. RIGHT JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. RIGHT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

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

1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. FULL JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. FULL JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

Russell NULL

Marry NULL

SQL Set Operation

The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

1. Union
 2. UnionAll
3. Intersect
4. Minus

1. Union

o The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
o In the union operation, all the number of datatype and columns must be same in both the tables on which
UNION operation is being applied.
o The union operation eliminates the duplicate rows from its resultset.

Syntax

1. SELECT column_name FROM table1

2. UNION
3. SELECT column_name FROM table2;

Example:

The First table

ID NAME

1 Jack

2 Harry

3 Jackson

The Second table

ID NAME

3 Jackson

4 Stephan

5 David

Union SQL query will be:

1. SELECT * FROM First

2. UNION
3. SELECT * FROM Second;

The resultset table will look like:

ID NAME

1 Jack
 2 Harry

3 Jackson

4 Stephan

5 David

2. Union All

Union All operation is equal to the Union operation. It returns the set without removing duplication and sorting
the data.

Syntax:

1. SELECT column_name FROM table1

2. UNION ALL
3. SELECT column_name FROM table2;

Example: Using the above First and Second table.

Union All query will be like:

1. SELECT * FROM First

2. UNION ALL
3. SELECT * FROM Second;

The resultset table will look like:

ID NAME

1 Jack

2 Harry

3 Jackson

3 Jackson

4 Stephan

5 David

3. Intersect

o It is used to combine two SELECT statements. The Intersect operation returns the common rows from
both the SELECT statements.
o In the Intersect operation, the number of datatype and columns must be the same.
o It has no duplicates and it arranges the data in ascending order by default.

Syntax

1. SELECT column_name FROM table1

 2. INTERSECT
3. SELECT column_name FROM table2;

Example:

Using the above First and Second table.

Intersect query will be:

1. SELECT * FROM First

2. INTERSECT
3. SELECT * FROM Second;

The resultset table will look like:

ID NAME

3 Jackson

4. Minus

o It combines the result of two SELECT statements. Minus operator is used to display the rows which are
present in the first query but absent in the second query.
o It has no duplicates and data arranged in ascending order by default.

Syntax:

1. SELECT column_name FROM table1

2. MINUS
3. SELECT column_name FROM table2;

Example

Using the above First and Second table.

Minus query will be:

1. SELECT * FROM First

2. MINUS
3. SELECT * FROM Second;

The resultset table will look like:

ID NAME

1 Jack

2 Harry

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.

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.