SQL Server Questions and Answers – Modifying Data – 2

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
View Answer

Answer: a
Explanation: EXISTS keyword checks for existence of condition.

2. Which of the following is an aggregate function?
a) Average
b) Sum
c) With
d) Minimum
View Answer

Answer: b
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
View Answer

Answer: b
Explanation: DDL statement is used alter structure of table.
advertisement
advertisement

4. In the query given above which one of the following is a temporary relation?

WITH max_budget (VALUE) AS
(SELECT MAX(budget)
FROM department)
SELECT budget
FROM department, max_budget
WHERE department.budget = MAX budget.value;

a) Budget
b) Department
c) Value
d) Max_budget
View Answer

Answer: d
Explanation: With clause creates a temporary relation.
Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!

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
View Answer

Answer: b
Explanation: To include aggregate functions having clause must be included after where.
advertisement

6. Which is the duplication of computer operations and routine backups to combat any unforeseen problems?
a) Concurrency
b) Deadlock
c) Backup
d) Recovery
View Answer

Answer: d
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
View Answer

Answer: a
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.
advertisement

8. Find all the tuples having temperature greater than ‘Paris’.
a)

SELECT * FROM weather
WHERE temperature > (SELECT temperature FROM weather WHERE city = ‘Paris’

b)

SELECT * FROM weather 
WHERE temperature > (SELECT * FROM weather WHERE city = ‘Paris’)

c)

SELECT * FROM weather 
WHERE temperature > (SELECT city FROM weather WHERE city = ‘Paris’)

d)

SELECT * FROM weather 
WHERE temperature > ‘Paris’ temperature
View Answer
Answer: a
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
View Answer

Answer: a
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?
a)

UPDATE Persons 
SET LastName=’Hansen’ INTO LastName=’Nilsen’

b)

MODIFY Persons 
SET LastName=’Nilsen’ WHERE LastName=’Hansen’

c)

MODIFY Persons 
SET LastName=’Hansen’ INTO LastName=’Nilsen’

d)

UPDATE Persons 
SET LastName=’Nilsen’ WHERE LastName=’Hansen’
View Answer
Answer: d
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.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.