This set of SQL Server Questions and Answers for experienced people focuses on “Modifying Data – 2”.
1. The EXISTS keyword will be true if:
a) Any row in the subquery meets the condition only
b) All rows in the subquery fail the condition only
c) Both of these two conditions are met
d) Neither of these two conditions is met
Explanation: EXISTS keyword checks for existance of condition.
2. Which of the following is a aggregate function ?
Explanation: Sum is used to add set of values.
3. The command ________________ such tables are available only within the transaction executing the query, and are dropped when the transaction finishes.
a) Create table
b) Create temporary table
c) Create view
d) Create label view
Explanation: DDL statement is used alter structure of table.
WITH max_budget (VALUE) AS (SELECT MAX(budget) FROM department) SELECT budget FROM department, max_budget WHERE department.budget = MAX budget.value;
In the query given above which one of the following is a temporary relation ?
Explanation: With clause creates a temporary relation.
5. Aggregate functions can be used in the select list or the_______clause of a select statement or subquery. They cannot be used in a ______ clause.
a) Where, having
b) Having, where
c) Group by, having
d) Group by, where
Explanation: To include aggregate functions having clause must be included after where.
6. Which is duplication of computer operations and routine backups to combat any unforeseen problems:
Explanation: Recovery means to take the backup data while there is a crash.
7. The UNION SQL clause can be used with
a) SELECT clause only
b) DELETE and UPDATE clauses
c) UPDATE clause only
d) All of the mentioned
Explanation: The SQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements.
8. Find all the tuples having temperature greater than ‘Paris’.
SELECT * FROM weather WHERE temperature > (SELECT temperature FROM weather WHERE city = ‘Paris’
SELECT * FROM weather WHERE temperature > (SELECT * FROM weather WHERE city = ‘Paris’)
SELECT * FROM weather WHERE temperature > (SELECT city FROM weather WHERE city = ‘Paris’)
SELECT * FROM weather WHERE temperature > ‘Paris’ temperature
Explanation: Subquery—also referred to as an inner query or inner select—is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery.
9. Which of the following statement is true ?
a) DELETE does not free the space containing the table and TRUNCATE free the space containing the table.
b) Both DELETE and TRUNCATE free the space containing the table.
c) Both DELETE and TRUNCATE does not free the space containing the table.
d) DELETE free the space containing the table and TRUNCATE does not free the space containing the table.
Explanation: The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
10. How can you change “Hansen” into “Nilsen” in the “LastName” column in the Persons table?
UPDATE Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’
MODIFY Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
MODIFY Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’
UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
Explanation: In its simplest form, the syntax for the UPDATE statement when updating one table is:UPDATE table SET column1 = expression1,column2 = expression2,… WHERE conditions.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server for Experienced people, here is complete set of 1000+ Multiple Choice Questions and Answers.