View Full Microsoft DP-800 Exam Dumps and Practice Test Dumps.

 

Question 1

Which SQL statement is used to retrieve data from one or more tables in a SQL Server database?

1. SELECT
2. GET
3. FETCH
4. READ

Correct Answer: 1

Explanation

The SELECT statement is used to retrieve data from tables, views, and other queryable objects in SQL Server. It can return specific columns, filter rows with WHERE, sort results with ORDER BY, and combine data from multiple tables using joins. For example, SELECT Name, Salary FROM Employees returns the selected columns from the Employees table. Understanding SELECT is one of the most important SQL skills because it is used frequently for data analysis, reporting, and database administration.

Question 2

Which SQL clause is used to filter rows before they are returned by a query?

1. ORDER BY
2. WHERE
3. GROUP BY
4. HAVING

Correct Answer: 2

Explanation

The WHERE clause filters individual rows based on a specified condition. For example, SELECT * FROM Employees WHERE Department = ‘IT’ returns only employees working in the IT department. The WHERE clause is evaluated before grouping and aggregation in a typical query-processing flow. HAVING is used to filter groups after aggregation. Using WHERE effectively can reduce the amount of data processed and returned, which can also improve query performance when appropriate indexes exist.

Question 3

Which SQL clause is used to sort query results?

1. SORT BY
2. GROUP BY
3. ORDER BY
4. ARRANGE BY

Correct Answer: 3

Explanation

The ORDER BY clause sorts the rows returned by a query. By default, sorting is ascending, but DESC can be used for descending order. For example, SELECT Name, Salary FROM Employees ORDER BY Salary DESC displays employees from the highest salary to the lowest. Multiple columns can also be included in the ORDER BY clause. Sorting can require additional processing, especially for large result sets, so indexes and query design should be considered when performance is important.

Question 4

Which SQL function returns the number of rows that match a query condition?

1. SUM()
2. COUNT()
3. TOTAL()
4. ROWS()

Correct Answer: 2

Explanation

The COUNT() aggregate function returns the number of rows or non-NULL values depending on how it is used. COUNT(*) counts rows in the result set, while COUNT(column_name) counts non-NULL values in that column. For example, SELECT COUNT(*) FROM Employees returns the total number of employee rows. COUNT() is commonly used in reporting and data analysis. Understanding how NULL values affect different forms of COUNT() is important when creating accurate database queries.

Question 5

Which SQL statement is used to add new rows to a table?

1. INSERT
2. ADD
3. CREATE ROW
4. APPEND

Correct Answer: 1

Explanation

The INSERT statement adds new rows to a table. A common form is INSERT INTO Employees (Name, Department) VALUES (‘Ali’, ‘IT’). The column list identifies where the values should be placed. When inserting data, the values must be compatible with the corresponding column data types and constraints. If required columns are missing or invalid values are supplied, SQL Server can reject the operation. Proper validation and transaction handling are important when inserting production data.

Question 6

Which SQL statement is used to modify existing rows in a table?

1. CHANGE
2. MODIFY
3. UPDATE
4. ALTER ROW

Correct Answer: 3

Explanation

The UPDATE statement changes existing rows in a table. It normally uses a WHERE clause to identify the rows that should be changed. For example, UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 10 changes the salary for one employee. Without an appropriate WHERE condition, an update can affect every row in the table. Before running important updates, administrators should verify the filtering condition and, when appropriate, use a transaction so the change can be reviewed or rolled back.

Question 7

Which SQL statement removes existing rows from a table?

1. REMOVE
2. DELETE
3. DROP ROW
4. ERASE

Correct Answer: 2

Explanation

The DELETE statement removes rows from a table. A WHERE clause is normally used to identify which rows should be deleted. For example, DELETE FROM Employees WHERE EmployeeID = 25 removes the employee with that ID. If the WHERE clause is omitted, all rows in the table can be deleted. The table itself remains available after a DELETE. Administrators should carefully verify delete conditions, especially in production environments, because accidental deletion can cause serious data loss.

Question 8

Which SQL keyword removes duplicate rows from a query result?

1. UNIQUE
2. ONLY
3. DISTINCT
4. DEDUP

Correct Answer: 3

Explanation

The DISTINCT keyword removes duplicate combinations of selected column values from a query result. For example, SELECT DISTINCT Department FROM Employees returns each department only once. DISTINCT applies to the complete set of selected columns, not just one column unless only that column is selected. It can be useful for reporting and discovering unique values. However, using DISTINCT unnecessarily may add processing overhead, so query design should focus on returning the data that is actually required.

Question 9

Which SQL clause groups rows so that aggregate functions can be applied to each group?

1. GROUP BY
2. ORDER BY
3. PARTITION BY
4. CLUSTER BY

Correct Answer: 1

Explanation

The GROUP BY clause groups rows based on one or more columns. Aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() can then calculate values for each group. For example, SELECT Department, COUNT(*) FROM Employees GROUP BY Department returns the number of employees in each department. Columns selected alongside aggregate functions generally need to be included in the grouping when they are not themselves aggregated. GROUP BY is widely used in reporting and analytics queries.

Question 10

Which SQL clause filters grouped results after an aggregate calculation?

1. WHERE
2. FILTER
3. HAVING
4. GROUP FILTER

Correct Answer: 3

Explanation

The HAVING clause filters groups created by GROUP BY. It is commonly used when the filtering condition involves an aggregate value. For example, HAVING COUNT(*) > 10 returns only groups containing more than ten rows. This differs from WHERE, which filters individual rows before grouping takes place. Using HAVING correctly is important when building reports that need conditions based on totals, averages, counts, or other aggregate calculations.

