Getting Started with SQL*Plus

Getting Started with SQL*Plus

IST 318Database AdministrationFall 2005

Lab 1

Getting Started With SQL*Plus

Objective

On the one hand, this lab is used as a review to let you pick up what we learned about SQL and Oracle from IST 220. On the other hand, it introduces some techniques to use SQL*Plus more efficiently.

General Instructions for Labs

Follow the instructions below to finish this lab. Many of the requirements given here also apply to subsequent lab assignments.

Initialize a SQL*Plus session using the user ID and password provided by the instructor.

Write SQL*Plus commands and SQL SELECT statements to accomplish the following tasks. You may write your code with your favorite editor (e.g., Notepad), and then copy and paste into the SQL*Plus window to execute.

Cut and paste the results right after your code. Print out the file (with your code and results) after you are done with this lab.

Part I – SQL Basics

  1. Show the structure of the BOOKS table. Create a list of books stored in the table and the category in which each book belongs. However, reverse the sequence of the columns so the category of each book is listed first.
  1. From the ORDERS table, list the customer number for each customer who has placed an order with the book store. List each customer number only once. (Hint: use the DISTINCT or UNIQUE keyword.)
  1. Create a list of authors that displays the last name followed by the first name for each author. The last names and the first names should be separated by a comma and a blank space.

Give the SQL statements that determine the following. Use appropriate ways to restrict and sort rows in the results. Assume all columns are to be retrieved in the order they are stored in the original tables, except otherwise specified.

  1. Which books are not in the Fitness category? (Copy your SQL statement. Do NOT copy the system output to your report yet. To be revisited in Part II.)
  1. Which customers live in either Georgia or New Jersey? Put the results in ascending order by last name.(Copy your SQL statement. Do NOT copy the system output to your report yet. To be revisited in Part II.)
  1. List all authors whose last name contains the letter pattern “IN”. Put the results in order of last name, then first name.
  1. List first and last names of all customers who were referred to the bookstore by another customer.
  1. Use a search pattern to find any book where the title has an “A” for the second letter in the title, and an “N” for the fourth letter. List the book title and publication date.
  1. List the title of any computer book that was published in 2001. Use two different ways to restrict the result: (a) use a search pattern on the year; (b) use the BETWEEN keyword.

Part II – Using SQL*Plus

Formatting Output– Query #1 Revisited

Execute the query SELECT * FROM books; you will see that the readability is poor. Follow the steps listed below to format your result. You may copy the final result to your report for submission.

-Set SQL*Plus environment, using
set pagesize 30
set linesize 100
set underline '='
Then type / to repeat the last SQL statement that was executed.

-Format columns, using
column cost format '$999.99'
column retail format 999.99 heading 'Retail|Price'
column pubdate heading 'Publish|Date'

-Add report titles and set variables
ttitle center 'Non-Fitness Titles' skip -
right 'Page: ' format 9 SQL.PNO skip 2
btitle center format a15 SQL.USER

-Add the criterion you used in Query 6. Execute the query and copy the final results.

Executing from a Script

-Use the following command to clear all columns that have been formatted.
clear column

-Use the following command to redirect system output to a text file. You may change the filename as appropriate.
spool c:\query1.txt

-Execute the follow script, which summarizes the changes in the previous section.You may need to change the file name and path depending on where did you saved it.
start query1.sql

-Use the following command to clear all columns that have been formatted.
spool off

Your result should now be saved in the query1.txt file.

Editing SQL Statements– Query #7 Revisited

Assume that you have used the following SQL statement that included a few typos. You don’t have to retype the whole thing. Just use the change command to edit the statement and use / to start it again.

Type the

SQL> select * from customers order by last_name, first_name;

select * from customer order by last_name, first_name

*

ERROR at line 1:

ORA-00904: "FIRST_NAME": invalid identifier

SQL> list

1* select * from customers order by last_name, first_name

SQL> change $last_name$lastname

1* select * from customers order by lastname, first_name

SQL> ch $first_name$firstname

1* select * from customers order by lastname, firstname

SQL> /

Deliverables and Deadline

Deliverables include:

-SQL statements your used for the 9 queries, each with query number labeled and corresponding results attached

-Spool file for the last two sections in Part II

  • Executing from a Script, and
  • Editing SQL Statements

Please hand in a hardcopy of all the deliverables by beginning of class on Wednesday, 08/31.

1

Top View