Scribd Logo
Upload

Welcome to Scribd!

  • 270 views

    Database Programming With SQL 2-2: Limit Rows Selected Practice Activities

    Uploaded by

    christian tombilayuk

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Database Programming With SQL 2-2: Limit Rows Selected Practice Activities

    Uploaded by

    christian tombilayuk
    270 views3 pages

    Original Title

    DP_2_2_Practice

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    270 views3 pages

    Database Programming With SQL 2-2: Limit Rows Selected Practice Activities

    Uploaded by

    christian tombilayuk

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 3

    Database Programming with SQL 2-

    2: Limit Rows Selected Practice


    Activities
    Objectives
     Apply SQL syntax to restrict the rows returned from a query
     Demonstrate application of the WHERE clause syntax
     Explain why it is important, from a business perspective, to be able to easily limit data retrieved from a
    table
     Construct and produce output using a SQL query containing character strings and date values

    Vocabulary
    Identify the vocabulary word for each definition below.

    WHERE Restricts the rows returned by a select statement

    Comparison Operators Compares one expression to another value or expression

    Try It / Solve It

    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, last_name, 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, 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 = 1997;
    2
    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’;

    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

    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’;
    2
    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; / year NOT IN(2000)

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

    SELECT *
    FROM f_staffs
    WHERE birthdate < ‘1-January-1980’;

    Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

    You might also like