SQL, or Structured Query Language, is a language that is used to interact or communicate with a database. This language assists in performing tasks like retrieval, insertion, updating, and deletion of data from databases. This information is commonly asked in SQL interview questions. An ANSI (American National Standards Institute) standard, SQL helps developers execute queries, insert records in tables, update records, create databases, create tables, or delete tables.

No doubt other programming languages such as ASP, PHP, and ColdFusion helped in making the Internet very dynamic, but SQL has revolutionized how users interact with websites in general. Any reputed webpage that allows the user to provide content uses SQL.

Most Frequently Asked SQL Interview Questions

Here in this article, we will be listing frequently asked SQL Interview Questions and Answers with the belief that they will be helpful for you to gain higher marks. Also, to let you know that this article has been written under the guidance of industry professionals and covered all the current competencies.

1. What is SQL and explain its components?

SQL is a standard language which is used to accessing and manipulating databases. It stands for Structured Query Language. It can be used to develop a web application for a server-side scripting language, like ASP, PHP, etc.

It consists of three components that are listed below:-

• Data Definition Language.
• Data Manipulation Language.
• Data Control Language.

2. Explain the types of joins in SQL?

In SQL, Joins is used to merge records from two or more tables in a database, based on a related column between them.

Here are the four types of the joins in SQL

• INNER JOIN: It returns records that have matching values in both tables
• LEFT JOIN: It returns all records from the left table and the matched records from the right table
• RIGHT JOIN: Right Join returns all records from the right table and the matched records from the left table
• FULL JOIN: It returns all records when there is a match in either left or right table

3. What is a Primary key? Explain

A primary key is a column in a table that designated to uniquely identifies the rows in that table. Primary key holds a unique value for each row and cannot contain null values.

4. Explain Constraints in SQL?

It is a rule used to limit the type of data that can insert into a table, to maintain the integrity and accuracy of the data inside the table.

It can be divided into two types.

• Column level constraints
• Table-level constraints

These are the most common constraints that can be applied to a table.

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

5. What is the difference between HAVING and a WHERE in SQL?

WHERE clause is used for filtering rows, and it applies to every row but HAVING term is used to filter groups.

WHERE can be used without the GROUP BY but HAVING clause cannot be used without the GROUP BY.

6. Explain the difference between SQL and MySQL.

• Mysql is available free because it is open source, but SQL is not an open source.
• Mysql offers updateable views, but SQL offers indexed views which are more powerful.
• Mysql does not support XML, but SQL supports XML.
• Mysql not supported Auto tuning but SQL can.
• Mysql did not support User-defined functions, but SQL supported.
• Mysql support Transaction is very much limited but SQL support extensively and entirely offered.
• Mysql not offered Stored procedures and full joins but offered in SQL.
• Mysql does not support Cursor feature but is there in SQL

7. What is Trigger in SQL? Explain

In SQL, a trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. It can be performed when you run one of the following SQL like INSERT, UPDATE and DELETE occurred in a table. It’s activation time can be BEFORE or AFTER.

8. Explain Normalization and what are the advantages of it?

It is a systematic approach of decomposing tables to remove data redundancy and undesirable characteristics like Insertion, Update and Deletion. Normalization is a multi-step process that put data into the tabular form and removing duplicated data from the tables.

Advantages

• It helps to the reduction of redundant data.
• It has a much more flexible database design.
• It is better to handle on database security.
• It is dealing with data consistency within the database.

Rule of Normalization

• 1NF(First Normal Form)
• 2NF(Second Normal Form)
• 3NF(Third Normal Form)
• BCNF(Boyce and Codd Normal Form)
• 4NF(Fourth Normal Form)

9. How to find 3rd highest salary of an employee from the employee table in SQL?

SELECT salary FROM employee e1 WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM employee e2 WHERE e2.salary > e1.salary)

10. Explain ALIAS in SQL?

It is used to rename a table in a specific SQL statement. Renaming is a temporary change without change actual table name in the database. It can be applied to tables as well as fields.

The basic syntax of a table alias
SELECT field1, field2 FROM TABLE_NAME AS TABLE WHERE "WRITE_YOUT_CONDITION";

The basic syntax of a column alias
SELECT field1 AS column1 FROM TABLE_NAME WHERE "WRITE_YOUT_CONDITION";

11. Explain Scalar functions in SQL?

It is a function that takes one or more values but returns a single value. It is based on user input and returns a single value.

• UCASE()
• LCASE()
• MID()
• LEN()
• ROUND()
• NOW()
• FORMAT()

12. Explain Aggregate functions are available there in SQL?

The aggregate function performs a calculation on a set of values, and it returns a single value as output. It ignores NULL values when it performs calculation except for the COUNT function.

SQL provides many aggregate functions that are listed below.

• AVG()
• COUNT()
• SUM()
• MIN()
• MAX() etc

13. How to make a copy values from one column to another in SQL?

UPDATE `table_name` SET new_field=old_field

14. What are the advantages of SQL? Explain

There are many advantages of SQL, and some of them are mentioned below:

• No coding needed: It is easy to manage without any need to write the substantial amount of code
• Well defined standards: Long established are used by the SQL databases that are being used by ISO and ANSI.
• Portability: We can use this program in PCs, servers, laptops and mobile phones.
• Interactive Language
• Multiple data views

15. Who introduced SQL?

It was initially developed in the early 1970s at IBM by Donald D. Chamberlin and Raymond F. Boyce.

16. What is a Database? Explain

It is a collection of data that is organized to be easily accessed, managed and updated. It is distributed into rows, columns, and tables. It’s indexed to make it easier to find related information.

17. Explain table and field in SQL?

Table: It is a collection of related data that consists of rows and columns. It has a specified number of columns but can have any number of rows.

Field: It is a column in a table that is designed to maintain specific information about all records in the table.

18. Explain Unique key in SQL.

It is a set of one or more than one fields or columns of a table that uniquely identify a record in the table. It is little like a primary key, but it can accept only one null value.

19. Explain Foreign key in SQL?

It is a collection of fields in one table that uniquely identifies a row of another table. In simple words, the foreign key is defined in a second table, but it refers to the primary

20. Write a query to display the current date in SQL?

SELECT CURDATE();

21. Explain the difference between DROP and TRUNCATE commands in SQL?

TRUNCATE

• It removes all rows from a table.
• It does not require a WHERE clause.
• Truncate cannot be used with indexed views.
• It is performance wise faster.

DROP

• It removes a table from the database.
• All table’s rows, indexes, and privileges will also be removed when we used this command.
• The operation cannot be rolled back.

You can download here sql interview questions pdf after registeration or subscribe.

22. Write a query to find the names of users that begin with “um” in SQL?

SELECT * FROM employee WHERE name LIKE 'um%';

23. Explain CLAUSE in SQL?

It is the source of a rowset to be operated upon in a DML statement. These clauses are very common which is used with Select statement, Update statement and Delete statement.

SQL provides with the following clauses that are given below:

• WHERE
• GROUP BY
• HAVING
• ORDER BY etc

24. How to use distinct and count in SQL query? Explain

count(): It returns the number of rows in a table satisfying the condition specified in the WHERE clause. It would return 0 if there were no matching conditions.

Syntax:
SELECT COUNT(*) FROM category WHERE 'status' = 1;

distinct(): It is used to return only different values. In a table, a column often contains many duplicate values, and sometimes we want to list the different values. It can be used with aggregates functions.

Syntax:
SELECT DISTINCT class FROM College ORDER BY department

25. What has stored procedures in SQL and how we can use it?

It is a set of precompiled SQL statements that are used to perform a particular task. A procedure has a name, a parameter list, and SQL statement, etc. It will help in reduce network traffic and increase the performance.

Example

                                                    CREATE PROCEDURE simple_function
AS
    SELECT first_name, last_name
    FROM employee;

EXEC simple_function;