Basic SQL Interview Questions for Freshers

1. State the differences between HAVING and WHERE clauses.

Basis for WHERE HAVING
Comparison
Implemented in Row operations Column operations
Applied to A single row The summarized row or groups
Used for Fetching specific data from Fetching the entire data and
specific rows according to the separating according to the
given condition given condition
Aggregate Cannot have them Can have them
functions
Statements Can be used with SELECT, Cannot be used without a
UPDATE, and DELETE SELECT statement
GROUP BY Comes after the WHERE clause Comes before the HAVING
clause clause

2. What is SQL?
SQL stands for ‘Structured Query Language’ and is used for communicating with
databases. According to ANSI, SQL is the standard query language used for
maintaining relational database management systems (RDBMS) and also for
performing different operations of data manipulation on different types of data.
Basically, it is a database language that is used for the creation and deletion of
databases, and it can be used to fetch and modify the rows of a table and also
for multiple other things.

3. What are the subsets of SQL?

The main significant subsets of SQL are:

1. DDL(Data Definition Language)

2. DML(Data Manipulation Language)
 3. DCL(Data Control Language)
4. TCL(Transaction Control Language)

4. Explain the different types of SQL commands.

 Data Definition Language: DDL is that part of SQL which defines the
data structure of the database in the initial stage when the database is
about to be created. It is mainly used to create and restructure
database objects. Commands in DDL are:
o Create table
o Alter table
o Drop table
 Data Manipulation Language: DML is used to manipulate the already
existing data in the database. That is, it helps users retrieve and
manipulate the data. It is used to perform operations such as inserting
data into the database through the insert command, updating the data
with the update command, and deleting the data from the database
through the delete command.
 Data Control Language: DCL is used to control access to the data in the
database. DCL commands are normally used to create objects related to
user access and also to control the distribution of privileges among
users. The commands that are used in DCL are Grant and Revoke.
 Transaction Control Language: It is used to control the changes made
by DML commands. It also authorizes the statements to assemble in
 conjunction into logical transactions. The commands that are used in
TCL are Commit, Rollback, Savepoint, Begin, and Transaction.

5. What are the different types of database management systems?

Database Management System is classified into four types:

 Hierarchical database: It is a tree-like structure where the data is stored in

a hierarchical format. In this database, the parent may have many
children but a child should have a single parent.
 Network database: It is presented as a graph that allows many-to-many
relationships. This database allows children to have multiple children.
 Relational database: A relational database is represented as a table. The
values in the columns and rows are related to each other. It is the most
widely used database because it is easy to use.
 Object-Oriented database: The data values and operations are stored as
objects in this database. All these objects have multiple relationships
between them.

6. What are the usages of SQL?

These are the operations that can be performed using SQL database:

 Creating new databases

 Inserting new data
 Deleting existing data
 Updating records
 Retrieving the data
 Creating and dropping tables
 Creating functions and views
 Converting data types

7. What is a default constraint?

Constraints are used to specify some sort of rules for processing data and
limiting the type of data that can go into a table. Now, let’s understand the
default constraint.
The default constraint is used to define a default value for a column so that the
default value will be added to all the new records if no other value is specified.
For example, if we assign a default constraint for the E_salary column in the
below table and set the default value as 85000, then all the entries of this
column will have a default value of 85000 unless no other value has been
assigned during the insertion.

Now, let’s see how to set a default constraint. We will start off by creating a new
table and adding a default constraint to one of its columns.
Code:
create table stu1(s_id int, s_name varchar(20), s_marks int default
50)
select *stu1

Output:

Now, we will insert the records.

Code:
insert into stu1(s_id,s_name) values(1,’Sam’)
insert into stu1(s_id,s_name) values(2,’Bob’)
insert into stu1(s_id,s_name) values(3,’Matt’)
select *from stu1

Output:
8. What do you mean by table and field in SQL?
An organized data in the form of rows and columns is said to be a table. Here
rows and columns are referred to as tuples and attributes.
And the number of columns in a table is referred to as a field. In the record,
fields represent the characteristics and attributes.

9. What is a unique constraint?

Unique constraints ensure that all the values in a column are different. For
example, if we assign a unique constraint to the e_name column in the below
table, then every entry in this column should have a unique value.

