04 Limit Rows Selected

• ·         Restricts the rows returned by a select statement –Where Clause
• ·         Compares one expression to another value or expression – Comparison Operator

1. Using the Global Fast Foods database, retrieve the customer’s first name, last name, and address for the customer who uses ID 456.

SELECT first_name as "first name", last_name as "last name", address || ', ' || city || ', ' || state || '-' || zip || CHR(10) || '(Phone-' || phone_number || ')'  as "address"

FROM f_customers

WHERE id = 456; 

2. Show the name, start date, and end date for Global Fast Foods' promotional item “ballpen and highlighter” giveaway.

SELECT name, start_date as "start date", end_date as "end date"

FROM f_promotional_menus

WHERE give_away = 'ballpen and highlighter';

3. Create a SQL statement that produces the following output:

Oldest

The 1997 recording in our database is The Celebrants Live in Concert

SELECT 'The ' || year ||' recording in our database is '|| title  as "Oldest"

FROM d_cds

WHERE year = (SELECT MIN(year) FROM d_cds);

4. The following query was supposed to return the CD title “Carpe Diem" but no rows were returned. Correct the mistake in the statement and show the output.

SELECT produce, title

FROM d_cds

WHERE title = 'carpe diem' ;

SELECT producer, title

FROM d_cds

WHERE title = 'Carpe Diem' ;

Or in case only title has to be returned:

SELECT   title

FROM d_cds

WHERE title = 'Carpe Diem' ;

5. The manager of DJs on Demand would like a report of all the CD titles and years of CDs that were produced before 2000.

SELECT title, year

FROM d_cds

WHERE year < 2000;

6. Which values will be selected in the following query?

SELECT salary

FROM employees

WHERE salary < = 5000;

a. 5000

b. 0 - 4999

c. 2500

d. 5

All of the above

For the next three questions, use the following table information:

TABLE NAME: students

COLUMNS:

studentno NUMBER(6)

fname VARCHAR2(12)

lname VARCHAR(20)

sex CHAR(1)

major VARCHAR2(24)

7. Write a SQL statement that will display the student number (studentno), first name (fname), and last name (lname) for all students who are female (F) in the table named students.

SELECT studentno as "student number", fname as "first name", lname as "last name"

FROM students

WHERE sex = 'F';

8. Write a SQL statement that will display the student number (studentno) of any student who has a PE major in the table named students. Title the studentno column Student  Number.

SELECT studentno as "Student Number"

FROM students

WHERE major = 'PE';

9. Write a SQL statement that lists all information about all male students in the table named students.

SELECT *

FROM students

WHERE sex = 'M';

10. Write a SQL statement that will list the titles and years of all the DJs on Demand CDs that were not produced in 2000.

SELECT title, year

FROM d_cds

WHERE year != 2000;

11. Write a SQL statement that lists the Global Fast Foods employees who were born before 1980.

SELECT *

FROM f_staffs

WHERE birthdate  <  '01-JAN-1980';