SQL Interview Questions and Answers

A database is a collection of information organised into records within tables. For example, there might be customers, orders, inventory, etc. Each record represents something about one customer, order, item, etc.

SQL stands for Structured Query Language. It is a computer programming language. The main purpose of SQL is to help people access, modify, organize, store, and retrieve data in a relational database.

In general, data refers to any information stored in a computer. This includes text files, spreadsheets, databases, web pages, and so on. The stored information is called “data”.

SQL was designed to make it easier to work with data. Before SQL came along, programmers had to create their own programs to manipulate data. There were no standard commands or procedures like in SQL. Nowadays, everything from personal computers to huge supercomputers have SQL installed as part of their operating systems.

SQL is similar to Java and C# but not quite the same. Unlike other languages, SQL has its own syntax and keywords. You won’t find anything like this in C++ or Java.

There are many types of databases, and these are the major ones.

• Relational Database
• Object-Oriented Database
• Distributed Database
• NoSQL Database
• Graph Database
• Cloud Database
• Centralization Database
• Operational Database

A relational database stores information in rows and columns. For example, a person’s name might appear in the first row, address in the second row, phone number in the third row, and salary in the fourth row. These four items are combined together, making them one row. Rows are added together to form a table. Tables are then joined together to form a database.

An object-oriented database is very similar to a relational database except that instead of storing data in tables, objects (also known as classes) are created. Objects are collections of properties. Properties are attributes that define what each object looks like. When you add more than one property to an object, they become linked, forming a hierarchy.

A distributed database is a type of database where data is split across multiple servers. Servers may be located anywhere around the world. By having all of the data stored at many different locations, you get more performance out of your system.

NoSQL stands for non-relational. It means that the data doesn’t need to be organized into tables or rows. Instead, data can be structured differently. This makes it easier to access and process data quickly.

Graph databases organise information based on relationships between things. They follow directions such as a parent, child, sibling, friend, employee, etc. This allows users to navigate through the data easily.

Cloud Database is a type of software used to store large amounts of data. This data is accessed over the internet by using a web browser. Examples include Google Drive, DropBox, Microsoft SkyDrive, Amazon S3, etc.

The operational database is a database that is used for tracking data throughout the day. The data isn’t changed, just updated. If something goes wrong, you can use the operational database to track down problems.

DBMS stands for database management system. It manages the storage and retrieval of data within a computer. Some examples are Oracle, MySQL, PostgreSQL, MSSQL, etc.

RDBMS stands for relational database management systems. It is also called a traditional database management system. It stores data in tables. It uses SQL queries to retrieve data.

Database Management Systems(DBMSs) are applications that manage data. RDBMSs are designed specifically for managing data.

Tableau Software is a popular tool for visualizing data. Data is displayed with shapes and colors. You can create charts, graphs, maps, and dashboards using this tool.

MySQL is a free open-source database platform. It is built on top of the UNIX operating system. It has easy-to-learn commands and syntax. It is also fast and reliable.

SQL stands for Structured Query Language. It is a standard programming language used to query databases. MySQL is a programmable database engine. It is written in C++. MySQL is faster than SQL because it does not require parsing.

The Four Subsets of SQL are the following:

• Data definition language (DDL)
• Data manipulation language (DML)
• Data control language (DCL)
• Transaction Control Language (TCL)

Data Definition Language (DDL) is used to create, alter, and drop objects in a database. DDL is also known as CREATE, ALTER, DROP statements. These statements help us define the structure of our database.

Data Manipulation Language (Dml) helps us change the data stored in our database. We may want to add or delete records. We may need to insert new rows into existing tables.

Data Control Language (DCL is used to set up permissions for accessing various parts of the database. There are two types of DCL. One is user-based access control, and other is table-based access control.

Transaction Control Language (TCL), is used to start, commit, rollback transactions. In plain English, we can say TCL is used to ensure the consistency of database.

Tables are collection of related data. Fields store information about each record stored in a table. Let’s take the below given example. Here there is a table named “student“. Its field name is “name”. This table contains three records as shown below. Here first row shows the student’s name, second row shows his address, and third row shows his phone number.

Table Name: `STUDENT` FIELDS: | Name | Address | Phone Number

• The constraints are mandatory.
• They have no effect when the database is created. 
• They do not affect the size of the database.
• They protect against errors. 
• They enforce referential integrity.

A constraint is enforced at the time of creation of the Database. To explain you in better way imagine any relationship like mother father son daughter etc.. all these relationships should be present only once in the parent table so that child table will get associated with one and only one parent table.

For eg. if i have a customer table then it can associate many different supplier table based on the type of product ordered by customer. In this case the reference from both sides must be unique and hence constraint comes here. If the same relation exists twice than it will lead to orphan record. Hence we make sure that this relation exist only once so that it will always get associated with one and every time. 

It is nothing but a unique attribute in the database. It becomes primary because it uniquely identifies a single record. A unique value is required so that we can identify any particular record in the database without ambiguity.

When we create a relationship between two tables, then we need a column which holds the identity of one table. So that we know what record it needs to match, then those identities become known as foreign key columns.

Unique keys are also called primary keys. They are primary because they’re used to uniquely identify a table. They are also key because they act as a link between two otherwise unrelated rows or attributes. They are unique because they contain values that don’t repeat across distinct rows.

Foreign keys are columns that link rows of two tables. Let’s say I have a student table (“Students”), and a table (“Student_grades”). Every student has a unique ID number. We could use that number as a foreign key in “Student_grade” to connect their grades directly to them. Students who have higher grade point averages receive higher numbers.

Normalizing means normalizing data into smaller units. There is no exact definition for normalization. However, it usually involves:

• Reducing the number of fields within rows. If you notice, most relational databases contain fewer fields per row than non-relational databases.
• Reducing redundancy by combining related items into fewer rows.
• Relieving yourself of having to look up multiple tables to find the right combination of data.

The goal is to remove redundant and unnecessary data, reducing the chance of storing incorrect or extra data. In general normalized data is easier to query, update, and maintain.

One of the uses of foreign keys is to ensure that each unique entry in a foreign table corresponds to an entry in the primary table. This ensures that the structure of the foreign table matches the structure of its corresponding primary table. The other use of foreign keys is to enforce referential integrity. Some database systems allow referential integrity to be violated by deleting records from the secondary table and adding new records to the primary table instead.

Inconsistent dependencies occur when one piece of code depends on a set of objects that may not exist.

Denormalization is a form of data reduction in which data is duplicated. This includes all forms of duplication, such as repeating data (e.g., a table may contain three copies of the telephone number with different area codes) or repeating groups of data (a customer name can be repeated many times). In this case, denormalization means that duplicate data is stored in fewer places rather than more.

Arithmetic and logical operations are performed with operators. All databases support the same basic types of arithmetic operators. These include addition (+), subtraction (-), multiplication (*), division (/), exponentiation (**), modulus (%) and bitwise AND (&), OR (|), XOR (~) and NOT (!). Logical operators like AND, OR and NOT are often referred to as Boolean operators.

SQL operates are:

• Arithmetic operators
• Logical operators
• Comparison operators
• Bitwise operators
• Compound operators
• String operators

An index is a special type of data structure designed to speed up certain kinds of queries. It stores information about your data so that when you run a query against it, you don’t need to scan every record.

A unique index is an index where no two values for the column being indexed can have the same value.

Example: if you had a table containing employee names and phone numbers, you might want to create a unique index on the first name column to make sure that only one person has the exact same name.

We use indexes because they provide several benefits, including faster retrieval and insertion, improved concurrency, and better space utilization.

Clustered index refers to a kind of database indexing technique. In general, a clustered index is always at the end of the B-tree. That’s why it is called “clustered”.

Non-Clustered indices are usually created on columns that are part of the PRIMARY KEY. They’re also useful when you need to find rows based on some other criteria besides their key.

Joins are used to combine sets of related data together into a single result set. The most common join operation is the INNER JOIN, which combines the results of two tables according to matching conditions specified using WHERE clauses. When both tables have the same structure, there are four possible ways to write a simple SELECT statement. The simplest way is to list them in order:

Inner Join is a SQL operator. It allows us to get records from two tables that share a relationship between each other. The output will show all the records from the first table along with any matching records from the second table. If there is no match, then the corresponding field will be null.

Self-join is a type of join in relational databases. This means that the result table includes all the records from the original table as well as matching records from the same table itself.

In a cross join, multiple tables are combined into one result set. The final result set should include all combinations of the individual elements.

Outer Join is a SQL operator that combines rows from two tables into a single result set by linking them through a third table. The outer joined table contains all the rows from the first table, even those that do not have matches in the second table.

Left or right outer join is similar to an outer join, but instead of returning all unmatched rows from the first table, it returns only those rows from the first table where the condition does not match.

Right outer join is similar to left outer join but instead of matching the condition in the middle table it looks for mismatches in the second table. Although no rows are found in the second table, the first table is returned as a result. This can occur when the first row in the second table has no matches in the first table.

Full outer join is like left or right Join except that it includes all matched rows regardless of whether they come from the left or right table. By default, this is true for left joins, but for right joins, it defaults to false.

Nested query is a subquery where the result of the nested query becomes part of the outer query. This helps us perform complex queries without using loops.

In the world of data management, views are virtual tables that emulate specific tables. Despite providing easy access to large amounts of data, they cannot stand alone. Because views are essentially just queries saved in text files, they tend to run very slowly. On the other hand, tables are physically organized objects that store information. They’re often much faster than views because they don’t require text encoding and decoding.

• SELECT – Retrieves rows that meet certain criteria.
• INSERT – Inserts new rows into a database table.
• UPDATE – Changes existing values in a table.

Set operators are used to find common elements between two sets. They can be used on any column in your table. The four different types of set operators are: 

• Union (U) – Find all records that exist in both collections.
• Intersection (INTERSECT) – Finding all records that exist in at least one collection.
• Except (EXCEPT) – Only include items that aren’t included in either list.
• Symmetric Difference (SYMMETRIC DIFFERENCE) – Find all records that exist only in one collection but not the other.

• IN – Finds records which belong within a given range.
• BETWEEN – Finds records having specified date or time range.

Where Clause shows conditions on particular columns whereas Having clauses show conditions among particular groups of result set.

It stands for Atomicity, Consistency, Isolation, Durability. It means that all changes made by transactions should be valid and remain visible throughout the system.

A Cursor is an object in SQL Server Management Studio that allows you to view data. You use cursors to browse through result sets produced by complex SQL statements.

First we create a cursor, then open it using OPEN statement. After that we fetch data using FETCH command till it reaches end of result set. To close a cursor use CLOSE command.

Schema is a name for a data structure, such as a table or view, that contains some piece of data. Within a database there might be many schemas, each with its own set of tables, views, indexes, and so forth. There are three kinds of schemas in SQL Server; User Schemas, System Schemas, and Default Schema.

Database Blackbox testing means, testing in a way so as not to change the black box. The tester is unaware of internal implementation details, such as algorithms. He/she interacts with the application through a standardized interface, usually using standard input output format. Using this method, tester does not get confused by the complexity of the underlying code logic.

Data Integrity means ensuring that a record that is changed is validated against other records such that errors cannot occur if a transaction conflicts with another transaction. For example, consider a company where sales staff enter customer orders into the system. If an order is entered incorrectly, it must still be possible to cancel it without causing damage to other customers’ information. A transactional update will ensure that this happens.

RLS helps us to restrict user’s access to objects in a Database. It uses security level in place of roles to control access to various objects. We have 3 major categories of Access Rights as listed below:

• Ownership – This right gives privilege to owner(user) to perform activities on the Object.
• Membership – This right gives permission to users who can see rows owned by the user.
• Expressions – This rights give scope over what expressions are allowed to execute in the context of the object. This right restricts how functions may be used.

Normally, a normalization rule describes a relational model. Using entity-relationship diagrams, rules are formulated. These rules define all the relationships between entities. In general rule two things are kept in mind while designing a database. That is, firstly only one thing from different group must be related together, meaning that different groups should be stored within separate tables. Secondly every row within a given table should contain only unique value.Your database design is perfect if these two conditions are met.

When you need to join more than one source table together to produce a single final output, instead of using subqueries. An example would be joining multiple tables containing employee salary and benefits.

ERD is a tool to develop a conceptual view of a database. It allows people to graphically depict relations among database entities. A simple ERD shows how objects relate and why they have been organized that way. A relationship might represent any number of attributes, including foreign key columns, properties or even business rules. You may also have many-to-many relationships in which there are several objects linking each other.

It replaces NULL values with some specified string in case when expression returns NULL.

It is basically a replacement for IFNULL() statement. Instead of checking if the column contains null value we just substitute this value with some default value as defined in coalesce_default parameter of this function.

SQL injection attacks occur when users provide input in such a way that enables them to manipulate the result set produced by the query. For e.g. suppose the user enters ‘;DROP TABLE Students’ in place of name field. This will cause drop table statement to execute against selected student database.

Subquery is a type of SELECT statement. There are three types of subqueries

• Correlated Subquery: Here the results of one or more queries are returned along with the main query.
• Uncorrelated Subquery: Here no data is returned but only the query itself is executed.
• Scalar Subquery: Here the entire query is evaluated and the resulting value is returned.

Both of these statements insert same data into the target table. But the former inserts the data without specifying the column names whereas the latter specifies the column names.

Group functions help us to perform aggregate functions like SUM(), AVG(), MAX(), MIN(). They can be used to find out the maximum/minimum value of a particular column in a table.

A relationship is a connection between two or more tables. It helps us to link two tables together. For instance, if we want to know about students who have taken a course, we could create a new table called CoursesStudents where we store information about the courses taken by the students. Then we can link this table to the Student table using a foreign key.

The following are the different types of relationships:

• One To Many: When one object has many associations with another object.
• Many To Many: When one or more objects have many associations with another object(s).
• One To One: When one object has exactly one association with another object.
• Many To One: When one or more object has exactly one association (or reference) with another object.

Merge statement is used to merge multiple rows from source table into single row in destination table. It also works on the columns which are not part of primary key.

Recursive stored procedures are similar to procedural programming languages. They can be used to calculate complex operations. Suppose you have a requirement to calculate the sales tax for a particular product. You would write a stored procedure to do so. Now the problem arises that you don’t want to repeat the calculation every time your application needs to calculate the sales tax. Hence, you might think of creating a stored procedure which calculates the sales tax at run-time. Similarly, you can use recursive stored procedures to solve problems related to calculations of taxes, interest etc.

Triggers are a mechanism which automatically executes certain actions when certain events happen. For e.g., whenever there is any update operation on a record, trigger will automatically recalculate the price of an item. There are three types of triggers:

• AFTER TRIGGER – This type of trigger runs after the action specified in it completes successfully.
• BEFORE TRIGGER – This trigger runs before the specified action takes place.
• INSTEAD OF TRIGGER – This kind of trigger runs instead of the specified action.

Clause is a set of instructions which define the meaning of expressions in a query. Clauses are enclosed within parentheses (). The most common clauses are SELECT, WHERE, FROM, HAVING, ORDER BY, LIMIT, UNION, JOIN.

HAVING clause filters out only those records which satisfy all the conditions defined inside the HAVING clause. Whereas WHERE clause filters out only those values which satisfy the condition stated in the WHERE clause. If both clauses are present then they are combined logically.

Group By clause groups data based on some criteria. For example, suppose we have a database containing details of employees working in a company. We may group these employees based on their department.

By using an alias, we can indicate the ordinal position of a column in a select list. A commas separate the alias name from the AS keyword to create an alias.

For eg: SELECT emp_name FROM employee

• SELECT emp_name FROM person
• SELECT p.emp_name AS emp_name FROM persons P

Aggregate function returns a measure of central tendency or a summary statistic of a collection of numeric data. Examples of such functions include AVG(), MAX(), MIN() etc. The most commonly used aggregates are SUM(), COUNT(), AVG().

Scalar functions return one value. These functions are very useful as they can be used to perform various mathematical operations like addition, subtraction, multiplication, division etc. On the other hand, non-scalar functions return more than one value. For example, using a scalar function, SUM(), you can add all the numbers entered by users. A non-scalar function is also known as an aggregate function.

Distinct command removes duplicate rows from a table. It operates on two tables joined with each other. A distinct operator works on columns of the same name. If you have a table named EMP having columns NAME and DEPARTMENT, then you can apply DISTINCT on this table to remove duplicate names.

Null IF is used to check whether a given expression equals null. So, if you run this command without specifying any expression, it checks for null values.

A view is a virtual table that provides read-only access to another table. Views provide fast access to data but cannot be updated directly. They are similar to queries except that views are stored in memory whereas queries are saved on disk. In order to modify results of a query, we first create a new query by modifying the original query. Then we save this new query to a file so that later we can retrieve the modified result set. Using a view, we don’t need to go through these steps. To create a view, we simply run a CREATE VIEW statement.

Collations are rules that help determine how strings are compared within the same database. Different collations can cause strings to sort differently. Each character has a collating sequence associated with it. This sequence determines how the string should be sorted. There are many different types of collations available.

When we talk about collation sensitivity, we mean the ability of some characters to dictate their relative positions in comparison with others. Some characters are more sensitive than others. That means that when comparing two strings, certain characters may influence the sorting priority. Characters that might affect the sorting criteria are termed as primary key characters.

Local variables are temporary variable which exist only inside an execution context. We can declare local variables at any point within our script.

Variables declared outside of an execution context are called global variables. It is the entire program or module that is covered by global variables. Throughout the application, they are accessible.

An auto increment column automatically increments its value whenever a row is inserted into the table. The default behavior of auto incrementing is to start counting at 1. You can change the starting value by setting the AUTO_INCREMENT property.

PL/SQL is a database programming language. It was invented by Oracle Corporation. In contrast, SQL is a standard protocol and language for querying databases. SQL is generally used for retrieving data from a relational database.

Use the following query to get the total number of records in a particular table. SELECT COUNT(*) FROM TABLE_NAME;

A datawarehouse (DW) is a collection of information that is organized in such a way that it can be accessed quickly using a variety of tools. DWs are usually designed to store large amounts of data, often terabytes or even petabytes.

Create the tables with identical structures. For example, if you want to copy the structure of one table to another then use the following command. ALTER TABLE t1 RENAME TO t2;

Copy data from one table to another table using INSERT INTO… SELECT syntax. For example, insert rows from one table to another using the following command. INSERT INTO t1 SELECT * FROM t2;

Comments are special sequences of characters enclosed in /* */ that appear anywhere in a SQL statement. They do not have any effect on the operation of the statement. However, they are useful for documenting statements.

Rank function returns the position of a given row in a group. It works on ordered sets of values. On the other hand, dense rank function gives the position of a row in relation to all the rows in the set.

Row_number() is used to generate sequential numbers for each row in a result set. This is used to order result set in ascending or descending order. Rank() is used to assign a rank to each row in a result set based on the specified ordering.

Ordering by columns without specifying direction is called ascending order. In descending order, the last column specified will be first. If no column is specified, the result is sorted alphabetically.