Unit – 1

DBMS VS RDBMS
Database Management System
o Database management system is a software which is used to manage the
database. For example: MySQL, Oracle, etc are a very popular commercial
database which is used in different applications.
o DBMS provides an interface to perform various operations like database creation,
storing data in it, updating data, creating a table in the database and a lot more.
o It provides protection and security to the database. In the case of multiple users,
it also maintains data consistency.

DBMS allows users the following tasks:

o Data Definition: It is used for creation, modification, and removal of definition

that defines the organization of data in the database.
o Data Updation: It is used for the insertion, modification, and deletion of the
actual data in the database.
o Data Retrieval: It is used to retrieve the data from the database which can be
used by applications for various purposes.
o User Administration: It is used for registering and monitoring users, maintain
data integrity, enforcing data security, dealing with concurrency control,
monitoring performance and recovering information corrupted by unexpected
failure.

Advantages of DBMS
o Controls database redundancy: It can control data redundancy because it stores
all the data in one single database file and that recorded data is placed in the
database.
 o Data sharing: In DBMS, the authorized users of an organization can share the
data among multiple users.
o Easily Maintenance: It can be easily maintainable due to the centralized nature
of the database system.
o Reduce time: It reduces development time and maintenance need.
o Backup: It provides backup and recovery subsystems which create automatic
backup of data from hardware and software failures and restores the data if
required.
o multiple user interface: It provides different types of user interfaces like
graphical user interfaces, application program interfaces

RDBMS

A relational database management system (RDBMS) is a collection of

programs and capabilities that enable IT teams and others to create, update,
administer and otherwise interact with a relational database. RDBMSes store
data in the form of tables, with most commercial relational database
management systems using Structured Query Language (SQL) to access the
database. However, since SQL was invented after the initial development of
the relational model, it is not necessary for RDBMS use.
The RDBMS is the most popular database system among organizations
across the world. It provides a dependable method of storing and retrieving
large amounts of data while offering a combination of system performance
and ease of implementation.

What is a Database Management System(DBMS)?

Database Management System (DBMS) is software used to identify, manage, and create a
database that provides administered access to the data.

What is a Relational Database Management System

(RDBMS)?
Relational Database Management System (RDBMS) is a more advanced version of a DBMS system
that allows access to data in a more efficient way. It is used to store or manage only the data that
are in the form of tables.

What is the Difference between DBMS and RDBMS?

DBMS stands for Database Management System, and RDBMS is the acronym for the Relational
Database Management system. In DBMS, the data is stored as a file, whereas in RDBMS, data is
stored in the form of tables. To know what is the difference between RDBMS and DBMS, check out
the table below.

Difference between RDBMS and DBMS

RDBMS DBMS

Data stored is in table format Data stored is in the file format

Multiple data elements are accessible together Individual access of data elements

Data in the form of a table are linked together No connection between data

Normalisation is not achievable There is normalisation

Support distributed database No support for distributed database

Data is stored in a large amount Data stored is a small quantity

Here, redundancy of data is reduced with the help of key Data redundancy is common
and indexes in RDBMS

RDBMS supports multiple users DBMS supports a single user

It features multiple layers of security while handling data There is only low security while
handling data

The software and hardware requirements are higher The software and hardware
requirements are low

Oracle, SQL Server. XML, Microsoft Access.

 Codd's 12 Rules

Dr Edgar F. Codd, after his extensive research on the Relational Model of database
systems, came up with twelve rules of his own, which according to him, a database
must obey in order to be regarded as a true relational database.
These rules can be applied on any database system that manages stored data using
only its relational capabilities. This is a foundation rule, which acts as a base for all the
other rules.

Rule 1: Information Rule

The data stored in a database, may it be user data or metadata, must be a value of
some table cell. Everything in a database must be stored in a table format.

Rule 2: Guaranteed Access Rule

Every single data element (value) is guaranteed to be accessible logically with a
combination of table-name, primary-key (row value), and attribute-name (column value).
No other means, such as pointers, can be used to access data.
Rule 3: Systematic Treatment of NULL Values
The NULL values in a database must be given a systematic and uniform treatment. This
is a very important rule because a NULL can be interpreted as one the following − data
is missing, data is not known, or data is not applicable.

Rule 4: Active Online Catalog

The structure description of the entire database must be stored in an online catalog,
known as data dictionary, which can be accessed by authorized users. Users can use
the same query language to access the catalog which they use to access the database
itself.

Rule 5: Comprehensive Data Sub-Language Rule

A database can only be accessed using a language having linear syntax that supports
data definition, data manipulation, and transaction management operations. This
language can be used directly or by means of some application. If the database allows
access to data without any help of this language, then it is considered as a violation.

Rule 6: View Updating Rule

All the views of a database, which can theoretically be updated, must also be updatable
by the system.

Rule 7: High-Level Insert, Update, and Delete Rule

A database must support high-level insertion, updation, and deletion. This must not be
limited to a single row, that is, it must also support union, intersection and minus
operations to yield sets of data records.

Rule 8: Physical Data Independence

The data stored in a database must be independent of the applications that access the
database. Any change in the physical structure of a database must not have any impact
on how the data is being accessed by external applications.

Rule 9: Logical Data Independence

The logical data in a database must be independent of its user’s view (application). Any
change in logical data must not affect the applications using it. For example, if two
tables are merged or one is split into two different tables, there should be no impact or
change on the user application. This is one of the most difficult rule to apply.
Rule 10: Integrity Independence
A database must be independent of the application that uses it. All its integrity
constraints can be independently modified without the need of any change in the
application. This rule makes a database independent of the front-end application and its
interface.

Rule 11: Distribution Independence

The end-user must not be able to see that the data is distributed over various locations.
Users should always get the impression that the data is located at one site only. This
rule has been regarded as the foundation of distributed database systems.

Rule 12: Non-Subversion Rule

If a system has an interface that provides access to low-level records, then the interface
must not be able to subvert the system and bypass security and integrity constraints.
 Unit – 2

SQL is a standard language for accessing and manipulating databases.

What is SQL?
 SQL stands for Structured Query Language
 SQL lets you access and manipulate databases
 SQL became a standard of the American National Standards Institute
(ANSI) in 1986, and of the International Organization for Standardization
(ISO) in 1987

What Can SQL do?

 SQL can execute queries against a database
 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views

SQL is a language to operate databases; it includes Database Creation, Database

Deletion, Fetching Data Rows, Modifying & Deleting Data rows, etc.
SQL stands for Structured Query Language which is a computer language for storing,
manipulating and retrieving data stored in a relational database. SQL was developed in
the 1970s by IBM Computer Scientists and became a standard of the American
National Standards Institute (ANSI) in 1986, and the International Organization for
Standardization (ISO) in 1987.
Components
The Structured Query Language (SQL) is the set of instructions used to interact
with a relational database. In fact, SQL is the only language that most databases
understand. Whenever you interact with such a database, the software translates
your commands (whether they are mouse clicks or form entries) into a SQL
statement that the database knows how to interpret. SQL has three main
components: the Data Manipulation Language (DML), the Data Definition
Language (DDL), and the Data Control Language (DCL).
Data Manipulation Language
The Data Manipulation Language (DML) contains the subset of SQL commands
used most frequently — those that simply manipulate the contents of a database
in some form. The four most common DML commands retrieve information from
a database (the SELECT) command, add new information to a database (the
INSERT command), modify information currently stored in a database (the
UPDATE command), and remove information from a database (the DELETE
command).

Data Definition Language

The Data Definition Language (DDL) contains commands that are less frequently
used. DDL commands modify the actual structure of a database, rather than the
database’s contents. Examples of commonly used DDL commands include those
used to generate a new database table (CREATE TABLE), modify the structure of
a database table (ALTER TABLE), and delete a database table (DROP TABLE).

Data Control Language

The Data Control Language (DCL) is used to manage user access to databases. It
consists of two commands: the GRANT command, used to add database
 permissions for a user, and the REVOKE command, used to remove existing
permissions. These two commands form the core of the relational database
security model.

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;  

What are SQL datatypes?

Datatypes in SQL are used to define the type of data that can be stored in a column of a
table, like, INT, CHAR, MONEY, DATETIME etc. They provide guidelines for SQL to
understand what type of data is expected inside each column, and they also identify
how SQL will interact with the stored data. The datatype specification, hence, prevents
the user from entering any unexpected or invalid data.
For example, if we want a column to store only integer values, we can specify its data
types as INT. SQL will show an error if any other value apart from an integer is inserted
into that particular column.

Types of Datatypes
There are three main types of datatypes in the SQL server. They are listed below −
 String
 Numeric
 Date and Time

SQL Keywords

Keyword Description

ADD Adds a column in an existing table

ADD CONSTRAINT Adds a constraint after a table is already created

ALL Returns true if all of the subquery values meet the condition

ALTER Adds, deletes, or modifies columns in a table, or changes the data type of a
column in a table

ALTER COLUMN Changes the data type of a column in a table

ALTER TABLE Adds, deletes, or modifies columns in a table

AND Only includes rows where both conditions is true

ANY Returns true if any of the subquery values meet the condition

AS Renames a column or table with an alias

ASC Sorts the result set in ascending order

BACKUP Creates a back up of an existing database

DATABASE

BETWEEN Selects values within a given range

CASE Creates different outputs based on conditions

CHECK A constraint that limits the value that can be placed in a column

COLUMN Changes the data type of a column or deletes a column in a table

CONSTRAINT Adds or deletes a constraint

CREATE Creates a database, index, view, table, or procedure

CREATE Creates a new SQL database

DATABASE

CREATE INDEX Creates an index on a table (allows duplicate values)

CREATE OR Updates a view
REPLACE VIEW

CREATE TABLE Creates a new table in the database

CREATE Creates a stored procedure

PROCEDURE

CREATE UNIQUE Creates a unique index on a table (no duplicate values)

INDEX

CREATE VIEW Creates a view based on the result set of a SELECT statement

DATABASE Creates or deletes an SQL database

DEFAULT A constraint that provides a default value for a column

DELETE Deletes rows from a table

DESC Sorts the result set in descending order

DISTINCT Selects only distinct (different) values

DROP Deletes a column, constraint, database, index, table, or view

DROP COLUMN Deletes a column in a table

DROP Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint

CONSTRAINT

DROP DATABASE Deletes an existing SQL database

DROP DEFAULT Deletes a DEFAULT constraint

DROP INDEX Deletes an index in a table

DROP TABLE Deletes an existing table in the database

DROP VIEW Deletes a view

EXEC Executes a stored procedure

EXISTS Tests for the existence of any record in a subquery

FOREIGN KEY A constraint that is a key used to link two tables together

FROM Specifies which table to select or delete data from

FULL OUTER JOIN Returns all rows when there is a match in either left table or right table
GROUP BY Groups the result set (used with aggregate functions: COUNT, MAX, MIN,
SUM, AVG)

HAVING Used instead of WHERE with aggregate functions

IN Allows you to specify multiple values in a WHERE clause

INDEX Creates or deletes an index in a table

INNER JOIN Returns rows that have matching values in both tables

INSERT INTO Inserts new rows in a table

INSERT INTO Copies data from one table into another table
SELECT

IS NULL Tests for empty values

IS NOT NULL Tests for non-empty values

JOIN Joins tables

LEFT JOIN Returns all rows from the left table, and the matching rows from the right table

LIKE Searches for a specified pattern in a column

LIMIT Specifies the number of records to return in the result set

NOT Only includes rows where a condition is not true

NOT NULL A constraint that enforces a column to not accept NULL values

OR Includes rows where either condition is true

ORDER BY Sorts the result set in ascending or descending order

OUTER JOIN Returns all rows when there is a match in either left table or right table

PRIMARY KEY A constraint that uniquely identifies each record in a database table

PROCEDURE A stored procedure

RIGHT JOIN Returns all rows from the right table, and the matching rows from the left table

ROWNUM Specifies the number of records to return in the result set

SELECT Selects data from a database

SELECT DISTINCT Selects only distinct (different) values

SELECT INTO Copies data from one table into a new table

SELECT TOP Specifies the number of records to return in the result set

SET Specifies which columns and values that should be updated in a table

TABLE Creates a table, or adds, deletes, or modifies columns in a table, or deletes a

table or data inside a table

TOP Specifies the number of records to return in the result set

TRUNCATE TABLE Deletes the data inside a table, but not the table itself

UNION Combines the result set of two or more SELECT statements (only distinct
values)

UNION ALL Combines the result set of two or more SELECT statements (allows duplicate
values)

UNIQUE A constraint that ensures that all values in a column are unique
 UPDATE Updates existing rows in a table

VALUES Specifies the values of an INSERT INTO statement

VIEW Creates, updates, or deletes a view

WHERE Filters a result set to include only records that fulfill a specified condition

Delimiters in MySQL?
Delimiters can be used when you need to define the stored procedures, function as well
as to create triggers. The default delimiter is semicolon.
You can change the delimiters to create procedures and so on. However, but if you are
considering multiple statements, then you need to use different delimiters like $$ or //.

SQL CREATE TABLE Example

The following example creates a table called "Persons" that contains five
columns: PersonID, LastName, FirstName, Address, and City:

Syntax to create a new table:

1. CREATE TABLE table_name    
2. (  
3. column_Name1 data_type ( size of the column ) ,    
4. column_Name2 data_type ( size of the column) ,    
5. column_Name3 data_type ( size of the column) ,    
6. ...    
7. column_NameN data_type ( size of the column )  
8. ) ;   
 Example :
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and
will hold characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

PersonID LastName FirstName Address City

         

SQL - UPDATE Query

The SQL UPDATE Query is used to modify the existing records in a table. This
statement is a part of Data Manipulation Language, as it only modifies the data present
in a table without affecting the table's structure.
Since it only interacts with the data of a table, the UPDATE statement needs to used
cautiously. If the rows to be modified aren't selected beforehand, all the rows in the
table will be affected and the correct table data is either lost or needs to be reinserted.
Therefore, to filter records that needs to be modified, you can use a WHERE clause.
Using a WHERE clause, you can either update a single row or multiple rows.
The UPDATE statement makes use of locks on each row while modifying them in a
table, and once the row is modified, the lock is released. Therefore, it can either make
changes to a single row or multiple rows with a single query.
Syntax
The basic syntax of the UPDATE query with a WHERE clause is as follows −
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
You can combine N number of conditions using the AND or the OR operators.
Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
The following query will update the ADDRESS for a customer whose ID number is 6 in
the table.
UPDATE CUSTOMERS

SET ADDRESS = 'Pune'

WHERE ID = 6;

Output
The query produces the following output −
(1 row affected)

