Basic SQL Server Interview Questions and Answers
In this article, I am going to discuss the most frequently asked basic SQL Server Interview Questions and Answers.
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers.
This allows for a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
Explain DML, DDL, DCL and TCL statements with examples?
The DML stands for Data Manipulation Language. DML is used to retrieve, insert, update and delete data in a database that means DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.
DML statements include the following:
SELECT – select records from a table
INSERT – insert new records
UPDATE – update/Modify existing records
DELETE – delete existing records
The DDL stands for Data Definition Language. DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.
CREATE – create a new Table, database, schema
ALTER – alter the existing table, column description
DROP – delete existing objects from a database
The DCL stands for data control language. Generally, we will use these commands to secure database objects by creating roles, permissions using GRANT, REVOKE operations. In SQL Server, the following operations will come under DCL operations
GRANT – allows users to read/write on certain database objects
REVOKE – keeps users from the read/write permission on database objects
The TCL stands for Transactional Control Language. TCL is used to manage transactions within a database. Examples: COMMIT, ROLLBACK, Begin Transaction statements
BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in case of any error
What is the difference between Drop, Delete and Truncate statements in SQL Server?
Drop, Delete and Truncate – All operations can be rolled back.
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
All the statements (Delete, Truncate and Drop) are logged operations but the amount of information that is logged varies. Delete statement logs an entry in the transaction log for each deleted row, whereas Truncate Table logs only the Page deallocations. Hence, truncate is a little faster than Delete.
- The DELETE command is used to remove some or all rows from a table.
- A WHERE clause can be used with a DELETE command to remove some specific rows from a table.
- If the WHERE condition is not specified, then all rows are removed.
- The DELETE operation will cause all DELETE triggers on the table to fire.
- It does not reset the identity of the column value.
- It removes rows on the row-by-row basis and hence for each deleted row it records an entry in the transaction logs, thus this is slower than truncate.
- This is a DML command so it is just used to manipulate or modify the table data and it does not change any property of a table.
- TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.
- It Does not require a WHERE clause, so we cannot filter rows while Truncating.
- IDENTITY columns are re-seeded on this operation if no seed was defined then the default value 1 is used.
- No Triggers are fired on this operation because it does not operate on individual rows.
- TRUNCATE removes the data by deallocating the data pages used to store the table’s data instead of rows and records, and only the page deallocations are recorded in the transaction log thus it is faster than delete.
- We cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
- TRUNCATE is DDL Command
- The DROP command removes a table from the database.
- All the related Data, Indexes, Triggers, Constraints, and Permission specifications for the Table are dropped by this operation.
- Some objects like Views, Stored Procedures that reference the dropped table are not dropped and must be explicitly dropped.
- Cannot drop a table that is referenced by any Foreign Key constraint.
- No Triggers are fired on this operation because it does not operate on individual rows.
Note: If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if the session is closed, it cannot be rolled back but DELETE can be rolled back.
What is Cascading referential integrity constraint?
Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a user attempts to delete or update a primary key to which an existing foreign keys point.
If a delete or update statement affects rows in a foreign key table, those values will be set to NULL when the primary key record is deleted or updated. The foreign key columns affected must allow NULL values.
If a delete statement affects one or more rows in a foreign key table, those rows will be deleted when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value from the primary key record after it has been updated.
If a delete or update statement affects rows in a foreign key table, then all rows containing those foreign keys are set to the default value. All foreign key columns in the related table must have default constraints defined on them.
This is the default action. This specifies that if an update or deletes statement affects rows in foreign key tables, then the action will be denied and rolled back. An error message will be raised.
What is the difference between where clause and having clause in SQL Server?
This is one of the most frequently asked SQL Server Interview Questions and in almost all interviews this question being asked. Let us understand the difference with an example. For this example, I am going to use the following Sales table.
SQL Script to create and populate Sales table with test data
Create table Sales ( Product nvarchar(50), SaleAmount int ) Go Insert into Sales values ('iPhone', 500) Insert into Sales values ('Laptop', 800) Insert into Sales values ('iPhone', 1000) Insert into Sales values ('Speakers', 400) Insert into Sales values ('Laptop', 600) Go Select * from Sales Go
To calculate total sales by product, we would write a GROUP BY query as shown below
SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product
Now if we want to find only those products where the total sales amount is greater than $1000, we will use HAVING clause to filter products
SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product HAVING SUM(SaleAmount) > 1000
If we use the WHERE clause instead of HAVING clause, we will get a syntax error. This is because the WHERE clause doesn’t work with aggregate functions like sum, min, max, avg, etc.
SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product WHERE SUM(SaleAmount) > 1000
When we execute the above query, it will give us the below error.
Incorrect syntax near the keyword ‘WHERE’.
So, in short, the difference is WHERE clause cannot be used with aggregates whereas HAVING clause can.
However, there are other differences as well that we need to keep in mind when using WHERE and HAVING clauses. WHERE clause filters rows before aggregate calculations are performed whereas HAVING clause filters rows after aggregate calculations are performed. Let us understand this with an example.
Total sales of iPhone and Speakers can be calculated by using either WHERE or HAVING clause
Calculate Total sales of iPhone and Speakers using WHERE clause: In this example, the WHERE clause retrieves only iPhone and Speaker products and then performs the sum.
SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales WHERE Product in ('iPhone', 'Speakers') GROUP BY Product
Calculate Total sales of iPhone and Speakers using HAVING clause: This example retrieves all rows from the Sales table, performs the sum and then removes all products except iPhone and Speakers.
SELECT Product, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY Product HAVING Product in ('iPhone', 'Speakers')
So, from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
Another difference is WHERE comes before GROUP BY and HAVING comes after GROUP BY.
Difference between WHERE and Having
- WHERE clause cannot be used with aggregate functions whereas HAVING clause can be used with aggregate functions. This means WHERE clause is used for filtering individual rows on a table whereas HAVING clause is used to filter groups.
- WHERE comes before GROUP BY. This means WHERE clause filters rows before aggregate calculations are performed. HAVING comes after GROUP BY. This means HAVING clause filters rows after aggregate calculations are performed. So, from a performance standpoint, HAVING is slower than WHERE and should be avoided when possible.
- WHERE and HAVING clause can be used together in a SELECT query. In this case WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then the HAVING clause filters the groups.
- WHERE clause can be used with – Select, Insert, and Update statements whereas HAVING clause can only be used with the Select statement.
What are the differences between primary key and unique key in SQL Server?
This is of the most asked SQL Server Interview Questions in Interviews. Let discuss this question in detail.
- A table can have only one primary key. On the other hand, a table can have more than one unique key.
- The primary key column does not accept any null values whereas a unique key column accepts one null value.
- Both Primary key and unique key enforce uniqueness of the column on which they are defined. But By default, the primary key creates a unique clustered index on the column whereas unique key creates a unique non clustered index.
How can we copy the data from one table to another?
When we copy the data from one table to another table then the two tables should contain the same structure.
INSERT <NEW TABLE NAME> SELECT * FROM <OLD TABLE NAME>
INSERT DUMMYEMP SELECT * FROM EMPLOYEE
When we copy the data from one table to another table we use insert and select query.
Tables always independent objects that mean a table does not depend on other tables
How to create a new table from an existing table or in how many ways we can create a new table from an existing table?
If required we can create a new table from an existing table as below.
Syntax1: (with all column from an existing table)
SELECT * INTO <NEW TABLE NAME> FROM <OLD TABLE NAME>
SELECT * INTO NEWEMPLOYEE FROM EMPLOYEE
When we execute the above query it will create a new table with all records from an existing table.
Syntax2: (with specific columns from an existing table)
SELECT <REQUIREDCOLUMN> INTO <NEW TABLE NAME> FROM <OLD TABLE NAME>
SELECT EID, SALARY INTO SPECEMP FROM EMPLOYEE
When we execute the above query it will create a new table with the specific column data from an existing table.
Syntax3: (creating a new table without data)
SELECT * INTO <NEW TABLE NAME> FROM <OLD TABLE NAME> WHERE 1 = 0
SELECT * INTO DUMMYEMP FROM EMPLOYEE WHERE 1 = 0
SELECT <REQUIRED COLUMNS> INTO <NEW TABLE NAME> FROM <OLD TABLE NAME>
SELECT EID, SALARY INTO TAB1 FROM EMPLOYEE WHERE 1 = 0
When we execute the above query it will create a new table without records from an existing table.
In this article, I try to explain most frequently asked basic SQL Server Interview Questions and Answers. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.No HTML was returned.