• Author / Uploaded
• ivory2011

• Categories
• Base de datos Oracle
• Bases de datos
• SQL
• Tabla (base de datos)
• Datos

Citation preview

academy.oracle.com

Database Design Project Oracle Baseball League Store Database Project Scenario: You are a small consulting company specializing in database development. You have just been awarded the contract to develop a data model for a database application system for a small retail store called Oracle Baseball League (OBL). The Oracle Baseball League store serves the entire surrounding community selling baseball kit. The OBL has two types of customer, there are individuals who purchase items like balls, cleats, gloves, shirts, screen printed t-shirts, and shorts. Additionally customers can represent a team when they purchase uniforms and equipment on behalf of the team. Teams and individual customers are free to purchase any item from the inventory list, but teams get a discount on the list price depending on the number of players. When a customer places an order we record the order items for that order in our database. OBL has a team of three sales representatives that officially only call on teams but have been known to handle individual customer complaints.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

2

Part 3 : Physical Design Part 3 Step 1: Introduction to Oracle Application Express Upload and run a script in APEX In this step you will use the “obl Sports.ddl” file provided for you to create your relational database. This file must first be extracted. Log on to your APEX environment using your username and password: 1. Click on SQL workshop. a. Click on SQL Scripts. b. Click on upload to browse for and chose the obl Sports.ddl file from your local drive. c. Once you have uploaded the file click on the run button. d. Choose run now e. Click on the view results option and scroll to the bottom of the screen to see if any errors occurred. f. If you had any errors then click on edit script to resolve the errors. g. Rerun the script. 2. Click on SQL workshop. a. Click on SQL commands b. Run the following query to check that the customers table was created properly: DESCRIBE customers; c. Check the description matches your table mapping document. d. Do the same for the other tables that were created. 3. Click on SQL workshop. a. Click on Object Browser to verify the tables through the application instead of through SQL. b. Select tables on the left menu. c. Click on a table to select it. d. Use the tabs along the top to investigate the table information. 4. All changes that you make in APEX are permanent changes so you do not need to save your work.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

3

Task Instructions Use the completed table mapping document produced in a previous exercise to verify the table creation. Table Name

Table Short Name

customers

ctr

Key Type

Optionality

Column Name

pk

*

ctr_number

VARCHAR2

6

uk

*

email

VARCHAR2

50

*

first_name

VARCHAR2

20

*

last_name

VARCHAR2

30

*

phone_number

VARCHAR2

11

*

current_balance

NUMBER

6,2

o

loyalty_card_number

VARCHAR2

6

fk1

o

tem_id

VARCHAR2

4

fk2

o

sre_id

VARCHAR2

4

Table Name

Data Type

Size

Table Short Name

customers_addresses cas Data Type

Size

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

8

*

address_line_1

VARCHAR2

30

o

address_line_2

VARCHAR2

30

*

city

VARCHAR2

15

*

postal_code

VARCHAR2

7

*

ctr_number

VARCHAR2

6

fk

Table Name

Table Short Name

teams

tem

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

4

*

name

VARCHAR2

20

*

number_of_players

NUMBER

2

o

discount

NUMBER

2

Data Type

Size

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

4

Table Name

Table Short Name

sales_representatives

sre

Key Type

Optionality

Column Name

pk

*

id

VARCHAR2

4

uk

*

email

VARCHAR2

50

*

first_name

VARCHAR2

20

*

last_name

VARCHAR2

30

*

phone_number

*

commission_rate

VARCHAR2 NUMBER

11 2

*

supervisor_id

VARCHAR2

4

fk

Data Type

Size

Table Name

Table Short Name

sales_representatives_address

sas

Key Type

Optionality

Column Name

Pk, fk

*

id

VARCHAR2

4

*

address_line_1

VARCHAR2

30

o

address_line_2

VARCHAR2

30

*

city

VARCHAR2

15

*

postal_code

VARCHAR2

7

Data Type

Size

Table Name

Table Short Name

orders

odr

Key Type

Optionality

Column Name

pk

*

id

*

odr_date

DATE

*

odr_time

TIMESTAMP

0

*

number_of_units

NUMBER

2

