Information
AI Chat

Top 70+ SQL Interview Questions and Answers [Mostly Asked]

Course

Research Development And Data Analysis (EDUC 555)

26 Documents

Students shared 26 documents in this course

University

Capital University, Columbus Ohio

Academic year: 2020/2021

Listed booksFUNDAMENTOS DE ANATOMIA CLINICA Donne ai tempi dell'oscurità. Voci di detenute politiche dell'Argentina della dittatura militare

Uploaded by:

Anonymous Student

This document has been uploaded by a student, just like you, who decided to remain anonymous.

Capital University, Columbus Ohio

Comments

Please sign in or register to post comments.

Preview text

Courses

Free Courses Interview Questions Tutorials Community

Home / Interview Question / Top 72 SQL Interview Questions and Answers for 2022

Top 72 SQL Interview Questions and Answers for 2022

Go through these top Structured Query Language (SQL) interview questions for developers to learn SQL programming thoroughly. SQL is one of the most widely used languages. Almost all bigwigs of the tech industry, such as Uber, Netýix, Airbnb, etc., use SQL. This blog lists all the top SQL interview questions. Learn and ace your SQL interview right away!

By Naveen 4 K Views 44 min read Updated on June 28, 2022

Become a Certified Professional

Basic SQL Interview Questions

1. Deüne database.

A database is an organized collection of structured data that can be stored, easily accessed, managed, and retrieved digitally from a remote or local computer system. Databases can be complex and vast and are built with a üxed design and modeling approach. While smaller databases can be stored on a üle system, large ones are hosted on computer clusters or cloud storage.

2. What is DBMS and RDBMS? Explain the diûerence between them.

A database management system or DBMS is system software that can create, retrieve, update, and manage a database. It ensures the consistency of data and sees to it that it is organized and easily accessible by acting as an interface between the database and its end users or application software DBMS can be classiüed into four types:

SQL Interview Questions and Answers | IntellipaatSQL Interview Questions and Answers | Intellipaat

diûerent types of data by using the features of SQL. Basically, it is a database language that is used for the creation and deletion of databases. It can also be used, among other things, to fetch and modify the rows of a table.

4. What is normalization and its types?

Normalization is used in reducing data redundancy and dependency by organizing üelds and tables in databases. It involves constructing tables and setting up relationships between those tables according to certain rules. The redundancy and inconsistent dependency can be removed using these rules to make normalization more ýexible.

The diûerent forms of normalization are:

First Normal Form: If every attribute in a relation is single-valued, then it is in the ürst normal form. If it contains a composite or multi-valued attribute, then it is in violation of the ürst normal form. Second Normal Form: A relation is said to be in the second normal form if it has met the conditions for the ürst normal form and does not have any partial dependency, i., it does not have a non-prime attribute that relies on any proper subset of any candidate key of the table. Often, the solution to this problem is specifying a single-column primary key. Third Normal Form: A relation is in the third normal form when it meets the conditions for the second normal form and there is not any transitive dependency between the non-prime attributes, i., all the non-prime attributes are decided only by the candidate keys of the relation and not by other non-prime attributes. Boyce-Codd Normal Form: A relation is in the Boyce-Codd normal form or BCNF if it meets the conditions of the third normal form, and for every functional dependency, the left-hand side is a super key. A relation is in BCNF if and only if X is a super key for every nontrivial functional dependency in form X 3> Y.

5. What is denormalization?

Denormalization is the opposite of normalization; redundant data is added to speed up complex queries that have multiple tables that need to be joined. Optimization of the read performance of a database is attempted by adding or grouping redundant copies of data.

6. What are Joins in SQL?

Join in SQL is used to combine rows from two or more tables based on a related column between them. There are various types of Joins that can be used to retrieve data, and it depends on the relationship between tables.

There are four types of Joins:

Inner Join Left Join Right Join Full Join

7. Explain the types of SQL joins.

There are four diûerent types of SQL Joins:

(Inner) Join: It is used to retrieve the records that have matching values in both the tables that are involved in the join. Inner Join is mostly used to join queries.

####### SELECT *

FROM Table_A JOIN Table_B; SELECT * FROM Table_A INNER JOIN Table_B;

Left (Outer) Join: Use of left join is to retrieve all the records or rows from the left and the matched ones from the right.

SELECT * FROM Table_A A LEFT JOIN Table_B B ON A = B;

Right (Outer) Join: Use of Right join is to retrieve all the records or rows from the right and the matched ones from the left.

SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A = B;

