hey guys welcome to analytics Vidya SQL or structured query language is one of the most well-known and longest running database languages and with the help of SQL queries you can perform tasks such as creating databases managing them updating data or even retrieve data from the databases according to our leading job portal if you are looking for a role in it data Tech data analysis business intelligence or even mobile or web development roles SQL is a must have skill and in this video we'll cover our top 10 conceptual questions that are often asked in SQL interviews this is part of our ongoing interview Series where we will cover more such topics so stay tuned for that we have already covered python so I'll leave a link to that particular video in the description part and don't forget to subscribe to our YouTube channel for more such interesting data Tech content on this note let's get started all right let's look at our first question what do you mean by dbms and what are the different types of dbms well dbms stands for database management system the primary responsibility of a dbms is to store create update and manage the databases in simple words dbms is a software that manages or organizes the data in a database imagine dbms is like a digital filing cabinet for storing and organizing information and you have a lot of data like customer names product details or sales records now instead of keeping all of that data in scattered files or papers a dbms helps you store and manage that data in a structured way on a computer moreover this database is easily accessible to developers and also maintains an interface between the database and the end user of the application or the product this means a dbms is like a bridge that connects two important groups of people in the computer World developers and end users of applications now let's look at the types of dbms we have first we have a relational database let's have a look at what a relational database is relational database or rdbms as we call it is a system where data is organized in a two-dimensional tables using rows and columns okay now the next type of database is object oriented database it is a system where information or data is represented in the form of objects which is used in the object oriented programming the next database in our list is hierarchical database it is a system where the data elements have a one-to-many relationship the last database we have is the network database a network database management system is a system where the data elements maintain one-to-many relationships or many to many relationships so this is the basic idea about dbms now let's move on to our next question all right our second question is what is the difference between dbms and rdbms we have talked about both dbms and rdbms in our previous question now let's talk about the differences so the first key difference is that a dbms stores the data in the format of files whereas rdpms stores the data in the format of rows and columns like that in our table secondly dbms is designed to handle a small amount of data whereas rdbms is designed to handle large amounts of data thirdly within dbms individual access to data elements is possible whereas in rdbms multiple data elements are accessible together next dbms doesn't support multiple users whereas rdbms does support multiple users then a distributed database is not supported within dbms whereas in rdbms a distributed databases in fact supported and lastly in dbms normalization is achievable whereas in rdbms normalization is not achievable now to further Deep dive on this last point normalization is the process of minimizing redundancy from a relation or data so these are basically the differences between rdbms and dbms hope you guys are familiar with them now let's check out our next question at this point all right our next question is explain joins in SQL now this is one of the most common questions asked in the interviews join Clause is used to combine rows from one or more than one table based on a common column from both tables the two tables are merged and we then retrieve new data from that imagine you have two separate lists of information one with the customer information and another with order details we want to see a list of customers along with the orders they have placed for this we use something called as inner join which combines data from both tables based on a special code which is customer ID over here that they have in common this way we connect each customer to their orders so the result we get is a neat table that shows the names of customers and the orders they made now let's have a look at the types of joins in SQL so there are four types of joins in SQL inner join full join left join and right join most common type of join is the inner join inner join returns all the rows from multiple tables when the condition is satisfied this is the syntax for inner join now let's look at the left join in left join of SQL only rows from the left table are returned and the union of left and the right table where the condition is satisfied this is the Syntax for the left join on the other hand in right join of SQL all the rows from the right table are returned but only the matching rows from the left table where the join condition is fulfilled this is the syntax of right join finally we have full join in full join of SQL all the records are returned when there is a match in any of the tables therefore it returns all the rows from the left hand side of the table and all the rows from the right hand side of the table this is the syntax of full join and here is a pictorial representation of all the joint closes screenshot this this will help you I hope you guys are pretty clear about joins in SQL by now let's move on to the next question all right the next question is explain ETL in SQL extract transform and load is referred to as ETL in SQL in simple words it is a process used to extract data from different available sources and transform the data depending on our needs now let's have a closer look at what each function means think of extract as scooping up data from different places like getting ingredients from various stores to cook a meal then comes transform imagine this step as sorting and chopping vegetables marinating raw material and doing all the necessary preparation before cooking and finally comes load think of it as serving the prepared meal onto plates so that people can enjoy it clear guys now ETL could involve several processes the processes are filtering sorting joining aggregating cleaning analyzing data Etc and finally loading the data is the Final Destination location cool let's move on to the next question the next question is what are constraints in SQL this is another very commonly asked question in interviews constraints in SQL are basically used to specify the limit or to clarify rules on the data types of the table in simple words think of constraints Like rules or restrictions that you can apply to your database tables this is to make sure that data stored in them is accurate reliable and consistent remember that if there is any violation between the constraints and the data action the action is aborted now constraints can be specified while creating or altering the table statement and this could be done basically in two ways now let's look at the different types of constraints that we have first up is note null what does it do it ensures that a column must always have a value and cannot be left empty or null next is unique it guarantees that values in a specific column are distinct this means there are no duplicates primary key is a combination of node null and unique it ensures that each row in a table is uniquely identifiable the next is foreign key it basically prevents actions that would destroy links between tables it basically creates a relationship between two tables by ensuring that value in one tables column match value in another tables column check constraints check constraint ensures that the value in a column satisfies specific condition it basically makes the rules that data in a column must follow finally we have default constraints so default sets the default value for a column if no value is specified this is all about constraints if you guys have any doubts so far feel free to post them in the comments will clear them off for you now let's move on to our next question next question is what do you understand about denormalization first let's understand what normalization is normalization is a process to minimize data redundancy by organizing fields and tables of a database what do I mean by this normalization in SQL is like organizing your kitchen you keep things in separate drawers and cabinets for example all the plates are in one place all the cups in another and so on this makes it easier to find things and avoid redundancy now let's see what denormalization is denormalization basically refers to a database optimization technique in which we add redundant data to one or more tables it means sometimes we put data together in a way that's not super neat and organized but it helps us find things faster basically the process of taking data that is normalized and making it known normalized is called denormalization however note that denormalization does not mean reversing normalization on or not to normalize remember guys it is an optimal technique that is applied after normalization got it now let's move on to the next question the next most common question asked in interviews are about the different subsets of SQL let's first understand what they do SQL subsets are like different sets of tools you use for different tasks in a database such as managing data designing the structure Etc now there are four uh subsets ddl or data definition language DML which stands for data manipulation language DCL which is data control language and finally TCL which is transaction control language uh first let's see what ddl or data definition language does ddl focuses on the structure of the data itself like creating modifying and deleting tables DML or data manipulation language is all about manipulating retrieving and managing data in our database next is DCL or data control language this subset is about managing who can access and change the data in the database and finally we have the transaction control language this subset deals with transactions which are sequences of SQL commands that are treated as a single unit of work this is all about subsets let's move on to the next question our eighth question is what is a view and what is the difference between views and tables first we will look into the definition of table and then views a table basically refers to our collection of data this data is organized in form of rows and columns if you have to explain to anybody what a table is basically in SQL you have a database and a database consists of n number of tables now let's focus on what a view is view is a virtual table that has the subset or a part of the data contained in a SQL table now it does not store any data of its own view basically represents a stored query that can be used to retrieve data from one or more tables here is a detailed division between View and table you can take a screenshot of this for your future reference or better understanding so this is all we had for views and tables in SQL now let's move on to our next question so this question is about what are the uses of SQL functions now first understand what SQL functions are SQL functions are methods used to perform data operations and you know functions are an essential part of SQL because they allow you to perform various operations on data and make queries more powerful and flexible they can do various operations let's have a look at them if you want to perform some calculations on the data and convert the data type you use SQL function you can also use SQL function to modify individual data items and format dates and numbers functions also allow you to manipulate and work with the text data so you can also manipulate the string output now let's move on to the next question the last question is about asset property in SQL first let's see what asset stands for as it is an acronym that stands for four essential properties in context of databases in SQL a stands for atomicity C4 consistency I4 isolation and d stands for durability these properties ensure that database transactions or a series of one or more database operations are reliable it also aims that maintaining the Integrity of the data now let's break down acid in simple terms a stands for atomicity think of atomicity as an All or Nothing concept so any operation performed on the data should be executed either entirely or not executed at all partial execution is not possible in SQL transactions in simple words there's no in between State C stands for consistency consistency means that a transaction brings the database from one consistent state to another thereafter I stands for isolation isolation is about making sure that one transaction doesn't interfere with another transactions run independently and doesn't see each other's intermediate results and finally comes the which is durability durability guarantees that once a transaction is completed successfully its effects are permanent even in the face of system crashes or failures I hope this clears what asset property is so guys that's all we had for you in this particular SQL interview questions video if you have any further conceptual questions regarding SQL do uh type that in the comment section below and we'll get back to you with an answer for more such data Tech content do subscribe to our Channel I'll see you in the next video bye