*

ctr_number

VARCHAR2

6

fk

Data Type VARCHAR2

Size 9

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

5

Table Name

Table Short Name

ordered_items

oim

Key Type

Optionality

Column Name

*

quantity_ordered

NUMBER

3

*

quantity_shipped

NUMBER

3

pk, fk1

*

odr_id

VARCHAR2

9

pk, fk2

*

itm_number

VARCHAR2

10

Table Name

Table Short Name

items

itm

Key Type

Optionality

Column Name

pk

*

itm_number

VARCHAR2

10

*

name

VARCHAR2

20

*

description

VARCHAR2

50

*

category

VARCHAR2

25

o

color

VARCHAR2

15

o

size

CHAR

1

fk

*

ilt_id

VARCHAR2

11

Table Name

Table Short Name

inventory_list

ilt

Key Type

Optionality

Column Name

pk

*

id

* *

Data Type

Data Type

Data Type

Size

Size

Size

VARCHAR2

11

cost

NUMBER

7,2

units

NUMBER

4

Table Name

Table Short Name

price_history

phy

Key Type

Optionality

Column Name

pk

*

start_date

DATE

pk

*

start_time

TIMESTAMP

0

*

price

NUMBER

7,2

o

end_date

DATE

o

end_time

TIMESTAMP

0

*

itm_number

VARCHAR2

10

pk, fk1

Data Type

Size

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

6

Part 3 Step 2: Data Definition Language (DDL) Use DDL to build and maintain database tables Exercise 1: Reading information from a script In this step you will use the “obl Sports.ddl” file to consolidate your knowledge of DDL. Open the “obl Sports.ddl” in a text editor. 1. How many tables have been created using the CREATE TABLE statement? Solution : 10 2. How many columns are created for the price history table? Solution : 6 3. What statement is used to enforce the constraint that the category column of the items table must have a value? Solution : NOT NULL 4. What is the name of the foreign key constraint between the customers and customer addresses tables? Solution : customer_address_customer_fk 5. What are the lowest and highest values that can be stored in the commission_rate column for the sales_representatives table? Solution : -99 to 99 6. What are the lowest and highest values that can be stored in the price column for the price_history table? Solution : -99999.99 to 99999.99 7. What are the 3 columns that make up the primary key for the price_history table? Solution : itm_number, start_date, start_time

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

7

Exercise 2 : Updating Constraints Log-in to APEX and go to the SQL commands environment Modifying a column 1. Run the DESCRIBE command on the orders table to view its structure. 2. Task: Add a default constraint that will use todays date to assign a value to the odr_date column of the orders table if no date is provided. Solution : ALTER TABLE orders MODIFY (odr_date DATE DEFAULT SYSDATE); 3. Run the DESCRIBE command again to verify the command was successful. Adding a check constraint 1. Run the DESCRIBE command on the customers table to view its structure. 2. Task: Add a check constraint that will not allow the customers current balance to go below zero. Solution : ALTER TABLE customers ADD CONSTRAINT cust_bal_ckeck CHECK (current_balance >= 0); 3. Run the DESCRIBE command again to verify the command was successful. 4. A check constraint is not shown in the results of a describe command. a. Go to the Object Browser b. Select the customers table. c. Click on the CONSTRAINTS tab. d. You will see your constraint here. Adding a column The client has decided that they would like a separate column for the customer’s mobile phone number. This is an optional column that will be required to store 11 digits. 1. Run the DESCRIBE command on the customers table to view its structure. 2. Task: Add column that will satisfy the clients requirements Solution : ALTER TABLE customers ADD mobile_number VARCHAR2(11); Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

8

3. Run the DESCRIBE command on the customers table to view its structure. Dropping a column The client has decided that they don’t need the mobile number column as most customers only provide a single contact number and that is already catered for with the existing phone_number column. 1. Run the DESCRIBE command on the customers table to view its structure. 2. Task: Drop the column that was created to store the mobile phone number. Solution : ALTER TABLE customers DROP COLUMN mobile_number; 3. Run the DESCRIBE command on the customers table to view its structure.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

9

Part 3 Step 3: Data Manipulation Language (DML) Use DML operations to manage database tables In this step you will populate and work with the data that is stored in the database system tables. Exercise 1 : Running a script to populate the tables. You have to consider the order of the tables when populating them. A table that has a foreign key field cannot be populated before the related table with the primary key. 1. Use the table mapping document and list the order that you would use to populate the tables. Solution : inventory_list items price_history sales_representatives sales_representatives_addresses teams customers customer_addressses orders ordered_items 2. Open the “sports data.sql” and look at the order the data is being added there, does your list match? This file has been provided for you in the interaction (sports data.zip) and must first be extracted. 3. Run the “sports data.sql” script in APEX to populate your tables 4. Check that no errors occurred when you ran the script.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

10

Exercise 2- Inserting rows to the system 1. Add a new team to the system id

name

Number_of_players

t004

Jets

discount

10

5

Solution : INSERT INTO teams (id, name, number_of_players, discount) VALUES('t004', 'Jets', 10, 5);

2. Add a new Customer with the following details to the system

ctr number

email

c02001

brianrog@hoote ch.com

First name Brian

Last name Rogers

Phone number 01654564898

Current balance -5

Loyalty card number

tem id

lc4587

Solution : INSERT INTO customers (ctr_number, email, first_name, last_name, phone_number, current_balance, loyalty_card_number) VALUES('c02001', '[email protected]', 'Brian', 'Rogers', '01654564898', -5, 'lc4587' );

3. This information violates the check constraint that the current balance must not be less than zero. Change the current balance to 50 and rerun the query. Solution : INSERT INTO customers (ctr_number, email, first_name, last_name, phone_number, current_balance, loyalty_card_number) VALUES('c02001', '[email protected]', 'Brian', 'Rogers', '01654564898', 50, 'lc4587' );

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

sre id

11

Part 3 Step 4: Data Manipulation Language (DML) Use DML operations to manage database tables In this step you will populate and work with the data that is stored in the database system. Exercise 1- Updating rows to the system 1. Run the following query to view the content of the price_history table:

SELECT start_date, TO_CHAR (start_time, 'HH24:MI:SS'), price, end_date, TO_CHAR (end_time, 'HH24:MI') FROM price_history;

2. Obl is going to update the price of the premium bat so you will need to write a query that will close off the current price by adding the system date values to the end_date and end_time fields. To run this query you will need to both match the item number and identify that the end date is null. This ensures that you are updating the latest price. Solution : UPDATE price_history SET end_date = SYSDATE, end_time = SYSDATE WHERE itm_number = 'im01101045' AND end_date is null;

3. Rerun the select statement on the price_history table to ensure that the statement has been executed. Solution : SELECT start_date, TO_CHAR (start_time, 'HH24:MI:SS'), price, end_date, TO_CHAR (end_time, 'HH24:MI') FROM price_history;

4. Insert a new row that will use the current date and time to set the new price of the premium bat to be 99.99.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

12

Solution : INSERT INTO price_history (start_date, start_time, price, itm_number) VALUES(SYSDATE, SYSDATE, 99.99, 'im01101048');

5. Rerun the select statement on the price_history table to ensure that the statement has been executed. Exercise 2: Deleting rows from the system 1. Bob Thornberry has contacted Obl to ask that the 83 Barrhill Drive address be removed from the system as he can longer receive parcels at this address. Write a SQL statement that will remove this address from the system. Solution : DELETE FROM customers_addresses WHERE id = 'ca0101';

2. Run a select statement on the customers_addresses table to ensure that the statement has been executed. Solution : SELECT * FROM customers_addresses

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

13

Part 3 Step 5: Retrieving Data Using SELECT Write and Execute SELECT statements In this step you will retrieve data that is stored in the database system by using a SELECT statement. Exercise 1: Retrieving all columns from a table. Using the SELECT * statement show all data stored in the following tables: 1. customers. Solution: SELECT * FROM customers; 2. teams. Solution: SELECT * FROM teams; 3. items Solution: SELECT * FROM items;

Exercise 2: Selecting Specific Columns 1. Display the customer number, first name, last name, email and phone number of the customers. Solution: SELECT ctr_number, first_name, last_name, email, phone_number FROM customers; 2. Display the name and number of players for each team. Solution: SELECT name, number_of_players FROM teams; 3. Display the name, description and category for every item in the table. Solution: SELECT name, description, category FROM items; Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

14

Part 3 Step 6: Retrieving Data Using SELECT Write and Execute SELECT statements In this step you will retrieve data that is stored in the database system by using a SELECT statement.

Part 1: Using Arithmetic Operators 1. Every customer has been told they can pay off their current balance over a 12 month period. Display the customer’s first name, last name, current balance and monthly payment. Solution: SELECT first_name, last_name, current_balance, current_balance/12 FROM customers; To display result to decimal places ROUND or TRUNC could be used: SELECT first_name, last_name, current_balance, ROUND(current_balance/12, 2) FROM customers; 2. Obl is considering giving a gift card to all its customers of 5.00 that can be used to reduce their current balance. Write a query that will show the customers first name, last name, customer number, current balance and the value of their balance minus the gift value. Solution: SELECT first_name, last_name, ctr_number, current_balance, current_balance-5 FROM customers; 3. What would be the problem with implementing this scheme? Solution: The current balance cannot go below zero.

Exercise 2 : Using Column Aliases 1. You previously wrote a query that display the customer’s first name, last name, current balance and monthly payment. Rewrite the query to use First Name, Last Name, Balance and Monthly Repayments as the column aliases. The aliases are to be shown exactly as described (case sensitive).

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

15

Solution: SELECT first_name AS "First Name", last_name AS "Last Name", current_balance AS "Balance", current_balance/12 AS "Monthly Repayments" FROM customers; Exercise 3: Using Literal Character Strings 1. Write a query that will display the team information in the following format: The Rockets team has 25 players and receives a discount of 10 percent. Use Team Information as the column alias. Solution: SELECT 'The ' || name || ' team has ' || number_of_players || ' players and receives a discount of ' || discount || ' percent.' AS "Team Information" FROM teams; 2. Why does the last team not show a discount? Solution: It contains a null value which is not the same as zero.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

16

Part 3 Step 7: Restricting Data Using WHERE Limit rows using WHERE In this step you will refine the data that is returned in your query by adding a WHERE clause to your SELECT statement. Exercise 1: Using the WHERE Clause. 1. Using the unique customer number in the where clause display all columns for Maria Galant. Solution: SELECT * FROM customers WHERE ctr_number = 'c01986 '; 2. Display the first name, last name and customer number for all customers who have a current balance of greater than 100. Use an appropriate alias for your column headings. Solution: SELECT first_name AS "First Name", last_name AS "Last Name", "Balance" FROM customers WHERE current_balance >100;

current_balance AS

3. Display the order id, date and time of all orders that were placed before the 28th of May 2017. Use an appropriate alias for your column headings. Solution: SELECT id AS "Order ID", odr_date AS "Order Date", TO_CHAR (odr_time, 'HH24:MI:SS') AS "Order Time" FROM orders WHERE odr_date < '28-May-2017'; Exercise 2: Range Conditions: BETWEEN Operator 1. Display the inventory id, cost and number of units using appropriate aliases for all items that have a trade cost of between 3.00 and 15.00. Solution : SELECT id AS "Inventory ID", cost AS "Cost", units AS "Number of Units in Stock" FROM inventory_list WHERE cost BETWEEN 3 AND 15; Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

17

Exercise 3: Membership Conditions: IN Operator 1. Display the inventory id, cost and number of units using appropriate aliases for all items that have 50, 100, 150 or 200 units in stock. Solution: SELECT id AS "Inventory ID", cost AS "Cost", units AS "Number of Units in Stock" FROM inventory_list WHERE units IN (50, 100, 150, 200, 250); Exercise 4: Membership Conditions: NOT IN Operator 1. Display the inventory id, cost and number of units using appropriate aliases for all items that do not have 50, 100, 150 or 200 units in stock. Solution: SELECT id AS "Inventory ID", cost AS "Cost", units AS "Number of Units in Stock" FROM inventory_list WHERE units NOT IN (50, 100, 150, 200, 250);

