1. What is database normalization?

Ans: It is a process of analyzing the given relation schemas based on their

functional dependencies and primary keys to achieve the following desirable
properties:
1) Minimizing Redundancy
2) Minimizing the Insertion, Deletion, And Update Anomalies
Relation schemas that do not meet the properties are decomposed into smaller
relation schemas that could meet desirable properties.
2. What is SQL?
SQL is Structured Query Language designed for inserting and modifying in a
relational database system.
3. What are the differences between DDL, DML and DCL in SQL?

Ans: Following are some details of three.

DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and RENAME

come under this.

DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT, DELETE and

UPDATE come under this.

DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under this.

4. What is the difference between having and where clause?

Ans: HAVING is used to specify a condition for a group or an aggregate function used in select

statement. The WHERE clause selects before grouping. The HAVING clause selects rows after

grouping. Unlike HAVING clause, the WHERE clause cannot contain aggregate functions.
5. What is Join?

Ans: An SQL Join is used to combine data from two or more tables, based
on a common field between them. For example, consider the following two
tables.

Student Table StudentCourse Table

ENROLLN STUDENTNAM COURSEID ENROLLNO

O E ADDRESS 1 1000

1000 geek1 geeksquiz1 2 1000

1001 geek2 geeksquiz2 3 1000

1002 geek3 geeksquiz3 1 1002

 SELECT StudentCourse.CourseID, Student.StudentName
FROM StudentCourse
INNER JOIN Student
ON StudentCourse.EnrollNo = Student.EnrollNo
ORDER BY StudentCourse.CourseID;

COURSEID STUDENTNAME

Result
1 geek1

1 geek2

2 geek1

2 geek3

3 geek1
6.What is Identity?
Ans: Identity (or AutoNumber) is a column that automatically generates numeric
values. Start and increment value can be set, but most DBA leave these at 1. A GUID
(Globally unique identifier) column also generates numbers; Identity/GUID columns
do not need to be indexed.

CREATE TABLE Persons (

Personid int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

PRIMARY KEY (Personid)

);
7. What is a view in SQL? How to create one
Ans: A view is a virtual table based on the result-set of an SQL
statement. We can create using create view syntax.

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition
8. What are the uses of view?
1. Views can represent a subset of the data contained in a table; consequently, a
view can limit the degree of exposure of the underlying tables to the outer world:
a given user may have permission to query the view, while denied access to the
rest of the base table.

2. Views can join and simplify multiple tables into a single virtual table

3. Views can act as aggregated tables, where the database engine aggregates
data (sum, average etc.) and presents the calculated results as part of the data

4. Views take very little space to store; the database contains only the definition
of a view, not a copy of all the data which it present.

5. Depending on the SQL engine used, views can provide extra security
9. What is a Trigger?
Ans: A Trigger is a code that associated with insert, update or delete
operations. The code is executed automatically whenever the
associated query is executed on a table. Triggers can be useful to
maintain integrity in database.

10. What is a stored procedure?

Ans: A stored procedure is a precompiled SQL code that performs a
specific task or set of tasks, such as data retrieval, insertion, update,
or deletion. It is a reusable program stored in a database
management system (DBMS) that can be executed repeatedly with
different input parameters.
Example:

CREATE PROCEDURE SelectAllCustomers

AS

SELECT * FROM Customers

GO;

Execute the stored procedure above as follows:

Example:

EXEC SelectAllCustomers;

11. What is the difference between Trigger and Stored Procedure?

Ans: Unlike Stored Procedures, Triggers cannot be called directly. They can
only be associated with queries.
12. What is a transaction? What are ACID properties?
Ans: A Database Transaction is a set of database operations that must be
treated as whole, means either all operations are executed or none of
them.
An example can be bank transaction from one account to another account.
Either both debit and credit operations must be executed or none of them.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties
that guarantee that database transactions are processed reliably.

13. What are indexes?

Ans: A database index is a data structure that improves the speed of data
retrieval operations on a database table at the cost of additional writes
and the use of more storage space to maintain the extra copy of data.
Data can be stored only in one order on disk. To support faster access
according to different values, faster search like binary search for different
values is desired, For this purpose, indexes are created on tables. These
indexes need extra space on disk, but they allow faster search according
to different frequently searched values.
14. What are clustered and non-clustered Indexes?
Ans: Clustered indexes is the index according to which data
is physically stored on disk. Therefore, only one clustered index
can be created on a given database table.
CREATE CLUSTERED INDEX CI_EmployeeID ON
Employees(EmployeeID);

Non-clustered indexes don’t define physical ordering of data,

but logical ordering. Typically, a tree is created whose leaf point
to disk records. B-Tree or B+ tree are used for this purpose.
CREATE NONCLUSTERED INDEX NCI_Department ON Employees(Department);
e is a table where only one row is fully repeated. Write a Query to find the Repeated r

Name Section

abc CS1

bcd CS2

abc CS1

In the above table, we can find

duplicate row using below query.
SELECT name, section FROM tbl
GROUP BY name, section
HAVING COUNT(*) > 1
16. Query to find 2nd highest salary of an employee?

SELECT max(salary) FROM EMPLOYEES WHERE salary IN

(SELECT salary FROM EMPLOYEEs MINUS SELECT max(salary)

FROM EMPLOYEES);

OR

SELECT *

FROM

( SELECT salary, ROWNUM AS rownum

FROM (

SELECT DISTINCT salary FROM employees ORDER BY salary DESC ) AS subquery

)

WHERE rownum = 2;
17.There is a table which contains two column
Student and Marks, you need to find all the
students, whose marks are greater than average
marks i.e. list of above average students.

SELECT student, marks

FROM table

WHERE marks > SELECT AVG(marks) from table;

18. Name the employee who is having third highest salary
using sub queries.

SELECT Emp1.Name

FROM Employee Emp1

WHERE 3 = (SELECT COUNT(DISTINCT(Emp2.Salary))

FROM Employee Emp2

WHERE Emp2.Salary > Emp1.Salary

)
19.Why we can not use WHERE clause with aggregate
functions like HAVING ?

• The difference between the having and where clause in SQL is that
the where clause can NOT be used with aggregates, but the having
clause can.

Please note : It is not a predefined rule but by and large you’ll see that
in a good number of the SQL queries, we use WHERE prior to GROUP
BY and HAVING after GROUP BY.

• The Where clause acts as a pre filter where as Having as a post filter.

• The where clause works on row’s data, not on aggregated data.

Explanation: Let us consider below table ‘Marks’.

Student Course Score

a c1 40
a c2 50
b c3 60
d c1 70
e c2 80

Consider the query

SELECT Student, sum(Score) AS total

FROM Marks

Group by Student;
This would select data row by row basis.
The having clause works on aggregated data.
For example, output of below query
SELECT Student, sum(score) AS total FROM Marks Group
by Student;
Student Total
a 90
b 60
d 70
e 80
When we apply having in given query, we get

SELECT Student, sum(score) AS total

FROM Marks

Group by Student

having total > 70

Student Total
a 90
e 80
20 Difference between primary key and unique key and
why one should use unique key ?

• Primary key:
 Only one in a row(tuple).

 Never allows null value(only key field).

 Unique key identifier and can not be null and must be unique.

• Unique Key:
 Can be more than one unique key in one row.

 Unique key can have null values(only single null is allowed).

 It can be a candidate key.

 Allow for “unknown” values: This is useful in scenarios where data is incomplete or
uncertain, but you still want to maintain uniqueness for known values.
21 What’s the difference between materialized and
dynamic view?

• Materialized views
 Disk based and are updated periodically based upon the
query definition.
 A materialized table is created or updated infrequently and it
must be synchronized with its associated base tables.

• Dynamic views
 Virtual only and run the query definition each time they are
accessed.
 A dynamic view may be created every time that a specific
view is requested by the user.
22. What is embedded and dynamic SQL?
• Static or Embedded SQL
 SQL statements in an application that do not change at runtime
and, therefore, can be hard-coded into the application.

• Dynamic SQL
 SQL statements that are constructed at runtime; for example, the
application may allow users to enter their own queries.
 Dynamic SQL is a programming technique that enables you to
buildSQL statements dynamically at runtime. You can create more
general purpose, flexible applications by using dynamic SQL
because the full text of a SQL statement may be unknown at
compilation.
23. Explain the difference between 2NF and 3NF.
o Answer: 2NF eliminates partial dependencies, whereas 3NF
eliminates transitive dependencies. In 2NF, each non-key
attribute depends on the entire primary key, whereas in 3NF,
a non-key attribute depends on only one key attribute.

24. What is the difference between a 2-tier and 3-tier

architecture in a DBMS?
o Answer: A 2-tier architecture consists of a presentation layer
(client) and a data layer (server), whereas a 3-tier
architecture adds an application logic layer (middleware)
between the presentation and data layers.
25. How do you design a database schema for a given
problem domain?
o Answer: Identify entities, attributes, and relationships; create
entity-relationship diagrams (ERDs); normalize the schema; and
define primary and foreign keys.

26. What are the two integrity rules used in DBMS?

o Answer: Entity integrity (primary key cannot have a NULL value)
and referential integrity (foreign key values must match existing
primary key values).
27. What is the purpose of a checkpoint in a DBMS?
o Answer: Checkpoints are used to ensure database consistency and
recoverability by periodically writing transaction logs to disk, allowing for
recovery in case of a failure.

28. What is the LIKE operator used for in SQL?

o Answer: Pattern matching, allowing for matching of strings with wildcards
(e.g., % for 0 or more characters, _ for a single character).
29. How do you optimize a slow-running SQL query?
o Answer: Use indexing, rewrite queries to reduce joins and
subqueries, and optimize table structures.

30. What are the main differences between RDBMS (Relational

Database Management System) and NoSQL databases?
o Answer: RDBMS uses a fixed schema, supports ACID
transactions, and is optimized for structured data, whereas
NoSQL databases are schema-less, support eventual
consistency, and are optimized for unstructured or semi-
structured data.
31. What are some popular DBMS systems, and what
are their characteristics?
o Answer: Examples include Oracle, MySQL, PostgreSQL
(RDBMS), MongoDB, Cassandra (NoSQL), Redis (in-
memory), and SAP HANA (column-store).

32.How do DBMS systems handle concurrent access

to shared data?
o Answer: Through locking mechanisms (e.g., row-level,
table-level), optimistic concurrency control, or
pessimistic concurrency control, ensuring data
consistency and integrity.
33. What are the three levels of data abstraction in a
DBMS?
o Answer: Physical level (storage), logical level (schema and
relationships), and view level (virtual tables and derived
data).

34 What is Enterprise Resource Planning (ERP), and

what kind of a database is used in an ERP application?

• Enterprise Resource Planning (ERP) is an information system

used in manufacturing companies and includes sales,
inventory, production planning, purchasing and other business
functions. An ERP system typically uses a multiuser database.
35. What are data and information, and how are they
related in a database?

• Data is recorded facts and figures, and information is

knowledge derived from data. A database stores data in
such a way that information can be created.

36. Why is a database considered to be "self-

describing"?

• In addition to the users' data, a database contains a

description of its own structure. This descriptive data is
called "metadata."
37.Write an SQL SELECT statement to display all the
columns of the STUDENT table but only those rows
where the Grade column is greater than or equal to 90.

SELECT * FROM STUDENT WHERE Grade >= 90;.

38. Name and briefly describe the five SQL built-in

functions.

COUNT: computes the number of rows in a table. SUM: total

numeric columns. AVG: computes the average value. MAX:
obtains the maximum value of a column in a table. MIN:
obtains the minimum value of a column in a table.
39 Write an SQL SELECT statement to count the number of rows
in STUDENT table and display the result with the label
NumStudents.

SELECT COUNT(*) AS NumStudents FROM STUDENT;

40. What is an SQL subquery?

An SQL subquery is a means of querying two or more tables at the same

time. The subquery itself is an SQL SELECT statement contained within
the WHERE clause of another SQL SELECT statement and separated by
being enclosed in parenthesis. Some subqueries have equivalent SQL join
structures, but correlated subqueries cannot be duplicated by a join.
41.Why are functional dependencies not equations?

Equations deal with numerical relationships. A functional

dependency deals with the existence of a determinant
relationship between attributes, regardless of whether or not
there is a numerical relationship between them. Thus, if we
know that there is no hot water every Wednesday, No-Hot-
Water is functionally dependent on Wednesday. So, if we know
it is Wednesday, then we know we will have No-Hot-Water. This
is a functional dependency, but not an equation.
42.What does it mean when we say that a relation is in
Boyce-Codd Normal Form (BCNF)?

A relation is in BCNF when every determinant in the relation is

a candidate key. This means that any possible primary key can
determine all other attributes in the relation. Attributes may
not be determined by non-candidate key attributes or part of a
composite candidate key.
43. You have been given a set of tables with data and asked to create a new
database to store them. When you examine the data values in the tables,
what are you looking for?

(1) Multivalued dependencies, (2) Functional dependencies, (3) Candidate keys,

(4) Primary keys and (5) Foreign keys.
How do you create a Foreign Key constraint in SQL?
44. Create a Foreign Key constraint using the FOREIGN KEY clause in a CREATE
TABLE or ALTER TABLE statement, specifying the referencing column(s) and the
referenced Primary Key column(s).

Example:

CREATE TABLE orders (

order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
45. Can a table have multiple Foreign Keys?

Yes, a table can have multiple Foreign Keys, each referencing a

different Primary Key column in another table.

46. How do you update or delete a record in a table with

Foreign Key constraints?

When updating or deleting a record in a table with Foreign Key

constraints, the database will check the referential integrity and
prevent actions that would leave orphaned records or violate the
relationships.
47.What are the steps for transforming an entity
into a table?

• The steps are: (1) specify the primary key, (2) specify
candidate keys, (3) specify column properties including
null status, data type, default value (if any), and data
constraints (if any), and (4) verifying normalization.
• 48. What is a composite key in DBMS, and when is it
used?

A composite key is a combination of two or more columns that

uniquely identify each tuple in a table. It is used when a single
attribute is not sufficient to uniquely identify a record, such as
in cases where multiple records have the same value for a
single attribute.
49. Define and discuss data constraints.

Data constraints on a column are the limits put on the values the
data can have. There are four types of data constraints: (1) domain
constraints, which define a limited set of values for the column, (2)
range constraints, which specify that the values must fall within a
certain range, (3) intrarelation constraints, which define what
values the column can have based on values of other columns in
the same table, and (4) interrelation constraints, which define
values the column can have based on values of columns in other
tables.
50. Explain the "paradigm mismatch" between SQL
and application programming languages.

SQL statements return a set of rows, while an application

program works on one row at a time. To resolve this
mismatch the results of SQL statements are processed as
pseudofiles, using a cursor or pointer to specify which row is
being processed.
51. Name four applications for triggers.

(1) providing default values, (2) enforcing data constraints, (3) updating views
and (4) enforcing referential integrity

52. Why is database redesign necessary?

Database redesign is necessary for two reasons. First, redesign is necessary

both to fix mistakes made during the initial database design. Second, redesign
is necessary to adapt the database to changes in system requirements. Such
changes are common because information systems and organizations do not
just influence each other they create each other. Thus, new information
systems cause changes in systems requirements.
53. What is the difference between a correlated subquery and a regular
subquery?

A correlated subquery appears deceptively similar to a regular subquery. The

difference is that a regular subquery can be processed from the bottom up. In a
regular subquery, results from the lowest query can be determined and used to
evaluate the upper-level query. In contrast, in a correlated subquery, the
processing is nested; that is, a row from an upper query statement is used in
comparison with rows in a lower level query. The key distinction of a correlated
subquery is that the lower-level select statements use columns from upper-level
statements.
Example of Corelated Subquery:
Finding employees who earn more than the average
salary in their department:
SELECT e1.employee_id, e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department = e1.department
)

The subquery is executed once for each row in the outer query,
using the current value of department to calculate the average
salary.
54. How to add a NOT NULL column to a table.

First, add the column as NULL. Then use UPDATE to add data to
every row. Finally use an ALTER TABLE . . . ALTER COLUMN
statement to change the column constraint to NOT NULL.
55.You have two tables, EMPLOYEE and COMPUTER that are
in a one-to-one relationship. The foreign key is EmpNumber
in COMPUTER which references EmpNumber as the primary
key of EMPLOYEE. Explain what must be done to convert
the one-to-one EMPLOYEE-COMPUTER relationship to a one-
to-many relationship where one employee can have more
than one computer.

In the one-to-one relationship, there will be a constraint on

EmpNumber as a foreign key in COMPUTER stating that
EmpNumber must be unique. To convert the relationship to a one-
to-many relationship, just drop this constraint.
56. What are the major functions of the database
administrator?

Managing database structure, controlling concurrent

processing, managing processing rights and
responsibilities, developing database security, providing
for database recovery, managing the DBMS and
maintaining the data repository.
57.What are the ways in which an Oracle database
can be created?

There are three (3) ways to create an Oracle database.

You can create a database using the Database
Configuration Assistant, using the Oracle-supplied
database creation procedures or using the SQL CREATE
DATABASE command.
58. Under what conditions should indexes be used?

Indexes can be created to enforce uniqueness, to facilitate sorting, and to enable

fast retrieval by column values. A good candidate for an index is a column that is
frequently used with equal conditions in WHERE clauses.

59. What are the index creation steps in SQL?

• DDL Statement: Index creation typically involves a Data Definition Language

(DDL) statement, such as CREATE INDEX in SQL.

