07 Sorting Rows

• ·         Orders the rows in ascending order (the default order); A-Z  ASC [Ascending]
• ·         Orders the rows in descending order: Z-A  DESC  [ Descending]
• ·         To arrange according to class, kind, or size-Sorting 

1. In the example below, assign the employee_id column the alias of “Number.” Complete the SQL statement to order the result set by the column alias.

SELECT employee_id, first_name, last_name

FROM employees;

SELECT employee_id as "Number", first_name, last_name

FROM employees

ORDER BY "Number";

2. Create a query that will return all the DJs on Demand CD titles ordered by year with titles in alphabetical order by year.

SELECT title

FROM d_cds

ORDER BY year, title;

3. Order the DJs on Demand songs by descending title. Use the alias “Our Collection” for the song title.

SELECT title as "Our Collection"

FROM d_cds

ORDER BY  title DESC;

4. Write a SQL statement using the ORDER BY clause that could retrieve the information needed. Do not run the query.

Create a list of students who are in their first year of school. Include the first name, last name, student ID number, and parking place number. Sort the results alphabetically by student last name and then by first name. If more than one student has the same last name, sort each first name in Z to A order. All other results should be in alphabetical order (A to Z).

SELECT first_name,  last_name,  student_id ,  parking_number

FROM students

WHERE year = 1

ORDER BY  last_name ,  first_name DESC;

5. Write a SQL statement using the employees table and the ORDER BY clause that could retrieve the information in the following table. Return only those employees with employee_id<125.

DEPARTMENT_ID	LAST_NAME	MANAGER_ID
90	Kochhar	100
90	King	-
90	De Haan	100
60	Lorentz	103
60	Hunold	102
60	Ernst	103
50	Mourgos	100

SELECT department_id , last_name, manager_id

FROM employees

WHERE employee_id<125

ORDER BY  department_id DESC , last_name DESC;

1. Limiting values with the WHERE clause is an example of:

a. Projection

b. Ordering

c. Joining

d. Grouping

e. Selection

e

2. You want to sort your CD collection by title, and then by artist. This can be accomplished using:

a. WHERE

b. SELECT

c. ORDER BY

d. DISTINCT

c

3. Which of the following are SQL keywords?

a. SELECT

b. ALIAS

c. COLUMN – it’s a reserved word

d. FROM

a and d

4. Which of the following are true?

a. Multiplication and division take priority over addition.

b. Operators of the same priority are evaluated from left to right.

c. Parentheses can be used to override the rules of precedence.

d. None of the above are true.

a, b, c

5. The following query was written:

SELECT DISTINCT last_name

FROM students

a. To select all the outstanding students

b. To choose last names that are duplicates

c. To select last names without duplicates

d. To select all last names

c

6. The following string was created using which SELECT clause?

Abby Rogers is an order taker for Global Fast Foods

a. SELECT first_name ||' ' ||last_name ||' is an ' staff_type ' for Global Fast Foods'

b. SELECT Abby Rogers is an ||staff_type||' for Global Fast Foods'

c. SELECT first_name,last_name '||staff_type||' for Global Fast Foods'

d. SELECT first_name ||' ' ||last_name ||' is an '||staff_type||' for Global Fast Foods'

d

7. Which of the following SELECT clauses will return uppercase column headings?

a. SELECT id, last_name, address, city, state, zip, phone_number;

b. SELECT ID, LAST_NAME, ADDRESS, CITY, STATE, ZIP, PHONE_NUMBER;

c. SELECT Id, Last_name, Address, City, State, Zip, Phone_number;

d. SELECT id AS ID, last_name AS NAME, address AS ADDRESS, city AS CITY, state AS STATE, zip AS ZIP, phone_number AS PHONE_NUMBER;

ALL

8. Which SELECT clause will return the last names in alphabetical order?

a. SELECT last_name AS ALPHABETICAL  - OK

b. SELECT last_name AS ORDER BY  - space is alias order by so it required “”

c. SELECT last_name -OK

d. SELECT ASC last_name – no column preset with name asc, so it fails

a and c

9.  Which SELECT clause will return a column heading for employee_id called “New Employ-ees”?

a. SELECT last_name AS "New Employees"

b. SELECT employee_id AS New Employees

c. SELECT employee AS "New Employees"

d. SELECT employee_id AS "New Employees"

d

10. Examine the following query:

SELECT last_name, job_id, salary

FROM employees

WHERE job_id = 'SA_REP' OR job_id = 'AD_PRES' AND salary >15000;

Which results could not have been returned from this query?

a. Joe Everyone, sales representative, salary 15000

b. Jane Hendricks, sales manager, salary 15500

c. Arnie Smithers, administration president, 20000

d. Jordan Lim, sales representative, salary 14000

WHERE job_id = 'SA_REP' OR (job_id = 'AD_PRES' AND salary >15000);

I don’t see job_id as 'SA_REP' / 'AD_PRES' in any record above

none

But if I consider full forms as obvious, and ignore small errors in representation of choices:

  Possible options: a, c and d

11. Finish this query so it returns all employees whose last names start with “St”.

SELECT last_name

FROM employees

WHERE last_name LIKE 'St%';

12. What salary values will not be returned from this query?

SELECT last_name, first_name, salary

FROM employees

WHERE salary BETWEEN 1900 AND 2100;

Above 2100 , below 1900

13. Correct each WHERE clause:

a. WHERE department_id NOT IN 101,102,103;

b. WHERE last_name = King

c. WHERE start date LIKE "05-May-1998"

d. WHERE salary IS BETWEEN 5000 AND 7000

e. WHERE id =! 10

a. ERE department_id NOT IN (101,102,103)

b. WHERE last_name = 'King'

c. WHERE start_date   =   '05-May-1998'

d. remove IS

e. WHERE id  != 10

14. SELECT prefix

FROM phone

WHERE prefix BETWEEN 360 AND 425

OR prefix IN (206,253,625)

AND prefix BETWEEN 315 AND 620;

Which of the following values could be returned?

625, 902, 410, 499

Read as: [BETWEEN, IN >AND>OR]

WHERE prefix (BETWEEN 360 AND 425)

OR (prefix IN (206,253,625)

AND (prefix BETWEEN 315 AND 620));

·         625 cancelled by BETWEEN

·         902 out of the world

·         410 supported by BETWEEN OR

·         499 supported by BETWEEN but negated by IN

410