Exercise 5: Pattern Matching: LIKE Operator 1. Display item number and name of all items that have a name that begins with g. Use an appropriate alias for your column headings. Solution: SELECT itm_number AS "Item ID", name AS "Item Name" FROM items WHERE name LIKE 'g%'; Exercise 6 : Pattern Matching: Combining Wildcard Characters with the LIKE Operator 1. Display item number and name of all items that have a name that contain a lowercase o. Use an appropriate alias for your column headings. Solution: SELECT itm_number AS "Item ID", name AS "Item Name" FROM items WHERE name LIKE '%o%'; Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

18

Part 3 Step 8: Restricting Data Using WHERE Limit rows using WHERE In this step you will refine the data that is returned in your query by adding a WHERE clause to your SELECT statement.

Exercise 1: Using the NULL Conditions 1. Write a query that will display information for teams that don’t receive a discount in the following format: The Rovers team has 25 players and does not receive a discount. Use Team Information as the column alias. Solution: SELECT 'The ' || name || ' team has ' || number_of_players || ' players and does not receive a discount ' AS "Team Information" FROM teams WHERE discount IS NULL; 2. Write a query that will display information for only teams that receive a discount in the following format: The Rockets team has 25 players and receives a discount of 10 percent. Use Team Information as the column alias. Solution: SELECT 'The ' || name || ' team has ' || number_of_players || ' players and receives a discount of ' || discount || ' percent.' AS "Team Information" FROM teams WHERE discount IS NOT NULL;

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

19

Exercise 2: Logical Operators: AND 1. Write a query that will display the customer number, address line 1 and postal code for customers that live in the starford area of Liverpool. Use Customer Number, Street Address and Postal Code as the column aliases. Solution: SELECT ctr_number AS "Customer Number", Address_line_1 AS "Street Address", postal_code AS "Postal Code" FROM customers_addresses WHERE city = 'Liverpool' AND address_line_2 = 'Starford'; Exercise 3: Logical Operators: OR 1. Write a query that will display the customer number, address line 1 and postal code for customers that live in either starford or Liverpool in general. Use Customer Number, Street Address and Postal Code as the column aliases. Solution: SELECT ctr_number AS "Customer Number", Address_line_1 AS "Street Address", postal_code AS "Postal Code" FROM customers_addresses WHERE city = 'Liverpool' OR address_line_2 = 'Starford'; Exercise 4: Logical Operators: NOT Equal To 1. Write a query that will display the customer number, address line 1 and postal code for customers that do not live in Liverpool. Use Customer Number, Street Address and Postal Code as the column aliases. Solution: SELECT ctr_number AS "Customer Number", Address_line_1 AS "Street Address", postal_code AS "Postal Code" FROM customers_addresses WHERE city NOT IN ('Liverpool');

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

20

Part 3 Step 9: Sorting Data Using ORDER BY Use the ORDER BY Clause to Sort SQL Results In this step you will sort the order of the data that is returned in your query by adding an ORDER BY clause to the end of your SELECT statement. 1. Display the team name and number of players alphabetically in order of team name. Use an appropriate alias for your column headings. Solution: SELECT name AS "Team Name", number_of_players AS "Number of Players" FROM teams ORDER BY name 2. Display the team name and number of players in descending order of number of players. Use an appropriate alias for your column headings. Solution: SELECT name AS "Team Name", number_of_players AS "Number of Players" FROM teams ORDER BY number_of_players DESC; 3. Display the team name and number of players alphabetically in order of team name. Use Team Name for the name alias and Players for the number of players. Sort the output in descending order of name using the alias in the ORDER BY clause. Solution: SELECT name AS "Team Name", number_of_players AS "Number of Players" FROM teams ORDER BY "Team Name" DESC;

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

21

Part 3 Step 10: Exercise 1 : TOP-N-ANALYSIS 1. The customers are numbered sequentially with each new customer being assigned a higher customer number. Use TOP-N-ANALYSIS to only show the First and last name of the first three customers. Show the customers first and last name in the same column using Customer Name as the column alias. Solution: SELECT ROWNUM As "Order of Membership", first_name || ' ' ||last_name AS "Customer Name" FROM customers WHERE ROWNUM