Data is an integral part of every organization and being proficient in SQL is a must for today’s professionals. When one faces an interview, he/she is likely to face SQL interview questions. Here I have captured the most relevant SQL Interview Questions that one needs to be prepared for.
- What are the different types of data?
Ans: Data can be categorised into the following types –
- Structured Data – Data stored in tables in RDBMS
- Semi-Structured Data – XML data
- Unstructured Data – Audio, Video, text files
- What are the differences between OLTP and OLAP?
Ans: The major differences between OLTP and OLAP are as follows –
| OLTP | OLAP |
| OLTP stands for Online Transactional Processing and handles data that is transactional in nature. | OLAP stands for Online Analytical Processing and handles data that is used for analytical and decision-making processes. |
| It consists of current data | It consists of historical data |
| It is highly normalized to reduce redundancy of data | It is de-normalized and redundancy of data is kept purposefully |
| It supports both read and write operations on data | It primarily supports read operation on data |
What are the different types of SQL?
Ans: SQL is of the following types –
- Data Definition Language (DDL) – It helps in defining the database using commands like CREATE, ALTER, DROP
- Data Manipulation Language (DML) – It helps in retrieving and modifying data using commands like SELECT, INSERT, UPDATE, DELETE
- Data Control Language (DCL) – It helps in controlling the access to a database using commands like GRANT, REVOKE
- What are the different types of JOINS in SQL?
Ans: The different types of JOINS are –
- INNER JOIN – In this join the matching records from both the tables are available in the output based on the join conditions.
- LEFT JOIN – In this join the matching records from both tables and the unmatched records from the left table are available in the output.
- RIGHT JOIN – In this join the matching records from both tables and the unmatched records from the right table are available in the output.
- FULL OUTER JOIN – In this join the matched records and the unmatched records from both the tables are available in the output.
- What are the different keys used in SQL?
Ans: There are following types of keys used in SQL –
- Primary Key – It is a column or group of columns which is used to uniquely identify a record in a table.
- Candidate Key – It is one of the set of columns that can uniquely identify a record. Primary key is one of the candidate keys.
- Composite Key – When a candidate key/primary key is composed of more than one column it is called a composite key.
- Foreign Key – A primary key of another table being used in a given table is the foreign key for that table.
- Surrogate Key – When there is no column or group of columns that can uniquely identify a record, then a key is created manually to identify records in the table. This key is called a surrogate key.
- What is the difference between TRUNCATE and DROP commands?
Ans: TRUNCATE command removes all the records from a given table, but keeps the structure of the table as intact. Whereas DROP command removes all the records of a table along with the table structure from a database.
- What is Normalization? What are its types?
Ans: Normalization is the technique used to reduce data redundancy and maintain data integrity. It helps in removing anomalies that arise when we insert, update or delete data from the tables.
The various normal forms that we generally use are –
- 1NF – A table is in 1NF form if the data is at atomic level, i.e. there are no composite data in any given column.
- 2NF – A table is in 2NF if it is in 1NF and has all non-key columns dependent on primary key.
- 3NF – A table is in 3NF if it is in 2NF and there is no transitive dependency of non-key columns.
Read Also: Custom Software Outsourcing – What’s the Difference Between a Big and a Small Company?
- What is the difference between ‘BETWEEN’ and ‘IN’?
Ans: BETWEEN is used to pick up a value within a given range whereas IN is used to pick up a value in a given set.
Examples –
- BETWEEN – SELECT * FROM students WHERE marks BETWEEN 70 AND 100
- IN – SELECT * FROM students WHERE student_name IN (‘Sam’, ‘David’, ‘Roy’,’Celena’)
- Select the third highest Salary in Employee table.
Ans: SELECT TOP 1 * FROM
(SELECT DISTINCT TOP 3 FROM Employee
ORDER BY Salary DESC)
ORDER BY Salary ASC
- What is the use of NVL?
Ans: NVL is used to assign a value to a column when there is NULL in that column.
- What are the various constraints in SQL?
Ans: Following are the various constraints in SQL –
- NOT NULL
- CHECK
- UNIQUE
- DEFAULT
- PRIMARY KEY
- FOREIGN KEY
Read Also: How to Use a GUI with Ubuntu Linux on AWS EC2
- What is a VIEW and how is it useful?
Ans: A VIEW is a virtual table that is formed using one or more tables, storing a subset of data. It provides the following benefits –
- It is often created using a complex query. Thus, it avoids execution of the complex query every time data needs to be fetched from the tables.
- It restricts access to data in the tables and supports data integrity.
- It optimizes the complex query as a simple one.
These are some of the SQL interview questions that companies usually ask. But there is no sure shot guarantee that the questions will be limited to this set. So, it is advised that we keep ourselves updated on our SQL concepts.