Search results for 'SELECT'

DBM 405 Lab 2 Simple PL/SQL Applications Advanced Database Oracle

Scenario/Summary
The purpose of this week's lab is to work with basic PL/SQL syntax to create an anonymous block of code. In the lab, you will be using SQL*Plus to modify one of the tables in the MovieRental schema and then write a simple block of code to update the table with some new data and then execute the code in SQL*Plus. As an additional task in the lab, you will be asked to modify the existing PL/SQL block of code given to you to add exception handling and then execute it in SQL*Plus. Both of these concepts will help enforce the material covered in this second week.
For the lab, you will need to create a script file containing the PL/SQL code 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 PL/SQL block code and output from Oracle after the block of code has executed. To successfully test the code in Step 3, you will need to copy/paste your code into SQL*Plus for each movie ID as you change the value for the host variable. Spool your output to a file named with your last name plus lab 2 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab2.txt. Submit both the spooled output AND the script file for grading of the lab.

LAB STEP
Step 1:
As business is becoming strong and the movie stock is growing for More Movie Rentals, the manager wants to do more inventory evaluations. One item of interest concerns any movie for which the company is holding $75 or more in value. The manager wants to focus on these movies in regards to their revenue generation to ensure the stock level is warranted. To make these stock queries more efficient, the application team decides that a column should be added to the MM_MOVIE table named STK_FLAG that will hold a value '*' if stock is $75 or more. Otherwise, the value should be NULL. Add the new column to the MM_MOVIE table as a CHAR data type.
Execute a DESC MM_MOVIE on the table both before you add the new column and after the column is added.
Note: Since this is code will be in your script file, you will need to comment it out after the first time you have execute the ALTER TABLE statement successfully to avoid getting errors each additional time your script file is run.

Step 2:
Create an anonymous block of PL/SQL code that contains a CURSOR FOR loop to accomplish the task described above in Step 1. Your loop will need to interrogate the value (using an IF statement) found in the movie_qty field of the cursor loop variable to see if it is >= 75. If this is true then you will need to update the new column in the table with an '*' WHERE CURRENT OF the table. If the quantity is not >= 75 (the ELSE side of the IF statement) then update the new column with a NULL.
Execute a SELECT * from MM_MOVIE both before and after you execute the new PL/SQL block of code to show that the process works.

Step 3:
Here is a block that retrieves the movie title and rental count based on a movie ID provided via a host variable.
SET SERVEROUTPUT ON
VARIABLE g_movie_id NUMBER
BEGIN
:g_movie_id := 4;
END;
/
DECLARE
v_count NUMBER;
v_title mm_movie.movie_title%TYPE;
BEGIN
SELECT m.movie_title, COUNT(r.rental_id)
INTO v_title, v_count
FROM mm_movie m, mm_rental r
WHERE m.movie_id = r.movie_id
AND m.movie_id = :g_movie_id
GROUP BY m.movie_title;
DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count);
END;
/
Modify the block of code to add exception handlers for errors that you can and cannot anticipate. You will need to execute the entire code listing shown above each time you wish to test it by changing the value of :g_movie_id for each test.
Once finished, test your exception handling by running the modified block for the following values of :g_movie_id. Be sure that you can capture the value in the :g_movie_id host variable.
•    12 - normal output will display title and number of rentals
•    13 - exception - there is no movie ID for 13
•    1 - exception - Movie with ID 1 has never been rented

This concludes the Lab for Week 2.

DBM 405 Lab 7 Study Case front-end GUI

Scenario/Summary
The More Movies company has hired you to redesign a database system for them that can facilitate the process of renting out and returning movies.
They already have an Oracle database that stores information about movies, members who rent the movies, and the rentals. This is the database that you already have become familiar with and the one which includes tables: MM_MOVIE, MM_MOVIE_TYPE, MM_MEMBER, MM_RENTAL, and MM_PAY_TYPE. The machine on which this database is running has both the server and client Oracle9i software installed on it. Every night, a clerk updates data to account for the day's activities, and periodically the reports are run to summarize business, show renting trends, etc. Access to the database is accomplished using a SQL*Plus environment that is very similar to the iSQL*Plus that you know from the previous database course. This business process worked okay for as long as More Movies stayed a very small business.
However, the company has grown substantially, expanding its operations to more movie selection and more members, and consequently, it has moved to a larger location. It occupies a two-story shop now. It became very impractical to record rentals at the end of the day. They also do not want to rely on clerks knowing any SQL programming in order to record updates and run reports.
In short, there is a need for a more convenient database system. The machine on which the database is currently running is powerful enough to host the database server. The database should be accessible from four checkout stations that process renting out and returning movies. This system should have an easy-to-use graphical user interface access.
For the lab, you will be creating several documents to be submitted for the lab. Be sure that you save the documents with your last name and lab7 in the file name. Place all documents into a single ZIP file and submit for grading.

LAB STEP
Step 1:
Describe what software you propose to use to develop the front-end GUI application for the new system. Be sure to justify your choice. Keep in mind portability, ease of use, scalability, and ability to update. What other options have you considered?

Step 2:
In setting up the servers and environment, do you propose to use middleware? If so, what kind, and where would you deploy it?

Step 3:
Provide a system diagram of the proposed system. Be sure to include such things as servers (application and database), user clients, and any other special pieces to the puzzle that you might think of.

Step 4:
Provide a detailed design of the GUI screen that facilitates renting out and returning movies. For every button, or other component that provides reaction to user's events, give detailed pseudocode. Also, clearly indicate where you would use any of the PL/SQL code that you developed for the labs in this course. If the application platform you have selected does not support PL/SQL then describe how you would take the processing developed in the procedures and functions and incorporate it into the system.

This concludes the Lab for Week 7.

POS 410 Week 2 Table Queries SQL for Business

Create two tables using the specifications listed.
• Use the database built in last week’s assignment to create the two tables and associated columns listed below.  Table names, data types, and column lengths must be used as specified; column names may be created as desired and appropriate.  Avoiding the use of spaces and special characters in the column names is recommended.
• In the Employee table, create an Employee ID column that generates a unique number for each employee and designate the column as the Primary Key. In the Job table, use the job title column as the table’s primary key.

Table Name = Employee_Tbl
• Employee ID (type= numeric identity) PK
• Last name (type=varchar(30))
• First name (type=varchar(30))            
• Address (type=varchar(30))
• City (type=varchar(30))        
• State (type=varchar(2))
• Telephone area code (type=varchar(3))            
• Telephone number (type=varchar(7))
• Employer Information Report (EEO-1) classification                  (type=varchar(30))
• Hire date (type=date)
• Salary (type=money)
• Gender (type=varchar(1))
• Age (type=numeric)
• Job Title (type=varchar(30))

    
Table Name = Job_Tbl
• Job title (type=varchar(30)) PK    
• Job description (type=varchar(2000))
• Exemption status (type=varchar(1))

Use the SQL INSERT statement to go to the human resources department in the Kudler Fine Foods Virtual Organization. Using information found in the employee files for the La Jolla and Encinitas stores, enter records into the Employee table for the following employees:
• Glenn Edelman
• Eric McMullen
• Raj Slentz
• Erin Broun
• Donald Carpenter
• David Esquivez
• Nancy Sharp

Use the information from the job classifications and descriptions to enter records into the Job Title table for the following titles:
• Accounting clerk
• Assistant manager
• Bagger
• Cashier
• Computer support specialist
• Director of finance and accounting
• Retail assistant bakery and pastry
• Retail assistant butchers and seafood specialists
• Stocker

Hint: Data entered into the job title column of the employee_tbl and job_tbl tables must match exactly, including case, spaces, and so on.  If they do not match exactly, you will not be able to create the foreign key or perform the joins that are required in later assignments.
Deliverables:  check your results by selecting all of the data from both tables (one table at a time).  Copy and paste screen images of your work in a word doc, then post as an attachment in your assignment tab of the classroom.  You may need to resize your windows and take multiple screen shots to capture all of the required information.  The screen images should show the following information:
• Database, table, and column definitions
• SQL statements used to load the data
• Data stored in both tables (display contents after data is loaded)
Refer to the materials forum for the rubric that will be used to grade this assignment.

POS 410 Week 3 Data Changes and SQL Statements SQL for Business

Use the Kudler_FF database to build a foreign key.  The foreign key should be built on the job title column in the employee_tbl, which references the job title column in the job_tbl.  An “alter table” statement will be required to build this FK on the employee_tbl.

Deliverable: Copy (screen image) the SQL statement used to build the foreign key and the associated output message from its execution into a Word document and post them to the assignment tab of the classroom.

Use the Kudler_FF database to write SQL statements and enter records into the Employee table for workers identified in the employee files for the administrative offices and the DelMar store. Check results by selecting all columns from both tables.

Deliverable: Copy (screen image) the SQL used to check your results and the output to a Word document and post to the assignment tab of the classroom.  

Use the tables in the Kudler_FF database to write the following SQL queries:
o Write a SQL query that joins the two tables in the Kudler_FF database, and displays the Employee Last Name, First Name, Hire Date, Area Code, Job Title, and Exemption Status.
o Write a SQL query that joins the two tables in the Kudler_FF database and uses BETWEEN to restrict record selection; use hire dates to restrict data.
o Write a SQL query that joins the two tables in the Kudler_FF database and uses LIKE to restrict record selection; use telephone area codes to restrict data.
o Write a SQL query that joins the two tables in the Kudler_FF database, and restricts the record selection to an exemption status.
o Write a SQL query that uses the UNION of the two tables to produce the results set

Deliverable: Copy (screen image) each query and the results set to a Word document and post them to the assignment tab of the classroom.  

Refer to the materials forum for the rubric that will be used to grade this assignment.

CMIS 420 Homework Assignment 3 Advanced Relational Database Concepts and Applications

Homework Task 1:
Create a PL/SQL block to complete the followings.  
Output the cheapest movie information.  You can use the view you created in project 1 - task 1
In your block, referential type should be used to receive the cursor return.
Use DBMS_OUTPUT to output your result.
Possible Exception should be handled in exception handling section.  You are not required to use user-defined exception. Use Oracle predefined exception. When exception occurs, you need to output error code and error message.
Spool the output to a text file.  Don't forget to use "Set serveroutput on"
Submit your code as .sql file and spooled output

Homework Task 2:
Create a stored procedure based on task 1 with an input parameter movie_id. Modify your cursor to use movie_id to select desired movie information. And execute the procedure and spool the execution result.
Spool the output to a text file.  Don't forget to use "set serveroutput on"
Submit your code as .sql file and spooled output

Homework Task 3:
Create a statement trigger on orders table. The trigger fires after updating the table. When the trigger fires one record insert into the following temp table using the insert statement shown below:
Note: you need to create temp_table and its sequence using the following code.

PROMPT creating table temp_table ...............
DROP TABLE temp_table;
CREATE TABLE temp_table
( num_col NUMBER(5) not null primary key,
char_col VARCHAR2(30),
date_col  VARCHAR2(30));

PROMPT creating SEQUENCE trigger_seq ...............
DROP SEQUENCE trigger_seq;
CREATE SEQUENCE trigger_seq
START WITH 1
INCREMENT BY 1;

INSERT INTO temp_table (num_col, char_col, date_col)
VALUES (trigger_seq.NEXTVAL, 'After Statement trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
Submit your code as .sql file

Homework Task 4:
Create a row trigger on order_items table to fire after inserting the data into order_items table. When the trigger fires it inserts a record into temp_table using the following insert statement:
INSERT INTO temp_table (num_col, char_col, date_col)VALUES (trigger_seq.NEXTVAL, 'After Row Trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
Submit your code as .sql file

Homework Task 5:
Test your triggers.
Update orders table to set total payment to 300. fire statement trigger on orders table.
Insert one record into order_items. Fire row trigger on order_items table.
Query temp_table to get the inserted records for trigger firing.
Spool the output to a text file and submit it.

CIS 373-30 SQL 1 EXERCISE
What to do:
Problem: ConstructionCo, pp. 278 – 282, Do problems 1 to 12 from page 278-280
You are given a partially correct Oracle script file: Ch07_ConstructCo_ORA.sql (posted in the same folder).  The script is incomplete because they did not define primary keys and foreign keys when creating the tables.  As shown in class (and also in my video capture), you will create your own script file based on this one by defining the primary keys and foreign keys (refer to the text book for the ERD).
To make it easier for you, I added several SQL Plus commands to redirect the output.  At the beginning of the Ch07_ConstructCo_ORA.sql ,  I added:

spool j:\scripts\SQL1.lst
set pagesize 24
set pause on
set linesize 60
and at the very end, I added:
Spool out

The Spool command will redirect the output, in my case, to the J: drive in the scripts folder and the file will be called SQL1.lst  (lst = listing, typical extension for output list).  Make sure you know the path name to redirect the output to.  I would recommend you to use the USB drive; make your path name shorter to avoid typos.  Please also noted that I set the pause ON. It means that you have to hit “Enter” to move to the next page.
How to do:
(1)    Start your Notepad, copy the necessary statements from Ch07_ConstructCo_ORA.sql  with the proper PKs and FKs, save it as  Ch07_SQL_1_Answers.sql.
(2)    Log in Oracle 11g XE
(3)    Start SQL Command Line (SQL Plus) program.
(4)    In SQL Plus, load and execute  Ch07_SQL_1_Answers.sql  (by using the start command in SQL Plus.)
(5)    To answer the questions, you can type your SQL statement directly in SQL Plus.  If it works, copy and paste the statement in your script file Ch07_SQL_1_Answers.sql.  Use comment, e.g. --, or /* …. */ to separate the questions.  The following is the skeleton of your script file:

spool j:\scripts\SQL1.lst
set pagesize 24
set pause on
set linesize 60

-- Name:  <Your Name>  SQL 1 Homework, pp. 278-280
--
<<<< Based on Ch07_SQL_1_Answers.sql, create the tables with proper PKs and FKs.  Copy all  INSERT statements to populate the tables.  Then you can start answering the questions.  Make sure you have created and populated  the tables correctly before moving forward.  Do it incrementally like I demonstrated in the videos. >>>>

-- Problem 1:
select name from student where student_ID = 1234;

--
-- Problem 2:
insert into Student Values (13567, “Doe”, “John”, “CIS”);
: : : : :

--  Problem 12:
UPDATE EMP_2
SET …….
WHERE EMP_HIREDATE ….
AND JOB_CODE >= ….. ;

Spool out

After you have finished all problems and created the script file, run the script file in its entirety one last time. When you are finished, submit both Ch07_SQL_1_Answers.sql and the output file SQL1.lst in Blackboard.

IT452 Unit 4 Outer Joins and Subqueries

a) List all order ID's for the customer named 'Customer LOLJO'. You must use a subquery.

b) List the lastname and firstname of all employees who have sold to customer 'Customer LOLJO'. Use a nested subquery (no joins).

c) List the empid, lastname, and firstname of all employees who are managers. (If an employee is a manager, his/her empid will appear as someone else's mgrid.) Use a subquery. (HINT: The subquery will list all the distinct mgrid values.)

d) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Write an uncorrelated subquery that uses NOT IN.

c) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Write a correlated subquery that uses NOT EXISTS.

e) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Use an outer join.
(HINT: First, write an inner join that returns all the (distinct) employees who *did* take an order during that month. Next, change WHERE to AND so that the predicate is part of the ON clause of the join. Finally, change your join to an outer join, using the WHERE clause to filter for only non-matching rows.)

f) Write a subquery that returns the first order placed in the orders table

g) Using a scalar subquery (returns only 1 row) to outer query, write a query to returns the customer id for orders placed by the sales rep Judy Lew

h) Select the EmployeeID, lastname, firstname for the employee that had sales but does NOT have a manager. (requires Inner Join and subquery)

i) Find the customer, shipcountry and number of orders that the customer made where the customer is not located in the United States. (Requires a Count, subquery and condition)

IT452 Unit 5 Table Expressions

1. What is the average number of order line items over all orders? The Sales.OrderDetails table has one row for each product for each order. Each row represents a "line item" on a sales invoice for the orderid. Start by writing a query that returns the number (COUNT) of line items (rows) for each orderid. Modify that query to CAST the line item count as decimal (8,4). Then use that query as a derived table to get the average number of line items over all orders.
(Returns 9 rows)

2. Create a view named Sales.LOLJO_Orders that lists all the orderid values for orders placed by customer 'Customer LOLJO'. (Requires an Inner Join)
(Returns 10 rows)

3. Create a view named Sales.WhoSoldToLOLJO that lists the empid, lastname, and firstname of all employees who have sold to customer 'Customer LOLJO'. Execute a Select query to show the results of the view.
(6 rows)

4. Write a CTE to extract the employee with the most orders. Write the Outer query to display the EmployeeID and Highest Orders Sold Count for that Employee Result: (empid 4, 156 orders)

5. Create an inline table-valued function named Sales.fCustOrders that takes a customer name as an argument and returns the orderid and orderdate values for orders placed by that customer. The orderdate should have the date only, no time (Hint: CAST). Your project should have your CREATE FUNCTION statement. Query the function to see the results from the function for the argument (page 165) for companyname 'Customer MLTDN'.
(4 rows)

6. Create a VIEW called TopSales that shows the TOP 10 percent of sales. Verify the results of the view in a SELECT clause.
(216 rows)

7. Encrypt the TopSales View and bind the schema so that it cannot be dropped or altered created in #6. Test it by trying to remove the producID from the underlying Table and show the error

8. Change the TopSales view from # 7 so that it contains a filter that only has qty = 130 and does not allow modifications. Test it with an Update Statement by trying to change the quantity.

IT452 Unit 6 Set Operations and Data Modification

1. List the contact name, contact title, address, city, region, postal code, and country of all customer and supplier contacts. Sort the results by country, then by region, then by city. Use UNION ALL. 120 rows returned

2. List the city, region, and country that have both a customer and a supplier. Use INTERSECT. 4 rows returned

3. Create a table in the tempdb database using the following CREATE TABLE statement:
USE tempdb; CREATE TABLE dbo.DOGS ( DogID int IDENTITY NOT NULL ,Name varchar(20) NOT NULL ,BirthDay date ,Alive char(1) NOT NULL -- Either 'Y' or 'N' ); GO
Write one INSERT statement with one VALUES clause to insert information about the following dogs:
Name Birthday Alive
Samantha 1993-03-17 Passed away Feb 2009 (Alive = 'N')
Misty 1993-06-20 No longer alive
Henry the 1/8th 2003-10-21 Alive
(Note: a small dog) Inka 2006-09-18 Alive
Result: (4 row(s) affected) Paste your INSERT statement into your Assignment document.

4. Use SELECT … INTO … syntax to create a Vendor table (name the table Vendor) in the tempdb database that has the exact structure and content as the purchasing.vendor table. Validate that the new vendor table was created in the tempdb (using SQL Query) and that all rows were inserted. Paste both queries into your project document. (104 rows returned)

5. Write a DELETE statement that removes the vendors from the tempdb.vendor table that have the PreferredVendorStatus flag turned on. Result: (11 row(s) affected)

6. The tempdb.vendor table is no longer needed. Please truncate it. Verify the table has been emptied using a SQL Query.

7. Run the following code to create the tempdb.dbo.DimProducts table:
USE tempdb;
CREATE TABLE [dbo].[DimProducts]( [dimProdID] [int] NOT NULL,
[ProductID] [int] NOT NULL, [ProductName] [nvarchar](60) NOT NULL,
[UnitPrice] [smallmoney] NOT NULL, [BeginDate] [date] NOT NULL,
[EndDate] [date] NOT NULL, CONSTRAINT [PK_Products] PRIMARY KEY
CLUSTERED ( [dimProdID] ASC ) );
GO
Use bcp to import data into the tempdb.dbo.DimProducts table from the DimProducts.txt file. The file may be obtained from Doc Sharing.
You should get a message that 77 rows were imported. Put a copy of your bcp command into your Assignment document.

8. Run the following code to create the tempdb.dbo.ProductStage table:
USE tempdb;
CREATE TABLE [dbo].[ProductStage]( [dimProdID] [int] NOT NULL,
[ProductID] [int] NOT NULL, [ProductName] [nvarchar](60) NOT NULL,
[UnitPrice] [smallmoney] NOT NULL, [BeginDate] [date] NOT NULL,
[EndDate] [date] NOT NULL );
GO
Use BULK INSERT to import data into the tempdb.dbo.ProductStage table from the ProductStage.csv file. That file may be obtained from Doc Sharing.
You should get a message that 27 rows were imported. Put a copy of your BULK INSERT command into your Assignment document.

9. Write a query that determines if there are any product names in the tempdb.dbo.ProductStage table that are not in the tempdb.dbo.DimProducts table. Use EXCEPT. Put a copy of your query into your Assignment document. In addition, put the product name(s) you returned from this query into your Assignment document.

10. Write a MERGE statement that modifies the tempdb.dbo.DimProducts table based on the contents of the tempdb.dbo.ProductStage table. When the dimProdID values match, update the target table based on what is different in the source row. [Examine the data in both tables to determine what row(s) will need updating.] When the dimProdID values do not match, insert the source row. Paste your MERGE query into your Assignment document. It should return a message that 27 row(s) were affected. In addition, report how many rows.