Top 65 SQL Interview Question and Answers | SQL Training | Edureka Rewind

Key Takeaways

hi guys this is sahiti on behalf of Ed Rea and I welcome you to this session on SQL interview questions so in this session guys we'll mainly focus on the top 65 interview questions generally asked about SQL now in the era of 2.5 Quinton bites of data being generated every day data plays a crucial role in decision making for business operation this quite quintessentially makes us handle data and databases and gives us the need to use the database Management systems 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 and 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 so definitely knowing SQL will open the doors for you to become a database administrator so guys if you're preparing for interviews to become a database administrator then 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 question so the first question that we have is what is the difference between delete and trage 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 trunk gate statement the trunet 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 ask you the difference between the delete and the trunet statements don't forget to mention that you know the trunet statement is definitely faster than the delete statement and the trunet statement is from the data definition family and the delete statement is from the dat data manipulation family 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 present in the database so for example if you have n number of tables and you want to manipulate the data in 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 four different subsets of SQL that you should mentioned that is the data definition language the data manipulation language the data control language and the transaction 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 now the different kinds of database Management systems are the hierarchical the relational the network and the objectoriented database Management Systems now talking about the hierarchal database Management Systems this type of database management system has a style of predecessor and the successor type of relationship so it has a structure similar to data for tree where 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 the data in relation to another piece of data in the database the network database management system supports many to many relations wherein multiple member records can be linked and the objectoriented database Management Systems use a small individual softwares called objects and each object contains a piece of data and the 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 do you mean by a table and a field in SQL so now the answer to this question is quite simple a table 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 a 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 fields so you can explain with an example as you can see on 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 fields now moving on to our next question that is what are 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 four types of joints in Sequel that is the inner joint the full joint the left joint and the right joint right now moving on to our next question that is is what is the difference between car and Vare 2 data type in SQL now this is one of the most confusing questions that is generally asked in your interviews you since you know generally people get confused between what is car and what is vcat data type now both car and vcat 2 are used for character data type but vcat 2 is used for character strings of variable length whereas the C is used for strings of fixed length so whenever you have strings of fixed length then you use the C data types and whenever you have strings of variable length then you can use the vatu data type now for example CAD 10 can only store 10 characters and will not be able to store a string of any other length whereas the wcat 21 can store any length that is either 6 2 8 it completely depends on the variable right now moving on to our next question that is what is a primary key now when you're working in a database field obviously a company can have n number of databases having n number of tables inside it now each and every table in the database has to be connected with the other table right now for that we need to uniquely identify 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 around 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 employee information table which has columns like you know employee number employee name employee country and the employee age now obviously you can uniquely identify each and every employee with the employee ID right so basic basically that will be our primary key so over here employee number will be a primary key now let's move on to our next question that is what are constraints so constraints 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 constraints 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 altering 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 the the notnull constraint the unique constraint the check constraint the default constraint and the index constraint now the notnull constant basically ensures that a null value cannot be stored in a 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 makees 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 constraint 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 and you need to make sure that you know only the values which satisfy the condition are stored in the column then you can use this particular constraint now talking about the default constraint the default constraint consists of a set of default values for a column when no values specify so for example in a table of 10 rows you know you have a column in which you do not specify all the values for all the 10 rows then you can make sure that you know you set a default value and the default value will be automatically stored whenever you don't mention a value in that column now moving on to the last type of constraint that is the index constraint the index constraint is used to create and retrieve data from the databases very quickly so I hope I'm clear with the different types of constraints that you need to answer about when you asked this question about what are the constraints that you know now let's move on to the next question that is what is difference between SQL and MySQL now this is one of the most popular questions that is generally asked you know because since people generally get confused between what is SQL and what is my SQL 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 whereas my SQL is a relational datab Bas management system which works on many platforms so basically this provides multi-user access to support many storage engines and is backed by Oracle so that's the basic difference between SQL and my SQL guys SQL is basically the core of relational database which is used for accessing and managing a database and my SQL is an open- Source relational database management system now let's move forward to our next question that is what is a 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 you're 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 in two tables the foreign key in the child table table references 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 our 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 entered 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 our 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 retrial 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 cluster index and only one table can have a clustered index coming to the non-clustered index the non-clustered index is also used for the retrieval of data from the database but yes it is slower than the clustered 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-cluster indexes the main difference between both of them are both 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 clustered 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 clustered index Alters the way records are stored in the table and the non-clustered index does not do that now let's move forward to our next question that is write a SQL query to display the current dat 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 you're sometimes asked to write the queries also so they can give you some scenario based questions or they can just ask you 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 date so basically with the help of this function you can return the current date and Tim stamp so the syntax of the function is really simple it's just get date and it applies to SQL Server 2017 16 14 12 2008 R2 2018 and 2005 now 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 date so when you execute this particular query you would see an output that you know the current date and time 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 four types of joints that you need to talk about that is the inner joint the full joint the left joint and the right joint so talking about the inner joint this joint basically returns those records 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 B you're 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 joint on both of these tables you'll see that you know you'll get an output of all those records which either have a match in the left or the right table now talking about the left joint the left joint 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 joined to both of these tables then the output would be all those records from the left table and the records from the right table which satisfy the specified condition similarly the right join is just the vice versa the right join basically Returns the records from the right table and also those records would satisfy the condition from the left table so whenever you apply the right joint to table a and table B and consider that you know the table B is right table you'll get all those records from the table B 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 into the table and it also adds the Redundant data into a table by incorporating database queries that combine data from various tables in a single table so if you have to just explain denormalization you can just say that you unit is a technique which is used to access data from higher to lower forms of database and it basically adds redundant data into the table by incorporating datab 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 a thing in real world about which data can be stored in a database so table store data that represent one type of entity so 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 now talking about relationships relation or links 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 a relationship so that the customer details could be red now that particular information is basically related 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 cluster indexes 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 clustered and the non-clustered indexes question both of the indexes are basically used for easy retrieval of data right so that's what an index does this is basically a performance tuning method to allow faster retrial 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 if a 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 clustered index coming to the third type of index that is is the non-clustered index the 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-cluster 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-cluster index now moving forward to our next question that is what is normalization and what are its advantages so normalization is basically the process of organizing data to avoid duplication and redundancy it's basically the opposite of d normalization that we just talked about in one of the previous questions so if you just have to Define normalization then you can say that you know it 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 smaller rows 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 com back 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 drop command basically removes a 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 trunet 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 use the drop command you drop the complete table and whenever you will use the truncate command you will remove all the rows from the table the syntax of both the commands are almost same the syntax of drop command is basically drop object and object name that is basically the table and table name and the Syntax for truncate command is truncate table and table name 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 four types of normalizations that is 1 NF 2nf 3nf 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 to this particular table now 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 Allan's address has divided into two different record and the movies rented and 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 1 NF that is the first normalized form so for a table to be in 1 NF you need to make sure that each table cell 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 record record such as you know Miss Alice Johnson First Street house number three Mission Impossible and the second record as Miss Alice Johnson First Street house number three Clash of Titans similarly we'll have Mr David Allen Third Street 45 with interstella the Mr David Allen Third Street 45 with Edge of Tomorrow and similarly Mr David Allen's 7eventh Nu Mission Impossible Fallout so that's how basically you get your table into one andf you have records with unique value in the complete table now as you can see on the screen this is a table for 1 NF now you have to divide this table to 2 NF so when I say 2 andf that is the second normalized form you can divide this table into two different tables so to have a table in 2 NF you need to make sure that you know the database should be in 1 NF and should also have a single column primary key since our table is in one NF that we considered that's applicable to our situation now let's move forward to the 2 andf now in the 2 andf you'll clearly see that you know we have salutation full name address and movies rented right now to divide this table into two 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 movies rented so Miss Alice Johnson staying in First Street house number three must have rented Mission Impossible similarly she must have rented Clash of Titans Mr David Allen staying in Third Street 45 has rented Interstellar and Edge of Tomorrow and Mr David Allen staying in 7th Avenue has rented Mission Impossible Fallout so that's how I've divided the table into 2nf now moving forward to 3nf now a database to be in 3nf you need to make sure that you know your database is first in 2nf 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 movies dented column attributes and the third table with salutation ID and salutation so basically we're going to identify Mr with one Miss with two M with three and doctor with four so that's how we're going to get our tables to three andf well guys with this we come to an end of this particular example that you know our table is been completely normalized and the highest normal form available for this particular table is 3nf now moving forward to the final type of normalization that is bcnf now if your database is in third normal form and there would be some scenarios where animales would be present and if you have more than one candid key then bcnf comes into rule when you further divide your table so that you know there would be only one candidate key present so Guys these are the various types of normalization that is 1 NF 2 NF 3 NF and bcnf so in 1 NF there is no repeating groups within the rows in 2nf 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 table so Guys these were the different types of normalizations that you need to understand about now let's move forward to our next question that is what is asset 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 atomicity refers to the transactions that are completely done or failed where transaction refers to a sing single logic operation of a 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 say that you know your transaction never leaves the database without completing its state 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 ever may come in between such as power loss crash or any sort of error now let's move forward to our next question that is what do you mean by a trigger in SQL so triggers in SQL are a special type of stored procedures that are defined to execute automatically in place or after data modifications it allows you to execute a batch of code when an insert update or any other query is executed against the specific table so guys there are mainly six type of triggers that you need to understand about that is the before insert after insert before update after update before delete and after delete so basically the two main key terms that you need to understand are the before and the after so these are completely applied on the insert update and the 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 a table the before update is activated before the data in the table is updated and the after update is activated after the data and table is updated the before delete is activated before data is removed from the table and after delete is activated after the data is removed from the table so guys this is what a trigger in SQL is 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 operators available in SQL that is the arithmetic bitwise comparison compound and The Logical operators so Guys these are the main five types of operators available in SQL so now let's move forward to our next question that is our null value 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 a null value mainly represents a value which is unavailable unknown assigned or not applicable whereas a zero is a number and a blank space is a character so if anybody ask you if null values are same as that of zero blank space please say it's no because you know the null values is basically anything which is unavailable unknown and is assigned or not applicable whereas 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 joint the cross joint produces the cross product or cartisan product of two tables whereas the natural joint 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 you know the cross joint basically produces the cross product of both the tables and the natural joint is completely BAS 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 subquery in SQL a subquery is basically a query inside under the query where a query is defined to retrieve data or information back from the database so subqueries are always executed first and the result of the subquery is passed on to the main query so as you can see on my screen I have an out of query inside which I have a subquery or an inner query so basically this query is assigned to retrieve data from the data database and then this query will be executed first so if there are any errors in this query then the complete query would not be executed only when the subquery is completely executed then the outter query will be executed now let's move forward to our next question that is what are the different types of subquery now there are mainly two types of subqueries that is the correlated and the non-correlated subquery 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 refers to column in a table the non-edited subquery is an independent query where the output of the subquery is substituted in the main query so you'll have to understand the difference between both of them if you do not understand by definition you can definitely go and execute the commands write the two different types of queries in your SQL and you'll get to know what I'm talking about basically non-correlated type of subquery the output of the subquery will be substituted into the main query and in cor related type of subquery the query is 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 kind of queries so there are mainly three queries that I've mentioned over here that is Select star from table one star means basically select all right when you want to select all the rows all the records from the table you can use star next when I say select count star from table one that is basically you select all the records from the table one and then you count them and coming to the third query that is Select rows from system indexes where ID object ID of table one and indeed is less than two that means it basically counts the number of Records in the table with system indexes 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 can type in the command like you know select star from table name where you mention your table name where employee name because I'm considering an employee table in the scenario like a% in codes so when I say a% 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 again the similar query like you know select star from table name where employee name like percentage a right so where 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 highest 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 salary from and then write a subquery with it says select top free salary 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 guys now let's move forward to our 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 sum 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 forward to our next question that is what is a relationship and what are the different types of relationship so 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- referencing relationship when you have a relationship between two tables that is table a and table B so when I say one toone relationship between both the tables table a and table b a single record in table a will be related to a single record in table B coming to one to many relationship a single record in table a can be related to many records in table B next coming to the many to one relationship many records in table a can be related to one record in table B 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 forward to our next question that is how can you insert null values in a 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 a column list or explicitly by specifying null keyword in the values Clause so Guys these are the two ways through which you know you can insert insert null values in a column while inserting the data now let's move forward to the next question that is what is the difference between the between and the in condition operators the between operators basically used to display rows based on a range of values in a row whereas 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 the in condition operators then you can use the example on the screen you can just write a query like you know select sta from students by role number 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 inondation Operators you can use a query like this like you know Select Staff from students where role number in 81525 so all those records from a student table would be selected where the role number would be either 8 15 or 25 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 is why are the SQL functions used the SQL functions are used to perform some calculations on the data to modify individual data items to manipulate the output to format dates and 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 merch 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 exists or an insert if the row 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 forward to our next question that is what do you mean by recursive stored procedure recursive stored procedures refers to a stored procedure which calls by itself until it reaches some boundary condition this recursive function or a 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'll use the 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 our next question that is what is a clause in SQL now SQL Clauses basically helps you to limit the result set by providing a condition to the query a clause 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 having these two particular Clauses you basically mention a condition into these particular Clauses right like where student number is equal to two or having you know role number greater than five and so on right so that's how you can use a clause in Sequel now let's move forward to our next question that is what is the difference between the having clause and the wear Clause now the main difference between having clause and we Clause is 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 wear Clause coming to the we Clause the where Clause is applied to each row before they're a 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 wear Clause so guys that was about the difference between the having clause and the wear 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 are by writing a query with parameters using xec and by using SP hyphen execute SQL so there are basically three ways in which Dynamic SQL can be executed that is by writing query with parameters using ex and using Spore execute SQL now moving forward to the next question that is what are the various levels of constraints now constraints as I mentioned before are basically the representation of a column to enforce data entity and consistency so mainly there are two levels of constants that is the column level constant and the table table level constraint now let's move forward to our next question that is how can you fetch common records from two tables well you can fetch common records from two tables by using the intersect statement so the syntax of intersect statement is as you can see on the screen it's basically select column one column two and so many columns from the table name where condition basically you mention the condition over here then you mention the keyword intersect and then again mention another table name right so that is again you mention select the columns from a specific table where the condition matches so that's how you use the intersect command 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 records from two tables now let's move forward to our next question that is list some case manipulation functions in SQL so there are mainly three case manipulation functions in SQL that is the lower upper and the in cap now talking about the lower function this function basically Returns the string in lower case 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 mention strings and codes 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 mention the string within the code moving forward to the third function that is the inite cap this function Returns the string with the first letter in uppercase and the rest of the letters in lower case so the Syntax for this function is basically in cap and in Brackets you mention the string with quotes 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 operators available in SQL so the different set operators available in SQL are union intersect and the minus operators so let's talk about Union first so when you consider two queries that is the left query and the right query and you apply the union operation you will see that you know it will combined rows 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 that 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 10 rows in the first table and 10 rows in the second table but the common rows 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 minus operation so when you apply left query minus right query you'll see that you know you'll get 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 minus left query you'll see that you know you'll get only those rows as an output from the right query which are not included in the left query so Guys these were the different set operators available in SQL that is the union operator the intersect operator and the minus operator now let's move forward to our next question that is what is an alias command now Alias name can be given to any table or any column so this Alias name can be referred in where Clause to identify a particular table or a column so that's basically an aess command so for example if you see on the screen you see a query right like select m. employ ID department. res from employee M Department as depth where m. employe ID is equal to department. employe 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 Dept so that's how you can use the alas command guys let's move forward to the next question now that is what are 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 example 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 Scala functions the Scala functions basically return a single value based on the input value so if you need an example for Scala functions then you can consider the uke and now as Scala functions as they're calculated with respect to the string so guys this is what aggregate functions and Scala functions mean let's move forward to the next question that is how can you fetch alternate records from a table when you answer this question of how you can fetch alternate records from a table please make sure that you know you mention a point that you can fetch alternate records 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 student ID from student again where mod row number comma 2 equal to zero so we're using this function of where mod row number comma 2 equal to Z right so when you divide the row number with two and if you get the reminder as zero then that particular RO number would be an even number and similarly if you get a reminder as one then that particular RO number would be an odd number that's how you can segregate the alternate records that is the even number records and the odd number records you just have to mention the condition mod row number comma 2 equal to zero if for even and mod row number comma 2 equal to 1 for odd now let's move forward to the next question that is name 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 you know select sta from students where student name 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 so when you use the underscore sign in the query like you know select star from student where student name like abcore 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 fetch 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 so when you use the substring statement you can use the statement like you know select substring student name one to five add student name from student the first five characters of student name will be retrieved from the student table now similarly when you use the write statement you can use the statement like you know select write student name comma five as student name 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 statement now let's move forward with the next question that is what is the main difference between SQL and pl/sql now SQL as I mentioned before is a query language that allows you to issue a single query or execute a single insert update delete whereas pl/sql that is basically Oracle 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 guys that is the main difference between SQL and procedural language SQL in SQL you just have to write a single query to execute insert update and delete and in procedural angled SQL you have to write full program with loops and variables to perform multiple operations like insert update delete and select 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 my screen I have two tables of you know 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 are 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 making complex queries 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 a stored procedure can be used as a modular programming which means create one store and call for several times whenever it is required this 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 guys that was about the advantages and disadvantages of store procedures now let's move forward with the next question that is list all types of the user defined functions so the 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 mainly 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 collation 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 width and Etc so that's what a collation 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 collan sensitivity so the different types of collan sensitivity are the case sensitivity the Kaa sensitivity the wet sensitivity and the accent sensitivity so Guys these are the mainly four types of sensitivities of collation so I hope that you've understood till now next in this 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 exist only inside the function these varibles 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 program so guys that was about the local and the global variables next let's move forward and understand what is autoincrement in SQL autoincrement 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 autoincrement keyword can be only used in Oracle whereas the identity keyword can be used in the SQL Server so that was about Auto increment in SQL 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 the online processing Warehouse data also has a subset of data called the data mask so data warehouse can be understood as a central repository of data where the data is assembled from multiple sources of information so guys that was about data warehouse now let's move 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 then go to programs and go to the Microsoft SQL server and click SQL Enterprise Manager to run SQL Enterprise Manager from Microsoft SQL Server program group now to change the authentication modes in the SQL Server you can follow the steps that I've mentioned on the screen that is you have to click on start go to programs go to Microsoft SQL server and click SQL 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 a stuff and replace function so talking about the stuff function first this function is basically used to overwrite existing characters or insert a string into another string so the Syntax for this function is basically stuff string expression start length and replacement 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 string are 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 string expression search string replacement string where every search string in the string expression will be replac with the replacement string I hope I'm clear with the stop function and the replace function so Guys these were the top 65 questions that could be asked in your interviews so guys if you've attended any interviews and you've been asked questions related to SQL please please do let us know in the comment section if you have any doubts related to them 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 that