Day 2

Quiz

1:Can you use wildcards with IN, as in this example?
select ProductName, UnitPrice
from Products
where ProductName in ('%tofu%', '%cereal%', '%grain%')

A1: No. The server will treat the wildcards (% characters) as literal characters and will try to find products whose name is literally '%tofu%' or '%cereal%'. To combine multiple wildcard searches, use OR to connect LIKE clauses:
select ProductName, UnitPrice
from Products
where ProductName like '%tofu%'
    or ProductName like '%cereal%'
    or ProductName like '%grain%'

2:When do you need to group conditions using parentheses?
A2: Parentheses are required when your WHERE clause consists of more than two conditions and there is a combination of OR and AND conjunctions.
3:In this query, is the sort order on the EmployeeID ascending or descending?
select EmployeeID, OrderDate, OrderID
from Orders
order by EmployeeID, OrderDate desc

A3: The sort order is ascending. The scope of the ASC and DESC keywords is only over a single column or expression, not over the entire list of columns.
4:In this query and result set featuring DISTINCT, why are there multiple rows with the value 'Sales Representative'in the title column? Isn't DISTINCT supposed to eliminate those duplicates?
select distinct Title, LastName
from Employees

A4: Result:
Title                          LastName
------------------------------ --------------------
Inside Sales Coordinator       Callahan
Sales Manager                  Buchanan
Sales Representative           Davolio
Sales Representative           Dodsworth
Sales Representative           King
Sales Representative           Leverling
Sales Representative           Peacock
Sales Representative           Suyama
Vice President, Sales          Fuller

Remember that DISTINCT removes duplicate rows, not duplicate values. Each combination of title and last name is unique.

Exercises

1:List all customers in Mexico. How many are there?
A1: There are 5 customers. 
select CustomerID, CompanyName, Country
  from Customers
 where Country = 'Mexico'

2:Find Andrew Fuller's home phone number.
A2: His phone number is (206) 555-9482. 
select FirstName, LastName, HomePhone
  from Employees
 where FirstName = 'Andrew'
   and LastName = 'Fuller'

3:What product costs the same as Chang?
A3: (For now, this problem requires two steps. Answer: Inlagd Sill.) 
select UnitPrice
from Products
where ProductName = 'Chang'

(The intermediate result is $19.00.)

select ProductName
from Products
where UnitPrice = 19

4:Produce a list of different countries shipped to in May of 1998. 
select distinct ShipCountry
  from orders
 where orderdate >= '5/1/98'
   and orderdate < '5/31/98'

A4: Result: 
ShipCountry
---------------
Austria
Brazil
Denmark
France
Germany
Mexico
Switzerland
USA
Venezuela

5:Find the youngest employee. When was he (she) born?
A5: Anne Dodsworth was born in 1966. 
select LastName, FirstName, BirthDate
  from Employees
 order by BirthDate Desc

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset