QUERYING THE HR SCHEMA

In this section, you execute the SELECT statement to query tables in the HR schema. You also use the ORDER BY and WHERE clauses within the SELECT statement to sort and restrict data in the result set.

 

Querying Tables

In this section, you execute the SELECT statement to retrieve data from tables and views. You can select rows and columns that you want to return in the output. In its simplest form, a SELECT statement must contain the following:

• A SELECT clause, which specifies columns containing the values to be matched
• A FROM clause, which specifies the table containing the columns listed in the SELECT clause
Syntax: SELECT {*|[DISTINCT] column|expression [alias],...}
      FROM    <table>;

You can display all columns of data in a table by entering an asterisk (*) after the SELECT keyword. Execute the following statement to view all rows and columns in the DEPARTMENTS table:
SELECT *
FROM departments;




You can display specific columns of data in a table by specifying the column names in the SELECT statement. Execute the following statement to view the JOB_ID and JOB_TITLE columns in the JOBS table:
SELECT job_id, job_title
FROM jobs;



 

Restricting Data

In this section, you use the WHERE clause to restrict the rows that are returned from the SELECT query. A WHERE clause contains a condition that must be met. It directly follows the FROM clause. If the condition is true, the row that meets the condition is returned.


Modify the SELECT statement. Execute the following query to restrict the number of rows to DEPARTMENT_ID 60:
SELECT *
FROM departments
WHERE department_id=60;



 

Sorting Data

In this section, you use the ORDER BY clause to sort the rows that are retrieved from the SELECT statement. You specify the column based on the rows that must be sorted. You also specify the ASC keyword to display rows in ascending order (default), and you specify the DESC keyword to display rows in descending order.


Execute the following SELECT statement to retrieve the LAST_NAME, JOB_ID, and HIRE_DATE columns of employees who belong to  the SA_REP job ID. Sort the rows in ascending order based on the HIRE_DATEcolumn.
SELECT last_name, job_id, hire_date
FROM   employees
WHERE  job_id='SA_REP'
ORDER BY hire_date;



Modify the SELECT statement to display rows in descending order. Use the DESC keyword.
SELECT last_name, job_id, hire_date
FROM   employees
WHERE  job_id='SA_REP'
ORDER BY hire_date DESC;