SQL Server Interview Questions and Answers

SQL Server 2019 latest edition aim is to grow the demand for SQL Server platform that provides choices of cloud environments, development languages, operating systems, and data types. It has come up with a new feature Big Data Clusters for SQL Server and also provides additional improvements and capabilities for database engines. SQL Server 2019 edition has introduced new services like Reporting Services, Machine Learning Services, Master Data Services, and Integration Services. 

Database is defined as a well-organized collection of information or data, generally the data can be managed and accessed easily. The prominent role of the database is to operate large amounts of data by managing, retrieving, and storing data. The database is generally controlled by Database Management Systems (DBMS). 

RDBMS stands for Relational Database Management Systems. A database system specifically designed to maintain relational databases is called RDBMS. A relational database is the most commonly used digital database which is completely based on the relational model. It is defined as the collection of data that organizes the pre-defined relationships between data points for easy access. A relational database organizes data into tables. Many relations database management systems make use of SQL for maintaining the database and for querying. In RDBMS, the data is represented in terms of rows (tuples). RDBMS is easier to administer and implement.

There are mainly 5 types of SQL Commands, and they are as follows:

DML (Data Manipulation Language) commands

• Insert
• Update 
• Delete

DDL (Data Definition Language) commands

• Alter
• Drop
• Create
• Truncate

DCL (Data Control Language) commands

• Revoke
• Grant

DQL (Data Query Language) commands

• Select

TCL (Transaction Control Language) commands

• Rollback
• Savepoint
• Commit

The SQL Select is the prominent DQL command. The SQL Select statement is used to select/fetch/retrieve the amount of data from a database and the output data is stored in a result table known as result-set. Select the query statement is the most commonly used SQL statement.

SQL SELECT Syntax:

Use the following syntax to select all the fields present in the database

Select * From table_name

Use the following syntax to select specific fields from the table

Select col1, col2, col3, ..... From table_name

In Structured Query Language, the query plays a key role because it is used to perform various operations/actions in the database. A query is a form of a connection between the user and the database to fetch output. The user interacts with the database via query and gets the required information. Many queries are present in SQL to fetch results from the database. Using queries will make your work easier in ways like add, delete, change, and view your data in the database. With queries, you can automate tasks like reviewing the data, storing data, and summarizing data. 

SQL Subquery can also be called as an Inner query or Nested query. The Subquery is defined as a query present within or embedded in the main SQL query. All the subqueries are enclosed between parenthesis. The main goal of the subquery is to return data based on the condition mentioned in the main query to further restrict the data to be retrieved. You can make use of subqueries within various statements like INSERT, SELECT, UPDATE, DELETE, etc. 

Types of Subqueries are as follows:

• Correlated subqueries
• Multiple row subquery
• Multiple column subqueries
• Single-row subquery

Essential Properties of Subquery are listed below:

• One or more subqueries can be present in a query.
• A Sub-query does not contain an ORDER-BY Clause
• A Subquery is enclosed in the parenthesis
• Sub-query acts as a filter condition for the primary query
• A Subquery statement must be present on the right-hand side of the comparison operator. 

The important SQL Queries are listed below:

• SELECT
• ALTER TABLE
• DROP
• CREATE
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• REVOKE
• GRANT

1. SELECT Query

This query is the most commonly used SQL query because it is a statement used to select and fetch the data from the database.

SELECT Syntax: 

SELECT col1, col2, col3, ..... FROM table_name

2. ALTER TABLE 

The Alter Table query statement is used to drop and add various constraints to an existing table. This statement is also used to add, modify, delete columns to a table in an existing database.

ALTER TABLE Syntax: 

ALTER TABLE table_name
ADD column_name datatype;

3. DROP 

Drop statement is used to erase all the data present in a table. This statement is used to remove a component from a RDBMS.

DROP Syntax:

DROP TABLE table_name

4. CREATE

The Create Table statement is used to create a new table in a database. The Create Database statement is used to create a new database.

CREATE Syntax:

CREATE TABLE table_name (
col_1 datatype,
col_2 datatype,
col_3 datatype,
col_4 datatype,
.............
);

5. INSERT

Insert statement is used to add a new amount of data to a database.

INSERT Syntax:

INSERT INTO table_name ( col_1, col_2, col_3, col_4)
VALUES ( value_1, value_2, 'value_3', value_4);

6. UPDATE

Update statement is used to change or modify the records present in a table. This statement is mainly used for updating any existing data in a table.

UPDATE Syntax:

UPDATE table_name
SET col_1 = value1, col_2 = value2, col_3 = value3, ...........
WHERE condition;

7. DELETE

The Delete statement is used to delete or erase the records from a table in a database.

DELETE Syntax:

DELETE FROM table_name WHERE condition;

8. TRUNCATE

Truncate statement is also used to delete or erase all records present inside the table without a WHERE clause.

TRUNCATE Syntax:

TRUNCATE TABLE categories;

9. REVOKE

The Revoke statement is used to remove or erase privileges from all the users or a specific role.

10. GRANT  

The Grant statement is used to provide privileges or access to various database objects to the users.

The key difference between SQL and MySQL are projected below: