SQL Interview Questions

If you are looking to build your career as a SQL Developer then prepare in advance with these frequently asked interview questions on SQL. This SQL based interview questions covers from basic to advanced level of SQL concepts which include types of statement in SQL, Schema Vs. Database, operators in SQL, normalization, nested queries, etc. which will help you get acquainted with the SQL concepts and answer every question with confidence.

  • 4.8 Rating
  • 40 Questions
  • 30 Mins of Read
  • 9654 Reader(s)

Beginner

There are three types of statements available in SQL.

  • DCL: Data control language: It allows us to control the access of the database resources.  GRANT, DENY and REVOKE are the examples.
  • DDL: Data definition language: These kinds of statements allow you to create, update and delete the definition of resources like tables, triggers, views etc. CREATE, ALTER, DROP, TRUNCATE, RENAME are some of the examples.
  • DML: Data manipulation language: These statements help a developer to manipulate the data for ex. Insert, update, delete and retrieve the data. SELECT, INSERT, UPDATE are some of the examples of DML.
  • TCL: Transaction control language: These statements are used to manage transactions. BEGIN, COMMIT, ROLLBACK  are some of the examples of TCL.

DBMS Stands for “Database Management System”. User can interact with DBMS via APIs or commands. User can store a collection of data in the system. DBMS provides ways to structure the data according to the type of data.

There are two types of DBMS:

  • Relational: The data is stored in tables and one can relate data across multiple relations as well. Example – MySQL.
  • NoSQL: This family represents unstructured data.

A database is a container of all of the resources under one umbrella. A database can contain tables, triggers, indexes, log files, etc etc.

A schema is a logical group of those resources. A database can have multiple schemas inside it. Schemas are also known as namespaces in some of the databases. In certain databases, schemas are almost similar to a database as well. In some of the databases, there is no existence of schema as well.

SQL is an acronym for the structured query language. It is meant to provide a common language to create and manage database constructs and manage data.

Currently, we have a lot of RDBMS systems available. All of these follow SQL in order to manage their basic database operations.

In order to structure the data in logical units, the table is a representation of data in terms of rows and columns. Columns represent a similar type of data whereas row represents an entity. Rows are also known as tuples in SQL.

Each table has a unique name and its definition contains fields. Tables can be created using a CREATE TABLE statement. In order to modify the definition of a table, you can use an ALTER TABLE statement.

At the time of table definition, one needs to define the fields going to be present in the table and the type of each field. Type means the datatype each field represents.

For example, a person’s first name can be represented as a string whereas his age can be represented by a number.

Tables are key in any RDBMS systems. Tables hold the data in a structured manner. In order to store data in a table, you need to, first of all, define the structure of the table.

You can create the table by “CREATE TABLE” command. Where we need to define fields of the table and add a data type with the same.

Once the table is created we can use “ALTER TABLE” command where we can alter the field types and field names.

If we want to delete the data of a table “DELETE” command to delete the data from a table.

If we want to delete all the data of a table then we can use “TRUNCATE TABLE” command to delete all the data at the same time.

If we want to destroy the table structure with the data then we can use the “DROP TABLE” command.

We are using tables to store the data. Important thing is to retrieve data from the table when required.  If we have a lot of data and at a time we are required to get a subset of data, it becomes hectic to go through all the rows and identify the row required, which is time-consuming as well as not efficient.

In order to uniquely identify a column, SQL provides a way to create a primary key. It designates a column or a set of columns which are required to uniquely identify a unique row in the table. In certain SQL implementations, the primary key is by default created if table structure does not have it.

You can not have null values as part of the primary key.  We can specify a column and designate it as a primary key while “CREATE TABLE” or “ALTER TABLE”.

A unique key is a key which works similar to the primary key. Unique keys are also created to identify a single row. Unique key can also have one or more columns in it. The only difference between a primary key and a unique key is that the unique key can allow null value as part of column value and null can also be counted as a value which might be used once per column.

The RDBMS is used for defining relations across tables. Tables can have rows which might be related to each other. In order to relate to one or more rows with one of the relations/tables, we can use foreign keys.

Foreign key maintains referential integrity by enforcing a link between the data across two or more tables.  We can define the foreign keys while creating tables and referencing columns of an already created tables in the definition of a newly created table.

Foreign keys also referred to as parent-child relationship across tables. While destroying the data foreign keys make sure that the data is not deleted in parent without removing the references in the child table. It is mandatory to delete the data first in child table and the parent table.

While we want to make sure the type of data we want to store in a database, constraints help us validate the type of data we can store or we can specifically we can check the values as well.

  • NOT NULL: Makes sure that the NULL Value cannot be stored
  • UNIQUE: Makes sure that values in each row for a column is unique
  • CHECK: Makes sure that all values in the column satisfy a given condition
  • DEFAULT: When no value is specified, this constraint makes sure to assign a value

We can define these constraints while CREATING a table or modifying the table in the ALTER TABLE command.

In order to retrieve data faster, we can create an index on a specific column of a table. This way in order to locate the exact row where the value can be searched in a table in a faster manner.

You can think of it as an index of the book. By looking at index in any book, we may traverse to the specific topic of our choice, likewise, we can visit the specific row in a table.  They are acting as a lookup tables database engines can use to speed up data retrieval. It speeds up the commands SELECT and WHERE Clause. It might slow down data input for INSERT or UPDATE statements.

We can create an index using a CREATE INDEX statement on a table for a specific column or a combination of columns.  Same way if we want to delete the index, we can use DROP INDEX command.

Yes, there are mainly 3 types of indexes available.

Unique Index: It can impact the values getting stored in an index. This only allows unique values in the table as well. It is implicit in the primary key is defined for the same column.

Clustered Index: We can create a clustered index on a column of a table. It alters the way the records are stored in a database in its own space where it points its records back to original rows of a table and re-orders them according to the definition. One table can have only one clustered index.

Non-Clustered Index: Difference with a clustered index is that the non clustered index does not create a new sort order but it only stores in the same logical order. Each table can have more than one non clustered index.

We can use the “SELECT” statement to query data from a table. The SELECT command provides a way to specify parameters to the database management system, by providing input on what table to query, what value of a column to be queried, which field values need to be retrieved for selected rows, which operations we can perform on the selected values of fields.

In order to select all columns of a row we can use “(*)”.

For Ex.    “SELECT * FROM table WHERE column = ‘x’ ”. This is just for reference.

We can use three logical operators on the data of a table ( And, OR, NOT )

           For example,

SELECT * FROM table WHERE column1 = ‘X’ AND column2 = ‘y’;    // Example of AND
           SELECT * FROM table WHERE column1= ‘X’ OR column2 = ‘y’;      // Example of OR
SELECT * FROM table WHERE column1= ‘x’ and column2 != ‘y’;  // Example of NOT ( ! )

Here is the list of all operators.

  • Arithmetic     : + , -  , * , % , /
  • Bitwise         : & , | ,   ^
  • Comparison :     == , > , < , <= , >= , <>
  • Compound   : +=, *= , -=, /= , %= ,&=,  ^-=, |*=

Here are the aggregate functions we can use:

SELECT MIN(column1) from table; // to select minimum value from a column

SELECT MAX(column1) from table ; // to select maximum value from a column

SELECT COUNT(column1) from table; // to count number of rows in the current query

SELECT AVG(column1 ) from  table; // to determine the average value from a column

SELECT SUM(column1) from table; // to determine sum of all values of a column

These are some of the special operators

BETWEEN: Operator to select values between a given range.

For ex. SELECT * FROM table WHERE column BETWEEN value1 and value2;

IS NULL: can check the value of a field as null or not null

For ex. SELECT * FROM table WHERE column IS NULL;

LIKE: to check value like a given expression.

% is used with Like to search for more than one character.

_ is used with LIKE to search for one character.

SELECT * FROM table WHERE column LIKE expression;

EXISTS: to identify if the record exists or not.

SELECT * FROM table WHERE EXISTS ( SELECT * FROM table WHERE condition );

When there is a need to select a column’s values from a value present in a different table, we can use nested queries to select a value.

For example.  If we want to select all students present in India. Assume that the student profile is present in student_profie table and cities are present in cities table where the country is also present.

