Top-30 SQL questions in 2020 interviews that you should know.

Preamble

Relational databases are among the most frequently used databases to date, and therefore SQL skills are mandatory for most positions.

In this article with SQL interview questions I will introduce you to the most frequently asked questions on SQL (Structured Query Language).

This article is an ideal guide for learning all concepts related to SQL, Oracle, MS SQL Server and MySQL database.

Our SQL Queries article is a universal resource where you can accelerate your interview preparation. It consists of a set of 30 most common questions that the interviewer can ask during the interview.

It usually starts with basic SQL questions and then moves on to more complex questions based on the discussion and your answers. These SQL questions from the interviews will help you get the most out of it at different levels of understanding.
Let’s get started!

What is the difference between DELETE and TRUNCATE operators?

What are the subsets of SQL?

DDL (Data Definition Language) – allows you to perform various operations with the database, such as CREATE (creation), ALTER (change) and DROP (delete objects).
DML (Data Manipulation Language) – allows you to access and manipulate data, for example, insert, update, delete and extract data from the database.
DCL (Data Control Language) – allows you to control access to the database. Example – GRANT (grant rights), REVOKE (revoke rights).

What is meant by DBMS? What types of DBMS do exist?

Database – a structured collection of data. Database Management System (DMS) – software that interacts with the user, applications and the database itself for data collection and analysis. DBMS allows the user to interact with the database. The data stored in the database can be modified, retrieved and deleted. They can be of any type, such as strings, numbers, images, etc.

There are two types of DBMS:

• Relational Database Management System: Data is stored in relationships (tables). An example is MySQL.
• Non-relational Database Management System: There is no concept of relations, tuples or attributes. Example – Mongo.

What is meant by table and field in SQL?

Table is an organized set of data in the form of rows and columns. The field is the columns in the table. For example:

Table: Student_Information
Field: Stu_Id, Stu_Name, Stu_Marks

What are SQL connections?

The JOIN operator is used to connect rows from two or more tables based on the column linked between them. It is used to combine two tables or get data from them. There are 4 types of connections in SQL, namely:

• Inner Join
• Right Join
• Left Join
• Full Join

What is the difference between the CHAR and VARCHAR data type in SQL?

Both Char and Varchar are character data types, but varchar is used for variable length character strings, while Char is used for fixed length strings.

For example, Char(10) can only store 10 characters and cannot store a string of any other length, while varchar(10) can store a string of any length up to 10, i.e. 6, 8 or 2.

What is a Primary key?

• Primary key – a column or set of columns that uniquely identifies each row in the table.
• Unambiguously identifies one row in the table.
• Zero (Null) values are not allowed.

Example: In the Student table, the Stu_ID is the primary key.

What are Constraints?

Restrictions (Constraints) are used to specify a restriction on the table data type. They can be specified when creating or changing a table. Example of a constraint:

• NOT NULL
• CHECK
• DEFAULT
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY

What is the difference between SQL and MySQL?

SQL is a standard language for Structured Query Language based on English, while MySQL is a database management system. SQL is a relational database language used for data access and management, whereas MySQL is a relational DBMS (Database Management System), as well as SQL Server, Informix, etc.

What is a Unique key?

• Unambiguously identifies one row in the table.
• Many unique keys in one table are acceptable.
• NULL values are allowed (note: it depends on the DBMS, in SQL Server a NULL value can be added only once in the UNIQUE KEY field).

So what would Foreign keys mean?

• A foreign key supports referential integrity by providing communication between the data in two tables.
• The foreign key in the child table refers to the primary key in the parent table.
• Restricting the foreign key prevents actions that break the connection between the child and parent tables.

What is meant by data integrity?

Data integrity defines the accuracy as well as consistency of data stored in a database. It also defines integrity restrictions to ensure compliance with business rules for data when it is entered into an application or database.

What is the difference between clustered and non-clustered indices in SQL?

The difference between clustered and non-clustered indexes in SQL:

• A cluster index is used to easily and quickly extract data from a database, while reading from an unclassified index is relatively slower.
• Clustered index changes the way the records are stored in the database – it sorts the rows by column, which is set as a clustered index, while in non-clustered index it does not change the way of storage, but creates a separate object inside the table, which points to the source rows of the table when searching.
• One table can have only one clustered index, while it can have many unclustered indexes.

Write an SQL query to display the current date

SQL has a built-in GetDate function () that helps you return the current timestamp/date.

List the connection types

There are different types of connections that are used to extract data between tables. Basically they are divided into four types, namely:

Inner Join: In MySQL, this is the most common type. It is used to return all rows from multiple tables for which a connection condition is met.

Left Join: In MySQL, it returns all rows from the left (first) table and only the matching rows from the right (second) table for which the connection condition is met.

