Oracle

ITSE 2309 LAB 2 More SQL Queries and Modification

Oracle 11g SQL–Chapters- 3,6,8,9,11,12,
You will continuing using items created in Lab 1

Lab 2a -- Problems 1–4,
1. For each customer, list each stock item ordered,
1) the manufacturer,
2) the quantity ordered, and
3) the total price paid.
Include the following columns in the order given below:
- From Customer Table: Company
- From Stock Table: Description
- From the Manufact Table: Manu_Name
- From the Items Table: Quantity, Total Price
Order the output by Company and Description.
Submit/hand in Output from SQL query

2. List all orders with a shipping date between December 25, 1999 and January 5, 2000
Include
1) the Order Number,
2) Order Date,
3) Customer company name, and
4) Shipping Date.
Order by
Customer Company Name and Order Number.
Submit/hand in Output from SQL query

3. Count the number of customers who do not have any orders placed.
Submit/hand in Output from SQL query

4. List all customers –
I) Who are ordering equipment whose description begins with 'tennis' or 'volleyball'.
II )Include
1) Customer number,
2) Stock number, and
3) Description.
Submit/hand in Output from SQL query
Do not repeat any rows.

Lab 2b Problems 5, 6, 7 and 8
5. Use the following SQL CREATE commands to CREATE the following tables in your
CREATE TABLE Professor
(Prof_ID NUMBER(3) Constraint pk_Professor Primary Key,
Prof_Lname VARCHAR2(15) NOT NULL,
Prof_Hiredate DATE,
Prof_Sal NUMBER(8,2),
Prof_Dept CHAR(6),
);
CREATE TABLE Student
(Stu_ID NUMBER(4) Constraint pk_Student Primary Key,
Stu_Lname VARCHAR2(15) NOT NULL,
Stu_Major CHAR(6),
Stu_CredHrs NUMBER(4),
Stu_GradePts NUMBER(5),
Prof_ID NUMBER(3),
CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)
REFERENCES Professor
);
Submit/Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables created.

6. Insert the following data into the tables created above using SQL INSERT commands.
Professor Table:
Prof_ID Prof_Lname Prof_Hiredate Prof_Sal Prof_Dept
123 Hilbert 20-MAY-1992 58000.00 MATH
243 Newell 15-JUL-1997 65500.00 CMPSCI
389 Lessing 04-APR-1988 40250.00 ENG
Student Table:
Stu_ID Stu_Lname Stu_Major Stu_CredHrs Stu_GradePts Prof_ID
2001 Parker CMPSCI 52 160 243
2166 Smith ENG 30 75 389
3200 Garcia MATH 62 248 123
4520 Smith CMPSCI 45 157 NULL
BE SURE TO ISSUE A COMMIT AFTER TABLE MODIFICATION COMMANDS HAVE BEEN RUN SUCCESSFULLY.
Submit a
Listing of each INSERT command,
The systems response and the resulting tables after the INSERTS are completed
(Example: SELECT * FROM Student;).

7. Perform the following SQL DELETE statements. Be sure to do them in order.
Issue a COMMIT command after all DELETEs have run.
a. Try to delete Professor 389. What message do you get? ___________________________
b. Delete Student 2166.
c. Now Delete Professor 389. Explain why the first attempt in a. was unsuccessful, and this time the DELETE was successful.
Submit/hand in : A listing of the DELETE statements.
The answers to questions a. b. and c.
A listing of the two tables after the deletes have run.

8. Perform the following UPDATE commands.
Issue a COMMIT command after all UPDATEs have run.
a. Replace the value of the Prof_ID for Student 4520 with 243.
b. Add 10% to the salary for each professor
Submit/hand in : A listing of the UPDATE statements
A listing of the two tables after the UPDATEs have run.

CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database

Overview:
Use SQL, PL/SQL, and Triggers to design and create a Mail-Order Database System. Please create your own data for testing purpose. Use the attached file "Project 2 Tables" as a guide to creating the tables. You should pre-populate the PARTS, CUSTOMERS, EMPLOYEE and ZIPCODES tables.

Due Date:
Check the due date in Syllabus for the exact date for this assignment. No project will be accepted after the due date.

Deliverables:
Turn in all SQL scripts in the form of a SQL script files. The script files should include,