SELECT name, city FROM student_profile WHERE city IN ( SELECT city FROM cities WHERE country = ‘INDIA’ );

Normalization is a process which can help us organize the data to avoid duplication and redundancy. It allows us to organize the database into logical data structures which might have more tables with small amount of rows. We can access the relevant data with greater flexibility and quicker access.

There are following levels of normalization process.

  • First Normal Form: Each row needs to be unique.
  • Second Normal Form: Partition tables with the help of foreign keys in a logical way.
  • Third Normal Form: No further partition of tables is possible, we have to achieve a maximum level of partition by referencing tables.
  • Boyce Codd Normal Form:  Still in 3rd NF, still there can be anomalies resulted if it has more than one candidate key.

RDBMS provides a facility of transactions. With transactions support, RDBMS are said to be ACID Compliant. ACID stands for atomicity, consistency, isolation and durability.

Atomicity is a property of a transaction, which makes sure that the statements being executed in a transaction are completely done or rolled back. If some transactions fail then the changes made by the operation then the system makes sure that the changes are not getting flushed to the disk.

Consistency is a property which acts as a validation. Transactions make sure the state of the data is consistent before and after the transaction.

Isolation is a property which allows transactions to process the data in parallel at the same time.

Durability is a property which allows transactions once committed, it will make sure that even if power failure happens RDBMS will make sure the transaction completes.

Triggers are a special type of functions or stored procedures which can be run after the data in a table is modified, added or deleted. We can create triggers using CREATE TRIGGER command. We can define a function which can operations to be performed on top of data.

Triggers can be independently created and defined on values of a given table and a column. In order to change the trigger, you can either DROP trigger and recreate it or you can use ALTER Trigger in order to change the stored procedure.

When we define primary keys, we generally use a number to represent the column. AUTO_INCREMENT assigns a new value to each row on insertion. AUTO_INCREMENT is a type of configuration in the system table where it is called a sequence. The rules of the sequence can be defined by the user. If you want auto increment the number by 2 for each new row insertion, it is possible via the configuration. The sequence value becomes useful in programming where it provides a reference to the row being inserted in the table and returns the ID of the newly inserted row.

Yes, you are allowed to create a custom sequence. You can create it via command CREATE SEQUENCE.  You can specify the name of the sequence and define the AUTO_INCREMENT value of it. As well as you can specify the name of the sequence and if you want the value of newly inserted record should start from a specific number, then you are allowed to do so as well.

            For ex. CREATE SEQUENCE sequence_name.

Though now AUTO_INCREMENT is sufficient in the databases like mysql. You can modify system tables as well to modify increment value.

The following commands are mainly used with the transactions of the database.

  • COMMIT: if the executed statements are well executed and we want to flush the changes we did in the transaction, then we can issue COMMIT to flush the changes on the disk.
  • ROLLBACK: if we do not want our changes to be flushed to disk and remain permanently then we can use ROLLBACK command
  • SAVEPOINT: When there are long-running transactions, we can create save points which can help us to rollback transaction up to last savepoint command.
  • RELEASE SAVEPOINT: We can issue RELEASE SAVEPOINT to clear the savepoint created.
  • SET Transaction: We can use SET TRANSACTION to READ_WRITE or READ_ONLY.

Here are the scalar functions used in SQL, these functions return a single value based on the input value.

  • ROUND(): rounds up the decimal field to a number
  • MID() :  Returns the mid character in  a string
  • UCASE(): returns the field value in upper case.
  • LCASE(): return the field value in lower case.
  • FORMAT(): specifies the display format
  • LEN(): returns the length of a given string

A view acts as a virtual table in SQL. We can define a query which anyone can use to view the data on demand. The view doesn’t store data in its table. We can create a view by the following command.

CREATE VIEW  viewname AS SELECT * FROM  sample_table WHERE condition;

In order to update the existing view, you can use the following command,

CREATE OR REPLACE VIEW viewname AS SELECT * FROM another_sample WHERE condition;

The very first step about security is to allow access to the database. In order to access a database one needs to create a user and assign a password for the same. We can also restrict the user to access the system from specific network/networks as well. One can define a strong password policy to make sure someone doesn’t create weak passwords.

