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.
