Reference no: EM131765569

Terms

Define the following terms:

1. Implicit syntax vs. Explicit syntax
2. Table alias
3. Qualified column name
4. Inner JOIN vs Outer JOIN vs FULL JOIN
5. Self-join
6. UNION
7. Aggregate function (with two examples)
8. Summary query
9. INTERSECT vs EXCEPT
10. HAVING

Exercise 1 Determine how many products you can find in the Production.Product table.

504
(1 row(s) affected)

Exercise 2 Write a query that retrieves the number of products in the Production.Product table that are included in a subcategory.

Hint: the rows that have NULL in column ProductSubcategorylD are considered not a part of any subcategory.
HasSubCategorylD
295
(1 row(s) affected)
Notice that the result has a column name. Also take a look at the message you receive from SQL Server.

Exercise 3 Determine how many Products reside in each SubCategory.

The answer to this is retrievable if you write a query that uses the COUNT aggregate function combined with a GROUP BY clause.

The column ProductSubcategorylD is a candidate for building groups of rows when querying the Production.Product table. Your result set should look something like the result below:

Notice the column alias for the second column.

ProductSubcategorylD   Counted Products
NULL 209
1 32
2 43
(38 row(s) affected)

Exercise 4 Write a query that lists the country and province names stored in AdventureWorks2014 sample database.

In the Person schema, you will find the CountryRegion and StateProvince tables. Join them and produce a result set similar to the following. Notice that there is no particular sort order in the result set.
Country  Province
Canada  Alberta
United States  Alaska
United States  Alabama
(181 row(s) affected)

Exercise 5 Continue to work with the previous query and add a filter to list only the countries Germany and Canada.

Also, notice the sort order and column headings of the result set. Your result set should look similar to the following:

Country  Province
Canada  Alberta
Canada  British Columbia

Germany Brandenburg
Germany  Hamburg
(20 row(s) affected

Exercise 6 Write a query that retrieves the average commission percent for the SalesPerson table.

Exercise 7 Write a query that retrieves a count of all Male Employees. Name the column.
Hint: Use a HumanResources table.

Exercise 8 Write a query that retrieves the highest List Price and lowest List Price of any Product in the Production.Product table.

Column names should be Max List Price and Min List Price. Min List Price cannot be 0.00.
Exercise 9 Identify the possible causes of error(s) and rectify. Consider the following SQL query:
SELECT ProductlD, LineTotal AS 'Total'
FROM Sales.SalesOrderDetail
Group By Cube(LineTotal)
Once executed, the preceding query generates errors. Identify the possible causes of such error(s) and rectify.
ProductID  Total
707  157772.394392
708  160869.517836
718  395182.699300
(267 row(s) affected