Practice Solutions
A
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ2
Practice Solutions AĆ3
Preface
There is often more than one way to achieve any result in SQL. Where possible, the
alternatives have been identified in these solutions. The performance benefits, if any,
are also mentioned. If you want to analyze any of the statements, refer to SQL*Trace.
This facility allows you to see how the SQL statement is being interpreted at the
database level.
For more information, see
Oracle7 Server SQL Reference, Release 7.3 and Oracle7 Server Administrators
Guide.
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ4
Practice 1 Solutions
1.
SQL commands are always held in a buffer.
"
True
2.
SQL*Plus commands assist with querying data.
"
False
SQL*Plus commands allow you to format resulting data and control files.
Only SQL accesses the database.
3.
Show the structure of the S_DEPT table. Select all information from the
S_DEPT table.
SQL> DESCRIBE s_dept
Name Null? Type
---------------------------- -------- ----

ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
REGION_ID NUMBER(7)
SQL> SELECT *
2 FROM s_dept;
ID NAME REGION_ID
--------- ------------------------- ---------
10 Finance 1
31 Sales 1
32 Sales 2
33 Sales 3
34 Sales 4
35 Sales 5
41 Operations 1
42 Operations 2
43 Operations 3
44 Operations 4
45 Operations 5
50 Administration 1
12 rows selected.
Practice Solutions AĆ5
Practice 1 Solutions
continued
4.
Show the structure of the S_CUSTOMER table. Using this table, perform the
following actions:
SQL> DESCRIBE s_customer
Name Null? Type
-------------------------- -------- ----
ID NOT NULL NUMBER(7)

NAME NOT NULL VARCHAR2(50)
PHONE VARCHAR2(25)
ADDRESS VARCHAR2(400)
CITY VARCHAR2(30)
STATE VARCHAR2(20)
COUNTRY VARCHAR2(30)
ZIP_CODE VARCHAR2(75)
CREDIT_RATING VARCHAR2(9)
SALES_REP_ID NUMBER(7)
REGION_ID NUMBER(7)
COMMENTS VARCHAR2(255)
a.
Retrieve all information from the S_CUSTOMER table.
SQL> SELECT *
2 FROM s_customer;
Continued
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ6
Practice 1 Solutions
continued
4.—continued
b.
Display the name and phone number for each customer.
SQL> SELECT name, phone
2 FROM s_customer;
NAME PHONE
----------------------------------- --------------
Unisports 55-2066101
Simms Atheletics 81-20101
Delhi Sports 91-10351
Womansport 1-206-104-0103

Kam’s Sporting Goods 852-3692888
Sportique 33-2257201
Sweet Rock Sports 234-6036201
Muench Sports 49-527454
Beisbol Si! 809-352689
Futbol Sonora 52-404562
Kuhn’s Sports 42-111292
Hamada Sport 20-1209211
Big John’s Sports Emporium 1-415-555-6281
Ojibway Retail 1-716-555-7171
Sporta Russia 7-3892456
15 rows selected.
Continued
Practice Solutions AĆ7
Practice 1 Solutions
continued
4.—continued
c.
Display the phone number and name for each customer, with phone number
appearing first.
SQL> SELECT phone, name
2 FROM s_customer;
PHONE NAME
-------------------- -------------------------
55-2066101 Unisports
81-20101 Simms Atheletics
91-10351 Delhi Sports
1-206-104-0103 Womansport
852-3692888 Kam’s Sporting Goods
33-2257201 Sportique

234-6036201 Sweet Rock Sports
49-527454 Muench Sports
809-352689 Beisbol Si!
52-404562 Futbol Sonora
42-111292 Kuhn’s Sports
20-1209211 Hamada Sport
1-415-555-6281 Big John’s Sports Emporium
1-716-555-7171 Ojibway Retail
7-3892456 Sporta Russia
15 rows selected.
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ8
Practice 2 Solutions
1.
You cannot order by a column that you have not selected.
"
False
2.
This SELECT statement will execute successfully.
"
True
SQL> SELECT last_name, title, salary Ann_sal
2 FROM s_emp
3 WHERE last_name = ’Dancs’;
3.
This SELECT statement will execute successfully.
"
True
SQL> SELECT *
2 FROM s_emp
3 WHERE salary*12 = 9600;

