INTRODUCTION: -

Good morning, sir/mam /Good Afternoon /Good Evening

Thank you for giving me an opportunity to introduce myself.
My Name Is Saurabh Sudhir Gurav I Am from Kolhapur Currently Staying in Pune. I Have
Completed Bachelor of Science 2022 and I have completed SQL, PLSQL course in my tech
focus institute Pune I have good knowledge about that course. I Have Completed HSC In K.H
College Gargoti and I Have Completed SSC In New English School Pal. My Strength Is I'm A
Quick Learner, I am Hard Worker & Punctual in My Work. My Hobbies Are Listen Music
and Traveling That’s All About Me Thank You.
WHY SWITCH BSC TO IT?
During My Graduation, I Realized The Potential Of IT And Programming. There Is So Much
Potential And No Boundaries. And The Best Part About That Anyone With Curiosity Can Join
IT And Learn To Program. That’s Why I Want To Switch From BSC to IT.
WHAT WILL YOU SEE IN POTENTIAL IT?
Smartphones Getting Upgrades New Technologies Are Running
WHY SHOULD BE HIRE YOU?
After failing in every interview, I figured out my mistakes and worked on them. I upskilled
myself and learn from my mistakes.
What is Data?
Data Is Different Types of Information It Is Collection Of Row And Facts.
What is Database?
A Database Is an Organized Collection of Structured Information, Or Data, Typically Stored
Electronically in A Computer System. A Database Is Usually Controlled by A Database
Management System (DBMS)
What is the Full form of SQL?

SQL is Structured Query Language. SQL is a program created and

formulated in the Relational Database Management System to handle
structured data SQL can execute queries against a database so we can
retrieve data from a database we can perform insert update, delete
records in a database we can create new databases We can create
new tables in a database we can create stored procedures, views
function trigger in a database we can set permissions on tables,
procedures, and views

DEFFENCE BETWEEN BETWEEN AND IN OPARATOR: -

Both Of These Operators Are Used To Find Out The Multiple Values From
The Table. The BETWEEN operator is used to fetch rows based on a range
of values. And The IN operator is used to check for values contained in
specific sets. The values can be numbers, text, or dates.
EXCEPT AND MINUS DEFFERNCE: -
EXCEPT and MINUS are synonymous operators which can be used to compare the results of
two queries and return the distinct rows from the first query that are not output by the
second.
When comparing two result sets using EXCEPT or MINUS, the data types must be compatible
and the number and order of the columns should match across queries

What is an extraction table?

Data extraction tables are used to compile the data that matches the intervention
question. Information provided in these data extraction tables allows readers to
assess the applicability of the findings in their area of interest.

Can we create view on subquery?

Subqueries cannot be used in the FROM clause of a view. There is a general
principle that you cannot modify a table and select from the same table in a subquery

What is Alias in Sql?

It is a temporary name assigned to the table or table column for the purpose of a particular
SQL query.
What Is SQL Injection: -

SQL injection is a code injection technique that might destroy your

database.

SQL injection is one of the most common web hacking techniques.

Difference between RDBMS and DBMS

RDBMS DBMS

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

Multiple data elements are accessible together Individual access of data elements

Data in the form of a table are linked together No connection between data
Normalisation is not achievable There is normalisation

Support distributed database No support for distributed database

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

Here, redundancy of data is reduced with the Data redundancy is common

help of key and indexes in RDBMS

RDBMS supports multiple users DBMS supports a single user

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

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

Oracle, SQL Server. XML, Microsoft Access.

WHAT IS ACID PROPERTIES: -

ACID refers to the four key properties of a transaction: atomicity, consistency, isolation, and
durability.
A-Atomicity
The entire transaction takes place at once or doesn't happen at all.
C=Consistency
The database must be consistent before and after the transaction.
I= Isolation
Multiple Transactions occur independently without interference.
D= Durability
The changes of a successful transaction occurs even if the system failure occurs.
SQL OBJECTS: -
SQL objects are schemas, journals, catalogs, tables, aliases, views, indexes, constraints,
triggers, sequences, stored procedures, user-defined functions, user-defined types, global
variables, and SQL packages. SQL creates and maintains these objects as system objects
RELATIONAL EXAMPLE: -
CARDINALITY
1 TO 1
MANY TO MANY
1 TO MANY
MANY TO 1
PLSQL FULL FORM
PL/SQL (procedural language extension to Structured Query Language) In
Oracle database management. The purpose of PL/SQL is to combine database
language and procedural programming language

WHERE CLAUSE HAVING CLAUSE

Filter Table Based Data Catering to Group Base Data Under Set Condition
Specific Condition

Applicable Without Group by Clause Does Not Function Without Group by

Clause

It Is Use to Row Function It Is Used to Column Function

Applied Before Group by Clause Used After Group by Clause

Used With Single Row Operation Such Applicable With Multiple Row Function
as Upper Lower And So On Such as Sum Count And Soon
Like operator :-

When We Have to Pattern Matching Or Use Wild Card Character Then We Use
Operator Like Operator Which Is Comparative Operator.

1) Count (*): -
 --Return The Number of Rows in Table This Include Null Values And Duplication
Example: -

SELECT COUNT (*) FROM EMPLOYEES WHERE DEPARTMENT_ID=50

2) COUNT(EXPRESSION): -

--Return The Number of Row with Non-Null Values for Expression

Example: -

SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES WHERE

DEPARTMENT_ID= 80

3) COUNT (DISINCT(EXPRATION)): -

--Return The Number of Unique Non-Null Values That Are In The Column Identified
By Expression

SELECT COUNT (DISTINCT (DEPARTMENT_ID FROM EMPLOYEES))

**JOINS**

Joins Are Used To retrieve the data Two or More Tables on given condition Is Known
As Joins.

There Are Three Types of Joins

[Link] JOIN: - there are 3 type equi join non equi join natural join

--Inner Join Return Only Common Data from Both the Table.

--Inner Join Is Also Named as Join When We Write in Query.

EQUI JOIN:-

--It Create a Join For Equality Or Matching Column Values Of The Relative Tables
Using Equal sign (=) With On clause.

NON-EQUI JOIN:-

It Is Perform a Join Using Comparison Operator Other Than Equal Sign Like (<, >,
<=, >=) With Conditions.

Natural Join
A NATURAL JOIN is a JOIN operation that creates an implicit join clause for
you based on the common columns in the two tables being joined. Common
columns are columns that have the same name in both tables

[Link] JOINS :-

--Returning Records with Match And Unmatched Data.

Outer Joins There Are Three Types

[Link] OUTER JOIN:-

--It Return All the Records from Left Side Table And Matched Records From The
Right Side Table

[Link] OUTER JOIN:-

-- It Return All the Records from Right Side Table And Matched Records From The
Left Side Table

[Link] OUTER JOIN:-

--Full Outer Join Is the Type of Outer Join Which Will Returns All the Data from Both
the Tables.

--It Gives All Matching and Non-Matching Data from Both the Table.

SELF JOIN:-

When In the Table the Self-Referential integrity Is Present When We Use For Self-Join This
Table

USING CLAUSE :-

--Using Clause Is Used to Match Only One Column When More Than One Column
Match.

--Natural Join and Using Clause Are Mutually Exclusive.

CROSS JOIN:-

A cross join is a type of join that returns the Cartesian product of rows from the
tables in the join. In other words, it combines each row from the first table with each
row from the second table

JOIN SET OPERATOR

In Inner Join the Common Data from Both In Intersect Set Operator It Return
Table or Multiple Table Is Return in Common Data from Both Table in
Output but I In Comparison It Does. Bi Output in Set Operator a During
Not Consider Null Values from Both Table Comparison Or It Consider Null Value ·
From Both Table.

In Join the Common Data Duplication Is In Set Operator the Common Data
Return in Output Duplication Is Not Return in Output
In Join Find Out the Common Data &
In Set Operator Find Out the Common &
Non-Common Data from Both Tables in
Non-Common Data in Multiple Query
Single Query

A Join Means for Combining Fields or A Set Operator Common Must Same
Data from Two Tables by Using Datatype & The Number of Column
Same for Each Select Statement.

Common Value Each Other

**SET OPERATOR**

Set Operator Combine the Results of Two Component Query Into Single Result
That's Is Known As Set Operator.

SET OPERATOR RULES :

1. We Can Use the Order by Clause in Last Query

2. Number of Column Should Be Same Both the Query

3. Data Type Should Be Consisting Format

SET OPERATOR THERE ARE FOUR TYPES,

1. INTERSECT:-

Intersect Is Operator to Use Combine Two Query If Return Common Data

in Both the Query

2. UNION:

Union Is Operator It Use to Return All the Records From Multiple Table If
Eliminate The Duplicate Value

3. UNION ALL:

Union All Is Operator It Use to Return All the Record From Multiple Table If
Included The Duplicate Value

4. Minus:

Minus is a Operator It Use To Combine The Two Query If Return

Noncommon Data
 UNION UNION ALL

In Union Set Operator In Union All Set Operator' It Return All

Data From Both Table Including
It Return All Data From Duplication.
Both Table Excluding Duplication

In Union Operator The Common Data Is In Union All Operator It Return The
Show One Time In Output Duplication. As It Is Present In Both Table

In Union Operator It Return In Union All Operator It Return Number Of

Noncommon Data As It Is & Neglect Row's Present In Both Table In Output
Duplication.

INTERSECT MINUS

In Intersect It Return The In Minus It Return Non Common Data From Left
Common Data From Both Tables Side Table
In One On Time

In Intersect The Position Of Both In Minus We Change The Position Of Both Table
Table Are Change Then Not Then It Change! In Output Because It Return
Change In Output. Non Common Data From Left Side Table

The Output Is Depend On The The Output Is Depend On Non Common Data Of
Common Data Of Both Tables. Left Side Table
**SUBQUERY**

Query Within Query Is Called Subquery.

The Output Of Inner Query When Pass Of A Input For Outer Query Is Known As
Subquery.

THERE ARE TWO TYPES OF SUBQUERY

 [Link] SUBQUERY:

When Inner Query Gives Single Row Output Is Known As Single Row Subquery.

