This page has 0 threads | Add post

Exercises

Many people around the world enjoy and benefit from our exercises. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

You can find other training resources for the subject of this exercise here:

Classes in Oracle

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Software ==>	Oracle (35 exercises)
Topic ==>	Using Oracle (1 exercise)
Level ==>	Average difficulty
Subject ==>	Oracle training

Follow this exercise to install the Wise Owl music database schema in Oracle

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file . If you want to download the SQL Server database that this yields:

Go into SQL Server Management Studio;
Open the SQL file you've just unzipped (you can press CTRL + O to do this); then
Execute this script.

This will generate the database that you'll need to use in order to do this exercise (remember to refresh your list of databases to see it!). Note that any files provided are only to be used for exercises published on this website, and may not be reused or distributed in any form without the prior written permission of Wise Owl.

Many of our Oracle SQL exercises depend on our music database of artists, albums, tours and shows. Here's how to install it!

This script and database are to be used only as an aide for individuals doing exercises published on the Wise Owl website or included in a Wise Owl course. Neither this script nor the database it generates may be distributed in whole or in part in any format whatsoever without the prior written permission of Wise Owl Business Solutions Ltd (this prohibition specifically prohibits the use of our exercises to train others).

Note that you may not need to follow all of the steps below, which are included for completeness. The steps assume that you have installed Oracle and also SQL Developer (we are on version 26.2) and that you have already created a user with system access:

Here we've already created a user called FREEPDB1 - SYSTEM with administrator access.

If you haven't got one of these set up it might be time to contact your friendly IT department!

Creating a music user and schema

Start by right-clicking on your system user to create a new worksheet:

Creating a new worksheet for a system user

There are many ways to create a new SQL worksheet this - this is just one!

Type in the following instructions:

-- create a new user, giving a password

CREATE USER music IDENTIFIED BY WiseMusicOwl;

-- give this user sufficient rights

GRANT

CREATE SESSION,

CREATE TABLE,

CREATE VIEW,

CREATE SEQUENCE,

CREATE PROCEDURE

TO music;

-- don't restrict user's space

ALTER USER music

QUOTA UNLIMITED ON users;

Press F5 to run these instructions. They should create a new music user, and also the associated schema that you'll need to hold your data.

Creating a Connection to your Schema

You now need to create a connection to your music schema:

Click on the green plus sign and choose to create a new database connection.

Fill in the dialog box:

Connection dialog box for music database

The password here will be WiseMusicOwl, since this was the one we assigned to this user when we created them earlier. It's a good idea to tick the Save Password box so you don't have to remember this. We're putting the schema within the FREEPDB1 top level pluggable database (as it's called in Oracle).

Generating the Database

You should now be able to create a worksheet for this connection:

Creating a worksheet for music connection

Right-click on the new music connection and choose to open an SQL worksheet for it.

Open the file in the folder referenced at the start of this exercise in an application like NotePad, and copy all of the text in it:

Start of the query to generate music database

The start of the (long) text - press Ctrl + A to select it all.

Paste this into your new blank query window:

The start of the script.

Press F5 to run this query. SQL Developer should run through all of the statements, executing each in turn, to create all of the tables in the music database.

Checking this has worked

To check this has worked, expand the music connection to see its contents:

You should see a list of tables like this.

You're now ready to use this database for any Oracle SQL exercise!

You can find other training resources for the subject of this exercise here:

Classes in Oracle

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This page has 0 threads

Add a new post

EXERCISE TOPIC

ORACLE EXERCISES

ORACLE EXERCISES

Follow this exercise to install the Wise Owl music database schema in Oracle

Creating a music user and schema

Creating a Connection to your Schema

Generating the Database

Checking this has worked

Head office

London

Manchester