Oracle

IT358 Unit 8 Users and Roles

Outcomes addressed in this activity:
Create users
Create roles
Use the GRANT and REVOKE statements
Create and access database links

Project requirements:
In this project, you will work with privileges, roles, and passwords:
1. Answer Review questions 1–5 from Chapter 7.
2. Complete Hands on Assignments #1-10 from Chapter 7

Oracle 11G SQL Chapter 7 Hands-On assignment Solution
1. Create a new user account. The name of the account should be a combination of your first initial and your last name.
2. Attempt to log in to Oracle 10g using the newly created account.
3. Assign privileges to the new account that allow the new user to connect to the database, create new tables, and alter an existing table.
4. Using a properly privileged account, create a role named customerrep that allows new rows to be inserted into the ORDERS and ORDERITEMS tables and allows rows to be deleted from those tables.
5. Assign the account created in Assignment 1 the customerrep role.
6. Log in to Oracle 10g using the new account created in Assignment 1. Determine the privileges currently available to the account.
7. Revoke the privilege to delete rows in the ORDERS and ORDERITEMS tables from the customerrep role.
8. Revoke the customerrep role from the account created in Assignment 1.
9. Delete the customerrep role from the Oracle 10g database.
10. Delete the user account created in Assignment 1.

IT358 Unit 7 Synonyms Index and Sequence

Outcomes addressed in this activity:
Describe how the schema objects work
Describe how sequences and indexes are created, modified, and removed within Oracle
Describe when to use an Index
Utilize Synonyms

Project requirements:
In this project, you will create a view, synonym, and index:
1. Answer the Review Questions #1–5 from Chapter 6.
2. Complete Hands-on Assignments #1-10.

Oracle 11G SQL Chapter 6 Hands-On assignment Solution
1. Create a sequence to use for populating the Customer# column of the CUSTOMERS table. When setting the start and the increment values, keep in mind that data already exists in this table. The options should be set to not cycle the values, not cache any values, and no minimum or maximum values should be declared.
2. Add a new customer row using the sequence created in Question 1. The only data currently available for the customer is as follows: last name = Shoulders, first name = Frank, and zip = 23567.
3. Create a sequence that will generate integers starting with the value 5. Each value should be three less than the previous value generated.The lowest possible value should be 0, and the sequence should not be allowed to cycle. Name the sequence MY_FIRST_SEQ.
4. Issue a SELECT statement that will display NEXTVAL for MY_FIRST_SEQ three times. Because the value is not being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the last SELECT?
5. Change the setting of MY_FIRST_SEQ so the minimum value that can be generated is –1000.
6. Create a private synonym that will allow you to reference the MY_FIRST_SEQ object as NUMGEN.
7. Use a SELECT statement to view the CURRVAL of NUMGEN. Delete the NUMGEN synonym and MY_FIRST_SEQ.
8. Create a Bitmap index on the CUSTOMERS table to speed up queries that search for customers based on their state of residence. Verify that the index exists, and then delete the index.
9. Create a B-tree index on the customer’s Last Name column. Verify that the index exists by querying the data dictionary. Remove the index from the database.
10. Many queries search by the number of days to ship (number of days between the order and shipping dates). Create an appropriate index that might improve the performance of these queries.

IT358 Unit 6 Views

Outcomes addressed in this activity:
Create a view, using CREATE
VIEW command or the CREATE OR REPLACE VIEW command
Explain the effect of the WITH READ ONLY option
Demonstrate the ability to update records in a simple view
Demonstrate the ability to re-create a view
Identify problems associated with adding records to a complex view
Identify the key-preserved table underlying a complex view

Project requirements:
In this project, you will write joins and subqueries:
1. Complete the Review Questions for numbers 1–5 from Chapter 13.
2. Complete the Hands-on Assignments #1-10 from Chapter 13.

Oracle 11G SQL Chapter 13 Hands-On assignment Solution
1. Create a view that will list the name of the contact person at each publisher and the person’s phone number. Do not include the publisher’s ID in the view. Name the view CONTACT.
2. Change the CONTACT view so that no users can accidentally perform DML operations on the view.
3. Create a view called HOMEWORK13 that will include the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make certain the view will be created even if the FIRSTATTEMPT table does not exist.
4. Attempt to view the structure of the HOMEWORK13 view.
5. Create a view that will list the ISBN and title for each book in inventory along with the name and telephone number of the individual to contact in the event the book needs to be reordered. Name the view REORDERINFO.
6. Try to change the name of one of the individuals in the REORDERINFO view to your name. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
7. Select one of the books in the REORDERINFO view and try to change the ISBN of the book. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
8. Delete the record in the REORDERINFO view that contains your name (if that step was not performed successfully, then delete one of the contacts already listed in the table).Was there an error message displayed when performing this step? If so, what was the cause of the error message?
9. Issue a rollback command to undo any changes made with any previous DML operations.
10. Delete the REORDERINFO view.

IT358 Unit 5 INNER JOIN OUTER JOIN subquery

Outcomes addressed in this activity:
Create a query with an INNER JOIN
Create a query with an OUTER JOIN
Create a query that contains a subquery

Project requirements:
In this project, you will write joins and subqueries:
1. Complete the Hands-on Assignments #1–5 for Chapter 9.
2. Complete the Hands-on Assignments #1–5 for Chapter 12.

Oracle 11G SQL Chapter 9 Hands-On assignment Solution
Generate and test two SQL queries for each of the following tasks: (a) the SQL statement needed to perform the stated task using the traditional approach, and (b) the SQL statement needed to perform the stated task using the JOIN keyword.
1. Create a list that displays the title of each book and the name and phone number of the person at the publisher’s office whom you would need to contact to reorder each book.
2. Determine which orders have not yet shipped and the name of the customer who placed each order. Sort the results by the date on which the order was placed.
3. List the customer number and names of all individuals who have purchased books in the fitness category.
4. Determine which books Jake Lucas has purchased. Perform the search using the customer name, not the customer number.
5. Determine the profit of each book sold to Jake Lucas. Sort the results by the date of the order. If more than one book was ordered, sort the results by the profit amount in descending order. Perform the search using the customer name, not the customer number.

Oracle 11G SQL Chapter 12 Hands-On assignment Solution
Use a subquery to accomplish each of the tasks. First, execute the query you will use as the subquery to verify the results.
1. Determine which books have a retail price that is less than the average retail price of all books sold by JustLee Books.
2. Determine which books cost less than the average cost of other books in the same category.
3. Determine which orders were shipped to the same state as order 1014.
4. Determine which orders had a higher total amount due than order 1008.
5. Determine which author or authors wrote the book(s) most frequently purchased by customers of JustLee Books.

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.