Oracle-DSSD - Guide to oracle software PDF

Preview

Summary

Guide to oracle software...

Description

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

This booklet introduces you to SQL (Structured Query Language) and the use of SQL in Oracle. The booklet is written in 5 parts, this is part 1 and contains 6 exercises and a consolidation exercise. Things you need to know:  Foundation SQL is portable – if it works in Oracle, it should work in SQL Server and MySQL  SQL is a redundant language – there is more than one way of doing things  When you write a query, as long as the syntax (the structure of the query) is correct, the Cost Based Optimiser will estimate the most efficient way of carrying out the query. There are some limitations to this  On Blackboard, there is an Oracle manual. This lists key commands and you can use the manual for quick reference  Exercises 1 – 8 cover the minimum you need to know on this module. Exercises 9 -10 are extension material which you should complete to get a better grade and out of interest Finally: Never spend hours on an SQL element. If it does not run, it may be something as simple as a misplaced comma. Make a note of the problem and ask in your next tutorial or lecture session Good luck! I hope you enjoy working with SQL Clare Stanier

© Clare Stanier October 2013

1

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________ EXERCISE 1 GETTING STARTED: THIS EXERCISE SHOWS YOU HOW TO LOG ON TO ORACLE AND HOW TO CREATE A SIMPLE TABLE, ENTER DATA AND USE A SCRIPT FOR DATA.......................3 EXERCISE 2 STORING YOUR DATA: THIS EXERCISE LOOKS IN MORE DETAIL AT TABLE CREATION AND SETTING PRIMARY AND FOREIGN KEYS........................................................................8 EXERCISE 3 RETRIEVING DATA 1: THIS EXERCISE INTRODUCES QUERIES USING SIMPLE SELECT, ORDER BY AND WHERE.............................................................................................................. 10 EXERCISE 4 RETRIEVING DATA 2: THIS EXERCISE INTRODUCES AGGREGATE FUNCTIONS – SUM, MAX, MIN, COUNT.............................................................................................................................. 13 EXERCISE 5 RETRIEVING AND MANAGING DATA2 : THIS EXERCISE INTRODUCES ARITHMETIC OPERATIONS AND UPDATE........................................................................................................................ 13 EXERCISE 6

RETRIEVING DATA 3: THIS EXERCISE INTRODUCES GROUP BY..............................14

CONSOLIDATION.......................................................................................................................................... 14

© Clare Stanier October 2013

2

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

Exercise 1 Getting Started: This exercise shows you how to log on to Oracle and how to create a simple table, enter data and use a script for data You can download a free version of Oracle at Oracle.com. We advise against doing that at this stage. Oracle is available on virtually every machine and the application will use a lot of memory. If you do decide to download Oracle do not download the Oracle enterprise version! Your machine is unlikely to cope. 1.1Getting Started  Log on to Developer Location varies depending on installation but will usually be available via Programming tools –Oracle Application Development tab

Select SQL Developer and you will see this window

You need to understand a bit about how Oracle works to understand what you are doing next. The university has an Oracle installation with a student space. You have your own section of the Oracle student space (stora). When you log on , you need to tell the system who you are, how to connect to the Oracle instance and which host name to use. The first time you use it, Oracle will be slow to load.

Your password will be the same as your user name. It is NOT your university network login

© Clare Stanier October 2013

3

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

If you have problems connecting, check that: -you have spelt the hostname correctly/ you have checked service name/your password is correct Then click on Test and you should see a success message 1.2

Doing Something You will then see a page that looks something like this:

When you work with Oracle, you are working as a developer. You have a lot more control than you would have with Access but you also have to make decisions.

1.2.1

For the assignment, you will need a customers table so we will create a simple version here.

1.2.2

Right click on table and select new table

© Clare Stanier October 2013

4

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

1.2.3 Give the table a name and make sure you click on Advanced so that you see this screen

It will save you a lot of time eventually if you get this straight now.

o To add a column, click on o We are only working with simple data types o For string data types (anything which can hold letters as well as numbers), just set the size o For numeric types, precision means the number of digits – If precision is 4 then 1000 is valid but 10000 is not. Scale means how many decimal points. Precision 4, scale 2 means 11.11 is valid but 111.11 is not o Cannot be null - only tick this if you REALLY want to ensure that this field can never be blank. Ticking not null for everything causes a lot of problems 1.2.4 Choosing sensible data types, create a table which has Customer_ID (this field is usually numeric) Customer_Fname © Clare Stanier October 2013

5

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

