________________
A Practice Solutions ________________
Practice 1: Solutions
Part 1
Test your knowledge:
1. Initiate an i SQL*Plus session using the user ID and password that are provided by the
instructor.
2.i SQL*Plus commands access the database.
True/False
3. The following SELECT statement executes successfully:
SELECT last_name, job_id, salary AS Sal
FROM  employees;
True/False
4. The following SELECT statement executes successfully:
SELECT *
FROM  job_grades;
True/False
5. There are four coding errors in this statement. Can you identify them?
SELECT    employee_id, last_name
sal x 12  ANNUAL SALARY
FROM      employees;
• The EMPLOYEES table does not contain a column called sal. The column is called SALARY.
• The multiplication operator is *, not x, as shown in line 2.
• The ANNUAL SALARY alias cannot include spaces. The alias should read ANNUAL_SALARY or should be enclosed in double quotation marks.
•    A comma is missing after the LAST_NAME column.
Part 2
You have been hired as a SQL programmer for Acme Corporation. Your first task is to create some reports based on data from the Human Resources tables.
6. Your first task is to determine the structure of the DEPARTMENTS table and its contents. DESCRIBE departments
SELECT *
FROM  departments;
7. You need to determine the structure of the EMPLOYEES table.
DESCRIBE employees
The HR department wants a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias
STARTDATE for the HIRE_DATE column. Save your SQL statement to a file named
lab_01_07.sql so that you can dispatch this file to the HR department.
SELECT employee_id, last_name, job_id, hire_date StartDate
FROM  employees;
8. Test your query in the file lab_01_07.sql to ensure that it runs correctly.
SELECT employee_id, last_name, job_id, hire_date StartDate
FROM  employees;
truncate带查询9. The HR department needs a query to display all unique job codes from the EMPLOYEES
table.
SELECT DISTINCT job_id
FROM  employees;
Part 3
If you have time, complete the following exercises:
10. The HR department wants more descriptive column headings for its report on employees.
Copy the statement from lab_01_07.sql to the iSQL*Plus Edit window. Name the
column headings Emp #, Employee, Job, and Hire Date, respectively. Then run your query again.
SELECT employee_id "Emp #", last_name "Employee",
job_id "Job", hire_date "Hire Date"
FROM  employees;
11. The HR department has requested a report of all employees and their job IDs. Display the
last name concatenated with the job ID (separated by a comma and space) and name the column Employee and Title.
SELECT last_name||', '||job_id "Employee and Title"
FROM  employees;
If you want an extra challenge, complete the following exercise:
12. To familiarize yourself with the data in the EMPLOYEES table, create a query to display all
the data from the EMPLOYEES table. Separate each column output by a comma. Name the column title THE_OUTPUT.
SELECT employee_id || ',' || first_name || ',' || last_name
|| ',' || email || ',' || phone_number || ','|| job_id
|| ',' || manager_id || ',' || hire_date || ','
|| salary || ',' || commission_pct || ',' || department_id
THE_OUTPUT
FROM  employees;
Practice 2: Solutions
The HR department needs your assistance with creating some queries.
1. Because of budget issues, the HR department needs a report that displays the last name and
salary of employees earning more than $12,000. Place your SQL statement in a text file named lab_02_01.sql. Run your query.
SELECT  last_name, salary
FROM    employees
WHERE  salary > 12000;
2. Create a report that displays the last name and department number for employee number 176. SELECT  last_name, department_id
FROM    employees
WHERE  employee_id = 176;
3. The HR departments needs to find high-salary and low-salary employees. Modify
lab_02_01.sql to display the last name and salary for all employees whose salary is not in the range of $5,000 to $12,000. Place your SQL statement in a text file named
lab_02_03.sql.
SELECT  last_name, salary
FROM    employees
WHERE  salary NOT BETWEEN 5000 AND 12000;
4. Create a report to display the last name, job ID, and start date for the employees with the last
names of Matos and Taylor. Order the query in ascending order by start date.
SELECT  last_name, job_id, hire_date
FROM    employees
WHERE    last_name IN ('Matos', 'Taylor')
ORDER BY hire_date;
5. Display the last name and department number of all employees in departments 20 or 50 in
ascending alphabetical order by name.
SELECT  last_name, department_id
FROM    employees
WHERE    department_id IN (20, 50)
ORDER BY last_name ASC;
Practice 2: Solutions (continued)
6. Modify lab_02_03.sql to list the last name and salary of employees who earn between
$5,000 and $12,000 and are in department 20 or 50. Label the columns Employee and Monthly Salary, respectively. Resave lab_02_03.sql as lab_02_06.sql. Run the statement in lab_02_06.sql.
SELECT  last_name "Employee", salary "Monthly Salary"
FROM    employees
WHERE    salary  BETWEEN 5000 AND 12000
AND      department_id IN (20, 50);
7. The HR department needs a report that displays the last name and hire date for all employees
who were hired in 1994.
SELECT  last_name, hire_date
FROM    employees
WHERE    hire_date LIKE '%94';
8. Create a report to display the last name and job title of all employees who do not have a
manager.
SELECT  last_name, job_id
FROM    employees
WHERE    manager_id IS NULL;
9. Display the last name, salary, and commission for all employees who earn commissions. Sort
data in descending order of salary and commissions.
SELECT  last_name, salary, commission_pct
FROM    employees
WHERE    commission_pct IS NOT NULL
ORDER BY salary DESC, commission_pct DESC;
10. Members of the HR department want to have more flexibility with the queries that you are
writing. They would like a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt. (You can use the query created in practice exercise 1 and modify it.) Save this query to a file named
lab_02_10.sql.
SELECT  last_name, salary
FROM    employees
WHERE  salary > &sal_amt;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。