Functional Dependency

The functional dependency is a relationship that exists between two attributes. It typically exists
between the primary key and non-key attribute within a table.

1. X → Y

The left side of FD is known as a determinant, the right side of the production is known as a
dependent.

For example:

Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address.

Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we
know the Emp_Id, we can tell that employee name associated with it.

Functional dependency can be written as:

1. Emp_Id → Emp_Name

We can say that Emp_Name is functionally dependent on Emp_Id.

Types of Functional dependency

1. Trivial functional dependency

o A → B has trivial functional dependency if B is a subset of A.

o The following dependencies are also trivial like: A → A, B → B

Example:
1. Consider a table with two columns Employee_Id and Employee_Name.
2. {Employee_id, Employee_Name} → Employee_Id is a trivial functional dependency as
3. Employee_Id is a subset of {Employee_Id, Employee_Name}.
4. Also, Employee_Id → Employee_Id and Employee_Name → Employee_Name are trivial dependencie
s too.

2. Non-trivial functional dependency

o A → B has a non-trivial functional dependency if B is not a subset of A.

o When A intersection B is NULL, then A → B is called as complete non-trivial.

Example:

1. ID → Name,
2. Name → DOB

Inference Rule (IR):

o The Armstrong's axioms are the basic inference rule.
o Armstrong's axioms are used to conclude functional dependencies on a relational database.
o The inference rule is a type of assertion. It can apply to a set of FD(functional dependency) to derive
other FD.
o Using the inference rule, we can derive additional functional dependency from the initial set.

The Functional dependency has 6 types of inference rule:

1. Reflexive Rule (IR1)

In the reflexive rule, if Y is a subset of X, then X determines Y.

1. If X ⊇ Y then X → Y

Example:

1. X = {a, b, c, d, e}
2. Y = {a, b, c}

2. Augmentation Rule (IR2)

The augmentation is also called as a partial dependency. In augmentation, if X determines Y, then XZ
determines YZ for any Z.
1. If X → Y then XZ → YZ

Example:

1. For R(ABCD), if A → B then AC → BC

3. Transitive Rule (IR3)

In the transitive rule, if X determines Y and Y determine Z, then X must also determine Z.

1. If X → Y and Y → Z then X → Z

4. Union Rule (IR4)

Union rule says, if X determines Y and X determines Z, then X must also determine Y and Z.

1. If X → Y and X → Z then X → YZ

Proof:

1. X → Y (given)
2. X → Z (given)
3. X → XY (using IR2 on 1 by augmentation with X. Where XX = X)
4. XY → YZ (using IR2 on 2 by augmentation with Y)
5. X → YZ (using IR3 on 3 and 4)

5. Decomposition Rule (IR5)

Decomposition rule is also known as project rule. It is the reverse of union rule.

This Rule says, if X determines Y and Z, then X determines Y and X determines Z separately.

1. If X → YZ then X → Y and X → Z

Proof:

1. X → YZ (given)
2. YZ → Y (using IR1 Rule)
3. X → Y (using IR3 on 1 and 2)

6. Pseudo transitive Rule (IR6)

In Pseudo transitive Rule, if X determines Y and YZ determines W, then XZ determines W.

1. If X → Y and YZ → W then XZ → W

Proof:
1. X → Y (given)
2. WY → Z (given)
3. WX → WY (using IR2 on 1 by augmenting with W)
4. WX → Z (using IR3 on 3 and 2)

Normalization
A large database defined as a single relation may result in data duplication. This repetition of data may result
in:

o Making relations very large.

o It isn't easy to maintain and update data as it would involve searching many records in relation.
o Wastage and poor utilization of disk space and resources.
o The likelihood of errors and inconsistencies increases.

So to handle these problems, we should analyze and decompose the relations with redundant data into
smaller, simpler, and well-structured relations that are satisfy desirable properties. Normalization is a process of
decomposing the relations into relations with fewer attributes.

What is Normalization?
o Normalization is the process of organizing the data in the database.
o Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to
eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.
o Normalization divides the larger table into smaller and links them using relationships.
o The normal form is used to reduce redundancy from the database table.

Why do we need Normalization?

The main reason for normalizing the relations is removing these anomalies. Failure to eliminate anomalies leads
to data redundancy and can cause data integrity and other problems as the database grows. Normalization
consists of a series of guidelines that helps to guide you in creating a good database structure.

Data modification anomalies can be categorized into three types:

o Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple into a relationship
due to lack of data.
o Deletion Anomaly: The delete anomaly refers to the situation where the deletion of data results in the
unintended loss of some other important data.
o Updatation Anomaly: The update anomaly is when an update of a single data value requires multiple
rows of data to be updated.

Types of Normal Forms:

Normalization works through a series of stages called Normal forms. The normal forms apply to individual
relations. The relation is said to be in particular normal form if it satisfies constraints.

Following are the various types of Normal forms:

Normal Form Description

1NF A relation is in 1NF if it contains an atomic value.

2NF A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the

3NF A relation will be in 3NF if it is in 2NF and no transition dependency exists.

BCNF A stronger definition of 3NF is known as Boyce Codd's normal form.

4NF A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-valued dependency.

5NF A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining should be lossle

Advantages of Normalization
o Normalization helps to minimize data redundancy.
o Greater overall database organization.
o Data consistency within the database.
o Much more flexible database design.
o Enforces the concept of relational integrity.

Disadvantages of Normalization
 o You cannot start building the database before knowing what the user needs.
o The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF, 5NF.
o It is very time-consuming and difficult to normalize relations of a higher degree.
o Careless decomposition may lead to a bad database design, leading to serious problems.

First Normal Form (1NF)

o A relation will be 1NF if it contains an atomic value.
o It states that an attribute of a table cannot hold multiple values. It must hold only single-valued
attribute.
o First normal form disallows the multi-valued attribute, composite attribute, and their combinations.

Example: Relation EMPLOYEE is not in 1NF because of multi-valued attribute EMP_PHONE.

EMPLOYEE table:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385, UP
9064738238

20 Harry 8574783832 Bihar

12 Sam 7390372389, Punjab

8589830302

The decomposition of the EMPLOYEE table into 1NF has been shown below:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP

14 John 9064738238 UP

20 Harry 8574783832 Bihar

12 Sam 7390372389 Punjab

12 Sam 8589830302 Punjab

Second Normal Form (2NF)

o In the 2NF, relational must be in 1NF.
o In the second normal form, all non-key attributes are fully functional dependent on the primary key
Example: Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a
teacher can teach more than one subject.

TEACHER table

TEACHER_ID SUBJECT TEACHER_AGE

25 Chemistry 30

25 Biology 30

47 English 35

83 Math 38

83 Computer 38

In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of
a candidate key. That's why it violates the rule for 2NF.

To convert the given table into 2NF, we decompose it into two tables:

TEACHER_DETAIL table:

TEACHER_ID TEACHER_AGE

25 30

47 35

83 38

TEACHER_SUBJECT table:

TEACHER_ID SUBJECT

25 Chemistry

25 Biology

47 English

83 Math

83 Computer

Third Normal Form (3NF)

 o A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
o 3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
o If there is no transitive dependency for non-prime attributes, then the relation must be in third normal
form.

A relation is in third normal form if it holds atleast one of the following conditions for every non-trivial function
dependency X → Y.

1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.

Example:

EMPLOYEE_DETAIL table:

EMP_ID EMP_NAME EMP_ZIP EMP_STATE

222 Harry 201010 UP

333 Stephan 02228 US

444 Lan 60007 US

555 Katharine 06389 UK

666 John 462007 MP

Super key in the table above:

1. {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so on

Candidate key: {EMP_ID}

Non-prime attributes: In the given table, all attributes except EMP_ID are non-prime.

Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID. The non-
prime attributes (EMP_STATE, EMP_CITY) transitively dependent on super key(EMP_ID). It violates the
rule of third normal form.

That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP> table, with
EMP_ZIP as a Primary key.

EMPLOYEE table:

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010

 333 Stephan 02228

444 Lan 60007

555 Katharine 06389

666 John 462007

EMPLOYEE_ZIP table:

EMP_ZIP EMP_STATE EMP_CITY

201010 UP Noida

02228 US Boston

60007 US Chicago

06389 UK Norwich

462007 MP Bhopal

Boyce Codd normal form (BCNF)

o BCNF is the advance version of 3NF. It is stricter than 3NF.
o A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
o For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

Example: Let's assume there is a company where employees work in more than one department.

EMPLOYEE table:

EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO

264 India Designing D394 283

264 India Testing D394 300

364 UK Stores D283 232

364 UK Developing D283 549

In the above table Functional dependencies are as follows:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
 Candidate key: {EMP-ID, EMP-DEPT}

Keep Watching

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.

To convert the given table into BCNF, we decompose it into three tables:

EMP_COUNTRY table:

EMP_ID EMP_COUNTRY

264 India

264 India

EMP_DEPT table:

EMP_DEPT DEPT_TYPE EMP_DEPT_NO

Designing D394 283

Testing D394 300

Stores D283 232

Developing D283 549

EMP_DEPT_MAPPING table:

EMP_ID EMP_DEPT

D394 283

D394 300

D283 232

D283 549

Functional dependencies:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate keys:
For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}

Now, this is in BCNF because left side part of both the functional dependencies is a key.

Fourth normal form (4NF)

o A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued
dependency.
o For a dependency A → B, if for a single value of A, multiple values of B exists, then
the relation will be a multi-valued dependency.

Example
STUDENT

STU_ID COURSE HOBBY

21 Computer Dancing

21 Math Singing

34 Chemistry Dancing

74 Biology Cricket

59 Physics Hockey

The given STUDENT table is in 3NF, but the COURSE and HOBBY are two independent
entity. Hence, there is no relationship between COURSE and HOBBY.

In the STUDENT relation, a student with STU_ID, 21 contains two

courses, Computer and Math and two hobbies, Dancing and Singing. So there is a Multi-
valued dependency on STU_ID, which leads to unnecessary repetition of data.

So to make the above table into 4NF, we can decompose it into two tables:

STUDENT_COURSE
STU_ID COURSE

21 Computer

21 Math

34 Chemistry

74 Biology

59 Physics

STUDENT_HOBBY

STU_ID HOBBY

21 Dancing

21 Singing

34 Dancing

74 Cricket

59 Hockey

Fifth normal form (5NF)

o A relation is in 5NF if it is in 4NF and not contains any join dependency and joining
should be lossless.
o 5NF is satisfied when all the tables are broken into as many tables as possible in
order to avoid redundancy.
o 5NF is also known as Project-join normal form (PJ/NF).

Example
SUBJECT LECTURER SEMESTER

Computer Anshika Semester 1

Computer John Semester 1

Math John Semester 1

Math Akash Semester 2

Chemistry Praveen Semester 1

In the above table, John takes both Computer and Math class for Semester 1 but he
doesn't take Math class for Semester 2. In this case, combination of all these fields
required to identify a valid data.

Suppose we add a new Semester as Semester 3 but do not know about the subject and
who will be taking that subject so we leave Lecturer and Subject as NULL. But all three
columns together acts as a primary key, so we can't leave other two columns blank.

So to make the above table into 5NF, we can decompose it into three relations P1, P2 &
P3:

P1

SEMESTER SUBJECT

Semester 1 Computer

Semester 1 Math

Semester 1 Chemistry

Semester 2 Math

P2

SUBJECT LECTURER

Computer Anshika
Computer John

Math John

Math Akash

Chemistry Praveen

P3

SEMSTER LECTURER

Semester 1 Anshika

Semester 1 John

Semester 1 John

Semester 2 Akash

Semester 1 Praveen

Relational Decomposition
o When a relation in the relational model is not in appropriate normal form then the
decomposition of a relation is required.
o In a database, it breaks the table into multiple tables.
o If the relation has no proper decomposition, then it may lead to problems like loss
of information.
o Decomposition is used to eliminate some of the problems of bad design like
anomalies, inconsistencies, and redundancy.

Types of Decomposition
Lossless Decomposition

o If the information is not lost from the relation that is decomposed, then the
decomposition will be lossless.
o The lossless decomposition guarantees that the join of relations will result in the
same relation as it was decomposed.
o The relation is said to be lossless decomposition if natural joins of all the
decomposition give the original relation.

