lesphomework

section 5

2006-04-23T12:41:00.000-07:00

SQL - Section 5
Lesson 4
1. Create a query that will show the average cost of the DJ on Demand events. Round to two decimal places.
Select round(avg(cost),2) from d_events;

2. Find the average salary for Global Fast Foods staff members whose manager ID is 19.
Select avg(salary) from f_staffs where manager_id=19;

3. Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9.
Select sum(salary) from f_staffs where id in(9, 12);

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.
Select min(salary), max(hire_date), max(last_name), min(last_name) from employees 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?

SELECT sum(total_sales)FROM orders; 1

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?

March 30, 1969

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.

Select avg(order_number) from f_orders where order_date between '01-Jan-02' and'31-Dec-02';
9. What was the hire date of the last Oracle employee hired?
Select max(hire_date) from employees
1
0. In the following SELECT clause, which value returned by the SELECT statement will be larger?
SELECT SUM(operating_cost), AVG(operating_cost)
Sum(operating_cost)

Lesson 5
1. How many songs are listed in the DJs on Demand D_SONGS table?
Select count(title) from d_songs;
6
2. In how many different location types has DJs on Demand had venues?
Select count(distinct loc_type) from d_venues;
4
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) from d_track_listings;
5, 4
4. How many of the DJs on Demand customers have email addresses? Select count(email) from d_clients;
3
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?Select count(distinct auth_expense_amt) from d_partners;
1
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;
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.
Select count(auth_expense_amt), round(avg(nvl(auth_expense_amt, 0)), 2) from d_partners;
8. Refer to the DJs on Demand database D_EVENTS table shown.
Which clauses represent valid statements?From_____a. FROM event_date Sum______b. SELECT SUM(cost) Sum______c. SELECT SUM(event_date) Avg______d. SELECT description, AVG(cost) AS "Expense"Where______e. WHERE MIN(id) = 100Max______ f. SELECT MAX(AVG(cost) Min_____ g. SELECT MIN(event_date)
9. Which statement(s) is/are True about the following SQL statement:SELECT AVG(NVL(selling_bonus, 0.10))FROM bonuses; T_____a. The data types of the values in the NVL clause can be any data type except date data._____b. If the selling_bonus column has a null value, 0.10 will be substituted.T_____c. There will be no null values in the selling_bonus column when the average is calculated.F _____d. This statement will cause an error. There cannot be two functions in the SELECT statement.
10. Which of the following statements is/are TRUE about the following query?SELECT DISTINCT colors, sizesFROM items;T_____a. Each color will appear only once in the results set.F_____b. Each size will appear only once in the results set.F_____c. Unique combinations of color and size will appear only once in the results set.T_____d. Each color and size combination will appear more than once in the results set.

section 4

2006-04-23T12:17:00.000-07:00

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.

section 3 lesson 2

2006-03-14T10:02:00.000-08:00

1 select *from d_play_list_items,d_track_listings
2 select *from d_play_list_items,d_track_listings
where d_play_list_items.song_id=d_track_listings.song_id
3 select d_songs.title, d_songs.artist, d_types.description from d_songs,d_types
4 select d_songs.title, d_songs.artist, d_types.description from d_songs,d_types
where id in (47,48)
5 select * from d_clients , d_events, d_job_assignments
6. select d_track_listings.song_id, d_cds.titlefrom d_track_listings, d_cds

section 2 4

2006-03-07T10:22:00.000-08:00

select first_name,last_name,zip
from F_CUSTOMERS
where length(zip)<10

lesson 4

2006-03-05T18:04:00.000-08:00

1.select ROUND(86.678,-2) from dual
2.select upper(title) from where cd_number in (90,91)
3.select last_nameupper(substr(first_name,1,1)) as "User Passwords" from
4.select LOWER(REPLACE('It's a small world', 'It's a small','hello')) from dual
5. select substr('fiddledeedum',10,3)substr('fiddledeedee',1,6) from dual
6. select replace('Mississippi','i','$') from dual
7. select round(5332.342,3) from dual
8. select round(3.14159,-2) from dual
9. select trunc(73.892,-1) from dual
10. select next_day(add_mounths(sysdate, 6),'Firday') from dual
11. select add_months(sysdate,120) as "Future" from dual
12. select add_months('29-FEB-04',48) as "Future" from dual
13. select themes from where themes="%ie%"14. select title, year from where year between 2000 and 2003

section 3

2006-02-25T19:08:00.000-08:00

select round('event_date','month'),months_between(sysdate,'event_date')
from dual

select (months_between(vacation_date,start_date)*30.5)as"Days"
from dual;

select (months_between('01-JAN-06','31-DEC-06')/12)*365
from dual;

select trunc(round(sysdate,'month'),'month')as alias,trunc(round(sysdate,'year'),'year')as alias
from dual;

select last_day('01-JUN-05')as aliasfrom dual;

select round(months_between(sysdate,birthday)/12,'year')
from dualwhere name='Bob Miller';

select add_months(sysdate,6) as"Appointment"
from dual;

select months_between('27-MAY-06','01-JAN-07')
from dual;

-select next_day('27-MAY-06','friday')as"First Friday"
from dual;

section 1 lesson 2

2006-02-17T07:29:00.000-08:00

select last_name,salary,round(salary/1.55,2)
from employees
where employee_id between 100 and 102

select last_name,trunc(salary+salary*0.0533,2)
from employeeswhere department_id=80;

select mod(38873,2)
from dual;

select round(845.553,1)
from dual;

elect round(30695.348,2)
from dual;

select round(30695.348,-2)
from dual;

select trunc(2.3454,1)
from dual;

select last_name,salary
from employeeswhere mod(salary,3)=0

select mod(34,8)as"EXAMPLE"
from dual;

section 1 lesson 1

2006-02-14T10:17:00.000-08:00

1.select concat(concat( 'Oracle',' Internet'),' Academy')
from dual

2.select substr ('Oracle Internet Academy',13,3) as "The Net"
from dual

3.SELECT LENGTH ('Oracle Internet Academy')
from DUAL

4.SELECT INSTR('Oracle Internet Academy', 'I')
from DUAL

5.SELECT lpad('Oracl',9,'*')rpad('e',5,'*')rpad('Internet',12,'*')rpad('Academy',12,'*')
from DUAl

6.SELECT 'Oracle'Lpad('Internet',11,'$')Lpad('Academy',10,'$')
from DUAl

7.select replace('Oracle Internet Academy','Internet','2004-2005')as "The best Class"
from dual

8.SELECT order_date,LPAD(order_total,7,'$') AS"TOTAL"
FROM F_ORDERS

9.SELECT CONCAT(FIRST_NAME, LAST_NAME)'_'ADDRESS ' 'CITY' 'STATE' 'ZIP
FROM F_CUSTOMERS
WHERE ID=456

2005-09-12T09:40:00.000-07:00