1

Selecting Rows

Schedule:

Timing

Topic

40 minutes 25 minutes 65 minutes

Lecture Practice Total

Class Management Note:

Files required for lesson are: Demonstration: l1prec1.sql, l1prec2.sql, l1alias.sql, l1null1.sql Practice: None

12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Objectives
In order to extract data from the database you need to use the Structured Query Language (SQL) SELECT command. You may need to restrict the columns that are displayed. This lesson explains all of the commands you will use to perform these actions. You will want to create SELECT statements that can be used time and time again. In this lesson you will also see how to save your statements for later use. At the end of this lesson, you should be able to
D D D D D D

Write a SELECT statement to query the database. Perform arithmetic calculations using SQL arithmetic operators. Handle null values. Specify alternative column headings using aliases. Concatenate columns. Edit SQL statements in the SQL*Plus buffer and create command files.

Selecting Rows

13

14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Basic Query Block

A SELECT statement retrieves information from the database, implementing all algebraic operators. Syntax SELECT FROM [DISTINCT] {*,column [alias],....} table; is a list of at least one column. suppresses duplicates. selects all columns. selects the named column. gives selected columns a different heading. specifies the table containing the columns.

where: SELECT DISTINCT * column alias FROM table

Selecting Rows

15

16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Basic Query Block Writing SQL Commands

continued

By following these simple rules and guidelines, you will be able to construct valid statements that are easy both to read and to edit. D SQL commands may be entered on one or many lines. D Clauses are usually placed on separate lines for readability and ease of editing.
D D D

Tabs and indents can be used to make code more readable. Command words cannot be split across lines or abbreviated. Keywords and commands typically are entered in uppercase; all other words, such as table names and columns, are entered in lowercase. SQL commands are not case sensitive, unless indicated. An SQL command is entered at the SQL prompt, and subsequent lines are numbered. This is called the SQL buffer. Only one statement can be current at any time within the buffer, and the statement can be executed in a number of ways: D Place a semicolon (;) at the end of last clause.
D D D

D D

Place a semicolon or slash on the last line in the buffer. Place a slash at the SQL prompt. Issue a SQL*Plus RUN command at the SQL prompt.

For more information, see Oracle Applications: Coding Standards, Release 10G.

Selecting Rows

17

Simplest SELECT statement contains the following two clauses:

18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Basic Query Block

In its simplest form, a SELECT statement must include the following: D A SELECT clause, which specifies the columns to be displayed.
D

continued

A FROM clause, which specifies the table containing the columns listed in the SELECT clause.

Selecting All Columns and Rows The asterisk (*) selects all columns from the table. Example List all columns and all rows from the S_DEPT table. SQL> SELECT 2 FROM * s_dept;

ID ------10 31 32 33 34 35 41 42 43 44 45 50

NAME REGION_ID --------------- --------Finance 1 Sales 1 Sales 2 Sales 3 Sales 4 Sales 5 Operations 1 Operations 2 Operations 3 Operations 4 Operations 5 Administration 1

12 rows selected.
Class Management Note:

Let the students know that the details of all the tables are given in Appendix B.

Selecting Rows

19

1 10

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Basic Query Block

Selecting Specific Columns

continued

You restrict the query to display only certain columns by specifying the column names, separated by commas, in the SELECT clause. Example Display all department numbers, employee last names, and manager numbers in the S_EMP table. SQL> SELECT 2 FROM dept_id, last_name, manager_id s_emp;

DEPT_ID ------50 41 31 10 50 41 42 43 44 ...

LAST_NAME MANAGER_ID ------------ ---------Velasquez Ngao 1 Nagayama 1 Quick-To-See 1 Ropeburn 1 Urguhart 2 Menchu 2 Biri 2 Catchpole 2

25 rows selected.

Specify the columns you want to see, in the order in which you want to see them, in the SELECT clause. Do not forget to use the comma as a column name separator. Column Heading Defaults Character and date column headings and data are left-justified within a column and numbers are right-justified. Character and date column headings may be truncated, but number headings may not be truncated. The column labels appear in uppercase by default. You can override the column label display with an alias.

Selecting Rows

1 11

1 12

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Arithmetic Expressions
You may need to modify the way data is displayed, perform calculations, or look at what-if scenarios. This is possible using arithmetic expressions. An arithmetic expression may contain column names, constant numeric values, and the arithmetic operators. Arithmetic Operators These are the arithmetic operators available in SQL. You may use arithmetic operators in any clause of a SQL statement except the FROM clause. Operators + * / Description Add Subtract Multiply Divide

Selecting Rows

1 13

1 14

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Arithmetic Expressions

continued

Example Use the multiplication operator to display annual salary figures and their commission percentage for all employees. SQL> SELECT 2 FROM last_name, salary * 12, commission_pct s_emp;

LAST_NAME SALARY*12 COMMISSION_PCT ------------ ---------- -------------... Havel 15684 Magee 16800 10 Giljum 17880 12.5 Sedeghi 18180 10 Nguyen 18300 15 Dumas 17400 17.5 Maduro 16800 ... Notice that the resultant calculated column SALARY*12 is not a new column in the originating table, but is for display only.
Class Management Note:

Let the students know that SQL*Plus ignores blank spaces before and after the arithmetic operator. Formatting column values will be covered in later lessons.

Selecting Rows

1 15

1 16

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Arithmetic Expressions
Operator Precedence

continued

If an arithmetic expression contains more than one operator, multiplication and division are evaluated first. If operators within an expression are of the same priority, then evaluation is from left to right. Example Display the last name, salary, and annual compensation of employees. Calculate the annual compensation as 12 multiplied by the monthly salary, plus a one-time bonus of $100. SQL> SELECT 2 FROM last_name, salary, 12 * salary + 100 s_emp;

LAST_NAME SALARY 12*SALARY+100 ------------ ---------- ------------Velasquez 2500 30100 Ngao 1450 17500 Nagayama 1400 16900 Quick-To-See 1450 17500 Ropeburn 1550 18700 Urguhart 1200 14500 ... Note: Use parentheses to reinforce the standard order of precedence and to improve clarity. For example, the expression above can be written as (12 * salary) + 100 with no change in the result.

Selecting Rows

1 17

Class Management Note:

DEMO: l1prec1.sql, l1prec2.sql PURPOSE: l1prec1.sql demonstrates the previous pages example with no parentheses. Note the first couple rows of totals. Then, execute demo file l1prec2.sql, which demonstrates using the parentheses to override the rules of precedence. Note that the totals are larger.

1 18

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Arithmetic Expressions

continued

Override the rules of precedence with parentheses to specify the order in which operators are executed. Example Display the last name, salary, and annual compensation of employees. Calculate the annual compensation as monthly salary plus a monthly bonus of $100, multiplied by 12. SQL> SELECT 2 FROM last_name, salary, 12 * (salary + 100) s_emp;

LAST_NAME SALARY 12*(SALARY+100) ------------ ---------- --------------Velasquez 2500 31200 Ngao 1450 18600 Nagayama 1400 18000 Quick-To-See 1450 18600 Ropeburn 1550 19800 Urguhart 1200 15600 ...

Selecting Rows

1 19

Class Management Note:

DEMO: l1alias.sql. PURPOSE: The column headings for each of the examples on the next page. Demonstrate how to modify the file to demonstrate the other example.

1 20

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Column Aliases
When displaying the result of a query, SQL*Plus normally uses the selected columns name as the heading. In many cases, that heading may be difficult to understand or even meaningless. You can change a columns heading by using a column alias. Specify the alias after the column in the SELECT list using a space as a separator. By default, alias headings will be forced to uppercase and cannot contain blank spaces, unless the alias is enclosed in double quotation marks ( ). Example Display the last name, salary, and annual compensation of employees. Calculate the annual compensation as monthly salary plus a monthly bonus of $100, multiplied by 12. Name the column ANNUAL_SALARY. SQL> SELECT 2 3 FROM last_name, salary, 12 * (salary + 100) AS ANNUAL_SALARY s_emp;

