Chapter 9 Application

Question 1

Sort by the car column.

Answer 1

Select * from NASCAR2 order by car;

Question 2

Sort by the car column explicitly cast as an integer.

Answer 2

Select * from NASCAR2 order by cast(car as signed);

Question 3

Sort by the car column implicitly cast as an integer.

Answer 3

Select * from NASCAR2 order by car + 0;

Question 4

Sort by the car column after it has been padded with leading zeros.

Answer 4

Select LPAD(car, 2, ‘0’) as car, driver from NASCAR2 order by car;

Question 5

Apply the LOCATE function to find the blank character in the driver string.

Answer 5

Select driver, LOCATE(‘ ‘, driver) as space from NASCAR2;

Question 6

Use SUBSTRING() in combination with LOCATE() to parse driver into first and last names.

Answer 6

Select driver, substring(driver, 1, LOCATE(‘ ‘,driver) - 1) as FirstName, Substring(driver, Locate(‘ ‘, driver)+1) as LastName from NASCAR2;

Question 7

Invoke SUBSTRING_INDEX() to parse driver into first and last names.

Answer 7

Select SUBSTRING_INDEX(driver,’ ‘, 1) as FirstName, SUBSTRING_INDEX(driver,’ ‘, -1) as LastName from NASCAR2;

Question 8

Prepare a query that projects car, amount, and integer values for amount returned by ROUND(), TRUNCATE(), FLOOR(), and CEILING(). Show the result set for cars 2 and 8.

Answer 8

Select Round(amount,0) as Rounded_Amount, Truncate(amount,0) as Truncated_Amount, Floor(amount) as RoundedDown_Amount, Ceiling(amount) as RoundedUp_Amount from NASCAR2 where car=08 or car=02;

Question 9

Query NASCAR to projects all columns for those rows with amount values BETWEEN four and five thousand (dollars).

Answer 9

select * from NASCAR2 where amount BETWEEN 4000 and 5000;

Question 10

Code a query that invokes NOW(), SYSDATE(), and CURDATE(). Show the result set given that this query is issued during the exam 2 period.

Answer 10

select now(), sysdate(), curdate();

Question 11

Given a date type named lastRaceDate is added to NASCAR, prepare a query that projects driver and the three numeric values for month, day and year, and separated by forward slashes (e.g. 02/28/2015). Use CONCAT(), MONTH(), DAYOFWEEK(), and YEAR() in the SELECT clause.

Answer 11

Select concat(driver,’ ‘,lastRaceDate), Month(lastRaceDate) as Month, DayofWeek(lastRaceDate) as Day, Year(lastRaceDate) as Year from NASCAR2;