Single Row Subquery In Use To Comparison Operators.(=,<,>,>=,<=)

Comparison Operators(=,<,>,>=,<=)

[Link] SUBQUERY :-

When Inner Query Gives Multirow Output Is Known As Multirow Subquery.

Multirow Subquery Is Use To ( Any All In )Operators.

ANY AND ALL DEFFENCE:

ANY ALL IN

Any Operator Use To Where All Operator Use To Where In Operator Use To Where Clause
Clause And Having Clause. Clause And Having Clause. And Having Clause

returns TRUE if ANY of the returns TRUE if ALL of the

subquery values meet the subquery values meet the The In Operator Is A Shorthand
condition condition For Multiple Or Conditions.

The In Operator Allows You

In Any Operator It Return The In All Operator The Value Is To Specify Multiple Values In
Value Which Is Compare To Compare With Minimum &
A Where Clause.
Minimum & Maximum Value Of Maximum Value Of The Set &
The Set & According To Return The Value According
Condition. To Condition.

DROP DELETE TRUNCATE

Drop command used Delete command used to Truncate command used to

to Delete Structure Delete Only Data Delete Only Data but
And Data structure is remaining.

Drop is DDL Delete is A DML

Command Command Truncate is a DDL command
 Can Be Roll Back Can Not Be Roll Back
Can Not Be Roll Back

Where Clause Can Where Clause Used Where Clause Can Not Be
Not Be Use Use

Free up the space Slower Than Truncate Faster Than Delete

from memory.

In the drop command action performed by Does not free up space from
view of table does not delete can be rolled back memory.
exist. as it uses buffer.

------------ -------------- While in this command view

of table exist.

Difference between DDL and DML:

DDL DML

It stands for Data Manipulation

Language.
It stands for Data Definition Language.

It is used to create database schema and can be It is used to add, retrieve or update the
used to define some constraints as well. data.

It basically defines the column (Attributes) of the It add or update the row of the table.
table. These rows are called as tuple.

It is further classified into Procedural and

Non-Procedural DML.
It doesn’t have any further classification.

Basic command present in DDL are CREATE, BASIC command present in DML are
DROP, RENAME, ALTER etc. UPDATE, INSERT, MERGE etc.

DDL does not use WHERE clause in its statement. While DML uses WHERE clause in its
statement.
 **COMMANDS IN SQL**

DDL : DATA DEFINATION LANGUAGE

It Is Define as Data Structure DDL Commands Are Automatically Committed

DDL Statements there Are 4 Types

[Link]

a) Add b) Rename c) Modify d) Drop

[Link]

[Link] [Link]

1] ALTER :

This Command Used To Alter The Structure Of Database. In Alter We

Add Column, Remove Column From Table & Modify The Table

SYNTAX :-

Alter Table (Table Name) Add Column (Column Name(Data Type));

[Link] :

The Create Command Is Used To Create The Table In Database .(Table,

View, Sequence, Index)

--The Create Statement Is One Of The DDL Statement

SYNTAX :-

Create Table (Table Name) (Column (Datatype)…..);

[Link] :-

This Command Is Used To Delete Table From The Database.

SYNTAX:-

Drop Table (Table Name);

[Link]:-

Truncate Is Used To Delete All Row From Table & Free Space
Containing Table

SYNTAX:-

Truncate Table (Table Name)

 DML : DATA MANIPULATION LANGUAGE

It Is Define As Manipulate The Data DML Commands Are Not-Automatically Committed

DML Statement Are 4 Types:

[Link]

[Link]

[Link]

[Link]

1. INSERT:-

In Insert Statement We Add New Row To A Table Which Is Already Present

In Database.

-- In insert clause not required column name for add new row

value in the column

-- if we do not use column list the values must be listed according

to the default order of column in the table & the value insert into each

column .

-- if a data must be entered in a format other than the default

format date we must use to date function.

Syntax :-

Insert Into Table Name(Column ,Column,…..)

Values(Value , Value , ……..);

[Link] : -

In Update We Change The Data From Table According To Our

Requirement. In Update We Change The Data By Using Primary Key

Column In Particular Table Because The Primary Key Have Unique Value. -- In

Update We Update More Than One Row At A Time When Required

In Update The Specific Row Update If Where Clause Specified

--If We Neglect Where Clause All The Row Modified In The Table
 SYNTAX:-

Update Table Name Set Column =Value (For Change)

Where Condition; (For Which Row)

[Link]:-

In Delete Statement We Remove The Row From The Table

--In Where Clause For Delete A Specific Record From The Table

--If We Not Use Where Clause Then Delete All Record From The Table
.

[Link] :-

It Provide The Ability To Conditionally Update Or Insert Data Into Database Table. Perform
An Update If Row Is Already Exist Otherwise Insert Is Perform

Syntax: -

MERGE INTO target table alias---- TARGET USING source table-----SOURCE

ON(S.S_ID = T.S_ID) WHEN MATCHED THEN UPDATE

SET source. source column name = target. column name,

WHEN NOT MATCHED THEN

INSERT VALUES (source table alias. target table column name);

TCL : TRANSACTION CONTROL LANGUAGE

