Refer to the review examples shown below to answer the following questions about joins:
1. How do you know where to place the (+) in an outer join?
----the other side has null information
2. How do you know whether to indicate a join as a right, left, or full outer join?
----what side or both sides have null information
3. What is the function of the WHERE clause in an equijoin?
----provide the conditions to join tables
4. When using a nonequijoin, what conditions must be true about the data in each table?
----the column name
5. What data will not be returned in a natural join between two tables?
1. Create a query that will show the average cost of the DJ on Demand events. Round to two decimal places.
2. Find the average salary for Global Fast Foods staff members whose manager ID is 19.
3. Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9.
where id in(12,19);
4. Using the Oracle database, select the lowest salary, the most recent hire date, the last name of the person who is at the top of an alphabetical list of employees, and the last name of the person who is at the bottom of an alphabetical list of employees. Select only employees who are in departments 50 or 60.
where department_id in(50,60);
5. Your new Internet business has had a good year financially. You have had 1,289 orders this year. Your customer order table has a column named total_sales. If you submit the following query, how many rows will be returned?
6. You were asked to create a report of the average salaries for all employees in each division of the company. Some employees in your company are paid hourly instead of by salary. When you ran the report, it seemed as though the averages were not what you expected. They were much higher than you thought! What could have been the cause?
----someone paid hourly, someone paid yearly
7. Employees of Global Fast Foods have birth dates of July 1, 1980, March 19, 1979, and March 30, 1969. If you select MIN(birthdate), which date will be returned?
8. Create a query that will return the average order total for all Global Fast Foods orders from January 1, 2002, to December 21, 2002.
where order_date is between 'January 1, 2002' and 'December 21, 2002';
9. What was the hire date of the last Oracle employee hired?
10. In the following SELECT clause, which value returned by the SELECT statement will be larger?SELECT SUM(operating_cost), AVG(operating_cost)
1. How many songs are listed in the DJs on Demand D_SONGS table?
2. In how many different location types has DJs on Demand had venues?
----select count(distinct loc_type)
3. The d_track_listings table in the DJs on Demand database has a song_id column and a cd_number column. How many song IDs are in the table and how many different CD numbers are in the table?
----select count(song_id),count(distinct cd_number)
4. How many of the DJs on Demand customers have email addresses?
5. Some of the partners in DJs on Demand do not have authorized expense amounts (auth_expense_amt). How many partners do have this privilege?
6. Using the table shown, what values will be returned when the following statement is issued?
SELECT COUNT(shoe_color), COUNT(DISTINCT shoe_color)FROM shoes;
----the total number of shoes, the number of shoes for different types
7. Create a query that will convert any null values in the auth_expense_amt column on the DJs on Demand D_PARTNERS table to 100000 and find the average of the values in this column. Round the result to two decimal places.