1. A script file containing all the DML and DDL statements. That is, the SQL used to create the tables and sequence and the SQL to pre-populate or insert records in the tables. Name this file XXX_PROJ2.sql, where XXX are you intials.
2. A file containing the PL/SQL package (Specification and Body) that provides the functionality listed in the requirements below. Name this file XXX_PROJ2.pkg, where XXX are you initials.
3. A file containing the database triggers. Name this file XXX_PROJ2.trg, where XXX are you initials.
4. Finally, provide a test SQL*PLUS routine (PL/SQL anonymous block) that will test the PL/SQL functionality developed. Name this file XXX_PROJ2_tst.sql, where XXX are your initials.

You should submit your assignment through WebTycho as you did for previous assignments.
Use winzip or any zip software to package the four (4) files into one file called XXX_project2.zip, where XXX are your initials.

Requirements:
The Mail-Order Database consists of the following tables and attributes. Please ensure that all constraints are created when creating the tables. All constraints other than NOT NULL constraints must be named.
1. EMPLOYEE(ENO, ENAME, ZIP, HDATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
2. PARTS(PNO, PNAME, QOH, PRICE, REORDER_LEVEL, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
3. CUSTOMERS(CNO, CNAME, STREET, ZIP, PHONE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
4. ORDERS(ONO, CNO, ENO, RECEIVED, SHIPPED, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
5. ODETAILS(ONO, PNO, QTY, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
6. ZIPCODES(ZIP, CITY)
7. ORDERS_ERRORS(TRANSACTION_DATE, ONO, MESSAGE)
8. ODETAILS_ERRORS(TRANSACTION_DATE, ONO, PNO, MESSAGE)
9. RESTOCK(TRANSACTION_DATE, PNO)

• The EMPLOYEE table contains information about the employees of the company. The ENO (Employee Number) attribute is the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
• The PARTS table keeps a record of the inventory of the company. The record for each part includes its number (PNO) and name (PNAME) as well as the quantity on hand (QOH), the unit price (PRICE) and the reorder level (REORDER_LEVEL). PNO is the primary key for this table.
• The CUSTOMERS table contains information about the customers of the mail-order company. Each customer is assigned a customer number (CNO), which serves as the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
• The ORDERS table contains information about the orders placed by customers, the employee who took the orders, and the dates the orders were received and shipped. Order number (ONO) is the primary key. The Customer number (CNO) attribute is a foreign key referring to the CUSTOMERS table, and the ENO attribute is a foreign key referring to the EMPLOYEES table.
• The ODETAILS table contains information about the various parts order by the customer within a particular order. The combination of ONO and PNO attributes forms the primary key. The ONO attribute is a foreign key referring to the ORDERS table, and the PNO attribute is a foreign key referring to the PARTS relation.
• The ZIPCODES table maintains information about the zip codes for various cities. ZIP is the primary key.
• The ORDERS_ERRORS table contains information about any errors that occurred when an order is processed. Transaction date is the date of the transaction.
• The ODETAILS_ERRORS table contains information about all errors that occur when processing an order detail. Transaction date is the date of the transaction.
• The RESTOCK table contains information about all parts (PNO) that are below the reorder level. Transaction date is the date of the transaction.

1. Write a package called Process_Orders to process customer orders. This package should contain four procedures and a function, namely;
Add_order. This procedure takes as input customer number, employee number, and received date and tries to insert a new row in the Orders table. If the received date is null, the current date is used. The shipped date is left as null. If any errors occur, an entry is made in the Orders_errors table. A sequence called Order_number_seq should be used to populate the order number (ONO) column.
Add_order_details. This procedure receives as input an order number, part number, and quantity and attempts to add a row to the Odetails table. If the quantity on hand for the part is less than what is ordered, an error message is sent to the Odetails_errors table. Otherwise, the part is sold by subtracting the quantity ordered from the quantity on hand for this part. A check is also made for the reorder level. If the updated quantity for the part is below the reorder level, an entry is made to the Restock table.
Ship_order. This procedure takes as input an order number and a shipped date and tries to update the shipped value for the order. If the shipped date is null, the current date is used. If any errors occur, an entry is made in the Orders_errors table.
Delete_order. This procedure takes as input an order number and tries to delete records from both the Orders and Odetails tables that match this order number. If any errors occur or there is no record that matches this order number, an entry is made in the Orders_errors table.
Total_emp_sales. This function takes as input an employee number. It computes and returns the total sales for that employee.

2. Create triggers on the PARTS, ORDERS, and ODETAILS tables to populate the CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY columns when an insert or update is made. Use SYSDATE and the pseudo column USER to populate these columns.

3. Write a trigger that fires when a row in the Orders table is updated or deleted. The trigger should record the dropped order records in another table called deleted_orders. The deleted_orders table should also contain a date attribute that keeps track of the date and time the action (update or delete) was performed. This date is quite different from the CREATED_DATE and UPDATED_DATE from the Order table. Do not copy these dates to the deleted_order table. Please include the table creation script for the deleted_orders table in the script file.

4. Create a sequence called order_number_seq that will be used to populate the order number (ONO) column.

5. Write a PL/SQL anonymous block to test the above.

MSCD640 Assignment 1

Part 2: Hands-on exercise

You’ve just been through an eight week intensive training for database administration. You are now a support DBA. You’ve been assigned to handle tickets for a database environment. Handle each of the trouble tickets below. Capture all of your work as either screen shots or spooled output. You’ll need to hand in this output to the shift manager at the end of your shift that verifies you successfully handled each ticket.

---------------------------------------------------------------------------------
Ticket #1: Production DBA has reported that the database you’re assigned to is not in archive log mode. You need to enable archive logging in your assigned database.

Verify:
SQL> archive log list;
---------------------------------------------------------------------------------
Ticket #2: User has reported that they’re receiving an open cursors error from the application. You need to verify the database initialization setting of open_cursors and double the setting (for example, if it’s 50, change it to 100).

Verify:
SQL> show parameter open_cursors;
---------------------------------------------------------------------------------
Ticket #3: Production DBA notices abnormal wait times related to the online redo logs. The DBA recommends that you immediately add one online redo log to your database. Make sure you size the new online redo log the same size as the current logs.

Verify:
SQL> select group#, member from v$logfile;
---------------------------------------------------------------------------------
Ticket #4: Development DBA has filed a ticket asking for an additional tablespace. Create a tablespace, named AP_DATA size its datafile at 20M.

Verify:
SQL> select tablespace_name from dba_tablespaces;
---------------------------------------------------------------------------------
Ticket #5: Development team has filed a ticket asking for a new user account. Create a user named AP_MGMT with the password of f00b0r and assign it the following:
• Assign it the default tablespace of AP_DATA
• Assign it the temporary tablespace of TEMP
• Grant it connect, create table, and create sequence
• Alter the user to have a quota of unlimited on the AP_DATA

Verify:
SQL> connect ap_mgmt/f00b0r
SQL> select * from user_users;
---------------------------------------------------------------------------------
Ticket # 6: Development team is requesting that you create two tables in the AP_DATA account: EMP and DEPT. Here are the requirements.

• The DEPT table needs 2 columns: dept_id, dept_name
• The dept_id is a number.
• The dept_id is the primary key.
• The dept_name is varchar2(30)
• The dept_name needs a check constraint that limits it to the following values (‘HR’, ‘IT’, ‘SECURITY’)

• The EMP table needs 3 columns: emp_id, emp_name, dept_id
• The emp_id column is a number.
• The emp_id column is the primary key.
• The emp_name column is varchar2(30)
• The dept_id column is a number.
• The EMP(dept_id) column needs a foreign key constraint defined that references the DEPT(dept_id) parent table.

Verify:
SQL> desc emp;
SQL> desc dept;
---------------------------------------------------------------------------------
Ticket #7: The application team needs you to seed the EMP and DEPT tables with data:

insert into dept (dept_id, dept_name) values(1, ‘HR’);
insert into dept (dept_id, dept_name) values(2, ’IT’);
insert into dept (dept_id, dept_name) values(3, ‘SECURITY’);
insert into dept (dept_id, dept_name) values(4, ‘WAREHOUSE’);

insert into emp (emp_id, emp_name, dept_id) values (50, ‘GEORGE’, 2);
insert into emp (emp_id, emp_name, dept_id) values (20, ‘JANE’, 1);
insert into emp (emp_id, emp_name, dept_id) values (30, ‘JOHN’, 3);

Verify:
SQL> select * from emp;
SQL> select * from dept;
---------------------------------------------------------------------------------
Ticket #8: After the EMP and DEPT tables have been created and seeded (prior two steps). An end user is reporting strange locking issues with the DEPT and EMP tables. You need to run a script that validates whether or not the EMP table has an index created on the foreign key column.

---------------------------------------------------------------------------------
Ticket #9: Development team is reporting that they now require an index be added to the EMP table on the dept_id column. Make its tablespace APP_DATA.

Verify:
SQL> select index_name, column_name from user_ind_columns;
---------------------------------------------------------------------------------
Ticket #10: The storage manager is concerned about disk space. The manager wants a report showing how much space all of the tablespaces in your database are consuming. The manager would like to see space free and space consumed in the report.

---------------------------------------------------------------------------------
Ticket #11: The security department is reporting a massive security breach!! They are requesting that you lock all users in your database except for SYS.

---------------------------------------------------------------------------------
Ticket #12: The application team wants a report showing the space used for each table and index in the AP_MGMT account:

---------------------------------------------------------------------------------
Ticket #13: The production DBAs have requested that archive logging be disabled for your database. Disable archive logging in your database:

Verify:
SQL> archive log list;
---------------------------------------------------------------------------------
Ticket #14: With a bit of sadness, you realize your shift will end soon. Before you leave, your team leader would like you to write few sentences indicating whether or not your training prepared you adequately for the job. Do you have any recommendations for your team leader?

MIS 562 Week 7 Homework Roles and Privileges

Part 1
Using the following Data Dictionary views write the statements that will perform the following actions. Be sure to test your statements. (Do not use SELECT *)
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS

Question SQL statement or Answer
1. Determine what privileges your account has been granted through a role. (10 points)

2. Determine what system privileges your account has been granted. (10 points)

3.Execute the following statement then determine what table privileges your account has been granted. (15 points)
Grant select on student to public;

4. Determine what system privileges the DVONLINE role has. (10 points)

5. Analyze the following query and write a description of the output it produces. (15 points)
SELECT COUNT(DECODE(SIGN(total_capacity-20), -1, 1, 0, 1)) "<=20",
COUNT(DECODE(SIGN(total_capacity-21), 0, 1, -1, NULL,
DECODE(SIGN(total_capacity-30), -1, 1)))"21-30",
COUNT(DECODE(SIGN(total_capacity-30), 1, 1)) "31+"
FROM
(SELECT SUM(capacity) total_capacity, course_no
FROM section
GROUP BY course_no)

6. Determine the top three zip codes where most of the students live. Use an analytical function. The query will product 10 rows. (10 points)

Part 2
Analyze the file from Doc Share called utlpwdmg.sql and analyze the code in this file. Write a paragraph that describes what the function performs. What are the inputs parameters, the output parameter and what does the function do? (30 points)

Oracle Database to Track Students Grade

Build an Oracle database to track students grade in a class:
The database tracks
*Student Information
*Instructor Information
*Class information
*Grading Breakdown
*Students' Grades

Different queries will show
*List of students with semester grades
*List of students who received an A
*Average grades
*Above average grades

MIS 562 Week 5 Homework Query Optimization

Using the student schema from week 2, provide answers to the following questions.

Question
SQL statement or Answer
1. Generate statistics for the student, enrollment, grade, and zipcode tables (15 pts)

2. Write a query that performs a join, a subquery, a correlated subquery using the student, enrollment, grade, and zipcode tables. Execute each query to show that it produces the same results. (15 pts)

3. Produce an autotrace output and explain plan for each query. (10 pts)

4. Analyze the results and state which performs best and why. Write an analysis of what operations are being performed for each query. Determine which query is the most efficient and explain why (10 pts)

CMIS 320 Lab 4 Homework 4 Choose Oracle datatypes

Instructions
Choose Oracle datatypes for the following attributes from a normalized relation including:
Vendor(VendorID, Address, ContactName)
Item (ItemID, Description)
PriceQuote(VendorID, ItemID, Price)
Describe why you selected the datatypes for each attribute.

CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script

Homework Submission Requirements:
Homework should be submitted as an attached file in your assignment folder. Text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
You must name your file to include your first initial of your first name and last name.
Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.

Part I Data Modeling
You are working for country club with thousands of members. You have been tasked with designing a database to keep track of the members and their guests.
The club keeps track of mail and telephone contact information, name and membership number. When a member joins the club they can become a social member with pool, racket ball and weight room privileges or golf member which includes all of the social member privileges plus access to the golf course.
Develop, document and design an EER for this situation.
Submit your word document no later than the due date.

Part II Lab 3
In lab 2, you will learn how to edit and run a SQL script in your schema in NOVA Oracle database. You also continue to learn simple SQL commands, such as select and insert ...

Build and execute SQL script
In this lab, we learn how to write a SQL script that can be run on NOVA Oracle database to execute the SQL commands included in the file. You are provided with SQL Statements and sqlplus commands to add into the script. What you are requested to do is to reorganize the commands in proper order to avoid runtime errors.
How to write a script: The script file is a text file which contains executable commands. For SQL script, it contains SQL statements which can be executed against a proper database. For our SQL script that runs against Oracle database. Therefore, we include some of Oracle specific SQLPlus commands that help us make output clear and understandable and allow us to save the output file.
Get the txt file below that contains SQL and SQLPlus commands. Spool commands are for getting the execution results. Set echo on or off are for getting executed commands printed on the screen before the execution result. Others are all SQLcommands. These commands should work as they are without modification.
Download lab3.txt
There are a few simple suggested rules that can make your script robust.
Use remark, rem, to add comment or reminder in the script anything followed rem is not executed.
Spool on command should be the first command and followed
Set echo on should be before any execution commands begin.
Set echo off and spool off at the end of the file.
For SQL DDL commands. Drop the table before create it. this gives you clean database to begin with. The table created last should be dropped first.
For SQL DML, delete the records first before insert them into the table. This allow you begin with empty table. delete the records from child table first if primary key and foreign key constraints exist between the two tables. However, insert into parent table first then insert into child table. Don't forget the child record needs a refernce key value from the parent table. Always end with commit command to save the data.
you can use vi text editor to edit the script on NOVA or use a text editor at local to create and edit the script and upload to Nova using sFTP. The script MUST be saved with .sql extension in order for it to be run in SQLPLus
After you successfully create and executed your script. Transfer your output file back and Submit it through assignment for grading.

CMIS 320 Lab 2 Homework 2 Data modeling and Sample database

Homework Submission Requirements:
Homework should be submitted as an attached file in your assignment folder. text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
You must name your file to include your first initial of your first name and last name.
Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.

Part I Data Modeling
For each of the following descriptions:
a. A piano manufacturer wants to track all pianos it makes. Each piano has a unique serial number and a manufacturing completion date. Each instrument represents exactly one piano model, all of which have an identification number and model. The company produces thousands of pianos of a certain model, and the design is specified before any single piano exists.
b. A vendor builds multiple types of tablet computers. Each has a type identification number and a name. The key specifications for each type include amount of storage and display type. The company uses multiple processor types, exactly one of which is used for a specific tablet type. The same processor can be used in multiple types of tablets. Each processor has a manufacturer and a manufacturer's unique code that identifies it.
perform the following tasks:
1. Identify the degree and cardinalities of the relationship.
2. Express the relationships graphically with an E-R diagram.
Document your work into a single, well-organized, well-written word document and submit no later than due date.

Part II Lab 2
In lab 2, you will learn how to run a SQL script to create a sample database in your schema in NOVA Oracle database. You also start to learn simple SQL commands, such as select and insert

Click here to open Lab 2 instruction page

This part of lab is to learn to run a SQL script on NOVA Oracle database server, and spool screen output to a text file. These techniques will be used when you work on your project 1 and 2. Download script file. This is sql script file containing completed sql commands to create tables and constraints and populate data to each table. The database you create is called JustLee Book Store. The code should be bug free.
Download lab2.sql script
Start spool command and run the script:
Log on NOVA, make a dir named scripts using command mkdir scripts
Then cd to newly created script directory
Upload your script to scripts directory using FTP
(I downloaded a free FTP client CORE FTP LE) and use it to upload and download file)
Now you have a newly create dir and your script is uploaded. You are ready to run your script.
Using SQL*Plus, log on NOVA Oracle database with your account
At SQL prompt,Type the following command to turn on spool function: SQL>spool spool01.lst
spool01.lst is the spool file name
Then use this command to run the script: SQL>@lab2.sql
The command will execute all the SQL commands inside the script and output will be spooled to spool01.lst
In the script, there are drop table statement as well. When first run, these commands will fail because the tables are not created yet.
You can run the script multiple times without any problems.
When you finish the execution, issue command SQL>spool off
This command will end spool function and save output to the file you defined
When you have done all of these. Use FTP get spool01.lst to your local computer and submit it as part of homework 2
You should know how to perform the following tasks using sqlplus on NOVA Oracle database after you complete this lab:
Using FTP to upload or download a file (scripts...)
Using sqlplus to log on to NOVA Oracle database
Using sqlplus command, spool to output screen output to a text file and transfer the file back to your local PC.
Understand the steps taken place when you perform FTP file transfer and Spooling your output.
Test the database you create and learn SQL queries.
You need to spool the execution results of the following commands.
Do a database query: select table_name from user_tables; USER_TABLES is a database system view that allow users to see the tables they created in their own schema.
You should be able to list the tables you just created
use this query: select * from "table_name from the above query"; you can retrieve the data in these tables one a time
use this query: select count(*) from "table_name from the above query"; you can retrieve the record counts in these tables one a time
Use SQL*Plus command "desc books" to check columns and their datatypes. And insert one record into the table. Use SQL command "commit" to save the record into your database.
When you have done all of these. Use FTP get spooled file to your local computer and submit it as part of homework 2