DATABASE MANAGEMENT SYSTEM

CHAPTER 1.

INTRODUCTION
What is database?

 The database is a collection of inter-related data which is used to retrieve, insert and delete the
data efficiently. It is also used to organize the data in the form of a table, schema, views, and
reports, etc.
 For example: The college Database organizes the data about the admin, staff, students and faculty
etc.
 Using the database, we can easily retrieve, insert, and delete the information.

What is data?

Data is a raw and unorganized fact that required to be processed to make it meaningful. Data can be
simple at the same time unorganized unless it is organized. Generally, data comprises facts, observations,
perceptions numbers, characters, symbols, image, etc.

What is information?

Information is a set of data which is processed in a meaningful way according to the given requirement.

Information assigns meaning and improves the reliability of the data.

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.
 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.
nai huwa o User Administration: It is used for registering and monitoring users, maintain data integrity,
valke
enforcing data security, dealing with concurrency control, monitoring performance and recovering
information corrupted by unexpected failure.

DBMS vs. File System

DBMS FILE SYSTEM

DBMS is a collection of data. In DBMS, the File system is a collection of data. In this
user is not required to write the procedures. system, the user has to write the procedures
for managing the database.
DBMS gives an abstract view of data that File system provides the detail of the data
hides the details. representation and storage of data.
DBMS provides a crash recovery mechanism, File system doesn't have a crash
i.e., DBMS protects the user from the mechanism, i.e., if the system crashes while
system failure. entering some data, then the content of the
file will lost.
DBMS provides a good protection It is very difficult to protect a file under the
mechanism. file system.
DBMS contains a wide variety of File system can't efficiently store and
sophisticated techniques to store and retrieve the data.
retrieve the data.
DBMS takes care of Concurrent access of In the File system, concurrent access has
data using some form of locking. many problems like redirecting the file while
other deleting some information or updating
some information.

Characteristics Of DBMS

o It uses a digital repository established on a server to store and manage the information.
o It can provide a clear and logical view of the process that manipulates data.
o DBMS contains automatic backup and recovery procedures. 1) represents complex relationship between
data
o It can reduce the complex relationship between data. 2)control data dependency
3)enforces user defined rules
4)ensures data sharing
5)It has central dictionary to strore information
6)enforces data access authorization.
 o It is used to support manipulation and processing of data.
o It is used to provide security of data.

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

Disadvantages of DBMS

o Cost of Hardware and Software: It requires a high speed of data processor and large memory
size to run DBMS software.
o Size: It occupies a large space of disks and large memory to run them efficiently.
o Complexity: Database system creates additional complexity and requirements.
o Higher impact of failure: Failure is highly impacted the database because in most of the
organization, all the data stored in a single database and if the database is damaged due to electric
failure or database corruption then the data may be lost forever.

Advantages Disadvantages

Data independence Size - Very large

No unplanned redundant data Complex
Consistent data High Cost
Data security Increased HW Requirement
Data integrity High impact of failure
Easily Maintanace
CHAPTER 2.

DATABASE ARCHITECTURE AND MODELING

The DBMS design depends upon its architecture. The basic client/server architecture is used to deal with
a large number of PCs, web servers, database servers and other components that are connected with
networks.

DBMS 3-tier Architecture

DBMS 3-tier architecture divides the complete system into three inter-related but independent
modules as shown below

1. Physical Level: At the physical level, the information about the location of database objects in the
data store is kept. Various users of DBMS are unaware of the locations of these objects.
2. Conceptual Level: At conceptual level, data is represented in the form of various database tables.
For Example, STUDENT database may contain STUDENT and COURSE tables which will be
visible to users but users are unaware of their storage.
3. External Level: An external level specifies a view of the data in terms of conceptual level tables.
Each external level view is used to cater to the needs of a particular category of users. For Example,
FACULTY of a university is interested in looking course details of students, STUDENTS are
interested in looking at all details related to academics, accounts, courses and hostel details as well.
So, different views can be generated for different users.
Data Independence

Data independence means a change of data at one level should not affect another level. Two types of data
independence are present in this architecture:
1. Physical Data Independence: Any change in the physical location of tables and indexes should not
affect the conceptual level or external view of data. This data independence is easy to achieve and
implemented by most of the DBMS.
2. Conceptual Data Independence: The data at conceptual level schema and external level schema
must be independent. This means a change in conceptual schema should not affect external schema.
e.g.; Adding or deleting attributes of a table should not affect the user’s view of the table. But this
type of independence is difficult to achieve as compared to physical data independence because the
changes in conceptual schema are reflected in the user’s view.

Database Design:

Phases of database design:

Database designing for a real-world application starts from capturing the requirements to physical
implementation using DBMS software which consists of following steps shown below:

Conceptual Design: The requirements of database are captured using high level conceptual data model.
For Example, the ER model is used for the conceptual design of the database.
Logical Design: Logical Design represents data in the form of relational model. ER diagram produced in
the conceptual design phase is used to convert the data into the Relational Model.
Physical Design: In physical design, data in relational model is implemented using commercial DBMS
like Oracle, DB2.
Chapter-3
ER Model
o ER model stands for an Entity-Relationship model. It is a high-level data model. This model is
used to define the data elements and relationship for a specified system.
o It develops a conceptual design for the database. It also develops a very simple and easy to design
view of data.
o In ER modeling, the database structure is portrayed as a diagram called an entity-relationship
diagram.

For example, Suppose we design a school database. In this database, the student will be an entity with
attributes like address, name, id, age, etc. The address can be another entity with attributes like city, street
name, pin code, etc and there will be a relationship between them.

Component Of ER Diagram
1. Entity:

An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as
rectangles.

Consider an organization as an example- manager, product, employee, department etc. can be taken as an
entity.

a. Weak Entity

An entity that depends on another entity called a weak entity. The weak entity doesn't contain any key

attribute of its own. The weak entity is represented by a double rectangle.

2. Attribute

The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.

For example, id, age, contact number, name, etc. can be attributes of a student.
a. Key Attribute

The key attribute is used to represent the main characteristics of an entity. It represents a primary key. The

key attribute is represented by an ellipse with the text underlined.

b. Composite Attribute

An attribute that composed of many other attributes is known as a composite attribute. The composite
attribute is represented by an ellipse, and those ellipses are connected with an ellipse.

c. Multivalued Attribute

An attribute can have more than one value. These attributes are known as a multivalued attribute. The
double oval is used to represent multivalued attribute.

For example, a student can have more than one phone number.
d. Derived Attribute

An attribute that can be derived from other attribute is known as a derived attribute. It can be represented
by a dashed ellipse.

For example, A person's age changes over time and can be derived from another attribute like Date of
birth.

3. Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent

the relationship.

a. One-to-One Relationship

When only one instance of an entity is associated with the relationship, then it is known as one to one
relationship.

For example, A female can marry to one male, and a male can marry to one female.

b. One-to-many relationship

When only one instance of the entity on the left, and more than one instance of an entity on the right

associates with the relationship then this is known as a one-to-many relationship.

For example, Scientist can invent many inventions, but the invention is done by the only specific
scientist.

c. Many-to-one relationship

When more than one instance of the entity on the left, and only one instance of an entity on the right

associates with the relationship then it is known as a many-to-one relationship.

For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship

When more than one instance of the entity on the left, and more than one instance of an entity on the right

associates with the relationship then it is known as a many-to-many relationship.

For example, Employee can assign by many projects and project can have many employees.

CONCEPT OF SUPERCLASS AND SUBCLASS

Superclass: An entity type that represents a general concept at a high level.

Subclass: An entity type that represents a specific concept at lower levels.

A subclass is said to inherit from a superclass. A subclass can inherit from many superclasses in the
hierarchy. When a subclass inherits from one or more superclasses, it inherits all their attributes. In
addition to the inherited attributes, a subclass can also define its own specific attributes. A subclass also
inherits participation in the relationship sets in which its superclass (higher-level entity) participates.

The process of making a superclass from a group of subclasses is called generalization. The process of
making subclasses from a general concept is called specialization.

Specialization: A means of identifying sub-groups within an entity set which have attributes that are not
shared by all the entities (top-down).

Specialization
o Specialization is a top-down approach, and it is opposite to Generalization. In specialization, one
higher level entity can be broken down into two lower level entities.
o Specialization is used to identify the subset of an entity set that shares some distinguishing
characteristics.
o Normally, the superclass is defined first, the subclass and its related attributes are defined next,
and relationship set are then added.

For example: In an Employee management system, EMPLOYEE entity can be specialized as TESTER
or DEVELOPER based on what role they play in the company.

