SQL Interview Questions
Several jobs require candidates to have a profound knowledge of SQL. These SQL Interview Questions have been designed specially to get you acquainted with the nature of questions that you may encounter during your interview for the subject of SQL.
1. What is SQL?
SQL stands for Structured Query Language. It is a query language and used for performing operations on the database such as insert new records, update records, delete records and view records etc. SQL queries are performed on database like Oracle database, MySQL database, MongoDB database and SQL Server database etc. All database management system uses SQL as standard database language.
2. What are the usages of SQL?
SQL is used in almost every relational database management system. It is used for following features:
- To access data from relational database management systems(RDMS).
- To describe the data.
- To execute queries against the database.
- To define data in a database and manipulate it when needed.
- To create and drop databases and tables.
- To create a view, stored procedure and functions in a database.
- To provide permissions on tables, views and procedures.
3. What are tables and fields in SQL?
A table is a collection of data in an organized manner. It has rows and columns. Columns can be categorized as vertical, and rows are horizontal. A table contains specified number of column called fields but can have any number of rows which is called record. For example:
Field: Employee_ID, Name, Address, Department
4. What is a primary key?
A primary key is a field or combination of fields which ensures that all values are UNIQUE and NOT NULL. A table can have only one primary key constraint. For example - Social Security Number (SSN) can be treated as primary key for an individual.
5. What is a foreign key?
A foreign key is a field or combination of fields in one table that refers to the primary key in another table. It is used to link two tables together. The table containing the foreign key is known as the child table. The table which is referenced is called the referenced or parent table.
The foreign key constraint helps to preserve links between two tables. It prevents invalid data from being inserted in the foreign key column. The value which can be inserted into the foreign key column must be the values contained in the referenced table.
6. What is a unique key?
A Unique key is used to ensure that all values in a column of a table are different (unique). A Unique key field can not accept duplicate values and can accept NULL value only once.
7. What is the difference between primary key and unique key?
A Unique key and Primary key are two essential constraints in SQL with little difference. A primary key carries unique value but it can not be NULL whereas unique key carries unique value and it can have single NULL value. Additionally, a table can have multiple unique constraints but only one primary key constraint.
8. What is DBMS? What are its different types?
DBMS stands for Database Management System and a database is a structured collection of data.
A DBMS is a software application that allows a user to interact with the database. The data in database can be created, modified , retrieved or deleted using DBMS.
There are two types of DBMS:
- Relational Database Management System (RDMS): The data is stored in relations (tables). For example – MySQL.
- Non-Relational Database Management System: There is no concept of relations, tuples or attributes. For example – MongoDB
9. What is RDBMS?
RDBMS stands for Relational Database Management System. It is a database management system based on relational model. It stores data into the collection of tables and links those table using the relational operators wherever needed. It also provides relational operators to manipulate the data stored into the tables. RDBMS examples - MySQL, SQLServer, Microsoft Access, Oracle database, etc.
10. What are the types of operators available in SQL?
Operators are used to perform operation on two operands. There are three types of operators used in SQL:
- Arithmetic operators: addition(+), subtraction(-), multiplication(*), division(/), etc.
- Comparison operators: =, !=, <>, <, > <=, >=, !<, !>
- Logical operators: ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, IS NULL, UNIQUE
11. What is a join and how many different types of join in SQL?
The SQL Join is used to combine rows of two or more tables based on common column between them. There are four types of joins in SQL:
- Inner Join
- Left Join
- Right Join
- Full Join
12. What is Inner Join in SQL?
The SQL Inner Join is used to combine column values of two tables and returns all rows from both of the tables when there is a match between the columns.
13. What is Left Join in SQL?
The SQL Left Join is used to combine column values of two tables based on the match between the columns. It returns all rows of the table on the left side of the join and matching rows of the table on the right side of the join. The rows of the left side table where there is no match in the right side table, the result table will contain NULL value.
14. What is Right Join in SQL?
The SQL Right Join is used to combine column values of two tables based on the match between the columns. It returns all rows of the table on the right side of the join and matching rows of the table on the left side of the join. The rows of the right side table where there is no match in the left side table, the result table will contain NULL value.
15. What is Full Join in SQL?
The SQL Full Join is used to combine column values of two tables based on the match between the columns and returns all rows of the both tables. If there is no match in any table, the result table will contain NULL value.
16. What is a constraint?
The SQL CONSTRAINTS are used to specify rules on data columns of a table. It is used to limit the type of data that can be stored in a table and consequently ensures accuracy and reliability of the data in the database. Most commonly used constraints in SQL are:
- NOT NULL - Ensures that a column cannot have a NULL value.
- UNIQUE - Ensures that all values in a column are different.
- PRIMARY KEY - Uniquely identifies each row/record in a column of a table. A combination of a NOT NULL and UNIQUE constraints.
- FOREIGN KEY - Uniquely identifies a row/record in a column of another table.
- CHECK - Ensures that all values in a column satisfies certain conditions.
- DEFAULT - Sets a default value for a column when none is specified.
- INDEX - Used to create and retrieve data from the database very quickly.
17. What is Auto Increment?
The SQL Auto Increment keyword is used to create a unique numerical value for each additional record inserted into a table. Generally, it is used to create the numerical primary key field.
18. What is Normalization?
Normalization is the process of organizing structured data in the database efficiently. It includes creation of tables, establishing relationships between them, and defining rules for those relationships. It is used to minimize the redundancy by organizing fields and table of a database.
19. What is Denormalization?
Denormalization is the reverse process of normalization.
20. What is the difference between BETWEEN and IN condition operators?
The SQL BETWEEN Operator is used to specify a range in a SQL statement's WHERE clause. It is a shorthand for >= AND <=. It is a inclusive operator and includes start and end value while specifying the range.
The SQL IN Operator is used to specify multiple values in a SQL statement's WHERE clause. It is a shorthand for multiple OR conditions.