Solutions Chapter 2: SQL: The Basics Test Your Thinking

1. Invoke an editor from SQL*Plus; create a file called first.sql containing an SQL statement that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions start with 'Intro', sorted by their prerequisites.

   Solution:

   /*
   -----------------------------------------------------
   File name:   first.sql
   Purpose:     Display data from course table
   Created by:  Joe Morris on December 7, 2002
   -----------------------------------------------------
   */
   
   SELECT *
     FROM course
    WHERE cost = 1195
      AND description like 'Intro%'
    ORDER BY prerequisite;
   

2. Create another file called second.sql that retrieves data from the STUDENT table for students whose last names begin with 'A','B', or 'C', and who work for 'Competrol Real Estate', sorted by their last names.

   Solution:

   /*
   -----------------------------------------------------
   File name:   second.sql
   Purpose:     Display data from course table
   Created by:  Joe Morris on December 7, 2002
   -----------------------------------------------------
   */
   SELECT *
     FROM student
    WHERE (last_name like 'A%'
           OR last_name like 'B%'
           OR last_name like 'C%')
      AND employer = 'Competrol Real Estate';
   

3. Create yet another file called third.sql that retrieves all the descriptions from the GRADE_TYPE table, for rows that were modified by the user MCAFFREY.

   Solution:

   /*
   -----------------------------------------------------
   File name:   third.sql
   Purpose:     Display data from course table
   Created by:  Joe Morris on December 7, 2002
   Modified by: ---
   -----------------------------------------------------
   */
   SELECT description
     FROM grade_type
    WHERE modified_by = 'MCAFFREY';
   

4. Execute each of the files, in the order they were created.

   Solution: Make sure you specify the correct location of the file when you execute it at the SQL*Plus prompt. For example, if you created the files in a directory called c:\guest, you need to execute the files at the SQL*Plus prompt as follows:

   @c:\guest\@first
   @c:\guest\@second
   @c:\guest\@third
   

   Note, instead of using the @ symbol you can also use the START command. Refer to Appendix C, "SQL*Plus Command Reference," for more SQL*Plus commands.

Select Another Chapter

1. SQL and Data
2. SQL: The Basics
3. Character and Number Functions
4. Date, Conversion, and Miscellaneous Functions
5. Aggregate Functions, GROUP BY, and HAVING
6. Equijoins
7. Subqueries
8. Set Operators
9. Complex Joins
10. Insert, Update, and Delete
11. Create, Alter, and Drop Tables
12. Views, Indexes, and Sequences
13. The Data Dictionary and Dynamic SQL Scripts
14. Security
15. Advanced SQL Queries
16. SQL Optimization