Interviewers commonly ask candidates SQL questions during job interviews because SQL, or Structured Query Language, is the standard language used to manipulate data stored in relational databases like Microsoft SQL Server and Oracle. These types of databases are important in the business world because they help companies store, organize, and manage all of their customer data in one place. If you are thinking about getting a job as a database developer or analyst, here are eight of the most common SQL interview questions employers ask in order to assess your skills with SQL on the job.
1) What is Relational Database?
A relational database is a collection of data that is organized in tables, much like an Excel spreadsheet. Instead of rows and columns, however, relational databases have fields and records. A table can contain any number of records but each record belongs to only one table. The elements within a table are strongly related to each other through primary keys and foreign keys.
2) How does an index work?
An index is a data structure designed to make it quicker to retrieve information from a database. Without an index, you’d have to scan every record in a table in order to find a particular value. So let’s say you want to retrieve all records where city = Boston. Without an index, you’d have to look at every single row and compare against your search criterion.
3) What are different types of joins?
In SQL, there are three main types of joins: INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN. There is also a FULL OUTER JOIN.
4) What are subqueries?
Subqueries are queries within queries. They can be nested and they can return a single value or multiple values. Subqueries are most often used in data manipulation and reporting.
5) What is the difference between DELETE and TRUNCATE?
DELETE statement is used when we want to remove some or all records from the table, while the TRUNCATE statement will delete entire rows from the table and even reset the identity if existing
DELETE statement contains WHERE clause, while the TRUNCATE statement doesn’t contain WHERE clause
DELETE is a DML command which only modifies the table data, while the TRUNCATE is a DDL command
6) What is the difference between Stored Procedures and Functions?
Stored Procedure may or may not return a value, while a Function must return a value.
A Function can be called from a Stored Procedure, while a Stored Procedure cannot be called from a Function
Stored Procedure can have input and output parameters, while a Function can only have input parameters
7) Explain CTEs, Common Table Expressions.
Common Table Expressions (CTEs) are used to define recursive queries and can be very handy in certain situations. A recursive query is any query that includes a reference to itself, either directly or indirectly. The most common use for CTEs is to define reports where data is grouped and aggregated.
8) What is a cursor in SQL?
A cursor allows you to traverse or loop through data in a database table or view. Cursors are similar to variables, but hold one record at a time instead of many records. When used correctly, cursors can improve performance and increase functionality.