Right Join: in MySQL, it returns all rows from the right (second) table and only the matching rows from the left (first) table for which the connection condition is executed.

Full Join: returns all records that have a match in any of the tables. Consequently, it returns all rows from the left table and all rows from the right table.

What do you mean by denormalization?

Denormalization is a technique that is used to transform from higher to lower normal forms. It helps database developers improve the performance of the entire infrastructure by adding redundancy to the table. It adds redundancy to a table, given the frequent database queries that combine data from different tables into a single table.

What are entities and relationships?

Essences: A person, place, or object in the real world whose data can be stored in a database. Tables contain data that represent the same entity type. For example – a bank database has a table of clients to store information about clients. The table of clients stores this information as a set of attributes (columns in the table) for each client.

Relationships: relations or connections between entities that have some kind of relation to each other. For example – the client’s name is associated with the client’s account number and contact information, which may be in the same table. There may also be relationships between separate tables (e.g. client to account).

What is an index?

Indexes refer to a performance tuning method that allows you to extract records from a table more quickly. An index creates a separate structure for the field to be indexed and therefore allows faster retrieval of data.

Describe the different types of indexes

There are three types of indexes, namely:

• Unique Index: This index does not allow a field to have repetitive values if the column is indexed uniquely. If the primary key is defined, the unique index can be applied automatically.
• Clustered Index: This index changes the physical order of the table and performs a search based on the key values. Each table can have only one Clustered Index.
• Non-Clustered Index: This index does not change the physical order of the table and maintains the logical data order. Each table can have many unclustered indexes.

What is normalization and what are its advantages?

Normalization is the process of data organization, which aims to avoid duplication and redundancy. Some of its advantages:

• Better database organization
• More tables with small rows
• Effective access to data
• More flexibility for requests
• Quick search for information
• It’s easier to implement data security
• Allows for easy modification
• Reducing redundancy and duplication of data
• Smaller database
• Provides consistency of data after changes are made

What is the difference between DROP and TRUNCATE commands?

The DROP command deletes the table itself, and you can’t make a Rollback command, while the TRUNCATE command deletes all rows from the table (note: SQL Server Rollback will work fine and roll back DROP).

Explain the different types of normalization

There are many consecutive normalization levels. These are the so-called normal forms. Each subsequent normal form includes the previous one. The first three normal forms are usually enough.

• The first normal form (1NF) – there are no repetitive groups in the lines
• The second normal form (2NF) – each non-key (supporting) column value depends on the whole primary key
• Third normal form (3NF) – each non-key value depends only on the primary key and does not depend on another non-key column value

What is the ACID property in the database?

ACID means Atomicity, Consistency, Isolation, Durability. It is used to provide reliable processing of data transactions in a database system.

• Atomicity. Guarantees that the transaction will be fully executed or fail, where the transaction represents a single logical data transaction. Also, this means that if one part of any transaction fails, the entire transaction fails and the database state remains unchanged.
• Consistency. Guarantees that the data must comply with all validation rules. Simply put, you can say that your transaction will never leave your database in an unacceptable state.
• Isolation. The main purpose of isolation is to control the mechanism of parallel data change.
• Longevity. Longevity means that if a transaction is validated (COMMIT), the changes that occur within the transaction will be preserved regardless of what may get in their way (e.g. power loss, failure or errors of any kind).

What do you mean by trigger in SQL?

An SQL Trigger is a special type of stored procedures that are designed for automatic execution at the moment or after a data change. This allows you to execute a code packet when an insert, update, or any other query is executed to a specific table.

Which operators are available in SQL?

There are three types of operators available in SQL, namely:

• Arithmetic Operators
• Logical Operators
• Comparator operators

Do the values of NULL match the values of zero or space?

The value of NULL is not equal to zero or space. A value of NULL represents a value that is unavailable, unknown, assigned or inapplicable, while zero is a number and the space is a character.

What is the difference between a cross join and a natural join?

A cross join creates a cross or Cartesian product of two tables, whereas a natural join is based on all columns having the same name and data types in both tables.

What is a subquery in SQL?

A subquery is a query within another query that defines a query for extracting data or information from a database. In a subquery, an external query is called the main query, while an internal query is called a subquery. Subqueries are always executed first, and the result of the subquery is passed on to the main query. Also, it can be nested in SELECT, UPDATE or any other query. The subquery can also use any comparison operator, such as >, < or =.

What are the types of subqueries?

There are two types of subqueries, namely: correlated and uncorrelated.

• A correlated subquery is a query that selects data from a table with a reference to an external query. It is not considered an independent query because it refers to another table or column in the table.
• Uncorrected subquery: This query is an independent query in which the output data of the subquery is substituted for the main query.

List the ways to get the number of records in a table?

To calculate the number of records in a table, you can use the following commands:

SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.