Top 100 SQL Server Relational Database interview questions and Answers for Experienced and Fresher

Q.5. What is RDBMS?

Ans. RDBMS stores data in the form of a group of multiple tables. The
relationships are built by connecting the common fields of relevant tables.
For example, MS SQL Server, MySQL, Oracle, IBM DB2, and Amazon Redshift, are
some examples of RDBMS.

Q.6. Why RDBMS is more popular than DBMS?

Or

Write the difference between DBMS and RDBMS.

Ans. Following are the main points of difference between DBMS and
RDBMS.

Accessing data

In DBMS, Data elements are accessed separately whereas in RDBMS, multiple
data elements are accessible at the same time.

Relationship Between Data

In DBMS, No relationship occurs between data in data tables, but in RDBMS,
the data in multiple tables are interrelated with each other.

Normalization

In DBMS, Normalization is not present, but in RDBMS, Normalization is
present.

Distributed Database

In DBMS, a distributed database is not supported, but in RDBMS, a
distributed database is supported.

Data Storage Format

In DBMS, Data may be stored in either a navigational or hierarchical format
whereas, in RDBMS, data is stored in a tabular format i.e. headers as column
names and the rows having relevant values.

Volume of Data:

In DBMS, a small amount of data can be stored and managed, but in
RDBMS, a larger amount of data can be stored and managed as per the
storage space available in the local drive.

Data Redundancy

In DBMS, data redundancy occurs when the volume of data increases,
but in RDBMS,

keys and indexes don’t permit data redundancy.

Users

In RDBMS, a single user is allowed whereas, in RDBMS, multiple users
are allowed to work with various tables at the same time.  

Data Fetching

In DBMS, data fetching is slower for a large quantity of data whereas in
RDBMS data fetching is faster.

Data Security

In DBMS, the data manipulation process has a lower security level whereas,
in RDBMS,  multiple data security levels are applied.

Software and Hardware needs

In DBMS, the requirement of software and hardware is lesser than RDBMS.

Some examples of DBMS are Window Registry, XML, etc.

Some examples of RDBMS are Microsoft Access, SQL Server,
MySQL, Oracle, PostgreSQL, etc.

Q.7. What is SQL?

Ans. SQL (Structured Query Language) is a most ideal and crucial
language for RDBMS and is valuable in managing organized data that
contains entities or variables with proper relations through common columns
between them. SQL is utilized for interaction or data manipulation with
RDBMS databases.

Q.8. What is data manipulation?

Ans. Data manipulation means creation, modification, and deletion of
a database or a table. Also, SQL is used for searching and reporting data
from databases.

Q.9. What is normalization and describe its different forms?

Ans. Normalization is utilized in decreasing data redundancy and
dependency by establishing multiple fields and tables in specific databases.
It includes designing tables and building relationships between the same
tables as per certain rules. The redundancy and unpredictable dependency can
be eliminated by applying these rules to turn normalization more
adaptable.

Different types of normalization

1st Normal Form:

In this form, each attribute in relation has single-valued data. It doesn’t
contain a multi-valued attribute.

2nd Normal Form:

When the relation in 1st normal form does not possess any partial dependency
or non-prime attribute that depends on any specific subset of any candidate
key of the table. A single-column primary key can resolve this issue
well.

3rd Normal Form:

In this form, the second normal form doesn’t have any transitive reliability
between the attributes that are non-prime. It means the non-prime attributes
are determined only through the candidate keys and not by other non-prime
attributes.

Boyce-Codd Normal Form (BCNF):

In Boyce-Codd normal form or BCNF, the 3rd normal form contains a left-hand
super key for each non-trivial valuable reliability in form X –> Y.

Q.10. What is denormalization?

Ans. In Denormalization, the redundant data is enriched to make
complex queries faster that have multiple tables and those ought to be
joined. Improvement of the readability of a database is endeavored by adding
or grouping repetitive copies of data.

Q.11. What are Joins in SQL? Describe the types of joins.

Ans. Joins in SQL, play in a combination of rows from two or more
data tables containing relevant columns between them.
Different types
of Joins are implemented in databases to obtain data, which depend on the
requirement of reporting and the relationship between tables.
 
Mainly
four types of Joins are implemented in SQL:

Inner Join
This join is used to fetch the records that have
conforming values in both the tables that are included in the join.
‘SELECT
* FROM Table_A1 INNER JOIN Table_B1’;