Example:

EMPLOYEE_DEPARTMENT table:

EMP_ID EMP_NAME EMP_AGE EMP_CITY

22 Denim 28 Mumbai

33 Alina 25 Delhi

46 Stephan 30 Bangalore

52 Katherine 36 Mumbai

60 Jack 40 Noida

The above relation is decomposed into two relations EMPLOYEE and DEPARTMENT
EMPLOYEE table:

EMP_ID EMP_NAME EMP_AGE EMP_CITY

22 Denim 28 Mumbai

33 Alina 25 Delhi

46 Stephan 30 Bangalore

52 Katherine 36 Mumbai

60 Jack 40 Noida

DEPARTMENT table

DEPT_ID EMP_ID DEPT_NAME

827 22 Sales

438 33 Marketing

869 46 Finance

575 52 Production

678 60 Testing

Now, when these two relations are joined on the common column "EMP_ID", then the
resultant relation will look like:

Employee ⋈ Department

EMP_ID EMP_NAME EMP_AGE EMP_CITY DEPT_ID DEPT_NAME

22 Denim 28 Mumbai 827 Sales

33 Alina 25 Delhi 438 Marketing

46 Stephan 30 Bangalore 869 Finance

52 Katherine 36 Mumbai 575 Production

60 Jack 40 Noida 678 Testing

Hence, the decomposition is Lossless join decomposition.

Dependency Preserving

o It is an important constraint of the database.

o In the dependency preservation, at least one decomposed table must satisfy every
dependency.
o If a relation R is decomposed into relation R1 and R2, then the dependencies of R
either must be a part of R1 or R2 or must be derivable from the combination of
functional dependencies of R1 and R2.
o For example, suppose there is a relation R (A, B, C, D) with functional dependency set
(A->BC). The relational R is decomposed into R1(ABC) and R2(AD) which is
dependency preserving because FD A->BC is a part of relation R1(ABC).

Multivalued Dependency
o Multivalued dependency occurs when two attributes in a table are independent of
each other but, both depend on a third attribute.
o A multivalued dependency consists of at least two attributes that are dependent on
a third attribute that's why it always requires at least three attributes.

Example: Suppose there is a bike manufacturer company which produces two colors(white
and black) of each model every year.

BIKE_MODEL MANUF_YEAR COLOR

M2011 2008 White

M2001 2008 Black

M3001 2013 White

 M3001 2013 Black

M4006 2017 White

M4006 2017 Black

Here columns COLOR and MANUF_YEAR are dependent on BIKE_MODEL and independent
of each other.

In this case, these two columns can be called as multivalued dependent on BIKE_MODEL.
The representation of these dependencies is shown below:

1. BIKE_MODEL → → MANUF_YEAR
2. BIKE_MODEL → → COLOR

This can be read as "BIKE_MODEL multidetermined MANUF_YEAR" and "BIKE_MODEL

multidetermined COLOR".

Join Dependency
o Join decomposition is a further generalization of Multivalued dependencies.
o If the join of R1 and R2 over C is equal to relation R, then we can say that a join
dependency (JD) exists.
o Where R1 and R2 are the decompositions R1(A, B, C) and R2(C, D) of a given
relations R (A, B, C, D).
o Alternatively, R1 and R2 are a lossless decomposition of R.
o A JD ⋈ {R1, R2,..., Rn} is said to hold over a relation R if R1, R2,....., Rn is a lossless-join
decomposition.
o The *(A, B, C, D), (C, D) will be a JD of R if the join of join's attribute is equal to the
relation R.
o Here, *(R1, R2, R3) is used to indicate that relation R1, R2, R3 and so on are a JD of R.

Inclusion Dependency
 o Multivalued dependency and join dependency can be used to guide database
design although they both are less common than functional dependencies.
o Inclusion dependencies are quite common. They typically show little influence on
designing of the database.
o The inclusion dependency is a statement in which some columns of a relation are
contained in other columns.
o The example of inclusion dependency is a foreign key. In one relation, the referring
relation is contained in the primary key column(s) of the referenced relation.
o Suppose we have two relations R and S which was obtained by translating two entity
sets such that every R entity is also an S entity.
o Inclusion dependency would be happen if projecting R on its key attributes yields a
relation that is contained in the relation obtained by projecting S on its key
attributes.
o In inclusion dependency, we should not split groups of attributes that participate in
an inclusion dependency.
o In practice, most inclusion dependencies are key-based that is involved only keys.

Canonical Cover
In the case of updating the database, the responsibility of the system is to check whether
the existing functional dependencies are getting violated during the process of updating.
In case of a violation of functional dependencies in the new database state, the rollback of
the system must take place.

A canonical cover or irreducible a set of functional dependencies FD is a simplified set of

FD that has a similar closure as the original set FD.

Extraneous attributes
An attribute of an FD is said to be extraneous if we can remove it without changing the
closure of the set of FD.

Example: Given a relational Schema R( A, B, C, D) and set of Function Dependency FD = {

B → A, AD → BC, C → ABD }. Find the canonical cover?
 Competitive questions on Structures in HindiKeep Watching

Solution: Given FD = { B → A, AD → BC, C → ABD }, now decompose the FD using

decomposition rule( Armstrong Axiom ).

1. B → A
2. AD → B ( using decomposition inference rule on AD → BC)
3. AD → C ( using decomposition inference rule on AD → BC)
4. C → A ( using decomposition inference rule on C → ABD)
5. C → B ( using decomposition inference rule on C → ABD)
6. C → D ( using decomposition inference rule on C → ABD)

Now set of FD = { B → A, AD → B, AD → C, C → A, C → B, C → D }

The next step is to find closure of the left side of each of the given FD by including that FD
and excluding that FD, if closure in both cases are same then that FD is redundant and we
remove that FD from the given set, otherwise if both the closures are different then we do
not exclude that FD.

Calculating closure of all FD { B → A, AD → B, AD → C, C → A, C → B, C → D }

1a. Closure B+ = BA using FD = { B → A, AD → B, AD → C, C → A, C → B, C → D }