The second security step would be to allow a user to access a database, either to create or to select data from the database from tables etc. One can grant permission to only access data, insert data, manipulate resources or create resources, or one can also grant permission to drop a table, database, views and triggers.

The other security features might include replication, backup related permissions or some plugins which are used for group communication protocols etc.

When we are talking in terms of two tables, tuples can be related in the following ways with respect to the primary key to foreign key relations.

Let’s take the example of a person and his house ownership.

One to One relationship: One person can have ownership of only one house.

One to Many relationships: One person can have ownership of multiple houses.

Many to one Many people can own a single house.

Many to many relationships: Many people can own many houses.

UNION: returns all distinct rows returned by two queries.

For ex.   SELECT * FROM sample_table WHERE sample_column = ‘x’

                                                 UNION

           SELECT * FROM sample_table WHERE sample_column = ‘y’;

MINUS: returns all rows of the first query but not returned by the second query.

For ex.   SELECT * FROM sample_table WHERE sample_column = ‘x’

                                                    MINUS

              SELECT * FROM sample_table WHERE sample_column = ‘x’ and sample_column2= ‘abc’;

UNION ALL: returns all rows including duplicates from both the queries.

For ex.   SELECT * FROM sample_table WHERE sample_column = ‘x’

                                                 UNION ALL

              SELECT * FROM sample_table WHERE sample_column = ‘y’;

INTERSECT: returns all rows returned by both the queries.

For ex.   SELECT * FROM sample_table WHERE sample_column = ‘x’

                                                  INTERSECT

            SELECT * FROM sample_table WHERE sample_column = ‘y’;

Denormalization is a process of database optimization technique used to increase the performance of the database, by adding redundant data to the same table. In certain situations, it is fair to do that if the situation requires. It is a reverse process of normalization.

This technique is seldom used. In a practical sense, we have not used this a lot.

SQL data types are used to associate with the columns of the table. SQL data types can be categorized in the following categories.

Numeric: integer, bit, tinyint, bigint, float etc

Date and time: Date, Time, Timestamp, DateTime etc

String : char,varchar, text, nchar,nvarchar,ntext for Unicode

Boolean: true and false

Binary: binary and varbinary

Others: blob, clob, XML,JSON, inet address etc

All datatypes are not supported by every vendor. The above-mentioned datatypes are not all datatypes. These are some of the popular ones. Every vendor might have different limits on the data types.

We can use join keyword to query entries from multiple tables. It is used with different keys to find these entries.

  1. Inner Join: selects rows which are common between the tables based on the matching key
  2. Outer Join: selects rows which are common from the table as well as uncommon records from Left or the right table.
  3. Left Outer Join: Selects all common rows + the rest of the data of left table with null values for the right table
  4. Right Outer Join: Selects all common rows + the rest of the data of right table with null values for the left table
  5. Full Outer Join: Selects all common rows + rest of the data from the left and right table
  6. Cross Join: when we try to join all rows of two tables we get n * m rows  ( assuming n rows from first table and m rows from the second table )

Group By” clause is used to arrange identical data into groups with the help of functions. We can use group by clause with aggregate functions like COUNT, MAX, MIN, SUM, AVG.

In order to group similar data on more than one column is also possible. For example, a list of users is there. If we want to understand how many users are Male and born in a specific year we can use Group by clause and identify that.

For example,

SELECT gender, born_in_year,count(*)  FROM user_profile GROUP BY gender, born_in_year;

When we need to join the rows of the same table, it is called the selfjoin. Selfjoins are used to compare the value of a column to other values of the same column in the same table.

For example, if we need to find out the manager name of the employee and we only have one master table with all employee details, we need to do a self join and get the data.

Select E.emp_id,E.name,M.Name as ‘manager name’ from employee E,employee M where E.emp_jd=M.manager_id;

We can easily store the data in file-based systems. But when it comes to query the data file based system has a lot of disadvantages.

RDBMS are developed to handle a large amount of data, both in terms of storing and retrieving the data. DBMS is more efficient than the file system.

RDBMS can handle data redundancy and inconsistencies. Isolation of data based on data types is managed well in DBMS Systems.

Isolation for accessing data at the same time and processing of data is allowed in RDBMS very easily. RDBMS is fully ACID Compliant whereas traditional file systems are not providing those facilities at all.

As well as RDBMS provide more security features than the traditional file system.

An ability to execute transactions is related to a schedule in DBMS.

Schedules can be Serial or Concurrent. When one transaction executes completely and only after its completion the other transaction starts, this schedule is called serial schedule. When operations of transactions are interleaved with another transaction then it is called a concurrent schedule.

In any RDBMS systems, locks play a very important role in order to maintain consistency for the data. The different types of locks are,

  • Shared locks: These are read-only locks. Other transactions can’t update the data
  • Update locks: lock to update a row/rows. Generally, we use SELECT the data FOR UPDATE. If we use FOR UPDATE, it takes a lock on those rows.
  • Exclusive locks: Locks for one transaction modifying or inserting or deleting data.
  • Intent locks: before taking locks, a notification mechanism that the transaction might require a lock on the data
  • Schema locks: if we want to change the structure of schema, we can use it

In some databases, we can also take a lock for maintenance.

Backup of a database is generally being taken care by taking a dump of the current state of the RDBMS system. For example, mysql creates a dump file which actually dumps all the schema, table structures, with data in an SQL file.  In order to restore the same data to a different server, one can un-dump the same SQL file and recreate all database as is. We can also take a backup of only table structures if we do not require data.

In some cases where a point in time backups are required, they take DB dump and also take care of log files which get created with the database to replay those logs after the dump has restored. There are several strategies available to do so. For mysql, we can use mysqldump command.

For example : mysqldump  -uroot database_name > file.sql

While designing the SQL Query one needs to make sure that the query which is being fired is using the index. In order to identify whether the index is being used or not you can use

EXPLAIN  <QUERY>: When we hit this command, it describes the query execution plan where it will identify based on a number of rows, whether the query is going to use the index or not and how much time it is going to take approximately. For that query to run, how many rows the database engine needs to go through etc.

EXPLAIN SELECT * FROM sample_table;

                      QUERY PLAN

---------------------------------------------------------

Seq Scan on sample_table  (cost=0.00..155.00 rows=10000 width=4)

(1 row)

When you run this command with the query the statement does not get executed.

When you use “EXPLAIN ANALYZE <Query>” then the statement gets executed and you get the actual values.

Yes, we can create an index which includes multiple columns. Here is an example for the same.

CREATE UNIQUE INDEX index_name  ON table_name (column1, column2)

Now, whenever we will use the query which has both of the columns in where clause, the database engine is going to use this index.

For example : SELECT * FROM table_name where column1 = ‘x’ and column2 = ‘y’;

We can use EXPLAIN with the query and verify the same.

Description

SQL is a database computer language designed to interact with a database. The interaction part includes updating, deleting and requesting information from database. SQL is ISO and ANSI standard and various database products like Oracle and Microsoft SQL Server support SQL. It is used in every industry to deal with complex databases.
 

The rising demand of SQL has created lots of opportunities for the SQL developers from many prime companies around the world. According to a recent report released by Statistica, SQL is the most popular database management systems (DBMS) in the world and the market is expected to grow by 70% in the year 2020. So, you have a chance to go ahead make your career in SQL.
 

SQL Developers are paid highly. According to Indeed, they earn an average of $85,483 per year and can play the roles like Database developer, Data/ETL Developer Analyst (SQL Server), SQL Database developer, SQL Server Developer, Data Visualization Developer, Business Intelligence developer, and so on. Amdocs, IBM, Oracle, Wipro, Infosys, TCS, CTS, are the top companies hire SQL Developers.
 

These PL/SQL interview questions are very common and specially framed for basic to advanced level individual to clear any type of tricky SQL job interview. These top SQL interview questions and answers will increase your confidence level and let you ace the interview easily.
 

Preparing with these interview questions for SQL will aid you achieve your dream job and face the toughest of SQL questions in the best possible way. PL/SQL interview questions are framed by the experts and proved to be more effective questions.  
 

So, feel confident during an interview with these SQL interview questions and answers given below. All the best and excel the interview!

Read More
Levels