Customer_Lname Customer_DateJoined 1.2.5 When you have created all the fields, click on Primary Key and set Customer_ID as the Primary key

1.2.6 Click OK. Well Done; you have created your first Oracle table

1.3

Inserting Data We do not want you to spend your time keying in data. When we start the SQL exercises, we will give you a script. When you create test data for the assignment only create as much data as you need to test the database.

–

1.3.1 You can insert data by keying it in: 

Double click on the table name and a window that looks like this will appear (my table is not the same as yours)



Click on the data tab and you will see this screen

Click on the green plus sign to insert a row and then enter some data (2 rows is enough) When you have entered the data you click on your data may be lost. © Clare Stanier October 2013

to commit the data.. If you do not do this, 6

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

1.3.2A real life database may hold several million records. Single data entry is very timeconsuming. We are now going to script data

1.3.4 At the top of the screen you should see a tab called in my example untitled1 SQL; in your screen it will have the same name as your database

Click on the tab to see this window

1.3.5

We are now going to run a script to create some tables and enter some data. Go to the Oracle Samples Script on Blackboard. Open the script and copy ALL the text (Ctl+a; ctrl +c).

Paste the data into the SQL window (Ctrl +v) Click on this icon And watch what happens. Developers reuse code. If one table is very similar to another, you don’t have to recreate the whole thing each time, you just adapt your code or scripting language. We will show how to do this later in the module. It will save you a lot of time

© Clare Stanier October 2013

7

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

when it comes to creating your database for the assignment and it is what you would do as a professional level developer

© Clare Stanier October 2013

8

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

Exercise 2 Storing your Data: this exercise looks in more detail at table creation and setting Primary and Foreign keys In exercise we will create some more professional looking tables. 2.1

Create Table Right click on Tables and select new. You will see this window. Make sure you tick the advanced box

2.2

Create a Table Student with StudentID as the PK. PKs are usually numeric and usually integer so there will be no decimal points (SCALE is used to set the number of decimal points you want)

2.3

Now click on Primary Key and you will see this screen

Select StudentID and move it into the selected colums © Clare Stanier October 2013

9

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

Click OK and you have set the PK You

are going to use this Pk as a foreign key so make a note of the data type and size

2.4

Now create another table – Next of kin. Create the table with NOK as the PK and include a field studentiD which has the same date type and size as studentID in the student table

2.5

You are now going to set the FK and set up the relationship between Student and NOK

   

Click ADD and the system gives the FK a name Check that you are referencing the correct table Check that you are referencing the correct field in the correct table Check it all matches

Then look at the bottom right of the screen Set this to on delete CASCADE © Clare Stanier October 2013

10

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

This is a little dangerous because it means that when you delete the PK, the system will automatically delete all the FKs as well. If you select restrict instead, the system will not let you delete the PK if there are FKs which match to the PK. 2.6 Practise this by running the Samples Script and setting appropriate FKs on the Supplier and Warehouse Table

© Clare Stanier October 2013

11

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

Exercise 3 Retrieving Data 1: this exercise introduces queries using simple SELECT, Order By and WHERE

3.1

SELECT The green icon executes a statement. The icon with a document attached executes a script file. The script file command always works. icon clears the screen

The SELECT keyword is the command which tells the system to search for something. You SELECT data from the table (you are actually carrying out a relational algebra operation but the system handles this for you) In the window, key in SELECT * FROM WAREHOUSE and you will see the results in the window below The * symbol is an instruction to the system to show all fields. To find a specific element, use the fieldname SELECT BINNAME FROM WAREHOUSE This is Ok and works but is not ‘professional’. A better way to write the query is always to use the table name – so there is no ambiguity about which table the system should refer to SELECT WAREHOUSE.BINNAME FROM WAREHOUSE: This is a lot of typing so we usually make things shorter SELECT W.BINNAME FROM WAREHOUSE W The final “W” tells the system that you are using W as a synonym (identifier) for the table Create the following queries:    

3.2

Find all items in the Item table Find suppliername and area from the supplier table (separate fields with commas) Find everything in the warehouse table Find purchaseprice in the Item Table

Order By

© Clare Stanier October 2013

12

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________ This command allows you to order the data. By default, it orders ascending. If you wish to order descending, you key in ORDER BY Desc Order By is ALWAYS the last element in a select statement

Select all data from the warehouse table. Order by binname WAREHOUSE_REF BINNAME BINLOCATION BINCAPACITY F ------------- -------- -------------- ----------- 222233 LIGHTING A10 200 N 222234 DOMESTIC A11 100 Y 222235 PLUMBING A4 300 Y 222236 OFFICE A5 150 Y

