Review Answers for Chapter 3

Answers to Exercises › Review Answers for Chapter 3

Review Answers for Chapter 3

Exercise 3-1 Answer

This is the answer for Exercise 3-1.

select *
       from skill;

Exercise 3-2 Answer

This is the answer for Exercise 3-2.

select skill_id, skill_name
       from skill;

Exercise 3-3 Answer

This is the answer for Exercise 3-3.

select emp_fname, emp_lname, street, city
       from employee;

Exercise 3-4 Answer

This is the answer for Exercise 3-4.

select dept_id as "Department ID", dept_name as "Name"
       from department;

Exercise 3-5 Answer

This is the answer for Exercise 3-5.

select emp_id as "Employee",
       salary_amount as "Salary",
       bonus_percent as "Bonus Percentage",
       bonus_percent * salary_amount as "Bonus Paid"
       from position;

Exercise 3-6 Answer

This is the answer for Exercise 3-6.

select distinct city
       from employee;

Exercise 3-7 Answer

This is the answer for Exercise 3-7.

select emp_id, emp_lname
       from employee
       order by emp_lname desc;

Exercise 3-8 Answer

This is the answer for Exercise 3-8.

select skill_id, skill_name
       from skill
       order by skill_id;

Exercise 3-9 Answer

This is the answer for Exercise 3-9.

select dept_id, emp_lname, emp_id
       from employee
       order by dept_id, emp_lname;

Exercise 3-10 Answer

This is the answer for Exercise 3-10.

select emp_id as "Employee",
       salary_amount as "Base Salary",
       bonus_percent as "Bonus Percentage",
       bonus_percent * salary_amount as "Bonus Paid"
       from position
       order by 4;

Review Answers

These are the answers for Review.

How many columns would be retrieved by the following statement:
```
select * from sample_table;
```
- c. All
How can you limit the number of columns returned by your SELECT statement?
- b. List the columns you want to see
How can you give heading names to columns?
- b. Use AS after the column name and specify the heading
Given a table called SUPPLY_PRICE and a column in that table called PART_NUMBER, which of the following statements will find the number of unique part numbers in the table?
- c. select distinct part_number from supply_price;
How can you name the column you want to sort by?
- a. Use the column name
- b. Use the heading name
- d. Use the column number

Scenario Answers

These are the answers for Scenarios.

You need to list all jobs the company has for a government screen. The screen should show job ID, job title, and minimum and maximum rate for the job. Use the JOB table, checking Appendix C for table descriptions.

select job_id, job_title, min_rate, max_rate
       from job;

JOB_ID JOB_TITLE MIN_RATE MAX_RATE ------ --------- -------- -------- 8001 Vice President 90000.00 136000.00 2077 Purch Clerk 17000.00 30000.00 9001 President 111000.00 190000.00 3051 Data Entry Clerk 8.50 11.45 4700 Purch Agnt 33000.00 60000.00 3029 Computer Operator 25000.00 44000.00 6011 Manager - Acctng 59400.00 121000.00 4130 Benefits Analyst 35000.00 56000.00 4666 Sr Mechanic 41000.00 91000.00 4123 Recruiter 35000.00 56000.00 5555 Salesperson 30000.00 79000.00 4025 Writer - Mktng 31000.00 50000.00 4023 Accountant 44000.00 120000.00 2051 AP Clerk 8.80 14.60 4734 Mktng Admin 25000.00 62000.00 5110 CUST SER MGR 40000.00 108000.00 2053 AR Clerk 8.80 14.60 6004 Manager - HR 66000.00 138000.00 5111 CUST SER REP 27000.00 54000.00 4012 Admin Asst 21000.00 44000.00 2055 PAYROLL CLERK 17000.00 30000.00 4560 Mechanic 11.45 21.00 5890 Appraisal Spec 45000.00 70000.00 3333 Sales Trainee 21600.00 39000.00 6021 Manager - Mktng 76000.00 150000.00 25 rows processed

The screen you just created (in Scenario 1) has all the necessary information but is difficult to read because it is not sorted. Modify the SELECT statement to create the same screen sorted by job title.

select job_id, job_title, min_rate, max_rate
       from job
       order by job_title;

JOB_ID JOB_TITLE MIN_RATE MAX_RATE ------ --------- -------- -------- 4023 Accountant 44000.00 120000.00 4012 Admin Asst 21000.00 44000.00 5890 Appraisal Spec 45000.00 70000.00 2051 AP Clerk 8.80 14.60 2053 AR Clerk 8.80 14.60 4130 Benefits Analyst 35000.00 56000.00 3029 Computer Operator 25000.00 44000.00 5110 CUST SER MGR 40000.00 108000.00 5111 CUST SER REP 27000.00 54000.00 3051 Data Entry Clerk 8.50 11.45 6011 Manager - Acctng 59400.00 121000.00 6004 Manager - HR 66000.00 138000.00 6021 Manager - Mktng 76000.00 150000.00 4560 Mechanic 11.45 21.00 4734 Mktng Admin 25000.00 62000.00 9001 President 111000.00 190000.00 4700 Purch Agnt 33000.00 60000.00 2077 Purch Clerk 17000.00 30000.00 2055 PAYROLL CLERK 17000.00 30000.00 4123 Recruiter 35000.00 56000.00 3333 Sales Trainee 21600.00 39000.00 5555 Salesperson 30000.00 79000.00 4666 Sr Mechanic 41000.00 91000.00 8001 Vice President 90000.00 136000.00 4025 Writer - Mktng 31000.00 50000.00 25 rows processed

Periodically, a company list is produced showing each department and all employees assigned to that department. This list should be sorted first by department ID and then by employee ID within each department. Display department ID, employee ID, and employee last name. Create the appropriate SQL SELECT statement to produce this list using the EMPLOYEE table.

select dept_id, emp_id, emp_lname
       from employee
       order by dept_id, emp_id;

DEPT_ID EMP_ID EMP_LNAME ------- ------ --------- 1100 2246 Hamel 1100 4703 Halloran 1100 5008 Fordman 1110 1765 Alexander 1110 2106 Widman 1120 2004 Johnson 1120 2898 Umidy 1120 3294 Johnson 1120 3338 White 2200 2180 Albertini 2200 2448 Lynn 2200 3704 Moore 2200 3767 Lowe 2200 4660 MacGregor . . . 55 rows processed

The screen you just created is very useful except that the headings are difficult to understand. Rewrite the statement so that the column names are "Department", "Employee ID", and "Last Name".

select dept_id as "Department", emp_id as "Employee ID",
       emp_lname as "Last Name"
       from employee
       order by 1, 2;

Department Employee ID Last Name ---------- ----------- --------- 1100 2246 Hamel 1100 4703 Halloran 1100 5008 Fordman 1110 1765 Alexander 1110 2106 Widman 1120 2004 Johnson 1120 2898 Umidy 1120 3294 Johnson 1120 3338 White 2200 2180 Albertini 2200 2448 Lynn 2200 3704 Moore 2200 3767 Lowe 2200 4660 MacGregor . . . 55 rows processed