TOP-10 Interview Questions in SQL for Experienced

SQL stands for a structured query language, meaning a domain-specific programming language for controlling data in DBMS. SQL programming skills are incredibly e beneficial and required in the market as there is extensive use of database management systems in nearly every software application. Below is a curated list of SQL interview questions and answers for experienced personals that are likely to be asked during an interview.

1. What is a stored procedure? Explain with an example.

A stored procedure is a developed SQL code that could be saved and reused. We can also consider a stored procedure to be a function containing many SQL statements for accessing the database system. We can combine several SQL statements into a stored procedure and use them whenever required.

The stored procedure could be used as a means of modular programming, indicating we can create a stored system once and call at various times as required. Faster execution while compared to executed multiple queries is also supported.

Syntax:

CREATE PROCEDURE procedure_name
AS
Sql_statement
GO;
To execute, we will use this:
EXEC procedure_name

Example:

We will set up a saved protocol that will allow workers to extract their age.

create procedure employee_age

as

select e_age from employee

go

Now, we will execute it.

exec employee_age

Output:

2. What do you know about Joins? Define different types of Joins.

The Join clause is used for merging rows on a linked column from two or more columns. Different forms of joins could be used to gather data that rely on the relationship of tables.

There are four types of Joins:

1. Inner Join: Essentially, Inner Join returns records in both tables that have the corresponding values.
2. Left Join: Rows that are common between the tables and the rows of the left-hand table are returned from the left-hand table, i.e., all rows are returned even though the right-hand table does not contain matches.
3. Right Join: It returns rows that are common to all rows of the right side table from the table on the right side, i.e., all of the rows are produced from the left side table even though no matches are available.
4. Full Join: Full Join returns from the left side table all rows and the right side table.

3. What do you understand by a Temporary Table? Write a query to create a Temporary Table.

A temporary table lets us store intermediate outcomes and process those results. Temporary tables are created and can be discarded directly if not used again. They are accommodating when provisional data must be stored.

Syntax:

CREATE TABLE #table_name();

The below query will create a temporary table:

create table #book(b_id int, b_cost int)

Now, we will insert the records.

insert into #book values(1,100)
insert into #book values(2,232)
select * from #book

Output:

4. Explain the difference between OLTP and OLAP.

OLTP: It stands merely for on-line transaction processing and can be viewed as a powerful and transaction-oriented applications category. The ability to keep the system stable is one of the essential qualities of the OLTP system.

OLAP: OLAP is a type of application that is a relatively lower frequency for internet transactions and reflects online analytical processing. Effectiveness computing is heavily dependent on reaction time for OLAP systems. Therefore, these structures are commonly used in data recovery or for the maintenance of historical aggregates, typically using them in multi-dimensional schemes.

5.   Explain database white box testing and black-box testing.

The evaluation approach for white boxes primarily concerns the internal configuration of a given dataset, in which users hide parameters. The procedure for checking the white box includes:

• Since the code error can be found by checking the white box, internal errors can be eliminated.
• The default table value is chosen for verifying the accuracy of the database.
• This procedure verifies the referential rule.
• It helps to evaluate the database, trigger, display, and SQL functions module.
• Check the results of the other query functions.

The black box test method typically consists of configuration checking and the incorporation of databases. It consists of:

• Information of mapping
• Analysis of incoming information

6.   What is the use of the Intersect operator?

The intersect operator will merge multiple selected statements and only returns the combination of a specific record of two chosen words. When Table A and Table B are over, and the Intersect operator is added to both tables, we only obtain the records typical due to chosen statements from both.

Syntax:

SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2

7.   What is the COALESCE function?

The COALESCE function takes the first non-null value in a series of inputs.

Syntax:

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

Example:

SELECT COALESCE(NULL, 1, 2, ‘MYSQL’)

Output:

1

8.   What is AUTO INCREMENT?

In SQL, AUTOINCREMENT is used for creating a single number automatically until a new record is placed into a table.

For each record, the primary key is unique, and we add the primary AUTOINCREMENT field to raise it by inserting a new record.

By default, the value of AUTO-INCREMENT begins at one and is raised by one until a new record is added.

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)
)

9. What do you understand by Normalization and Denormalization?

Two approaches used in databases are primarily normalization and denormalization.

Normalization is used in reducing data duplication and indebtedness by coordinating fields and tables in datasets. It includes building tables and creating relationships according to some rules between individual tables. These guidelines will eliminate complexity and contradictory reliance to make it more flexible.

Denormalization is the opposite of normalization. We add redundant data here, essentially, so that complicated queries containing many tables can be speeded up. Herein, by inserting redundant information or grouping information, we aim towards improving the read output of a database.

10. Explain a Self-Join.

A Self-Join is a regular join in which a table is joined with itself depending upon some relation between its columns. It uses the inner join or left join clause, and a table is used for assigning different names to the table within the query.

SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee",
B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor"
FROM employee A, employee B
WHERE A.emp_sup = B.emp_id;

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.