Create the following queries  Select everything from the item table, order by saleprice  Select everything from the item table, order by purchase price  Select warehouse_ref, binname from warehouse, order by binname

3.3

WHERE So far, all you have done is select. WHERE allows you to use Criteria for example: SELECT * FROM WAREHOUSE WHERE BINNAME ='DOMESTIC'

Note the quote marks around DOMESTIC and that DOMESTIC is in uppercase. Try without the quote marks and see what happens

Now you can create a whole range of queries.     

Create a query which finds all items in the item table called DESK Create a query which finds all Suppliers in Oxford Create a query which find all items which cost less than 15 (use 20) Create a query which finds all items in the item table with a saleprice equal to 15 (sometimes a query will not return any data. This is a useful piece of information)

© Clare Stanier October 2013

13

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________ 3.4 Consolidation The client has asked for the following information. Work out how to design the queries to get the right information.

   

I want to know the name and saleprice of any item which has a delivery charge which is more than £10 I want to know the names of all suppliers ordered by area I want to know all the information we have about items in the warehouse I want to know the purchase price and saleprice of every item, ordered by sale price descending



© Clare Stanier October 2013

14

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

Exercise 4 Retrieving Data 2: this exercise introduces aggregate functions – Sum, Max, Min, Count 4 A function is a piece of code which goes something. An aggregate function is a function which works over groups of data – for example, counting the amount of items or adding up the total value of the inventory. We are using 4 functions in this exercise    

4.1

Max returned the highest or biggest value Min returns the lowers Count counts the number of values Sum adds up values

MAX To find a maximum value SELECT MAX(SALEPRICE) FROM ITEM;  Use Max to find the largest deliverycharge

4.2

COUNT To find out how many items there are in the item table SELECT COUNT(ID) FROM ITEM;  Use Count to count the number of suppliers in the supplier table

4.3

SUM

Gives a total SELECT SUM(SALEPRICE) FROM ITEM; Use sum to add up the total value of the items in the item table using purchase price

© Clare Stanier October 2013

15

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

Exercise 5 Retrieving and Managing Data2 : this exercise introduces arithmetic operations and UPDATE You can use multiplication (*), division(/) and subtraction (-) with functions. 5.1

Suppose you are working out profit margins and you want to see what happens if purchase prices increase by 10% Select SUM (Purchaseprice)*1.1 from item Why *1.1? * here is the multiplication symbol. 10 x 1 = 10. 10x 1.10 is a 10% increase



Use this approach to see what happens to your income if you increase the total saleprice by 50%

IMPORTANT !! All you are doing here is changing the way you see the data. These changes are not saved to the database 5.2

This time we will make permanent changes to the database and save the changes The UPDATE command allows you to change values. You instruct the system to update the table and then set the value to the value you want. UPDATE ITEM SET NAME = 'COMPUTER_DESK' WHERE ID = 4123   

Use update to change the name ‘SINK’ to ‘SHOWER’ Use Update to change the purchaseprice of TAP to £50 Use Update to change deliverycharge to £100 where the deliverycharge is £20

© Clare Stanier October 2013

16

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

Exercise 6

Retrieving Data 3: this exercise introduces Group By

Group by is used to select by category. GROUP BY creates an intermediate table which is then queried to produce the result set. SELECT COUNT(ID), NAME FROM ITEM GROUP BY NAME

6.1

When using group by, all data items in the select clause must appear in the group by clause except aggregate functions i.e. Max, Min, Count, Sum and AVG (which we have not looked at yet). If you see the error message: ORA-00979: not a GROUP BY expression Look at the items in the select clause

Try this query SELECT COUNT(ID), warehouse_ref FROM ITEM GROUP BY warehouse_ref

 Count the number of items in the item table grouped by supplier  Count the number of items in the item table grouped by delivery charge  Count the number of items in the item table grouped by delivery charge and use order by to order the charges in ascending order

© Clare Stanier October 2013

17

ORACLE Introduction AUTUMN 2013 _______________________________________________________________________________________

CONSOLIDATION



 Find the cheapest item in the item table  Find the most expensive item in the item table  Select all the data from the item table and group by the name of the item  Select all the data from the item table and group by delivery charge Update the delivery charge so that delivery charge is set to 300 (the result of this might not be what you expect; we look at this in part 2)

© Clare Stanier October 2013

18...