Right (Outer) Join: Use of Right join is to retrieve all the records
or rows from the right table and the matched records from the left table.
‘SELECT
* FROM Table_AA1 RIG HT JOIN Table_B B1 ON A1.col = B1.col’;

Left (Outer) Join: Left join is incorporated to get all the records
or rows from the left side table and the matched record from the right
table.
‘SELECT*FROM Table_A A1 LEFT JOIN Table_B B1ON A1.col = B1.col’;

Full (Outer) Join: Full join is utilized to obtain the records that
match the records either in the right or left table.
SELECT * FROM
Table_A A1 FULL JOIN Table_B B1 ON A1.col = B1.col; 

Q.12. Write the subsets of SQL? Explain them.

Or

What are the types of SQL queries? Explain them.

Ans. Mainly 4 types of SQL queries are used.

A.) Data Definition Language (DDL)
The DDL queries are
designed with the use of suitable SQL commands to define the database
structure and modify it. Some crucial SQL commands are given below.

CREATE command is used to build up tables, databases, schema,
etc.

DROP COLUMN command is used to drop single or multiple columns from
any table.

DROP command is used to decline tables and other unnecessary
database objects.

ALTER command brings changes to the description of various database
objects.

ADD COLUMN command is used to add any particular column to the table
schema.  

TRUNCATE command removes tables, procedures, views, tables, and
other useless database objects

B.) Data Manipulation Language (DML)

These DML SQL queries are utilized to alter data in a database.

‘SELECT INTO’ command helps in the selection of data from one table
and insertion of the same data into another table.

INSERT command helps in inserting data into a targeted table

UPDATE command helps in modifying the value of any data in the
database

DELETE command helps in the removal of data from a table 

C.) Data Control Language (DCL)

These DCL SQL queries handle the database access rights and
authorization control.

GRANT command allows access rights to the different objects of a
database

REVOKE command removes approval from the objects of the database. 

D.) Transaction Control Language (TCL)

TCL is a set of SQL commands that practically controls all
the transactions in a targeted database and the changes brought by the
DML statements. TCL permits various SQL statements to be grouped into
logical transactions.

COMMIT command helps in committing an irreversible transaction,
i.e., the earlier image of the database before the transaction cannot be
obtained.

ROLLBACK command helps in reverting the steps in a specific
transaction when it faces an error.

‘SET TRANSACTION command establishes the aspects of the transaction.

SAVEPOINT command helps in setting a savepoint in the particular
transaction to which the rollback command can be implemented.

Q.13. What are the major applications of SQL?

Ans. Following are the
major applications
of SQL.

Writing SQL data integration scripts

Setting and running database analytical queries

Obtaining subsets of data within a database for analytical applications and
transaction processes.

Inserting, updating, and eliminating rows and columns of data in a table.

Q. 14. What is a constraint in SQL?

Ans. Generally, SQL constraints are utilized to apply some type of
rules for data processing and restrict the data type at the time of adding
or altering a table.

Q.15. What is a default constraint in SQL?

Ans. A default constraint in SQL is utilized to establish a default
value for a specific column so that it is added to all new data if no other
value is mentioned.

For instance: if we assign a default constraint for the S_fees column in the
following table and set the default value to 1200, then all the data of this
column will contain the default value of 1200 unless no more value has been
assigned at the time of the inserting function.

Q. 16. How can you set a default constraint?

Ans. When we will start creating a new table and assigning a default
constraint to any one column of it?

Q.17. What is a UNIQUE constraint?

Ans. The Unique constraints in SQL make sure that all the values
entered in a column are different and it doesn’t allow Duplicate records.

Q. 18. How can we assign unique constraint?

Ans. If we assign a unique constraint to the S_name column in
the following table, then each entry in this column should possess a unique
value.

Steps

First, we have to create a table name Stu1.

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

Now, we will add the records.

Q. 19. What is a primary key?

Ans. A primary key is basically incorporated to comprehend all table
data uniquely. It can’t contain NULL values but must possess unique values.
Only one primary key can be provided in one table, and it doesn’t make a
difference whether it carries single or multiple fields, for turning it into
a composite key.

The following query demonstrates the implementation of a primary key for the
student table:

Q.20. What is a unique key?

Ans. The key that can receive only a null value and cannot receive
duplicate values is known as a unique key. A unique key assures that all
columns and rows are unique.

Q. 21. Write down the difference between HAVING and WHERE clauses?

Ans. In SQL queries, the WHERE clause is utilized with aggregates,
whereas the HAVING clause is utilized with the aggregated data. Generally,
the WHERE clause functions on the specific data that are retrieved from a
row and not with the entire data.

