Hi guys, this is Sahiti
on behalf of Edureka.
And I welcome you
to this session
on SQL interview questions.
So in this session
guys will mainly focus
on the top 65 interview
questions generally asked
about SQL. Now in the era
of 2.5 quintillion bytes
of data being generated
every day data plays a crucial
role in decision-making
for business operation.
This quite essentially
makes us handle
data and databases
and gives us the need to use
the database management system
with various kinds
of database Management Systems
present in the market today,
the relational database
management system is one
of the most popular
systems available.
Now this type
of database management system
uses a structure
that allows the users
to identify an access data
in relation to another piece
of data in the database
and SQL is the core
of relational database,
which is used for accessing
and managing the database.
So definitely knowing
sequel will open the doors
for you to become
a database administrator.
So guys if you're preparing
for interviews to become
a database administrator
than SQL is one
of the important skills
that you need to master in.
So with the note of this
let's just get started
with the SQL interview questions. So the first question
that we have is what is
the difference between delete
and truncate statement.
So I'll start by explaining
you what is the delete command?
Well, the delete command
is basically used to delete
a row in the table.
So you can also roll back data
after using the delete statement
and it is a data
manipulation command. Coming
to the truncate statement,
the truncate statement
is basically used to delete
all the rows from a table
and you cannot roll
back the data.
This is a data
definition language command
and it is faster
than the delete command.
So if anyone asks you the
difference between the delete
and the truncate statements
don't forget to mention
that you know,
the truncate statement
is definitely faster
than the delete statement
and the truncate statement is
from the data definition family
and the delete statement
is The data manipulation
from the right now moving on
to our next question
that is what are
the different subsets of SQL.
Well, the different subsets
of SQL are mainly
the data definition language
the data manipulation language
the data control language
and the transaction
control language now coming
to the data definition language
this set of language consists
of the commands
that can be used to define
the database schema.
So whenever you want
to define the database schema,
you can use the commands from
this particular language coming
to the data
manipulation language
the manipulation language
consists of all the commands
that deal with the manipulation
of data presented the database.
So for example,
if you have n number
of tables and you want
to manipulate the data
and present in those tables,
then you use data
manipulation commands coming
to the data control language
the data control language
includes the commands
which deal with
the right permissions
and other controls
of the database systems.
So for example,
you work for an Enterprise
and the Enterprise
has a database now,
obviously all the employees
will not have the access
to the database right so
data control language.
Mainly deals with how you give
the user permissions to access
to the database now coming
to the last type of language
that is the transaction
control language.
This basically
includes the commands
which mainly deal
with the transaction
of the database whenever
you want to roll back
or you want to perform any
various kind of transactions,
then you use the set of commands
from this language, right?
So, I hope I'm clear.
So as I said,
there are mainly
for different subsets of sequels
that you should mention
that is the data
definition language
the data manipulation language
the data control language
and the Construction Control
language now moving on
to our next question.
That is what do you mean
by database Management systems
and what are
the different types of it?
So now if I have to define
the database management
system for you,
then a database management
system is a software application
that interacts with
the users applications
and the database
itself to capture
and analyze the data.
So the data stored
in a database can be modified
retrieved and deleted
and can be of any type like
the strings numbers images Etc.
So you can basically
store any kind of data.
Not a different kinds
of database Management systems
are the hierarchical
the relational the network
and the object oriented database
Management Systems the talking
about the hierarchical
database Management Systems
this type of database
management system has
a style of predecessor
and the successive
type of relationship.
So it has a structure
similar to that of a tree
while in the nodes
represent the records
and the branches of the tree
represent the fields coming
to the relational database
management system this type
of database management system
uses a structure
that allows the user to identify
and access data in relation
to another piece of data
in the database the network
database management system
supports many to many relations
where in multiple member
records can be linked
and the object oriented database
Management Systems use
a small individual
software called objects
and each object contains a piece
of data and instructions
for the actions to be
done with the data.
So I hope I'm clear
with this point.
So now moving on
to our next question
that is what you mean by a table
and a field in SQL.
So now the answer
to this question is quite simple
at a Basically refers
to a collection of data
in an organized manner
in forms of rows
and columns and the field
refers to the number
of columns in the table.
If you have to explain
anybody what table
and field mean so
basically in SQL,
you have a database
and the database consists
of n number of tables, right?
So that is basically a table
and the tables have
few column names, right?
So the column names
are basically the feet
so you can explain
with an example as you can see
in the screen so over here,
I have an employee
information table
into which the employee
information table is a table
and the column names
in this particular table
are the fries now moving on
to our next question.
That is what our joints in SQL.
Now, this is one
of the most popular questions
that is generally asked
in your interviews.
So basically a joint Clause is
used to define to combine rows
from two or more tables based
on related columns between them.
It is used to merge two tables
or retrieve data from there
and there are mainly
for types of joints in SQL.
That is the inner join
the full joint the left join
at the right joint right now
moving on to our next question.
Is what is the difference
between care and warka
to data type in sequence?
So this is one of the most
confusing questions
that is generally asked
in an interview you'll see you
since you know generally
people get confused
between what is care.
And what is vodka datatype now
both Karen barca to are used
for character data type,
but work at to is used
for character strings
of variable length,
whereas the cat is used
for strings of fixed length.
So whenever you have
strings of fixed length,
then you use the cad data types
and whenever you have strengths
of variable length,
then you can use
the warka to data type
for example cat then
can only store 10 characters
and will not be able to store
a string of any other length
whereas to work at 2:10
can store any length
that is either 628.
It completely depends
on the variable right now
moving on to our next question.
That is what is the primary key.
Now when you're working
in a database field,
obviously a company you can have
n number of databases having
n number of tables inside this
now each and every table
in a database has
to be connected
with the other table
right now for that.
We need to unique I didn't fight
a table or you know,
you can say you need
to uniquely identify a column.
So basically primary key
is a set of attributes
that can be used to uniquely
identify every Tuple.
So if there are
three to four candidate Keys
present in a relationship then
out of those one can be chosen
as the primary key.
Now, as you can see
on the screen, we have
an employer information table
which has columns
like, you know,
employee number employee
named employee country
and the employee age now,
obviously you can uniquely
identify each and every employee
with the employee ID, right?
So basically that
will be a primary case
over here employee number
will be a primary key.
Now.
Let's move on to a next question
that is what our constraints
so constants are basically
used to specify the limit
of the data type
of the table, right?
So whenever you're
creating a table,
then you can use
the constant to set a limit
of the data type of the table.
It can be either specified
while either creating
the table or you know,
while you're ordering
the table statement
so it can be basically specified
in both the ways now there
are mainly five constraints
that you need to understand
about that is Not null
constraint the unique
constant the check constraint
the default constraint
and the index constraint
that the not now
constant basically ensures
that a null value
cannot be stored in the column.
So whenever you mention
this particular constant
into a column of your table,
then this particular constant
will make sure that you know,
no null value can be stored
for that particular column
in the table moving on
to the next constant.
That is the unique constant
this constant make
sure that you know,
all the values in the column
are basically different.
So if you need to make sure
that you know in a column
you have unique values
for each and every
row then you can use
this particular constraint now
moving on to the next constant.
That is the check constraint the
check constant basically ensures
that all the values
in the column satisfy
specific condition.
So if you have a condition you
need to make sure that you know,
only the values
with satisfy the condition
are stored in the column,
then you can use this particular
constraint now talking
about the default constraint
default constant consists
of a set of default values
for a column with
no value specified.
So for example in a table
of ten rows No,
you have a column in which you
do not specify all the values
for all the 10 row
then you can make sure
that you know,
you will set a default value
in the default value
will be automatically
stored wherever you
don't mention a value
in in that call now moving on
to the last step of constant.
That is the index contrary.
The index constraint
is used to create
and retrieve data from
the database is very quickly.
So I hope I'm clear with the
different types of constraints
that you need to answer about
when you're asked his question
about what are the constraints
that you know,
now let's move on
to the next question
that is what is the difference
between SQL and my SQL now,
this is one of the most
popular questions
that is generally ask, you know,
because since people generally
get confused between what is SQL
and what's my sequel so
let me just explain you
the simple difference
between both of them.
So SQL is basically
a standard language
which stands for structured
query language based
on the English language.
Whereas the MySQL is
a database management system.
So SQL is basically the core
of relational database
which is used for accessing
and managing the database.
But as my SQL is
a relational database.
Iseman system which works
on many platforms.
So basically this provides
multi-user access to support
many storage engines
and is backed by a rocker.
So that's the basic difference
between SQL and MySQL guys SQL
is basically the core
of relational database
which is used for accessing
and managing a database
and MySQL is
an open source relational
database management system.
Now, let's move over
to our next question
that is what is
the unique key now
unique key basically identifies
a single row in a table.
This basically allows multiple
values per table and also
the null values are allowed
so you'll have to make sure
that you know,
whenever using the unique key,
you need to have an idea
that you know,
it identifies a single Row
in the table multiple values
are allowed for the table
and also null values are allowed
but yes duplicate values
are not allowed.
All right now moving forward
to our next question that is
what is a foreign key
a foreign key basically
maintains referential Integrity
by enforcing a link
between the data
into tables the foreign key
in the child table.
Friends is the primary key
in the parent table
and the foreign key
constraint prevents actions
that would destroy the links
between the child
and the parent table.
So if you have a database
and you know,
if you have around 10 tables
in that particular
database and then
if you want to maintain
relations between all
these tables in the database,
then you need to use the primary
key foreign key concept
so that you identify
the relation between both
these tables with the help
of foreign key now moving
forward to a next question.
That is what do you mean
by data Integrity now
data Integrity basically
defines the accuracy
of the data as well
as the consistency
of data stored in the database.
It also defines the Integrity
constraints to enforce
business rules on the data
when it is enter
into an application
or a database.
So if you have to
answer this question,
then you can answer
by saying that,
you know data Integrity
basically defines the accuracy
and the consistency of data.
Now, let's move forward
to a next question
that is what is the difference
between the clustered
and the non-clustered
index in SQL.
The clustered index
is basically used
for The easy to retrieve all
of the data from the database
and is faster
than the non-clustered index
the clustered index Alters.
The way records are stored
in the database
as it sorts out
the rows by column
which is set to be
the clustered index
and only one table can have
a clustered index coming
to the Dom clustered index
the non-clustered index
is also used for the retrieval
of data from the database.
But yes, it is slower
than the cluster index
the non-clustered index does
not alter the way it is stored.
But yet it creates
a separate object within a table
which points back
to the original table
rows after searching
and also one table can have
many non-clustered indexes
the main difference
between both of them are
what are used for easy
retrieval of data,
but yes, the clustered
index is faster
than the non-clustered index
and only one table can have
only one cluster index
but yes a single table can have
many non-clustered indexes apart
from that the third difference
between both of them is also
that you know,
the cluster index Alters the way
records are stored in the table
and the non Plus.
Index does not do that.
Now.
Let's move forward
to our next question.
That is write a SQL query
to display the current date.
Now when you
attend the interviews
for a database administrator,
let me just tell you
that not only the theoretical
questions are asked but yes,
yes sometimes asked
to write the query is also
so they can give you
some scenarios questions
or they can just ask
you a few simple queries
that you need to make sure
that you know,
you know them
so that's the reason
that this question
could be asked that,
you know, write a SQL query
to display the current date now
in SQL to display
the current date.
We have a function
known as the get day.
So basically with the help
of this function,
you can return the current date
and time stamp.
So the syntax of function
is really simple
as just get date and it
applies to SQL Server 2000
17 16 14 12 2008
R2 2008 and 2005.
If you just have to give
an example to the interviewer
about how to write a query
to display the current date.
You can just mention
the query like,
you know select get day.
So when you execute
this particular query
you would see an output that you
know the current date and time.
I would be displayed
as out right
so that's how you can write
a query to display
the current date now moving
forward to the next question
that is what are
the different types of joints.
So as I mentioned
before there are mainly
for types of joints
that you need to talk
about that is the inner join
the full join the left join
and the right joint.
So talking about inner join
this joint basically
returns to circuits
which have matching values
in both the tables now consider
two tables table a and table
B.
Now when you apply
the inner join on both
of these tables you'll observe
that, you know,
the output would be
all those records
which have matching values
in both the tables
that is table a
and table be the talking
about the full joint
the full joint basically
returns all those records
which either have
a matching value in the left
or the right table.
So whenever you apply full join
in both of these tables,
you will see that, you know,
you get an output
of all those records
which either have
a match in the left
or the right table now talking
about the left join the left jaw
and Returns the records
from the left table
and also those records
which satisfy the condition
from the right table.
So if you have table a
and table B,
and you apply a left join, too.
Of the Stables then the output
would be all those records
from the left table
and the record
from the right table
which satisfy the
specified condition.
Similarly.
The right joint is just
the vice versa the right joint
basically returns a record
from the right table
and also those records
would satisfy the condition
from the left table.
So whenever you apply
the right choice in to table a
and table be and consider
that you know,
the table B is right table
you'll get all those records
from the table p and also
matching records from table a
which satisfy the condition.
So these are the various types
of joints that you need to talk
about guys now moving forward
to the next question.
That is what do you mean
by denormalization now
denormalization basically refers
to a technique
which is used to access the data
from higher to lower
forms of database.
It increases the performance
of the entire infrastructure
as it introduces
redundancy inter table
and it also adds
the Redundant data into a table
by incorporating database
queries that combined data
from various tables
in a single table.
So if you have to just
explain denormalization,
you can just say
that you know,
it is a Sneak which is used
to access data from higher
to lower forms of database
and it basically adds written
the data into the table by
incorporating database queries
that combine data
from various tables.
So I hope I'm clear
with this point now moving
forward to the next question
that is what are the entities
and relationships.
So let me just explain you
what entities are first so
entities can be anything like,
you know a person place
or thing in real world
about which data
can be stored in a database.
So table stored data
that represent one
type of entity.
So for example,
a blank database has
a customer tables to store
the customer information.
Now the customer Table stores
this information as
a set of attributes
that is basically the columns
within the table
for each and every customer.
So if you consider,
you know, a customer table
has around five parameters,
like customer ID customer
name customer phone
number customer email ID
and so on then all
these customer ID customer
name customer phone number are
basically the column names
that is the attributes
for the entities
and customer information
will be the entity the talking
about relationships relational.
Between the entities
that have something
to do with each other
is basically relationships.
So for example,
the customer name is related
to the customer account number
and the contact
information right.
Now.
This might be in the same table
or it could be
in any other table also, right.
So if you have two tables
out of which one table
has the customer name
and the other table has
all the other information
like the customer ID
customer name and so on.
Now these two tables
will be related to each other
with the relationship
so that the customer details
could be retrieved now
that particular information
is basically relationships
now moving forward
to our next question
that is what is an index
in the previous questions.
I talked about
the non-clustered index
and the clustered
index is right,
but what exactly is
index now index basically refers
to a performance tuning method
of allowing faster retrieval
of records from the table.
So as I mentioned before
in the plastered on the
non-clustered indexes question,
both of the indexes
are basically used
for easy retrieval
of data, right?
So that's what index
does this is basically
a performance tuning method to
allow faster retrieval of data.
From the table
and also an index creates
an entry for each value.
So to allow the faster retrieval
of records from the table it
basically creates an entry
for each and every value.
Now, let's move forward to
the different types of indexes.
So well, I've already explained
you about two types of indexes
that is the clustered and
the non-clustered index but yes,
let's understand again
about both of them
and also an additional index
that is the unique index.
So mainly there are three types
of indexes that is used
that is the unique index
the clustered index
and the non-clustered index
the unique index basically
does not allow the field
to have duplicate values
if the column is unique indexed.
So for primary key
is defined a unique index
can be applied automatically.
So unique index is mainly used
when you do not want any
duplicate values to be present
in the column now moving
to the clustered index
the clustered index basically
reorders the physical
order of the table
and searches based
on the basis of key values.
So each table can have only
one cluster index coming
to the third type
of index Teresa nonplussed.
Indexed non-clustered index does
not alter the physical order
of the table and maintains
a logical order of the data.
So each table can have many
non-clustered indexes, right?
So basically guys there
are three types of indexes
that you need to explain about
that is the unique the cluster
and the non-clustered index
now moving forward
to our next question.
That is what this normalization
and what are its advantages.
So normalization is
basically the process
of organizing data to avoid
duplication and redundancy.
So it's basically the opposite
of denormalization that we
just talked about in one
of the previous questions.
So if you just have
to Define normalization,
then you can see
that you need is a process
of organizing data to avoid
duplication and redundancy.
Now the advantages
of normalization are
as you can see on the screen,
it offers better database
organization more tables
with small rose efficient
data access greater flexibility
for queries quickly finds
the information easier
to implement security allows
easy modification reduction
of redundant data
and duplicate data
more compact database.
And ensures consistent data
after modification.
So Guys, these are
few advantages of normalization.
Now, let's move forward
to our next question
that is what is the difference
between the drop
and the truncate commands
the crop command basically
removes the table
and it cannot be rolled back
from the database whenever
you use the drop command
just remember that, you know,
you completely drop
the complete table
and it cannot be rolled
back from the database
and the truncate command removes
all the rows from the table
and also cannot be rolled
back into the database.
So the drop command
and the truncate command
differ by this.
So whenever you used
to draw from and you
drop the complete table
and whenever you will use
to truncate command,
you will remove all
the rows from the table
the syntax of both.
Our commands are almost same
the syntax of drop command
is basically drop
object in object name
that is basically the table
and table name and the Syntax
for truncate command is
truncate table and table dream.
So guys that was about the drop
and the truncate command.
Now, let's move forward
to the next question
that is what are the different
types of normalization.
So there are mainly
for types of Vicious
that is 1 and f 2
and F 3 and F and bcnf.
Now, let me explain
you the different types of
normalization with an example.
So guys, this is the table
that we're going to consider
for the example.
Now you basically have
to apply normalization
for this particular table the
to apply normalization
to this particular table.
You'll clearly observe
that you know,
there is Alice Johnson
who has a particular address
and has rented few movies
from a specific category
and the category action
is mentioned twice over here.
Similarly.
David Allen's address
has been divided
into two different record.
And the movies rented
in the categories
have also been divided
into two different records
right now to apply normalization
to this particular table
you first have to apply
the one in them.
That is the
first normalized form.
So for a table to win one
and if you need to make sure
that each table cells
should have a single value.
So basically all
the records must be unique.
So as you saw
in this particular table,
we had redundant values
in the same record, right?
So we're going to have each cell
with a unique record.
So we'll have records
such as you know.
Alice Johnson First Street house
number 3 mission impossible
and the second record
as Miss Alice Johnson
for Street house number
3 Clash of Titans.
Similarly.
We'll have mr.
David Allen Third Street fortify
with Interstellar the mr.
David Allen Third Street 45 with
Edge of Tomorrow and similarly.
Mr. David Allen 7th.
Annual Mission
Impossible fall out.
So that's all basically you
get your table into one.
And if you have records
with unique value
in the complete table now
as you can see on the screen,
this is a table
for one and left.
Now, you have to divide
this table to 2 and F.
So when I said to and if that is
the second normalized form,
you can divide this table
into two different tables,
so to have a table into and
if you need to make sure
that you know,
the database should be in one
in if and should also have
a single column primary key
since the table is in 1nf
that we considered
that's applicable
to our situation.
Now, let's move forward
to the 2N them now in the to
and there will clearly see
that you know,
we have salutations
full name address
and movies rented right?
Let us divide this People need
to do different tables.
You can clearly see
that, you know,
I've added an ID
to both the tables.
So I've added ID salutation
full name and address
and ID movie stranded
so Miss Alice Johnson staying
in First Street house number
3 must have rented
Mission Impossible.
Similarly.
She must have rented
Clash of Titans.
Mr. David Allen thing Interstate
45 has entered Interstellar
and Edge of Tomorrow.
And mr.
David Allen staying in 7th.
Avenue has rented
Mission Impossible fall out.
So that's how I've divided
the table and to do
and they're now moving
forward two three left
now a database to be entry
and if you need to make sure
that you know,
the or database
is person to an f
and must not have any transitive
functional dependency.
So for that what you're going
to do is you're going to further
divide the two tables
into three tables
as you can see in the screen.
So we're going to have
an ID full name address
and salutation ID,
and also another table with ID
movie stunted column attributes
and the third table
with salutation ID
and shallot Asia.
So basically we're going
to identify mr.
With one miss with
to This with three
and doctor with for so
that's how we're going
to get a tables to 3nf.
Well guys with this
we come to an end
of this particular example
that you are table
is been completely normalized
and the highest normal form
available for this
particular table is tree
and are now moving forward
to the final type
of normalization that is bcnf.
Now if your database is
in third normal form
and they would be
some scenarios where anomalies
would be present.
And if you have more
than one candidate key then
bcnf comes in to roll
when you further divide
your table so that you know,
they would be only
one candidate key present.
So Guys, these are the various
types of normalization
that is 1 and f 2
and F 3 and F in bcnf.
So in 1nf,
there is no repeating groups
within the rows
into an app every non-key
column is dependent
on a whole primary key in 3nf.
It is completely dependent
on the primary key
and no other non-key
column values and in bcnf,
you have to make sure
that you know,
there's only one candidate key
present in the team.
So Guys, these were
the different types
of normalization that you
need to understand about now,
let's move What our next
question that is
what is acid property
in database the asset
property mainly stands
for atomicity consistency
isolation and durability.
It is basically used to ensure
that the data transactions
are processed reliably
in a database system.
So if you have to Define
asset property in a database,
then you have to Define all
these terms individually
a stands for atomicity.
So Atomic City refers
to the transactions
that are completely
done or failed
where transaction refers to a
single logic operation for data.
It means if one part
of any transaction fails,
then the entire transaction
fails and the database state
is left unchanged coming to
consistency consistency ensures
that the data must meet
all the validation rules
in simple words.
You can see that you know,
your transaction never leaves
the database without completing
its take third part
that is isolation.
The main goal of isolation
is the concurrency control
and the last part that is
durability durability means
that if a transaction
has been committed it will occur
whatever may come in between To
such as far large crash
or any sort of error.
Now, let's move forward
to our next question.
That is what you mean
by a trigger in SQL.
So triggers and SQL
are a special type
of stored procedures
that are defined to execute
automatically in Play store
after data modifications.
It allows you to execute
a patch of code
when an insert update
or any other queries executed
against the specific table.
So guys, there are
mainly 6 type of triggers
that you need to understand
about that is the before insert
after insertbefore update
after update before delete
and after delete.
So basically the
two main key terms
that you need to understand
at the before
and the after so these
are completely applied
on the insert update
and delete commands.
The before insert
is basically activated
before the data is inserted
into the table.
The after insert is activated
after the data is inserted
into the table.
The before update is activated
before the data
in the table is updated and
after update is activated
after the data
in table is updated.
The before delete is activated
before data is removed
from the table.
After delete is activated
after the data is removed
from the table.
So as this is what a trigger
in SQL s it is basically a type
of stored procedures
that is defined to execute
automatically in place
or after data modifications.
Now, let's move forward
to our next question
that is what are
the different types
of operators available in SQL.
So there are mainly five types
of operatives available in SQL.
That is the arithmetic bit
wise comparison compound
and The Logical operators.
So as these are
the main file types
of operators available in SQL.
So now let's move forward
to our next question
that is our null values same as
that of zero or a blank space
a null value is not at
all same as that of a zero
or a blank space an l-value
mainly represents a value
which is unavailable unknown
assigned or not.
Applicable.
Whereas a zero is a number
and a blank space.
It's a character.
So if anybody asks you
if null values are same
as that of 0 blank space,
please say it's know
because you know,
the null values
is basically anything
which is unavailable unknown or
and is s And the not applicable
but as a zero is a number
and a blank space
is a character.
Now, let's move forward
to our next question
that is what is the difference
between a cross joint
and a natural join the cross
joint produces the cross product
or Cartesian product
of two tables,
whereas the natural join
is based on all the columns
having the same name
and data types
in both the tables
the main difference
between both of them is
that in the cross
joint basically produces
the cross product
of both the table
and the natural join
is completely based
on all the columns having
the same name data types
in both the team now,
let's move forward
to our next question.
That is what is a sub query
in SQL a sub-query is basically
a query inside under the query
where a query is defined
to retrieve data
or information back
from the data base.
So some queries are
always executed first
and the result of the sub-query
is passed on to the main query.
So as you can see on my screen,
I have an outer query
inside which I have a sub query
or an inner query.
So basically the square
is assigned to retrieve data
from the database
and then the square
Will be executed first.
So if there are any errors
in this query then
the complete query
would not be executed only
when the stock query
is completely executed.
Then the outer query
will be executed.
Now, let's move over
to the next question
that is what are the different
types of sub-query.
Now, there are mainly
two types of sub queries.
That is the correlated and
non correlated sub-query.
The correlated subqueries
are basically those queries
with selected data
from a table referenced
in the outer query.
It is not considered
as an independent query
as it refers to another table
and the first column in a table.
The non correlated sub-query
is an independent query
where the output
of the sub-query is substituted
in the main coil.
So you'll have to
understand the difference
between both of them.
If you do not
understand by definition,
you could definitely go
and execute the commands right
the two different types
of queries in SQL
and you get to know what
I'm talking about basically
non-correlated type
of sub query the output
of the sub-query
will be substituted
into the main query
and correlated type of sir.
Create the query select the data
from a reference table
in the outer query.
Now.
Let's move forward
to our next question.
That is can you list
the ways to get the count
of Records in a table now
to count the number
of Records in a table,
you can use various
kinds of queries.
So there are
mainly three queries
that have mentioned over here.
That is Select star from table
1 star means basically select.
All right, when you want
to select all the rows all
the records in the table,
you can use star next
when I say select count star
from table 1 that is
basically you select all
the records from the table 1
and then you count them
and coming to the third
query data select rows
from system in Texas
where ID object ID of table
1 and indeed is less than 2
that means is basically Council
number of Records in the table
with system in Texas.
These are the various ways
to count the number
of Records in a table.
Now, let's move forward
to our next question
that is write a SQL query
to find the names of employees
that begin with the alphabet
a now to display the name
of employees that begin
with a you Type in the command
like you don't select
star from table name
where you mentioned
your table name
where employ name
because I'm considering
an employee table
in the scenario
like a percent in quotes So
when I say a person all
those names starting with a
and having any letters
or any characters
after that would be selected.
So guys, that's
how you can write a query
to find the name of employees
that begin with a now suppose.
If you want to write a query
to find the names of employees
that end with a what you
can simply do is you can
against either similar query
like in a select star
from table name
where employ named
like percentage a rights
over the percentage
comes forward that means,
you know all the names
that start with any alphabet
but end with the a
will be considered now,
let's move forward
to our next question
that is write a SQL query
to get the third highest salary
of an employee
from an employee table now
to get the third is
salary of an employee
from an employee table.
You can write a query
as you can see on the screen.
You can just mention
select top one salvi
from and then write a sub query
which says select top three V
from employee table
order by salary
in a descending order as M
and then again order
by salary in ascending order
for the outer query
so you can write a query
like this Christ.
Now, let's move forward
to a next question
that is what is the need
of group functions
in SQL now group functions
work on a set of rows
and return one result per group.
So basically some of the most
commonly used group functions
are the average count
Max Min some and variance So
when you say what is the need
of group functions
in SQL the need is basically
because the group functions
work on a set of row
and return one result per group.
Now, let's move over
to our next question
that is what is a relationship
and what are the different
types of relationship.
So the relation
or links between entities
that have something to do
with each other can be basically
termed as relationships.
So relationships can be also
defined as the connection
between the tables
in a database right now.
The different types
of relationships are
the one to one relationship
the one to many relationship
many to one relationship
and the self
Sensing the relationship
when you have a relationship
between two tables
at his table a and table
be so when I say
one-to-one relationship
between both the tables table a
and table be a single recording
table a will be related
to a single record
in table be coming
to one-to-many relationship
a single recording table a
can be related to many records
and table P next coming
to the many to one
relationship many records
in table a can be
related to one record
in table be coming to
self-referencing relationship.
The self-referencing
relationship basically means
that you can have a single table
out of which two columns
are related to each other
with the relationship.
Now, let's move over
to a next question.
That is how can you insert
null values in the column
while inserting data now to
insert null values in a column
while inserting the data
you mainly have two ways
that is either by implicitly
by omitting columns
from columnist or explicitly
by specifying null keyword
in the values Claus.
So as these are the two ways
through which you know,
you can insert null
values in a column
while in I think the data now,
let's move forward
to the next question
that is what is the difference
between the between
and in condition operators.
The between of it is basically
used to display rows based
on a range of values in a row.
Where as the in condition
operator is used to check
for values contained
in a specific set of values.
So if you have to explain
with an example for between
and in condition operators,
then you can use
the example on the screen.
You can just write a query like
in a select star from students.
We're rolling were
between 10 to 50.
So all those records
from the students table
would be selected
whose role number stands
between 10 to 50 coming
to the in condition operators.
You can use a query
like this like,
you know, select star
from students were roll number
in eight fifteen twenty five.
So all those records from a
student table would be selected
where the roll number
would be either eight fifteen
or twenty five.
So guys,
that is the main difference
between the between and the
in condition operators.
Now, let's move forward
to our next question.
That is why are
the SQL functions?
The SQL functions are used
to perform some calculations
on the data to modify
individual data items
to manipulate the output
to format dates numbers
and also to convert
the data types.
So if you want to perform
these activities or you know,
if you want to
perform these actions,
then you need to use
the SQL functions.
Now, let's move forward
to our next question
that is what is the need
of merge statement.
This statement is basically
used to allow conditional update
or insertion of data
into a table.
So it performs an update
if a row exist or an insert
if the road doesn't exist.
So basically the need
of merge statement is
that you know,
it allows conditional update or
insertion of data into a table.
Now, let's move over
to our next question.
That is what do you mean
by recursive stored procedure
recursive stored procedures
refers to a stored procedure
which caused by itself
until it reaches
some boundary condition
this recursive function
or procedure helps
the programmers to use
the same set of code
n number of times.
So basically you'll first
mention the boundary condition
and then you are you
Recursive stored procedure
to check you know,
if it calls by itself
till it reaches the
specified boundary condition.
Now, let's move forward
to a next question
that is what is a clause in SQL.
Now sequel Clauses
basically helps you to limit
the result set by providing
a condition to the query
across helps to filter the rows
from the entire set of Records.
So for your better understanding
the example is basically where
and having Clauses.
So when you write
a query you having
these two particular Clauses you
basically mention a condition
into these particular
Clauses, right?
Like we're student number
is equal to 2 or having,
you know goal number
greater than 5 and so on right?
So that's how you
can use Applause in SQL.
Now, let's move forward
to the next question
that is what is the difference
between the having clause
and the where cross
but the main difference
between having clause
and where Clauses basically
that you know,
the having Clause can be only
used with the select statement.
It is usually used by the group
by clause and whenever
Group by is not used
having basically behaves like a
where Clause coming
to the where Clause the
where Clause is applied.
Each row before they
are part of the group
by function in a query.
So having Clause is basically
used with the select statement
and it is usually used
in the group by Clause
when the having Clause
is not used with the group by
Clause the having Clause behaves
like a where cross so guys
that was about the difference
between the having clause
in the where Clause now,
let's move forward
to our next question
that is list of ways
in which Dynamic SQL
can be executed the ways
in which Dynamic SQL
can be executed our
by writing a query
with parameters using exact
and by using SP - execute SQL.
So there are basically
three ways in which
Dynamic SQL can be executed
that is by writing query with
parameters using X and using
SP underscores execute SQL.
Now moving forward
to the next question
that is what are
the various levels
of constraints the constraints
as I mentioned before I
basically the representation
of a column to enforce
data entity and consistency.
So Bailey there are
two levels of constants.
That is the column
level constant and
the table level constraint now,
let's move Over
to our next question
that is how can you fetch
Commons records from two tables?
Well, you can fetch cam records
from two tables by using
the intersect statement.
So the syntax
of intersex statement is
as you can see on the screen.
It's basically select column
1 column 2 and so many columns
from the table
name where condition
basically, we you mentioned
the condition over here.
Then you mention
the keyword intersect
and then again mentioned
under the table name, right?
So that is again,
you mentioned select the columns
from a specific table
where the condition matches
so that's how you use
the intersect so mad so
for your better understanding
you can see the example
on the screen you
can see you know
that I have used
select student ID
that is basically
a single column from student
then use the intersect command.
And then again,
I've mentioned select student ID
from a different table
that is exam.
So that's how you
can fetch common the courts
from two tables.
Now, let's move forward
to our next question
that is listen case
manipulation functions in SQL.
So there are mainly three case
manipulation functions in SQL.
That is the lower upper
and the in eat cap the talking
about the logo Shouldn't
this function basically
Returns the string in lowercase.
It takes a string as an argument
and returns it by converting
into a lower case the syntax
of this particular function is
basically lower and in Brackets,
you mentioned strings
in quotes now moving forward
to the next function.
That is the upper function
this function Returns
the string in uppercase.
It takes a string as an argument
and returns it by converting it
into an upper case, right?
So the Syntax
for this particular function
is upper and in Brackets,
you mentioned the spring
within the codes moving forward
to the third function
that is the in each cap.
This function Returns the string
with the first letter
in uppercase and the rest
of the letters in lowercase.
So the Syntax
for this function is basically
in each Gap and in Brackets,
you mentioned the
string with coats.
So Guys, these were
the three case manipulation
functions in SQL.
Now, let's move forward
to our next question
that is what are
the different set
of operators available in SQL.
So the different set operators
available in SQL
our Union intersect and the -
operators, so let's talk
about Union first.
So when you consider What
he said is the left query
and the right query
and you apply the union
operation you will see
that you know,
it will combine Rose
from both those queries.
You'll get an output
of you know,
all those records
from the left table.
That is basically the left query
and also from the right query
that is the right table moving
forward to the next operation
is the intersect operation.
So when you apply
the intersect operation
to the left query
and the right query you'll see
that you know,
you'll get only those rows
which are common
in both the queries, right?
So for example,
you have ten rows
in the first table
and ten rows
in the second table,
but the common Rose
in both these table
are just two rows.
So you'll get only
those two rows as an output
to this particular
operation moving forward
to the third type of operation.
That is the - operation.
So when you apply left query -
right query you'll see
that you'll get the only
those rows from the left query
which are not included
in the right query as an output
and when you apply
the operation of right query -
left query you'll see
that here you'll get only
those rows as an output
from the right query
which are not included.
It in the left query.
So Guys.
These were the different set
of operators available in SQL.
That is the union operator
the intersect operator
and the minus operator.
Now, let's move forward
to a next question
that is what is
an alias command.
Now here is name can be given
to any table or any column.
So the scale is named
can be referred in
where Clause to identify
a particular table or a column.
So that's basically
an alias command.
So for example,
if you see on the screen
you see a query right?
Let's select m dot employee
ID department DOT result
from employ amp
Department as depth
where m dot employee ID is equal
to Department employee ID.
So what I've given over here is
that I've given an alias name
to the employee table as M.
And for the Department table,
I've given an alias
name as depth.
So that's how you can use
the Alias command guys.
Let's move forward
to the next question.
Now that is what our Aggregate
and scalar functions.
The aggregate functions
are used to evaluate
mathematical calculation
and return a single value.
These calculations are done
from columns in a table.
So for But
if you need to consider
the aggregate functions,
then you can consider
the max function
and the count function as
an aggregate function now coming
to the scalar functions
the scalar functions basically
return a single value based
on the input value.
So if you need an example
for scalar functions,
then you can consider the UK's
and now a scalar functions
as they're calculated
with respect to the spring.
So guys, this is
what aggregate functions
and scalar functions mean.
Let's move forward
to the next question.
That is how can you fetch
alternate record from a table.
When you answer this question
of how you can fetch
alternate records from a table.
Please make sure that you
will you mention a point
that you can fetch alternate
because that is both the odd
and the even row numbers now
to display the even row numbers
you can use the command like,
you know, select student ID
from select row number 2 net ID
from student again.
We're moored row number
comma 2 equal to 0.
So we're using this function
of where Ma Drew number comma
2 equal to 0, right.
So when you divide
the row number with to and
if you get the reminder as 0
then that particular row number
would be an even number and Lily
if you get a reminder as 1 then
that particular row number
would be an odd number
that's how you can segregate
the alternate recalls.
That is the even number records
and the odd number records.
You just have to mention
the condition madro number comma
2 equal to 0 if
or even and madro number comma
2 equal to 1 for odd now,
let's move forward
to the next question
that is named the operator
which is used in query for
pattern matching the operator,
which is used in the query
for pattern matching is
basically the like operator so
in like operator,
you can use either
the percentage sign or the
underscore sign the percentage
sign basically matches
to zero or more characters.
If you use the percentage sign
in the query like in
a select star from students
where student named
like a percentage
so all those student names
whose name starts with a
and ends with any character
will be taken into account
and all the information
related to all those records
will be retrieved
and coming to when you
use the underscore sign
the underscore sign basically
matches exactly one character.
When you use the underscore sign
in the query like,
you know select star
from student by student name
like ABC underscore.
So all those records,
you know whose student
name has a characters
which match the pattern
ABC and any character present
after that will be
taken into account.
So guys, that's how you
can use the like operator
for pattern matching.
You can either use
the percentage sign with it
or an underscore sign with it.
Now, let's move forward
to the next question.
That is how can you select
unique records from a table?
Well, you can select
unique records from a table
by using the distinct keyword.
So for your better understanding
you can write a query like,
you know, select
distinct student ID
from student, right?
So with the help of this query
all the unique records
from the student table
will be selected.
Let's move forward
with the next question.
That is how can you fetch
first five characters
of a string now,
there are obviously a lot
of ways to patch the characters
from a string.
So for your understanding I've
mentioned two examples over here
that is either you can use
the substring command
or the right command.
So when you use the substring
statement you can use the Smells
like you know select
substring student named
want to fight a student
named from student.
The first five characters
of student name
will be retrieved from
the student table now similarly
when you use
the right statement,
you can use the statements like,
you know, Selleck bright student
name comma five a student named
from student, right?
So with this query again
all the first five characters
of each and every student name
from the student table
would be retrieved.
So that's how guys you can fetch
the first five characters
of a string
that is either by using
the substring statement
or the right State now,
let's move forward
with the next question
that is what is
the main difference
between SQL and pl/sql.
The sequel as I mentioned
before is a query language
that allows you to issue
a single query or execute
the single insert update.
Delete whereas PL / sequel
that is basically oracle's
procedural language SQL allows
you to write a full program
of Loops variables Etc to
accomplish multiple operations
such as select inserts
updates and deletes.
So is that is
the main difference
between SQL and procedure?
Language sequel in SQL
you just have to write
a single query to execute
insert update and delete
and in procedure wrangled SQL.
You have to write full program
with loops and variables
to perform multiple operations,
like insert update
delete and selects.
Now, let's move forward
to our next question that is
what is a view a view is
basically a virtual table
which consists of a subset
of data contained in a table
since views are not present.
It takes less space to store
and Views can have data
of one or more tables combined
based on the relationship.
So as you can see on the screen,
I have two tables of you are
n number of rows and columns now
once I execute a query to form
a view you can clearly see that,
you know, it has combined
The Columns and the rows
from both the tables
and has created a view
of both of these tables
into a single table.
That's what a view is.
Now.
Let's move forward
to the next question that is
what our views used for so views
as I just explained basically
refer to a logical snapshot
based on a table or on any other
view it is used for restricting.
The access to data
are making complex
where the simple
ensuring data Independence
and also providing
the different views
of the same data.
So that's what views
are used for.
Now.
Let's move forward
with the next question that is
what is a stored procedure
a stored procedure is
basically a function
which consists of many
SQL statements to access
the database system several
SQL statements are Consolidated
into a stored procedure
and are executed whenever
and wherever required
which obviously saves time
and we can avoid
writing code again.
And again, so with the help
of stored procedure
you can make sure
that you know several SQL
statements are executed again
and again and you don't have
to write the query for it again
because you know you
if you just have
a stored procedure for it,
it will automatically
execute the queries for you.
Now, let's move forward
with the next question
that is list some advantages
and disadvantages
of stored procedure.
Let's talk about
the advantages first.
So stored procedure can be used
as a modular programming
which means create one store
and code for several
times whenever it is.
Quad the supports
faster execution and it
also reduces Network traffic
which provides better security
to the data coming
to the disadvantages
of stored procedure.
The only disadvantage
of stored procedure is
that it can be executed
only in the database
and utilizes more memory
in the database server.
So as that was
about the advantages
and disadvantages
of stored procedures.
Now, let's move forward
with the next question
that is list all types
of the user defined functions.
So there are mainly three types
of user defined functions.
That is the scalar functions the
inline table valued functions
and the multi statement
valued functions.
So you may only have to mention
these three functions
when you're asked
about the different types
of user defined functions next.
Let's move forward
and understand.
What do you mean by collation?
So Collision is defined as a set
of rules that determine
how data can be sorted
as well as compared.
So character data
is sorted using the rules
that Define the correct
character sequence along
with the options for specifying
case sensitivity character
with and excetra.
So that's what a Nation is
guys it is basically a set
of rules that determine
how data can be sorted
as well as compared.
Now.
Let's move forward
with the next question
that is what are the different
types of collision sensitivity.
So the different types
of collisions sensitivity
are the case sensitivity
the corner sensitivity
the with sensitivity
and the action sensitivity.
So there's these are
the mainly for types
of sensitivities of collision.
So I hope that you've understood
till now next in the session.
Let's understand.
What are the local and
the global variables now talking
about local variables first,
these variables can be only used
or exists only
inside the function.
These variables are not used
or can't be referred by
any other function, right?
So whenever you want
to use local variables,
then you need to understand
the fact that you know,
they can be only used or exist
inside a single function
and they can't be referred
by any other function coming
to Global variables.
These variables are the
variables which can be accessed
throughout the program.
So Global variables
cannot be created whenever
the function is called,
but yes, they can be accessed
throughout the Graham so guys
that was about the look
and the global variable next.
Let's move forward
and understand
what is auto increment
in SQL Auto increment keyword
allows the users to create
a unique number to get generated
whenever a new record
is inserted into the table.
So this keyword
is usually required
whenever primary key is used
and auto increment keyword
can be only used in Oracle.
Whereas the identity keyword
can be used in the SQL Server.
So that was about Auto
incremented sequel guys next.
Let's understand.
What is a data warehouse
data warehouse refers
to a central repository of data
where the data is assembled
from multiple sources
of information those data
are Consolidated transformed
and made available
for the mining as well as to
online processing Warehouse data
also has a subset of data
called the data Mass.
So data warehouse
can be understood
as a central repository of data
with the data is assembled from
multiple sources of information.
So guys that was
about data warehouse.
Now, let's move forward
and understand what are
the different Authentication
Modes in SQL server
and how it can be changed the
different authentication modes
in SQL Server are
basically the windows mode
and the mixed mode.
So these two modes
are basically used
in SQL windows and to change
the authentication modes
in SQL Server.
What you can simply do is
you have to follow the steps
that I've mentioned
on the screen
that is you have
to first click on start
and go to programs and go
to the Microsoft SQL server
and click sequel
Enterprise Manager to run
SQL Enterprise Manager
from the Microsoft SQL Server
program group now to change
the authentication modes
in the SQL Server,
you can follow the steps
that I've mentioned
or the screen
that is you have to click
on start go to programs go
to Microsoft SQL server
and click sequel
Enterprise Manager to run
SQL Enterprise Manager
from the Microsoft SQL
Server program group,
then select the server
from the tools menu and select
SQL Server configuration
properties and choose
the security page, right?
So that's how you can change
the authentication mode
in the SQL Server guys.
Now, let's move forward
with the final question
in the session
that is what our stuff
and replace function so
About the stuff function first
this function is basically used
to override existing characters
or insert a string
into another string.
So the Syntax for this function
is basically stuff string
expression start length
and the placement characters
where the string expression is.
Basically the string
that will have characters
substituted start represents
the starting position
the length refers
to the number
of characters in The String
which are substituted
and the replacement spring car
the new characters
which are injected
in the spring coming
to the replace function.
This function is basically
used to replace
the existing characters
of all the occurrences.
So the syntax for
this particular function is
that replace spring expression
search string replacement spring
where every search string
in the string expression
will be replaced
with the replacement string.
I hope I'm clear
with the staff function
and the replace function.
So Guys, these were
the top 65 questions
that could be asked
in your interviews.
So guys if you have written
in any interviews
and you've been asked
questions related to SQL,
please do let us know
in the comments section
if you have any doubts
related Adams so
that we clarified for you guys,
so guys that's an end
from my side today.
I hope you've gone
through all the 65 questions
and you've understood what kind
of questions can be asked
in your interviews.
So if you're preparing
for an interview all the best
for your interviews,
so thank you
and have a great day.
I hope you have enjoyed
listening to this video.
Please be kind enough to like it
and you can comment any
of your doubts and queries
and we will reply them
at the earliest do look out
for more videos in our playlist
And subscribe to Edureka
channel to learn more.
Happy learning.
