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