Categories
Top 50 SQL Interview Questions and Answers
This blog contains SQL interview questions and answers from freshers to experienced professionals for concepts such as MS SQL Server, MySQL database, etc. It is a one-stop resource through which you can avail maximum benefits and prepare for job interviews easily. Check out the top SQL interview questions asked by recruiters today:
1. What is SQL?
SQL stands for Structured Query Language. It is the standard language for RDBMS and is useful in handling organized data that has entities or variables with relations between them. SQL is used for communicating with databases.
According to ANSI, SQL is used for maintaining RDBMS and for performing different operations of data manipulation on different types of data by using the features of SQL. Basically, it is a database language that is used for the creation and deletion of databases. It can also be used, among other things, to fetch and modify the rows of a table.
According to ANSI, SQL is used for maintaining RDBMS and for performing different operations of data manipulation on different types of data by using the features of SQL. Basically, it is a database language that is used for the creation and deletion of databases. It can also be used, among other things, to fetch and modify the rows of a table.
2. Define Database.
A database is an organized collection of structured data that can be stored, easily accessed, managed, and retrieved digitally from a remote or local computer system. Databases can be complex and vast and are built with a fixed design and modeling approach. While smaller databases can be stored on a file system, large ones are hosted on computer clusters or cloud storage.
3. What is denormalization?
Denormalization is the opposite of normalization; redundant data is added to speed up complex queries that have multiple tables that need to be joined. Optimization of the read performance of a database is attempted by adding or grouping redundant copies of data.
4. What is normalization and its types?
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 normalization more flexible.
The different forms of normalization are:
The different forms of normalization are:
First Normal Form
If every attribute in a relation is single-valued, then it is in the first normal form. If it contains a composite or multi-valued attribute, then it is in violation of the first normal form.Second Normal Form:
A relation is said to be in the second normal form if it has met the conditions for the first normal form and does not have any partial dependency, i.e., it does not have a non-prime attribute that relies on any proper subset of any candidate key of the table. Often, the solution to this problem is specifying a single-column primary key.Third Normal Form:
A relation is in the third normal form when it meets the conditions for the second normal form and there is not any transitive dependency between the non-prime attributes, i.e., all the non-prime attributes are decided only by the candidate keys of the relation and not by other non-prime attributes.Boyce-Codd Normal Form:
A relation is in the Boyce-Codd normal form or BCNF if it meets the conditions of the third normal form, and for every functional dependency, the left-hand side is a super key. A relation is in BCNF if and only if X is a super key for every nontrivial functional dependency in form X –> Y.5. What is the difference between DBMS and RDBMS?
A database management system or DBMS is system software that can create, retrieve, update, and manage a database. It ensures the consistency of data and sees to it that it is organized and easily accessible by acting as an interface between the database and its end-users or Application Software. DBMS can be classified into four types:
RDBMS stores data in the form of a collection of tables. The relations are defined between the common fields of these tables. MS SQL Server, MySQL, IBM DB2, Oracle, and Amazon Redshift are all based on RDBMS.
Hierarchical Database:
It has a treelike structure with the data being stored in a hierarchical format. The parent in a database can have multiple children, but a child can have only a single parent.Network Database:
This type of database is presented as a graph that can have many-to-many relationships allowing children to have multiple children.Relational Database:
It is the most widely used and easy-to-use database. It is represented as a table and the values in the columns and rows are related to each other.Object-oriented Database:
The data values and operations are stored as objects in this type of database, and these objects have multiple relationships among them.RDBMS stores data in the form of a collection of tables. The relations are defined between the common fields of these tables. MS SQL Server, MySQL, IBM DB2, Oracle, and Amazon Redshift are all based on RDBMS.
DBMS vs RDBMS
Parameters | DBMS | RDBMS |
Access | Data elements need to be accessed separately | Multiple data elements can be accessed at the same time |
Relationship Between Data | No relationship between data | Data in tables is related to each other |
Normalization | It is not present | It is present |
Distributed Database | It does not support distributed database | It supports distributed database |
Data Storage Format | Data is stored in either a navigational or hierarchical form | Data is stored in a tabular structure with headers being the column names and the rows containing corresponding values |
Amount of Data | It deals with a small quantity of data | It deals with a larger amount of data |
Data Redundancy | It is prevalent | Keys and indexes do not allow data redundancy |
Number of Users | It supports a single user | It supports multiple users |
Data Fetching | It is slower for large amounts of data | It is speedy due to the relational approach |
Data Security | Low-security levels when it comes to data manipulation | Multiple levels of data security exist |
Software and Hardware Requirements | Low | High |
Examples | XML, Window Registry, etc. | MySQL, SQL Server, Oracle, Microsoft Access, PostgreSQL, etc. |
6. What are the applications of SQL?
The major applications of SQL include:
1) Writing data integration scripts
2) Setting and running analytical queries
3) Retrieving subsets of information within a database for analytics applications and transaction processing
4) Adding, updating, and deleting rows and columns of data in a database
1) Writing data integration scripts
2) Setting and running analytical queries
3) Retrieving subsets of information within a database for analytics applications and transaction processing
4) Adding, updating, and deleting rows and columns of data in a database
7. What are the subsets of SQL?
SQL queries are divided into four main categories:
DDL queries are made up of SQL commands that can be used to define the structure of the database and modify it.
These SQL queries are used to manipulate data in a database.
These SQL queries manage the access rights and permission control of the database.
TCL is a set of commands that essentially manages the transactions in a database and the changes made by the DML statements. TCL allows statements to be grouped together into logical transactions.
Data Definition Language (DDL)
DDL queries are made up of SQL commands that can be used to define the structure of the database and modify it.
CREATE
Creates databases, tables, schema, etc.DROP
Drops tables and other database objectsDROP COLUMN
Drops a column from any table structureALTER
Alters the definition of database objectsTRUNCATE
Removes tables, views, procedures, and other database objectsADD COLUMN
Adds any column to the table schemaData Manipulation Language (DML)
These SQL queries are used to manipulate data in a database.
SELECT INTO
Selects data from one table and inserts it into anotherINSERT
Inserts data or records into a tableUPDATE
Updates the value of any record in the databaseDELETE
Deletes records from a tableData Control Language (DCL)
These SQL queries manage the access rights and permission control of the database.
GRANT
Grants access rights to database objectsREVOKE
Withdraws permission from database objectsTransaction Control Language (TCL)
TCL is a set of commands that essentially manages the transactions in a database and the changes made by the DML statements. TCL allows statements to be grouped together into logical transactions.
COMMIT
Commits an irreversible transaction, i.e., the previous image of the database prior to the transaction cannot be retrievedROLLBACK
Reverts the steps in a transaction in case of an errorSAVEPOINT
Sets a savepoint in the transaction to which rollback can be executedSET TRANSACTION
Sets the characteristics of the transaction8. What are Joins in SQL?
Join in SQL 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 on the relationship between tables.
There are four types of Joins:
1) Inner Join
2) Left Join
3) Right Join
4) Full Join
There are four types of Joins:
1) Inner Join
2) Left Join
3) Right Join
4) Full Join
9. Explain the types of SQL joins.
There are four different types of SQL Joins:
It is used to retrieve the records that have matching values in both the tables that are involved in the join. Inner Join is mostly used to join queries.
(Inner) Join:
It is used to retrieve the records that have matching values in both the tables that are involved in the join. Inner Join is mostly used to join queries.
SELECT *
FROM Table_A
JOIN Table_B;
SELECT *
FROM Table_A
INNER JOIN Table_B;
Left (Outer) Join:
Use of left join is to retrieve all the records or rows from the left and the matched ones from the right.
SELECT *
FROM Table_A A
LEFT JOIN Table_B B
ON A.col = B.col;
Right (Outer) Join:
Use of Right join is to retrieve all the records or rows from the right and the matched ones from the left.
SELECT *
FROM Table_A A
RIGHT JOIN Table_B B
ON A.col = B.col;
Full (Outer) Join:
The use of Full join is to retrieve the records that have a match either in the left table or the right table.
SELECT *
FROM Table_A A
FULL JOIN Table_B B
ON A.col = B.col;
10. What is a DEFAULT constraint?
Constraints in SQL 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 us understand what is a default constraint.
A default constraint is used to define a default value for a column so that it is added to all new records if no other value is specified. For example, if we assign a default constraint for the E_salary column in the following table and set the default value to 85000, then all the entries of this column will have the default value of 85000, unless no other value has been assigned during the insertion.
Now, let us go through how to set a default constraint. We will start by creating a new table and adding a default constraint to one of its columns.
A default constraint is used to define a default value for a column so that it is added to all new records if no other value is specified. For example, if we assign a default constraint for the E_salary column in the following table and set the default value to 85000, then all the entries of this column will have the default value of 85000, unless no other value has been assigned during the insertion.
Now, let us go through how to set a default constraint. We will start by creating a new table and adding a default constraint to one of its columns.
create table stu1(s_id int, s_name varchar(20), s_marks int default 50)
select *stu1
inserting record:
insert into stu1(s_id,s_name) values(1,’arun’)
insert into stu1(s_id,s_name) values(2,’raj’)
insert into stu1(s_id,s_name) values(3,’ram’)
select *from stu1
11. What is meant by table and field in SQL?
An organized data in the form of rows and columns is said to be a table. Simply put, it is a collection of related data in a table format.
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 and contain specific information about the data.
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 and contain specific information about the data.
12. What is the difference between primary key and unique key?
Both primary and unique keys carry unique values but a primary key cannot have a null value, while a unique key can. In a table, there cannot be more than one primary key, but there can be multiple unique keys.
13. 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 following table, then every entry in this column should have a unique value.
create a table.
create table stu2(s_id int unique, s_name varchar(20))
insert the records.
insert into stu2 values(1,’arun’)
insert into stu2 values(2,’raj’)
insert into stu2 values(3,’ram’)
14. What is a primary key?
A primary key is used to uniquely identify all table records. It cannot have NULL values and must contain unique values. Only one primary key can exist in one table, and it may have single or multiple fields, making it a composite key.
Now, we will write a query for demonstrating the use of a primary key for the employee table:
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)
);
15. What is a unique key?
The key that can accept only a null value and cannot accept duplicate values is called a unique key. The role of a unique key is to make sure that all columns and rows are unique.
The syntax for a unique key will be the same as the primary key. So, the query using a unique key for the employee table will be:
The syntax for a unique key 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)
);
16. What is an index?
Indexes help speed up searching in a database. If there is no index on a column in the WHERE clause, then the SQL Server has to skim through the entire table and check each and every row to find matches, which may result in slow operations in 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.
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.
CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)
17. What are the usages of SQL?
The following operations can be performed by using SQL database:
1) Creating new databases
2) Inserting new data
3) Deleting existing data
4) Updating records
5) Retrieving the data
6) Creating and dropping tables
7) Creating functions and views
8) Converting data types
1) Creating new databases
2) Inserting new data
3) Deleting existing data
4) Updating records
5) Retrieving the data
6) Creating and dropping tables
7) Creating functions and views
8) Converting data types
18. Explain the different types of SQL commands.
DDL
DDL is that part of SQL that defines the data structure of the database in the initial stage when the database is about to1) Create table
2) Alter table
3) Drop table
DML
DML is used to manipulate already existing data in a database, i.e., it helps users to retrieve and manipulate data. It is used to perform operations such as inserting data into the database through the insert command, updating data with the update command, and deleting data from the database through the delete command.DCL
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 to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke.TCL
TCL is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction with logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.19. What are the subsets of SQL?
The main subsets of SQL are:
1) Data Definition Language (DDL)
2) Data Manipulation Language (DML)
3) Data Control Language (DCL)
4) Transaction Control Language (TCL)
1) Data Definition Language (DDL)
2) Data Manipulation Language (DML)
3) Data Control Language (DCL)
4) Transaction Control Language (TCL)
20. What is a foreign key?
A foreign key is an attribute or a set of attributes that reference the primary key of some other table. Basically, a foreign key is used to link together two tables.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
)
21. What is a data warehouse?
A data warehouse is a large store of accumulated data, from a wide range of sources, within an organization. The data helps drive business decisions.
22. What do you mean by data integrity?
Data integrity is the assurance of accuracy and consistency of data over its whole life cycle. It is a critical aspect of the design, implementation, and usage of systems that store, process, or retrieve data.
Data integrity also defines integrity constraints for enforcing business rules on data when it is entered into a database or application.
Data integrity also defines integrity constraints for enforcing business rules on data when it is entered into a database or application.
23. What are SQL operators?
SQL operators are the special keywords or characters that perform specific operations. They are also used in SQL queries. These operators can be used within the WHERE clause of SQL commands. Based on the specified condition, SQL operators filter the data.
The SQL operators can be categorized into the following types:
1) addition (+)
2) subtraction (-)
3) multiplication (*)
4) division (/)
5) remainder/modulus (%)
1) ALL
2) AND
3) ANY
4) ISNULL
5) EXISTS
6) BETWEEN
7) IN
8) LIKE
8) NOT
10) OR
11) UNIQUE
1) equal to (=)
2) not equal to (!= or )
3) less than (<),
4) greater than (>)
5) less than or equal to (<=)
6) greater than or equal to (>=)
7) not less than (!<)
8) not greater than (!>)
1) AND (& symbol)
2) OR (|, ^)
3) NOT (~)
1) Add equals (+=)
2) subtract equals (-=)
3) multiply equals (*=)
4) divide equals (/=)
5) modulo equals (%=)
1) + (String concatenation)
2) += (String concatenation assignment)
3) % (Wildcard)
4) [] (Character(s) matches)
5) [^] (Character(s) not to match)
6) _ (Wildcard match one character)
The SQL operators can be categorized into the following types:
Arithmetic Operators:
For mathematical operations on numerical data1) addition (+)
2) subtraction (-)
3) multiplication (*)
4) division (/)
5) remainder/modulus (%)
Logical Operators:
For evaluating the expressions and return results in True or False1) ALL
2) AND
3) ANY
4) ISNULL
5) EXISTS
6) BETWEEN
7) IN
8) LIKE
8) NOT
10) OR
11) UNIQUE
Comparison Operators:
For comparisons of two values and checking whether they are the same or not1) equal to (=)
2) not equal to (!= or )
3) less than (<),
4) greater than (>)
5) less than or equal to (<=)
6) greater than or equal to (>=)
7) not less than (!<)
8) not greater than (!>)
Bitwise Operators
For bit manipulations between two expressions of integer type. It first performs conversion of integers into binary bits and then applied operators1) AND (& symbol)
2) OR (|, ^)
3) NOT (~)
Compound Operators:
For operations on a variable before setting the variable’s result to the operation’s result1) Add equals (+=)
2) subtract equals (-=)
3) multiply equals (*=)
4) divide equals (/=)
5) modulo equals (%=)
String Operators
For concatenation and pattern matching of strings1) + (String concatenation)
2) += (String concatenation assignment)
3) % (Wildcard)
4) [] (Character(s) matches)
5) [^] (Character(s) not to match)
6) _ (Wildcard match one character)
24. What are entities and relationships?
Entities:
An 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
A relationship between entities can be referred to as a connection between two tables or entities.For example, in a college database, the student entity and the department entities are associated with each other.
25. 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 are 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.OLAP
It 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 multidimensional schemas.26. What do you know about CDC in SQL Server?
CDC refers to 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.
27. Why is the FLOOR function used in SQL Server?
The FLOOR() function helps to find the largest integer value to a given number, which can be an equal or lesser number.
CREATE INDEX index_name
ON table_name (column1, column2)
Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table.
CREATE UNIQUE INDEX index
ON table_name(column_name)
28. 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 the data based on range-based queries. A clustered index is actually related to how the data is stored; only one clustered index is possible per table.Non-clustered Index:
It stores data at one location and indexes at another location. The index has pointers that point to the location of the data. As the indexes in a non-clustered index are stored in a different place, there can be many non-clustered indexes for a table.Parameters | Clustered Index | Non-clustered Index |
Used For | Sorting and storing records physically in memory | Creating a logical order for data rows; pointers are used for physical data files |
Methods for Storing | Stores data in the leaf nodes of the index | Never stores data in the leaf nodes of the index |
Size | Quite large | Comparatively, small |
Data Accessing | Fast | Slow |
Additional Disk Space | Not required | Required to store indexes separately |
Type of Key | By default, the primary key of a table is a clustered index | It can be used with the unique constraint on the table that acts as a composite key |
Main Feature | Improves the performance of data retrieval | Should be created on columns used in Joins |
29. What is the difference between SQL and MySQL?
Now Let’s compare the difference between SQL and MySQL.
SQL | MySQL |
It is a structured query language used in a database | It is a database management system |
It is used for query and operating database system | It allows data handling, storing, and modifying in an organized manner |
It is always the same | It keeps updating |
It supports only a single storage engine | It supports multiple storage engines |
The server is independent | During backup sessions, the server blocks the database |
30. 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, this primary field is added 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.
Since the primary key is unique for each record, this primary field is added 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.
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)
)
31. 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.
32. What is the ACID property in a database?
The full form of ACID is atomicity, consistency, isolation, and durability. ACID properties are used to check the reliability of transactions.
Atomicity refers to completed or failed transactions, where a 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 vali
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, fire, or some other kind of disturbance.
Atomicity refers to completed or failed transactions, where a 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 vali
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, fire, or some other kind of disturbance.
33. State the differences between SQL and PL/SQL
Far far away, behind the word mountains, far from the countries Vokalia and Consonantia, there live the blind texts. Separated they live in Bookmarksgrove right at the coast
34. What do you understand about a character manipulation function?
Character manipulation functions are used for the manipulation of character data types.
Some of the character manipulation functions are:
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
I 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
35. What is the difference between DROP and TRUNCATE commands?
If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints.
To create and use the table again in its original form, all the elements associated with the table need to be redefined.
However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.
To create and use the table again in its original form, all the elements associated with the table need to be redefined.
However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.
36. 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.
37. How can you create empty tables with the same structure as another table?
This can be achieved by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. In this way, SQL prepares the new table with a duplicate structure to accept the fetched records. However, there are no records that will get fetched due to the WHERE clause in action. Therefore, nothing is inserted into the new table, thus creating an empty table.
38. 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:
1) One-to-one relationship
2) Many-to-one relationship
3) Many-to-many relationship
1) One-to-one relationship
2) Many-to-one relationship
3) Many-to-many relationship
39. What are the third-party tools that are used in SQL Server?
The following is the list of third-party tools that are used in SQL Server:
1) SQL CHECK
2) SQL DOC 2
3) SQL Backup 5
4) SQL Prompt
5) Litespeed 5.0
1) SQL CHECK
2) SQL DOC 2
3) SQL Backup 5
4) SQL Prompt
5) Litespeed 5.0
40. How can you handle expectations in SQL Server?
TRY and CATCH blocks handle exceptions in SQL Server. 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 in the TRY block, then the control will automatically move to that CATCH block.
41. Mention different types of replication in SQL Server?
In SQL Server, three different types of replications are available:
1) Snapshot replication
2) Transactional replication
3) Merge replication
1) Snapshot replication
2) Transactional replication
3) Merge replication
42. What is SQL Server Agent?
SQL Server Agent plays an important role in the daily work of SQL Server administrators or DBAs. This is one of the important parts of SQL Server. The aim of the server agent is to easily implement tasks using a scheduler engine that enables the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to store scheduled management task information.
43. 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 (DML) operations. The operations recently performed on the rows are automatically stored in magic tables. Magic tables are not physical tables; they are just temporary internal tables.
44. Can we link SQL Server with others?
SQL Server allows the OLEDB provider, which provides the link, to connect to all databases.
Example
Oracle, I have an OLEDB provider that has a link to connect with an SQL Server group.45. What is Hybrid OLAP?
Hybrid OLAP (HOLAP) uses a combination of multidimensional data structures and relational database tables to store multidimensional data. The aggregations for a HOLAP partition are stored by analysis services in a multidimensional structure. The facts are stored in a relational database.
46. What is the difference between BETWEEN and IN operators in SQL?
The BETWEEN operator is used to represent rows based on a set of values. The values may be numbers, text, or dates. The BETWEEN operator returns the total number of values that exist between two specified ranges.
The IN condition operator is used to search for values within a given range of values. If we have more than one value to choose from, then we use the IN operator.
The IN condition operator is used to search for values within a given range of values. If we have more than one value to choose from, then we use the IN operator.
47. Explain database white box testing and black box testing.
The white box testing method mainly deals with the internal structure of a particular database, where users hide specification details. The white box testing method involves the following:
1) As the coding error can be detected by testing the white box, it can eliminate internal errors.
2) To check for the consistency of the database, it selects the default table values.
3) This method verifies the referential integrity rule.
4) It helps perform the module testing of database functions, triggers, views, and SQL queries.
The black box testing method generally involves interface testing, followed by database integration. The black box testing method involves the following:
1) Mapping details
2) Verification of incoming data
3) Verification of outgoing data from the other query functions
1) As the coding error can be detected by testing the white box, it can eliminate internal errors.
2) To check for the consistency of the database, it selects the default table values.
3) This method verifies the referential integrity rule.
4) It helps perform the module testing of database functions, triggers, views, and SQL queries.
The black box testing method generally involves interface testing, followed by database integration. The black box testing method involves the following:
1) Mapping details
2) Verification of incoming data
3) Verification of outgoing data from the other query functions
48. 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. Temporary tables are created and can be automatically deleted when they are no longer used. They are very useful in places where temporary data needs to be stored.
49. What are the types of views in SQL?
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.50. 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.