• Column Specification: Specify the column(s) or expression(s) to be indexed.

• Index Type: Choose the index type (unique, non-unique, clustered, or non-
clustered).

• Index Name: Assign a name to the index.

• Example SQL Statement

• CREATE INDEX idx_student_name ON students (name);

60. What is the coding pattern for using a JDBC driver?

1. Load the driver.

2. Establish a connection to the database.

3. Create a statement.

4. Do something with the statement.

61. Briefly describe the six database activities that occur during the
systems development life cycle.

• The enterprise modeling that analyzes the current data processing.

• Conceptual data modeling that identifies entities, relationships, and attributes.

• The logical database design that identifies data integrity and security
requirements.

• The physical database design and definition that defines the database to a
DBMS.

• The database implementation that installs and converts data from prior
systems.

• Database maintenance that fixes errors in the database and database

applications.
62. Difference between DELETE and TRUNCATE
DELETE is a DML(Data Manipulation Language) command
and is used when we specify the row (tuple) that we want to
remove or delete from the table or relation. The DELETE
command can contain a WHERE clause.
If the WHERE clause is used with the DELETE command,
then it removes or deletes only those rows (tuples) that
satisfy the condition; otherwise, by default, it removes all
the tuples (rows) from the table. Remember that DELETE
logs the row deletions.
Syntax:
DELETE FROM TableName
WHERE condition;
• TRUNCATE is a DDL(Data Definition Language)
command and is used to delete all the rows or tuples
from a table. Unlike the DELETE command, the
TRUNCATE command does not contain a WHERE clause.
• In the TRUNCATE command, the transaction log for each
deleted data page is not recorded. Unlike the DELETE
command, the TRUNCATE command is fast. We cannot
roll back the data after using the TRUNCATE
command. Memory Space is free after executing
Truncate command.
Syntax:
• TRUNCATE TABLE TableName;
63. Explain some of the main goals of normalization.

• Normalization should minimize data redundancy. It should

also simplify referential integrity constraints.
Normalization will also make it easier to insert, update,
and delete data. And finally, it provides better design.

64. List some of the properties of a relation.

• Relations in a database have a unique name and no

multivalued attributes exist. Each row is unique and each
attribute within a relation has a unique name. The
sequence of both columns and rows is irrelevant.
65. Explain what needs to happen to convert a relation to
third normal form

• First you must verify that a relation is in both first normal form and
second normal form. If the relation is not, you must convert into
second normal form. After a relation is in second normal form, you
must remove all transitive dependencies.

66. Describe domain constraints.

• Domain constraints include entity integrity and referential

integrity. The domain is a set of values that may be assigned to an
attribute. The entity integrity rule states that a primary key cannot
be null. Referential integrity states that each foreign key value
must match a primary key value or be null.
67. What is denormalization and why would someone consider
doing so?

• Denormalization is the process of taking normalized relations and

changing them so that they are not longer normalized. This process may
lead to anomalies and create data redundancy as negative consequences.
However, the revised relations should improve database performance.

68. What are the steps to follow when preparing to create a table?

• 1. Identify the data type, length, and precision for each attribute. 2.
Identify the columns that can accept a null value. 3. Identify the columns
that need to be unique. 4. Identify primary and related foreign keys with
the parent table being created before the child. 5. Determine default
values. 6. Determine where the domain values are that need to be
constrained. 7. Create the indexes.
69. Explain a join between tables
• A join allows tables to be linked to other tables when a
relationship between the tables exists. The relationships are
established by using a common column in the tables and
often uses the primary/foreign key relationship.
70. What is Self Join
• A self-join is a type of join operation in a relational database
management system (RDBMS) where a table is joined with
itself, allowing you to combine data from a single table by
creating a virtual copy of the table and establishing
relationships between the original and virtual tables.
72. What are the common use cases for self-join.
• Finding hierarchical relationships (e.g., employee-manager,
category-subcategory)
• Comparing rows within the same table (e.g., finding similar
employees, products with similar characteristics)
• Generating combinations of rows (e.g., all possible pairs of
students, products)
• Aggregating data (e.g., total salary by department, average
rating by category)
• Resolving ambiguities in multiple relationships or foreign keys
• Denormalizing data for specific query requirements
• In summary, self-join is a powerful tool for combining and
analyzing data within a single table, enabling you to
perform complex queries and data transformations that would
be difficult or impossible with traditional joins.
73. Briefly describe an outer join.

• An outer join includes the records that match and those that do not
have a matching value in another table. Outer joins can be a LEFT
outer join (includes all records from the first table listed) or a RIGHT
outer join (includes all records from the second table listed) or FULL
outer join.

74. Describe a subquery.

• A subquery is a query that is composed of two queries. The first

query (inner query) is within the WHERE clause of the other query
(outer query). In some cases the inner query provides results for the
outer query to process. In other cases, the outer query results
provide results for the inner query (correlated subquery).
75. Can a composite key be used as a foreign key in another
table?

Yes, a composite key can be used as a foreign key in another table

within a relational database. In SQL databases, a composite key
comprises multiple columns to uniquely identify rows in a table.

76. Describe and contrast SQL and QBE.

• QBE is a direct-manipulation database language that uses a graphical

approach to query construction. Some database systems translate
QBE queries into SQL. QBE does not adhere to a standard but SQL
does. Both SQL and QBE are relational database languages.
77. What is the difference between horizontal and
vertical partitioning?

• Horizontal partitioning is where some rows of a table are

placed into the base relations at one site and other rows
are placed at another site. Vertical partitioning is where
some columns of a table are placed into the base relations
at one site and other columns are placed at another site
but each all of these relations must share a common
domain.
78. What is the importance of SQL joins in database management?
• SQL joins are important in database management for the following reasons:
 A method of stitching a database back together to make it easier to read
and use.

 Additionally, they maintain a normalized database. Data normalization

helps us keep data redundancy low so that when we delete or update a
record, we will have fewer data anomalies in our application.

 Joins have the advantage of being faster, and as a result, are more efficient.

 It is almost always faster to retrieve the data using a join query rather than
one that uses a subquery.

 By utilizing joins, it is possible to reduce the workload on the database. For

example, instead of multiple queries, you can use one join query. So, you
can better utilize the database's ability to search, filter, sort, etc.
79. What are the different types of Joins in SQL?
• There are various types of join statements you can use depending on the use
case you have. Specifically, there are four types of joins as follows:
 80. State the difference between inner join and left outer join.
 Inner Join: This join generates datasets that contain matching records in both tables
(left and right). By using an inner join, only the rows that match between each of the
tables are returned; all non-matching rows are removed.

• Example: Let's take a look at two tables. Here’s the Tb1_Employee table and the
Tb2_Employment table.
Emp_ID Emp_Name Emp_No Emp_ID Emp_Profile Emp_Country Emp_Join_Date
101 Ashish Kaktan 9450425345 101 Content Writer Germany 2021-04-20
102 Raj Choudhary 8462309621 104 Data Analyst India 2022-12-11
103 Vivek Oberoi 7512309034 Software
105 India 2022-01-03
104 Shantanu Khandelwal 9020330023 Engineer
105 Khanak Desai 8451004522 Development
108 Europe 2023-02-15
Executive
Marketing
109 Mexico 2020-05-23
Manager

Let’s perform INNER JOIN on these two tables using a SELECT statement, as shown below:

SELECT Emp_Name, Emp_No, Emp_Profile, Emp_Country, Emp_Join_Date FROM Tb1_Employee INNER

JOIN Tb2_Employment ON Tb1_Employee.Emp_ID=Tb2_Employment.Emp_ID;
 • Output:

Emp_Name Emp_No Emp_Profile Emp_Country Emp_Join_Date

Ashish Kaktan 9450425345 Content Writer Germany 2021-04-20

Shantanu Khandelwal 9020330023 Data Analyst India 2022-12-11

Khanak Desai 8451004522 Software Engineer India 2022-01-03

Left outer Join: It returns datasets that have matching records in
both tables (left and right) plus non-matching rows from the left
table. By using a left join, all the records in the left table plus the
matching records in the right table are returned.
Example: Let’s now perform LEFT OUTER JOIN on these two tables
using a SELECT statement, as shown below:
SELECT Tb1_Employee.Emp_Name, Tb1_Employee.Emp_No,
Tb2_Employment.Emp_Profile, Tb2_Employment.Emp_Country
FROM Tb1_Employee LEFT OUTER JOIN Tb2_Employment
ON Tb1_Employee.Emp_ID=Tb2_Employment.Emp_ID;
• Output:

Emp_Name Emp_No Emp_Profile Emp_Country

Ashish Kaktan 9450425345 Content Writer Germany

Raj Choudhary 8462309621 Null Null

Vivek Oberoi 7512309034 Null Null

Shantanu Khandelwal 9020330023 Data Analyst India

Khanak Desai 8451004522 Software Engineer India