C# - C Sharp: DBI202 - PT2

Q1: What is the main purpose of an index in SQL Server?
To create triggers
To back up data
To improve query performance
To manage foreign keys

Q2: How many clustered indexes can a SQL table have?
Only one
Two
Three
Unlimited

Q3: Which index type stores data in sorted order?
Non-clustered index
Filtered index
Hash index
Clustered index

Q4: What command is used to create an index?
ADD INDEX
CREATE INDEX
MAKE INDEX
SET INDEX

Q5: Which of the following can improve query performance the most?
Proper indexing
More triggers
Larger tables
Disabling constraints

Q6: What is a SQL View?
A trigger
A stored procedure
A virtual table based on a query
A database schema

Q7: Can a view be based on multiple tables?
No
Yes
Only one table
Only with joins disabled

Q8: Which command updates an existing view?
ALTER VIEW
UPDATE VIEW
MODIFY VIEW
CREATE OR REPLACE

Q9: What is the main advantage of a view?
Stores physical data
Increases table size
Simplifies complex queries
Enables triggers

Q10: What is a stored procedure (SP)?
A backup routine
An index file
A transaction log
A precompiled group of SQL statements

Q11: How do you execute a stored procedure?
EXEC ProcedureName
RUN ProcedureName
EXECUTE SQL
CALL ProcedureName

Q12: Which keyword defines input parameters in a stored procedure?
#parameterName
@parameterName
$parameterName
%parameterName

Q13: What is a trigger used for?
Improving performance
Storing data permanently
Encrypting passwords
Automatically executing code after an event

Q14: Which trigger type fires after an insert?
BEFORE INSERT
INSTEAD OF DELETE
AFTER INSERT
ON UPDATE

Q15: Can a table have multiple triggers for the same event?
No
Yes
Only one
Only for updates

Q16: Which SQL clause limits the number of rows returned?
TOP
LIMIT
FIRST
MAX

Q17: How do you select the top 10 rows from a table?
SELECT FIRST 10 * FROM Table
SELECT 10 * FROM Table
SELECT TOP 10 * FROM Table
SELECT * LIMIT 10

Q18: What does CASE...WHEN do?
Creates a loop
Defines indexes
Declares a variable
Implements conditional logic in SQL

Q19: Which statement is correct for CASE?
CASE IF condition THEN result END
CASE WHEN condition THEN result END
CASE ON condition RETURN result
CASE EXEC condition END

Q20: What is an ERD?
Entity Relationship Diagram
Entity Record Definition
External Relational Data
Execution Runtime Diagram

Q21: What does ERD stand for in database design?
Entity Reference Data
External Record Design
Entity Row Definition
Entity Relationship Diagram

Q22: What does a relationship in an ERD represent?
A data type
A connection between two entities
A database schema
A column name

Q23: What SQL keyword can be used with Common Table Expressions (CTE)?
WITH
JOIN
USING
HAVING

Q24: What does a CTE (Common Table Expression) do?
Defines a permanent table
Creates a view
Creates a temporary result set used within a query
Declares an index

Q25: How do you start a CTE definition?
CREATE CTE CTE_Name
DEFINE CTE CTE_Name
BEGIN CTE CTE_Name
WITH CTE_Name AS (SELECT ... )

Q26: Can a CTE be recursive?
No
Yes
Only for views
Only for triggers

Q27: What is the correct syntax for an IF statement in SQL Server?
IF (condition) BEGIN ... END
IF condition THEN
WHEN condition THEN
IF condition DO

Q28: What keyword is used for alternate logic in SQL IF statements?
OTHER
CASE
WHEN
ELSE

Q29: What is a trigger most commonly used for?
Creating indexes
Defining schemas
Auditing data changes
Backing up data

Q30: What command removes an existing trigger?
DELETE TRIGGER
DROP TRIGGER
REMOVE TRIGGER
ALTER TRIGGER

