Difference between revisions of "Oracle:Sample Database"
| (46 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | [[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> [[Oracle_Getting_Started|Getting Started]] >> SQL*Plus | ||
| + | |||
== Copying the Sample Database == | == Copying the Sample Database == | ||
The sample database used in this workbook needs to be set-up first by the following steps: | The sample database used in this workbook needs to be set-up first by the following steps: | ||
| − | === Download the setup script === | + | === 1. Download the setup script === |
<ul> | <ul> | ||
| − | <li>Go to the Oracle web server: http:// | + | <li>Go to the Oracle web server: http://ora-srv.wlv.ac.uk</li> |
| − | <li>Look for the link called sqlload.sql setup script</li> | + | <li>Look for the link called <i>sqlload.sql setup script</i></li> |
| − | <li>Right click on this and depending on your browser, select | + | <li>Right click on this and depending on your browser, select <i>Save Link As....</i>, <i>Save Page As....</i> or <i>Save Target As….</i></li> |
| − | <li>Save the file to your “Documents” Personal storage folder (U:\ drive)</li> | + | <li>Save the file to your “Documents” Personal storage folder (U:\ drive), or somewhere easily accessible if using your own computer.</li> |
</ul> | </ul> | ||
| − | + | Note, it is advisable not to save the file in any folders, or the Desktop to begin with, since that makes it hard to access via Oracle. Once the script is run, you can move the script somewhere else. | |
| − | + | === 2. Run the setup script === | |
| + | |||
| + | Once you have logged into Oracle successfully (if not, see: [[Oracle:SQLPlus|using SQL*Plus]]), the format for running a file is: | ||
| + | |||
| + | @ drive_name:\folder_name\filename | ||
| + | |||
| + | For example, if on a University PC and have saved the file to your “Documents” folder, type: | ||
@ u:\sqlload | @ u:\sqlload | ||
| Line 20: | Line 28: | ||
and press RETURN. | and press RETURN. | ||
| − | If you have saved your file somewhere else, for example, a memory stick, then replace the u:\ with the location of your file, including any subfolders, e.g., e:\oracle\sqlload | + | If you have saved your file somewhere else, for example, a memory stick, or on your own computer, then replace the '''u:\''' with the location of your file, including any subfolders, e.g., '''e:\oracle\sqlload''' |
This sets up your database on the central server and this same database can be accessed from the MI labs, other University labs and from home. | This sets up your database on the central server and this same database can be accessed from the MI labs, other University labs and from home. | ||
| + | |||
| + | <font size="2" color="red">Note, this command only needs to be executed <b>once</b>. If you re-run it, the command will drop the tables you previously created, thereby losing any changes you may have made to them.</font> | ||
| + | |||
| + | === 3. List your tables === | ||
| + | |||
| + | To check that your tables have been setup correctly, type: | ||
| + | |||
| + | select table_name from user_tables; | ||
| + | |||
| + | If the system responds with: | ||
| + | |||
| + | TABLE_NAME | ||
| + | -------------------------------------------------------------------------- | ||
| + | CUSTOMER | ||
| + | DEPT | ||
| + | EMP | ||
| + | SALGRADE | ||
| + | |||
| + | Then your tables are now ready to use. If it returns with: | ||
| + | |||
| + | no rows selected | ||
| + | |||
| + | It means something went wrong and you need to carry out the previous steps again. | ||
| + | |||
| + | user_tables refers to a data dictionary table, which is a set of tables which allows you to query what objects you own. See <section 22> for further information on the Data Dictionary. | ||
| + | |||
| + | === Sample data set === | ||
| + | |||
| + | To see what the data looks like, refer to this page: [[Oracle_Sample_Data|Oracle Sample Dataset]] | ||
| + | |||
| + | == Next Step == | ||
| + | |||
| + | Type in some [[Oracle:Initial_Queries|initial queries]] | ||
Latest revision as of 09:31, 29 September 2020
Main Page >> Oracle and SQL >> Workbook >> Getting Started >> SQL*Plus
Copying the Sample Database
The sample database used in this workbook needs to be set-up first by the following steps:
1. Download the setup script
- Go to the Oracle web server: http://ora-srv.wlv.ac.uk
- Look for the link called sqlload.sql setup script
- Right click on this and depending on your browser, select Save Link As...., Save Page As.... or Save Target As….
- Save the file to your “Documents” Personal storage folder (U:\ drive), or somewhere easily accessible if using your own computer.
Note, it is advisable not to save the file in any folders, or the Desktop to begin with, since that makes it hard to access via Oracle. Once the script is run, you can move the script somewhere else.
2. Run the setup script
Once you have logged into Oracle successfully (if not, see: using SQL*Plus), the format for running a file is:
@ drive_name:\folder_name\filename
For example, if on a University PC and have saved the file to your “Documents” folder, type:
@ u:\sqlload
and press RETURN.
If you have saved your file somewhere else, for example, a memory stick, or on your own computer, then replace the u:\ with the location of your file, including any subfolders, e.g., e:\oracle\sqlload
This sets up your database on the central server and this same database can be accessed from the MI labs, other University labs and from home.
Note, this command only needs to be executed once. If you re-run it, the command will drop the tables you previously created, thereby losing any changes you may have made to them.
3. List your tables
To check that your tables have been setup correctly, type:
select table_name from user_tables;
If the system responds with:
TABLE_NAME -------------------------------------------------------------------------- CUSTOMER DEPT EMP SALGRADE
Then your tables are now ready to use. If it returns with:
no rows selected
It means something went wrong and you need to carry out the previous steps again.
user_tables refers to a data dictionary table, which is a set of tables which allows you to query what objects you own. See <section 22> for further information on the Data Dictionary.
Sample data set
To see what the data looks like, refer to this page: Oracle Sample Dataset
Next Step
Type in some initial queries