Full (Outer) Join: The use of Full join is to retrieve the records that have a match either in the left table or the right table.

SELECT * FROM Table_A A FULL JOIN Table_B B ON A = B;

Get 100% Hike!

Master Most in Demand Skills Now!

8. What are the subsets of SQL?

SQL queries are divided into four main categories:

Data Deünition Language (DDL) DDL queries are made up of SQL commands that can be used to deüne the structure of the database and modify it.

CREATE Creates databases, tables, schema, etc. DROP: Drops tables and other database objects DROP COLUMN: Drops a column from any table structure ALTER: Alters the deünition of database objects TRUNCATE: Removes tables, views, procedures, and other database objects

Email Address Phone Number

Submit

Output:

Now, we will insert the records.

Code:

insert into stu1(s_id,s_name) values(1,9Sam9) insert into stu1(s_id,s_name) values(2,9Bob9) insert into stu1(s_id,s_name) values(3,9Matt9) select *from stu

Output:

Also, learn from our blog on MySQL Interview Questions and Answers to crack any Interview.

11. What is a UNIQUE constraint?

Unique constraints ensure that all the values in a column are diûerent. For example, if we assign a unique constraint to the e_name column in the following table, then every entry in this column should have a unique value.

First, we will create a table.

create table stu2(s_id int unique, s_name varchar(20))

Now, we will insert the records.

insert into stu2 values(1,9Julia9) insert into stu2 values(2,9Matt9) insert into stu2 values(3,9Anne9)

Output:

A PRIMARY KEY constraint will automatically have a UNIQUE constraint. However, unlike a PRIMARY KEY, multiple UNIQUE constraints are allowed per table.

12. What is meant by table and üeld in SQL?

An organized data in the form of rows and columns is said to be a table. Simply put, it is a collection of related data in a table format.

Here rows and columns are referred to as tuples and attributes, and the number of columns in a table is referred to as a üeld. In the record, üelds represent the characteristics and attributes and contain speciüc information about the data.

13. What is a primary key?

A primary key is used to uniquely identify all table records. It cannot have NULL values and must contain unique values. Only one primary key can exist in one table, and it may have single or multiple üelds, making it a composite key.

Now, we will write a query for demonstrating the use of a primary key for the employee table:

// CREATE TABLE Employee ( ID int NOT NULL, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255), Employee_Age int, PRIMARY KEY (ID) );

14. What is a unique key?

The key that can accept only a null value and cannot accept duplicate values is called a unique key. The role of a unique key is to make sure that all columns and rows are unique.

The syntax for a unique key will be the same as the primary key. So, the query using a unique key for the employee table will be:

// CREATE TABLE Employee ( ID int NOT NULL, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255), Employee_Age int, UNIQUE(ID) );

15. What is the diûerence between primary key and unique key?

Both primary and unique keys carry unique values but a primary key cannot have a null value, while a unique key can. In a table, there cannot be more than one primary key, but there can be multiple unique keys.

Career Transition

Also, Have a look at SQL Command Cheatsheet.

19. What are the usages of SQL?

The following operations can be performed by using SQL database:

Creating new databases Inserting new data Deleting existing data Updating records Retrieving the data Creating and dropping tables Creating functions and views Converting data types

20. What is an index?

Indexes help speed up searching in a database. If there is no index on a column in the WHERE clause, then the SQL Server has to skim through the entire table and check each and every row to ünd matches, which may result in slow operations in large data.

Indexes are used to ünd all rows matching with some columns and then to skim through only those subsets of the data to ünd the matches.

Syntax:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

21. Explain the types of indexes.

Single-column Indexes: A single-column index is created for only one column of a table.

Syntax:

CREATE INDEX index_name ON table_name(column_name);

Composite-column Indexes: A composite-column index is created for two or more columns of a table.

Syntax:

CREATE INDEX index_name ON table_name (column1, column2)

Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table.

Syntax:

CREATE UNIQUE INDEX index ON table_name(column_name)

Courses you may like

22. What are entities and relationships?

Entities: An entity can be a person, place, thing, or any identiüable object for which data can be stored in a database.

For example, in a company9s database, employees, projects, salaries, etc., can be referred to as entities.

Relationships: A relationship between entities can be referred to as a connection between two tables or entities.

For example, in a college database, the student entity and the department entities are associated with each other.

That ends the section of basic interview questions. Let us now move on to the next section of intermediate interview questions.

Intermediate SQL Interview Questions

23. What are SQL operators?