Q31: Which keyword defines the beginning of a stored procedure?
MAKE PROCEDURE
BEGIN PROCEDURE
NEW PROCEDURE
CREATE PROCEDURE

Q32: What is a major advantage of stored procedures?
Improved performance and reusability
Reduced storage usage
Faster backup times
Easier ERD creation

Q33: Which clause can be used to limit rows returned from a view?
LIMIT
MAXROWS
ROWNUM
TOP

Q34: What happens if a trigger fails during execution?
Nothing happens
The triggering transaction may be rolled back
It continues silently
It logs a warning only

Q35: What is a non-clustered index?
An index that stores data separately from the table
The main data storage
A memory index
A clustered file

Q36: Can a view include joins between multiple tables?
No
Only one table allowed
Only with CTEs
Yes

Q37: What clause can be used inside a trigger?
ON...AS
BEGIN...END
DO...WHILE
CASE...WHEN

Q38: What is a recursive trigger?
A trigger that calls itself directly or indirectly
A trigger that never runs
A disabled trigger
A temporary trigger

Q39: What SQL command disables a trigger?
DELETE TRIGGER
STOP TRIGGER
DISABLE TRIGGER
DROP TRIGGER

Q40: Which keyword is used to re-enable a disabled trigger?
RUN TRIGGER
ENABLE TRIGGER
ALTER TRIGGER
RESUME TRIGGER

Q41: What is the main purpose of the CASE WHEN statement?
To define loops
To declare variables
To create indexes
To implement conditional logic within queries

Q42: Which of the following correctly uses CASE WHEN?
CASE salary IF 5000 THEN 'High' ELSE 'Low'
IF salary > 5000 THEN 'High' ELSE 'Low'
SELECT CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END FROM Employees
WHEN salary OVER 5000 THEN 'High'

Q43: Can CASE WHEN be used inside ORDER BY?
Yes
No
Only with subqueries
Only inside procedures

Q44: What clause is often used with CASE WHEN to handle NULL values?
HAVING
ISNULL
DISTINCT
TOP

Q45: What is the purpose of the TOP keyword in SQL Server?
To limit the number of rows returned
To join tables
To rename columns
To create indexes

Q46: Which query correctly selects the first 10 rows from a table?
SELECT * FROM Employees LIMIT 10
SELECT FIRST 10 * FROM Employees
SELECT MAX(10) FROM Employees
SELECT TOP 10 * FROM Employees

Q47: Can the TOP keyword be used with PERCENT?
No
Only in MySQL
Only in views
Yes

Q48: Which SQL clause determines the order before applying TOP?
ORDER BY
GROUP BY
HAVING
WHERE

Q49: What is the main advantage of indexes in SQL Server?
Reduced disk space
More complex design
Easier triggers
Faster query performance

Q50: What is a clustered index?
An index stored separately
An index that defines the physical order of data
A temporary index
A foreign key

Q51: How many clustered indexes can a table have?
Multiple
Unlimited
One
Depends on schema

Q52: What command removes an index?
DROP INDEX
DELETE INDEX
REMOVE INDEX
ALTER INDEX OFF

Q53: What SQL clause can you use to improve performance with large joins?
GROUP BY
INDEX
CASE
TOP

Q54: Can an indexed view improve query performance?
No
Only in tempdb
Yes
Only with triggers

Q55: What type of SQL object stores precompiled SQL statements?
View
Trigger
Stored Procedure
CTE

Q56: What command executes a stored procedure?
EXEC
RUN
CALL
DO

Q57: Can a stored procedure return values?
No
Only scalar values
Yes
Only tables

Q58: What is the purpose of the OUTPUT parameter in stored procedures?
To define triggers
To return values to the caller
To limit rows
To log errors

Q59: What keyword begins a trigger definition?
CREATE TRIGGER
BEGIN TRIGGER
DEFINE TRIGGER
NEW TRIGGER

Q60: What SQL concept best represents data entities and relationships?
Index
Procedure
Trigger
ERD