Question 11

Which type of JOIN returns matching rows from both tables and excludes nonmatching rows?

1. FULL JOIN
2. INNER JOIN
3. LEFT JOIN
4. CROSS JOIN

Correct Answer: 2

Explanation

An INNER JOIN returns rows where the join condition matches in both tables. For example, joining Employees and Departments on DepartmentID returns employees whose department has a matching record. Rows without a match on either side are excluded. INNER JOIN is commonly used when related data must exist in both tables for a result to be meaningful. Choosing the correct join type is important because different joins can produce significantly different result sets.

Question 12

Which JOIN returns all rows from the left table and matching rows from the right table?

1. LEFT JOIN
2. INNER JOIN
3. RIGHT JOIN
4. CROSS JOIN

Correct Answer: 1

Explanation

A LEFT JOIN, also called a LEFT OUTER JOIN, returns every row from the left table and matching rows from the right table. If no matching row exists on the right side, the right-side columns contain NULL values. This is useful when you want to find all records from one table, including records that have no related record in another table. For example, it can identify employees who have not yet been assigned to a project.

Question 13

Which SQL object is commonly used to store a reusable query that behaves like a virtual table?

1. Trigger
2. Stored procedure
3. View
4. Constraint

Correct Answer: 3

Explanation

A view is a database object that stores a query definition and presents its result as a virtual table. Views can simplify complex queries, provide a consistent interface to data, and help restrict access to selected columns or rows. A view normally does not store a separate copy of the underlying data. When users query the view, SQL Server retrieves data from its underlying objects according to the view definition. Views are useful for reporting, security, and abstraction.

Question 14

Which database object is designed to store a reusable set of SQL statements that can accept parameters?

1. View
2. Stored procedure
3. Index
4. Synonym

Correct Answer: 2

Explanation

A stored procedure is a programmable database object containing SQL statements and logic that can be executed as a unit. Stored procedures can accept parameters, perform data modifications, return results, and include conditional or procedural logic. They can help centralize business logic and reduce repeated SQL code. They are also useful for controlling how applications interact with database tables. Proper permissions and parameter handling should be used to maintain security and avoid problems such as SQL injection.

Question 15

Which database object automatically executes when specified data modification events occur on a table or view?

1. Trigger
2. View
3. Index
4. Sequence

Correct Answer: 1

Explanation

A trigger is a database object that automatically executes in response to specified events, such as INSERT, UPDATE, or DELETE operations. Triggers can be used for auditing, enforcing certain business rules, or maintaining related information. However, they should be designed carefully because they execute automatically and can make data changes harder to understand or troubleshoot. Excessive trigger logic can also affect performance. Administrators should document triggers clearly so that application developers and database administrators understand their behavior.

Question 16

Which database object is primarily used to improve the speed of data retrieval?

1. Constraint
2. Index
3. Trigger
4. Alias

Correct Answer: 2

Explanation

An index is a database structure designed to help SQL Server locate rows more efficiently. Instead of scanning an entire table for every query, the database can use a suitable index to find required data more quickly. Indexes can improve read performance, but they also require storage and can increase the cost of INSERT, UPDATE, and DELETE operations because indexes may need to be maintained. Good index design requires understanding query patterns, data distribution, and workload requirements.

Question 17

Which SQL Server feature is designed to ensure that a column or combination of columns uniquely identifies each row in a table?

1. Foreign key
2. CHECK constraint
3. Primary key
4. DEFAULT constraint

Correct Answer: 3

Explanation

A primary key uniquely identifies each row in a table. It prevents duplicate key values and normally does not allow NULL values. A table can have only one primary key constraint, although the primary key can contain multiple columns, known as a composite key. Primary keys are important for data integrity and are often referenced by foreign keys in related tables. Choosing an appropriate primary key helps establish reliable relationships between database entities.

Question 18

Which constraint is used to maintain a relationship between a column in one table and a key in another table?

1. UNIQUE
2. CHECK
3. DEFAULT
4. FOREIGN KEY

Correct Answer: 4

Explanation

A foreign key establishes a relationship between tables by requiring values in one table to correspond to a key in another table. For example, an Orders table may contain CustomerID that references the CustomerID primary key in a Customers table. This helps maintain referential integrity and prevents invalid references when properly configured. Foreign keys can also define actions for updates or deletes, depending on the database design. They are essential for maintaining reliable relationships in relational databases.

Question 19

Which SQL Server data type is generally appropriate for storing variable-length text?

1. VARCHAR
2. INTEGER
3. DATE
4. BIT

Correct Answer: 1

Explanation

VARCHAR stores variable-length character data. It is useful when text values can have different lengths and Unicode support is not required. SQL Server also provides NVARCHAR for Unicode text, which is appropriate when applications need to store characters from many languages. Choosing the correct text data type helps control storage requirements and supports application requirements. The maximum length should also be selected carefully rather than automatically assigning unnecessarily large sizes to every text column.

Question 20

Which SQL Server data type is designed to store date and time values together?

1. DATE
2. TIME
3. DATETIME2
4. YEAR

Correct Answer: 3

Explanation

DATETIME2 is a SQL Server data type designed to store both date and time values with greater precision and a wider range than the older DATETIME type. It is commonly preferred for new database designs when both date and time are required. DATE stores only a date, while TIME stores only a time. Selecting the correct temporal data type helps prevent unnecessary conversions and makes queries and calculations easier to understand and maintain.