First, we will create a table.

create table stu2(s_id int unique, s_name varchar(20))

Now, we will insert the records.

insert into stu2 values(1,’Julia’)
insert into stu2 values(2,’Matt’)
insert into stu2 values(3,’Anne’)

Output:
Career Transition

10. How would you find the second highest salary from the below
table?

Code:
select * from employee
select max(e_salary) from employee where e_salary not in (select
max(e_salary) from employee)

Output:

11. What is a primary key?

A primary key is used to uniquely identify all table records. It cannot have NULL
values, and it must contain unique values. A table can have only one primary key
that consists of single or multiple fields.
Now, we will write a query for demonstrating the use of a primary key for the
Employee table:
//
CREATE TABLE Employee (
ID int NOT NULL,
Employee_name varchar(255) NOT NULL,
Employee_designation varchar(255),
Employee_Age int,
PRIMARY KEY (ID)
);

Check out this video on SQL Tutorial for Beginners:

12. What is a Unique Key?
The key which can accept only the null value and cannot accept the duplicate
values is called Unique Key. The role of the unique key is to make sure that each
column and row are unique.
The syntax will be the same as the Primary key. So, the query using a Unique Key
for the Employee table will be:
//
CREATE TABLE Employee (
ID int NOT NULL,
Employee_name varchar(255) NOT NULL,
Employee_designation varchar(255),
Employee_Age int,
UNIQUE(ID)
);

13. What is the difference between Primary key and Unique Key?
Both Primary and Unique key carry unique values but the primary key can not
have a null value where the Unique key can. And in a table, there cannot be
more than one Primary key but unique keys can be multiple.

14. What is a foreign key?

A foreign key is an attribute or a set of attributes that references to the primary
key of some other table. Basically, it is used to link together two tables.
Let’s create a foreign key for the below table:

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
)
Courses you may like
15. What is an index?
Indexes help speed up searching in the database. If there is no index on any
column in the WHERE clause, then SQL Server has to skim through the entire
table and check each and every row to find matches, which might result in slow
operation on large data.
Indexes are used to find all rows matching with some columns and then to skim
through only those subsets of the data to find the matches.
Syntax:
CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

16. Explain the types of indexes.

Single-column Indexes: A single-column index is created for only one column of

a table.
Syntax:
CREATE INDEX index_name
ON table_name(column_name);

Composite-column Indexes: A composite-column index is an index created for

two or more columns of the table.
Syntax:
CREATE INDEX index_name
ON table_name (column1, column2)

Unique Indexes: Unique indexes are used for maintaining the data integrity of
the table. They do not allow multiple values to be inserted into the table.
Syntax:
CREATE UNIQUE INDEX index
ON table_name(column_name)

Now, let’s move on to the next question in this ‘Top SQL Interview Questions’
blog.

17. What are Entities and Relationships?

Entities: Entity can be a person, place, thing, or any identifiable object for which
data can be stored in a database.
For example: In a company’s database, employees, projects, salaries, etc can be
referred to as entities.
Relationships: Relationships between entities can be referred to as the
connection between two tables or entities.
For example: In a college database, the student entity and department entities
are associated with each other.
That is all in the section of Basic SQL practice questions. Let’s move on to the
next section of SQL intermediate interview questions.

Intermediate SQL Interview Questions

18. Why do we use the FLOOR function in SQL Server?

The FLOOR() function helps us to find the largest integer value to a given number
which can be an equal or lesser number.
19. State the differences between clustered and non-clustered
indexes.

 Clustered index: It is used to sort the rows of data by their key values. A
clustered index is like the contents of a phone book. We can open the
book at ‘David’ (for ‘David, Thompson’) and find information for all
Davids right next to each other. Since the data is located next to each
other, it helps a lot in fetching data based on range-based queries. Also,
the clustered index is actually related to how the data is stored. There is
only one clustered index possible per table.
 Non-clustered index: It stores data at one location and indexes at some
other location. The index has pointers that point to the location of the
data. As the index in the non-clustered index is stored in different
places, there can be many non-clustered indexes for a table.

Now, we will see the major differences between clustered and non-clustered
indexes:
Parameters Clustered Index Non-clustered Index
Used for Sorting and storing records Creating a logical order for data
physically in memory rows. Pointers are used for physical
data files
Methods for Stores data in the leaf Never stores data in the leaf nodes
storing nodes of the index of the index
Size Quite large Comparatively, small
Data accessing Fast Slow
Additional disk Not required Required to store indexes separately
space
Type of key By default, the primary key It can be used with the unique
of a table is a clustered constraint on the table that acts as a
index composite key
Main feature Improves the performance Should be created on columns used
of data retrieval in Joins

Now, in this ‘SQL Interview Questions and Answers’ blog, we will move on to the
next question.

Check out this video on SQL Tutorial for Beginners:

20. What do you know about CDC in SQL Server?
CDC is called change data capture. It captures recent INSERT, DELETE, and
UPDATE activity applied to SQL Server tables. It records changes to SQL Server
tables in a compatible format.

21. What is the difference between SQL and MySQL?

SQL MySQL
It is a structured query It is a database management system
language used in a database
It is used for query and It allows data handling, storing, and modifying
operating database system data in an organized manner.
SQL is always the same MySQL keeps updating
Only a single storage engine is MySQL supports multiple storage engines.
supported in SQL.
The server is independent in During backup sessions, the server blocks the
SQL database.

22. State the differences between SQL and PL/SQL.

SQL PL/SQL
SQL is a database structured query It is a programming language for a
language. database that uses SQL.
SQL is an individual query that is used PL/SQL is a block of codes used to write
to execute DML and DDL commands. the entire procedure or a function.
SQL is a declarative and data-oriented PL/SQL is a procedural and application-
language. oriented language.
It is mainly used for the manipulation of It is used for creating an application.
data.
It provides interaction with the It does not provide interaction with the
database server. database server.
It cannot contain PL/SQL code in it. It can contain SQL in it because it is an
extension of SQL.

23. What is the ACID property in a database?

The full form of ACID is Atomicity, Consistency, Isolation, and Durability. To check
the reliability of the transactions, ACID properties are used.

 Atomicity refers to completed or failed transactions, where transaction

refers to a single logical operation on data. This implies that if any
aspect of a transaction fails, the whole transaction fails and the
database state remains unchanged.
 Consistency means that the data meets all of the validity guidelines. The
transaction never leaves the database without finishing its state.
 Concurrency management is the primary objective of isolation.
 Durability ensures that once a transaction is committed, it will occur
regardless of what happens in between, such as a power outage, a fire,
or some other kind of disturbance.

24. How can we handle expectations in SQL Server?

TRY and CATCH blocks handle exceptions in SQL Server. We have to put the SQL
statement in the TRY block and write the code in the CATCH block to handle
expectations. If there is an error in the code inside the TRY block, the control will
automatically move to that CATCH block.

25. What is the need for group functions in SQL?

Group functions operate on a series of rows and return a single result for each
group. COUNT(), MAX(), MIN(), SUM(), AVG() and VARIANCE() are some of the
most widely used group functions.

26. What do you understand by a character manipulation

function?
Character manipulation functions are used for the manipulation of character
data types.
Some of the character manipulation functions are:
UPPER: It returns the string in uppercase.
Syntax:
UPPER(‘ string’)

Example:
SELECT UPPER(‘demo string’) from String;

Output:
DEMO STRING

LOWER: It returns the string in lowercase.

Syntax:
LOWER(‘STRING’)

Example:
SELECT LOWER (‘DEMO STRING’) from String

Output:
demo string

INITCAP: It converts the first letter of the string to uppercase and retains others
in lowercase.
Syntax:
Initcap(‘sTRING’)

Example:
SELECT Initcap(‘dATASET’) from String

Output:
Dataset

CONCAT: It is used to concatenate two strings.

Syntax:
CONCAT(‘str1’,’str2’)

Example:
SELECT CONCAT(‘Data’,’Science’) from String

Output:
Data Science

LENGTH: It is used to get the length of a string.

Syntax:
LENGTH(‘String’)
Example:
SELECT LENGTH(‘Hello World’) from String

Output:
11

Going ahead with this blog on ‘Top SQL Interview Questions,’ we will see the next
question.

27. What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a unique number
whenever a new record is inserted into a table.
Since the primary key is unique for each record, we add this primary field as the
AUTO_INCREMENT field so that it is incremented when a new record is inserted.
The AUTO-INCREMENT value starts from 1 and is incremented by 1 whenever a
new record is inserted.
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)
)

Now, let’s move on to the next question in this ‘Top SQL Interview Questions’
blog.

28. What is the difference between DELETE and TRUNCATE

commands?

 DELETE: This query is used to delete or remove one or more existing

tables.
 TRUNCATE: This statement deletes all the data from inside a table.
The difference between DELETE and TRUNCATE commands are as follows:

 TRUNCATE is a DDL command, and DELETE is a DML command.

 With TRUNCATE, we cannot really execute and trigger, while with DELETE,
we can accomplish a trigger.
 If a table is referenced by foreign key constraints, then TRUNCATE will not
work. So, if we have a foreign key, then we have to use the DELETE
command.

The syntax for the DELETE command:

DELETE FROM table_name
[WHERE condition];

Example:
select * from stu

Output:

delete from stu where s_name=’Bob’

Output:
The syntax for the TRUNCATE command:
TRUNCATE TABLE
Table_name;

Example:
select * from stu1

Output:

truncate table stu1

Output:

This deletes all the records from the table.

29. What is a “TRIGGER” in SQL?

The trigger can be defined as an automatic process that happens when an event
occurs in the database server. It helps to maintain the integrity of the table. The
trigger is activated when the commands like insert, update, and delete are given.
The syntax used to generate the trigger function is:
CREATE TRIGGER trigger_name

30. In SQL Server, how many authentication modes are there? And
what are they?
In SQL Server, two authentication modes are available. They are:

 Windows authentication mode: It allows authentication for Windows

but does not allow SQL server.
  Mixed mode: This mode enables both types, Windows and SQL Server, of
authentication.

31. What is the COALESCE function?

The COALESCE function takes a set of inputs and returns the first non-null value.
Syntax:
COALESCE(val1,val2,val3,……,nth val)

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

Output:
1

32. What do you understand by normalization and

denormalization?
Normalization and denormalization are basically two methods used in
databases.

Normalization is used in reducing data redundancy and dependency by

organizing fields and tables in databases. It involves constructing tables and
setting up relationships between those tables according to certain rules. The
redundancy and inconsistent dependency can be removed using these rules to
make it more flexible.
Denormalization is contrary to normalization. In this, we basically add redundant
data to speed up complex queries involving multiple tables to join. Here, we
attempt to optimize the read performance of a database by adding redundant
data or by grouping the data.

33. What are some common clauses used with SELECT queries in
SQL?
There are many SELECT statement clauses in SQL. Some of the most commonly
used clauses are:

 FROM

The FROM clause defines the tables and views from which data can be
interpreted. The tables and views listed must exist at the time the question is
given.

 WHERE

The WHERE clause defines the parameters that would be used to limit the
contents of the results table. You can test for basic relationships or for
relationships between a column and a series of columns using subselects.

 GROUP BY

This GROUP BY clause is commonly used for aggregate functions to produce a

single outcome row for each set of unique values in a set of columns or
expressions.

 ORDER BY

ORDER BY clause helps you to choose the columns on which the table’s result
should be sorted.

 HAVING
By using an aggregate function, the HAVING clause filters the results of the
GROUP BY clause.

34. What is wrong with the below-given SQL query?

SELECT gender, AVG(age) FROM employee WHERE AVG(age)>30 GROUP BY
gender

When we execute this command, we get the following error:

Msg 147, Level 16, State 1, Line 1

Aggregation may not appear in the WHERE clause unless it is in a subquery

contained in a HAVING clause or a select list, the column being aggregated is an
outer reference.
Msg 147, Level 16, State 1, Line 1
Invalid column name ‘gender’.

This basically means that whenever we are working with aggregate functions
and we are using GROUP BY, we cannot use the WHERE clause. Therefore,
instead of the WHERE clause, we should use the HAVING clause.
Also, when we are using the HAVING clause, GROUP BY should come first, and
HAVING should come next.
select e_gender, avg(e_age) from employee group by e_gender having
avg(e_age)>30

Output:

35. What is a function in SQL Server?

A function is an SQL Server database object. It is basically a set of SQL
statements that allow input parameters, perform processing, and return results
only. The function can only return a single value or table. The ability to insert,
update, and delete records in database tables is not available.

36. What do you know about the stuff() function?

The stuff function deletes a part of the string and then inserts another part into
the string starting at a specified position.
Syntax:
STUFF(String1, Position, Length, String2)

Here, String1 is the one that would be overwritten. Position indicates the
starting location for overwriting the string. Length is the length of the substitute
string, and String2 is the string that would overwrite String1.
Example:
select stuff(‘SQL Tutorial’,1,3,’Python’)

This will change ‘SQL Tutorial’ to ‘Python Tutorial’

Output:
Python Tutorial

37. What are Views? Give an example.

Views are virtual tables used to limit the tables that we want to display, and
these are nothing but the result of a SQL statement that has a name associated
with it. Since views are not physically present, they take less space to store.

Let’s consider an example. In the below employee table, say, we want to perform
multiple operations on the records with gender ‘Female’. We can create a view-
only table for the female employees from the entire employee table.
Now, let’s implement it on SQL Server.
Below is our employee table:
select * from employee

Now, we will write the syntax for view.

Syntax:
create view female_employee as select * from employee where
e_gender=’Female’
select * from female_employee

Output:

38. What are the third-party tools that are used in an SQL Server?
The following is the list of third-party tools:

 SQL CHECK
 SQL DOC 2
 SQL Backup 5
 SQL Prompt
 Litespeed 5.0

39. What are the types of views in SQL?

In SQL, the views are classified into four types. They are:

1. Simple View
A view that is based on a single table and does not have a GROUP BY clause or
other features.

1. Complex View

A complex view is one that is built from several tables and includes a GROUP BY
clause as well as functions.

1. Inline View: A view that is built on a subquery in the FROM Clause, which
provides a temporary table and simplifies a complicated query.
2. Materialized View: A view that saves both the definition and the details.
It builds data replicas by physically preserving them.

Advanced SQL Interview Questions for Experienced

Professionals

40. What is a stored procedure? Give an example.

A stored procedure is a prepared SQL code that can be saved and reused. In
other words, we can consider a stored procedure to be a function consisting of
many SQL statements to access the database system. We can consolidate
several SQL statements into a stored procedure and execute them whenever
and wherever required.
A stored procedure can be used as a means of modular programming, i.e., we
can create a stored procedure once, store it, and call it multiple times as
required. This also supports faster execution when compared to executing
multiple queries.
Syntax:
CREATE PROCEDURE procedure_name
AS
Sql_statement
GO;
To execute we will use this:
EXEC procedure_name

Example:
We are going to create a stored procedure that will help extract the age of the
employees.
create procedure employee_age
as
select e_age from employee
go
Now, we will execute it.
exec employee_age

Output:

41. What do you know about Joins? Define different types of Joins.
The Join clause is used to combine rows from two or more tables based on a
related column between them. There are various types of Joins that can be used
to retrieve data, and it depends upon the relationship between tables.
There are four types of Joins:

 Inner Join: Inner Join basically returns records that have matching values
in both tables.
 Left Join: Left Join returns rows that are common between the tables and
all the rows of the left-hand-side table, i.e., it returns all the rows from
the left-hand-side table even if there are no matches available in the
right-hand-side table.
 Right Join: Right Join returns rows that are common between the tables
and all the rows of the right-hand-side table, i.e., it returns all the rows
from the right-hand-side table even if there are no matches available in
the left-hand-side table.
 Full Join: Full Join returns all the rows from the left-hand-side table and all
the rows from the right-hand-side table.
42. Where are usernames and passwords stored in SQL Server?
In SQL Server, usernames and passwords are stored in the main database in the
sysxlogins table.

43. What are the types of relationships in SQL Server databases?

Relationships are developed by interlinking the column of one table with the
column of another table. There are three different types of relationships which
are as follows:

 One-to-onerelationship
 Many-to-one relationship
 Many-to-many relationship

44. Explain Inner Join.

Inner Join basically gives us those records that have matching values in two
tables.
Let us suppose, we have two tables Table A and Table B. When we apply Inner
Join on these two tables, we will get only those records that are common to both
Table A and Table B.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_x=table2.column_y;

Example:
select * from employee
select * from department

Output:
Now, we would apply Inner Join to both of these tables, where the ‘e_dept’
column in the employee table is equal to the ‘d_name’ column of the department
table.
Syntax:
select employee.e_name, employee.e_dept, department.d_name,
department.d_location
from employee inner join department

on
employee.e_dept=department.d_name

Output:

After Inner Join, we have only those records where the departments match in
both tables. As we can see, the matched departments are Support, Analytics,
and Sales.

45. State the differences between views and tables.

Views Tables
It is a virtual table that is extracted A table is structured with a set number of
from a database. columns and a boundless number of rows.
Views do not hold data themselves. A table contains data and stores the data
in databases.
A view is also utilized to query A table holds fundamental client
certain information contained in a information and the cases of a
few distinct tables. characterized object.
In a view, we will get frequently In a table, changing the information in the
queried information. database changes the information that
appears in the view

46. Mention different types of replication in SQL Server?

In SQL Server, three different types of replications are available:

 Snapshot replication
 Transactional replication
 Merge replication

47. What do you understand about a temporary table? Write a

query to create a temporary table.
A temporary table helps us store and process intermediate results. These
temporary tables are created and can be automatically deleted when they are
no longer used. They are very useful in places where we need to store
temporary data.
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:

48. Explain the difference between OLTP and OLAP.

OLTP: It stands for Online Transaction Processing, and we can consider it to be a
category of software applications that is efficient for supporting transaction-
oriented programs. One of the important attributes of the OLTP system is its
potential to keep up the consistency. The OLTP system often follows
decentralized planning to keep away from single points of failure. This system is
generally designed for a large audience of end-users to perform short
transactions. Also, queries involved in such databases are generally simple, need
fast response time, and in comparison, return only a few records. So, the
number of transactions per second acts as an effective measure for those
systems.

OLAP: OLAP stands for Online Analytical Processing, and it is a category of

software programs that are identified by a comparatively lower frequency of
online transactions. For OLAP systems, the efficiency of computing depends
highly on the response time. Hence, such systems are generally used for data
mining or maintaining aggregated historical data, and they are usually used in
multi-dimensional schemas.

49. What do you understand by Self Join?

Self Join in SQL is used for joining a table with itself. Here, depending upon some
conditions, each row of the table is joined with itself and with other rows of the
table.
Syntax:
SELECT a.column_name, b.column_name
FROM table a, table b
WHERE condition
Example:
Consider the customer table given below.
ID Name Age Address Salary
1 Anand 32 Ahmedabad 2,000.00
2 Abhishek 25 Delhi 1,500.00
3 Shivam 23 Kota 2,000.00
4 Vishal 25 Mumbai 6,500.00
5 Sayeedul 27 Bhopal 8,500.00
6 Amir 22 MP 4,500.00
7 Arpit 24 Indore 10,000.0
0

We will now join the table using Self Join:

SQL> SELECT [Link], [Link], [Link]
FROM CUSTOMERS a, CUSTOMERS b
WHERE [Link] < [Link];

Output:
ID Name Salary
2 Anand 1,500.0
0
2 Abhishek 1,500.0
0
1 Vishal 2,000.0
0
2 Vishal 1,500.0
0
3 Vishal 2,000.0
0
6 Vishal 4,500.0
0
1 Sayeedul 2,000.0
0
2 Sayeedul 1,500.0
 0
3 Sayeedul 2,000.0
0
4 Sayeedul 6,500.0
0
6 Sayeedul 4,500.0
0
1 Amir 2,000.0
0
2 Amir 1,500.0
0
3 Amir 2,000.0
0
1 Arpit 2,000.0
0
2 Arpit 1,500.0
0
3 Arpit 2,000.0
0
4 Arpit 6,500.0
0
5 Arpit 8,500.0
0
6 Arpit 4,500.0
0

50. Which command do we use to find out the SQL Server version?
The following command is used to identify the version of SQL Server:
Select SERVERPROPERTY('productversion')

51. What is the difference between Union and Union All operators?
The Union operator is used to combine the result set of two or more select
statements. For example, the first select statement returns the fish shown in
Image A, and the second returns the fish shown in Image B. Then, the Union
operator will return the result of the two select statements as shown in Image A
U B. Also, if there is a record present in both tables, then we will get only one of
them in the final result.

Syntax:
SELECT column_list FROM table1

Union:
SELECT column_list FROM table2

Now, we will execute it in the SQL server.

These are the two tables in which we will use the Union operator.

select * from student_details1

Union:
select * from student_details2

Output:
Now, Union All gives all the records from both tables including the duplicates.

Let us implement in it the SQL server.

Syntax:
select * from student_details1

Union All:
select * from student_details2

Output:

52. Can we link SQL Server with others?

SQL Server allows the OLEDB provider, who provides the link, to connect to all
databases.
Example: Oracle, I have an OLEDB provider that has a link to connect with a SQL
server group.
53. What is SQL Server Agent?
SQL Server agent plays an important role in the daily work of SQL Server
Administrators (DBAs.) This is one of the important parts of Microsoft’s SQL
Server. The aim of the server agent is to easily implement tasks using a
scheduler engine that enables tasks to be performed at scheduled times. SQL
Server Agent uses SQL Server to store scheduled management task information.

54. What is Cursor? How to use a Cursor?

A database Cursor is a control that allows you to navigate around the table’s
rows or documents. It can be referred to as a pointer for a row in the set of
rows. Cursors are extremely useful for database traversal operations like
extraction, insertion, and elimination.

 After any variable declaration, DECLARE a cursor. A SELECT Statement

must always be aligned with the cursor declaration.
 To initialize the result set, OPEN statements must be called before
fetching the rows from the result table.
 To grab and switch to the next row in the result set, use the FETCH
statement.
 To deactivate the cursor, use the CLOSE expression.
 Finally, use the DEALLOCATE clause to uninstall the cursor description and
clear all the resources associated with it.

Here is an example SQL cursor

DECLARE @name VARCHAR(50)

DECLARE db_cursor CURSOR FOR

SELECT name
From [Link]
WHERE employee_name IN (‘Jay’, ‘Shyam’)
OPEN db_cursor
FETCH next
FROM db_cursor
Into @name

Close db_cursor
DEALLOCATE db_cursor

55. What do you know about magic tables in SQL Server?

A magic table can be defined as a provisional logical table that is developed by
an SQL Server for tasks such as insert, delete, or update (D.M.L) operations. The
operations recently performed on the rows are automatically stored in magic
tables. They are not physical tables; they are just temporary internal tables.

56. What is the use of the Intersect operator?

The Intersect operator helps combine two select statements and returns only
those records that are common to both the select statements. So, after we get
Table A and Table B over here and if we apply the Intersect operator on these
two tables, then we will get only those records that are common to the result of
the select statements of these two.

Syntax:
SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2
Now, let’s see an example for the INTERSECT operator.
select * from student_details1
select * from student_details1

Output:

select * from student_details1

intersect
select * from student_details2

Output:

57. How can you copy data from one table into another?

Here, we have our employee table.

We have to copy this data into another table. For this purpose, we can use the
INSERT INTO SELECT operator. Before we go ahead and do that, we would have
to create another table that would have the same structure as the above-given
table.
Syntax:
create table employee_duplicate(
e_id int,
e_name varchar(20),
e_salary int,
e_age int,
e_gender varchar(20)
e_dept varchar(20)
)

For copying the data, we would use the following query:

insert into employee_duplicate select * from employees

Let us have a glance at the copied table.

select * from employee_duplicate

Output:
58. What is the difference between BETWEEN and IN operators in
SQL?
To represent rows based on a set of values, use the BETWEEN operator. The
values may be numbers, text, or dates. The BETWEEN operator returns the total
number of values that exist between two specified ranges.
To search for values within a given range of values, the IN condition operator is
used. If we have more than one value to choose from, we use the IN operator.

59. Describe how to delete duplicate rows using a single statement

but without any table creation.
Let’s create an Employee table where column names are ID, NAME,
DEPARTMENT, and EMAIL. Below are the SQL scripts for generating the sample
data:
CREATE TABLE EMPLOYEE
(
ID INT,
NAME Varchar(100),
DEPARTMENT INT,
EMAIL Varchar(100)
)

INSERT INTO EMPLOYEE VALUES (1,'Tarun',101,'tarun@[Link]')

INSERT INTO EMPLOYEE VALUES (2,'Sabid',102,'sabid@[Link]')
INSERT INTO EMPLOYEE VALUES
(3,'Adarsh',103,'adarsh@[Link]')
INSERT INTO EMPLOYEE VALUES
(4,'Vaibhav',104,'vaibhav@[Link]')

--These are the duplicate rows

INSERT INTO EMPLOYEE VALUES (5,'Tarun',101,'tarun@[Link]')

INSERT INTO EMPLOYEE VALUES (6,'Sabid',102,'sabid@[Link]')
We can see the duplicate rows in the above table.
DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE [Link] = [Link] AND
[Link] > [Link]

The SQL query above will delete the rows, where the name fields are duplicated,
and it will retain only those unique rows in which the names are unique and the
ID fields are the lowest. That is, rows with IDs 5 and 6 are deleted, whereas rows
with IDs 1 and 2 are retained.

60. Can you identify the employee who is having the third-highest
salary from the given Employee table (with salary-related data)?
Consider the below Employee table. In the table, ‘Sabid’ has the third-highest
salary (60000).
Name Salary
Tarun 70000
Sabid 60000
Adarsh 30000
Vaibhav 80000

Below is a simple query to find out the employee who has the third-highest
salary. The functions RANK, DENSE RANK, and ROW NUMBER are used to obtain
the increasing integer value by imposing the ORDER BY clause in the SELECT
statement, based on the ordering of rows. The ORDER BY clause is necessary
when we use RANK, DENSE RANK, or ROW NUMBER functions. On the other
hand, the PARTITION BY clause is optional.
WITH CTE AS
(
SELECT Name, Salary, RN = ROW_NUMBER() OVER (ORDER BY Salary
DESC) FROM EMPLOYEE
)
SELECT Name, Salary FROM CTE WHERE RN =3
61. What is the difference between HAVING and WHERE clauses?
The distinction between HAVING and WHERE clauses in SQL is that while the
WHERE clause cannot be used with aggregates, we use the HAVING clause with
the aggregated data. The WHERE clause works on the data from a row and not
with the aggregated data.
Let’s consider the Employee table below.
Name Department Salary
Tarun Production 50000
Tarun Testing 60000
Sabid Marketing 70000
Adarsh Production 80000
Vaibhav Testing 90000

The following would select the data on a row-by-row basis:

SELECT Name, Salary FROM Employee WHERE Salary >=50000

Output:
Name Salary
Tarun 50000
Tarun 60000
Sabid 70000
Adarsh 80000
Vaibhav 90000

The HAVING clause, on the other hand, operates on aggregated results.

SELECT Department, SUM(Salary) AS total FROM Employee GROUP BY
Department

Output:
Department Total
Marketing 70000
Production 130000
Testing 150000
Now, let’s see the output when we apply HAVING to the above query.
SELECT Department, SUM(Salary) AS total FROM Employee GROUP BY
Department HAVING SUM(Salary)>70000

Output:
Department Total
Production 130000
Testing 150000

62. Explain database white box testing and black box testing.
The white box test method mainly deals with the internal structure of a
particular database, where users hide specification details. The white box testing
method involves the following:

 As the coding error can be detected by testing the white box, it can
eliminate internal errors.
 To check for the consistency of the database, it selects the default table
values.
 This method verifies the referential integrity rule.
 It helps perform the module testing of database functions, triggers, views,
and SQL queries.

The black box test method generally involves interface testing, followed by
database integration. It includes:

 Mapping details
 Verification of the incoming data
 Verification of the outgoing data from the other query functions

63. What is the difference between DELETE and TRUNCATE in SQL?

Consider the following Student table.

If we want to delete the rows (tuples) of this table, we can use the delete
command. When we run the following command, it will delete all the rows.
Delete from Student;
We can also delete a specific row using the delete command as shown below:
Delete from Student where ID=1;

The truncate command is also used to delete the rows. However, in truncate, we
cannot delete a specific row. Thus, when we run the following command, it will
delete all the rows of the table.
Truncate Student;

Let’s say, we have executed rollback before committing changes, but in the case
of truncate, it is not possible because there is no log file generated (for
understanding, we can assume truncate to be something like ‘Shift + Delete’).
In the case of delete, we can perform rollback before committing the changes.
Hence, with the delete command, we have the option of recovering the original.
Also, the delete command is slower than the truncate command.