Wednesday, May 24, 2006

SECTION 6
LESSON 1
1. In the SQL query shown below, which of the following is True about this query?
____T___a. Kimberly Grant would not appear in the results set.
___F____b. The GROUP BY clause has an error because the manager_id is not listed in the SELECT clause.
___T____c. Only salaries greater than 16001 will be in the result set.
___T____d. Names beginning with Ki will appear after names beginning with Ko.
___F____e. Last names such as King and Kochhar will be returned even if they don't have salaries > 16000.
SELECT last_name, MAX(salary)

FROM employees
WHERE last_name LIKE 'K%'GROUP BY manager_id, last_name
HAVING MAX(salary) >16000
ORDER BY last_name DESC

2. Each of the following SQL queries has an error. Find the error and correct it. Use HTML DB to verify that your corrections produce the desired results.
a. SELECT manager_id
FROM employees
Having AVG(salary) <1600
GROUP BY manager_id;
b. SELECT cd_number, COUNT(title)

FROM d_cds
WHERE cd_number <>
GROUP BY CD_NUMBER;
c. SELECT ID, MAX(ID), artist AS Artist

FROM d_songs
WHERE duration IN('3 min', '6 min', '10 min')
AND ID <>
GROUP by ID;
d. SELECT loc_type, rental_fee AS Fee

FROM d_venues
WHERE id <100>
GROUP BY rental_fee,loc_type;

3. Rewrite the following query to accomplish the same result:
SELECT DISTINCT MAX(song_id) FROM d_track_listingsWHERE track IN ( 1, 2, 3);


----select max(distinct song_id)
from d_track_listings
where track in (1,2,3);

4. Your teacher needs an alphabetized list of all students?first and last names and the class average for Test 1. She would like the results grouped by gender (male and female), but only show male students whose grade level is 10. Write pseudocode for the SQL statement needed to accomplish the task.

----select first_name,last_name,avg(grade)
from class
where grade=10 and gender='male'
group by gender;
5. Indicate True or False

__T___a. If you include a group function and any other individual columns in a SELECT clause, then each individual column must also appear in the GROUP BY clause.
__F___b. You can use a column alias in the GROUP BY clause.
__T___c. The GROUP BY clause always includes a group function.
LESSON 2
Complete the practice exercises.Mark T or F for each of the following questions:
__F___1. The CONCAT character function combines two or more character values into one character string.
__T___2. The SYSDATE function returns the Oracle Server date and time.
__T___3. The RPAD character function returns a right-justified value with a string of characters replicated to fill a specified number of character positions.
__T___4. The ROUND number function will round 469.89 to 470 if the decimal place value is omitted.
__T___5. The SUBSTR character function returns a portion of a string beginning at the start of a string to a specified number of characters.
__T___6. DESC will order alphabetical data from Z to A.
__F___7. The column name in a SELECT statement is an example of a SELECTION.
__T___8. You cannot use a column alias in the WHERE clause.
__F___9. To specify an alias in the SELECT clause for retired employee, use 'Retired Employee.'
__F___10. Aliases always appear in UPPERCASE, unless enclosed within proper quote marks.

__F___11. If any column value in an arithmetic expression is NULL, the result defaults to 0.
__F___12. Null values are treated as zero when using SUM functions.
__F__13. In arithmetic expressions, multiply and divide are evaluated after addition and substraction.
__F__14. By default, column headings are returned in UPPERCASE.
__T__15. SQL statements are not case-sensitive, unless indicated.

__T__16. SQL statements can be entered on one or more lines.
__T__17. SQL keywords can be abbreviated using single quotes.
__F__18. Columns are displayed in the order in which they appear in the WHERE clause.
__F__19. Column aliases that are more than one word require the AS prefix.
__F_20. In the SELECT clause, if an expression is not a column name or a column alias, it is a literal value.

__F__21. Number and date values must be enclosed in single quotes.
__F__22. The keyword DISTINCT will affect all of the columns in the statement: SELECT DISTINCT(employee_id, department_id,department_name)
__T__23. HTML DB is a type of application.
__F__24. VARCHAR2 describes variable-length character values.
__F__25. To restrict the rows returned from a query, use the SELECT clause.
__T__26. The comparison operator IN is used to match any of a list of items.
__T__27. Using WHERE employee_id = NULL returns all employees where do not have an employee ID.
__T__28. BETWEEN 250 AND 350 will return 101 values.
__F__29. LIKE '_ _ e%' could return 'Fred.'
__T_30. To return SA_MAN, use LIKE ' SA /_%' ESCAPE ' \ '
__T_31. Logical operators are evaluated in the AND, OR, NOT order.
__F__32. WHERE e.last_name LIKE 'Sm%' OR d.department_id = 60 AND d.department_name = 'IT' could return anyone from the IT department whose department ID is 60.
__F_33. The rules of precedence would evaluate an expression that contained each of the following in the order: arithmetic, concatenation, NOT, OR
__F__34. The ORDER BY clause comes last in the SELECT statement.
__F__35. In SELECT last_name, first_name, the ORDER BY clause could include department_id.
__F__36. The order of execution of a SELECT statement is: FROM, WHERE, SELECT, ORDER BY
__F__37. ADD_MONTHS(hire_date, 6) will return a number.
__F__38. MONTHS BETWEEN ('05-DEC-89' , '10-JUN-93') will return 4.
__T__39. If April 21st is a MONDAY, then NEXT_DAY('21-APR'-03', 'FRIDAY') will return 25-APR-03.
__F__40. ROUND(SYSDATE, 'YEAR') will return 01-JAN-05 if today is August 23, 2004.
__T__41. All group functions ignore NULL values.
__F__42. SELECT COUNT( last_name, first_name) will return the number of last and first names in the database.
__F__43. If salary values in a column are 5000, null, 4000, 7000, using AVG(salary) will return 4000.
__T__44. AVG(DISTINCT salary) will average only the salaries that are different from each other.
__T__45. NULLIF(salary, 4000) will return 4000 if the employee's salary is 5000.
__T_ 46. COALESCE(hire_date, salary, commission_pct) will return salary if the hire date is NULL.
__T_ 47. The HAVING clause is used to restrict groups.

__F_48. a GROUP BY clause is used, any column that is in the SELECT clause that is not a group function must be listed in the GROUP BY clause.
__F__49. If a query lists clauses WHERE, GROUP BY, and HAVING, no ORDER BY clause can be used.
__T__50. If a join is needed and you want to use a WHERE clause, choose a JOIN ON or JOIN USING join.
__T__51. COUNT(DISTINCT department_id) will return the number of different department IDs.
__F__52. If a column had 4 NULL employee IDs and 250 defined IDs, COUNT will return 254.

LESSON 5
1. You need to know the title and artist of all DJs on Demand songs that have the same type code as the song, "I'm Going to Miss My Teacher."

----select title,artist
from d_songs
where type_code=(select type_code from d_songs where title='I'm Going to Miss My Teacher');

2. What Oracle employees have a salary greater than the maximum salary of any ST_CLERK?

----select job_title
from jobs
where salary>(select max_sslary from jobs where job_title='ST_CLERK');

3. What DJs on Demand d_play_list_items song_id's have the same event_id as song_id 45?

----select event_id
from d_play_list_items
where event_id=(select event_id from d_play_list_items where song_id=45);

4.Which events in the DJs on Demand database cost more than event_id = 100?

----select name
from d_events
where cost>(select cost from d_events where event_id=100);

5. Find the track number of the song that has the same CD number as "Party Music for All Occasions."

----select cd_number
from d_track_listings
where song_id=(select id from d_songs where title='Party Music for All Occasions');

6. List the DJs on Demand events whose theme code is the same as the code for "Tropical."


----select name
from d_event
where theme_code=(select code from d_themes where description='Tropical');

7. What are the names of the Global Fast Foods staff members whose salary is greater than the staff member whose ID is 12?

----select last_name,first_name
from f_staffs
where salary>(select salary from f_staffs where id=12);

8. What are the names of the Global Fast Foods staff members whose staff type is not the same as Bob Miller's?

----select last_name,first_name
from f_staffs
where staff_type!=(select staff_type from f_staffs where last_name='Miller' and first_name='Bob');

9. What is the staff type for those Global Fast Foods jobs that have a salary less than those of any Cook staff-type jobs?

----select staff_type
from f_staffs
where salary<(select salary from f_staffs where staff_type='Cook');


10. What DJs on Demand events have the same theme code event ID = 100?

---- select name
from d_events
where theme_code=(select theme_code from d_evnets where id=100);

11. Which Oracle employees have the same department ID as the IT department?

----select last_name,first_name
from employees
where department_id=(select department_id from departments where department_name='IT');

12. What are the department names of the Oracle departments that have the same location ID as Seattle?

----select department_id
from departments
where location_id=(select location_id from locations where city='Seattle');

13. Return the department ID and minimum salary of all employees, grouped by department ID, having a minimum salary greater than the minimum salary of those employees whose department ID is not equal to 50.

----select department_id, min(salary)
from employees where min(salary)>(select min(salary) from employees
where department_id!=50)
group by department_id;

14. Which statement(s) regarding subqueries is/are true?
a. It is good programming practice to place a subquery on the right side of the comparison operator.
b. A subquery can reference a table that is not included in the outer query's FROM clause.
c. Single-row subqueries can return multiple values to the outer query.


----a

1 Comments:

Blogger Henry Ruan said...

You did some of them. Try to finish them. Thanks.

11:24 AM  

Post a Comment

<< Home