4.
There are four coding errors in this statement. Can you identify them?
SQL> SELECT id, last_name,
2 salary x 12 ANNUAL SALARY
3 FROM s_emp
4 WHERE sal > 3000
5 AND start_date LIKE %84;
"
No SAL column in existence (WHERE clause).
"
The ANNUAL SALARY alias cannot include spaces. Alias should read
ANNUAL_SALARY or be enclosed by quotation marks.
"
The multiplication operator is *, not x as shown in line 2.
"
All values when using the LIKE operator should be enclosed within single
quotation marks. The value should read ‘%84’ in line 5.
Practice Solutions AĆ9
Practice 2 Solutions
continued
5.
Use the S_CUSTOMER table to perform the following actions.
a.
Create a query to display the name, customer number, and credit rating for all
companies represented by sales representative 11. Save your SQL statement to
a file named p2q5.
SQL> SELECT name, id, credit_rating
2 FROM s_customer
3 WHERE sales_rep_id = 11
4

SQL> SAVE p2q5
Created file p2q5
b.
Run your query in the file p2q5.
SQL> START p2q5
NAME ID CREDIT_RA
------------------------------ --------- ---------
Womansport 204 EXCELLENT
Beisbol Si! 209 EXCELLENT
Big John’s Sports Emporium 213 EXCELLENT
Ojibway Retail 214 POOR
Continued
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ10
Practice 2 Solutions
continued
5.—continued
c.
Load p2q5 into the SQL buffer. Name the column headings Company,
Company ID, and Rating. Rerun your query. Resave your query as p2q5.
"
Solution file: p2q5.sql
SQL> GET p2q5
1 SELECT name, id, credit_rating
2 FROM s_customer
3* WHERE sales_rep_id = 11
SQL> 1 SELECT name ”Company”, id ”Company ID”,
SQL> i credit_rating ”Rating”
SQL> RUN
1 SELECT name ”Company”, id ”Company ID”,
2 credit_rating ”Rating”

3 FROM s_customer
4* WHERE sales_rep_id = 11
Company Company ID Rating
------------------------------ ---------- ---------
Womansport 204 EXCELLENT
Beisbol Si! 209 EXCELLENT
Big John’s Sports Emporium 213 EXCELLENT
Ojibway Retail 214 POOR
SQL> SAVE p2q5 REPLACE
Wrote file p2q5
Continued
Practice Solutions AĆ11
Practice 2 Solutions
continued
5.—continued
d.
Retrieve p2q5 into the SQL buffer. Order the query results in descending
order by customer number. Run your query.
SQL> GET p2q5
1 SELECT name ”Company”, id ”Company ID”,
2 credit_rating ”Rating”
3 FROM s_customer
4* WHERE sales_rep_id = 11
SQL> i ORDER BY 2 DESC
SQL> /
Company Company ID Rating
------------------------------ ---------- ---------
Ojibway Retail 214 POOR
Big John’s Sports Emporium 213 EXCELLENT
Beisbol Si! 209 EXCELLENT

Womansport 204 EXCELLENT
6.
Show the structure of the S_EMP table.
SQL> DESCRIBE s_emp
Name Null? Type
-------------------------- -------- ----
ID NOT NULL NUMBER(7)
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
USERID NOT NULL VARCHAR2(8)
START_DATE DATE
COMMENTS VARCHAR2(255)
MANAGER_ID NUMBER(7)
TITLE VARCHAR2(25)
DEPT_ID NUMBER(7)
SALARY NUMBER(11,2)
COMMISSION_PCT NUMBER(4,2)
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ12
Practice 2 Solutions
continued
6.—continued
a.
Display the user name for employee 23.
SQL> SELECT userid
2 FROM s_emp
3 WHERE id = 23;
USERID
--------
rpatel
b.