TRUNCATE Command
TRUNCATE is another DDL command which deletes or removes all the records from the
table.

This command also removes the space allocated for storing the table records.

Syntax of TRUNCATE command

TRUNCATE TABLE Table_Name;  

RENAME Command
RENAME is a DDL command which is used to change the name of the database table.

Syntax of RENAME command

1qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqRENAME T
ABLE Old_Table_Name TO New_Table_Name;  
Example
RENAME TABLE Student TO Student_Details ; 

ALTER Command
ALTER is a DDL command which changes or modifies the existing structure of the
database, and it also changes the schema of database objects.

We can also add and drop constraints of the table using the ALTER command.

Syntax to add a newfield in the table:

ALTER TABLE name_of_table ADD column_name column_definition;  

Suppose, you want to add the 'Father's_Name' column in the existing Student table. To
do this, you have to write the following DDL command:

ALTER TABLE Student ADD Father's_Name Varchar(60);  
 
SQL Constraints
SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This
ensures the accuracy and reliability of the data in the table. If there is any
violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to
a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies
each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column if no value is specified
 CREATE INDEX - Used to create and retrieve data from the database very
quickly

Unit - 3
 Operators, Functions and Joins

Operators

Arithmetic Operators
Arithmetic operators can perform arithmetical operations on numeric operands
involved. Arithmetic operators are addition(+), subtraction(-), multiplication(*) and
division(/). The + and - operators can also be used in date arithmetic.

Operator Meaning Operates on

+ (Add) Addition Numeric

value

- (Subtract) Subtraction Numeric

value

* (Multiply) Multiplication Numeric

value

/ (Divide) Division Numeric

value

% (Modulo) Returns the integer remainder of a division. For example, 17 % 5 = 2 Numeric

because the remainder of 17 divided by 5 is 2. value

Syntax:
SELECT <Expression>[arithmetic operator]<expression>...
FROM [table_name]
WHERE [expression];
Parameter Description
 Expression Expression made up of a single constant, variable, scalar function, or column name and
can also be the pieces of a SQL query that compare values against other values or perform
arithmetic calculations.

arithmetic Plus(+), minus(-), multiply(*), and divide(/).

operator

table_name Name of the table.

Logical Operators
The Logical operators are those that are true or false. They return a true or false
values to combine one or more true or false values.

The Logical operators are:

Operator Description

AND Logical AND compares between two Booleans as expression and returns true when both expressions
are true...

OR Logical OR compares between two Booleans as expression and returns true when one of the
expression is true...

NOT Not takes a single Boolean as an argument and changes its value from false to true or from true to
false....

Special operators

Operator Description Operates on

IN The IN operator checks a value within a set of values separated by commas Any set of values of
and retrieve the rows from the table which are matching.... the same datatype

BETWEEN The SQL BETWEEN operator tests an expression against a range. The range Numeric, characters,
consists of a beginning, followed by an AND keyword and an end or datetime values
expression....

ANY ANY compares a value to each value in a list or results from a query and A value to a list or a
evaluates to true if the result of an inner query contains at least one row.... single - columns set of
values

ALL ALL is used to select all records of a SELECT STATEMENT. It compares a value A value to a list or a
to every value in a list or results from a query. The ALL must be preceded by single - columns set of
the comparison operators and evaluates to TRUE if the query returns no values
rows....

SOME SOME compare a value to each value in a list or results from a query and A value to a list or a
evaluate to true if the result of an inner query contains at least one row... single - columns set of
values

EXISTS The EXISTS checks the existence of a result of a subquery. The EXISTS Table
subquery tests whether a subquery fetches at least one row. When no data
is returned then this operator returns 'FALSE'...

Syntax:
SELECT [column_name | * | expression] [logical operator]
[column_name | * | expression .....]
FROM <table_name>
WHERE <expressions> [ logical operator |
arithmetic operator | ...] <expressions>;
Parameters:

Name Description

column_name Name of the column of a table.

 * All the columns of a table.

expression Expression made up of a single constant, variable, scalar function, or column name and
can also be the pieces of a SQL query that compare values against other values or
perform arithmetic calculations.

table_name Name of the sable.

logical operator AND, OR , NOT etc.

arithmetic Plus(+), minus(-), multiply(*) and divide(/).

operator

SQL - Relational Operators

Relational operators compare two expressions or values and return a Boolean result.
Following table shows all the relational operators supported by PL/SQL. Let us
assume variable A holds 10 and variable B holds 20, then −

Operato Description Example

r

Checks if the values of two operands are equal or not, if yes (A = B) is not
=
then condition becomes true. true.

!=
Checks if the values of two operands are equal or not, if values (A != B) is
<>
are not equal then condition becomes true. true.
~=

Checks if the value of left operand is greater than the value of (A > B) is not
>
right operand, if yes then condition becomes true. true.

Checks if the value of left operand is less than the value of right (A < B) is
<
operand, if yes then condition becomes true. true.

>= Checks if the value of left operand is greater than or equal to (A >= B) is
 the value of right operand, if yes then condition becomes true. not true.

Checks if the value of left operand is less than or equal to the (A <= B) is
<=
value of right operand, if yes then condition becomes true. true

SQL Server Range Operators

Range operators are used to retrieve the data using the condition specified
in ranges.
SQL Server supports range operators such as BETWEEN and NOT BETWEEN.

 BETWEEN : specifies condition in inclusive ranges to search the rows in

database table.
 NOT BETWEEN : specifies condition in exclusive ranges to search the
rows in database table.

Range operators can be used in the WHERE clause.

Range Operators Syntax

SELECT column_name1[, column_name2, ....]

FROM table_name
WHERE expression1 [BETWEEN | NOT BETWEEN ] expression2 AND
expression3

here expression can be constant, variable, return value of function, column value or any
combination of constant, variable, function or column value.

BETWEEN Operator on WHERE Statement

BETWEEN operator is used to specify the condition based on ranges in
WHERE clasue.

BETWEEN Operator Example

To get rows based on salary range from 6000 to 8000 in a Employee table.

SELECT Name, DeptId, City, Email, Salary

FROM Employee
WHERE Salary BETWEEN 6000 AND 8000

Pattern Matching in SQL

o LIKE clause is used to perform the pattern matching task in SQL.
o A WHERE clause is generally preceded by a LIKE clause in an SQL query.
o LIKE clause searches for a match between the patterns in a query with the pattern in the
values present in an SQL table. If the match is successful, then that particular value will
be retrieved from the SQL table.
o LIKE clause can work with strings and numbers.

The LIKE clause uses the following symbols known as wildcard operators in SQL to
perform this pattern-matching task in SQL.

1. To represent zero, one or more than one character, % (percentage) is used.

2. To represent a single character _ (underscore) is used.

Let us start with the syntax of a LIKE clause:

SELECT ColumnName1, ColumnName2 FROM TableName WHERE ColumnName LIKE [
Expression];  

Here, Expression refers to the pattern which we want to search for in the values of a
table. This expression will include the wildcard operators such as '%' and '_'.

IN Predicate

An IN operator allows the specification of two or more expressions to be used for a

query search. The result of the condition is TRUE if the value of the corresponding
column equals one of the expressions specified by the IN predicate:
EXISTS Predicate

The EXISTS Predicate accepts a subquery as an argument. It returns TRUE if the

subquery returns one or more rows, and returns FALSE if it returns zero rows.

Here's an example:
NOT IN predicate
The NOT IN predicate seems to be just the inverse of the useful IN predicate, but in SQL,
this isn't entirely true
The SQL Server NOT IN operator is used to replace a group of arguments using the <> (or !=)
operator that are combined with an AND. It can make code easier to read and understand for
SELECT, UPDATE or DELETE SQL commands. Generally, it will not change performance
characteristics.
SELECT *
FROM Sales.Invoices
WHERE LastEditedBy NOT IN (11,17,13);
The SQL ANY and ALL Operators
The ANY and ALL operators allow you to perform a comparison between a single
column value and a range of other values.

The SQL ANY Operator

The ANY operator:

 returns a boolean value as a result

 returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the
values in the range.

ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
   FROM table_name
   WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >,

>=, <, or <=).

The SQL ALL Operator

The ALL operator:

 returns a boolean value as a result

 returns TRUE if ALL of the subquery values meet the condition
 is used with SELECT, WHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all
values in the range. 
ALL Syntax With SELECT
SELECT ALL column_name(s)
FROM table_name
WHERE condition;

ALL Syntax With WHERE or HAVING

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
   FROM table_name
   WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >,

>=, <, or <=).

The SQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
SET Operators in SQL
SET operators are special type of operators which are used to combine the result of two
queries.

Operators covered under SET operators are:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

UNION
When multiple SELECT queries are joined using UNION operator, Oracle displays the
combined result from all the compounded SELECT queries, after removing all
duplicates and in sorted order (ascending by default), without ignoring the NULL values.
Consider the below five queries joined using UNION operator. The final combined result
set contains value from all the SQLs. Note the duplication removal and sorting of data.

SELECT 1 NUM FROM DUAL

UNION
SELECT 5 FROM DUAL
UNION
SELECT 3 FROM DUAL
UNION
SELECT 6 FROM DUAL
UNION
SELECT 3 FROM DUAL;

NUM
-------
1
3
5
6
UNION ALL
UNION and UNION ALL are similar in their functioning with a slight difference. But
UNION ALL gives the result set without removing duplication and sorting the data. For
example,in above query UNION is replaced by UNION ALL to see the effect.
Consider the query demonstrated in UNION section. Note the difference in the output
which is generated without sorting and deduplication.
SELECT 1 NUM FROM DUAL
UNION ALL
SELECT 5 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

NUM
-------
1
5
3
6
3

INTERSECT
Using INTERSECT operator, Oracle displays the common rows from both the SELECT
statements, with no duplicates and data arranged in sorted order (ascending by default).
For example,the below SELECT query retrieves the salary which are common in
department 10 and 20.As per ISO SQL Standards, INTERSECT is above others in
precedence of evaluation of set operators but this is not still incorporated by Oracle.
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 20

SALARY
---------
1500
1200
2000

MINUS
Minus operator displays the rows which are present in the first query but absent in the
second query, with no duplicates and data arranged in ascending order by default.
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;

JOB_ID
-------------
HR
FIN
ADMIN

SQL JOIN
SQL Join statement is used to combine data or rows from two or more tables
based on a common field between them. Different types of Joins are as
follows: 
 INNER JOIN
 LEFT JOIN
 RIGHT JOIN
 FULL JOIN
 NATURAL JOIN 

Consider the two tables below as follows: 

 Student

StudentCourse 

The simplest Join is INNER JOIN. 

A. INNER JOIN
The INNER JOIN keyword selects all rows from both the tables as long as the
condition is satisfied. This keyword will create the result-set by combining all
rows from both the tables where the condition satisfies i.e value of the common
field will be the same. 
Syntax: 
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.

table2: Second table
matching_column: Column common to both the tables.
Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER
JOIN. 

Example Queries(INNER JOIN)

This query will show the names and age of students enrolled in different
courses.
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM
Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Output:  
 B. LEFT JOIN
This join returns all the rows of the table on the left side of the join and matches
rows for the table on the right side of the join. For the rows for which there is no
matching row on the right side, the result-set will contain null. LEFT JOIN is also
known as LEFT OUTER JOIN.
Syntax: 
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.

table2: Second table
matching_column: Column common to both the tables.
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the
same.

Example Queries(LEFT JOIN): 

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output: 

C. RIGHT JOIN
RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table
on the right side of the join and matching rows for the table on the left side of
the join. For the rows for which there is no matching row on the left side, the
result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN. 
Syntax: 
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.

table2: Second table
matching_column: Column common to both the tables.
Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are
the same. 
  

Example Queries(RIGHT JOIN):

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output: 
 D. FULL JOIN
FULL JOIN creates the result-set by combining results of both LEFT JOIN and
RIGHT JOIN. The result-set will contain all the rows from both tables. For the
rows for which there is no matching, the result-set will contain NULL values.

Syntax:  
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

table1: First table.

table2: Second table
matching_column: Column common to both the tables.
Example Queries(FULL JOIN): 
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:  
NAME COURSE_ID

HARSH 1
 NAME COURSE_ID

PRATIK 2

RIYANKA 2

DEEP 3

SAPTARHI 1

DHANRAJ NULL

ROHIT NULL

NIRAJ NULL

NULL 4

NULL 5

NULL 4

E. Natural join (⋈)

Natural join can join tables based on the common columns in the tables being
joined. A natural join returns all rows by matching values in common columns
having same name and data type of columns and that column should be
present in both tables.
Both table must have at list one common column with same column name and
same data type.
The two table are joined using Cross join.
DBMS will look for a common column with same name and data type Tuples
having exactly same values in common columns are kept in result.
Example:

Employee 

Emp_i
d Emp_name Dept_id

1 Ram  10

2 Jon  30

3 Bob 50

Department 

Dept_id Dept_name

10 IT

30 HR

40 TIS

Query: Find all Employees and their respective departments.

Solution: (Employee) ⋈ (Department)
 Emp_i Dept_i
d Emp_name Dept_id d Dept_name

1 Ram  10 10 IT 

2 Jon 30 30 HR

Employee data  Department data 

SQL - Sub Queries

A Subquery or Inner query or a Nested query is a query within another SQL query and
embedded within clauses, most commonly in the WHERE clause. It is used to return
data from a table, and this data will be used in the main query as a condition to further
restrict the data to be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE
statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
There are a few rules that subqueries must follow −
 Subqueries must be enclosed within parentheses.
 A subquery can have only one column in the SELECT clause, unless multiple
columns are in the main query for the subquery to compare its selected columns.
 An ORDER BY command cannot be used in a subquery, although the main query
can use an ORDER BY. The GROUP BY command can be used to perform the
same function as the ORDER BY in a subquery.
 Subqueries that return more than one row can only be used with multiple value
operators such as the IN operator.
 The SELECT list cannot include any references to values that evaluate to a
BLOB, ARRAY, CLOB, or NCLOB.
 A subquery cannot be immediately enclosed in a set function.
 The BETWEEN operator cannot be used with a subquery. However, the
BETWEEN operator can be used within the subquery.

Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is
as follows −
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
 (SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Now, let us check the following subquery with a SELECT statement.
SQL> SELECT *

FROM CUSTOMERS

WHERE ID IN (SELECT ID

FROM CUSTOMERS

WHERE SALARY > 4500) ;

This would produce the following result.
+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+

Subqueries with the INSERT Statement

Subqueries also can be used with INSERT statements. The INSERT statement uses
the data returned from the subquery to insert into another table. The selected data in
the subquery can be modified with any of the character, date or number functions.
The basic syntax is as follows.
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
 [ WHERE VALUE OPERATOR ]
Example
Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now
to copy the complete CUSTOMERS table into the CUSTOMERS_BKP table, you can
use the following syntax.
SQL> INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;

Subqueries with the UPDATE Statement

The subquery can be used in conjunction with the UPDATE statement. Either single or
multiple columns in a table can be updated when using a subquery with the UPDATE
statement.
The basic syntax is as follows.
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Assuming, we have CUSTOMERS_BKP table available which is backup of
CUSTOMERS table. The following example updates SALARY by 0.25 times in the
CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.
SQL> UPDATE CUSTOMERS

SET SALARY = SALARY * 0.25

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP

WHERE AGE >= 27 );

This would impact two rows and finally CUSTOMERS table would have the following
records.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 125.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 2125.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Subqueries with the DELETE Statement

The subquery can be used in conjunction with the DELETE statement like with any
other statements mentioned above.
The basic syntax is as follows.
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Assuming, we have a CUSTOMERS_BKP table available which is a backup of the
CUSTOMERS table. The following example deletes the records from the CUSTOMERS
table for all the customers whose AGE is greater than or equal to 27.
SQL> DELETE FROM CUSTOMERS

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP

WHERE AGE >= 27 );

This would impact two rows and finally the CUSTOMERS table would have the following
records.
+----+----------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+---------+----------+

SQL Aggregate Functions

An aggregate function in SQL performs a calculation on multiple values and returns a single
value. SQL provides many aggregate functions that include avg, count, sum, min, max, etc. An
aggregate function ignores NULL values when it performs the calculation, except for the count
function. 
An aggregate function in SQL returns one value after calculating multiple values of a
column. We often use aggregate functions with the GROUP BY and HAVING clauses of
the SELECT statement.

Various types of SQL aggregate functions are:

 Count()

 Sum()

 Avg()

 Min()

 Max()

COUNT() Function

The COUNT() function returns the number of rows in a database table.

Syntax:

COUNT(*)  

or  

COUNT( [ALL|DISTINCT] expression )

SUM() Function

The SUM() function returns the total sum of a numeric column.

Syntax:

SUM()  

or  
SUM( [ALL|DISTINCT] expression )  

Example:

The following SQL statement finds the sum of the "unit price" fields in the "products"
table:

AVG() Function

The AVG() function calculates the average of a set of values.

Syntax:

AVG()  

or  

AVG( [ALL|DISTINCT] expression )  

Example:

The following SQL command calculates the average quantity in stock.

MIN() Function
The MIN() aggregate function returns the lowest value (minimum) in a set of non-NULL
values.

Syntax:

MIN()  

or  

MIN( [ALL|DISTINCT] expression )  

Example:

MAX() Function

The MAX() aggregate function returns the highest value (maximum) in a set of non-
NULL values.

Syntax:

AVG()  

or  

AVG( [ALL|DISTINCT] expression )  

Example:

The code depicted below will give us the maximum quantity in stock in the products
table.
Numeric Functions

Numeric Functions are used to perform operations on numbers and return

numbers. Following are the numeric functions defined in SQL:

ABS(): It returns the absolute value of a number.

Syntax: SELECT ABS(-243.5);
Output: 243.5

ACOS(): It returns the cosine of a number, in radians.

Syntax: SELECT ACOS(0.25);
Output: 1.318116071652818

CEIL(): It returns the smallest integer value that is greater than or equal to a
number.
Syntax: SELECT CEIL(25.75);
Output: 26

DIV(): It is used for integer division.

Syntax: SELECT 10 DIV 5;

GREATEST(): It returns the greatest value in a list of expressions.

Syntax: SELECT GREATEST(30, 2, 36, 81, 125);
Output: 125

LEAST(): It returns the smallest value in a list of expressions.

Syntax: SELECT LEAST(30, 2, 36, 81, 125);
Output: 2
String functions

String functions are used to perform an operation on input string and return an
output string.
Following are the string functions defined in SQL:
1. ASCII(): This function is used to find the ASCII value of a character.
Syntax: SELECT ascii('a');
Output: 97

2. CHAR_LENGTH(): Doesn’t work for SQL Server. Use LEN() for SQL Server.
This function is used to find the length of a word.
Syntax: SELECT char_length('Hello!');
Output: 6

3. CHARACTER_LENGTH(): Doesn’t work for SQL Server. Use LEN() for SQL

Server. This function is used to find the length of a line.
Syntax: SELECT CHARACTER_LENGTH('geeks for geeks');
Output: 15

4. CONCAT(): This function is used to add two words or strings.

Syntax: SELECT 'Geeks' || ' ' || 'forGeeks' FROM dual;
Output: ‘GeeksforGeeks’

5. CONCAT_WS(): This function is used to add two words or strings with a

symbol as concatenating symbol.
Syntax: SELECT CONCAT_WS('_', 'geeks', 'for', 'geeks');
Output: geeks_for_geeks

