Below are few major features of Mssql
Q1. What is MSSQL?
MS SQL (Microsoft SQL) is a relational database management system developed by Microsoft released in the year 1989. Written in C & C++.
The MS SQL is primarily used for storing and retrieving the data that are requested by software applications. These software applications can run on the same or different computers across the network to access data from the MS SQL. This is proprietary software that is platform dependant to create and maintain databases. It has support for both the GUI and command-based interface.
It has support for two types of components such as workstation and server. There are different editions of MS SQL available that are maintained and supported by Microsoft for different kinds of users.
Q2. Explain Trigger and its types?
The trigger in the database is basically a type of stored procedure that is automatically executed. These are executed when the DDL or DML command statement related to the trigger is executed. They are mainly used to assess or evaluate data before or after the data modification using the DDL or DML statements. Triggers are also used to preserve the integrity of the data to control server operations and to audit a server.
Types of triggers are divided into four types.
Q3. How will you handle blocking and deadlocks
Blocking occurs when two process wants to access the same data and second is waiting for the first one to release. When a lock to the resource is held for a long time, they cause blocking. Blocking can be resolved as soon as the first process releases the resource.
In a deadlock, each process is waiting for other processes to release the resource. Deadlock can be ignored and assumed that it never occurs. Other steps to handle deadlocks are to detect and prevent it.
Q4. what is DML?
DML (Data Manipulation Language) is a type of programming language that is used to add, delete, and modify the data in the database. It is a subset of the SQL that comprises of data change statements to modify the data present in the database. It does not modify the structure of the database. The statements used in the DML are select, insert, update, and delete to modify the data in the database.
Q5. Define join and name different type of joins?
The join statement in the SQL is used to combine two or more rows from two or more databases. It combines the database based on the related column between the database.
Different types of join statements in the SQL are inner join, left join, right join, and full join.
Q6. What is DCL?
DCL (Data Control Language) is a type of programming language that is used for access control of the data in the database. It has commands GRANT and revokes to provide access control and authorization to the data present in the database.
Q7. Write the Insert, Update, Delete Query in SQL ?
1. Insert statement syntax
INSERT INTO table_name (columns) VALUES (values)
The table_name is the table where you want to insert the values into the columns
2. Update statement syntax
UPDATE table_name SET column1 = value1, column2 = value2, … WHERE filter_expression
This updates the values of the column based on the filter expression that you mention.
3. Delete statement syntax
DELETE FROM table_name WHERE filter_expression
This deletes the value from the table based on the filter expression that you mention.
Q8. What is Triggers in PL/SQL
In PL/SQL, triggers are stored programs that are invoked when a specific condition is met. These programs are executed when some event occurs. It is executed when events like DML statements, DDL statements, and other database operations are executed. Using trigger, you can derive column values, enforce integrity, log or store information, impose security and prevent invalid transactions.
Q9. What is Normalization and types of Normalization?
Normalization is used to organize the data in the database to a structured normal form. It is done to avoid the redundancy and different types of anomalies present in the database. Normalization organizes the rows and columns of a database to ensure their dependencies are properly enforced by the database integrity constraints. There are four types of normalization available. They are,
Q10. What is the difference between a having clause and a where clause?
The where clause is used to filter the rows before grouping and having clause is used to filter the records after grouping.
They differ based on how they use the GROUP BY clause. The other main difference is that where clause can be used without using the GROUP BY clause whereas the Having clause is used only if the grouping is performed by the GROUP BY clause.
Q11. What are the properties and different types of sub-queries?
The Properties of Sub-Query are as follows :
The different types of Sub-Query are as follows:
Q12. What is sql profiler?
SQL Profiler is one of the prominent methods of SQL analyser. There should be a system to trace the functions of SQL, and it has to be analysed. Such an order to determine the activities of SQL is called Profiler.
While using the profiler, the activities can be captured as a picture and used for future reference and analysis.
Q13. Difference between drop, truncate and delete a table?
DROP - It removes a table from the database and it cannot be rolled back once dropped.
DELETE - It removes rows one at a time and records an entry in the transaction log for each deleted row.
TRUNCATE TABLE - It removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
Q14. What is Primary Key and Foreign Key?
The primary key is used to uniquely identify a record in the table so it cannot hold a null value. By default, a primary key is an index in the database table. A table can have only one primary key.
A foreign key is a field in one table that acts as a primary key in another table. You should manually create a foreign key and a table can have more than one foreign key.
Q15. What different are the Constraints used on Table Columns in SQL?
The different types of Constraints used on Table Columns in SQL are as follows:
Q16. They asked about oops concept and basic sql concepts?
The OOP concepts are basically the main ideas behind Object-Oriented Programming. It is consists of abstraction, encapsulation, inheritance, and polymorphism. Whereas Basic SQL concept can be defined as the database management concept which is used to store and manage data and information.
Q17. What's the difference between a primary key and a clustered index?
The main difference between the clustered index and primary key is, the clustered index helps to sort the data in the system by means of physical sorting. Whereas in the primary key, the data is assorted by means of programs.
The primary key can also be used to create a non clustered index.
Q18. What are the two authentication modes in SQL Server?
The two authentication modes in SQL Server are as follows:
Q19. What is the difference between Clustered and Non-Clustered Index?
The difference between Clustered and Non-Clustered Index can be stated as a Clustered index is a type of index in which table records are physically reordered to match the index. Whereas a Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on the disk.