Generalization: Multiple entity sets are synthesized into a higher-level entity set, based on common
features (bottom-up).
Generalization
o Generalization is like a bottom-up approach in which two or more entities of lower level combine
to form a higher level entity if they have some attributes in common.
o In generalization, an entity of a higher level can also combine with the entities of the lower level
to form a further higher level entity.
 o Generalization is more like subclass and superclass system, but the only difference is the
approach. Generalization uses the bottom-up approach.
o In generalization, entities are combined to form a more generalized entity, i.e., subclasses are
combined to make a superclass.

For example, Faculty and Student entities can be generalized and create a higher level entity Person.

Aggregation

In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship
with its corresponding entities is aggregated into a higher level entity.

For example: Center entity offers the Course entity act as a single entity in the relationship which is in a
relationship with another entity visitor. In the real world, if a visitor visits a coaching center then he will
never enquiry about the Course only or just about the Center instead he will ask the enquiry about both.
Chapter 4.

What is RDBMS
RDBMS stands for Relational Database Management Systems.

Proposed by E.F. Codd

All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL
and Microsoft Access are based on RDBMS.
Relational Data Base Management Systems (RDBMS) are database management systems that maintain
data records and indices in tables. Relationships may be created and maintained across and among the
data and tables. In a relational database, relationships between data items are expressed by means of
tables. This allows a high degree of data independence.

An RDBMS has the capability to recombine the data items from different files, providing powerful tools
for data usage.

How it works?

Data is represented in terms of tuples (rows) in RDBMS.

Relational database is most commonly used database. It contains number of tables and each table has its
own primary key.

Due to a collection of organized set of tables, data can be accessed easily in RDBMS.

What is table?
The RDBMS database uses tables to store data. A table is a collection of related data entries and contains
rows and columns to store data.

A table is the simplest example of data storage in RDBMS.

Let's see the example of student table.

What is field?

Field is a smaller entity of the table which contains specific information about every record in the table. In
the above example, the field in the student table consist of id, name, age, course.
What is row or record?
A row of a table is also called record. It contains the specific information of each individual entry in the
table. It is a horizontal entity in the table. For example: The above table contains 5 records.

Let's see one record/row in the table.

What is column?

A column is a vertical entity in the table which contains all information associated with a specific field in
a table. For example: "name" is a column in the above table which contains all information about student's
name.

NULL Values

The NULL value of the table specifies that the field has been left blank during record creation. It is totally
different from the value filled with zero or a field that contains space.
Data Integrity
There are the following categories of data integrity exist with each RDBMS:

Entity integrity: It specifies that there should be no duplicate rows in a table.

Domain integrity: It enforces valid entries for a given column by restricting the type, the format, or the
range of values.
Referential integrity: It specifies that rows cannot be deleted, which are used by other records.

User-defined integrity: It enforces some specific business rules that are defined by users. These rules are
different from entity, domain or referential integrity.

Difference between DBMS and RDBMS

The main differences between DBMS and RDBMS are given below:

S.No. DBMS RDBMS

1. DBMS applications store data as file. RDBMS applications store data in a tabular
form.
2. In DBMS, data is generally stored in either a In RDBMS, the tables have an identifier
hierarchical form or a navigational form. called primary key and the data values are
stored in the form of tables.
3. Normalization is not present in DBMS. Normalization is present in RDBMS.
4. DBMS does not apply any security with RDBMS defines the integrity constraint for
regards to data manipulation. the purpose of ACID (Atomocity,
Consistency, Isolation and Durability)
property.
5. DBMS uses file system to store data, so there in RDBMS, data values are stored in the
will be no relation between the tables. form of tables, so a relationship between
these data values will be stored in the form of
a table as well.

6. DBMS has to provide some uniform methods RDBMS system supports a tabular structure
to access the stored information. of the data and a relationship between them
to access the stored information.
7. DBMS does not support distributed RDBMS supports distributed database.
database.
8. DBMS is meant to be for small organization RDBMS is designed to handle large
and deal with small data. it supports single amount of data. it supports multiple users.
user.
9. Examples of DBMS are file systems, xml etc. Example of RDBMS are mysql, postgre, sql
server, oracle etc.
DBMS vs. File System
Chapter 6