Q.22. Differentiate between white box testing and black box testing of the
database.

Ans.

White Box testing

The white box testing procedure mostly deals with the internal structure of
a specific database, where end-users keep specification details in hidden
form. The white box test technique includes the following:

The white box can detect the coding error, and the internal errors can be
eliminated.

In this testing method, the consistency of the database could be observed,
as the default table values will be selected. Also, the referential
integrity regulation can be verified.

The database module performance such as functions, performance, triggers,
views, and SQL queries will be tested thoroughly as well.

Black box testing

Basically, the black box testing technique conducts interface testing, along
with database integration. The following are tested by the black box test
method.

Mapping details

Testing of incoming data for confirmation.

Testing of outgoing data (through the other queries)

Q.23. How can you create a new empty table with a similar structure as
another table?

Ans. This can be accomplished by retrieving the records of one table into a
new table throughout the INTO operator while making a WHERE clause false for
whole records. In this .method, SQL organizes the new table with a similar
structure to obtain the fetched records.

However, no records will be obtained because of the FALSE condition of the
WHERE clause. Hence, nothing is inserted into the new table, thus developing
an empty table.
————————————-
SELECT *
INTO Stud_copy
FROM Stud WHERE 1 = 2;
———————————-

Q.24. Write the difference between the primary key and the unique key?

Ans. Both primary and unique keys carry unique values but a primary
key cannot contain a null value, while a unique key can contain. In a table,
more than one primary key can’t be applied, but multiple unique keys can be
applied.

Q.25. What is a foreign key?

Ans. A foreign key is a single attribute or a set of attributes that become
the reference of some other table’s primary key. Usually, a foreign key
links two tables together.

By following queries, we can make a foreign key:

Q.26. What are the main subsets of SQL?

Ans. The main subsets of SQL are Data Definition Language
(DDL), Data Manipulation Language (DML), Data Control Language
(DCL), and Transaction Control Language (TCL).

Q.27. What is an index?

Ans. Indexes allow faster searching within a database. If an index is
not available on a column in the WHERE clause, then the search result in
large data may be delayed.

Hence, Indexes help find all rows with some matched columns.
—————–
Syntax:
CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)
—————–

Q.28. How many types of indexes are available?

Ans. Mainly 3 types of indexes are available as Single-column
Indexes, composite-column indexes, and unique indexes.

Q.29. Explain each type of index.

Ans. A single-column index is generated for only a single column of a
table.
Syntax:

Q.30. What are entities and relationships?

Ans. Entities: An entity can be a place, person, thing, or any
detectable object for which data is stored in a database.

For illustration, in a company database, the entities are employees,
salaries, projects, etc. can be considered as entities.

Relationships: A relationship is a link between two or more tables or
entities.

For illustration, in a college database, the student entity, subject entity,
and department entities are correlated with each other.

Q.31. What are SQL operators?

Ans. SQL operators in queries are the specific characters or keywords
that perform certain functions or operations. The operators can be utilized
within the WHERE clause of SQL commands. The SQL operators search and obtain
or filter the data as per a given condition.

Q.32. How many types of SQL operators are there?

Ans. Mainly 6 types of SQL operators are found as follows.

Arithmetic Operators, logical operators, comparison operators, bitwise
operators, compound operators, and string operators.

Q.33. Describe each type of SQL operator?

Ans. Arithmetic Operators

These operators are used for mathematical functions on numerical data

i) subtraction (-)
ii) addition (+)
iii) division (/)
iv)
multiplication (*)
v) remainder/modulus (%)

Logical Operators:

These operators help in assessing the expressions and return outcomes in
True or False.

ALL, ANY, AND, AN, IS NULL,
EXISTS, BETWEEN, IN, LIKE, NOT,
OR, UNIQUE

Comparison Operators:

These operators help in conducting a comparison between two values and
checking whether they are equal or not.

equal to (=)

not equal to (!= or <>)

greater than (>)

less than (<),

greater than or equal to (>=)

less than or equal to (<=)

not greater than (!>)

not less than (!<)

Bitwise Operators

These operators help in performing bit alteration between two integer type
expressions. It first converts integers into binary bits and then
implemented operators.

AND (& symbol),

NOT (~)   

OR (|, ^),

Compound Operators:

These operators are used for operations on a variable before setting the
result.

subtract equals (-=)

Add equals (+=)

divide equals (/=)

multiply equals (*=)

modulo equals (%=)

String Operators:

These operators are used for concatenation and structure fitting of strings.

+= (String concatenation assignment)

+ (String concatenation)

% (Wildcard)

[^] (Character(s) not to match)

[] (Character(s) matches)

_ (Wildcard match one character)

Q.34. What is data integrity?

Ans. Data integrity is the confirmation of the exactness and
consistency of data over its entire life cycle. It is a crucial aspect of
the design, execution, and method of systems that store, process, or obtain
data.

Data integrity also specifies integrity constraints for implementing
significant business rules on data when it is joined into a table or
database or any application.

Q.35. What is a data warehouse?

Ans. A data warehouse is a huge size store of accumulated data, from
a broad range of sources, within an organization. The data warehouse helps
in reporting and driving business decisions.

Q.36. How could you obtain the highest salary from the employee table?

Ans. With the following query, we could find the highest salary from the
employee table.

Q.37. What is the utility of the FLOOR function in SQL Server?

Ans. The FLOOR() function in SQL, assists in obtaining the largest
integer value to a specific number, that might be equal or lesser.

Q.38. What is Clustered Index?

Ans. This index is used to sort the data rows by their adequate values. A
clustered index is similar to the contents in pages of a phone book. Since
the data is placed next to each other, the clustered index helps a lot in
retrieving the data based on particular range-based queries. A clustered
index truly displays an arrangement of data and only one clustered index is
available per single table.

Q.39. What is a Non-clustered Index?

Ans. This index keeps data at one location and indexes at a different
location. The index has pointers that target the right location of the data.
Since the non-clustered indexes are placed in a separate place, more than
one non-clustered index can be applied to a table.

Q.40. What are the differences between clustered and non-clustered indexes?

Ans. Following are the major differences between clustered and
non-clustered indexes:

Utility

Clustered Index helps in grouping and storing data records, recorded in
memory physically.

A non-clustered index helps in generating a logical order for data rows;
pointers are utilized for physical data files.

Storing Methods

Clustered Index Keeps data in the index leaf nodes.

Non-clustered Index never keeps data in the index leaf nodes.

Size of index

Clustered index is quite large.

Non-clustered Index is Comparatively, small

Speed of Data Access

Clustered index has a faster speed of data access.

Non-clustered Index has slow performance.

Extra Disk Space

Clustered index doesn’t require extra disk space.  separately

Non-clustered index needs extra disk space.

Key types

Clustered index can be also a primary key of a table by default.

Non-clustered index can be utilized with unique constraints pertinent to the
specific table that performs as a composite key.

Main Feature

Clustered index Improves the performance of data interaction.

Non-clustered index should be established on columns in Joins.

Q.41. What is CDC in SQL Server?

Ans. CDC (Change Data Capture) catches activity in SQL Server table
such as INSERT, UPDATE,  and DELETE. It records
modifications in SQL Server tables in an understandable format.

Q.42. Find the difference between SQL and MySQL?

Ans. Following are the differences between SQL MySQL.

i) SQL (Structured Query Language) is used in a database for
multipurpose.
MySQL is a DBMS (Database Management System).

ii) SQL is utilized for querying and manipulating database system
MySQL
enables us to control, store, and alte in an organized way.

iii) SQL is often the same.
MySQL Keeps changing.

iv) SQL assists only a single storage engine
MySQL assists multiple
storage engines

V) SQL server is independent
MySQL  blocks the database at the
time of backup sessions.

Q. 43. What are the differences between SQL and PL/SQL?

Ans. SQL is a structured query language for database.

PL/SQL is a programming language to work with SQL database.

SQL query helps execute DDL and DML commands.

PL/SQL is a block of codes that helps write the whole procedure or a
required function

SQL is a declarative and database-oriented language.

PL/SQL is a procedural and application-based language.

SQL is mostly used for the data manipulation purpose.

PL/SQL is utilized for developing applications.

SQL interacts with the database server.

PL/SQL does not interact with the database server.

SQL cannot possess PL/SQL code

PL/SQL can possess SQL because it is an expansion of SQL.

Q.44. What do you mean by ACID property of a database?

Ans. ACID is meant for Atomicity, Consistency, Isolation, and
Durability. ACID properties are utilized to assess the reliability of
database transactions.

Q.45. What is the Atomicity property of the database?

Ans. Atomicity property refers to the finalized of failed
transactions, where a single transaction on data occurs. This property
states that if any element of a transaction fails, the entire transaction
fails and the database status is unchanged.

Q.46. What is the consistency property of the  database?

