MTA 98-364 Test 3

20. What statement would you use to delete the Equipment table?

Answer: DROP TABLE Equipment Section Reference: Deleting a Table with DROP TABLE Explanation: Now, maybe you just want to delete the entire table? The removal of an entire table looks like this: DROP TABLE <table_name>

17. What statement would you use to pull all records in which the State is CA from the Users table?

Answer: SELECT * FROM Users WHERE State = 'CA' Section Reference: Using Queries to Select Data Explanation: Suppose that you want to provide your boss with a list of employees whose salary range is above $50K. You are interested only in retrieving those employees who fit your boss's criteria. Here's how you would do that in SQL: SELECT first_name, last_name, salary FROM employees WHERE salary >= 50,000

19. What statement would you use to list the First_Name and Last_Name from the Users table which Department is not IT?

Answer: SELECT First_Name, Last_Name FROM Users WHERE Department <> 'IT' or SELECT First_Name, Last_Name FROM Users WHERE NOT Department = 'IT' Section Reference: Using the NOT Clause Explanation: In some instances, it is simpler to write your query to search data in terms of what you don't want in your output. Transact-SQL provides you with a NOT keyword for use in such situations.

18. What statement would you use to pull First_Name and Last_Name from the Users table in which Gender is F and State is CA?

Answer: SELECT First_Name, Last_Name FROM Users WHERE Gender = 'F' AND State = 'CA' Section Reference: Combining Conditions Explanation: You can combine several conditions in one query statement to satisfy your requirements.

16. What statement would you use to pull First_Name, Last_Name, and ID_Number from the Users table?

Answer: SELECT First_Name, Last_Name, ID_Number FROM Users Section Reference: Using Queries to Select Data Explanation: The SQL command for retrieving any data from a database is SELECT. Much like any other SQL command, it will read similar to an English statement. Composing a SELECT statement is similar to filling in the blanks, such as the following: SELECT id, name //columns FROM sysobjects // tables WHERE type = "jones" //conditions you want to produce results from This is a simple statement, but it provides the basic understanding of what the SELECT statement does and always follows the same pattern each time you issue a SELECT statement to the database.

2. What wildcard character retrieves all records with the SELECT command? a) * b) % c) $ d) ?

a) * Section Reference: Using Queries to Select Data Explanation: If you want to choose all column names from within a table, use an asterisk (*) in place of where the column name would be identified.

11. What statement is used to delete one or more rows in a table? a) DELETE b) REMOVE c) TRUNCATE d) CHANGE

a) DELETE Section Reference: Deleting Data Explanation: The DELETE statement is used to perform the exact function it states: remove rows from a table or a view. You can use DELETE in several ways, depending on the amount of data you need to have removed. The simplest method is to identify and delete individual rows from within the database using the DELETE command syntax.

7. Which clause used with the SELECT command will return values that you want to exclude from the normal parameters? a) EXCEPT b) INTERSECT c) SEARCH d) EXCLUDE

a) EXCEPT Section Reference: Using the EXCEPT and INTERSECT Clauses Explanation: The EXCEPT clause returns any of those distinct values from the left query that are not also found on the right query.

4. What clause would you use with the SELECT command that would pull records based on a range? a) COMBO b) BETWEEN c) OR d) COMBINE

b) BETWEEN Section Reference: Using the BETWEEN Clause Explanation: To help resolve the awkwardness in using the AND clause in a query statement, replace it with a BETWEEN clause instead. This allows you to specify the range to be used in a "between x and y" query format for a much cleaner statement.

12. What statement is used to delete a table from a database? a) DELETE b) DROP c) REMOVE d) TRUNCATE

b) DROP Section Reference: Deleting a Table with DROP TABLE Explanation: Suppose that you want to delete an entire table because it's obsolete. The removal of an entire table looks like this: DROP TABLE <table_name>

9. What statement would you use to add data to a table? a) ADD b) INSERT c) JOIN d) PRESENT

b) INSERT Section Reference: Using Queries to Insert Data Explanation: If you are looking to insert small quantities of data—for instance, adding a few new rows into your database—you can accomplish this by using the graphical interface tool or by using the INSERT statement.

1. What command do you use to perform a query in SQL? a) USE b) SELECT c) QUERY d) CHOSE

b) SELECT Section Reference: Using Queries to Select Data Explanation: The SQL command for retrieving any data from a database is SELECT. Much like any other SQL command, it will read similar to an English statement.

15. What command would you use to delete a table while leaving the table structure in place for future use? a) DELETE TABLE b) TRUNCATE TABLE c) REMOVE TABLE d) STOP TABLE

b) TRUNCATE TABLE Section Reference: Truncating a Table with TRUNCATE TABLE Explanation: The TRUNCATE TABLE statement removes only the data from within the table but leaves the table structure in place for future use.

8. What clause is used to combine related data from multiple table sources? a) COMBINE b) AND c) JOIN d) PLUS

c) JOIN Section Reference: Using the JOIN Clause Explanation: The JOIN clause allows you to combine related data from multiple table sources. JOIN statements are similar to both EXCEPT and INTERSECT in that they return values from two separate table sources.

6. What would you use to combine columns from two different tables? a) AND b) BETWEEN c) JOIN d) COMBINE

c) JOIN Section Reference: Using the UNION Clause Explanation: The JOIN clause allows you to combine related data from multiple table sources. JOIN statements are similar in application that both EXCEPT and INTERSECT do in that they return values from two separate table sources. Based on this knowledge, we can move forward with learning what data can be extracted by using JOIN statements.

3. What would you use to combine two parameters in a query in SQL? a) PLUS b) BOTH c) COMBINE d) AND

d) AND Section Reference: Combining Conditions Explanation: If you need more from a query than simply one set of parameters, you can use an AND clause to combine several conditions in one query statement to satisfy your requirements. For example, if you want to find the employees who work in the Shipping department but also want to find out which of those employees are female, you would use this query: SELECT first_name, last_name FROM employees WHERE department = 'shipping' AND gender = 'F'

5. What keyword used with the SELECT statement is used to not output specified records? a) OR b) DISALLOW c) DISREGARD d) NOT

d) NOT Section Reference: Using the NOT Clause Explanation: In some instances, you might find it simpler to write your query to search data in terms of what you don't want in your output. Transact-SQL provides you with a NOT keyword for use in such situations.

10. What statement would you use to change data in a table? a) CHANGE b) MODIFY c) INSERT d) UPDATE

d) UPDATE Section Reference: Using the UPDATE Statement Explanation: The UPDATE clause allows you to modify the data stored in tables by using data attributes such as the following: UPDATE <table_name> SET <attribute> = <value> WHERE <conditions>

13. How can you prevent the accidental loss of an entire table? a) Use the DELETE TRANS command. b) Use the LOOPBACK command. c) Use the SAFEDELETE command. d) Use referential integrity.

d) Use referential integrity. Section Reference: Using Referential Integrity Explanation: A failover measure that you can take regarding database tables is using referential integrity practice methods. One of the most common mistakes of database manipulating is the accidental loss of entire tables. The best way to avoid this type of situation in the first place is to ensure that your database is using referential integrity. Referential integrity does not allow deletion of tables, unless they are actually at the end of the relationship.

14. What do you perform when you want to recall specific records from a database? a) recall b) virtual extraction c) reach d) query

d) query Section Reference: Using Queries to Select Data Explanation: You use the SELECT statement to retrieve or extract data from one table, retrieve or extract data by using joins, and combine results by using UNION and INTERSECT. When you retrieve or extract data from a table, you are performing a query.