TCL is a transaction control language it is used to maintaining consistency of data base

object TCL made by DML command.

TCL COMMAND THERE ARE 3 TYPES :

1. SAVEPOINT:-

A SAVEPOINT Is A Point In A Transaction In Which You Can Roll The Transaction

Back

SYNTAX FOR SAVEPOINT COMMAND:

SAVEPOINT SAVEPOINT_NAME

2. ROLLBACK:-

Is The SQL Command That Is Used For Reverting Changes Performed By A

Transaction.
 SYNTAX ROLLBACK COMMAND:-

Rollback Save point Name;

3. COMMIT:-

COMMIT Is The SQL Command That Is Used For Storing Changes Performed By A
Transaction. When A COMMIT Command Is Issued It Saves All The Changes Since
Last COMMIT Or ROLLBACK

SYNTAX:-

Commit

DQL COMMANDS:-

DQL Is a Data Query Language It Is Used To Perform The Queries In Database Object

Syntax: -

Select

DCL COMMAND:-

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

GRANT: -

GRANT command is used to give access privileges to the users or other rights or
opportunities for the database. This command also allows users to grant permissions to
other users too.
REVOKE: -
The REVOKE command does just opposite to the GRANT command. It withdraws user
privileges on database objects.

CASE & DECODE

CASE :- CASE EXPRESSION. ALLOWS TO ADD IF ELSE LOGIC TO SQL

STATEMENT USING WHEN AND THEN.

-We Can Any Comparison In Case Expression. -Their

Must Be Consistency In The Data Type In Case

-Case Handle Null Value With Is Null Operator.

SYNTAX FOR CASE:

Select expression Case When column name = 'expression' Then 'expression'

 Else Column name

End As New Column name

From Table Name

Using Between And Case-When Attribute

Between-AND- Over

- We Can Avoid ELSE From CASE, If The Condition Didn't , Satisfy It Will Show
ELSE Value And If Avoid ELSE Then It Will Show "NULL" Value.

-End Command Is Compulsory Used In Case To End The Statement

DECODE :-

(Switch Case) Function. Decode Compare Expression To Each Search Values

One By One. If Expression Is Equal To Search Then Return The Corresponding
Result.

-Decode Has By Default Equal To Operator We Can Not Use Of Operator In Decode

-Decode Does Not Expect Consistency.

-Decode Treats Null' As Null

For Decode

SYNTAX FOR DECODE :-

Select Column-Name

Decode (Column-Name Exp.1, Exp2……) From Table Name

- We Don't Use Between And Operator In Decode Function.

**SEQUENCE**

A sequence is a data base object in oracle it is use to generate unique numbers of

sequence. A sequence can be ascending and descending order.

SYNTAX:-

Create Sequence (Sequence Name)

Increment By 1

Start With

Minvalue0

Maxvalue 5
 Nocache

Nocycle;

*INCREMENT BY:-*

Specify The Interval Between Sequence Number This Integer Value Can
Be Any Positive Or Negative But It Cannot Be 0.

*START WITH:-*

Specify The First Sequence Number To Be Generated

*MAXVALUE:-*

Specify The Maximum Value The Sequence Can Generate

Must Be Equal To Or Greater Than Start With And Must Be Greater Than Min Value

* MINVALUE:-*

Specify The Minimum Value Of Sequence Minvalue Must Be Less Than Or Is Equal
To Start With And Must Be Less Than Max. Value\

*CYCLE :-

Indicates That Sequence Continues To Generate Values Cyter Reaching Eithers Its
Max Or Min Value

*CACHE:-

The Sequence Cache Size Determines How Many Values Oracle Preallocates
In Memory, In The Shared Pool.

NEXTVAL:-

To Get The Next Value Of The Sequence You Use The Nextval Pseudo Column

Example :-

Select Sequence [Link]

From Dual;

CURRVAL:-

To Get The Current Value Of Sequence You Use The Currval.

Example :-

Select Sequence [Link]

From Dual;
- NOTE: When You Can Alter The Sequence Do Not Change The Start With
Value
s
Alter sequence (sequence name)

Increment by (value)

Maxvalue (value)

**NORMALIZATION**

WE CAN DIVIDE DATA INTO SMALL SMALL CHUNK TO REMOVE DATA

REDUNDANCY OR DUPLICATION.

*NORMAL FORMS IN SQL:-

1 NF (First Normal Form) :-

--Remove All The Duplicate Columns From The Table

---Creation Of Tables For The Related Data And Identification Of Unique Column

2 NF (Second Normal Form) :-

--Metting All Requirement Of The First Normal Form

--Placing The Subset Of Data In Separate Tables And Creation Of Relation Between
The Tables Using Primary Keys

3 NF (Third Normed Form):-

--Metting All Requirement Of 2 NF

--Removing The Columns Which Are Not Dependent On Primary Keys

**CONSTRAINTS**

Constraints Means Restrictions And Some Rules For Columns And Tables

• Column level – Limits only column data

• Table level – Limits whole table data

•
• NOT NULL
• UNIQUE
• CHECK

• DEFAULT
• PRIMARY KEY
• FOREIGN KEY
 1) NOT NULL-

