Education : Every lesson
writes the story of our tomorrow.
It's not just a product, It's a solution
to upgrade your tech life!
Bringing technology to life
one click at a time!
×
Course Categories
×
Product Categories
4.6
5.0
4.6
MSSQL Interview Questions MSSQL FAQ
Hi, Welcome to Monopoly IT Solutions, our Interview Questions / FAQs can be very helpful for interview preparation in several ways:
- Understanding the way how Questions are asked by interviewer
- Practicing Answers, find our appropriate ways of answeringand excel the interview
- Gaining Insight into the industry standard
- Reducing Anxiety of Interview
- Self analysis of Key Skills
Following are the conceptual theory questions
- SQL Server is a relational database management system (RDBMS) developed and marketed by Microsoft.
- Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with relational databases. SQL Server is tied to Transact-SQL, or T-SQL, Microsoft’s implementation of SQL, which includes a set of proprietary programming constructs.
- SQL Server has been exclusively available on the Windows environment for over 20 years. In 2018, Microsoft made it available on Linux. SQL Server 2018 became generally available in October 2018 and was compatible with both Windows and Linux.
- There are five editions of SQL Server. The entry-level is SQL Server Express, a free database for learning and building small desktop and server applications. It’s mainly used by individuals and small organizations that require only basic RDBMS functionalities.
- SQL Server Developer: is licensed to one user. It is used as a development and test system, not as a production server. It has all the functionalities of the Enterprise edition, only in the non-production environment. Developers and testers use it.
- SQL Server Web: is designed for Web hosts and Web Virtual Access Points (VAPs). It allows them to share (at low cost) their services and applications running on SQL Server.
- SQL Server Standard: provides core data management and Business Intelligence features for small organizations with minimal IT resources. It offers almost everything SQL Enterprise does but with some limitations.
- What is an IDENTITY column in insert statements? There are two authentication modes:
- Windows authentication Mode
- Mixed Mode (or) Sql Server Authentication
- IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.
- RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.
- SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.
- A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.
- Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:
- NOT NULL -NOT NULL - Restricts NULL value from being inserted into a column.
- CHECK - Verifies that all values in a field satisfy a condition.
- DEFAULT - Automatically assigns a default value if no value has been specified for the field.
- UNIQUE - Ensures unique values to be inserted into the field.
- INDEX - Indexes a field providing faster retrieval of records.
- PRIMARY KEY - Uniquely identifies each record in a table.
- FOREIGN KEY - Ensures referential integrity for a record in another table.
- SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.
- The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.
- A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.
- A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
- The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.
- (INNER) JOIN: Retrieves records that have matching values in both tables involved in the join. This is the widely used join for queries.
- LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.
- RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.
- FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.
- (INNER) JOIN: Retrieves records that have matching values in both tables involved in the join. This is the widely used join for queries.
- A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query.
- Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of the number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN.
- A database index is a data structure that provides a quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.
- Unique and Non-Unique Index:Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index.
- Clustered and Non-Clustered Index:Clustered indexes are indexes whose order of the rows in the database corresponds to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas, multiple non-clustered indexes can exist in the table. The only difference between clustered and non-clustered indexes is that the database manager attempts to keep the data in the database in the same order as the corresponding keys appear in the clustered index. Clustering indexes can improve the performance of most query operations because they provide a linear-access path to data stored in the database.
- Unique and Non-Unique Index:Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index.
- As explained above, the differences can be broken down into three small factors:
- Clustered index modifies the way records are stored in a database based on the indexed column. A non-clustered index creates a separate entity within the table which references the original table.
- Clustered index is used for easy and speedy retrieval of data from the database, whereas, fetching records from the non-clustered index is relatively slower.
- In SQL, a table can have a single clustered index whereas it can have multiple non-clustered indexes.
- Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle and is a critical aspect of the design, implementation, and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.
- A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.
- A subquery is a query within another query, also known as a nested query or inner query. It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively. For example, here we fetch the contact information for students who have enrolled for the maths subject:
There are two types of subquery - Correlated and Non-Correlated.- A correlated subquery cannot be considered as an independent query, but it can refer to the column in a table listed in the FROM of the main query.
- A non-correlated subquery can be considered as an independent query and the output of the subquery is substituted in the main query.
Following are the coding based questions
