Q. Why do you use a SELECT statement?
A. You use the SELECT statement to retrieve data from one or more tables.
Q. Name the wildcard characters that you can use when searching, and explain the differences between them.
A. The two wildcard characters are the percent (%) sign and the underscore (_). T-SQL uses the percent symbol as the wildcard for zero or more characters. The underscore (_) is the wildcard for a single character.
Q. Explain the DISTINCT keyword.
A. The DISTINCT keyword ensures uniqueness of values in the column or combination of columns included in the query result.
Q. Why would you use a Top Values query?
A. You use a Top Values query to limit the number of rows that appear in the output.
Q Why must you join tables together in a query?
A When you build a system based on normalized table structures, you must join the tables back together to see the data in a useable format.
Q Explain what a full join is.
A A full join combines the behavior of a left outer join and a right outer join. An example is where you show all customers whether or not they have orders, and all orders whether or not they are associated with a customer.
Q Explain the difference between the HAVING clause and the WHERE clause.
A SQL Server Express applies the HAVING clause after it summarizes the data, whereas it applies the WHERE clause before it summarizes the data.
Q Describe a subquery.
A SQL Server Express evaluates a subquery before it evaluates the main query.
Q Explain the difference between INSERT and SELECT INTO.
A INSERT adds data to an existing table, whereas SELECT INTO creates a new table containing the data that you are inserting.
Q Explain the difference between a DELETE statement and a trUNCATE statement.
A A DELETE statement enables you to selectively remove data from a table, whereas the TRUNCATE statement unconditionally removes all rows from a table.
Q Describe the difference between trUNCATE and DROP.
A trUNCATE removes all data from the table while retaining the table structure, whereas DROP removes the table from the database.
Q Explain what the STUFF function does.
A The STUFF function starts at a certain position and replaces a specified number of characters with other specified characters.
Q Explain what the DATEPART function does.
A The DATEPART function extracts part of a date. You designate the part of the date you want to extract, and the date from which you want to extract it.
Q Explain the ISNULL function.
A The ISNULL function returns information about whether the value in an expression is null. It receives the expression that you want to evaluate, and the value that you want to return if the expression is null. It returns the specified value.
Q. Name some advantages of views.
A. Views enable you to join data, aggregate data, customize data to the user's needs, hide underlying column names from users, limit the columns and rows that a user works with, and easily secure data.
Q. Explain why you would want to join data so that users can easily work with it.
A. A normalized database is not always easy for the user to work with. For example, only the CustomerID is stored in the Orders table. The Company Name is stored in the Customers table. To see both the Customer and Order information, the user must join the tables. Using a view, you can join the tables for the user, so that the user can work with the view as if it were a single table.
Q. Explain what it means to customize data to a user's needs.
A. Using a view, you can create a column that combines first, middle, and last name of an employee, or the city, state, and zip code from an address. This makes it much easier for the user to work with this data.
Q. How do views help you to secure data?
A. You can grant rights to logins and roles to the views that you create. It is not necessary to grant rights to the underlying tables. In this way you can give users access to just desired columns and rows. For example, you can give users rights to name and address data for the sales department in the employee table.