This Constraint Ensure That The Column Which Mentioned That Don't Have Null
Value If There Are Any Error It Will Give Error.

SYNTAX:-

Create Table (Table Name)

(Column-Name ,Datatype Constraint (Constraint Name) Not Null)

2)UNIQUE:-

This Constraint make sure that value will not be Repeat in that Particular column.

SYNTAX:-

Create table (table name)

(Column-Name ,Datatype Constraint (Constraint Name) Unique);

3)CHECK:-

This Constraint Is Used For To Check Condition Before Inserting Data Into Table

SYNTAX:-

(Column-Name, Datatype constraint (Constraint Name) check (condition) );

4)PRIMARY KEY:-

This Is The Most Important Constraint It Make Sure That Each Record Is Unique And
Can Be Uniquely Identified We Put Values Like Emp Id, Stud Id Such That They Are
Numbers And The Are Unique

(Column-Name, Datatype constraint (Constraint Name) check (condition) );

5)FOREIGN KEY:-

When we write a column of one table as reference in another table then it is foreign
key generally primary key of one table is foreign key of another table

SYNTAX:-

Column Name Datatype

CONSTRAINT (Constraint Name) FOREIGN KEY (Column Name)
REFERENCES Table Name (Column Name)

DEFAULT constraint is used to fill a column with default and fixed values.
The value will be added to all new records when no other value is provided.
For more details
Let’s see the difference between Primary Key and Foreign Key:
[Link]. PRIMARY KEY FOREIGN KEY

A primary key is used to ensure A foreign key is a column or group of columns in

data in the specific column is a relational database table that provides a link
unique. between data in two tables.
1

It uniquely identifies a record in It refers to the field in a table which is the

the relational database table. primary key of another table.
2

Only one primary key is Whereas more than one foreign key are allowed
allowed in a table. in a table.
3

It is a combination of UNIQUE It can contain duplicate values and a table in a

and Not Null constraints. relational database.
4

5 It does not allow NULL values. It can also contain NULL values.

Its value cannot be deleted from

the parent table.
6 Its value can be deleted from the child table.

It constraint can be implicitly

defined on the temporary
It constraint cannot be defined on the local or
tables.
7 global temporary tables.

**INDEX**

INDEX:-

--Whenever We Have To Improve The Performance Of Query Then Index Can Be

Very Helpful To Fetch Data Very Quickly.

--A SQL Index Used To Retrieve Data From A Database Very Fast.

--It Is One Of The Best Way To Improve The Performance Of Queries And
Application.

--Once Index Is Apply Or Create On Column.

**THERE ARE Three TYPES OF INDEX:-**

Unique Index – It does not allow a field to have duplicate values if the column is unique
indexed.
Clustered Index – This index defines the order in which data is physically stored in a table.
It reorders the physical order of the table and searches based on key values. There can be
only one clustered index per table.

Non-Clustered Index – It does not sort the physical order of the table and maintains a
logical order of the data. Each table can have more than one non-clustered index.

SYNTAX:-

Create Index (Index Name)

On Table Name (Column Name……)

**ANALYTICAL FUNCTION**

**(WINDOW FUNCTION)**

ANALYTICAL FUNCTIONS ARE CALLED WINDOW FUNCTIONS.

--WINDOW FUNCTIONS APPLIES AGGREGATES AND RANKING FUNCTION

OVER A PARTICULAR WINDOW SET OF ROWS

ANALYTICAL FUNCTIONS THERE ARE THREE TYPES

1.ROW_NUMBER:-

--Row Number It Assigns Consistive Integers To All The Rows Within Partitions .
Within A Partition Number Two Rows Can Have Same Row Numbers .

[Link]:-

Rank To All The Rows Rank Is Given Such That Rank 1 St Given To Next Row
Having Same Value It Gives Same Rank For The Next Rank After Two Same Rank
Value Will Be Skipped.

3.DENSE_RANK:-

Rank Each Within Particular Just Like Rank 1 St Row Is Given Rank 1 And Row
Having Same Value Have Same Rank.

ROW_NUMBER RANK DENSE_RANK

 Row Number Function Rank Will Skip It Generates A
Always Generate A The Next Unbroken
Unique Ranking Even Number Rank If Sequence .
With Duplicates Records. They Got
Duplicates.

**VIEW**

VIEW:-

-- A View Is Something of Virtual Table It Is Logically Represents Subset of Data

from One of More Table. A View Derives its data From The Table On Which It Is
Based These Tables Are Called Base Tables

There are two types of view.

SIMPLE VIEW :-

--Derives Data from Only One Table

--Contains No Function or Group Of Data

--Can Perform DML Operations Through THE View

COMPLEX VIEW:-

--Derives Data from Many Tables

--Contains Functions or Groups of Data

--Does Not Always Allow DML Operations Through the View

• Inline View: A view based on a subquery in FROM Clause, that subquery

creates a temporary table and simplifies the complex query.

• Materialized View: A view that stores the definition as well as data. It creates
replicas of data by storing it physically.

VIEW MATERALISED VIEW

Not stored physically data stored only logically View stored data physically
data
Formed from one or more base table or view Physical copy of base table