Q61: Which SQL operator checks whether a value falls within a specified range?
LIKE
BETWEEN
IN
EXISTS

Q62: What is the result of the following query?
SELECT DISTINCT City FROM Customers;
Returns all customer records
Returns unique city names only
Returns duplicate city names
Returns cities sorted alphabetically

Q63: Which SQL function converts all characters in a string to uppercase?
CAPITAL()
UCASE()
UPPER()
TOUPPER()

Q64: Which function returns the number of characters in a string?
SIZE()
COUNT()
LENGTH()
TOTAL()

Q65: Which clause is used to remove duplicate rows from the result set?
UNIQUE
DISTINCT
DIFFERENT
FILTER

Q66: Which query returns employees whose names start with the letter 'A'?
WHERE Name = 'A%'
WHERE Name LIKE 'A%'
WHERE Name IN ('A')
WHERE Name STARTS 'A'

Q67: What does the underscore (_) wildcard represent in a LIKE pattern?
Any sequence of characters
Exactly one character
Only digits
A space character

Q68: Which SQL function replaces NULL with a specified value in SQL Server?
IFNULL()
NVL()
ISNULL()
COALESCEONLY()

Q69: What will the following expression return?
SELECT 15 % 4;
3
4
2
1

Q70: Which SQL function returns the current system date and time in SQL Server?
CURRENTTIME()
NOW()
GETDATE()
SYSDATE()

Q71: Which function extracts the year from a date?
YEAR()
DATEYEAR()
EXTRACTYEAR()
GETYEAR()

Q72: Which function returns the current database name in SQL Server?
DATABASE()
CURRENT_DB()
DB_NAME()
GETDB()

Q73: What does the NOT IN operator do?
Returns rows matching all listed values
Returns rows whose value is not found in the list
Returns duplicate rows
Returns only NULL values

Q74: Which SQL statement correctly checks whether Salary is NULL?
Salary = NULL
Salary == NULL
Salary IS NULL
Salary NULL

Q75: Which of the following statements about NULL is true?
NULL equals 0
NULL equals an empty string
NULL represents an unknown or missing value
NULL is always FALSE

Q76: Which operator combines two conditions where both must be true?
OR
NOT
AND
XOR

Q77: Which operator returns rows when at least one condition is true?
AND
OR
NOT
BETWEEN

Q78: Which query returns products whose price is greater than or equal to 100?
WHERE Price > 100
WHERE Price => 100
WHERE Price >= 100
WHERE Price <> 100

Q79: Which SQL function returns the absolute value of a number?
ROUND()
ABS()
SIGN()
MOD()

Q80: Which of the following statements about DISTINCT is correct?
DISTINCT removes duplicate rows based on selected columns
DISTINCT sorts data automatically
DISTINCT only works with numeric columns
DISTINCT can only be used with COUNT()

Q81: Which SQL operator combines the results of two SELECT statements while removing duplicate rows?
MERGE
UNION
UNION ALL
INTERSECT

Q82: Which statement about UNION ALL is correct?
It removes duplicate rows.
It sorts the result automatically.
It returns all rows including duplicates.
It can only combine two tables.

Q83: Which operator returns only the rows that appear in both SELECT statements?
UNION
INTERSECT
EXCEPT
JOIN

Q84: Which operator returns rows from the first query that do not appear in the second query?
MINUS ALL
UNION
EXCEPT
INNER JOIN

Q85: Which statement about UNION is true?
Both SELECT statements must return the same number of columns.
The table names must be identical.
Both tables must have primary keys.
UNION only works on numeric columns.

Q86: Which SQL keyword checks whether a subquery returns at least one row?
ANY
ALL
EXISTS
IN

Q87: Which clause is commonly used with EXISTS?
Subquery
ORDER BY
TOP
GROUP BY

Q88: What does the IN operator compare?
A value against a list of values
A table against another table
A column against a table name
Two databases

Q89: Which operator returns TRUE if a value matches at least one value returned by a subquery?
ALL
ANY
BETWEEN
LIKE

Q90: Which operator requires a comparison to be true for every value returned by a subquery?
IN
ANY
ALL
EXISTS

Q91: Which SQL clause can contain a subquery?
WHERE only
FROM only
SELECT only
All of the above

Q92: What is a correlated subquery?
A subquery that executes independently.
A subquery that references columns from the outer query.
A subquery with GROUP BY only.
A subquery inside a JOIN clause only.

Q93: Which SQL function assigns a unique sequential number to each row?
RANK()
ROW_NUMBER()
DENSE_RANK()
COUNT()

Q94: Which window function gives the same rank to tied rows and skips the next rank?
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()

Q95: Which window function gives the same rank to tied rows without skipping numbers?
ROW_NUMBER()
RANK()
DENSE_RANK()
COUNT()

Q96: Which clause is mandatory when using ROW_NUMBER()?
HAVING
ORDER BY inside OVER()
GROUP BY
DISTINCT

Q97: Which keyword begins the definition of a window for window functions?
PARTITION
WINDOW
OVER
FRAME

Q98: What is the purpose of PARTITION BY in a window function?
To split data into groups for window calculations.
To create partitions on disk.
To divide a table into databases.
To delete duplicate rows.

Q99: Which SQL statement starts a transaction explicitly?
START QUERY
BEGIN TRANSACTION
OPEN TRANSACTION
NEW TRANSACTION

Q100: Which statement permanently saves all changes made during the current transaction?
SAVE
COMMIT
ROLLBACK
FINISH

Q101: Which ACID property ensures that a transaction is treated as a single indivisible unit?
Consistency
Atomicity
Isolation
Durability

Q102: Which ACID property guarantees that committed data will survive a system failure?
Isolation
Consistency
Durability
Atomicity

Q103: Which command undoes all changes made during the current transaction?
UNDO
CANCEL
ROLLBACK
RESTORE

Q104: What is the purpose of SAVEPOINT in a transaction?
To permanently save changes
To mark a point for partial rollback
To end a transaction
To lock a table

Q105: Which normal form eliminates repeating groups in a table?
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)

Q106: Which normal form removes partial dependency on a composite key?
1NF
2NF
3NF
4NF

Q107: Which normal form removes transitive dependency?
1NF
2NF
3NF
5NF

Q108: Which SQL constraint ensures a column satisfies a specified condition?
CHECK
UNIQUE
VERIFY
ASSERT

Q109: Which statement correctly creates a CHECK constraint?
Age CHECK > 18
CHECK (Age >= 18)
VALIDATE Age >=18
Age VERIFY >=18

Q110: Which keyword automatically generates sequential numeric values in SQL Server?
AUTO_INCREMENT
IDENTITY
SERIAL
SEQUENCEID

Q111: Which object can generate unique numbers independently of a table in SQL Server?
INDEX
SEQUENCE
TRIGGER
CURSOR

Q112: Which SQL object allows processing one row at a time?
VIEW
CURSOR
FUNCTION
INDEX

Q113: Which statement opens a cursor?
BEGIN CURSOR
START CURSOR
OPEN CursorName
EXEC CursorName

Q114: Which command retrieves the next row from a cursor?
NEXT
READ
FETCH NEXT
MOVE NEXT

Q115: Which command releases the resources used by a cursor?
DROP CURSOR
FREE CURSOR
DEALLOCATE
REMOVE CURSOR

Q116: Which SQL Server function returns the identity value generated in the current scope?
@IDENTITY
IDENT_CURRENT()
SCOPE_IDENTITY()
LAST_IDENTITY()

Q117: Which isolation level provides the highest level of transaction isolation?
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Q118: Which isolation level allows dirty reads?
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Q119: Which SQL Server object is primarily used to encapsulate reusable calculations and return a value?
TRIGGER
FUNCTION
VIEW
INDEX

Q120: Which type of user-defined function returns a table instead of a single value?
Scalar Function
Aggregate Function
Table-Valued Function
Window Function