Introduction To SQL
Structure Query Language(SQL) is a database query language used for storing and managing data in
Relational DBMS. SQL was the first commercial language introduced for E.F Codd's Relational model
of database. Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) use SQL as the
standard database query language. SQL is used to perform all types of data operations in RDBMS.
OR

What is SQL?

o SQL stands for Structured Query Language.

o It is designed for managing data in a relational database management system (RDBMS).
o It is pronounced as S-Q-L or sometime See-Qwell.
o SQL is a database language, it is used for database creation, deletion, fetching rows, and
modifying rows, etc.
o SQL is based on relational algebra and tuple relational calculus.

All DBMS like MySQL, Oracle, MS Access, Sybase, Informix, Postgres, and SQL Server use SQL as
standard database language.

A Brief History of SQL

 1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He
described a relational model for databases.
 1974 − Structured Query Language appeared.
 1978 − IBM worked to develop Codd's ideas and released a product named System/R.
 1986 − IBM developed the first prototype of relational database and standardized by ANSI. The
first relational database was released by Relational Software which later came to be known as
Oracle.

Why SQL is required?

SQL is required:

o To create new databases, tables and views

o To insert records in a database
o To update records in a database
o To delete records from a database
o To retrieve data from a database
What SQL does?

o With SQL, we can query our database in several ways, using English-like statements.
o With SQL, a user can access data from a relational database management system.
o It allows the user to describe the data.
o It allows the user to define the data in the database and manipulate it when needed.
o It allows the user to create and drop database and table.
o It allows the user to create a view, stored procedure, function in a database.
o It allows the user to set permission on tables, procedures, and views.

Advantages of SQL

There are the following advantages of SQL:

High speed
o Using the SQL queries, the user can quickly and efficiently retrieve a large amount of records
from a database.

No coding needed
o In the standard SQL, it is very easy to manage the database system. It doesn't require a substantial
amount of code to manage the database system.

Well defined standards

o Long established are used by the SQL databases that are being used by ISO and ANSI.

Portability
o SQL can be used in laptop, PCs, server and even some mobile phones.

Interactive language
o SQL is a domain language used to communicate with the database. It is also used to receive
answers to the complex questions in seconds.

Multiple data view

o Using the SQL language, the users can make different views of the database structure.

Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate it when needed.
 o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a database.
o SQL allows users to set permissions on tables, procedures, and views.

SQL Process

When you are executing an SQL command for any RDBMS, the system determines the best way to carry
out your request and SQL engine figures out how to interpret the task.
There are various components included in this process.
These components are −

 Query Dispatcher
 Optimization Engines
 Classic Query Engine
 SQL Query Engine, etc.
A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle logical
files.
Following is a simple diagram showing the SQL Architecture −
SQL Data Types/ Literals

Data types are used to represent the nature of the data that can be stored in the database table. For
example, in a particular column of a table, if we want to store a string type of data then we will have to
declare a string data type of this column.

Data types mainly classified into three categories for every database.

o String Data types

o Numeric Data types
o Date and time Data types

In SQL, a literal is the same as a constant. We'll cover several types of literals - string, integer, decimal,
and datetime literals.

String Literals
String literals are always surrounded by single quotes (').
For example:

'TechOnTheNet.com'
'This is a literal'
'XYZ'
'123'

Integer Literals
Integer literals can be either positive numbers or negative numbers, but do not contain decimals. If you do
not specify a sign, then a positive number is assumed. Here are some examples of valid integer literals:

536
+536
-536

Decimal Literals
Decimal literals can be either positive numbers or negative numbers and contain decimals. If you do not
specify a sign, then a positive number is assumed. Here are some examples of valid decimal literals:

24.7
+24.7
-24.7

Datetime Literals
Datetime literals are character representations of datetime values that are enclosed in single quotes. Here
are some examples of valid datetime literals:

'April 30, 2015'

'2015/04/30'
'2015/04/30 08:34:25'

SQL Command
https://www.studytonight.com/dbms/introduction-to-sql.php

SQL defines following ways to manipulate data stored in an RDBMS.

DDL: Data Definition Language
This includes changes to the structure of the table like creation of table, altering table, deleting a table etc.
All DDL commands are auto-committed. That means it saves all the changes permanently in the database.

Command Description

create to create new table or database

alter for alteration

truncate delete data from table

drop to drop a table

rename to rename a table

DML: Data Manipulation Language

DML commands are used for manipulating the data stored in the table and not the table itself.
DML commands are not auto-committed. It means changes are not permanent to database, they can be
rolled back.

Command Description
insert to insert a new row

update to update existing row

delete to delete a row

merge merging two rows or two tables

TCL: Transaction Control Language

These commands are to keep a check on other commands and their affect on the database. These
commands can annul changes made by other commands by rolling the data back to its original state. It can
also make any temporary change permanent.

Command Description

commit to permanently save

rollback to undo change

savepoint to save temporarily

DCL: Data Control Language

Data control language are the commands to grant and take back authority from any database user.

Command Description

grant grant permission of right

revoke take back permission.

DQL: Data Query Language

Data query language is used to fetch data from tables based on conditions that we can easily apply.
Command Description

select retrieve records from one or more table

SQL Operators
Operators manipulate individual data items called operands or arguments. Operators are represented by
special characters or by keywords. For example, the multiplication operator is represented by an asterisk
(*).

Unary And Binary Operators

The two general classes of operators are:

 unary: A unary operator operates on only one operand. A unary operator typically appears with its
operand in this format:
 operator operand

 binary: A binary operator operates on two operands. A binary operator appears with its operands
in this format:
 operand1 operator operand2

Other operators with special formats accept more than two operands. If an operator is given a null
operand, the result is always null. The only operator that does not follow this rule is concatenation (||).

Operator precedence determines the grouping of terms in an expression. This affects how an expression
is evaluated. Certain operators have higher precedence than others; for example, the multiplication
operator has higher precedence than the addition operator.
For example, x = 7 + 3 * 2; here, x is assigned 13, not 20 because operator * has higher precedence than
+, so it first gets multiplied with 3*2 and then adds into 7.
Here, operators with the highest precedence appear at the top of the table, those with the lowest appear at
the bottom. Within an expression, higher precedence operators will be evaluated first.
The precedence of operators goes as follows: =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE,
BETWEEN, IN.

Operator Operation

** exponentiation

+, - identity, negation
 *, / multiplication, division

+, -, || addition, subtraction, concatenation

comparison

NOT logical negation

AND conjunction

OR inclusion

Aggregate functions in SQL

In database management an aggregate function is a function where the values of multiple rows are
grouped together as input on certain criteria to form a single value of more significant meaning.
Various Aggregate Functions

1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()

Now let us understand each Aggregate function with a example:

Id Name Salary
-----------------------
1 A 80
2 B 40
3 C 60
4 D 70
5 E 60
6 F Null
Count():
Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct Non Null values over the column salary .i.e 4

Sum():

sum(salary): Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.

Avg():

Avg(salary) = Sum(salary) / count(salary) = 310/5

Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4

Min():

Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.

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

SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT column_name from table_name WHERE ... );

Example
Consider the EMPLOYEE table have the following records:

The subquery with a SELECT statement will be:

SELECT *
FROM EMPLOYEE
WHERE ID IN (SELECT ID
FROM EMPLOYEE
WHERE SALARY > 4500);
This would produce the following result:

2. Subqueries with the INSERT Statement

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.
In the subquery, the selected data can be modified with any of the character, date functions.
Syntax:
INSERT INTO table_name (column1, column2, column3....)
SELECT *
FROM table_name
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.
INSERT INTO EMPLOYEE_BKP
SELECT * FROM EMPLOYEE
WHERE ID IN (SELECT ID
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

UPDATE table
SET column_name = new_value
WHERE VALUE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME
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.

UPDATE EMPLOYEE
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 29);
This would impact three rows, and finally, the EMPLOYEE table would have the following records.

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
DELETE FROM TABLE_NAME
WHERE VALUE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME
 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.

DELETE FROM EMPLOYEE

WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP
WHERE AGE >= 29 );
This would impact three rows, and finally, the EMPLOYEE table would have the following records.

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

 Each SELECT statement within UNION must have the same number of columns
 The columns must also have similar data types
 The columns in each SELECT statement must also be in the same order

UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1

UNION ALL
SELECT column_name(s) FROM table2;

Note: The column names in the result-set are usually equal to the column names in the first SELECT
statement in the UNION.
SQL | Join (Inner, Left, Right and Full Joins)
A 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:
 INNER JOIN
 LEFT JOIN
 RIGHT JOIN
 FULL JOIN

Example of JOIN

SELECT columnlist

FROM TableA

INNER JOIN

TableB ON join condition

Difference between Join and union in SQL