Display the first name, last name, and department number of the employees in
departments 10 and 50 in alphabetical order of last name. Merge the first
name and last name together, and title the column Employees.
SQL> SELECT first_name||’ ’||last_name
2 ”Employees”, dept_id
3 FROM s_emp
4 WHERE dept_id IN (10,50)
5 ORDER BY last_name;
Employees DEPT_ID
------------------------------------ ---------
Mark Quick-To-See 10
Audry Ropeburn 50
Carmen Velasquez 50
Continued
Practice Solutions AĆ13
Practice 2 Solutions
continued
6.—continued
c.
Display all employees whose last names contain an “s”.
"
This solution is not quite complete because names that contain a capital
S do not appear in this list. In a later lesson, you will see how to change
your query into a case-insensitive query.
SQL> SELECT first_name, last_name
2 FROM s_emp
3 WHERE last_name like ’%s%’;
FIRST_NAME LAST_NAME
------------------------- ------------------------
Carmen Velasquez

Andre Dumas
Bela Dancs
d.
Display the user names and start date of employees hired between May 5,
1990 and May 26, 1991. Order the query results by start date ascending order.
SQL> SELECT userid, start_date
2 FROM s_emp
3 WHERE start_date BETWEEN ’05-may-90’
4 AND ’26-may-91’
5 ORDER BY start_date;
Continued
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ14
Practice 2 Solutions
continued
6d.—continued
USERID START_DAT
-------- ---------
rmenchu 14-MAY-90
cmagee 14-MAY-90
rpatel 17-OCT-90
echang 30-NOV-90
murguhar 18-JAN-91
anozaki 09-FEB-91
ysedeghi 18-FEB-91
mhavel 27-FEB-91
bdancs 17-MAR-91
sschwart 09-MAY-91
amarkari 26-MAY-91
11 rows selected.
7.

Use the S_EMP table to perform the following actions.
a.
Write a query to show the last name and salary of all employees who are not
making between 1000 and 2500 per month.
SQL> SELECT last_name, salary
2 FROM s_emp
3 WHERE salary NOT BETWEEN 1000 AND 2500;
LAST_NAME SALARY
------------------------- ---------
Smith 940
Patel 795
Newman 750
Markarian 850
Chang 800
Patel 795
Dancs 860
7 rows selected.
Continued
Practice Solutions AĆ15
Practice 2 Solutions
continued
7.—continued
b.
List the last name and salary of employees who earn more than 1350 who are
in department 31, 42, or 50. Label the last name column Employee Name, and
label the salary column Monthly Salary.
SQL> SELECT last_name ”Employee Name”,
2 salary ”Monthly Salary”
3 FROM s_emp
4 WHERE salary > 1350

5 AND dept_id IN (31, 42, 50);
Employee Name Monthly Salary
------------------------- --------------
Velasquez 2500
Nagayama 1400
Ropeburn 1550
Magee 1400
c.
Display the last name and start date of every employee who was hired in
1991.
SQL> SELECT last_name, start_date
2 FROM s_emp
3 WHERE start_date LIKE ’%91’;
Continued
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ16
Practice 2 Solutions
continued
7c.—continued
LAST_NAME START_DAT
------------------------- ---------
Nagayama 17-JUN-91
Urguhart 18-JAN-91
Havel 27-FEB-91
Sedeghi 18-FEB-91
Dumas 09-OCT-91
Nozaki 09-FEB-91
Patel 06-AUG-91
Newman 21-JUL-91
Markarian 26-MAY-91
Dancs 17-MAR-91

Schwartz 09-MAY-91
11 rows selected.
d.
Display the full name of all employees with no manager.
SQL> SELECT first_name, last_name
2 FROM s_emp
3 WHERE manager_id IS NULL;
FIRST_NAME LAST_NAME
------------------------- -------------------------
Carmen Velasquez
Practice Solutions AĆ17
Practice 2 Solutions
continued
8.
Show the structure of the S_PRODUCT table.
SQL> DESCRIBE s_product
Name Null? Type
------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(50)
SHORT_DESC VARCHAR2(255)
LONGTEXT_ID NUMBER(7)
IMAGE_ID NUMBER(7)
SUGGESTED_WHLSL_PRICE NUMBER(11,2)
WHLSL_UNITS VARCHAR2(25)
a.
Alphabetically display all products having a name beginning with Pro.
SQL> SELECT name
2 FROM s_product
3 WHERE name LIKE ’Pro%’;

NAME
-------------------------------------------------
-
Pro Curling Bar
Pro Ski Boot
Pro Ski Pole
Prostar 10 Pound Weight
Prostar 100 Pound Weight
Prostar 20 Pound Weight
Prostar 50 Pound Weight
Prostar 80 Pound Weight
8 rows selected.
Continued
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ18
Practice 2 Solutions
continued
8.—continued
b.
Display all product names and short descriptions for all descriptions
containing the word bicycle.
"
Results have been formatted.
SQL> SELECT name, short_desc
2 FROM s_product
3 WHERE short_desc LIKE ’%bicycle%’
NAME SHORT_DESC
------------------------- --------------------
Grand Prix Bicycle Road bicycle
Himalaya Bicycle Mountain bicycle
Grand Prix Bicycle Tires Road bicycle tires

Himalaya Tires Mountain bicycle tires
c.
Display all short descriptions. Compare the results from Exercise 10b. Did
your response in Exercise 10b return all descriptions containing “bicycle”?
"
No, not all words containing “bicycle” were returned because the
WHERE clause is case-sensitive. In a later lesson, you will see how to
remove the case-sensitivity.
SQL> SELECT short_desc
2 FROM s_product
3 ORDER BY 1;
Continued
Practice Solutions AĆ19
Practice 2 Solutions
continued
8c.—continued
SHORT_DESC
---------------------------------------------
Advanced ski boot
Advanced ski pole
Baseball
Batting helmet
Beginner’s ski boot
Beginner’s ski pole
Bicycle helmet
Catcher’s glove
Curling bar
Eighty pound weight
Elbow pads, pair
Fifty pound weight

Infielder’s glove
Intermediate ski boot
Intermediate ski pole
Junior soccer ball
Knee pads, pair
Mountain bicycle
Mountain bicycle tires
One hundred pound weight
Outfielder’s glove
Road bicycle
Road bicycle tires
Straight bar
Ten pound weight
Thirty inch bat
Thirty-six inch bat
Thirty-two inch bat
Tire pump
Twenty pound weight
Water bottle
World cup net
World cup soccer ball
33 rows selected.
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ20
Practice 3 Solutions
1.
Single row functions work on many rows to produce a single result.
"
False
Single row functions work for each row selected by the query and return one
result per row.

2.
You can use all of the arithmetic operators on date values.
"
False
Additional and subtraction are the only operators that can be used.
3.
What is the name of the pseudo column that holds the current date?
"
SYSDATE
Practice Solutions AĆ21
Practice 3 Solutions
continued
4.
Display the employee number, last name, and salary increased by 15% and
expressed as a whole number.
SQL> SELECT id, last_name, ROUND(salary * 1.15)
2 FROM s_emp;
ID LAST_NAME ROUND(SALARY*1.15)
------ ------------ ------------------
1 Velasquez 2875
2 Ngao 1668
3 Nagayama 1610
4 Quick-To-See 1668
5 Ropeburn 1783
6 Urguhart 1380
7 Menchu 1438
8 Biri 1265
9 Catchpole 1495
10 Havel 1503
11 Magee 1610

12 Giljum 1714
13 Sedeghi 1742
14 Nguyen 1754
15 Dumas 1668
16 Maduro 1610
17 Smith 1081
18 Nozaki 1380
19 Patel 914
20 Newman 863
21 Markarian 978
22 Chang 920
23 Patel 914
24 Dancs 989
25 Schwartz 1265
25 rows selected.
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ22
Practice 3 Solutions
continued
5.
Display the employee last name and title in parentheses for all employees. The
report should look like the output below.
SQL> SELECT last_name||’(’||INITCAP(title)||’)’
2 EMPLOYEE
3 FROM s_emp
4 ORDER BY last_name;
EMPLOYEE
----------------------------------------------------
Biri(Warehouse Manager)
Catchpole(Warehouse Manager)
Chang(Stock Clerk)

Dancs(Stock Clerk)
Dumas(Sales Representative)
Giljum(Sales Representative)
Havel(Warehouse Manager)
Maduro(Stock Clerk)
Magee(Sales Representative)
Markarian(Stock Clerk)
Menchu(Warehouse Manager)
Nagayama(Vp, Sales)
Newman(Stock Clerk)
Ngao(Vp, Operations)
Nguyen(Sales Representative)
Nozaki(Stock Clerk)
Patel(Stock Clerk)
Patel(Stock Clerk)
Quick-To-See(Vp, Finance)
Ropeburn(Vp, Administration)
Schwartz(Stock Clerk)
Sedeghi(Sales Representative)
Smith(Stock Clerk)
Urguhart(Warehouse Manager)
Velasquez(President)
25 rows selected.
Practice Solutions AĆ23
Practice 3 Solutions
continued
6.
Display each employee’s last name, hire date, and salary review date, which is the
first Monday after six months of service. Format the dates to appear in the format
similar to Eighth of May 1992.

"
Results have been formatted.
SQL> SELECT last_name, start_date,
2 TO_CHAR(NEXT_DAY(ADD_MONTHS(start_date,6),
3 ’MONDAY’),
4 ’fmDdspth ”of” Month YYYY’) REVIEW
5 FROM s_emp;
Continued
Introduction to Oracle: SQL and PL/SQL Using Procedure BuilderAĆ24
Practice 3 Solutions
continued
6.—continued
LAST_NAME START_DAT REVIEW
------------ --------- ------------------------------
Velasquez 03-MAR-90 Tenth of September 1990
Ngao 08-MAR-90 Tenth of September 1990
Nagayama 17-JUN-91 Twenty-Third of December 1991
Quick-To-See 07-APR-90 Eighth of October 1990
Ropeburn 04-MAR-90 Tenth of September 1990
Urguhart 18-JAN-91 Twenty-Second of July 1991
Menchu 14-MAY-90 Nineteenth of November 1990
Biri 07-APR-90 Eighth of October 1990
Catchpole 09-FEB-92 Tenth of August 1992
Havel 27-FEB-91 Second of September 1991
Magee 14-MAY-90 Nineteenth of November 1990
Giljum 18-JAN-92 Twentieth of July 1992
Sedeghi 18-FEB-91 Nineteenth of August 1991
Nguyen 22-JAN-92 Twenty-Seventh of July 1992
Dumas 09-OCT-91 Thirteenth of April 1992
Maduro 07-FEB-92 Tenth of August 1992

Smith 08-MAR-90 Tenth of September 1990
Nozaki 09-FEB-91 Twelfth of August 1991
Patel 06-AUG-91 Tenth of February 1992
Newman 21-JUL-91 Twenty-Seventh of January 1992
Markarian 26-MAY-91 Second of December 1991
Chang 30-NOV-90 Third of June 1991
Patel 17-OCT-90 Twenty-Second of April 1991
Dancs 17-MAR-91 Twenty-Third of September 1991
Schwartz 09-MAY-91 Eleventh of November 1991
25 rows selected.
Practice Solutions AĆ25
Practice 3 Solutions
continued
7.
Display the product name for products that have “ski” in the name.
SQL> SELECT name
2 FROM s_product
3 WHERE LOWER(name) LIKE ’%ski%’;
NAME
--------------------
Ace Ski Boot
Pro Ski Boot
Bunny Ski Pole
Ace Ski Pole
Pro Ski Pole
8.
For each employee, calculate the number of months between today and the date
the employee was hired. Order your result by the number of months employed.
Round the number of months up to the closest whole number.
SQL> SELECT last_name,

2 ROUND(MONTHS_BETWEEN(SYSDATE, start_date))
3 MONTHS_WORKED
4 FROM s_emp
5 ORDER BY MONTHS_BETWEEN(SYSDATE, start_date);
Continued