Page 3 Of 11: Delete This Text And Type Your Name Here

Page3of11delete This Text Andtype Your Name Herethis File Will Become

Section 3 Objectives: Executing Database Joins Create and execute database joins Use table aliases in SQL statements Vocabulary: Directions: Identify the vocabulary word for each definition below. (1 Point each) 1. A join that returns the unmatched rows as well as matched rows. Answer: 2. Performs a join on two tables, retrieves all the rows in the left table even if there is no match in the right table. Answer: 3. A join of two or more tables that return only matched rows. Answer: Try It / Solve It : In order to be successful with this lab, you 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 your results. At the top of your SQL window, you MUST increase the number of rows displayed to 20 so that I am able to see the first 20 rows of your results. You will need to provide the SQL statement you used to produce your results.

DBMS Output. Create a query using a LEFT OUTER JOIN that will display the first name, last name, and department name for all employees including those employees not assigned to a department. You will be using the Employees and Departments tables. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

2. Create a query using a RIGHT OUTER JOIN that will display the first name, last name, and department id for all employees including those departments that do not have an employee assigned to them. You will be gathering data from the Employees and Departments tables. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): DBMS Output. Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

3. Create a query using a FULL OUTER JOIN that will display the first name, last name, and department id for all employees including those departments that do not have an employee assigned to them and those employees not assigned to a department. You will be gathering data from the Employees and Departments tables. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

4. Create a query using a JOIN that will display the employee’s last name and employee number along with the manager’s last name and manager number. You will be gathering data from the Employees table. Label the columns Employee, Emp#, Manager, and Mgr# using an alias. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): DBMS Output. Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

5. Modify problem 4 and use a LEFT OUTER JOIN to display all employees, including those who have no manager. Order the results by the employee number. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): DBMS Output. Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

6. Create a query using a LEFT OUTER JOIN that will display the first name, last name, event date and description of the event the client held. You will be gathering data from the d_clients and d_events tables located in the DJs on Demand database. Include all clients even if they have not had an event scheduled. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): DBMS Output. Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

7. Create a query using a LEFT OUTER JOIN that will display the shift description and shift assignment date even if there is no date assigned for each shift description. You will be gathering data from the f_shifts and f_shift_assignments tables located in the Global Fast Foods database. Use a table alias in your SQL statement. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): DBMS Output. Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

Section 4 Objectives: Working With Group Functions

Create and execute group functions Vocabulary: Directions: Identify the vocabulary word for each definition below. (1 Point each) 1. A command that suppresses duplicates. Answer: 2. To gather into a sum or whole. Answer: 3. Returns the sum on numeric columns. Answer: 4. Used with columns that store any data type to return the maximum value. Answer: Try It / Solve It : In order to be successful with this lab, you 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 your results. At the top of your SQL window, you can increase the number of rows displayed. You will need to provide the SQL statement you used to produce your results.

1. The manager of Global Fast Foods would like to send out coupons for an upcoming sale. He wants to send one coupon to each household (could be more than one customer per household). Create a query using DISTINCT that displays the customer's last name and mailing address (address, city, state, zip). You will gather data from the f_customers table. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

2. Create a query that displays the sum of the salaries for Global Fast Food's staff members whose IDs are 12 and 9. You will gather data from the f_staffs table. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

3. Create a query that will find and display the average salary for Global Fast Food's staff members whose manager ID is 19. You will gather data from the f_staffs table. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

4. Create a query that will display how many songs are listed in the DJs on Demand d_songs table. Label the column 'Number of Songs'. Execute the query in Oracle Application Express (Apex). (2 Points) Select Statement You Used: My example of correct results from Apex (copy): Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point) Your Screenshot below:

References

  • Oracle Corporation. (2020). Oracle Database SQL Language Reference. Oracle.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (6th ed.). Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.
  • Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Pearson.
  • Rob, P., & Coronel, C. (2009). Database Principles: Fundamentals of Design, Implementation, and Management (8th ed.). Cengage.
  • Kroenke, D. M., & Auer, D. J. (2012). Database Concepts (6th ed.). Pearson.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Ullman, J. D., & Widom, J. (2008). Database Systems: The Complete Book. Pearson.
  • Allen, L. (2014). SQL: The Complete Reference. McGraw-Hill Education.

Related Assignments