View is updated each time the virtual table Updated manually or using triggers
(view)is used

Processing speed Is slow Processing speed are fast

Do not require memory space Utilizes memory space

WITH CLAUSE :-

- Using the with clause, you can use the same query block in select statement when it
occurs more than once within a complex query

- The With clause retrives the result of a query block and stores in the users temporary
tablespace

**CURSOR: -**

24. What is a Cursor?

The cursor is a Temporary Memory or Temporary Work Station. It is Allocated
by Database Server at the Time of Performing DML operations on Table by
User. Cursors are used to store Database Tables.
There Are Two Types of Cursors –
1 IMPLICITE
2 EXPLICITE
A IMPLICITE CURSOR:
- Created And Managed Internally by The Oracle Server to Process SQL Statement.
SOL Implicit CURSOR attributes:
SQL % Found: - It Returns True If Rows Found in Cursor
SQL % Not found: It return True if rows not found in cursor
SQL% Row Count: - It Return the Count of Rows Present Cursor
EXPLICITE CURSOR: -
Explicate Cursor Is Define & Manage by User
It Should Be Declare in The Declaration Section of The PLSQL Block
It Is Created on Select Statement Which Returns. More Than One Row.
Syntax
Cursor & Cursor-Name Is Select Statement,
THERE ARE FOUR STEPS IN WORKING WITH EXPLICITE CURSOR
1 Declare the Cursor for Initialization in The Memory
2 Open Cursor for Allocating Memory
3 Fetching the Cursor for Retrieving Data
4 Closing the Cursor to Release. Allocated Memory.
**RECORDS**
*Composite Data Types
--It can hold the multiple value.
--Variable of composite data type can hold multiple values of scalar data type
RECORDS: -
--It is collection of different data type
--Record is like structure in c
SYNTAX: -
Type record name is record
(Last name varchar2(20), salary number, job id varchar2(20)
)

**PROCEDURE**

A procedure is used to perform certain task in order.

**FUNCTION**

A function is used to calculate result using given inputs.

**TRIGGER**

TRIGGER:-
A Trigger Fires Automatically When a Specified Event Occurs.
TRIGGER THERE ARE TWO TYPES: -
1] APPLICATION TRIGGER: -
Only Used With Particular Application On Which Trigger Is Defined
2] DATABASE TRIGGER: -
Database Trigger Is Stored On Database & Used By All User. N number of Trigger Can Be
Apply To A Single Table
TRIGGER TIMING: -
Before & After
TRIGGER EVENTS: -
Insert, Update, Delete

FUNCTION PROCEDURE
Function Is Mainly Used In The Case T May Procedure May Or May Not Return A Where
Not Return A Where It Return Must Value It Return Value.

Function Can Be Called Using SQL Statement Procedure Can Not Called Using SQL
Statement
Function Is Mainly Use For Calculation Procedure Used For Executing The Business
Purpose Or Compilation Logic
Function Return One Value Procedure Can Return Multiple Value
Function Return Type Could Be Scaler Or Stored Procedure Return Integer Value By
Table Or Table Values Default Zero
Function Is Not Pre-Compilation Execution Procedure Pre-Compilation Execution
Function Want Support Differed Name Procedure Allowed Differed Name
Resolution Resolution
Accept Only IN Parameter Accept In Out In-Out Parameter
DML statements cannot be executed within DML statements can be executed within a
a function. procedure.
A function can be called by a procedure. A procedure cannot be called by a function.

IN OUT INOUT
Default mode Must be specified Must specified
Value passed into sub Value return to calling Act as both in & out
program environment
Formal parameter act as Uninitialized variable Initialized variable
constant
Can be assigned default Can’t assigned default value Can’t assigned default value
value

TRIGGER PROCEDURE
It is special kind of procedure that initiate It is group of transaul SQL statement
certain action on table like insertion, compiled into single execution plan
deletion or updating of data.

Trigger cannot accept parameter procedure can bet accept parameter

Trigger can't return any value. procedure can return 0 or n value.

A trigger is executed implicitly A procedure is executed implicitly

Trigger are used for auditing work. Procedure used for executing the business
logic
commit, save point & Rollback are not commit, save point & Rollback are allowed.
allowed.

**EXCEPTION HANDLING**
When Error That Occur at Run Time when we use for Exception
When An Exception Occurs the PLSQL Block Is Terminated
Exception There Are 3 Types
1] Predefine
----Implicitly raise
2] non-Predefine

3] User Define ----- explicitly raised

**PACKAGES**
A PL/SQL package is a named collection of functions, procedures, variables,
cursors, user-defined types, and records that are referenced using a common
qualifier, the package name
**BLOCK**
Block is a basic unit of plsql
Block there are 2 types
1 anonymous
2 non-anonymous
Anonymous :- unknown
Block without name is called anonymous
It is not saved in oracle
It is just one time use
Non-Anonymous:-
Block with name is called non-anonymous
Ex trigger procedure function
It is stored in oracle database and can be receive later

BLOCK STRUCTURE

DECLARE
BEGIN
EXCEPTION
END

DECLARE AND EXCEPTION ARE OPTIONAL

