hello all the society on behalf of Eddie Eureka and I welcome you to this session on DBMS interview questions so in the session guys we'll focus on the different questions that could be asked in your database administrator interviews so without any further due let's get started with today's session so the topics for today's session are as you can see on my screen I'll start the session by talking about the top general DBMS interview questions and then I'll discuss few sequel interview questions and sequel query interview questions so I hope the flow is clear to you all right now before I move on with the session I would like to request all of you to subscribe to our at Eureka YouTube channel to get daily notified on the top trending technologies and build your careers so on that toad let's get started with today's session now as all of us know that you know there is no place where data does not exist so in today's market where around 2.5 Quinten bytes of data gets generated every day it's very important for all of us to analyze the data and generate the required results but before that we have to hide the databases using the database management systems well knowing database management systems definitely opens the doors for database administrator and I believe you know this far so in this session we'll talk about the generic DBMS interview questions initially and then I'll talk about sequel which is the base to database management systems right so let's get started with the first section of the session that is generate DBMS interview questions so the first question that we have is what are the differences between DBMS and our DBMS now obviously all of us know that what is DBMS right it's an acronym for database management system so if you have to compare DBMS and our DBMS then I would say DBMS provides an organized way of managing retrieving and storing from a collection of logically related information but when it comes to our DBMS a DBMS provides the same way as that of DBMS but it provides with relational integrity so database management system in general is very common it's just the system which provides an organized way of managing and retrieving information now what happens in our DBMS is that the complete data present in the database is related to each other and it's mostly stored in the form of tables now I would also say that you know our DBMS is a type of DBMS or you know you can say it's a kind of DBMS so it basically you have a DBMS and then you have various kinds of it and our DBMS is one such kind of that so I hope this part is clear now let's move forward with the next question that is explain the terms database and DBMS and also mention the different types of TBMs now I'm sure everybody understands what database is right so in layman's terms if you have to explain database and I would say that you know you can consider database as a big container where all your data is in a logical format right so you can retrieve data from this particular database and then generate the required results now coming to DBMS if I have to define DBMS for you then it's a software application which interacts with the databases applications and users to capture and analyze the required data so the data stored in the database can be retrieved deleted and modified based on the clients requirement using the database management systems so you have a client and then you have a DBMS that is basically a software application through which you can access the database now the different types of database management systems are relational database management system hierarchical database management systems network database management systems and object oriented database management systems talking about relational database management systems this type of database management system uses a structure which allows the users to access data in relation to another piece of data in the database so in this type of database management system data is mostly stored in the form of tables coming to her are the database management system as the name suggests this type of database management system has a structure similar to that of a tree where in the nodes represent the records and the branches of the tree represent the fields in the health of one you have a tree structure and the relational one all the tables are related to each other coming to the network database management system this type of database management system supports many-to-many relations where in multiple member records can be linked and coming to the final type of DBMS that is object-oriented well this uses small individual software is called as objects to store pieces of data and the instructions for the actions to be done with that particular data right so if you have to understand all these terms in layman language guys and our DBMS is basically where all the data is deleted to each other well coming to alkyl here alkyl is in the form of a tree network comes where you know you have many to many relations where multiple member records can be linked together and object-oriented you have objects which have specific set of data and the instructions for actions to be done with that particular data so I hope this part is clear now let's move forward with the next question that is what are the advantages of database management systems now would say this is one of the most popular questions that is asked in the interviews that you know you have to know what is the advantage before you start using anything right so the advantages of database management systems are data independence sharing of data integrity constraints redundancy control and provides backup and recovery facilities talking about data independence this allows to change the structure of data without affecting the structure of any of the running application programs coming to sharing of data multiple users can use the data from the same database simultaneously coming to integrity constraints these constraints allow the data to be stored in database in a refined manner coming to the next one that is redundancy control it supports a mechanism to control the redundancy of data by integrating all the data into a single database and finally coming to providing backup and recovery facilities these provide a feature of backup and recovery to automatically create the data backup and restore the data as and when required right so that was about the advantages of DBMS caius let's move forward with the next question that is mentioned the different language is present in DBMS the different language is present in DBMS are mainly DD ldml d CL and PCL talk about DDL so DD l is basically data definition language which consists of commands which are basically used to define the database right so whenever you want to define the database that is basically do the groundwork at the foundation of your database you have to start using the DDL commands coming to DM l so DM l are basically data manipulation commands which consists of all those commands also used to manipulate the data present in the database so once your foundation is built you have to manipulate data right so that's where the MN commands come into the picture coming to d CL well these CL consists of command which deal with the user permissions and controls of the database system and finally coming to TCL wellthey CL is transaction control language which consists of commands which deal with the transaction of the database well d CL is data control language right so whenever you want to control the user permissions or grant the user the specific permission that's where d CL comes into the picture and whenever you want to deal with the transaction of the database that is when TCL comes into the picture so guys these are the main different languages present in DBMS that is TDL d ml d CL and this here now let's move forward with the next question that is what do you understand by query optimization well the answer to this particular question is released where the optimization is the face which identifies a plan for evaluating query that has the least estimated cost right so this face comes into the picture when there are large algorithms and methods to execute the same task so if you have to explain query optimization for anybody then it's really simple that you know it's a face which identifies a plan for evaluating query and make sure that you know it has the least estimated cost right now the advantages of query optimization are basically the output is provided faster a large number of queries can be executed in less time and it reduces time and space complexity now let's move forward with the next question that is to be considered null values the same as tired of the blank space or zero well I would say a null value is not at all same as that of zero of blank space that is because you know null value represents a value which is unavailable unknown unassign and not applicable whereas zero is a number and a blank space is a character so all these three terms are completely different guys zero is a number blank space is a character and now represents a value which is unavailable unknown unassigned or not applicable right what do you understand by aggregation and atomicity coming to aggregation first aggregation is a feature of the entity relationship model which allows the relationship set to participate in another relationship set coming to atomicity well this property states that a database modification must either follow all the rules or nothing at all right so even if one part of the transaction fails then the entire transaction fails so as these are two completely different concepts so if you want to know more about both of them you can refer to my session on my sequel tutorial where I've explained entity relationship diagram in depth and you can understand what is aggregation and what is diversity now let's move forward with the next question that is what are the different levels of abstraction in database management system well there are mainly three different levels of data abstraction that is physical level logical level and view level coming to physical level first well it is the lowest level of abstraction and describes how the data is stored coming to logical level the logical level is the next level of abstraction after the physical level right so this layer basically determines what data is stored in the database and what is the relationship between the data points coming to view level well view level is the highest level of abstraction and it describes only a part of the entire database there are mainly three different levels of data abstraction that is physical level logical level and view level now let's move forward with the next question that is what is an entity relationship model well if I have to define entity relationship model for you then it's a diagrammatic approach to database design where you represent real world objects as entities and mentioned relationships between them right so this approach basically helps the team of database administrators to understand the schema very easily so that was about llamando guys so now that you know what is ER model let's understand the next question that is what do you understand by the terms entity entity types and entity set in database management system coming to entity first well entity is a real world object having attributes which are nothing but the characteristics of that particular object right for example an employee can be entity and this particular entity can have attributes like employee ID employee name employee phone number and so on coming to entity type entity type is nothing but the collection of entity is having the same attributes right so generally an entity type refers to one or more related tables in a particular database so you can just understand entity type as a characteristic which uniquely identifies the entity for example an employee can have attributes such as employee ID employ name and Department right now this particular entity can be or entity type as you know a lot of entities can have the similar attributes coming to entity said well entity set is a collection of all the entities of a particular entity type in a database for example a set of employees or set of companies or a set of people can come under an entity set so guys this is what entity entity type an entity set means moving on to the next question that is what our relationships and mention the different types of relationships in database management system well I would say a relationship in database management system is a scenario where two entities are related to each other in such a scenario that the table consisting of the foreign key references to that of a primary key of the other table right so if you have two tables table a and T will be then the foreign key of table a will reference to that of the primary key of table P now the different types of relationships present in database management system are one-to-one relationship want too many relationship many too one relationship and self-referencing relationship coming to one to one relationship well this is used when a single ruined table is related to single row in table B coming to you one-to-many relationship this is used when the single draw in table is referred is related to many roads in table P coming to many to many relationship this is used when many rows in table a can be related to many other rows in table P and finally coming to self referencing relationship this is used when a record in table a is related to the same table itself right so that is basically the entity is related to itself so guys these are the four types of relationships now let's move forward with the next question that is what is concurrency control well concurrency control is a process of managing simultaneous operations in a database so that the database integrity is not compromised well there are mainly two approaches involved in the concurrency control that is optimistic approach and pessimistic approach the optimistic approach basically involves versioning and the pessimistic approach basically involves locking now let's move forward with the next question that is what our asset properties in database management system well I would say this is also one of the most frequently asked questions in your interviews so acid which stands for atomicity consistency isolation and durability is basically used to ensure that the data transactions are processed reliably in a database system so if I have to define otama city consistency isolation and durability for you and then in Thomas city refers to those transactions which are completely successful or are failed here each transaction the first was single logical operation of the data so even if one part of the 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 till your transaction never leaves the database without completing its state coming to the next term that its isolation the main goal of isolation is to make sure that you know concurrency control is met and finally coming to durability durability means that if a transaction has been committed it will lock up whatever may be the scenario like in any crash a failure or error and so on so these are the four terms guys atomicity consistency isolation and durability coming to the next question that is what is normalization and what are the different types normalisation what normalization is basically a process of organizing data to avoid any duplication of data and redundancy so there are mainly successive levels of normalization which are known as normal forms and each consecutive normal form depends on the previous one right now if I have to mention the different types of normalizations then there are mainly four types of normalization that is 1 and F 2 and F 3 and F and bcnf so to explain you all these types of normalization let me take an example right so as you can see on my screen I have a table which has basically salutation full name address moon is rented and category now you see that you know miss Alice Johnson has one particular address and has rented two movies of the same category coming to mr. David Allen David Allen's address is basically divided into two different section that is 0 third street 45 and 7th Avenue and then the movies rented are also divided into two sections that is through one one section for sci-fi and one section for action now obviously we see data duplication because you know David Alexander Qin twice it can come in the same row and also redundancy that you know actually the category values are written twice so to remove such kind of stuff you can use normalization initially any table starts with one and F right so in one and F basically each table cells should have a single value right so basically all the records who are made unique if I have to divide this particular table into its unique records then what will happen is I'll divide as you know a miss Alice Johnson staying in this particular address will then the movie Mission Impossible of category action similarly you will have a different record which says miss Alice Johnson staying in this particular address has rented when we clash of titans of category action right coming to mr. David Allen also David Allen thing in 3rd Street 45 will initially rent in stellar of sci-fi category and similarly mr. David Allen staying in 3rd Street 45 will again rent edge of tomorrow and then the category will be sci-fi similarly comes to the fifth record so will basically have five unique records as you can see on my screen now this is basically where your data is in 1nf but still you see a lot of redundancy of data right so for that we next coded 2nf well to make sure that you know the database is in second normalization form the database should be initially in one and F and should also have a single column primary key so to make sure that you know the database is in 2nf you have to make sure that you know database is in one and F and should have single column primary key so the previous table that I had chosen I'll basically divide into two different tables one will be able 1 which will basically have all the user details and table to will basically have details of the movies rented so after that I'll introduce a column known as ID in both the tables right so in both the tables what I'll do is I'll introduce ID for miss Alice Johnson to be ID 1 David Allen staying in 3rd Street fortify to be ID 2 and David Allen staying in the 7th Avenue will be ID 3 now these particular users will run specific movies right so basically I'll identify all those movies with respect to the IDS right so that's what I'm doing on my screen so if you see I basically have table 1 which basically has three IDs and all these three IDs are used in the table to write so in this way you have basically the single column primary key but yet if you see over here also there's a lot of redundancy of data as such you know you see in the second table that you know ID 1 is written twice right so to avoid that what we do is we normalize the database into 3nf to make sure that you know database is present in free and F you have to make sure that you know the database is initially present in 2nf and must not have any transitive functional dependencies so it basically should be in 2 and F and should not have any transitive functional dependencies to do that what I've done is I've created one more table which the salutation ID and salutation so that you know David Allen who was written twice is uniquely identified as one as a single person right so in the third table I have salutation ID and salutation and for mr. I've put one miss I've put two misses I've put 3 and dr. have put four so according to that I'll basically normalize this particular database now finally coming on to the fourth type of normalization that is bcnf or Boyce Codd normal form now this is basically used in a scenario where your database is already in the third normal form but there are few anomalies present right so when I say animal E is what I mean by that is you can have more than one candidate key well that is where BC and have comes into the role where you divide your tables further and then there would be only one candidate key press it so guys these are the various normal forms that is 1 and F 2 and F 3 and F and bcnf that you've understood right now let's move forward with the next question that is what are the different types of keys in database well there are mainly seven different types of keys in database that is scans eight key super key primary key unique key alternate key foreign key and composite key so to explain you the different types of keys I'm going to consider the example that you can see on my screen so starting with the candidate key first well candidate key is basically a set of attributes which can genetically identify a topic so basically each table can have more than one candidate key and apart from this out of all the candidate keys present one key can be chosen as the primary key so in the example that you can see on my screen since customer ID and pan number can uniquely identify every couple these two columns all you cannot say that you know these two attributes would be considered as a candidate key coming to the next type of key that is super key well super key is a set of attributes which can uniquely identify every couple so a candidate key a primary key and a unique key is super key but vice-versa isn't true coming to the next type of key that is primary key primary key is also a set of attributes which are used to uniquely identify every tupple now in the example that you can see on my screen since customer ID and pan number are candidate keys any one of them can be chosen as the primary key so I chosen customer ID as the primary key over here right coming to the next type of key that is unique key well uni key is similar to that of the primary key but allows null values in the column right so here the pan number can be considered as a unique key it might happen that you know few people do not have a pan number that particular attribute can be considered as the UD key coming to the next type of key that is alternate key well alternate keys are cannulated keys which are not chosen as primary keys right so for example if you see on my screen I had two candidate that was customer ID and pan number out of the Lord I chose customer ID as the primary key so the left of one is the pan number which is chosen as the alternate key coming to the next one that is foreign key well you can understand foreign key as an attribute that can only take the values present as the values of some of the attribute is basically the foreign key to attribute to which it refers this only example that you can see on my screen customer ID from the customers table is referred to the customer ID from the customer payment table the customer ID from the customers info table or do you know the customers table is basically the foreign key which I first said the primary key of the customer payment table that is the customer ID and finally coming to the last type of key that has a composite key a composite key is a combination of two or more columns that identify each tuple uniquely so in the example that you can see on my screen the customer ID and the date of payment can be grouped together to uniquely identify every tuple in the table so guys these were the different types of keys now let's move forward with the next question that is what do you understand by correlated subqueries in database management systems well a correlated sub-query is also sort of sub query reliant on any other query so when sub queries are executed for each of the rows of the outer query then they are termed as correlated subqueries now each sub query is basically executed a single time for every row of the outer query right so it's basically row by row processing so you can also understand correlated subqueries as those queries which are used for row by row processing by the parent statement right now here the parent statement can be select update or delete statements now let's move forward with the next question that is explained database partitioning and its importance now database partitioning is basically the process of dividing a logical database into independent units for betterment of availability performance and manageability now the importance of database partitioning is basically that it enables you to access large parts of specific partitioning it enables us to use cheap and slow storage which can be used to store data and finally improves the query performance now let's move forward with the next question that is what do you understand by functional dependency and transitive dependency in database management systems talking about functional dependency first a functional dependency is a constant which is used in describing the relationship among different attributes in a relation for example if you consider this relation a one having attributes x and y the functional dependency among these two attributes will be x implies two Y or you can understand that you know Y is functionally dependent on X coming to transitive dependency a transitive dependency is a constant which can only occur in relation of three or more attributes right so if you consider the same relation a one having tributes XY and said now X implies to Z is set to whole transitive dependency only if the functional dependencies X implies Y holds true why doesn't imply X holds true and Y implies two that holds true so that was about functional dependency and considerable dependency guys now let's move forward with the next question that is what is the difference between two tires and three tire architecture talking about the differences between two tire and free tire architecture where two tire is similar to that of a client-server architecture whereas the treat our architecture contains an extra layer between the client and the server so in the two tile architecture what happens is that clients directly communicate with the database at the server side but in the three tile architecture clients communicate with an application or a GUI on the server side that makes the system more secure and accessible right so this application they're after then communicates with the database system right now let's move forward with the next question and that is what are the differences between unique key and primary key so in the previous questions as I was telling you what are unique keys and primary keys I am sure you already know the answer to this particular question well unique keys where can basically store null values and primary keys cannot have a null value each table can have more than one unique key whereas when it comes to primary key each table can have only one primary key right so the main differences between unique key and primary key are you know unique key can have a null value and primary key cannot have a null value and each table can have more than one unique key whereas each table can have only one primary key that was about the differences guys now let's move forward with the next question that is what is a checkpoint in database management system and when does it occur now a checkpoint is a mechanism where all the previous locks are removed from the system and a permanently stored on the storage disk so checkpoints are those points from where the transaction log record can be used to recover all the committed data up to the point of the crash so that was about checkpoint guys now let's move forward with the next question that is mentioned the differences between triggers and stored procedures now this is also one of the most popular questions asked in your DBMS into big questions if after differentiate between triggers and stored procedures then triggers are basically a special kind of stored procedure that is not directly called by a user right so in fact a trigger is created and is programmed to fire when a specific event occurs coming to stored procedure stored procedures are a group of sequel statements which can be reused again and again and again right so basically these statements are created and stored at the database now coming to the next difference as I previously mentioned a clicker cannot be called or executed directly by a user right so it's basically when the corresponding events occur then triggers get created or are executed but coming to stored procedures we can execute a stored procedure by using the execute command or exe C command whenever we want the next difference is we cannot schedule a trigger but whereas it comes to stored procedures we can shade you in a job to execute the stored procedure on a predefined time coming to the next one that is that you know we cannot directly call another trigger within a trigger but when it comes to stored procedure we can call a stored procedure from another stored procedure the next difference between triggers and stored procedures is that enough parameters cannot be passed as an input and triggers whereas parameters can be passed as an input and stored procedures coming to the next difference that is triggers cannot return values whereas in stored procedures triggers can return zero or n values and now finally coming to the last difference that is you know transactions are not allowed within a trigger when it comes to stored procedures you can use transactions within the stored procedures so guys these are the basic differences between triggers and stored procedures now let's move forward with the next question that is what are the differences between hash join merge-join and nested loops now hash join is basically used when you have to join large tables more joints are used when projections of the joint tables are sorted on the joint columns and nested groups are completely out of joints and basically consists of an outer loop and an inner loop basically executing few tasks based on the condition so guys these are the main differences between hash join merge join and nested loops coming to the next question that is what do you understand by proactive retroactive and simultaneous update while talking about proactive update first these updates are applied to the database before it becomes effective in the real world environment coming to retroactive updates these updates are basically applied to a database after it becomes effective in the environment and coming to simultaneous update these updates are applied to the database at the same instance of time as it becomes effective in the real-world environment right so guys this is what the terms mean the proactive update retroactive update and simultaneous update the proactive updates are basically applied before it becomes effective in real-world environment the retroactive updates are updated on the databases after it becomes effective in the real world environment and simultaneous updates are applied on the database at the same instants of time as it becomes effective in the real-world environment right now let's move forward with the next question that is water in Texas and more of the differences between clustered and non-clustered index now you have to define index for you then indexes or index are basically data structures which are responsible for improving the speed of data retrieval operations on a specific table so this data structure uses more storage to maintain extra copies of data by using additional writes so indexes are mainly used for searching algorithms where you wish to retrieve data in a quick manner now coming to the differences between clustered and non-clustered index a clustered index is comparatively faster than a non-clustered index the second difference is that into the clustered index basically alters the wave records are stored in a database as it sorts out the rows by column which is said to be a clustered index right coming to the non-clustered index this does not alter the way the data is basically stored but it creates a separate object within a table which points back to the original table rows after searching and finally coming to the third difference that is one table can have only one clustered index but a non-clustered index scenario one table can have many non-clustered indexes so guys these are the basic differences between clustered index and non-clustered index now let's move forward with the next question that is what do you understand by intention and extension intention or most commonly known as database schema basically defines the description of the database right so this is basically specified during the database design and mostly remains unchanged coming to extension extension is the number of tuples available in the database at any instance of time right so this value keeps changing as and when tuples are created updated or deleted so the data present in database at a specific instance of time is basically known as the extension of the database or most commonly known as the snapshot of the database and the description of the database is basically known as intention or most commonly known as database schema now let's move forward with the next question that is what do you understand by cursor and what are the different types of it now a cursor is basically a database object which helps in manipulating data row by row and represents a result set so the types of cursor available our emphasis cursor and explicit cursor now implicit cursor is basically a type of cursor which is declared automatically as soon as the execution of sequel takes place so here the user is basically not indicated about the declaration of the cursor coming to explicit cursor now this type of cursor is basically defined by the PL sequel as it handles a query in more than a single row right so that was about cursor and it's different type guys now let's move forward with the next question that is explained the term specialization and generalization now specialization is basically a process of defining a set of subclasses of the entity type right so here each subclass will contain all the attributes and relationships of the parent entity now apart from this the subclasses may contain additional attributes and relationship specific to itself moving on to generalization generalization is a process of finding relations common attributes for a particular set of entities and finally defining a common superclass with them so that is what specialization and generalization mean guys now let's move forward with the next question that is what do you understand by data independence well when you say an application has data independence what it implies is that you know that the application is independent of the storage structure and the data access strategies of data right now let's move forward with the next question that is what are the different integrity rules present in database management systems well there are mainly two different integrity rules present in DBMS that is entity integrity and referential integrity talk about entity integrity first this rule states that the value of the primary key can never be null right so all the tuples in the column are identified as the primary key and should have a value coming to referential integrity this rule states that you know either the value of the foreign key is null or it should be the primary key of any other relation right so guys these are the basic different integrity rules present in database management system that is entity integrity and differential integrity now let's move forward with the next question that is what does fill factor concept mean with respect to indexes well fill factor concept is basically used to mention the percentage of space left on an every leaf level page which is packed with data so usually the default value is 100 right so if you have to understand fill factor concept guys is basically used to mention the percentage of space left on every leaf level page right so the default value is generally 100 now let's move forward with the next question that is what is index hunting and how does it help in improving query performance now index hunting is basically a process of boosting a collection of indexes right now this is basically done as indexes improve the query performance and the speed at which they're processed now it helps in improving query performance by suggesting the best queries using the query optimizer not only this but index query distribution and the performance I use as metrics to check the effect and finally databases are tuned into small collection of problem queries right so that was about index hunting and how it improves the query performance guys let's move forward with the next question that is what are the differences between Network and hierarchical database model well network database model is basically where each where in node can have multiple children nodes and vice versa and coming to hierarchical database model it's basically a top-down structure where each falen node can have multiple child nodes right but a single child node can have only a single parent node the main difference is that you know a network model each parent node can have multiple child nodes and vice versa whereas in hierarchical it's basically a top-down structure so basically each parent can have many child nodes but a child node can have only a single parent node coming to the next difference that is the network data base model supports one-to-one relationships one-to-many and many-to-many relationships but coming to a hierarchical database model hierarchical only supports one to one relationship and one to many relationship now let's move forward with the next question that is what is deadlock and how can it be resolved now deadlock is basically a situation which occurs when two transactions wait on a resource which is locked or either holls so deadlocks can be basically prevented by making all the transactions acquire all the locks at the same instance of time right so once the deadlock occurs the only way to cure is to abort one of the transactions and remove the partially completed one now let's move forward with the final question for this particular section that is what are the differences between exclusive lock and the shared lock now if I have to tell you the differences between exclusive lock and share lock exclusive lock is basically a lock on a data item when a transaction is about to perform the right operation but when it comes to shared lock a shared lock allows more than one transaction to read the data items right so that is the main difference guys exclusive lock is basically a lock when a transaction is about to perform the right operation and the shared lock is basically a type of lock which allows more than one transaction to leave the data items so guys with this question we come to an end to this particular section of this tutorial now let's move forward with the next section that is sequel interview questions so as I was telling you previously also sequel is one of the foundations of database management system so it's very important to know what are the top questions that could be asked related to sequel right so starting with the first question that is what are the differences between drop truncate and delete commands now this is one of the most popular questions or I would say one of the most frequently asked questions as you know all these three terms almost do the same thing so the drop command is basically used to delete a database table or a view the truncate command is basically used to delete all rows from a table and a delete command is basically used to delete a row from the table so the drop is basically used to drop the complete table database out of you truncate is to delete all the rows from the table and the delete command is basically used to delete one row from the table down with the crop command data cannot be rolled back even with the truncate command the data cannot be rolled back but when it comes to delete command data can be definitely rolled back now the drop and the truncate commands are DDL commands or data definition language commands and the delete command is basically a DML command that is the data manipulation language command the drop command is basically slower than truncate the crunket command is faster that drop and delete and the delete command is slower than front key right so if after to make it short than the drop and the delete commands are basically slower than truncate the drop command deletes the full structure of the table the truncate command preserves the structure of the table and the delete command also deletes the structure of the row from the table so if I have to talk about drop truncate and delete individually guys then the drop command is basically used to delete a database table all of you it can all the data cannot be rolled back with the drop command and it's a GDL command it's comparatively slower than truncate and deletes the full structure of the table coming to truncate command truncate command is basically used to delete all the rows from the table and data cannot be rolled back by using the truncate command it's also a DDL command and is faster than the drop and the delete commands not only this but it also preserves the structure of the table coming to the delete command well the delete command is basically used to delete a row in the table and data can be rolled back using this particular command it's a DML command and is slower than truncate and basically deletes the structure of the row from the table that were the differences between drop crunket and delete command guys now let's move forward with the next question that is what is sequel and where is it use the sequel or most commonly known as structured query language is the core of relational database which is used for accessing and managing the databases now this language is basically used to manipulate and retrieve data from a structured data format in the form of tables and holds relationships between those tables so in laymen terms you can use sequel to communicate with the database right so that was about what is sequel guys next let's move forward with the next question that is what do you understand by sub queries in sequel a sub query is basically a query inside another query where a query is defined to retrieve data or information back from the database right so in a sub query the outer query is called the main query whereas the inner query is called the sub query now sub queries are always executed first and the result of the sub query is passed on to the main query which then executes the main query that is the outer query so it can be basically nested inside a select command update command or any other query not only this but a sub query can also use any comparison operators like you know greater than less than equal to and so on now this was about sub-queries in sequel guys now let's move forward with the next question that is mentioned the differences between Union and union all now Union basically combines the result of two or more select statements consisting of distinct value but when it comes to Union all Union all combines the result set of two or more select statements consisting of the duplicate values so the syntax of Union is Union itself that is only the word Union and the syntax of Union all is Union all itself that is the words Union all now Union also has lower performance than unit all as duplicate rules need to be removed and coming to Union all Union all has a better performance than Union as duplicate rows need not have to be removed so guys these were the basic differences between Union and union all now let's move forward with the next question that is what do you understand by Clause and sequel now cross and sequel is basically used to limit the result set by mentioning a condition to the query so you can use a clause to filter the rows from the entire set of records right now if I have to give an example then example are basically where and having Clause now let's move forward to the next question that is mentioned the differences between having and where clause so having Clause can be only used with the Select statement whereas the where clause can be used in a group by clause thus having statement is used with a guru by function in a query whereas the where is basically applied to each row before they're a part of the group by function in a query right so guys these are the main differences between having in bed having is only used with a select statement and where is used in a group by clause having can be used with a group by function in a query whereas the where is applied to each row before they're a part of the group by function in a query apart from this one more thing that I would like to tell you over here is that you know whenever group I is not used having behaves like the where Clause right so guys that was about having an where cross now let's move forward with the next question that is how can you perform pattern matching in sequel well to perform pattern matching in sequel you have to use the like operator right so with the like operator you can basically use the percentage symbol and the underscore symbol so the percentage symbol is based basically used to match 0 or more characters and the underscore symbol is to match exactly one character so if you see the examples on my screen I've written a query that you know select star from customers with customer name like s percentage right so all the customers names starting with s and whatever they have like you know let's say you knows my name is aji T right so I have six characters maybe there's Sonia she has five characters right so all those names will be collected ashore coming to the next query that is Selleck star from customers where customer name like X Y Z underscore this basically matches only one character so all those values which have XYZ in the starting and have only one character after that will be taken into account now let's move forward with the next question that is mentioned few case manipulation functions in sequel now there are mainly three case manipulation functions in sequel that is lower upper and in each cap now talking about lower function first this function basically returns the string in lower case right so it takes a string as an argument and returns it by converting into lower case now the syntax is basically lower and in brackets you have to mention in quotes the string right now coming to upper function this function is basically used to return the string in upper case so it takes a string as an argument and returns it by converting into upper case right so the syntax is same again you have to mention upper and in brackets you have to mention string I'm coming to in each cap in it cap is basically used to return the string with the first letter in uppercase and all the other letters in lowercase so the syntax is again same it's say you just mention the keyword in it cap and in brackets you have to mention the string right now these were few case manipulation functions in sequel guys let's move forward with the next question that is water joints in sequel and what are the different types of joints well a joint class is basically used to combine rows from two or more tables based on a related column between them so it's basically used to merge two or more tables or retrieve data from there right so there are mainly four types of joints the inner join the right joined the left joint and the full join so talk about inner join first the inner join is basically used to return those records which have matching values in both the tables right coming to right joint this is basically used to return all the records from the right table and also those records would satisfy a condition from the left table and coming to the left join the left join is similarly used to return all the records from the left table and also those records would satisfy a condition from the right table and finally coming to full joint full join is basically used to return all those records which either have a matching value in the left table or the right table so if you want to detail and the standing of the different types of joints in sequence you can refer to my session on sequel joints now let's move forward with the final question for this particular section that is what do you understand by a view how to create a view update of you and drop of you now view in sequel is basically a single table which is derived from the other tables right so view contains of rows and columns similar to that of a real table and has fields from one or more tables so to create the view you basically have to use the syntax that you can see on my screen so you have to mention create view and then you have to mention view name that is basically whatever you want to mention and then use the as keyword and after that you have mentioned select and mention all the columns that you wish to choose from table names where condition right if you want to mention the condition you mentioned the condition now to update a view you have to use a syntax that you know create view or replace view and then mention the view name as and then again mentioned select and mention all the columns from where you want to select from table name where condition right and similarly coming to drop view well the syntax to drop view is very simple you just have to use the keyword drop view and then mention the view name so guys that was about view on how to create drop and update of view now let's move forward with the next section that is sequel query interview questions now obviously since sequel is a language to which you can Thea manipulate data it's very important for all of us to understand how to write queries to retrieve the data right so for that let's look at the top questions as in this particular section but before that if you want a detailed understanding on the top sequel questions aisles and interviews you can refer to my session on sequel interview questions all right starting with the first question write a query to create a duplicate table with and without data present now consider you have a table named customers having details like customer ID customer name and so on now if you have to create a duplicate table let's say duplicate customer with the date of present in it you have to mention the query as you can see on my screen that is create table and mention the table name that is duplicate customer as select star from custom right this will basically create a duplicate table of the real table with all the data present similarly if you want to create a duplicate table without the data present you have to mention the query again that you can see on my screen let's create table duplicate customer as select star from customers where 1 equal to 2 so basically the data will not be present in the duplicate table in such a scenario by using this query now let's move forward with the next question that is mentioned a query to calculate the even and the odd records from a table now to write a query to calculate the even and the odd records from a table you can write two different queries by using the mod functions right so if you want to retrieve the even record from the table first you have to basically try the query that you can see on my screen so I am monkey in considering the customers table right so I'll mention select customer ID from and the sub query I'll mention select row number comma customer ID from customers where Maud and then in the brackets I'll mention row number comma 2 equal to 0 right so I'm sure all of us know how mod function works right so whenever we divide let's say 5 divided by 2 we get a reminder 1 that means that you know it's an odd number but if you divide 6 divided by 2 and then we get a reminder 0 that means it's an even function right so that's the same logic that works over here also now similarly if you want to retrieve the odd records from a table you mentioned Stella customer ID from and in the sub query you mentioned select row number comma customer ID from customers where mod row number comma 2 equal to 1 so that's how guys you can retrieve the even records and the alter cards so now let's move forward with the next question that is write a query to remove duplicate rows from a table now to remove duplicate rows from a table you have to initially set the duplicate rows from the table without using the distinct keyword right so to select the duplicate rows from the table you can write a query as you can see on my screen that is select customer number from customers where row ID and in the nested query you mentioned select max row ID from customers C where customer equal to C dot customer number right and now once all the duplicate records are selected to delete the duplicate records from the customer table you have to mention the query that you can see on my screen there is delete from customers where row ID and in sub query you mentioned select max row why do you from customer see where customer number equal to C dot customer number so that's how guys you can remove to get rows from a table now let's move forward with the next question that is mentioned a query to add email validation to your database when I would say to answer to any particular query is not specifically unique as you may have various approaches to solve the problem right so to add email validation to your database I would say that you know you can use the query that you can see on my screen that is select emails from customers were not regular expression underscore like and in the brackets you mention email comma and then you'll mention all the characters that he has to match right so I'll mention a to said characters and then zero to nine characters dot underscore percentage plus plus minus and so on right so this is one way you can add email validation to your database but this is not the only V now coming to the last question for this particular session that is write a query to retrieve the last day of the next month in Oracle so the query to retrieve the last day of the next month in Oracle you can write a query as you can see here on my screen select last day and in brackets you mentioned add month and in inner brackets again you mentioned system date comma one from do it right so guys these were a few queries that you can practice for your DBMS interview questions so apart from that guys with this we come to an end to this particular session I hope you know you've understood all the questions and if you yourself have attended any interviews related to DBMS and sequel please comment your question in the comment section and will reply to you as soon as possible so this will be all from our site today I hope this set of questions will help you crack your interviews so I hope you found the session informative thank you and have a great day you