98-364 Database Admin. Fund. Chapter 3 (2)

DELETE 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.

What statement is used to delete one or more rows in a table?

INSERT 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.

What statement would you use to add data to a table?

UPDATE 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>

What statement would you use to change data in a table?

Answer: SELECT First_Name, Last_Name, ID_Number FROM Users 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

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

Answer: SELECT * FROM Users WHERE State = 'CA' 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

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

DROP 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>

What statement is used to delete a table from a database?

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

What statement would you use to delete the Equipment table?

Answer: SELECT First_Name, Last_Name FROM Users WHERE NOT Department = 'IT' 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.

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 Gender = 'F' AND State = 'CA' Explanation: You can combine several conditions in one query statement to satisfy your requirements.

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?

* (asterisk ) 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.

What wildcard character retrieves all records with the SELECT command?

JOIN 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.

What would you use to combine columns from two different tables?

AND 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'

What would you use to combine two parameters in a query in SQL?

EXCEPT Explanation: The EXCEPT clause returns any of those distinct values from the left query that are not also found on the right query.

Which clause used with the SELECT command will return values that you want to exclude from the normal parameters?

SELECT

The SQL command for retrieving any data from a database is _____________.

Use 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.

How can you prevent the accidental loss of an entire table?

JOIN 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.

What clause is used to combine related data from multiple table sources?

BETWEEN 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.

What clause would you use with the SELECT command that would pull records based on a range?

SELECT 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.

What command do you use to perform a query in SQL?

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.

What command would you use to delete a table while leaving the table structure in place for future use?

query 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.

What do you perform when you want to recall specific records from a database?

NOT 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.

What keyword used with the SELECT statement is used to not output specified records?