Top SQL Interview Questions in 2021

Introduction

SQL (Structured Query Language) is a language that helps in managing the data in an RDBMS (Relational Database Management System). SQL statements are used to perform tasks such as updating or retrieving data from a database.

This whole series is divided into three parts. This is part 1 of the blog, where we will be discussing beginner-level SQL interview questions. There are two more parts to this series where we have discussed intermediate and advanced levels SQL interview questions.

Beginner Level SQL Interview Questions

1. What are databases and DBMS?

Ans: In simple words, The database is an organised collection of the information or data we store, and it can be retrieved from any local or remote computer system.

DBMS stands for Database Management System, and it is software that is used for CRUD commands (Create, Read, Update and delete), and it ensures that our data is consistent and stored in an organised manner.

2. What is SQL?

Ans: SQL stands for Structured Query Language and is a language for RDBMS( Relational Database Management System). Data is stored in the form of a collection of tables, e.g., MySQL, Oracle, etc. In other words, SQL is the most commonly used language for data extraction and data organisation that is stored in a relational database.

3. What are the tables in SQL?

Ans: Tables are collections of related records and information at its view. These collections are called tuples. Tables are database objects which contain all the data in a database.

4. What are the CRUD commands for SQL?

Ans: CRUD stands for Create, Read, Update and delete. So, CRUD commands in SQL are:

CREATE DATABASE or CREATE TABLE is used for creating databases/tables.
SELECT is used for selecting or extracting data from the database.
UPDATE is used for updating data in a database.
DELETE is used for the deletion of data in a database.

5. Name different types of statements used in SQL?

Ans: There are five types of statements in SQL:

DDL (Data Definition Language) is used to define the database structure, e.g., tables. It has commands like CREATE, ALTER, DROP and RENAME.

DML(Data Manipulation Language) – It is used in manipulating the data present in the records. It has commands like SELECT, INSERT, UPDATE, and DELETE.

DCL(Data Control Language) – It helps set users privileges using commands like GRANT and REVOKE.

TCS(Transaction Control Statement) – This statement helps save to find changes permanently into the database.

SCS(Session Control Statements) – This statement helps in managing the session of a user dynamically.

6. What is a DISTINCT statement in SQL?

Ans: DISTINCT statement is used with Select statement. If the record contains any duplicate values, we can use a DISTINCT query to find the unique value in a specified column.

Syntax:

SELECT DISTINCT column_name;
FROM table_name;

7. What is the WHERE clause, and what is its syntax?

Ans: WHERE is used for defining the condition and displaying it only if the given condition is true.

Syntax:
SELECT column_name
FROM table_name
WHERE condition;

8. What are SQL constraints, and what are their types?

Ans: SQL constraints are used for setting the rules for all the records present in the table. There are six major constraints:

NOT NULL – Each column present in the record must have some value, and it should not be null.

UNIQUE – Each row and column contains a unique value.

PRIMARY KEY – This constraint is dependent on the previous two constraints, i.e., NOT NULL and UNIQUE, so that later on, we can choose one or a combination of more than one column to be the primary key.

FOREIGN KEY – It helps in ensuring the referential integrity of our data in a table.

CHECK – It’s used to check whether the values in the column fulfil the specified condition.

DEFAULT – The default constraint helps set the default value for a column.

9. Write an SQL query to find the current date-time.

Ans: SQL query to get the current date-time is SELECT getdate();

10. What is the use of the UPPER function?

Ans: UPPER() function converts the string to an upper case.

Syntax:
SELECT UPPER (‘Coding Ninjas is the best institute’);

//This converts the given text into capital letters.

11. Which operator in SQL is used to select the values within range?

Ans: BETWEEN operator is used for selecting the values within the range, and the values could be text, number, etc.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

12. What are the aggregate functions in SQL?

Ans: Aggregate functions in SQL are:

COUNT- It counts the number of rows in a particular column, but it neglects duplicate values.

SUM – It returns the sums of all the values in a particular column.

MIN and MAX – It returns the minimum and maximum values in a particular column.

AVG – It returns the average of the selected values.

14. Difference between UNION and UNION ALL.

Ans: The difference between UNION and UNION ALL is:

UNION: It gives you all distinct rows when you make either query (Only provides unique records).

UNION ALL: It gives you all rows when you make either query, including duplicates(gives all records, including duplicates).

15. What are transactions and controls in transactions?

Ans: A transaction is a sequence of tasks performed on the database related to CRUD commands (Create, Read, Update and Delete). Either we transfer the money to a bank account or any other thing. Controls in the transaction:

COMMIT – It saves all the changes made during the transaction.
ROLLBACK – If a transaction fails, then the rollback is necessary to revert all the changes made.
SET TRANSACTION – It helps in setting the name of the transaction.
SAVEPOINT – It sets the point where the transaction is to be rolled out.

16. Describe the properties of transactions.

Ans: The transaction has ACID properties:

Atomicity means completeness of the transaction. If the transaction fails, then roll back because the total sum before and after the transaction should be the same.

Consistency is used to make sure if the changes are reflected on the database.

Isolation is used to make sure if all the transactions are isolated or independent.

Durability is used to ensure that all changes are done in the database even after a system failure.

17. What is the trigger in SQL?

Ans: A trigger is a stored procedure that automatically runs or executes when a specific action occurs in the database, such as CREATE, READ, UPDATE and DELETE.

18. What is a view in SQL?

Ans: The view is a virtual table in which rows and columns are present with fields from one or more tables. They are logical representations of existing tables or another view. You can add SQL statements and functions to a view and present the data as if the data were coming from one table.

19. Which SQL keywords tell the order of the result/query?

Ans: For returning the result in ascending order, we will use – ASC keyword, and for returning the result in descending order, we will use – DESC keyword.

20. Write the SQL command for returning all the records from a table named “Students” sorted ascending by “Name.”

Ans: SELECT * FROM Students ORDER BY Name ASC;

22. How will you select ten random rows from a table named STUDENT?

Ans: SELECT * FROM STUDENT ORDER BY RAND() LIMIT 10

23. What is Alias in SQL?

Ans: Alias is the name that can be given to any column or table, and it can also be used with a WHERE clause to identify a particular column or table.

24. Write the difference between DELETE and TRUNCATE?

Ans: The table below shows the difference between DELETE and TRUNCATE statements in SQL:

DELETE	TRUNCATE
It removes a specific row from the table.	It removes all the rows from the table.
It is a DML command.	It is a DDL command.
It is relatively slower than the truncate command.	It is faster than the delete command.
You can roll back data after using the delete command.	You cannot roll back data after using the truncate command.

25. What is a Relationship in SQL?

Ans: Relationship means the connection between more than one table present in the database. There are four types of relationships in SQL:

One to one ( One author has written only one book)

Many to many( Many authors has written one or more books)

One to many(One author has written many books)

Many to One (Many students have enrolled in one course)

26. How to insert a new record into the table named student.

Ans: INSERT into student VALUES (val1,val2….)

27. Name the various privileges a user can grant to another user.

Ans: The various privileges a user can grant to another user are:

SELECT- Helps the user to select statements on tables.
RESOURCE- Grants a user privileges necessary to create procedures and triggers.
CONNECT- This privilege is granted on databases to the public database role by default.

28. What is schema?

Ans: In simple words, the schema is a collection of users in the database. And if we talk about SQL specifically, then the schema is a collection of database objects like tables, stored procedures, triggers, etc.

Q29.What is the difference between WHERE and HAVING clauses?

Ans: Both clauses limit the result set by providing conditions to filter the rows from the result set. However, there is one difference between the two, the WHERE clause scans the raw data (row by row) to check the conditions and filter them, but the HAVING clause scans the aggregated results to check the conditions and filter them. For this reason, HAVING comes after GROUP BY in the SQL query, whereas WHERE comes before it. In summary, WHERE filters the raw data, but HAVING filters the processed data.

Q30.How do, get the alternate records from the table in the SQL?

Ans: For fetching the odd numbers then the following query can be used:

SELECT EmpId from (SELECT rowno,EmpId from Emp) WHERE mod(row,2)=1;

If you want to fetch the even numbers, then the following query can be used:

SELECT EmpId from (SELECT rowno,EmpId from Emp) WHERE mod(row,2)=0;

Key Takeaways

In this blog, we learned that there are so many important questions related to SQL that are asked in interviews, from query/commands to theory.

We have covered the beginner-level questions in this blog. For more questions, you can refer to our blogs on intermediate and advanced-level SQL interview questions. So stay tuned and keep learning.

By: Yogesh Kumar