Unit 5: SQL Server For Backend Development - Practice Quiz
1 What is the main purpose of a database?
2 What does DBMS stand for?
3 Which company develops Microsoft SQL Server?
4 Which type of database stores data in tables made of rows and columns?
5 Which SQL command is used to create a new database table?
6 Which SQL command permanently removes an existing table and its data?
7 What does a row in a SQL Server table usually represent?
8 Which SQL command adds a new row to a table?
9 Which SQL command changes existing data in a table?
10 Which constraint uniquely identifies each row in a table?
11
Which constraint prevents a column from containing NULL values?
12 Which SQL clause filters rows according to a condition?
13 Which SQL clause sorts the rows in a query result?
14 Which SQL operator checks whether a value is within a specified range?
15 Which SQL operator is commonly used for pattern matching in text?
16 Which join returns only rows with matching values in both joined tables?
17 What is a SQL Server view?
18 Which type of SQL Server view has a unique clustered index and stores its result physically?
19
Which statement correctly starts the creation of a user-defined view named EmployeeView?
20 What is a stored procedure in SQL Server?
21 A banking application transfers money by deducting an amount from one account and adding it to another. Which database feature ensures that either both operations occur or neither operation occurs?
22 A developer wants SQL Server to generate a sequential numeric value automatically whenever a row is inserted. Which column property should be used?
23 An application stores customers, orders, and products with well-defined relationships and requires complex joins. Which type of database is most appropriate?
24
How are CREATE TABLE, INSERT, and GRANT classified, respectively?
25
The Orders table already contains data. Which statement safely adds an optional Notes column?
ALTER Orders CREATE Notes nvarchar(200) NULL;
ALTER TABLE Orders ADD Notes nvarchar(200) NULL;
MODIFY TABLE Orders Notes nvarchar(200) NULL;
UPDATE TABLE Orders ADD Notes nvarchar(200) NULL;
26 Which SQL Server statement increases the salary of employees in the Sales department by 10% using a join?
ALTER e SET Salary = Salary * 1.10 FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID;
INSERT e SET Salary = Salary * 1.10 FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID;
UPDATE e SET Salary = Salary * 1.10 FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE d.Name = 'Sales';
UPDATE Employees SET Salary = Salary * 1.10 JOIN Departments d WHERE d.Name = 'Sales';
27
Every order must refer to an existing customer. Which constraint should be placed on Orders.CustomerID?
28 Which query returns departments having more than five employees?
SELECT DepartmentID FROM Employees WHERE COUNT(*) > 5 GROUP BY DepartmentID;
SELECT DepartmentID FROM Employees GROUP BY DepartmentID WHERE COUNT(*) > 5;
SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 5;
SELECT DepartmentID FROM Employees HAVING COUNT(*) > 5 ORDER BY DepartmentID;
29
Which condition correctly selects employees whose ManagerID has no value?
ManagerID = NULL
ManagerID == NULL
ManagerID IS NULL
ManagerID IN NULL
30
A report must list every customer, including customers who have placed no orders. Which join should begin with Customers?
CROSS JOIN Orders
RIGHT JOIN Customers
LEFT JOIN Orders
INNER JOIN Orders
31
The Employees table contains both EmployeeID and ManagerID, where a manager is also an employee. Which technique retrieves each employee together with the manager's name?
Employees using two aliases
Employees without aliases
Employees by ManagerID
Employees with itself
32
A view displays only nonconfidential employee columns, and users receive SELECT permission on the view but not the base table. What is the main purpose of this design?
33 In SQL Server, what must be created first to materialize and index the result of a schema-bound view?
34 Which statement creates a user-defined view showing only active products?
CREATE VIEW ActiveProducts AS SELECT ProductID, Name FROM Products WHERE IsActive = 1;
CREATE TABLE ActiveProducts AS SELECT ProductID, Name FROM Products WHERE IsActive = 1;
SELECT VIEW ActiveProducts AS ProductID, Name FROM Products WHERE IsActive = 1;
CREATE ActiveProducts VIEW SELECT ProductID, Name FROM Products WHERE IsActive = 1;
35 Why is a stored procedure often preferred when the same multi-statement database operation is executed by several applications?
36
Given CREATE PROCEDURE GetOrders @CustomerID int AS ..., which statement correctly executes the procedure for customer 25?
EXEC GetOrders @CustomerID = 25;
RUN GetOrders WITH CustomerID = 25;
CALL GetOrders CustomerID AS 25;
SELECT GetOrders FROM CustomerID = 25;
37
A table must reject rows where EndDate is earlier than StartDate, while allowing equal dates. Which constraint expression is appropriate?
UNIQUE (EndDate >= StartDate)
CHECK (EndDate >= StartDate)
DEFAULT (EndDate >= StartDate)
FOREIGN KEY (EndDate >= StartDate)
38 A query must return customers who have at least one order. Which condition directly tests whether a related order row exists?
WHERE ANY (SELECT CustomerID FROM Orders) <> c.CustomerID
WHERE ALL (SELECT CustomerID FROM Orders) = c.CustomerID
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)
WHERE c.CustomerID BETWEEN (SELECT CustomerID FROM Orders)
39 A developer renames a column in a base table that is referenced by a view. Which view option helps prevent such schema changes from breaking the view definition?
WITH RECOMPILE
WITH ENCRYPTION
WITH CHECK OPTION
WITH SCHEMABINDING
40 A stored procedure must return a newly generated order ID through a parameter. How should that parameter be declared?
@OrderID int RETURN
@OrderID int OUTPUT
@OrderID int IDENTITY
@OrderID int DEFAULT
41
A relation Enrollment(StudentID, CourseID, InstructorID, InstructorOffice) has the functional dependencies (StudentID, CourseID) -> InstructorID and InstructorID -> InstructorOffice. Assuming (StudentID, CourseID) is the only candidate key, what is the highest normal form satisfied?
42
A stored procedure creates a local temporary table named #Work and then calls a nested stored procedure. Which statement correctly describes the table's scope?
43 An application requires multi-row ACID transactions, enforced foreign keys, and frequent joins across highly related entities. Which database type is the most natural primary choice?
44 In SQL Server, both commands are executed inside an explicit transaction against a table with an identity column. Which comparison is correct?
TRUNCATE cannot be rolled back; DELETE can be rolled back
DELETE and TRUNCATE both preserve the current identity value
DELETE is rollback-capable and resets identity; TRUNCATE normally preserves identity
TRUNCATE is rollback-capable and resets identity; DELETE normally preserves identity
45 How does SQL Server normally locate base-table rows from the leaf level of a nonclustered index?
46
Consider UPDATE p SET Price = s.Price FROM Products AS p JOIN PriceStage AS s ON s.ProductID = p.ProductID;. If PriceStage contains multiple rows for one ProductID, what is the safest conclusion?
47
A nullable column is defined as Price decimal(10,2) CHECK (Price > 0). Which value can still be inserted without violating this constraint?
-0.01
0.00
-100.00
NULL
48
A child table has a composite foreign key (RegionID, OfficeID) referencing a composite key in a parent table. What happens when a child row contains (7, NULL)?
(7, NULL) must already exist
49
Why does SELECT Quantity * UnitPrice AS Total FROM Sales WHERE Total > 100; fail, and what is the standard correction?
Total is reserved by SQL Server; delimit the alias using square brackets
SELECT aliases require aggregation; add GROUP BY Quantity, UnitPrice
WHERE is processed before the alias exists; use a derived table or repeat the expression
WHERE cannot compare numeric expressions; replace it with a HAVING clause
50
A query uses WHERE A.ID NOT IN (SELECT B.ID FROM B), and the subquery returns at least one NULL. What is the most reliable null-safe anti-join replacement?
WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.ID = A.ID)
WHERE A.ID <> ALL (SELECT ISNULL(B.ID, A.ID) FROM B)
WHERE EXISTS (SELECT 1 FROM B WHERE B.ID <> A.ID)
WHERE A.ID != (SELECT DISTINCT B.ID FROM B)
51 A query must return every customer, together with only that customer's open orders when such orders exist. Which predicate placement preserves customers having no open orders?
FULL JOIN Orders AS o ON o.CustomerID = c.CustomerID WHERE o.Status = 'Open'
INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID AND o.Status = 'Open'
LEFT JOIN Orders AS o ON o.CustomerID = c.CustomerID AND o.Status = 'Open'
LEFT JOIN Orders AS o ON o.CustomerID = c.CustomerID WHERE o.Status = 'Open'
52
Table A contains join-key values (1, 1, 2, NULL), while table B contains (1, 1, 1, 3, NULL). How many rows result from A FULL OUTER JOIN B ON A.KeyValue = B.KeyValue?
53
A view is defined using SELECT TOP (100) PERCENT ... ORDER BY CreatedAt DESC. What ordering guarantee does a later SELECT * FROM ViewName receive?
ORDER BY
54 After creating a schema-bound view that satisfies SQL Server's indexed-view restrictions, which index must be created first to materialize it?
55
A user-defined view is created with WITH SCHEMABINDING and references dbo.Accounts. What is a principal consequence?
56 Which statement correctly distinguishes a stored procedure's integer return status from its output mechanisms?
RETURN supplies one integer status; output parameters and result sets return data
RETURN supplies any SQL type; output parameters are restricted to integers
RETURN supplies a complete result set; SELECT supplies only status codes
RETURN and output parameters both support only one nullable integer value
57
Inside a procedure's CATCH block, XACT_STATE() returns -1. What transaction action is valid?
58 A procedure's query performs well for common parameter values but poorly for rare values because its cached plan was compiled using an unrepresentative first parameter. Which targeted mitigation recompiles only that statement for each execution?
SET NOCOUNT ON to the procedure
OPTION (RECOMPILE) to the affected statement
DISTINCT to the affected statement
WITH SCHEMABINDING to the procedure
59
An INSERT into Orders fires a trigger that inserts into another identity table. Which expression returns the identity generated for Orders rather than the trigger's identity?
@@IDENTITY
IDENT_SEED('Orders')
SCOPE_IDENTITY()
IDENT_CURRENT('TriggerTable')
60
A grouped query must retain departments having at least three rows in total and at least two rows whose Amount is non-null. Which HAVING clause is correct?
HAVING COUNT(*) >= 3 AND COUNT(ISNULL(Amount, 0)) = 2
HAVING COUNT(Amount) >= 3 AND COUNT(*) >= 2
HAVING COUNT(*) >= 3 AND COUNT(Amount) >= 2
HAVING SUM(Amount) >= 3 AND COUNT(Amount) >= 2
Did this save you a night before the exam?
LPU Notes is free, and it stays free. Ads cover part of the server bill. The rest comes out of a student's own pocket: the domain, the storage, and keeping the site up through the weeks everyone needs it at once.
The payment button didn't load. An ad blocker or a filtered network is the usual reason. to try again.
Nothing here is ever locked, and nothing unlocks. Chip in only if it was worth it. What it pays for →