1b. Closure B+ = B using FD = { AD → B, AD → C, C → A, C → B, C → D }

From 1 a and 1 b, we found that both the Closure( by including B → A and excluding B →
A ) are not equivalent, hence FD B → A is important and cannot be removed from the set
of FD.

2 a. Closure AD+ = ADBC using FD = { B →A, AD → B, AD → C, C → A, C → B, C → D }

2 b. Closure AD+ = ADCB using FD = { B → A, AD → C, C → A, C → B, C → D }

From 2 a and 2 b, we found that both the Closure (by including AD → B and excluding AD
→ B) are equivalent, hence FD AD → B is not important and can be removed from the set
of FD.

Hence resultant FD = { B → A, AD → C, C → A, C → B, C → D }

3 a. Closure AD+ = ADCB using FD = { B →A, AD → C, C → A, C → B, C → D }

3 b. Closure AD+ = AD using FD = { B → A, C → A, C → B, C → D }

From 3 a and 3 b, we found that both the Closure (by including AD → C and excluding AD
→ C ) are not equivalent, hence FD AD → C is important and cannot be removed from the
set of FD.

Hence resultant FD = { B → A, AD → C, C → A, C → B, C → D }

4 a. Closure C+ = CABD using FD = { B →A, AD → C, C → A, C → B, C → D }

4 b. Closure C+ = CBDA using FD = { B → A, AD → C, C → B, C → D }

From 4 a and 4 b, we found that both the Closure (by including C → A and excluding C →
A) are equivalent, hence FD C → A is not important and can be removed from the set of
FD.

Hence resultant FD = { B → A, AD → C, C → B, C → D }

5 a. Closure C+ = CBDA using FD = { B →A, AD → C, C → B, C → D }

5 b. Closure C+ = CD using FD = { B → A, AD → C, C → D }

From 5 a and 5 b, we found that both the Closure (by including C → B and excluding C →
B) are not equivalent, hence FD C → B is important and cannot be removed from the set of
FD.

Hence resultant FD = { B → A, AD → C, C → B, C → D }

6 a. Closure C+ = CDBA using FD = { B →A, AD → C, C → B, C → D }

6 b. Closure C+ = CBA using FD = { B → A, AD → C, C → B }

From 6 a and 6 b, we found that both the Closure( by including C → D and excluding C →
D) are not equivalent, hence FD C → D is important and cannot be removed from the set
of FD.

Hence resultant FD = { B → A, AD → C, C → B, C → D }

o Since FD = { B → A, AD → C, C → B, C → D } is resultant FD, now we have checked

the redundancy of attribute, since the left side of FD AD → C has two attributes, let's
check their importance, i.e. whether they both are important or only one.

Closure AD+ = ADCB using FD = { B →A, AD → C, C → B, C → D }

Closure A+ = A using FD = { B →A, AD → C, C → B, C → D }

Closure D+ = D using FD = { B →A, AD → C, C → B, C → D }

Since the closure of AD+, A+, D+ that we found are not all equivalent, hence in FD AD →
C, both A and D are important attributes and cannot be removed.

Hence resultant FD = { B → A, AD → C, C → B, C → D } and we can rewrite as

FD = { B → A, AD → C, C → BD } is Canonical Cover of FD = { B → A, AD → BC, C →

ABD }.

Example 2: Given a relational Schema R( W, X, Y, Z) and set of Function Dependency FD =

{ W → X, Y → X, Z → WXY, WY → Z }. Find the canonical cover?

Solution: Given FD = { W → X, Y → X, Z → WXY, WY → Z }, now decompose the FD using

decomposition rule( Armstrong Axiom ).

1. W → X
2. Y → X
3. Z → W ( using decomposition inference rule on Z → WXY )
4. Z → X ( using decomposition inference rule on Z → WXY )
5. Z → Y ( using decomposition inference rule on Z → WXY )
6. WY → Z

Now set of FD = { W → X, Y → X, WY → Z, Z → W, Z → X, Z → Y }

The next step is to find closure of the left side of each of the given FD by including that FD
and excluding that FD, if closure in both cases are same then that FD is redundant and we
remove that FD from the given set, otherwise if both the closures are different then we do
not exclude that FD.

Calculating closure of all FD { W → X, Y → X, Z → W, Z → X, Z → Y, WY → Z }

1 a. Closure W+ = WX using FD = { W → X, Y → X, Z → W, Z → X, Z → Y, WY → Z }

1 b. Closure W+ = W using FD = { Y → X, Z → W, Z → X, Z → Y, WY → Z }

From 1 a and 1 b, we found that both the Closure (by including W → X and excluding W →
X ) are not equivalent, hence FD W → X is important and cannot be removed from the set
of FD.
Hence resultant FD = { W → X, Y → X, Z → W, Z → X, Z → Y, WY → Z }

2 a. Closure Y+ = YX using FD = { W → X, Y → X, Z → W, Z → X, Z → Y, WY → Z }

2 b. Closure Y+ = Y using FD = { W → X, Z → W, Z → X, Z → Y, WY → Z }

From 2 a and 2 b we found that both the Closure (by including Y → X and excluding Y →
X ) are not equivalent, hence FD Y → X is important and cannot be removed from the set
of FD.

Hence resultant FD = { W → X, Y → X, Z → W, Z → X, Z → Y, WY → Z }

3 a. Closure Z+ = ZWXY using FD = { W → X, Y → X, Z → W, Z → X, Z → Y, WY → Z }

3 b. Closure Z+ = ZXY using FD = { W → X, Y → X, Z → X, Z → Y, WY → Z }

From 3 a and 3 b, we found that both the Closure (by including Z → W and excluding Z →
W ) are not equivalent, hence FD Z → W is important and cannot be removed from the set
of FD.

Hence resultant FD = { W → X, Y → X, Z → W, Z → X, Z → Y, WY → Z }

4 a. Closure Z+ = ZXWY using FD = { W → X, Y → X, Z → W, Z → X, Z → Y, WY → Z }

4 b. Closure Z+ = ZWYX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

From 4 a and 4 b, we found that both the Closure (by including Z → X and excluding Z →
X ) are equivalent, hence FD Z → X is not important and can be removed from the set of
FD.