BEGIN AND END ARE MANDOTORY

Q14. What is the difference between:

SELECT * FROM MyTable WHERE MyColumn <> NULL

SELECT * FROM MyTable WHERE MyColumn IS NULL

Ans. The first syntax will not work because NULL means ‘no value’, and you cannot use
scalar value operators. This is why there is a separate IS – a NULL predicate in SQL.

Q28. What is Denormalization?

Ans. Denormalization is a database optimization technique for increasing a database
infrastructure performance by adding redundant data to one or more tables

Q33. What is Auto Increment in SQL?

Ans. Auto Increment allows a unique number to be generated whenever a new record is
created in a table. Generally, it is the PRIMARY KEY field that we want to be created
automatically every time a new record is inserted
Q34. SQL Server runs in which TCP/IP port? Can it be
changed?
Ans. SQL Server runs on port 1433, and it can be changed from the Network Utility TCP/IP
properties.

Q36. What is Database Relationship?

Ans. A Database Relationship is defined as the connection between two relational database
tables. The primary table has a foreign key that references the primary key of another table.
There are three types of Database Relationship –

• One-to-one
• One-to-many
• Many-to-many

Q37. What is faster between a table variable and a temporary

table?
Ans. Between these, a table variable is faster mostly as it is stored in memory, whereas a
temporary table is stored on disk. In case the size of the table variable exceeds memory size,
then both the tables perform similarly.

Q38. Mention the command used to get back the privileges

offered by the GRANT command?
Ans. REVOKE command is used to get back the privileges offered by the GRANT command.

Q39. What is a Clause in SQL?

Ans. A clause in SQL is a part of a query that allows users to filter or customize how they
want their data to be queried to them. It lets users limit the result set by providing a
condition to the query. When there is a large amount of data stored in the database, Clause
can be used to query and get data required by the user. The clause function helps filter and
analyze data quickly.

For Example – WHERE clause, HAVING clause.

Q40. Explain the ‘WHERE’ Clause and the ‘HAVING’ Clause.
Ans. It is one of the most important SQL interview questions.

The WHERE clause is used to filter the records from the table or used while joining more
than one table. It returns the particular value from the table if the specified condition in the
WHERE clause is satisfied. It is used with SELECT, INSERT, UPDATE, and DELETE queries to
filter data from the table or relation.

For Example: SELECT * FROM employees

WHERE working_hour > 9;

The HAVING clause is used to filter the records from the groups based on the given
condition in the HAVING Clause. It can only be used with the SELECT statement. It returns
only those values from the groups in the final result that fulfills certain conditions.

For Example:

SELECT name, SUM(working_hour) AS “Total working hours”

FROM employees GROUP BY name

HAVING SUM(working_hour) > 6;

Q41. Explain the SELECT statement?

Ans. The SQL SELECT statement helps select data from a database. It returns a result set of
records, from one or more tables.

Q42. What are the differences between the ‘WHERE’ Clause

and the ‘HAVING’ Clause?
Ans. Below are the major differences between the ‘WHERE’ Clause and the ‘HAVING’ Clause:

WHERE Clause HAVING Clause

It performs filtration on individual rows based on HAVING clause performs filtration on

the specified condition. groups based on the specified condition.
 It is always used with the GROUP BY
It can be used without GROUP BY Clause.
Clause.

WHERE Clause is applied in row operations. HAVING is applied in column operations.

We cannot use the WHERE clause with

This clause works with aggregate functions.
aggregate functions.

WHERE comes before GROUP BY HAVING comes after GROUP BY.

This clause acts as a pre-filter. HAVING clause acts as a post-filter.

WHERE Clause can be used with SELECT, This Clause can only be used with the
INSERT, UPDATE, and DELETE statements. SELECT statement.

Q43. How to find:

duplicate records with one field? duplicate

records with more than one field?

Ans. Finding duplicate records with one field:

SELECT COUNT(field)

FROM table_name

GROUP BY field

HAVING COUNT(field) > 1

Finding duplicate records with more than one field:

SELECT field1,field2,field3, COUNT(*)

FROM table_name

GROUP BY field1,field2,field3

HAVING COUNT(*) > 1

Q49. What is Data Integrity?
Ans. Data integrity attributes to the accuracy, completeness, and consistency of the data in a
database. It also refers to the safety and security of data and is maintained by a collection of
processes, rules, and standards that were implemented during the design phase. Three
types of data integrity are:

• Column Integrity
• Entity Integrity
• Referential Integrity

Q50. What is the difference between Rename and Alias?

Ans. Rename is actually changing the name of an object and Alias is a temporary name given
to a database object

Q52. What is Referential Integrity?

Referential integrity is a relational database concept that suggests that the accuracy and
consistency of data should be maintained between primary and foreign keys.

Q53. What is Business Intelligence?

Ans. Business intelligence (BI) includes technologies and practices for collecting,
integrating, analyzing, and presenting business information. It combines business analytics,
data mining, data visualization, data tools and infrastructure, and best practices.

Q56. What is ERD?

Ans. ERD or Entity Relationship Diagram is a visual representation of the database
structures and shows a relationship between the tables. The ER Diagrams have three basic
elements:

• Entities – An entity is a person, place, thing, or event for which data is collected.
• Attributes – It refers to the data we want to collect for an entity. It is a property,
trait, or characteristic of an entity, relationship, or another attribute.
• Relationships – It describes how entities interact
Q61. Explain string functions in SQL?
Ans. SQL string functions are used for string manipulation.

Following are the extensively used SQL string functions:

• UPPER(): Converts character data to upper case

• LOWER(): Converts character data to lower case
• SUBSTRING() : Extracts characters from a text field
• RTRIM(): Removes all whitespace at the end of the string
• LEN(): Returns the length of the value in a text field
• REPLACE(): Updates the content of a string.
• LTRIM(): Removes all whitespace from the beginning of the string
• CONCAT(): Concatenates function combines multiple character strings

• Q62. What are the differences between the Primary

key and the Unique key?
• Ans. Differences between the Primary key and the Unique key are:

Primary Key Unique Key

Enforces column uniqueness in a table Determines a row that isn’t a primary key

Does not allow NULL values Accepts one NULL value

Has only one primary key Has more than one unique key

Creates clustered index Creates non-clustered index

Primary Key on CREATE TABLE Unique Key on CREATE TABLE

Syntax: CREATE TABLE Students ( ID int Syntax: CREATE TABLE Students ( ID int
NOT NULL PRIMARY NOT NULL UNIQUE, LastName
KEY, LastName varchar(255) NOT NULL, varchar(255) NOT NULL, FirstName
FirstName varchar(255), Age int); varchar(255),

Q64. What is the procedure to hide a specific table name of

the schema?
Ans. By using SYNONYMS, we can hide a specific table name of the schema.
 Syntax:

CREATE SYNONYM STU for STUDENTS;

After creating the above synonym, we can access the data of the STUDENTS table using STU
as the table name below

SELECT * from STU;

Q97. What is COALESCE?

Ans. COALESCE returns the first non-NULL expression within its arguments from more than
one column in the arguments.

The syntax for COALESCE is –

COALESCE (expression 1, expression 2, … expression n)

Q100. Explain the use of the NVL() function.

Ans. The NVL()function converts the Null value to the ACTUAL value

Q110. What is the difference between SQL and PL/SQL?

Ans. This is an important question that you must prepare for your SQL interview.

Below are some of the major differences between SQL and PL/SQL:

SQL PL/SQL

PL/SQL or Procedural Language/Structured Query Language is a

SQL is a database Structured
database programming language using SQL. It is a dialect of SQL
Query Language.
to enhance SQL capabilities.

It was developed by IBM

Corporation and first appeared in It was developed by Oracle Corporation in the early 90s.
1974.

Data variables are not available. Data variables are available.

SQL is a declarative language. PL/SQL is a procedural language.

It is data-oriented. PL/SQL is application-oriented.

It can execute only a single

It can execute a whole block of code at a time.
query at a time.

SQL can directly interact with

PL/SQL cannot directly interact with the database server.
the database server.

It can be embedded in PL/SQL. It cannot be embedded in SQL.

SQL is used to write queries, It is used to write program blocks, functions, procedures triggers,
DDL, and DML statements. and packages

SQL acts as the source of PL/SQL acts as a platform where SQL data will be displayed.

data that is to be displayed.

16. What is table and Field?

Table: A table has a combination of rows and columns. Rows are called
records and columns are called fields. In MS SQL Server, the tables are being
designated within the database and schema names.
Field: In DBMS, a database field can be defined as – a single piece of
information from a record.

19. What is On Delete cascade constraint?

ON DELETE CASCADE’ constraint is used in MySQL to delete the rows from
the child table automatically when the rows from the parent table are deleted.
For more details
28. What are the different operators available in SQL? There
are three operators available in SQL namely:
1. Arithmetic Operators
2. Logical Operators
3. Comparison Operators
 54. What is the need for group functions in SQL?
In database management, group functions, also known as an aggregate
function, is a function where the values of multiple rows are grouped together
as input on certain criteria to form a single value of more significant meaning.
59. What is ETL in SQL?
ETL is a process in Data Warehousing and it stands for Extract, Transform
and Load. It is a process in which an ETL tool extracts the data from various
data source systems, transforms it in the staging area, and then finally, loads
it into the Data Warehouse system. These are three database functions that
are incorporated into one tool to pull data out from one database and to put
data into another database

61. How to find the available constraint information in the table? In SQL
Server the data dictionary is a set of database tables used to store
information about a database’s definition

69. What is the difference between COALESCE() & ISNULL()?

COALESCE(): COALESCE function in SQL returns the first non-NULL
expression among its arguments. If all the expressions evaluate to null, then
the COALESCE function will return null.
NOT NULL Ensures that a column cannot have a NULL value.

DEFAULT Provides a default value for a column when none is specified.

UNIQUE Ensures that all the values in a column are different

PRIMARY Uniquely identifies each row/record in a database table

FOREIGN Uniquely identifies a row/record in any another database table

CHECK The CHECK constraint ensures that all values in a column satisfy certain conditio

INDEX Used to create and retrieve data from the database very quickly.