lesphomework

this is a crazy site for crazy people

Sunday

section 4

Lesson 2
1. Create a cross-join that displays the last name and department name from the employees and departments tables.
Select last_name, department_name
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.
Select department_id, department_name, location_id, city
from departments natural join locations;

3. Rewrite problem 2 using equijoin syntax.
Select d.department_id, d.department_name, l.location_id, l.city
from departments d, locations l
where d.location_id=l.location_id;

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 department_id, department_name, location_id, city
from departments natural join locations
where department_id in (20, 50);
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.title, d_types.code, d_types.description
from d_songs, d_types
where d_types.code=d_songs.type_code and d_types.code between 70 and 80;

6. a. When using Oracle proprietary syntax, the join condition is always placed in the _______________ clause of the SELECT statement.whereb. When using ANSI/ISO SQL: 1999 syntax, the join condition is always placed in the _______________ clause of the SELECT statement.from

7. A/an _______________ can be used to preface the column name in order to clarify which table and column are participating in the join.shortcut of the column

8. Table aliases are created in the ________________ clause of the SELECT statement.as

Lesson 3
1. Join the Oracle database locations and departments table using the location_id column. Limit the results to location 1400 only.Select l.*, d.* from locations l join departments d on (l.location_id=d.location_id)where l.location_id=1400;
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.
3. Display the city, department name, location ID, and department ID for departments 10, 20, and 30 for the city of Seattle. Select city, department_name, location_ID, department_ID from departments d join locations l using(location_id) where department_id in (10, 20, 30) and city='Seattle';
4. Display country name, region ID and region name for Americas.Select c.country_name, c.region_id, r.region_name from countries c join regions r on (c.region_id=r.region_id) where r.region_name='Americas';

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 e.last_name, e.hire_date, e.job_id, j.job_title, j.max_salary from employees e join jobs j on (e.job_id=j.job_id) where max_salary>12000;

6. Display job title, employee first name, last name, and email for all employees that are stock clerks.Select j.job_title, e.first_name, e.last_name, e.email from jobs j join employees e on(e.job_id=j.job_id) where job_title='Stock Clerk';
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, e.manager_id, m.first_name, m.last_name from employees e join employees m on(e.manager_id=m.manager_id);
8. Use JOIN ON syntax to query and display the location ID, city and department name for all Canadian locations. Select l.location_id, l.city, d.department_name from locations l join departments d on (l.location_id=d.location_id);
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 e.manager_id, d.department_name, d.department_ID, e.first_name, e.last_name from departments d join employees e on(e.department_id=d.department_id) where e.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 e.employee_id, e.last_name, d.department_id, d.department_name, e.hire_date from employees e join departments d on(e.department_id=d.department_id) where e.hire_date='07-jun-1994';

Lesson 4

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 j.job_id, j.job_title, e.first_name, e.last_name, e.department_id from employees e full outer join jobs J on(e.job_id=j.job_id) where e.job_id='IT_PROG';

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.manager_id "Mgr #",m.last_name "Manager"from employees e full outer join employees mon(e.employee_id=m.manager_id);

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.manager_id "Mgr #",m.last_name "Manager"from employees e full outer join employees mon(e.employee_id=m.manager_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.Select c.first_name, c.last_name, e.event_date, e.descriptionfrom d_clients c full outer join d_events e on(c.client_number=e.client_number(+));

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.Select s.description, a.shift_assgn_date from f _shifts s full outer join f_shift_assignments a on(f.code=a.code(+));


Lesson 5

1. A/An ________________ 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.Natural join

2. A/An _________________ is used when you need to query a table that has a relationship to itself.Self join

3. A/An _________________ preserves unmatched rows from one or both tables, returning the rows that are matched and unmatched from one or both tables.Cross join

4. In an outer join, a plus sign (+) is placed on the side of the join that is _________________ information.null

5. A __________________is used when a column in one table does not correspond directly to a column in another table.nonequijoin

6. The join condition is always placed in the _______________ clause of the SELECT statement.using

7. A/An _______________ is used to preface the column name in order to clarify which table and column are participating in the join.shortcut of the column name

8. Table aliases are created in the ________________ clause of the SELECT statement.as

9. In a full outer join, a row that does not contain data will/will not appear in the results set if the row satisfies the join condition.will

10. Table aliases cannot exceed _________ 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? ______
13. Joins that are based on hierarchical relationships such as manager and employee are called __________.
14. How many join conditions does it take to join three tables? ___

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?

17. What type of join(s) are based on all columns in two tables that have the same name?

18. Another name for a cross-join is a___________.

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:T___ cross-joinT___ equijoin___ nonequijoinT___ natural join___ full outer join___ left outer join___ USING clause

21. Which two types of join conditions cannot use an alias?nonequijoin USING clause

22. The query below is an example of what kind of join?SELECT last_name, first_nameFROM f_staffs, f_orders;Nonequijoin

23. Use the three tables shown to answer the following questions:a. What kind of join would you use to join all three tables? __________________ 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.

0 Comments:

Post a Comment

<< Home