Microsoft SQL Server Database Quiz Questions

In SQL Server, each column in a table must have a unique name to avoid ambiguity.

Different tables can have columns with the same name because column names are only required to be unique within a single table.

The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set, starting at 1.

SQL Server requires column names to be explicitly defined in queries and does not support dynamic column names.

A CTE allows you to define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

A table in SQL Server can only have one primary key, although the primary key can consist of multiple columns (composite key).

Columns in SQL Server can have default values, which are used when no value is provided for the column during an insert operation.

A FOREIGN KEY constraint enforces referential integrity by ensuring that values in one table match values in another table.

The ALTER TABLE statement in SQL Server can be used to rename columns with the sp_rename stored procedure.

The SELECT DISTINCT statement ensures that the result set contains only unique rows by removing duplicates.

The UPDATE statement is used to modify existing rows in a table and can change the values of one or more columns.

By default, SQL Server columns allow NULL values unless specified otherwise using the NOT NULL constraint.

Constraints can be defined at both the column level and the table level in SQL Server, depending on their scope.

Indexing columns can significantly improve the performance of queries, especially on large tables.

The ALTER TABLE statement can be used to drop columns from a table using the DROP COLUMN clause.

The COUNT() function returns the number of rows in a table or a specified subset of rows.

The ALTER TABLE statement with the ADD clause is used to add a new column to an existing table.

The SUM() function calculates the total sum of a numeric column in a result set.

The GROUP BY clause is used to group rows that have the same values in specified columns into aggregate data.

The ORDER BY clause is used to sort the result set of a query in ascending or descending order.

A FULL OUTER JOIN returns all rows from both tables, with matching rows where available and NULLs where there are no matches.

The MAX() function returns the maximum value of a specified column in the result set.

The UPPER() function converts all characters in a string to uppercase.

The TRUNCATE TABLE command removes all records from a table but keeps the table structure for future use.

The WHERE clause is used to filter records that meet specific criteria.

The ALTER TABLE command is used to modify the structure of an existing table, such as adding or dropping columns.

The GETDATE() function returns the current date and time from the system.

The ROUND() function rounds a numeric value to the specified number of decimal places.

The DROP TABLE command is used to delete a table and all its data from the database.

The CONCAT() function combines multiple strings into a single string.

SQL Server supports various data types such as INT, VARCHAR, and DATE for columns.

ISNULL() and COALESCE() functions can be used to handle NULL values by providing default values.

Keywords like UNION and INTERSECT are used to combine or compare result sets from multiple queries.

Constraints like PRIMARY KEY and FOREIGN KEY enforce data integrity by defining rules for data consistency.

Functions like GETDATE() and DATEADD() are used to handle date and time values.

Commands like UPDATE and INSERT are used to modify data within a table.

Clauses like WHERE, GROUP BY, and ORDER BY are used to filter, group, and sort results in a SELECT statement.

Functions like AVG(), COUNT(), and SUM() are used to perform aggregate calculations on data.

Commands like ALTER TABLE and DROP COLUMN are used to change a table’s structure.

Operators like =, <>, and BETWEEN are used to compare values in a WHERE clause.

Functions like CONCAT(), SUBSTRING(), and REPLACE() are used for string manipulation.

Foreign keys and primary keys are used to enforce referential integrity in SQL Server.

Commands like COMMIT and ROLLBACK are used to manage transactions and control changes.

Statements like CREATE INDEX and DROP INDEX are used to manage indexes on tables.

Functions like DATEPART(), YEAR(), and MONTH() are used to extract specific parts from a date.