Hence resultant FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

5 a. Closure Z+ = ZYWX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

5 b. Closure Z+ = ZWX using FD = { W → X, Y → X, Z → W, WY → Z }

From 5 a and 5 b, we found that both the Closure (by including Z → Y and excluding Z →
Y ) are not equivalent, hence FD Z → X is important and cannot be removed from the set
of FD.

Hence resultant FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

6 a. Closure WY+ = WYZX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

6 b. Closure WY+ = WYX using FD = { W → X, Y → X, Z → W, Z → Y }

From 6 a and 6 b, we found that both the Closure (by including WY → Z and
excluding WY → Z) are not equivalent, hence FD WY → Z is important and cannot be
removed from the set of FD.

Hence resultant FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

Since FD = { W → X, Y → X, Z → W, Z → Y, WY → Z } is resultant FD now, we have checked

the redundancy of attribute, since the left side of FD WY → Z has two attributes at its left,
let's check their importance, i.e. whether they both are important or only one.

Closure WY+ = WYZX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

Closure W+ = WX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

Closure Y+ = YX using FD = { W → X, Y → X, Z → W, Z → Y, WY → Z }

Since the closure of WY+, W+, Y+ that we found are not all equivalent, hence in FD WY →
Z, both W and Y are important attributes and cannot be removed.

Hence resultant FD = { W → X, Y → X, Z → W, Z → Y, WY → Z } and we can rewrite as:

FD = { W → X, Y → X, Z → WY, WY → Z } is Canonical Cover of FD = { W → X, Y → X, Z

→ WXY, WY → Z }.

Example 3: Given a relational Schema R( V, W, X, Y, Z) and set of Function Dependency FD

= { V → W, VW → X, Y → VXZ }. Find the canonical cover?

Solution: Given FD = { V → W, VW → X, Y → VXZ }. now decompose the FD using

decomposition rule (Armstrong Axiom).

1. V → W
2. VW → X
3. Y → V ( using decomposition inference rule on Y → VXZ )
4. Y → X ( using decomposition inference rule on Y → VXZ )
5. Y → Z ( using decomposition inference rule on Y → VXZ )

Now set of FD = { V → W, VW → X, Y → V, Y → X, Y → Z }.
The next step is to find closure of the left side of each of the given FD by including that FD
and excluding that FD, if closure in both cases are same then that FD is redundant and we
remove that FD from the given set, otherwise if both the closures are different then we do
not exclude that FD.

Calculating closure of all FD { V → W, VW → X, Y → V, Y → X, Y → Z }.

1 a. Closure V+ = VWX using FD = {V → W, VW → X, Y → V, Y → X, Y → Z}

1 b. Closure V+ = V using FD = {VW → X, Y → V, Y → X, Y → Z }

From 1 a and 1 b, we found that both the Closure( by including V → W and excluding V →
W ) are not equivalent, hence FD V → W is important and cannot be removed from the set
of FD.

Hence resultant FD = { V → W, VW → X, Y → V, Y → X, Y → Z }.

2 a. Closure VW+ = VWX using FD = { V → W, VW → X, Y → V, Y → X, Y → Z }

2 b. Closure VW+ = VW using FD = { V → W, Y → V, Y → X, Y → Z }

From 2 a and 2 b, we found that both the Closure( by including VW → X and

excluding VW → X ) are not equivalent, hence FD VW → X is important and cannot be
removed from the set of FD.

Hence resultant FD = { V → W, VW → X, Y → V, Y → X, Y → Z }.

3 a. Closure Y+ = YVXZW using FD = { V → W, VW → X, Y → V, Y → X, Y → Z }

3 b. Closure Y+ = YXZ using FD = { V → W, VW → X, Y → X, Y → Z }

From 3 a and 3 b, we found that both the Closure( by including Y → V and excluding Y →
V ) are not equivalent, hence FD Y → V is important and cannot be removed from the set
of FD.

Hence resultant FD = { V → W, VW → X, Y → V, Y → X, Y → Z }.

4 a. Closure Y+ = YXVZW using FD = { V → W, VW → X, Y → V, Y → X, Y → Z }

4 b. Closure Y+ = YVZWX using FD = { V → W, VW → X, Y → V, Y → Z }

From 4 a and 4 b, we found that both the Closure( by including Y → X and excluding Y →
X ) are equivalent, hence FD Y → X is not important and can be removed from the set of
FD.

Hence resultant FD = { V → W, VW → X, Y → V, Y → Z }.

5 a. Closure Y+ = YZVWX using FD = { V → W, VW → X, Y → V, Y → Z }

5 b. Closure Y+ = YVWX using FD = { V → W, VW → X, Y → V }

From 5 a and 5 b, we found that both the Closure( by including Y → Z and excluding Y →
Z ) are not equivalent, hence FD Y → Z is important and cannot be removed from the set
of FD.

Hence resultant FD = { V → W, VW → X, Y → V, Y → Z }.

Since FD = { V → W, VW → X, Y → V, Y → Z } is resultant FD now, we have checked the

redundancy of attribute, since the left side of FD VW → X has two attributes at its left, let's
check their importance, i.e. whether they both are important or only one.

Closure VW+ = VWX using FD = { V → W, VW → X, Y → V, Y → Z }

Closure V+ = VWX using FD = { V → W, VW → X, Y → V, Y → Z }

Closure W+ = W using FD = { V → W, VW → X, Y → V, Y → Z }

Since the closure of VW+, V+, W+ we found that all the Closures of VW and V are
equivalent, hence in FD VW → X, W is not at all an important attribute and can be
removed.

Hence resultant FD = { V → W, V → X, Y → V, Y → Z } and we can rewrite as

FD = { V → WX, Y → VZ } is Canonical Cover of FD = { V → W, VW → X, Y → VXZ }.

CONCLUSION: From the above three examples we conclude that canonical cover /
irreducible set of functional dependency follows the following steps, which we need to
follow while calculating Canonical Cover.

STEP 1: For a given set of FD, decompose each FD using decomposition rule (Armstrong
Axiom) if the right side of any FD has more than one attribute.

STEP 2: Now make a new set of FD having all decomposed FD.

STEP 3: Find closure of the left side of each of the given FD by including that FD and
excluding that FD, if closure in both cases are same then that FD is redundant and we
remove that FD from the given set, otherwise if both the closures are different then we do
not exclude that FD.

STEP 4: Repeat step 4 till all the FDs in FD set are complete.

STEP 5: After STEP 4, find resultant FD = { B → A, AD → C, C → B, C → D } which are not

redundant.

STEP 6: Check redundancy of attribute, by selecting those FD's from FD sets which are
having more than one attribute on its left, let's an FD AD → C has two attributes at its left,
let's check their importance, i.e. whether they both are important or only one.

STEP 6 a: Find Closure AD+

STEP 6 b: Find Closure A+

STEP 6 c: Find Closure D+

Compare Closure of STEP (6a, 6b, 6c) if the closure of AD+, A+, D+ are not equivalent,
hence in FD AD → C, both A and D are important attributes and cannot be removed,
otherwise, we remove the redundant attribute.
SQL
o SQL stands for Structured Query Language. It is used for storing and managing data
in relational database management system (RDMS).
o It is a standard language for Relational Database System. It enables a user to create,
read, update and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as
their standard database language.
o SQL allows users to query the database in a number of ways, using English-like
statements.

Rules:
SQL follows the following rules:

o Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase.
o Statements of SQL are dependent on text lines. We can use a single SQL statement
on one or multiple text line.
o Using the SQL statements, you can perform most of the actions in a database.
o SQL depends on tuple relational calculus and relational algebra.

SQL process:

o When an SQL command is executing for any RDBMS, then the system figure out the
best way to carry out the request and the SQL engine determines that how to
interpret the task.
o In the process, various components are included. These components can be
optimization Engine, Query engine, Query dispatcher, classic, etc.
o All the non-SQL queries are handled by the classic query engine, but SQL query
engine won't handle logical files.
Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate it when needed.
o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a database.
o SQL allows users to set permissions on tables, procedures, and views.

Advantages of SQL
There are the following advantages of SQL:

High speed
Using the SQL queries, the user can quickly and efficiently retrieve a large amount of
records from a database.
No coding needed
In the standard SQL, it is very easy to manage the database system. It doesn't require a
substantial amount of code to manage the database system.

Well defined standards

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

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

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

Multiple data view

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

SQL Datatype
o SQL Datatype is used to define the values that a column can contain.
o Every column is required to have a name and data type in the database table.

Datatype of SQL:
1. Binary Datatypes
There are Three types of binary Datatypes which are given below:

Data Type Description

binary It has a maximum length of 8000 bytes. It contains fixed-length binary

data.

varbinary It has a maximum length of 8000 bytes. It contains variable-length binary

data.

image It has a maximum length of 2,147,483,647 bytes. It contains variable-

length binary data.

2. Approximate Numeric Datatype :

The subtypes are given below:

Data From To Description

type

float -1.79E + 1.79E + 308 It is used to specify a floating-point value e.g.

308 6.2, 2.9 etc.
real -3.40e + 3.40E + 38 It specifies a single precision floating point
38 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 five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
1. Data Definition Language (DDL)

o DDL changes the structure of the table like creating a table, deleting a table,

altering a table, etc.

o All the command of DDL are auto-committed that means it permanently save all

the changes in the database.

Here are some commands that come under DDL:

o CREATE

o ALTER

o DROP
 o TRUNCATE

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

Syntax:

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

Example:

1. CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax

1. DROP TABLE table_name;

Example

1. DROP TABLE EMPLOYEE;

c. ALTER: It is used to alter the structure of the database. This change could be either
to modify the characteristics of an existing attribute or probably to add a new attribute.

Syntax:

To add a new column in the table

1. ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

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

 EXAMPLE

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

2. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

d. TRUNCATE: It is used to delete all the rows from the table and free the space
containing the table.

Syntax:

1. TRUNCATE TABLE table_name;

Example:

1. TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

o DML commands are used to modify the database. It is responsible for all form of

changes in the database.

o The command of DML is not auto-committed that means it can't permanently

save all the changes in the database. They can be rollback.

Here are some commands that come under DML:

o INSERT

o UPDATE

o DELETE
 a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row
of a table.

Syntax:

1. INSERT INTO TABLE_NAME

2. (col1, col2, col3,.... col N)

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

Or

1. INSERT INTO TABLE_NAME

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

For example:

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

b. UPDATE: This command is used to update or modify the value of a column in the
table.

Syntax:

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

CONDITION]

For example:

1. UPDATE students

2. SET User_Name = 'Sonoo'

3. WHERE Student_Id = '3'

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

Syntax:

1. DELETE FROM table_name [WHERE condition];

For example:

1. DELETE FROM javatpoint

2. WHERE Author="Sonoo";

3. Data Control Language

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

Here are some commands that come under DCL:

o Grant

o Revoke

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

Example

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

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

Example

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

 4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE
only.

These operations are automatically committed in the database that's why they cannot
be used while creating tables or dropping them.

Here are some commands that come under TCL:

o COMMIT

o ROLLBACK

o SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database.

Syntax:

1. COMMIT;

Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;

3. COMMIT;

b. Rollback: Rollback command is used to undo transactions that have not already
been saved to the database.

Syntax:

1. ROLLBACK;
 Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;

3. ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling
back the entire transaction.

Syntax:

1. SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

o SELECT

a. SELECT: This is the same as the projection operation of relational algebra. It is used
to select the attribute based on the condition described by WHERE clause.

Syntax:

1. SELECT expressions

2. FROM TABLES

3. WHERE conditions;

For example:
1. SELECT emp_name

2. FROM employee

3. WHERE age > 20;

 SQL Operator

There are various types of SQL operator:

SQL Arithmetic Operators

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

Operator Description Example

+ It adds the value of both operands. a+b will give 30

- It is used to subtract the right-hand a-b will give 10

operand from the left-hand operand.

* It is used to multiply the value of both a*b will give 200

operands.

/ It is used to divide the left-hand a/b will give 2

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

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, (a=b) is not true
if the values are queal then condition becomes
true.

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

if values are not equal, then condition becomes
true.

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

> It checks if the left operand value is greater than (a>b) is not true
right operand value, if yes then condition
becomes true.

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

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

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

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

