answer q
basil101516
555555.rtfPage 20 of 20
Incomplete Assignments: All lab assignments submitted need to be in complete form. If a lab is submitted incomplete, it will not be graded.
Delete this text and type your name here
Lab 5 .
You will need to log into Apex at https://iacademy.oracle.com " https://iacademy.oracle.com in order to complete this assignment.
Sections 5 - 7 Programming with SQL
Section 5 Objectives : Using Complex SQL and Aggregate Data
Use GROUP BY and HAVING Clauses
Vocabulary :
Directions: Identify the vocabulary word for each definition below. (1 Point each)
1. Used to specify which groups are to be displayed; restricts groups that do not meet group criteria.
Answer:
2. Divides the rows in a table into groups.
Answer:
3. Operator that returns all rows from both tables and eliminates duplicates.
Answer:
Try It / Solve It :
In order to be successful with this lab, y ou must review the ‘Oracle Student Tables’ file located under ‘Videos and Resources’ (multiple tabs across bottom of worksheet). This file will help you to understand the data that is being manipulated in the SQL statements .
I am providing a ‘copy’ (first 2 0 rows) of the ‘Results from Apex’ for each question. Hopefully, this will help you see if you are on track with your results. At the top of y our SQ L window, you can increase the number of rows displayed. You will provide the SQL statement you used to produce shown results AND you will provide a screen shot of your results. In order to receive points, your screenshot MUST include your workspace number.
1. Create a query that will return the average of the maximum employee salaries in each department. You will be gathering data from the employees table. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
2. Create a query that will display the employee_id and department_id from the employees table and a second query displaying employee_id, job_id, start_date, and department_id from the job_history table. You will combine the results as one single output. Make sure you suppress duplicates in the output. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
3. Create a query that will display an employee's department_id, manager_id, and job_id as well as the SUM of salaries. You are gathering data from the employees table. Use Grouping Sets to show the following groupings:
- department_id, manager_id, job_id,
manager_id, job_id
department_id, manager_id
Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
4. Rewrite the following query using alternate options in the WHERE clause that will accomplish the same result as shown below (Hint: Use < comparison operator to specify the track is any track less than 3). Execute the query in Oracle Application Express (Apex). (2 Points)
SELECT MAX(song_id)
FROM d_track_listings
WHERE track IN ( 1, 2, 3);
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
Section 6 Objectives : Creating Subqueries
Create and execute single and multiple row subqueries
Try It / Solve It :
In order to be successful with this lab, y ou must review the ‘Oracle Student Tables’ file located under ‘Videos and Resources’ (multiple tabs across bottom of worksheet). This file will help you to understand the data that is being manipulated in the SQL statements .
I am providing a ‘copy’ (first 20 rows) of the ‘Results from Apex’ for each question. Hopefully, this will help you see if you are on track with you r results. At the top of your SQL window, you can increase the number of rows displayed. You will provide the SQL statement you used to produce shown results AND you will provide a screen shot of your results. In order to receive points, your screenshot MUST include your workspace number.
1. Create a query that displays the first_name and last_name for all employees who have the same department ID as the IT Department. Use a column alias so that first_name displays as First Name and last_name displays as Last Name. You will be gathering data from the employees and departments tables. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
2. Create a query that displays the first name and last name of employees who have the same job id as Rajs and was hired after Davies. Use a column alias so that first_name displays as First Name and last_name displays as Last Name. You will be gathering data from the employees table. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
3. Create a query that will find and display the last names of all employees whose salaries are the same as the minimum salary for any department. Use a column alias so that last_name displays as Last Name. You will be gathering data from the employees table. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
Section 7 Objectives : Updating Column Values and Deleting Rows
Use the INSERT statement
Explain how foreign-key and primary-key integrity constraints affect UPDATE and DELETE statements
Vocabulary:
Directions: Identify the vocabulary word for each definition below. (1 Point each)
1. Modifies existing rows in a table.
Answer:
2. Ensures the data adheres to a predefined set of rules.
Answer:
3. Removes ONE row from a table.
Answer:
Try It / Solve It :
You will need to log into Apex at https://iacademy.oracle.com " https://iacademy.oracle.com in order to complete this portion of the assignment.
NOTE: You will need to execute the SQL statement below in order to copy the ‘customers’ table (f_customers). This will leave the f_customers table as is, while you modify the copy (copy_f_customers).
CREATE TABLE copy_f_customers
AS (SELECT * FROM f_customers);
Table copies will not inherit the associated primary-to-foreign-key integrity rules (relationship constraints) of the original tables. The column data types, however, are inherited in the copied tables.
If a change is not possible, give an explanation as to why it is not possible.
1. Insert the new customers shown below to the copy_f_customers table. Execute in Oracle Application Express (Apex). (3 Points)
ID
FIRST_
NAME
LAST_
NAME
ADDRESS
CITY
STATE
ZIP
PHONE_ NUMBER
511
Kathy
Harper
2 Willy Way
Los Angeles
CA
98008
8586667641
225
Daniel
Spode
1923 Silverado
Denver
CO
80219
4258879009
230
Adam
Zurn
5 Admiral Way
Seattle
WA
7193343523
Select Statement s You Used :
Using Select * from copy_f_customers, provide a screen shot of your inserted records.
Why are you not able to insert Adam Zurn?(1 Point)
Your Answer:
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
2. Make a copy of the f_staffs table. Name the copy copy_f_staffs. Execute: Select * from copy_f_staffs to view contents of the table. Sue Doe has been an outstanding Global Foods staff member and has been given a salary raise. She will now be paid the same as Bob Miller. Update her record in copy_f_staffs so that she is paid the same salary as Bob Miller. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
Using Select * from copy_f_staffs, provide a screen shot of your updated record .
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
3. Execute the following SQL statement. Explain your results. Execute the query in Oracle Application Express (Apex). (2 Points)
DELETE from departments
WHERE department_id = 60;
Explain the integrity constraint error
Your Answer:
4. Sue Doe has decided to go back to college and does not have the time to work and go to school. Delete her from the Global Fast Foods staff (copy_f_staffs table). Verify that the change was made. Execute the query in Oracle Application Express (Apex). (2 Points)
Select Statement You Used :
Using Select * from copy_f_staffs , provide a screen shot of your table after deleting Sue Doe.
My example of correct results from Apex (copy) : YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHO T MUST SHOW YOUR WORSPACE AND BE CORRECT RESULTS IN ORDER TO RECEIVE CREDIT. (1 Point)
This file will become rather large due to your screen shots. I encourage you to compress this file (zip) before submitting it.
Sections 7- 8--Programming with SQL
Section 7-8 Objectives: Working with DML and DDL Statements
Creating and Modifying Tables
Using Data Types
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below. (1 Point each)
1. Command used to make a new table.
Answer:
2. A collection of objects that are the logical structures that directly refer to the data in the database. Answer:
3. Specifies a preset value if a value is omitted in the INSERT statement.
Answer:
4. Stores data; basic unit of storage composed of rows and columns.
Answer:
5. Created and maintained by the Oracle Server and contains information about the database.
Answer:
Try It / Solve It:
Log into Apex. Execute the following CREATE TABLE SQL statement:
CREATE TABLE grad_candidates
(student_id NUMBER(6),
last_name VARCHAR2(15),
first_name VARCHAR2(15),
credits NUMBER (3),
graduation_date DATE);
After executing the above SQL statement, you should receive a ‘Table Created ‘message.
1. Create an SQL statement that will describe the structure of the table object called grad_candidates. Provide a screen shot of your table properties as shown below. (2 Points):
Select Statement You Used:
My example of table structure results from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
2. Create a new table using a subquery. Name the new table your last name – e.g., herbert_table. Using a subquery, copy grad_candidates into herbert_table. Provide a screen shot of the table structure. (2 Points)
Select Statement You Used:
My example of table structure results from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
3. Insert your personal information into the HERBERT_TABLE (or whatever name you gave it). Hint: DATE and VARCHAR2 data type values need to have an apostrophe surrounding each value ('). (2 Points)
Select Statement You Used:
My example of table content results from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. (1 Point)
3. Create an SQL statement using the ALTER TABLE command. Alter the HERBERT_TABLE (or whatever name you gave it) and insert a new column called e_mail_address with a VARCHAR2 data type that will hold 80 characters. (2 Points)
Select Statement You Used:
4. Create an SQL statement that will describe the structure of the table object you just inserted the e_mail_address column into. Provide a screen shot of the table structure. (2 Point)
Select Statement You Used:
My example of table structure results from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
5. You decide you no longer need the e_mail_address column after all. Create an SQL statement that will drop this column from the table. (2 Points)
Select Statement You Used:
6. Create an SQL statement that will describe the structure of the table object you just altered, removing the e_mail_address column. Provide a screen shot of the table structure. (2 Points)
Select Statement You Used:
My example of table structure results from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
7. Dropping a column from a large table can take a long time. What is a quicker alternative? (1 Point)
Answer:
8. Create an SQL statement that will modify the name of HERBERT_TABLE (or whatever name you gave it). Change the name of the table to PRACTICE_TABLE. Do not copy the table. (2 Points)
Select Statement You Used:
9. Create an SQL statement that will describe the structure of the table object PRACTICE_TABLE. Provide a screen shot of the table structure. (2 Points)
Select Statement You Used:
My example of table structure results from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
10. You no longer use the PRACTICE_TABLE and need to remove it from your schema. Create an SQL statement that will remove this table. (2 Point)
Select Statement You Used:
11. You just discovered you removed the wrong table! Opppps! Execute the following SQL statement to see what is in your recycle bin.
SELECT *
FROM user_recyclebin
My example of table structure results from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
12. Your table should be in the recycle bin. Now, create an SQL statement that recovers that table. (2 Points)
Select Statement You Used:
Execute the following SQL statement to view the PRACTICE_TABLE contents.
SELECT * from PRACTICE_TABLE
My example of table contents from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
13. What is the difference between the TRUNCATE and the DROP command? (1 Points)
Answer:
14. Create an SQL statement that will add the comment 'This is a practice table' to the PRACTICE_TABLE. (2 Points)
Select Statement You Used:
Execute the following SQL statement to view all schema table comments in the data dictionary.
SELECT *
FROM user_tab_comments;
My example of table comments from Apex (copy): YOU MUST DELETE MY SCREEN SHOT BELOW AND INSERT YOUR OWN. YOUR SCREEN SHOW MUST SHOW YOUR WORSPACE IN ORDER TO RECEIVE CREDIT. (1 Point)
:
