DBM 405 Lab 1 SQL Review Advanced Database Oracle

Description

DBM 405 Lab 1 SQL Review Advanced Database Oracle

Scenario/Summary
The purpose of the first week’s lab is to review the SQL language, and familiarize you with the database example that we will use in labs through this course. If you have not done it yet, please download the MovieRentals.SQL file to your computer from Doc Sharing. This script file will need to be run once logged into your Oracle user account to create the tables and data that will be used for this lab and subsequent labs in the course.
Setting up your environment:
Before attempting Lab 1, you need to be sure you have read through the SQL*Plus tutorial which can be found in Doc Sharing as well as under the SQL*Plus Tutorial tab in Week 1. This tutorial describes the functionality of the editor and will step you through the process of setting up and using the SQL*Plus in the iLab environment so that it will best serve your needs for the remainder of the labs required for this course. Once you have logged into Oracle through iLab then execute the MovieRental.SQL script and create the tables and data for the lab.
For the lab, you will need to create a script file containing the eight queries that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the query and result set. Spool your output to a file named with your last name plus lab 1 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab1.txt. Submit both the spooled output AND the script file for grading of the lab.

LAB STEP
Step 1:
Within SQL*Plus, list names of the tables that you have created whose name starts with MM (Hint: use data dictionary view USER_TABLES).

Step 2:
Use DESCRIBE (in short: DESC) command in SQL*Plus for each of these tables to show columns and their datatypes.

Step 3:
Use SELECT * command to display all data from each of the tables in the MoreMovies schema. Make sure that the LINESIZE and PAGESIZE have large enough values, and that you format columns so that the report looks good. You should end up with five queries and result sets.

Step 4:
Using the mm_movie and mm_movie_type tables, write a query that will list all movie categories together with the count of movies in each category. Give the column with the count in it a meaningful name such as IN STOCK.

Step 5:
Using the mm_movie and mm_rental tables, write a query that will list titles and checkout dates for all movies that were signed out by Wild Coyote (MEMBER_ID=13).

Step 6:
Using the same two tables used in Step 5, write an SQL sub-query that will list all movies (movie ids and titles) of all movies that have never been rented.

Step 7:
Using the mm_member and mm_rental tables, write a query that will list all members (member ID, first name, and last name) and the number of movies they have rented, for all members who have rented at least one movie. Order the result set so that it shows the largest number of movies rented as the first row.

Step 8:
Write a query that will display the largest number of movies rented by one member and that member’s name. Give the output column a meaningful name such as MAXIMUM NUMBER.

Step 9:
Using the mm_member and mm_rental tables, write a query that will display member ID, last name, first name, and the number of movies rented for each member. Give the column with the number of movies rented a meaningful name such as NUMBER RENTED.

Step 10:
Using the mm_member, mm_movie and mm_rental tables, write the query that will prepare a report that shows who rented which movie. Use member names (first and last) and movie title rather than the corresponding IDs. Order the report by member names, and for a single member by the movie titles.

This concludes the Lab for Week 1.