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

Hope you must have gone through my previous article on SQL server database interview questions. Today we'll explore more on sql with 100 interview questions who will help out for the fresher as well as experienced professionals.

Q.1. What do you understand by word ‘database'?

Or

What is a database?

Ans. A database is a storage place for an organized group of structured data that can be properly stored, effortlessly accessed, effectively managed, and obtained digitally from the same pc, local network, or remote computer system.

Q.2. What are the significant factors to consider for designing a database?

Ans. As per some factors like no. Of users, the volume of average data per year, the database design, and the complexity of the database could be planned and fixed up. While smaller size databases can be placed on a file system, larger databases are hosted on advanced and powerful computer system clusters or cloud storage devices.

Q.3. What is a DBMS?

Ans. DBMS stands for Database Management System. A DBMS is system software that allows to create, retrieve, edit or update, and organize a database. It makes sure that the consistency of stored data and watches to it that it is organized and effortlessly obtainable by functioning as an interface between the database and its application software  or end-users .

Q.4. How many types of DBMS are there? Write each type of database shortly.

Ans. Mainly there are 4 types of DBMS such as Hierarchical Database, relational database, object-oriented, and Network database.

1. Hierarchical Database: Here, data tables are arranged in a treelike structure with the data is stored in a hierarchical arrangement. In this arrangement, the parent table in a database can possess multiple children tables, but a child can be linked to only a single parent table.
2. Relational Database: It is a very popular DBMS that has been used by many people across the globe. In this database type, the table and the information in the columns and rows are interrelated to each other.
3. Object-oriented Database: In this DBMS, the data values and functions are stored as objects, and these objects possess multiple relationships with each other.
4. Network Database: The first DBMS has a graphical structure in which many-to-many relationships is made and that allows children table to possess multiple children tables.

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

More about here SQL performance.

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.

Relevant Reading

• SQL Injection
• An Overview of SQL in Data Analysis
• Strategies For Backing up Merge and Transaction Replication