Sunday, April 16, 2006

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.


----select employees.last_name,departments.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 departments.department_id,location_id,locations.city
from departments natural join locations;

3. Rewrite problem 2 using equijoin syntax.

----select d.department_id,d.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 departments.department_id,departments.department_name,location_id, locations.city
from departments natural join locations
where departments.department_id = 20
or departmnets.department_id=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.type_code,d_songs.title,d_types.description
from d_songs,d_types
where d_songs.type_code=d_types.code
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.
LESSON 3
1. Join the Oracle database locations and departments table using the location_id column. Limit the results to location 1400 only.


----select *
from locations l join departments d
on(l.location_id=d.location_id);

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.

----select dd.title,dd.cd_number,dd.song_id,dp.comments
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.

----select city,department_name,location_id,department_id
from departments join locations
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 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';

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 job_id, job_title,first_name,last_name,department_id
from employees join jobsusing (job_id)
where 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.last_name "Mnager",m.employee_id "Mgr#"
from employees e,employees m
where e.employee_id=m.employee_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.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.

----select first_name,last_name,event_date,description
from d_clients left outer join d_events
on(d_clients.client_id=d_events.client_id);
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,sa.shift_assgn_date
from f_shifts s
left outer join f_shift_assignments sa
where s.code=sa.code;
LESSON 5
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.

SECTION 3 LESSON 5
1. 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,employees m
where e.employee_id=m.manager_id

2. Modify question 1 to display all employees and their managers, even if the employee does not have a manager. Order the list alphabetically by the last name of the employee.

----select e.last_name "Employee",e.employee_id "Emp #",m.manager_id "Mgr #",m.last_name "Manager"
from employees e,employees

where e.employee_id(+)=m.manager_id
order by e.last_name;

3. Display the names and hire dates for all employees who were hired before their managers, along with their managers' names and hire dates. Label the columns Employee, Emp Hired, Manager, and Mgr Hired, respectively
d
---- select e.first_name' 'e.last_name "Employee",e.hire_date "Emp Hire",m.first_name' 'm.last_name "Manager",m.hire_date "Mgr Hire"
from employees e,employees m
where e.manager_id=m.employee_id and e.hire_date

4. Finish this code to produce all possible combinations of rows from both tables:
SELECT e.last_name, e.department_id, d.department_name

----SELECT e.last_name, e.department_id, d.department_name
from employees e,departments d
where e.department_id=d.department_id;

5. Finish this code to produce all information in the departments table whether it has a match in the locations table or not:
SELECT l.location_id, l.city, d.department_name

----SELECT l.location_id, l.city, d.department_name
from locations l,departments d
where l.location_id=d.department_id;

6. Finish this code to produce the following information for Diana Lorentz:
SELECT e.last_name, d.department_name

----select e.last_name,d.department_name
from employees e,departments d
where e.last_name='Diana', e.first_name='Lorentz'
and e.department_id=d.department_id;

7. How do you know which side of a join gets the (+) sign?
----This side has the values that the other doesn't.

Saturday, April 15, 2006

SECTION 3 LESSON 4
1. You need to produce a report for Global Fast Foods showing customers and orders. A customer must be included on the report even if the customer has had no orders.

----select *
from f_customers,f_orders
where f_customers.id=f_orders.cust_id(+);

2. Create a query of the Oracle database that shows employee last names, department IDs, and department names. Include all employees even if they are not assigned to a department.

----select d.department_name,e.last_name,e.department_id
from departments d,employees e
where e.department_id=d.department_id(+);

3. Modify the query in problem 2 to return all the department IDs even if no employees are assigned to them.

----select d.department_name,e.last_name,e.department_id
from departments d,employees e
where e.department_id(+)=d.department_id;

4. There are one or more errors in each of the following statements. Describe the errors and correct them.

a. WHERE e.department_id(+) = d.department_id (+);
b. SELECT e.first_name,e.last_name, d.department_name, e.department_id

FROM employees e, departments d
WHERE e.department_id = d.department_id;
c. WHERE e.position >= p.highest and<=p.lowest;
d. SELECT e.employee_id, e. last_name, d. location_id

FROM employees e, departments d
WHERE e.department_id = d.department_id;

5. Create a query that will show all CD titles and song IDs in the DJ on Demand database even if there is no CD number in the track-listings table.

----select dc.cd_number,dc.title,dt.cd_number
from d_cds dc,d_track_listings dt
where dc.cd_number(+)=dt.cd_number;

Monday, April 10, 2006

SECTION 3 LESSON 3
1. Create a join based on the cost of the event between the DJs on Demand tables D_EVENTS and D_PACKAGES. Show the name of the event and the code for each event.

---- select de.name,dp.code
from d_events de, d_packages dp
where de.cost between dp.low_range and dp.high_range;

2. Using the Oracle database, create a query that returns the employee last name, salary, and job-grade level based on the salary. Select the salary between the lowest and highest salaries.

----select e.last_name,e.salary, jg.grade_level
from employees e, job_grades jg
where salary between lowest_sal and highest_sal

3. What condition requires creation of a nonequijoin?

---- Since there is no exact match between the two columns in each table.

4. Rewrite the following nonequijoin statement using the logical condition operators (AND, OR, NOT):
WHERE a.ranking BETWEEN g.lowest_rank AND g.highest_rank

----ranking >= g.lowest_rank and <= g.highest_rank

5. How do you know when to use a table alias and when not to use a table alias?

----the column is so long, and the column has to be used many times

6. What is the purpose of the WHERE clause in a join?

----provide the condition

7. What kind of join would you use if you wanted to find data between a range of numbers?

----nonequijoin

8. What kind of join would you use if you wanted to join two tables on any rows that have the same name and the same data type.

----equijoin