Download SQL_11g_PITB_D64258GC11_ag.pdf PDF

TitleSQL_11g_PITB_D64258GC11_ag.pdf
TagsSql Oracle Database Table (Database) Databases Data Management
File Size2.8 MB
Total Pages132
Table of Contents
                            Oracle Database: SQL  Fundamentals I - Activity Guide
Table of Contents
Practices for Lesson 1: Introduction
	Practices for Lesson 1
	Practice 1-1: Introduction
	Solution 1-1: Introduction
Practices for Lesson 2: Retrieving Data Using the SQL SELECT Statement
	Practices for Lesson 2
	Practice 2-1: Retrieving Data Using the SQL SELECT Statement
	Solution 2-1: Retrieving Data Using the SQL SELECT Statement
Practices for Lesson 3: Restricting and Sorting Data
	Practices for Lesson 3
	Practice 3-1: Restricting and Sorting Data
	Solution 3-1: Restricting and Sorting Data
Practices for Lesson 4: Using Single-Row Functions to Customize Output
	Practices for Lesson 4
	Practice 4-1: Using Single-Row Functions to Customize Output
	Solution 4-1: Using Single-Row Functions to Customize Output
Practices for Lesson 5: Using Conversion Functions and Conditional Expressions
	Practices for Lesson 5
	Practice 5-1: Using Conversion Functions and Conditional Expressions
	Solution 5-1: Using Conversion Functions and Conditional Expressions
Practices for Lesson 6: Reporting Aggregated Data Using the Group Functions
	Practices for Lesson 6
	Practice 6-1: Reporting Aggregated Data Using the Group Functions
	Solution 6-1: Reporting Aggregated Data Using the Group Functions
Practices for Lesson 7: Displaying Data from Multiple Tables Using Joins
	Practices for Lesson 7
	Practice 7-1: Displaying Data from Multiple Tables Using Joins
	Solution 7-1: Displaying Data from Multiple Tables Using Joins
Practices for Lesson 8: Using Subqueries to Solve Queries
	Practices for Lesson 8
	Practice 8-1: Using Subqueries to Solve Queries
	Solution 8-1: Using Subqueries to Solve Queries
Practices for Lesson 9: Using the Set Operators
	Practices for Lesson 9
	Practice 9-1: Using the Set Operators
	Solution 9-1: Using the Set Operators
Practices for Lesson 10: Manipulating Data
	Practices for Lesson 10
	Practice 10-1: Manipulating Data
	Solution 10-1: Manipulating Data
Practices for Lesson 11: Using DDL Statements to Create and Manage Tables
	Practices for Lesson 11
	Practice 11-1: Using DDL Statements to Create and Manage Tables
	Solution 11-1: Using DDL Statements to Create and Manage Tables
Practices for Lesson 12: Creating Other Schema Objects
	Practices for Lesson 12
	Practice 12-1: Creating Other Schema Objects
	Solution 12-1: Creating Other Schema Objects
Additional Practices and Solutions
	Practice 1-1
	Solution 1-1
	Case Study
	Practice 2-1
	Solution 2-1
                        
Document Text Contents
Page 1

Oracle Database: SQL
Fundamentals I



Activity Guide

D64258GC11

Edition 1.1

March 2012

D76184

Page 2

Copyright © 2012 , Oracle and/or its affiliates. All rights reserved.

Disclaimer

This document contains proprietary information and is protected by copyright and
other intellectual property laws. You may copy and print this document solely for your
own use in an Oracle training course. The document may not be modified or altered
in any way. Except where your use constitutes "fair use" under copyright law, you
may not use, share, download, upload, copy, print, display, perform, reproduce,
publish, license, post, transmit, or distribute this document in whole or in part without
the express authorization of Oracle.

The information contained in this document is subject to change without notice. If you
find any problems in the document, please report them in writing to: Oracle University,
500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not
warranted to be error-free.

Restricted Rights Notice


If this documentation is delivered to the United States Government or anyone using
the documentation on behalf of the United States Government, the following notice is
applicable:

U.S. GOVERNMENT RIGHTS
The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or
disclose these training materials are restricted by the terms of the applicable Oracle
license agreement and/or the applicable U.S. Government contract.


Trademark Notice


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names
may be trademarks of their respective owners.




Authors
Supriya Ananth
Salome Clement
Brian Pottle

Technical Contributors
and Reviewers
Diganta Choudhury
Bryan Roberts
Kimseong Loh
Laszlo Czinkoczki
Brent Dayley
Nancy Greenberg
Manish Pawar
Clair Bennett
Zarko Cesljas
Yanti Chang
Gerlinde Frenzen
Helen Robertson
Joel Goodman
Pedro Neves
Hilda Simon

Editor
Raj Kumar

Graphic Designer
Satish Bettegowda

Publishers
Sujatha Nagendra
Joseph Fernandez

Muh
amm

ad K
iani

(kia
ni71

@h
otm

ailฺc
om)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

3,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 66

Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Practices for Lesson 8: Using Subqueries to Solve Queries
Chapter 8 - Page 2

Practices for Lesson 8

Lesson Overview
In this practice, you write complex queries using nested SELECT statements.
For practice questions, you may want to create the inner query first. Make sure that it runs and
produces the data that you anticipate before you code the outer query.

Muh
amm

ad K
iani

(kia
ni71

@h
otm

ailฺc
om)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

3,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 67

Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Practices for Lesson 8: Using Subqueries to Solve Queries
Chapter 8 - Page 3

Practice 8-1: Using Subqueries to Solve Queries
1. The HR department needs a query that prompts the user for an employee’s last name. The

query then displays the last name and hire date of any employee in the same department
as the employee whose name they supply (excluding that employee). For example, if the
user enters Zlotkey, find all employees who work with Zlotkey (excluding Zlotkey).




2. Create a report that displays the employee number, last name, and salary of all employees

who earn more than the average salary. Sort the results in order of ascending salary.


3. Write a query that displays the employee number and last name of all employees who work

in a department with any employee whose last name contains the letter “u.” Save your SQL
statement as lab_08_03.sql. Run your query.



Muh
amm

ad K
iani

(kia
ni71

@h
otm

ailฺc
om)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

3,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 131

Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Additional Practices and Solutions
Chapter 13 - Page 31

ADD (price NUMBER(8,2));



DESCRIBE title

b. Create a script named lab_apcs_7b.sql that contains update statements that
update each video with a price according to the list provided. Run the commands in the
script.
Note: Have the TITLE_ID numbers available for this exercise.

SET ECHO OFF

SET VERIFY OFF

UPDATE title

SET price = &price

WHERE title_id = &title_id;

SET VERIFY OFF

SET ECHO OFF

8. Create a report that contains each customer’s history of renting videos. Be sure to include
the customer name, movie rented, dates of the rental, and duration of rentals. Total the
number of rentals for all customers for the reporting period. Save the commands that
generate the report in a script file named lab_apcs_8.sql.
Note: Your results may be different.
SELECT m.first_name||' '||m.last_name MEMBER, t.title,

r.book_date, r.act_ret_date - r.book_date DURATION

FROM member m

JOIN rental r

ON r.member_id = m.member_id

JOIN title t

ON r.title_id = t.title_id

ORDER BY member;


Muh
amm

ad K
iani

(kia
ni71

@h
otm

ailฺc
om)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

3,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 132

Copyright ' 2012, Oracle and/or its affiliates. All rights reserved.

Additional Practices and Solutions
Chapter 13 - Page 32



Muh
amm

ad K
iani

(kia
ni71

@h
otm

ailฺc
om)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

3,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Similer Documents