Ans. Consistency refers to the successful passing of all validation
rules by data because each transaction leaves the database with proper
accomplishment.

Q.47.What is the isolation property of the  database?

The isolation property has the main object of Concurrency management in the
database.

Q.48. What is the Durability property of the  database?

Ans. The durability makes sure that once a transaction is completed, it
occurs regardless of what arises in between such as a power breakdown, fire,
or some other type of disturbance.

Q.49. What is the use of group functions in SQL?

Ans. Most of the group functions conduct on a number of rows and return a
distinct result for every group. COUNT(), SUM(), MAX(), MIN(), VARIANCE(,)
and AVG(), are some of the most frequently conducted group functions.

Q.50. Define a character manipulation function?

Ans. The character data types are manipulated by the character
manipulation functions.

Some manipulation functions of character are as follows:
The UPPER
function returns the string layout in uppercase format.

Q.51. Define AUTO_INCREMENT.

Ans. AUTO_INCREMENT is utilized in SQL to generate a new unique
number every time a new record is added to a table.

Example: The primary key is unique as the AUTO_INCREMENT field is added so
that it is incremented at the time of adding a new record.

The first number of the AUTO-INCREMENT is 1 which is increased by 1
with  the addition of each new record and subtracted by 1 with removal
of one existing record.

Q.52. Write the difference between DELETE and TRUNCATE commands.

Ans. DELETE command is utilized to delete one or more existing
records or tables.

TRUNCATE deletes only all the existing data from a table and the
table remains empty.

DELETE is a type of DML command.

TRUNCATE is a type of DDL command.

DELETE command can assist in accomplishment of a trigger.

TRUNCATE command cannot truly assist in execution and trigger,

DELETE command is used while we have a foreign key constraint.

TRUNCATE command does not work, if a table is linked by foreign key
constraints

DELETE command syntax is given below:

Q.53. Write down the difference between DROP and TRUNCATE commands.

Ans. Drop
If we drop a table, all things linked with the same
table are dropped as well. This includes the relationships made on the table
with other tables, access authority, and permission. 

To create and get the table used again in its original structure, all the
elements related to the table must be redefined.

Truncate

Nevertheless, if a table gets truncated, there are no such issues as given
above. The table preserves its original pattern.

Q.54. Define ‘TRIGGER’  in SQL.

Ans. The trigger is an automatic procedure or function that occurs
with occurance of an event within the database server. It assists in
retaining the integrity of the table. The trigger gets activated when the
SQL commands, such as insert, delete and update , are given.

Syntax
——————-
CREATE TRIGGER trigger_name
———————-

Q.55. Where are usernames and passwords stored in SQL Server?

Ans. In the ‘sysxlogins’ table of main database in SQL Server, the
usernames and passwords are stored.

Q.56. What are the kinds of relationships between databases in RDBMS SQL
Server?

Ans. As we know Relationships are developed by inter connecting the
column of two tables. Mainly, 3 various
types of relationships
are used regularly, which are are given below:

1. One-to-one relationship
2. Many-to-many relationship
3. Many-to-one relationship

Q.57. What are the third-party tools in SQL Server?

Ans. Following are the third-party tools that are utilized in SQL
Server:

———–
SQL DOC 2
SQL CHECK
SQL Backup 5
SQL
Prompt
Litespeed 5.0
—————

Q.58. How can you manage exceptions in SQL Server?

Ans. The exception can be controlled by TRY and
CATCH blocks. Place the SQL statement in the TRY block and write the
desired code in the CATCH block to control the expectations. If any
error occurs in the code in the TRY block, then the control will
automatically shift to that CATCH block.

Q.59. How many SQL authentication modes are there? And, what are they?

Ans. The two authentication modes  in SQL Server are Windows
Authentication Mode and Mixed Mode.

Windows Authentication Mode facilitates authentication for Windows but not
for SQL Server.

Mixed Mode permits both types of authentication ‘Windows and SQL Server‘.

Q.60. Write about function in SQL Server?

Ans. Generally, a function is a database object in SQL Server . A
function is usually a set of SQL statements that permit input parameters,
conduct processing, and return outcome only. However, a function can only
provide a single value or table of outcome; where the insert, delete and
update ability of records in tables is not available.

Q.61. What types of replication are in SQL Server?

Ans. Mainly, 3 types of replications are available:

1. Snapshot replication
2. Merge replication
3. Transactional replication

Q.62. Which command is utilized to identify the version of SQL Server?

Ans. Following command is utilized to detect the version of SQL
server.
————————–
Select
SERVERPROPERTY(‘productversion’)
————————

Q.63. Write about COALESCE function?

Ans. The COALESCE function carries a set of inputs and provide
the first non-null output.
Syntax:

Q.64. Can we connect SQL Server with others?

Ans. SQL Server enables the OLEDB provider, which provides the
link, to connect all databases.
Example: Oracle has an OLEDB provider
that has a link to connect an SQL Server group.

Q.65. Write about SQL Server Agent?

Ans. SQL Server Agent plays a significant role in the regular
operation of SQL Server Database Administrators or DBAs. The focus of the
server agent is to effortlessly carry out tasks utilizing a scheduler engine
that facilitates the tasks to be accomplished at planned times.
SQL Server Agent utilizes SQL Server to get stored the planned
management task data.

Q.66. Which tables are called magic tables in SQL Server?

Ans. A magic table is a temporary logical table that is formulated by
an SQL Server for various tasks such as insert, update and delete
(DML) operations. The recent operations  conducted on the rows
are stored automatically in magic tables. Magic tables are not physically
available in SQL server. The magic tables are just provisional internal
tables.  Here is explained full about
DDL vs DML

Q.67. Write the name of some common clauses utilized with SELECT queries in
SQL?

Ans. Although, many SELECT statement clauses are utilized in SQL,
some frequently used clauses are WHERE, FROM, GROUP BY, ORDER BY, and
HAVING, etc.

WHERE

The WHERE clause defines the particular parameters that restricts the
contents of the results table can test for basic relationships between a
column and a range of columns through subselects.

FROM

The FROM clause specifies the views and tables from which data can be
interacted. The listed tables and views should appear at the time the
question is raised.

GROUP BY

The GROUP BY clause is usually used for accumulate functions to generate a
single resulting row for every set of unique values in a bundle of columns
or manifestations.

ORDER BY

The ORDER BY clause helps in selecting the columns on which the result table
needs to be be sorted.

HAVING

The HAVING clause finds out the results of the GROUP BY clause by utilizing
an accumulate function.

Q.68. What is error with the following SQL query?

Ans. When this command will be executed, following error will be
displayed.

———————-
Msg 147, Level 16, State 1, Line 1
Invalid
column name ‘˜gender’.
———————

Accumulation may not happen in the WHERE clause until it is in a subquery
possessed in the HAVING clause or a select list; the column being
accumulated is an outer reference.

Usually, it means that whenever we work with accumulation functions and are
utilizing the GROUP BY clause, we cannot utilize the WHERE clause. So,
HAVING clause is used in the place of WHERE clause. The GROUP BY clause
needs to come first, followed by the HAVING clause.

Correct syntax is as follows
——————-

Output:
———————–

Q.69. What is stuff() function?

Ans. The stuff() function removes a part of the string and then adds
another part into the string, beginning at a particular position.

Syntax:

Q.70. What are views?

Ans. Views are virtual tables that displays the extracted data from
the tables as per our requirement. Views are only the result set of an SQL
query that has a particular name given to it. As views are not physically
available, they take less space to get stored.

Q.71. Explain views with proper example.

Ans. Following employee table can be taken for example of views. We
wish to conduct many operations on the records with gender ‘female’. Here,
we can develop a view-only table for the female employees from the employee
table.

Now, let us write query for view on the employee table on the SQL Server.

Q.72. What are types of views in SQL? Explain each type of views.

Ans. In SQL, the views are categorized into four types as follows.

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

Complex View: A view that is built from several tables and includes a
GROUP BY clause as well as functions.

Inline View: A view that is built on a subquery in the FROM clause,
which provides a temporary table and simplifies a complicated query.

Materialized View: A view that saves both the definition and the
details. It builds data replicas by physically preserving them.

Q.73. What is a stored procedure?

Ans. A stored procedure is a set of SQL query that can be saved for
future reuse purpose. However, a stored procedure is a function that
consists of number of SQL statements to interact with the database system.
We can centralize various SQL statements into a stored procedure and run
them requirements.

Q.74. Explain stored procedure with an example.

Ans. A stored procedure can be utilized as a medium of modular
programming, i.e., a stored procedure needs to be created once, saved or
stored, and can be called or run any time as per requirements. This also
executed faster than other queries.

Syntax:

Q.75. Define Inner Join with an example.

Ans. Inner Join usually provides us those records that have fetched
values in two tables.

