Here are some SQL Server interview questions for freshers, ranging from basic concepts to fundamental SQL skills:
What is SQL Server?
- SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used to store and manage data in structured tables and provides tools for querying, managing, and securing data.
What is a database?
- A database is a collection of data that is stored and organized in tables, which can be queried, updated, and managed through SQL.
What is a primary key?
- A primary key is a column or a combination of columns that uniquely identifies each row in a table. It must consist of unique values and cannot include NULL values.
What is a foreign key?
- A foreign key is a column in one table that uniquely identifies a row in another table. It ensures referential integrity between the two tables.
What are the different types of joins in SQL Server?
- INNER JOIN: Returns rows where there is a match in both tables.
- LEFT JOIN (OUTER JOIN): Returns all rows from the left table and matching rows from the right table.
- RIGHT JOIN (OUTER JOIN): Returns all rows from the right table and matching rows from the left table.
- FULL OUTER JOIN: Returns all rows when there is a match in one of the tables.
- CROSS JOIN: Returns the Cartesian product of both tables.
What is normalization?
- Normalization is the process of organizing data in a database to reduce redundancy and dependency by splitting large tables into smaller ones. The main goal is to avoid data anomalies.
What are the different types of normalization?
- 1NF (First Normal Form): Ensures that all columns contain atomic values and eliminates duplicate rows.
- 2NF (Second Normal Form): Ensures that the table is in 1NF and all non-key columns are fully dependent on the primary key.
- 3NF (Third Normal Form): Ensures that the table is in 2NF and that all columns are only dependent on the primary key.
What is denormalization?
- Denormalization is the process of combining tables to reduce complexity or improve performance. It introduces redundancy to avoid complex joins and increase query speed.
What is a stored procedure?
- A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. It can take parameters, perform operations, and return results.
What is a trigger in SQL Server?
- A trigger is a special type of stored procedure that automatically executes when certain events like INSERT, UPDATE, or DELETE occur on a table.
What is the difference between a DELETE and TRUNCATE operation?
- DELETE removes rows from a table but can be rolled back and can be filtered with a WHERE clause.
- TRUNCATE removes all rows from a table and cannot be rolled back (except in some cases) and does not allow filtering with a WHERE clause.
What is a view in SQL Server?
- A view is a virtual table created by a query that pulls data from one or more tables. It does not store data itself but provides a convenient way to access and manipulate data.
What is an index?
- An index is a database object that improves the speed of data retrieval operations. It works like a pointer to quickly locate rows in a table without scanning the entire table.
What is a clustered index?
- A clustered index determines the physical order of data in a table. There can only be one clustered index per table.
What is a non-clustered index?
- A non-clustered index is a separate structure from the data table that provides pointers to the data. A table can have multiple non-clustered indexes.
What are aggregate functions in SQL?
- Aggregate functions are used to perform calculations on a set of values. Examples include COUNT(), SUM(), AVG(), MAX(), and MIN().
What is the difference between CHAR and VARCHAR?
- CHAR is a fixed-length data type, while VARCHAR is a variable-length data type. CHAR always uses the specified length, while VARCHAR uses only the required space for the actual data.
What is a NULL value in SQL Server?
- A NULL value represents the absence of a value or unknown data. It is different from an empty string or zero.
What is the difference between WHERE and HAVING clauses?
- WHERE filters rows before grouping. It is used with non-aggregated columns.
- HAVING filters rows after grouping. It is used with aggregated columns.
What is a subquery?
- A subquery is a query inside another query. It is used to return a result that can be used in the main query’s WHERE, FROM, or SELECT clause.
What is a transaction in SQL Server?
- A transaction is a logical unit of work that ensures the database remains consistent. A transaction is either fully completed or not done at all, ensuring ACID properties (Atomicity, Consistency, Isolation, Durability).
What is the DISTINCT keyword used for?
- The DISTINCT keyword is used to remove duplicate rows from the result set and return only unique records.
What is the GROUP BY clause?
- The GROUP BY clause is used to group rows that have the same values into summary rows. It is often used with aggregate functions to summarize data.
What is the ORDER BY clause?
- The ORDER BY clause is used to sort the result set of a query in either ascending or descending order based on one or more columns.
What is SQL Server Profiler?
- SQL Server Profiler is a tool used to monitor and analyze SQL Server activities, like querying, stored procedures, and transactions. It helps in troubleshooting performance issues.
What is a backup in SQL Server?
- A backup is a copy of a database or its components (tables, logs, etc.) that is stored separately and can be used to restore the database in case of failure or data loss.
What is the difference between INNER JOIN and LEFT JOIN?
- INNER JOIN returns only matching rows from both tables.
- LEFT JOIN returns all rows from the left table and matching rows from the right table, or NULL if there is no match.
What is the EXPLAIN command used for in SQL Server?
- The EXPLAIN command is used to show the execution plan of a query, providing insights into how SQL Server processes the query and helping optimize performance.
- What is the INSERT INTO statement?
- The INSERT INTO statement is used to add new rows of data into a table.
What is the difference between UPDATE and SET in SQL Server?
- UPDATE modifies the existing records in a table, while SET is used to assign a value to a column during an UPDATE operation.
These questions are designed for freshers and focus on core SQL Server concepts, basic querying techniques, and foundational database knowledge. They can be used to assess a candidate’s understanding of relational databases and SQL Server.