SQL Interview Questions

What is SQL ?

Structured Query Language is a database tool which is used to create and access database to support software application.

How do we use distinct statement? What is its use ?

Select  Distinct Coloumn_Name from table_name ;

What are different clauses used in SQL ?

1. Where : This clause is used to define the condition, extract and display only those records which fulfill the given condition

Select * from table_name where ( Search_Condition ) ;

2. GROUP BY

3. Having 

4. ORDER BY - ASC/DSC

5. USING 

Why do we use SQL constraints ?

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.

Define 5 type of constraints in Database

a. NOT NULL : column must have some value and cannot be left null

b. UNIQUE: Each row and column should have unique value , non repeated. 

c. PRIMARY KEY: A primary key, also called a primary keyword, is a key in a relational database that is unique for each record.

d. FOREIGN KEY: A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.

e. CHECK: It is used to ensure whether the value in columns fulfills the specified condition

What are different Joins in Database

a. Inner Joins

b. Left Join 

c. Right Join 

d. Outer Join

What are transactions and control ?

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.

In simple word, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as

• COMMIT: It is used to save all changes made through the transaction

• ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before

• SET TRANSACTION: Set the name of transaction

• SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

How many aggregate functions available there in SQL

SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value.

There are 7 aggregate functions we use in SQL

• AVG(): Returns the average value from specified columns

• COUNT(): Returns number of table rows

• MAX(): Returns largest value among the records

• MIN(): Returns smallest value among the records

• SUM(): Returns the sum of specified column values

• FIRST(): Returns the first value

• LAST(): Returns Last value

What are scalar function in SQL

Scalar Functions are used to return a single value based on the input values. Scalar Functions are as follows

• UCASE(): Converts the specified field in upper case

• LCASE(): Converts the specified field in lower case

• MID(): Extracts and returns character from text field

• FORMAT(): Specifies the display format

• LEN(): Specifies the length of text field

• ROUND(): Rounds up the decimal field value to a number

What are triggers ?

Triggers in SQL is kind of stored procedures used to create a response to a specific action performed on the table such as Insert, Update or Delete. You can invoke triggers explicitly on the table in the database.

Action and Event are two main components of SQL triggers when certain actions are performed the event occurs in response to that action.

What are VIEW in sql

A View can be defined as a virtual table that contains rows and columns with fields from one or more table.

Syntax: CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

How we can update the view?

SQL CREATE and REPLACE can be used for updating the view.

Following query syntax is to be executed to update the created view

Syntax: CREATE OR REPLACE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

What is SQL Injection

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have an access to sensitive data and perform administrative tasks on databases.

For Example: SELECT column_name(s) FROM table_name WHERE condition;

What is the use of NVL function?

NVL function is used to convert the null value to its actual value.

What is the difference between DELETE and TRUNCATE?

1. The basic difference in both is DELETE is DML command and TRUNCATE is DDL

2. DELETE is used to delete a specific row from the table whereas TRUNCATE is used to remove all rows from the table

3. We can use DELETE with WHERE clause but cannot use TRUNCATE with it

What is the difference between DROP and TRUNCATE?

TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.

How to write a query to show the details of a student from Students table whose name starts with K?

SELECT * FROM Student WHERE Student_Name like ‘%K’; Here ‘like’ operator is used for pattern matching.

What is Relationship? How many types of Relationship are there?

The relationship can be defined as the connection between more than one tables in the database.

There are 4 types of relationships

• One to One Relationship

• Many to One Relationship

• Many to Many Relationship

• One to Many Relationship

What do you mean by Stored Procedures? How do we use it?

A stored procedure is a collection of SQL statements which can be used as a function to access the database. We can create these stored procedures previously before using it and can execute these them wherever we require and also apply some conditional logic to it. Stored procedures are also used to reduce network traffic and improve the performance.

What do we need to check in Database Testing?

Generally, in Database Testing following thing is need to be tested

• Database Connectivity

• Constraint Check

• Required Application Field and its size

• Data Retrieval and Processing With DML operations

• Stored Procedures

• Functional flow

What are Indexes in SQL?

The index can be defined as the way to retrieve the data more quickly. We can define indexes using CREATE statements.

Syntax: CREATE INDEX index_name

ON table_name (column_name)

Further, we can also create Unique Index using following syntax;

Syntax: CREATE UNIQUE INDEX index_name

ON table_name (column_name)

What is the syntax to add a record to a table?

To add a record in a table INSERT syntax is used.

Ex: INSERT into table_name VALUES (value1, value2..);

Define SQL Delete statement.

Delete is used to delete a row or rows from a table based on the specified condition.

The basic syntax is as follows:

DELETE FROM table_name WHERE <Condition>

Define COMMIT?

COMMIT saves all changes made by DML statements.

What is a primary key?

A Primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

What are foreign keys?

When a one table’s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables.

Foreign Key constraints enforce referential integrity.

What is CHECK Constraint?

A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

Is it possible for a table to have more than one foreign key?

Yes, a table can have many foreign keys and only one primary key.

What are the possible values for the BOOLEAN data field?

For a BOOLEAN data field, two values are possible: -1(true) and 0(false).

What is a stored procedure?

A stored procedure is a set of SQL queries which can take input and send back output.

What is identity in SQL?

An identity column in the SQL automatically generates numeric values. We can define a start and increment value of identity column.

What is Normalization?

The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more table and define relationships between them.

What is Trigger?

Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table)

How to select random rows from a table?

Using SAMPLE clause we can select random rows.

Example:

SELECT * FROM table_name SAMPLE(10);

Write a SQL SELECT query that only returns each name only once from a table?

To get the each name only once, we need to use the DISTINCT keyword.

SELECT DISTINCT name FROM table_name;

Explain DML and DDL?

DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE  are DML statements.

DDL stands for Data Definition Language. CREATE , ALTER, DROP, RENAME are DDL statements.

Can we rename a column in the output of SQL query?

Yes using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

Give the order of SQL SELECT?

Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.

Difference between TRUNCATE, DELETE and DROP commands?

DELETE removes some or all rows from a table based on the condition. It can be rolled back.

TRUNCATE removes ALL rows from a table by de-allocating the memory pages. The operation cannot be rolled back

DROP command removes a table from the database completely.

What is the difference between UNIQUE and PRIMARY KEY constraints?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys.

The primary key cannot contain Null values whereas Unique key can contain Null values.

What is a composite primary key?

Primary key created on more than one column is called composite primary key.

What is an Index?

An Index is a special structure associated with a table speed up the performance of queries. The index can be created on one or more columns of a table.