SECTION4 LESSON 2
Use the Oracle database for problems 1 - 4.
1. Create a cross-join that displays the last name and department name from the employees and departments tables.
from employees cross join departments;
2. Create a query that uses a natural join to join the departments table and the locations table by the location_id column. Display the department id and name, location id, and city.
from departments natural join locations;
3. Rewrite problem 2 using equijoin syntax.
from departments d,locations l
4. Create a query that uses a natural join to join the departments table by the location_id column. Restrict the output to only department IDs of 20 and 50. Display the department id and name, location id, and city.
----select departments.department_id,departments.department_name,location_id, locations.city
from departments natural join locations
where departments.department_id = 20
5. Use an equijoin between the DJs on Demand database tables, d_songs and d_types. Display the type code, description and title. Limit the rows returned to those type codes between 70 and 80.
---- select d_songs.type_code,d_songs.title,d_types.description
and d_songs.type_code is between 70 and 80;
6. a. When using Oracle proprietary syntax, the join condition is always placed in the _____where__________ clause of the SELECT statement.
b. When using ANSI/ISO SQL: 1999 syntax, the join condition is always placed in the _____from__________ clause of the SELECT statement.
7. A/an ______equijoin_________ can be used to preface the column name in order to clarify which table and column are participating in the join.
8. Table aliases are created in the ________from________ clause of the SELECT statement.
1. Join the Oracle database locations and departments table using the location_id column. Limit the results to location 1400 only.
from locations l join departments d
2. Join DJ on Demand d_play_list_items, d_track_listings, and d_cds tables with the JOIN USING syntax. Include the song ID, CD number, title, and comments in the output.
from d_cd dd join d_track_listing dt join d_play_list_items dp
on(dd.cd_number=dt.cd_number, and dt.song_id=dp.song_id);
3. Display the city, department name, location ID, and department ID for departments 10, 20, and 30 for the city of Seattle.
from departments join locations
where department_id in (10,20,30)
4. Display country name, region ID and region name for Americas.
----select country_name,region_id,region_name from countries join regionsusing(region_id)where country_name='United States of America';
5. write a statement joining the employees and jobs tables. Display the first and last names, hire date, job id, job title and maximum salary. Limit the query to those employees who are in jobs that can earn more than $12,000.
----select first_name,last_name,hire_date,job_id,job_title,max_salary from employees join jobs using (job_id) where salary>=12000;
6. Display job title, employee first name, last name, and email for all employees that are stock clerks.
----select first_name,last_name,email,job_title from jobs join employees using (job_id) where job_title='Stock Clerk';
The following questions use the JOIN…ON syntax:
7. Write a statement that displays the employee ID, first name, last name, manager ID, manager first name, and manager last name for every employee in the employees table. Hint: this is a self-join.
----select e.employee_id,e.first_name,e.last_name,m.employee_id,m.first_name,m.last_name from employees e,employees m where e.employee_id=m.employee_id;
8. Use JOIN ON syntax to query and display the location ID, city and department name for all Canadian locations.
----select location_id,city,department_name from departments d join locations l on(d.location_id=l.location_id) where country_id='CA'
9. Query and display manager ID, department ID, department name, first name, and last name for all employees in departments 80, 90, 110, and 190.
----select manager_id,department_id,department_name,first_name,last_name from employees join departments using(department_id) where department_id in(80,90,110,190)
10. Display employee ID, last name, department ID, department name, and hire date for those employees whose hire date was June 7, 1994.
----select employee_id,last_name,department_id,department_name,hire_date from employees join departments using(department_id) where hire_date='June 7,1994';
1. Return the job ID, job title, first name, last name, and department ID of all employees with a job ID of IT_PROG.
----select job_id, job_title,first_name,last_name,department_id
from employees join jobsusing (job_id)
2. Display the employee's last name and employee number along with the manager's last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.
----select e.last_name "Employee",e.employee_id "Emp#",m.last_name "Mnager",m.employee_id "Mgr#"
from employees e,employees m
3. Modify problem 2 to display all employees, including those who have no manager. Order the results by the employee number.
----select e.last_name "Employee",e.employee_id "Emp#",m.last_name "Mnager",m.employee_id "Mgr#" from employees e,employees m where e.employee_id=m.employee_id(+)
order by e.employee_id;
4. Create a query of the DJ on Demand database to return the first name, last name, event date, and description of the event the client held. Include all the clients even if they have not had an event scheduled.
from d_clients left outer join d_events
5. Using the Global Fast Foods database, show the shift description and shift assignment date even if there is no date assigned for each shift description.
from f_shifts s
left outer join f_shift_assignments sa
Practice exercises:1. A/An ______join query__________ is when the rows of the tables are combined with each other and produce new rows . The number of rows is equivalent to the product of the number of rows in each table.
2. A/An _____self join____________ is used when you need to query a table that has a relationship to itself.3. A/An _____cross join____________ preserves unmatched rows from one or both tables, returning the rows that are matched and unmatched from one or both tables.4. In an outer join, a plus sign (+) is placed on the side of the join that is _____null____________ information.5. A ______outer____________is used when a column in one table does not correspond directly to a column in another table.6. The join condition is always placed in the ____on___________ clause of the SELECT statement.7. A/An _____aliases__________ is used to preface the column name in order to clarify which table and column are participating in the join.8. Table aliases are created in the ____from____________ clause of the SELECT statement.9. In a full outer join, a row that does not contain data will appear in the results set if the row satisfies the join condition.10. Table aliases cannot exceed ___3______ characters in length.11. Identify the Oracle syntax to signify an outer join___________.12. If a join condition is written: WHERE e.client_number = c.client_number, what kind of join would it be if we wanted all the information in the e table even if the c table has missing data? _cross join_____13. Joins that are based on hierarchical relationships such as manager and employee are called _self join_________.14. How many join conditions does it take to join three tables? _2__15. What does the term "proprietary syntax" mean?16. What type of join condition is best when two columns with the same name but different data types exist in two different tables?
cross join17. What type of join(s) are based on all columns in two tables that have the same name?
self join18. Another name for a cross-join is a___nonequijoin________.19. When specifying a join, you need to identify the what, where, and how. Match up these terms to the SQL syntax words: FROM, WHERE, SELECT.20. For each join condition listed, mark T for those that can include an alias in the syntax:___ cross-join___ equijoin___ nonequijoin___ natural join___ full outer join___ left outer join___ USING clause21. Which two types of join conditions cannot use an alias?
natural join22. The query below is an example of what kind of join?SELECT last_name, first_nameFROM f_staffs, f_orders;
cross join23. Use the three tables shown to answer the following questions:a. What kind of join would you use to join all three tables? ______equrium____________ Write the syntax that would produce the desired result.b. Name two tables that could be used to retrieve data from a natural join.Write the syntax that would produce the desired result.c. What kind of join would you use to return only those students who have mentors?Write the syntax that would produce the desired result.d. What kind of join would you use to return all students whether they have a mentor or not.Write the syntax that would produce the desired result.