Supposes, we consider 2 tables, Table 1 and Table 2. If we implement Inner
Join on these 2 tables, we will get only those records that are commonly
available in both Table 1 and 2.

Syntax:

With incorporation of Inner Join, we got only those records where the
departments are matched in both tables. The matched departments are Sales,
Support, and Analytics .

Q.76. Find the differences between tables and views.

Ans. View is a virtual display of tabular data which is obtained from
a permanent database.

Table is designed with a set of columns and rows.

View does not carry data itself Permanently.

Table possesses data permanently in database.

View helps to query some specific information included in a few different
tables.

Table contains basic data and cases of a described object

View provides frequently queried data.

Table stores updated data and the same can be shown in view.

Q.77. What is a temporary table?

Ans. A temporary table assists us store and process some outcomes
temporarily. The Temporary tables are developed and can be deleted
automatically when they are no longer utilized. They are very valuable in
areas where temporary data needs to be sheltered for some time being.

Q.78. Write an SQL query to create a temporary table

Ans. The query for temporary table creation is almost similar to
permanent table. The following query has created a temporary table:

Syntax:
——————————-

Output:
—————————

Q.79. What is OLTP?

Ans. OLTP (Online Transaction Processing) is a type of software
application that is valuable for endorsing transaction-oriented programs.

Q.80. What is OLAP?

Ans. OLAP (Online Analytical Processing) is a a type of
software programs that are recognized by an approximately lower regularity
of online transactions.

Q.81. Find the difference between OLAP and OLTP?

Ans. OLAP: For this system, the performance of computing
relies on the response time. So, such systems are basically utilized for
data mining (web scrapping) retaining aggregated past data, and they are
generally utilized in multidimensional schemas.

OLTP: It helps to carry on the consistency. Usually, the OLTP system
attends decentralized methods to avoid any single failure. This system is
basically constructed for a large range   of end users to conduct
short transactions. These queries implicated in such databases are basically
simple, need quick response time, and, return only a few records. Hence, the
quantity of transactions per second behaves as an a significant measure for
those systems.

Q.82. What is Hybrid OLAP?

Ans. Hybrid OLAP (HOLAP) combines the multidimensional
data patterns and relational tables of database to store multidimensional
data. The accumulations for a HOLAP partition are stocked by analysis
services in a multidimensional pattern. All the evidences are kept in a
relational database.

Q.83. What do you mean by Self Join? Explain by an example

Ans. Self Join in SQL helps in joining the rows of table with the
other rows of the same table as per given to condition.

Syntax:

Q.84. Find the difference between Union and Union All operators?

Ans. The Union operator helps combine the results of two or more
select queries. For example, if A is one set and B is another set, all the
distinct elements of both sets will come together and it can be AUB. More
about
Union vs Union ALL

Syntax:

Q.85. What is a database cursor?

Ans. A database cursor is a supervision in database that permits you
to drive around a table, i.e, rows or documents. You can consider it as a
pointer for a specific row in a set of rows. However, the cursors are
incredibly valuable for database traversal procedures such as insertion,
extraction, and removal.

Q.86. How is a database cursor used?

Ans. After declaration of any variable, you need to DECLARE a
cursor. Often a SELECT statement need to be aligned with the
declàration of a cursor .

The OPEN statements need to be called prior to fetch the rows from
the result containing table in order to initiate the result set,.

The FETCH statement is used to catch and move to the result set’s
next row.

The CLOSE expression is used to exit the cursor.

At the end, the DEALLOCATE clause is used to uninstall the
description of cursor and clear all the resources related to it.

An example SQL cursor is given below:

Q.87. What is the function of the INTERSECT operator?

Ans. The INTERSECT operator combines 2 select statements and
retrieves only the common records between both the select statements.
Following query is made for table 1 and table 2.

Syntax:

Q.88. How can we copy all data from one table into another?

Ans. By considering our employee table, we can copy all or some data
into other table by using INSERT INTO SELECT operator. Before we proceed, we
need to create another duplicate table with similar structure as the first
table from which data is to retrieved for another table.
—————–

Syntax:

Q.89. How BETWEEN operator is different from IN operator in SQL?

Ans. In SQL, the BETWEEN operator represents rows based on a
set of specific values (text, numbers, or dates. The BETWEEN operator
retrieves the total number of values that appears between two specific
ranges.

The IN operator helps in searching the values within a specific range of
values. If we have possessed more than one value to select from, then IN
operator is used.

Q.90. Describe how to create table using SQL statement and delete duplicate
rows using a single SQL statement but without any table creation.

Ans. First we have to create an employee table where the name of the
columns are E-ID, E-NAME, E-DEPARTMENT, and EMAIL.

Following SQL scripts are used for creating the sample data:

Hence, the duplicate rows with IDs 5 and 6, will be deleted and the
remaining unique rows with unique names and the IDs 1 and 2, are
retained.

Q.91. How to find the employee name who has the second-highest salary from
the employee table (with salary-based data)?i

Ans. In the employee table, Tarum has the second-highest salary
(70,000).
————————–
Name Salary
Tarun
70,000
Sabid 60,000
Adarsh 30,000
Vaibhav 80,000
——————————–
Below
is a simple query to find out the employee who has the third-highest salary.
The functions DENSE RANK, RANK, and ROW NUMBER along
with Order By clause are used to obtain the required integer value.

Also, the PARTITION BY clause can be used.

Q.92.what are the 5 common mistakes in SQL?

Ans. 5 common mistakes are as follows.

1. Misspelling Commands
2. Using Case-Sensitive Names
3. Writing an Invalid Statement Order
4. Ignoring Quotes and Brackets
5. Table Aliases omitted

Q.93. How to handle Misspelling Command error?

Ans. This is the most genuine type of SQL mistake by developers due
to chubby fingers and reckless typings.

Wrong SQL code:

Q.94. How to handle the Forgotten Quotes and Brackets?

Ans. Definitely, the error alert will be displayed due to forgotten
quotes and brackets.
Wrong code:

Q.95. Explain with example how to resolve Invalid statement order error in
SQL?

Ans. When we’re writing SELECT statements, we need to remember that
there is a predetermined keyword pattern required for the statement to
perform appropriately.

Let’s have a look at an an illustration of a incorrectly-ordered
statement:

Q.96. Explain the function of significant SQL keywords such as: SELECT,
FROM, WHERE, GROUP BY, HAVING, ORDER BY?

Ans. SELECT determines column names and functions

FROM comes before the table name or names (and also JOIN conditions
if you’re working with multiple tables)

WHERE is used for filtering statements

GROUP BY exhibits the way of grouping columns

HAVING conducts filtering the grouped values

ORDER BY helps in the order in which the outcomes will be exhibited.

You cannot place a WHERE keyword before the keyword FROM, and
you can’t place a HAVING before a GROUP BY keyword. Otherwise,
the statement would be invalid.

Q.97. How to solve error of Omitting Table Aliases?

Ans. Creating table aliases is a prominent practice at the time of
joining table. These aliases differentiate among columns with the same name
in the tables; thus the database will come to know which column values to
provide.

It is not necessary when we’re joining various tables, since we can utilize
the entire table names. But, it is necessary to create table alias if we
conduct a self-join for a table.

If you’re writing an SQL statement to get present location of an exhibition
and the location from the last year:

Error statement:

Q.98. How do you handle error caused by Case-Sensitive Names in SQL?

Ans. This type error only occurs when you want to write non-standard
names for database tables or database objects.

Let’s say that you need to have a table named LargeCustomer and for some
reason you add another table called LargeCustomer. As you already know,
object names in databases are usually case-insensitive. So when you write a
query for the LargeCustomer table, the database will truly LargeCustomer
skip this, you must put double quotes around the table name. 

For example:

Wrong syntax

While making a table, you need to utilise double quotes if:
The
table has a case-sensitive name and table name includes special characters
or a blank space, like “Large Customer”. Using of double quote gets resolved
all these issues.

Q.99. How to improve the speed of SQL queries?

Ans. Make your SQL query faster and more efficient through following
methods.

1. Batch data removal and updates
2. Include automatic dividing SQL server features
3. Change your scalar function to a table-valued one.
4. Instead of UPDATE, use inline CASE statement
5. Decrease nested views to reduce lags
6. Data pre-staging
7. Utilize temporary tables
8. Prevent using re-use code
9. Prevent negative searches
10. Prevent cursors
11. Utilize only the required number of columns u want.
12. Count your all rows by the system table and No need to count everything in
    that table.
13. Never utilize Globally Unique Identifiers (GUIDs)
14. Prevent triggers
15. Keep the large and small transactions separated
16. Never double dip
17. Try to utilize stored procedures more
18. Avoid ORM (Object-Relational Mappers (ORMs))

Q.100. What is SQL tuning set (STS)?

Ans. SQL tuning set (STS) is a database object which can be
utilized as input to tuning tools. It helps inth  improvement of
database query performance.