SQL operators are the special keywords or characters that perform speciüc operations. They are also used in SQL queries. These operators can be used within the WHERE clause of SQL commands. Based on the speciüed condition, SQL operators ülter the data.

The SQL operators can be categorized into the following types:

Arithmetic Operators: For mathematical operations on numerical data addition (+)

subtraction (-) multiplication (*) division (/) remainder/modulus (%)

Logical Operators: For evaluating the expressions and return results in True or False ALL AND ANY ISNULL EXISTS BETWEEN IN LIKE NOT OR UNIQUE

Comparison Operators: For comparisons of two values and checking whether they are the same or not equal to (=) not equal to (!= or <>) less than (<), greater than (>) less than or equal to (<=) greater than or equal to (>=) not less than (!<) not greater than (!>)

27. Why is the FLOOR function used in SQL Server?

The FLOOR() function helps to ünd the largest integer value to a given number, which can be an equal or lesser number.

28. State the diûerences between clustered and non-clustered indexes

Clustered Index: It is used to sort the rows of data by their key values. A clustered index is like the contents of a phone book. We can open the book at <David= (for <David, Thompson=) and ünd information for all Davids right next to each other. Since the data is located next to each other, it helps a lot in fetching the data based on range-based queries. A clustered index is actually related to how the data is stored; only one clustered index is possible per table. Non-clustered Index: It stores data at one location and indexes at another location. The index has pointers that point to the location of the data. As the indexes in a non-clustered index are stored in a diûerent place, there can be many non-clustered indexes for a table.

Now, we will see the major diûerences between clustered and non-clustered indexes:

Parameters Clustered Index Non-clustered Index

Used For Sorting and storing records physically in memory

Creating a logical order for data rows; pointers are used for physical data üles

Methods for Storing

Stores data in the leaf nodes of the index

Never stores data in the leaf nodes of the index

Size Quite large Comparatively, small

Data Accessing

Fast Slow

Additional Disk Space

Not required Required to store indexes separately

Type of Key By default, the primary key of a table is a clustered index

It can be used with the unique constraint on the table that acts as a composite key

Main Feature Improves the performance of data retrieval

Should be created on columns used in Joins

29. What do you know about CDC in SQL Server?

CDC refers to change data capture. It captures recent INSERT, DELETE, and UPDATE activity applied to SQL Server tables. It records changes to SQL Server tables in a compatible format.

30. What is the diûerence between SQL and MySQL?

Now Let9s compare the diûerence between SQL and MySQL.

SQL MySQL

It is a structured query language used in a database

It is a database management system

It is used for query and operating database system

It allows data handling, storing, and modifying in an organized manner

It is always the same It keeps updating

It supports only a single storage engine It supports multiple storage engines

The server is independent During backup sessions, the server blocks the database

31. State the diûerences between SQL and PL/SQL

####### SQL PL/SQL

It is a database structured query language It is a programming language for a database that uses SQL

It is an individual query that is used to execute DML and DDL commands

It is a block of codes that are used to write the entire procedure or a function

It is a declarative and data-oriented language It is a procedural and application-oriented language

It is mainly used for data manipulation It is used for creating applications

It provides interaction with the database server It does not provide interaction with the database server

It cannot contain PL/SQL code It can contain SQL because it is an extension of SQL

32. What is the ACID property in a database?

The full form of ACID is atomicity, consistency, isolation, and durability. ACID properties are used to check the reliability of transactions.

Atomicity refers to completed or failed transactions, where a transaction refers to a single logical operation on data. This implies that if any aspect of a transaction fails, the whole transaction fails and the database state remains unchanged.

Consistency means that the data meets all validity guidelines. The transaction never leaves the database without ünishing its state. Concurrency management is the primary objective of isolation. Durability ensures that once a transaction is committed, it will occur regardless of what happens in between such as a power outage, üre, or some other kind of disturbance.

33. What is the need for group functions in SQL?

Group functions operate on a series of rows and return a single result for each group. COUNT(), MAX(), MIN(), SUM(), AVG(),

Output:

Data Science

LENGTH: It is used to get the length of a string.

Syntax:

LENGTH(8String9)

Example:

SELECT LENGTH(8Hello World9) from String

Output:

35. What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table.

Since the primary key is unique for each record, this primary üeld is added as the AUTO_INCREMENT üeld so that it is incremented when a new record is inserted.

The AUTO-INCREMENT value starts from 1 and is incremented by 1 whenever a new record is inserted.

Syntax:

CREATE TABLE Employee( Employee_id int NOT NULL AUTO-INCREMENT, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255) Age int, PRIMARY KEY (Employee_id) )

Do check out our Blog on PL/SQL Interview Questions to crack your SQL Interview.

36. What is the diûerence between DELETE and TRUNCATE commands?

DELETE: This query is used to delete or remove one or more existing tables. TRUNCATE: This statement deletes all the data from inside a table.

The diûerence between DELETE and TRUNCATE commands are as follows:

TRUNCATE is a DDL command, and DELETE is a DML command. With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can accomplish a trigger. If a table is referenced by foreign key constraints, then TRUNCATE will not work. So, if we have a foreign key, then we have to use the DELETE command.

The syntax for the DELETE command:

DELETE FROM table_name [WHERE condition];

Example:

select * from stu

Output:

delete from stu where s_name=9Bob

Output:

The syntax for the TRUNCATE command:

####### TRUNCATE TABLE

Table_name;

Example:

select * from stu

Output:

truncate table stu

Output:

This deletes all the records from a table.

37. What is the diûerence between DROP and TRUNCATE commands?

delete records in database tables is not available.

45. Mention diûerent types of replication in SQL Server?

In SQL Server, three diûerent types of replications are available:

Snapshot replication Transactional replication Merge replication

46. Which command is used to ünd out the SQL Server version?

The following command is used to identify the version of SQL Server:

Select SERVERPROPERTY('productversion')

47. What is the COALESCE function?

The COALESCE function takes a set of inputs and returns the ürst non-null value.

Syntax:

COALESCE(val1,val2,val3,......,nth val)

Example:

SELECT COALESCE(NULL, 1, 2, 8MYSQL9)

Output:

48. Can we link SQL Server with others?

SQL Server allows the OLEDB provider, which provides the link, to connect to all databases.

Example: Oracle, I have an OLEDB provider that has a link to connect with an SQL Server group.

49. What is SQL Server Agent?

SQL Server Agent plays an important role in the daily work of SQL Server administrators or DBAs. This is one of the important parts of SQL Server. The aim of the server agent is to easily implement tasks using a scheduler engine that enables the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to store scheduled management task information.

50. What do you know about magic tables in SQL Server?

A magic table can be deüned as a provisional logical table that is developed by an SQL Server for tasks such as insert, delete, or update (DML) operations. The operations recently performed on the rows are automatically stored in magic tables. Magic tables are not physical tables; they are just temporary internal tables.

51. What are some common clauses used with SELECT queries in SQL?

There are many SELECT statement clauses in SQL. Some of the most commonly used clauses with SELECT queries are:

FROM The FROM clause deünes the tables and views from which data can be interpreted. The tables and views listed must exist at the time the question is given.

####### WHERE

The WHERE clause deünes the parameters that are used to limit the contents of the results table. You can test for basic relationships or for relationships between a column and a series of columns using subselects. GROUP BY The GROUP BY clause is commonly used for aggregate functions to produce a single outcome row for each set of unique values in a set of columns or expressions. ORDER BY The ORDER BY clause helps in choosing the columns on which the table9s result should be sorted. HAVING The HAVING clause ülters the results of the GROUP BY clause by using an aggregate function.

52. What is wrong with the following SQL query?

SELECT gender, AVG(age) FROM employee WHERE AVG(age)>30 GROUP BY gender

When this command is executed, it gives the following error:

Msg 147, Level 16, State 1, Line 1

Aggregation may not appear in the WHERE clause unless it is in a subquery contained in the HAVING clause or a select list; the column being aggregated is an outer reference.

Msg 147, Level 16, State 1, Line 1 Invalid column name 8gender9.

This basically means that whenever we are working with aggregate functions and are using the GROUP BY clause, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we should use the HAVING clause.

When we are using the HAVING clause, the GROUP BY clause should come ürst, followed by the HAVING clause.

select e_gender, avg(e_age) from employee group by e_gender having avg(e_age)>

Output:

53. What do you know about the stuû() function?

The stuû() function deletes a part of the string and then inserts another part into the string, starting at a speciüed position.

Syntax:

STUFF(String1, Position, Length, String2)

Here, String1 is the one that will be overwritten. Position indicates the starting location for overwriting the string. Length is the length of the substitute string, and String2 is the string that will overwrite String1.

Example:

select stuff(8SQL Tutorial9,1,3,9Python9)

This will change 8SQL Tutorial9 to 8Python Tutorial

Output:

Was this document helpful?