SQL Interview Questions and Answers for Freshers
SQL Interview Questions and Answers For Freshers 2021
Frequently Asked SQL Interview Questions and Answers for Freshers .
If you are CS/IT student then you know how important DBMS subject is to crack the Interview. Here below you will find SQL Interview Questions and Answers that will help you in preparing for Interviews.
These SQL Questions and Answers are asked in many Companies. This will help you in clearing the concepts of DBMS.
Click on the button mentioned below to know more about SQL in detailed.
What is SQL
SQL is an abbreviation for Structured Query Language. SQL is a standard query language that is used for creating a relational database and performs a number of operations and manipulation of the data. SQL was originated in 1970. SQL is a Database language that includes functionalities like database creation, deletion, fetching rows and modifying rows, etc. Many times it is pronounced as ‘sequel.’
Functions of SQL:-
- SQL can execute queries against a database.
- SQL can retrieve data from a database.
- SQL can insert records in a database.
- SQL can update records in a database.
- SQL can delete records from a database.
- SQL can create new databases.
- SQL can create new tables in a database.
- SQL can create stored procedures in a database
- SQL can create views in a database.
- SQL can set permissions on tables, procedures, and views.
SQL Questions and Answers for Freshers
1. What is DBMS?
Solution:- DBMS stands for Database Management System. DBMS is a system that carries out operations like creation, maintenance, and use of databases. DBMS can be coined as a File Manager that manages data in a database rather than saving it in file systems.
3. What are tables and Fields?
Solution:- Data organized in a model with Rows and Columns is called a table. Rows are horizontal and Columns can be categorized as vertical. A table has a specified number of columns called fields but can have an infinite number of rows which is called a record.
4. What are the types of join and explain each?
Solution:- There are 4 types of Join:-
- Inner Join: Inner join returns rows when there is at least one match of rows between the tables.
- Right Join Right join return rows which are common between the tables and all rows of the Right-hand side table. Simply, it returns all the rows from the right-hand side table even though there are no matches in the left-hand side table.
- Left Join: Left join return rows which are common between the tables and all rows of the Left-hand side table. Simply, it returns all the rows from the Left-hand side table even though there are no matches in the Right-hand side table.
- Full Join: Full join return rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
5. What is Foreign Key?
Solution:-
- FOREIGN KEY: is a key used for linking 2 tables.
- A FOREIGN KEY is a field or maybe a collection of fields in a table that corresponds to the PRIMARY KEY of another table.
- The table containing the FOREIGN KEY is called the CHILD TABLE and the table containing the CANDIDATE KEY is called the PARENT TABLE.
6. What is the difference between DELETE and TRUNCATE statements?
Solution:-
DELETE
- The DELETE command helps to delete a row of a table.
- The rollback of data is possible after using the delete statement.
- It is a DML command.
- It is slower than a TRUNCATE statement.
TRUNCATE
- The TRUNCATE command helps to delete a row of a table.
- Rollback of data is not possible.
- It is a DDL command.
- As compared to DELETE the TRUNCATE command is faster.
7. What is Data Integrity?
Solution:-The assurance of consistency and accuracy of the data over the entire life cycle is Data Integrity. It is a critical aspect of the implementation, design, and usage of any system that processes, stores or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.
8.What is the SELECT statement?
Solution:-SELECT is a SQL command that is used for selecting data from a database. The data which is returned is saved in a result table, called result-set.
9. What is normalization?
Solution:- Normalization is a way of organizing fields and tables of the database in a way that reduces redundancy and dependency. The main objective is to create a single table where the operations like add, delete or modify can be performed on the field.
10. What is the ACID property in a database?
Solution:- To ensure that the data transactions are processed reliably in a database system we use the ACID property.
- Atomicity: It states that each transaction is all or nothing. It states that the entire transaction fails if one part of the transaction fails and the database state is left unchanged.
- Consistency: It ensures that the data must follow all validation rules. According to this a transaction never leaves your database without its state being completed.
- Isolation: The main goal of providing isolation is concurrency control. This property ensures that the concurrent property of execution should not be met.
- Durability: this property states that once a transaction has been committed, it remains committed, whatever the situation be, even power loss, crashes, or errors.
11. Explain different types of indexes.
Solution:- There are three major types of index:
Unique Index: It does not allow the field to possess redundant values if the column is indexed uniquely. A unique index can be applied automatically if a primary key is defined.
Clustered Index: It rearranges the order of the table and searches on the basis of key values. Every table has only one clustered index.
Non-Clustered Index: It causes no change in the order of the table and keeps a logical order of the data intact. There can be many nonclustered indexes for a table.
12. What do you mean by “Trigger” in SQL?
Solution:- Trigger is a special type of stored procedure that executes automatically in place or after data changes. It allows executing a batch of code when an insert, update, or any other query is executed against a specific table.
13. What is a constraint?
Solution:- Constraints are the limitations on the data type of a given table. A constraint can be specified while creating or altering the table statement. Sample of constraints are.
- NOT NULL.
- CHECK.
- DEFAULT.
- UNIQUE.
- PRIMARY KEY.
- FOREIGN KEY.
14. What are the set operators in SQL?
Solution:- SQL supports few Set operations which can be performed on the table data. These are used to get meaningful results from data stored in the table, under different special conditions.
The set operators in SQL are :
- UNION
- UNION ALL
- INTERSECT
- MINUS
15. What is the usage of the DISTINCT keyword?
Solution:- The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.
16. What is SQL?
Solution:- SQL stands for Structured Query Language, and it is used to communicate with the Database. SQL is a standard language for accessing and manipulating databases.
17. What is a primary key?
Solution:- A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. This is a special kind of unique key, and it has an implicit, NOT NULL constraint. It means Primary key values cannot be NULL. A relational database must always have one and only one primary key.
18. What is a unique key?
Solution:- A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns. A unique key is a set of one or more than one field/columns of a table that uniquely identify a record in a database table. You can say that it is a little like a primary key but it can accept only one null value and it cannot have duplicate values.
19. What are all the different normalizations?
Solution:- The normal forms can be divided into 4 forms, and they are explained below -.
- First Normal Form (1NF): According to this remove all the redundant columns from a table. Creation of tables for the related data and identification of unique columns.
- Second Normal Form (2NF): Should follow all requirements of the first normal form. Arranging the data subsets in separate tables and Creation of relationships between the tables using a primary key.
- Third Normal Form (3NF): Should follow all requirements of the 2NF. Removing the columns which are not dependent on primary key constraints.
- BCNF (Boyce-Codd Normal Form): Meeting all the requirements of the third normal form and it should not have multi-valued dependencies.
20. What is a relationship and what are they?
Solution:- Relationship in DBMS is the interconnection of the tables within the database. There are various relationships, and they are as follows:
- One to One Relationship.
- One to Many Relationship.
- Many to One Relationship.
- Self-Referencing Relationship.
21. What is a query?
Solution:- A query is really a request for data. A DB query is a code written in order to get the information back from the database. You ask the database for something and it answers in the best way it knows with data as a result of a query.
22. What is subquery and explain its types?
Solution:- A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.
There are two types of subquery :
- A correlated sub-query cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.
- A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.
23. What is a stored procedure?
Solution:- A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. Stored Procedure is a function that consists of many SQL statements to access the database system.
24. Advantages and Disadvantages of Stored Procedure?
Solution:- Stored procedure supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.
The disadvantage is that it can be executed only in the Database and uses more memory for storage.
25. What is Self-Join?
Solution:- A self-join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. This is used to compare values in a column with other values in the same column in the same table. ALIAS ES can be used for the same table comparison.
Login/Signup to comment