Database Tutorial

Course Tutorial Site

Site Admin

Lab #1: MySQL Monitor

without comments

Lab #1: Oracle Assignment

After you copy and start the Fedora image, you need to log into the instance as the student user. Login as the student user, as shown in the following screen shots:

  1. [2 points] Open and learn the basics of using the Fedora Gnome interface:

Instruction Details ↓

  1. You should see the following screen when you launch the Fedora environment. You should click the space bar inside the window to move to the next step.

FedoraLogin1

  1. You should see the following screen after clicking the space bar. Enter the password for the student user and click the return key.

FedoraLogin2

  1. It’s possible that the Fedora instance may apply software updates. Click any update message like the one below and it disappears.

FedoraLogin3

  1. Click on the Activities menu in the upper left hand corner and it displays a search box with a list of applications on the left. The nine dots icon at the bottom of the list opens a window with available applications. Click on the nine dots icon and you see the following image.

FedoraLogin4

  1. Click on the nine dots icon and you see the following image.

FedoraLogin5

  1. The list of applications displays a list of radio buttons on the right side of the display. Click on the second radio button and you see the following image.

FedoraLogin6

  1. The list of applications displays the Utility icon as the right-most icon on the last row. Click on the Utilities icon to display the following image.

FedoraLogin7

  1. The list of utility applications displays the Terminal icon as the right-most icon on the last row. Click on the Terminal icon to display a Terminal window, or alternatively click in the search box and enter terminal and click the return key, which displays the following image.

FedoraLogin8

  1. After clicking on the Terminal icon or typing the terminal string and clicking the return key, you will see the following window:

FedoraLogin9

After you launch the Terminal, you can do the following steps as the student user. If you expand the Instruction Details, you can see the individual subtasks:

  1. [2 points] Navigate to the lab1 directory from the student home directory:

Instruction Details ↓

Initially, you’ll see a prompt with the user name, an “@“ symbol, and the machine name inside square brackets followed by a “$“ symbol. This type of prompt is configured by the user’s .bashrc (Bash Shell resource file). You can determine the user’s home directory by using the pwd command, which means the Present Working Directory command.

[student@localhost ~]$ pwd

It will display:

/home/student

You can list the contents of the directory (also known as a folder) with the ls command, which is an abbreviation list.

[student@localhost ~]$ ls

It will display the following directories in blue text:

Code     Documents  Music             Pictures  Templates  workspace
Data     Downloads  NetBeans          Projects  Public    Videos
Desktop  eclipse    oradiag_student   Stage     vmware

You change directories with the cd command, which is stands for change directory. You can change to a subdirectory of the current directory with the following syntax. You should note that there isn’t a forward slash before the first level subdiretory.

[student@localhost ~]$ cd Data/cit225/mysql/lab1

Then, issue a pwd command to ensure you’re in the right place:

[student@localhost ~]$ pwd

It will displays:

/home/student/Data/cit225/mysql/lab1

You can list the contents of the /home/student/Data/cit225/mysql/lab1 directory with the ls command. You should see the following file:

[student@localhost ~]$ ls

It will display the following file in green text:

apply_mysql_lab1.sql

  1. [2 points] Connect to mysql as the student user:

Instruction Details ↓

You have several ways to connect to the MySQL database using the mysql command. You can provide the user name only or the user name and a password, or a user name, password, and database name.

Before you connect with the mysql program, you should verify that it’s in your environment’s $PATH variable. You can do that with the which command, like this:

[student@localhost ~]$ which mysql

It will display:

/usr/bin/mysql

You can connect to the MySQL instance with the mysql utility:

[student@localhost ~]$ mysql -ustudent -pstudent -Dstudentdb

It will display:

Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.25 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

  1. [2 points] Run the apply_mysql_lab1.sql script, which runs three scripts: (a) the cleanup_mysql.sql script, (b) the create_mysql_store.sql script, and (c) the seed_mysql_store.sql script.

Instruction Details ↓

When you run the sqlplus command from a directory, that directory becomes the home directory. Inside the MySQL Monitor environment, you can access any file in the local directory by simply prefacing it with an “\.“ symbol or the source keyword. If the file doesn’t exist it raises and exception.

You can’t check for the existence of the file from inside the MySQL Monitor. That means you should check before you launch the MySQL Monitor:

You call a file in mysql like this:

mysql> \. apply_mysql_lab1.sql

or, like this with the source keyword:

mysql> source apply_mysql_lab1.sql

Either will display:

+-----------------+--------------------------+---------+----------------+-------------+
| full_name       | TITLE                    | PRODUCT | check_out_date | return_date |
+-----------------+--------------------------+---------+----------------+-------------+
| Brian Winn      | RoboCop                  | XBO     | 2015-09-10     | 2015-09-15  |
| Brian Winn      | The Hunt for Red October | DVD     | 2015-09-10     | 2015-09-15  |
| Doreen Vizquel  | Camelot                  | VHS     | 2015-09-10     | 2015-09-15  |
| Doreen Vizquel  | I Remember Mama          | VHS     | 2015-09-10     | 2015-09-15  |
| Ian M Sweeney   | Cars                     | XBO     | 2015-09-10     | 2015-09-15  |
| Meaghan Sweeney | Hook                     | VHS     | 2015-09-10     | 2015-09-15  |
| Oscar Vizquel   | Star Wars I              | DVD     | 2015-09-10     | 2015-09-15  |
| Oscar Vizquel   | Star Wars II             | DVD     | 2015-09-10     | 2015-09-15  |
| Oscar Vizquel   | Star Wars III            | DVD     | 2015-09-10     | 2015-09-15  |
+-----------------+--------------------------+---------+----------------+-------------+
9 rows in set (0.00 sec)

  1. [2 points] Query the data dictionary to find the tables created by the create_oracle_store.sql script.:

Instruction Details ↓

Oracle maintains the data dictionary inside the sys schema but it provides access to narrow views of the data dictionary in the user_ views. You do need to filter out tables created to support the APEX installation. Here’s how you see the tables created in the student schema of an Oracle database instance while excluding the APEX tables:

mysql> show tables;

It will display:

+---------------------+
| Tables_in_studentdb |
+---------------------+
| address             |
| common_lookup       |
| contact             |
| contacts            |
| current_rental      |
| item                |
| member              |
| rental              |
| rental_item         |
| street_address      |
| system_user         |
| telephone           |
+---------------------+
12 rows in set (0.00 sec)

Written by michaelmclaughlin

September 18th, 2015 at 12:49 am

Posted in