SQL Logical Operators

There is the list of logical operator used in SQL:

Operator Description

ALL It compares a value to all values in another value set.

AND It allows the existence of multiple conditions in an SQL

statement.

ANY It compares the values in the list according to the

condition.

BETWEEN It is used to search for values that are within a set of

values.

IN It compares a value to that specified list value.

NOT It reverses the meaning of any logical operator.

OR It combines multiple conditions in SQL statements.

EXISTS It is used to search for the presence of a row in a specified

table.

LIKE It compares a value to similar values using wildcard

operator.

SQL Table

o SQL Table is a collection of data which is organized in terms of rows and columns. In DBMS,

the table is known as relation and row as a tuple.

o Table is a simple form of data storage. A table is also considered as a convenient

representation of relations.

Let's see an example of the EMPLOYEE table:

EMP_ID EMP_NAME CITY PHONE_NO

1 Kristen Washington 7289201223

2 Anna Franklin 9378282882

3 Jackson Bristol 9264783838

 4 Kellan California 7254728346

5 Ashley Hawaii 9638482678

In the above table, "EMPLOYEE" is the table name, "EMP_ID", "EMP_NAME", "CITY", "PHONE_NO" are
the column names. The combination of data of multiple columns forms a row, e.g., 1, "Kristen",
"Washington" and 7289201223 are the data of one row.

Operation on Table

1. Create table

2. Drop table

3. Delete table

4. Rename table

SQL Create Table

SQL create table is used to create a table in the database. To define the table, you should define the
name of the table and also define its columns and column's data type.

Syntax

00:00/04:58
47.2M
869
HTML Tutorial

1. create table "table_name"

2. ("column1" "data type",

3. "column2" "data type",

4. "column3" "data type",

5. ...

6. "columnN" "data type");

Example

1. SQL> CREATE TABLE EMPLOYEE (

2. EMP_ID INT NOT NULL,

3. EMP_NAME VARCHAR (25) NOT NULL,

4. PHONE_NO INT NOT NULL,

5. ADDRESS CHAR (30),

6. PRIMARY KEY (ID)

7. );

If you create the table successfully, you can verify the table by looking at the message by the SQL
server. Else you can use DESC command as follows:

SQL> DESC EMPLOYEE;

Field Type Null Key Default Extra

 EMP_ID int(11) NO PRI NULL

EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)

o 4 rows in set (0.35 sec)

Now you have an EMPLOYEE table in the database, and you can use the stored information related to
the employees.

Drop table

A SQL drop table is used to delete a table definition and all the data from a table. When this
command is executed, all the information available in the table is lost forever, so you have to very
careful while using this command.

Syntax

1. DROP TABLE "table_name";

Firstly, you need to verify the EMPLOYEE table using the following command:

1. SQL> DESC EMPLOYEE;

Field Type Null Key Default Extra

EMP_ID int(11) NO PRI NULL

 EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)

o 4 rows in set (0.35 sec)

This table shows that EMPLOYEE table is available in the database, so we can drop it as follows:

1. SQL>DROP TABLE EMPLOYEE;

Now, we can check whether the table exists or not using the following command:

1. Query OK, 0 rows affected (0.01 sec)

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

SQL DELETE table

In SQL, DELETE statement is used to delete rows from a table. We can use WHERE condition to delete
a specific row from a table. If you want to delete all the records from the table, then you don't need to
use the WHERE clause.

Syntax

1. DELETE FROM table_name WHERE condition;

Example

Suppose, the EMPLOYEE table having the following records:

 EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Denzel Boston 7353662627 100000

4 Angelina Denver 9232673822 600000

5 Robert Washington 9367238263 350000

6 Christian Los angels 7253847382 260000

The following query will DELETE an employee whose ID is 2.

1. SQL> DELETE FROM EMPLOYEE

2. WHERE EMP_ID = 3;

Now, the EMPLOYEE table would have the following records.

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

 4 Angelina Denver 9232673822 600000

5 Robert Washington 9367238263 350000

6 Christian Los angels 7253847382 260000

If you don't specify the WHERE condition, it will remove all the rows from the table.

1. DELETE FROM EMPLOYEE;

Now, the EMPLOYEE table would not have any records.

SQL SELECT Statement

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

Syntax

1. SELECT column1, column2, ...

2. FROM table_name;

Here, the expression is the field name of the table that you want to select data from.

Use the following syntax to select all the fields available in the table:

1. SELECT * FROM table_name;

Example:

EMPLOYEE
 EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

4 Robert Washington 9367238263 350000

5 Christian Los angels 7253847382 260000

To fetch the EMP_ID of all the employees, use the following query:

1. SELECT EMP_ID FROM EMPLOYEE;

Output

EMP_ID

5
 To fetch the EMP_NAME and SALARY, use the following query:

1. SELECT EMP_NAME, SALARY FROM EMPLOYEE;

EMP_NAME SALARY

Kristen 150000

Russell 200000

Angelina 600000

Robert 350000

Christian 260000

To fetch all the fields from the EMPLOYEE table, use the following query:

1. SELECT * FROM EMPLOYEE

Output

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

4 Robert Washington 9367238263 350000

5 Christian Los angels 7253847382 260000

SQL INSERT Statement

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

1. Without specifying column name

2. By specifying column name

Sample Table

EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

1. Without specifying column name

 If you want to specify all column values, you can specify or ignore the column values.

Syntax

Competitive questions on Structures in HindiKeep Watching

1. INSERT INTO TABLE_NAME

2. VALUES (value1, value2, value 3, .... Value N);

Query

1. INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000, 48);

Output: After executing this query, the EMPLOYEE table will look like:

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

2. By specifying column name

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

Syntax

1. INSERT INTO TABLE_NAME

2. [(col1, col2, col3,.... col N)]

3. VALUES (value1, value2, value 3, .... Value N);

Query

1. INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7, 'Jack', 40);

Output: After executing this query, the table will look like:

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

 7 Jack null null 40

SQL Update Statement

The SQL UPDATE statement is used to modify the data that is already in the database. The condition in the
WHERE clause decides that which row is to be updated.

Syntax

1. UPDATE table_name

2. SET column1 = value1, column2 = value2, ...

3. WHERE condition;

Sample Table

EMPLOYEE

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

Updating single record

Update the column EMP_NAME and set the value to 'Emma' in the row where SALARY is 500000.

Competitive questions on Structures in HindiKeep Watching

Syntax

1. UPDATE table_name

2. SET column_name = value

3. WHERE condition;

Query

1. UPDATE EMPLOYEE

2. SET EMP_NAME = 'Emma'

3. WHERE SALARY = 500000;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

 3 Christian Denver 100000 42

4 Emma Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Updating multiple records

If you want to update multiple columns, you should separate each field assigned with a comma. In the
EMPLOYEE table, update the column EMP_NAME to 'Kevin' and CITY to 'Boston' where EMP_ID is 5.

Syntax

1. UPDATE table_name

2. SET column_name = value1, column_name2 = value2

3. WHERE condition;

Query

1. UPDATE EMPLOYEE

2. SET EMP_NAME = 'Kevin', City = 'Boston'

3. WHERE EMP_ID = 5;

Output
 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 Kevin Boston 200000 36

6 Marry Canada 600000 48

Without use of WHERE clause

If you want to update all row from a table, then you don't need to use the WHERE clause. In the EMPLOYEE
table, update the column EMP_NAME as 'Harry'.

Syntax

1. UPDATE table_name

2. SET column_name = value1;

Query

1. UPDATE EMPLOYEE

2. SET EMP_NAME = 'Harry';

Output
 EMP_ID EMP_NAME CITY SALARY AGE

1 Harry Chicago 200000 30

2 Harry Austin 300000 26

3 Harry Denver 100000 42

4 Harry Washington 500000 29

5 Harry Los angels 200000 36

6 Harry Canada 600000 48

NSQL DELETE Statement

The SQL DELETE statement is used to delete rows from a table. Generally, DELETE statement removes
one or more records form a table.

Syntax

1. DELETE FROM table_name WHERE some_condition;

Sample Table

EMPLOYEE

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

Deleting Single Record

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

8.3M
253
Java Tricky Program 16 - Autoboxing, Inheritance and Overriding

Query

1. DELETE FROM EMPLOYEE

2. WHERE EMP_NAME = 'Kristen';

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

5 Russell Los 200000 36

angels

6 Marry Canada 600000 48

Deleting Multiple Record

 Delete the row from the EMPLOYEE table where AGE is 30. This will delete two rows(first and third
row).

Query

1. DELETE FROM EMPLOYEE WHERE AGE= 30;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

2 Robert Austin 300000 26

3 Christian Denver 100000 42

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Delete all of the records

Delete all the row from the EMPLOYEE table. After this, no records left to display. The EMPLOYEE table
will become empty.

Syntax

1. DELETE * FROM table_name;

2. or

3. DELETE FROM table_name;

Query

1. DELETE FROM EMPLOYEE;

Output: After executing this query, the EMPLOYEE table will look like:
EMP_ID EMP_NAME CITY

Note: Using the condition in the WHERE clause, we can delete single as well as multiple
records. If you want to delete all the records from the table, then you don't need to use the
WHERE clause.

Views in SQL

o Views in SQL are considered as a virtual table. A view also contains rows and columns.

o To create the view, we can select the fields from one or more tables present in the

database.

o A view can either have specific rows based on certain condition or all the rows of a table.

Sample table:

Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi

2 Kathrin Noida

3 David Ghaziabad

4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19
 2 Kathrin 86 21

3 David 74 18

4 Alina 90 20

5 John 96 18

1. Creating view

A view can be created using the CREATE VIEW statement. We can create a view from a
single table or multiple tables.

Syntax:

00:00/05:19

1. CREATE VIEW view_name AS

2. SELECT column1, column2.....

3. FROM table_name

4. WHERE condition;

2. Creating View from a single table

In this example, we create a View named DetailsView from the table Student_Detail.

Query:

1. CREATE VIEW DetailsView AS

2. SELECT NAME, ADDRESS

3. FROM Student_Details

4. WHERE STU_ID < 4;

Just like table query, we can query the view to view the data.
1. SELECT * FROM DetailsView;

Output:

NAME ADDRESS

Stephan Delhi

Kathrin Noida

David Ghaziabad

3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the SELECT
statement.

In the given example, a view is created named MarksView from two tables Student_Detail and
Student_Marks.

Query:

1. CREATE VIEW MarksView AS

2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS

3. FROM Student_Detail, Student_Mark

4. WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView:

1. SELECT * FROM MarksView;

NAME ADDRESS MARKS

Stephan Delhi 97
 Kathrin Noida 86

David Ghaziabad 74

Alina Gurugram 90

4. Deleting View

A view can be deleted using the Drop View statement.

Syntax

1. DROP VIEW view_name;

Example:

If we want to delete the View MarksView, we can do this as:

1. DROP VIEW MarksView;

SQL Index

o Indexes are special lookup tables. It is used to retrieve data from the database very fast.

o An Index is used to speed up select queries and where clauses. But it shows down the

data input with insert and update statements. Indexes can be created or dropped without

affecting the data.

o An index in a database is just like an index in the back of a book.

o For example: When you reference all pages in a book that discusses a certain topic, you

first have to refer to the index, which alphabetically lists all the topics and then referred

to one or more specific page numbers.

1. Create Index statement

It is used to create an index on a table. It allows duplicate value.

 Syntax

1. CREATE INDEX index_name

2. ON table_name (column1, column2, ...);

Example

1. CREATE INDEX idx_name

2. ON Persons (LastName, FirstName);

2. Unique Index statement

It is used to create a unique index on a table. It does not allow duplicate value.

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

00:00/10:59
45.5K
Everything NEW in iOS 16 & iPadOS 16 Beta 3! Share Photo Library, Lockdown Mode, &
More!

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 ADDRE

1 John 20 US

2 Stephan 26 Dubai

3 David 27 Bangkok

4 Alina 29 UK

5 Kathrin 34 Bangalor

6 Harry 42 China

7 Jackson 25 Mizoram

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:

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, [Link]

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, [Link]

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, [Link]

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, [Link]

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