6. FIND_IN_SET(): This function is used to find a symbol from a set of

symbols.
Syntax: SELECT FIND_IN_SET('b', 'a, b, c, d, e, f');
Output: 2

7. FORMAT(): This function is used to display a number in the given format.

Syntax: Format("0.981", "Percent");
Output: ‘98.10%’

8. INSERT(): This function is used to insert the data into a database.

Syntax: INSERT INTO database (geek_id, geek_name) VALUES (5000,
'abc');
Output: successfully updated
9. INSTR(): This function is used to find the occurrence of an alphabet.
Syntax: INSTR('geeks for geeks', 'e');
Output: 2 (the first occurrence of ‘e’)
Syntax: INSTR('geeks for geeks', 'e', 1, 2 );
Output: 3 (the second occurrence of ‘e’)

10. LCASE(): This function is used to convert the given string into lower
case.
Syntax: LCASE ("GeeksFor Geeks To Learn");
Output: geeksforgeeks to learn

11. LEFT(): This function is used to SELECT a sub string from the left of
given size or characters.

Conversion Function

Implicit Data Type Conversion

In implicit data type conversion, the SQL programmer does not specify anything. Rather
the system converts one type of data to another as per its requirements. For example -
a numeric data type can be converted to character if required and vice versa.
Implicit DataType Conversion only occurs if the data types used in the query are valid
so that the system can recognise them and convert them as required. This is not
possible with invalid or wrongly provided data types.

Explicit Data Type Conversion

If the programmer wants,they can explicitly convert data types from one form to
another. There are SQL functions provided  for this express purpose. These SQL
functions are −

TO_CHAR
This function is used to explicitly convert a number or date data type to char.
The syntax of this function is given as follows −
TO_CHAR(number,format,parameters)

This function changes number to char with the specific format as provided according to
the syntax. The parameters can be used to specify decimal characters, group
separators etc.
For example −
SELECT CHAR(sysdate, “Month DD,YYYY”) FROM DUAL;

This returns the system date in the form of 31 July, 2018

TO_NUMBER
This function is used to explicitly convert a string to number. If the string to be
converted does not contain numeric characters then TO_NUMBER shows an error.
The syntax of this function is given as follows −
TO_NUMBER(number,format,parameters)

This function changes the given string to number with the specific format as provided
according to the syntax.
The parameters and format are optional so to convert a string to a number
For example,
TO_NUMBER (‘353.78’);

This returns the string 353.78 in numerical format.

TO_DATE
This function takes character values and returns the output in the date format.
The syntax of this function is given as follows −
TO_DATE(number, format, parameters)

This function changes the given string to number with the specific format as provided
according to the syntax.
SELECT TO_DATE(‘2018/07/31’,’yyyy/mm/dd’) FROM DUAL;

This takes the values in character format and returns them in date format as specified.
CRUD operations refer to the basic operations that can be performed on data stored in
a database. CRUD stands for Create, Read, Update, and Delete. These operations are
essential for managing data in any database management system.

Create (C): Create operation is used to insert new data into the database. In SQL, this
is done using the INSERT statement. The INSERT statement adds a new row to a
table.

Read (R): Read operation is used to retrieve data from the database. In SQL, this is
done using the SELECT statement. The SELECT statement retrieves data from one or
more tables and returns the result set.

Update (U): Update operation is used to modify existing data in the database. In SQL,
this is done using the UPDATE statement. The UPDATE statement modifies one or
more columns in one or more rows of a table.

Delete (D): Delete operation is used to remove data from the database. In SQL, this is
done using the DELETE statement. The DELETE statement removes one or more rows
from a table.
SELECT:
SELECT column_name(s) FROM table_name WHERE condition;

INSERT:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1, value2, value3, ...);

UPDATE:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
DELETE:
DELETE FROM table_name WHERE condition;
CREATE SEQUENCE my_sequence
START WITH 1
INCREMENT BY 1
MAXVALUE 100
NOCYCLE;
This will create a sequence called my_sequence that starts at 1 and increments by 1,
generating numbers up to a maximum value of 100. The NOCYCLE option is used to
prevent the sequence from starting over at the beginning when it reaches the maximum
value.
CREATE SEQUENCE sequence_name
[START WITH start_value]
[INCREMENT BY increment_value]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE cache_size | NOCACHE];
sequence_name: The name of the sequence you want to create.
start_value: The value at which the sequence should start. If not specified, the default is
1.
increment_value: The amount by which the sequence should be incremented. If not
specified, the default is 1.
max_value: The maximum value that the sequence can generate. If not specified, the
default is the maximum value for the data type of the sequence.
min_value: The minimum value that the sequence can generate. If not specified, the
default is the minimum value for the data type of the sequence.
CYCLE: Specifies whether the sequence should restart at the minimum value when it
reaches the maximum value. If specified, the sequence will cycle. If not specified, the
default is NOCYCLE.
CACHE: Specifies the number of sequence numbers to cache for performance. If not
specified, the default is 20.