Note: You can include the AS keyword before the alias name to comply with ANSI SQL 92 standards. Column Aliases with Double Quotation Marks If the alias contains spaces, special characters (such as # or $), or is case-sensitive, enclose the alias in double quotation marks ( ). SQL> SELECT 2 3 FROM last_name, salary, 12 * (salary + 100) Annual Salary s_emp;

Class Management Note:

Within a SQL statement, a column alias can be used in both the SELECT and the ORDER BY clauses. You cannot use column aliases in the WHERE clause. Both alias features comply with the ANSI SQL 92 standard. You can use this method of achieving mixed case column headings for now. In a later section, you can format headings using the SQL*Plus COLUMN command section.

Selecting Rows

1 21

1 22

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

The Concatenation Operator

You can link columns to other columns, arithmetic expressions, or constant values to create a character expression by using the concatenation operator (||). Columns on either side of the operator are combined to make one single output column. Example Display the full names of the employees with the heading Employees. SQL> SELECT 2 FROM first_name||last_name AS Employees s_emp;

Employees -------------------------------------------------CarmenVelasquez LaDorisNgao MidoriNagayama MarkQuick-To-See AudryRopeburn MollyUrguhart ... The AS keyword before the alias name makes the SELECT clause easier to read.
Class Management Note:

The resulting column is VARCHAR2 datatype.

Selecting Rows

1 23

1 24

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Literal Character String

A literal is any character, expression, or number included in the SELECT list that is not a column name or a column alias. It is printed for each row returned. Literal strings of free-format text can be included in the query result and are treated like a column in the SELECT list. Date and character literals must be enclosed within single quotation marks ( ); number literals must not. Example Display the full names of the employees and their titles with the heading Employees. Be sure to add punctuation. SQL> SELECT 2 3 FROM first_name || || last_name || , || title Employees s_emp;

Employees --------------------------------Carmen Velasquez, President LaDoris Ngao, VP, Operations Midori Nagayama, VP, Sales Mark Quick-To-See, VP, Finance Audry Ropeburn, VP, Administration Molly Urguhart, Warehouse Manager ...
Class Management Note:

Point out to students that there is a blank space between single quotes in the middle of first_name and last_name, in the select statement.

Selecting Rows

1 25

Class Management Note:

Make sure everyone is aware of exactly what a null is. DEMO: l1null1.sql PURPOSE: Demonstrates why sales representatives have values and everyone else has a null. No commission values exist for non-sales representatives, therefore the calculation with null results in null. Currently, Oracle treats a character value with length of zero as null. However, this may not continue to be true in future versions of Oracle.

1 26

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Managing Null Values

If a row lacks a data value for a particular column, that value is said to be null, or to contain null. A null value is a value that is unavailable, unassigned, unknown, or inapplicable. In the COMMISSION_PCT column, you notice that only Sales Representatives earn commission. Other employees are not entitled to earn commission. A null value represents that fact. A null value is not the same as zero or a space. Zero is a number, and a space is a character. Columns of any datatype can contain null values, unless the column was defined as NOT NULL or as PRIMARY KEY when the table was created. Null Values in Arithmetic Expressions If any column value in an expression is null, the result is null. For example, if you attempt to perform division with zero, you will get an error. However, if you divide by null, the result is null. Example Display the last name, salary, title, and calculated commission. SQL> SELECT 2 3 FROM last_name, title, salary*commission_pct/100 COMM s_emp;

LAST_NAME -----------... Havel Magee Giljum Sedeghi Nguyen Dumas Maduro ...

TITLE COMM --------------------- ---------Warehouse Manager Sales Representative Sales Representative Sales Representative Sales Representative Sales Representative Stock Clerk

140 186.25 151.5 228.75 253.75

For more information, see Oracle7 Server SQL Reference, Release 7.3, Elements of SQL.

Selecting Rows

1 27

1 28

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Managing Null Values

NVL Function In order to convert a null value to an actual value, use the NVL function. Syntax NVL (expr1, expr2) where: expr1 expr2

continued

is the source value or expression that may contain null. is the target value for converting null.

Note: You can use the NVL function to convert any datatype, but the return value is always the same as the datatype of expr1. Example To calculate values for all employees from the previous example, use the NVL function to convert null values to zero. SQL> SELECT 2 3 FROM LAST_NAME -----------... Havel Magee Giljum ... last_name, title, salary * NVL(commission_pct,0)/100 COMM s_emp; TITLE COMM --------------------- ---------Warehouse Manager Sales Representative Sales Representative 0 186.25 186.25

NVL Conversions for Various Datatypes Datatype NUMBER DATE CHAR or VARCHAR2 Conversion Example NVL(number_column,9) NVL(date_column,01-JAN-95) NVL(character_column,Unavailable)

Selecting Rows

1 29

SQL> SELECT 2 FROM

DISTINCT name s_dept;

Class Management Note:

DEMO PURPOSE: Show all the rows. Then compare the result by using the DISTINCT keyword. 1. At the prompt, enter: SELECT name FROM s_dept; 2. Show the students the duplicate rows in the output. 3. At the prompt, enter: SELECT DISTINCT name FROM s_dept; 4. Now display the unique department names.

1 30

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Preventing the Selection of Duplicate Rows

Unless you indicate otherwise, SQL*Plus displays the results of a query without eliminating duplicate rows. Example Displaying All Rows Display all department names in the S_DEPT table. SQL> SELECT 2 FROM name s_dept;

NAME -----------------Finance Sales Sales Sales Sales Sales Operations . . . 12 rows selected The DISTINCT Keyword To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT command word. Example Displaying Unique Rows Display all unique department names in the S_DEPT table. SQL> SELECT 2 FROM DISTINCT name s_dept;

NAME ----------------------Administration Finance Operations Sales

Selecting Rows

1 31

1 32

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Preventing the Selection of Duplicate Rows

DISTINCT with Multiple Columns

continued

You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all selected columns. Example Display all the different combinations of job titles and department numbers. SQL> SELECT 2 FROM DISTINCT dept_id, title s_emp;

DEPT_ID ------10 31 31 32 33 34 34 35 41 41 41 42 42 43 43 44 44 45 45 50 50

TITLE --------------------VP, Finance Sales Representative VP, Sales Sales Representative Sales Representative Sales Representative Stock Clerk Sales Representative Stock Clerk VP, Operations Warehouse Manager Stock Clerk Warehouse Manager Stock Clerk Warehouse Manager Stock Clerk Warehouse Manager Stock Clerk Warehouse Manager President VP, Administration

21 rows selected.

Selecting Rows

1 33

1 34

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL*Plus Commands
In this lesson, you saw how SQL commands are executed within a product called SQL*Plus. SQL*Plus is a SQL and PL/SQL command execution environment with additional features. You can use a number of SQL*Plus commands when writing even the most basic of SQL statements. This section covers some basic SQL*Plus commands to help you to
D D D D D D

Describe the table structure. Edit SQL in the buffer. Save files containing SQL for editing purposes. Execute saved files. Load SQL commands from a file into the SQL buffer. Obtain online help

For more information, see SQL*Plus Users Guide and Reference, Release 3.3.

Selecting Rows

1 35

1 36

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Logging in to SQL*Plus
How you invoke SQL*Plus depends upon which type of operating system or windows environment you are running. Log In Through a Windows Environment You double-click the SQL*Plus icon in the window manager, then enter the username, password, and database, if required.

Log In Through a Command Line Environment Once you log on to your machine, at the operating system prompt enter the SQL*Plus command. sqlplus [username [/password [@database]]] where: username password is your database username. is your database password. If you enter your password here, it is visible. @database is the database connect string. Note: To ensure the integrity of your password, do not enter it at the operating system prompt. Instead, only enter your username. Enter your password at the Password prompt. Once you are successfully logged in SQL*Plus, you see the following message:
SQL*Plus: Version 3.1.2 Production on Fri May 12th 15:31:32 1995 Copyright (c) Oracle Corporation 1979, 1992, All rights reserved. SQL>

Selecting Rows

1 37

SQL> DESCRIBE s_dept

1 38

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Displaying Table Structure

In SQL*Plus, you can display the structure of a table using the DESCRIBE command. The result of the command is to see the column names, datatypes, and whether a column must contain data. DESC[RIBE] tablename where: tablename is the name of any existing table, view, or synonym accessible to the user.

Example Display information about the structure of the S_DEPT table. SQL> DESCRIBE s_dept

Name --------------ID NAME REGION_ID where: Null? Type Datatype NUMBER (p,s) VARCHAR2(s) DATE CHAR(s)

Null? Type -------- -------------------NOT NULL NUMBER(7) NOT NULL VARCHAR2(25) NUMBER(7) indicates that a column must contain data. displays the datatype for a column.

Description Number value having a maximum number of digits p, and the number of digits to the right of the decimal point s. Variable length character value of maximum size s. Date and time value between January 1, 4712 B.C. and December 31, 4712 A.D. Fixed length character value of size s.

For more information, see Oracle 7 Server SQL Reference, Release 7.3.

Selecting Rows

1 39

1 40

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL*Plus Editing Commands

When you enter a SQL command, it is stored in a part of memory called the SQL buffer and remains there until you enter a new command. SQL*Plus commands are entered one line at a time and are not stored in the SQL buffer. Guidelines
D

If you press [RETURN] before completing a command, SQL*Plus will prompt you with a line number. You terminate the SQL buffer by either entering one of the terminator characters (semicolon or slash), or pressing [RETURN] twice. You will now see the SQL prompt.

SQL*Plus Editing Commands Command A[PPEND] text C[HANGE] / old / new / C[HANGE] / text / CL[EAR] BUFF[ER] DEL DEL n DEL m n I[NPUT] I[NPUT] text L[IST] L[IST] n L[IST] m n R[UN] n n text 0 text Description Adds text to the end of the current line. Changes old text to new in the current line. Deletes text from the current line. Deletes all lines from the SQL buffer. Deletes current line. Deletes one line (specified by n) Deletes a range of lines (m to n) Inserts an indefinite number of lines. Inserts a line consisting of text. Lists all lines in the SQL buffer. Lists one line (specified by n). Lists a range of lines (m to n). Displays and runs the current SQL command in the buffer. Specifies the line to make the current line. Replaces line n with text. Inserts a line before line 1.

Selecting Rows

1 41

1 42

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

SQL*Plus File Commands and Online Help

SQL commands act as the vehicle to the Oracle Server. SQL*Plus commands are used to control the environment, format query results, and manage files. You can use the commands identified in the following table. File Commands Command SAV[E] filename [.ext] [REP[LACE]|APP[END]] Description Saves current contents of SQL buffer to a file. Use APPEND to add to an existing file; use REPLACE to overwrite an existing file. The default file extension is .sql. Writes the contents of a previously saved file to the SQL buffer. The default extension for the filename is .sql. Runs a previously saved command file. Runs a previously saved command file (same as START). Invokes the editor and saves the buffer contents to a file named afiedt.buf. Invokes editor to edit contents of a saved file. Stores query results in a file, OFF closes the spool file. OUT closes the spool file and sends the file results to the system printer. Leaves SQL*Plus.

GET filename [.ext]

STA[RT] filename [.ext] @ filename EDIT ED[IT] [filename[.ext]] SPO[OL] [filename[.ext]| OFF|OUT] EXIT

Note: You can change the text editor invoked by changing the value of the SQL*Plus variable _EDITOR by using the DEFINE command. You can enter only one SQL*Plus command per SQL prompt. SQL*Plus commands are not stored in the buffer. To continue a SQL*Plus command on the next line, end the current line with a hyphen (-).

Selecting Rows

1 43

Class Management Note:

Stress that if an alias was used for the column name in the SELECT statement, then the alias is used in the COLUMN command. If the COLUMN command specifies a HEADING setting, the heading will supersede the alias in the display. And, if the column was qualified in the SELECT statement, for example S_EMP.SALARY, only format the column name, for example SALARY.

1 44

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Report
COLUMN Command Control the display of a column in a report by using the COLUMN command. For example, you can change the heading, width, and format. Syntax COL[UMN] [{column|alias} [option ...]]

COLUMN Command Options Option CLE[AR] FOR[MAT] format HEA[DING] text JUS[TIFY] {align} NOPRI[NT] NUL[L] text PRI[NT] TRU[NCATED] WRA[PPED] WOR[D_WRAPPED] Description Clears any column formats. Changes the display of the column data. Sets the column heading. A vertical line (|) will force a line feed in the heading if you do not use justification. Justifies the column heading (not the data) to be left, center, or right. Hides the column. Specifies text to be displayed for null values. Shows the column. Truncates the string at the end of the first line of display. Wraps the end of the string to the next line. Same as WRAPPED, but ensures that words do not split.

Format elements used in the FORMAT option are discussed on the next page. If you have a lengthy command, you can continue it on the next line by ending the current line with a hyphen (-).

Selecting Rows

1 45

Class Management Note:

DEMO: l1col.sql PURPOSE: The script file contains only a SELECT statement. Apply formatting to the columns. 1. Run the script file. Note the label for the LAST_NAME column and position of the label and format for the SALARY column. 2. Enter the COLUMN commands at the SQL prompt. COLUMN last_name HEADING Employee|Name FORMAT A15 COLUMN salary JUSTIFY LEFT FORMAT 99,990.00 3. Run the script file again. Show the students the now-formatted column information.

1 46

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Report
Display or Clear Settings

continued

To show or clear the current COLUMN command settings, use the following commands: Command COL[UMN] column COL[UMN] CLE[AR] COL[UMN] Description Displays the current settings for the specified column. Displays the current settings for all columns. Clears the settings for all columns.

COL[UMN] column CLE[AR] Clears the settings for the specified column.

Selecting Rows

1 47

Class Management Note:

DEMO: l1col.sql PURPOSE: The script file contains only a SELECT statement. Demonstrate that if the format is incorrect for the value, pound signs will display. 1. Run the script file. Note the format for the SALARY column. 2. Enter the following command: COLUMN salary FORMAT 999 3. Run the script file again. Show the students the now-formatted column information. 4. Enter the another COLUMN command: COLUMN salary FORMAT A15 5. Run the script file again.

1 48

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Creating a Report
Sample Column Format Model Elements Element Description An 9 0 $ L . , Sets a display width of n for character and date columns. Represents a single zero-suppression digit. Enforces leading zero. Represents a floating dollar sign. Represents local currency. Represents the position of the decimal point. Represents the thousand separator. Example N/A 999999 099999 $9999 L9999 9999.99 9,999 Result N/A 1234 01234 $1234 L1234

continued

1234.00 1,234

The Oracle7 Server displays a string of pound signs (#) in place of a whole number whose digits exceed the number of digits provided in the format model. It will also display pound signs in place of a value whose format model is alphanumeric, but whose actual value is numeric.

Selecting Rows

1 49

1 50

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Summary
In this lesson, you have learned about retrieving information from a database table with the SELECT statement. SELECT [DISTINCT] {*, column [alias], ...} FROM table; where: SELECT DISTINCT * column alias FROM table SQL*Plus SQL*Plus is an execution environment you can use to send SQL commands to the database server and to edit and to save SQL commands. Commands may be executed from the SQL prompt or from a script file. is a list of at least one column. suppreses duplicates. selects all columns. selects the named column. gives selected column a different heading. specifies the table containing the columns.

Selecting Rows

1 51

1 52

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice Overview
This is the first of many practices. In most cases, the questions show the output that is required. The solutions (if you require them) can be found in Appendix A. Practices are intended to introduce all topics covered in the lesson. The first six questions are paperbased. In any practice, there may be if you have time questions. Do these only if you have completed all other questions within the allocated time and would like a further challenge to your skills. Practice Contents
D D D D

Selecting all data from different tables. Describing the structure of tables. Performing arithmetic calculations and specifying different column names. Editing commands in the buffer.

Please take this practice slowly and precisely. You can experiment with saving and running command files. If you have any questions at any time, please attract the instructors attention. Paper-Based Questions For questions 12 circle either True or False.
Class Management Note:

Duration: 25 minutes Please advise students how to log in to SQL*Plus.

Selecting Rows

1 53

1 54

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder

Practice 1
1. 2. 3.

SQL commands are always held in a buffer. True / False SQL*Plus commands assist with querying data. True / False Show the structure of the S_DEPT table. Select all information from the S_DEPT table. ID --------10 31 32 33 34 35 41 42 43 44 45 50 NAME REGION_ID ------------------------- --------Finance 3 Sales 1 Sales 2 Sales 3 Sales 4 Sales 5 Operations 1 Operations 2 Operations 3 Operations 4 Operations 5 Administration 1

12 rows selected.
4.

Show the structure of the S_CUSTOMER table. Using this table, perform the following actions.
a. b.

Retrieve all information from the S_CUSTOMER table. Display the name and phone number for each customer.

Selecting Rows

1 55

Practice 1
4.Continued c.

continued

Display the phone number and name for each customer, with phone number appearing first. PHONE -------------------55-2066101 81-20101 91-10351 1-206-104-0103 852-3692888 33-2257201 234-6036201 49-527454 809-352689 52-404562 42-111292 20-1209211 1-415-555-6281 1-716-555-7171 7-3892456 15 rows selected. NAME ------------------------Unisports Simms Atheletics Delhi Sports Womansport Kams Sporting Goods Sportique Sweet Rock Sports Muench Sports Beisbol Si! Futbol Sonora Kuhns Sports Hamada Sport Big Johns Sports Emporium Ojibway Retail Sporta Russia

1 56

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder