In Oracle SQL Developer, you can work on the exercise focuses on HR, the sample human resources database you activated above. Before starting the exercise, explore the HR database schema, using these helpful hints as needed.

• Study the HR database schema linked here: Database Sample Schemas (look under Schema Diagrams).

• List all of HR users tables by logging in as the hr user and running SELECT table_name FROM dba_tables WHERE owner = HR and SELECT view_name FROM dba_views WHERE owner= HR ; .

• Based on results of previous queries, list tables and views included in HR schema areo JOBS

  o EMPLOYEES

o JOB_BRADES o COUNTRIES o LOCATIONS o DEPARTMENTSo JOB_HOSTORYo REGIONS

o EMP_DETAILS_VIEW Display a detailed table schema using DESCRIBE table name;

START of LAB 02 Exercise

Create a file to record SQL queries, results and explanations on following questions. Upon the completion of lab works, convert the file to LAB02.PDF

1. 1 List all the rows of the departments table.

2. 2 Find the number of employees in the database (hint: use the COUNT() aggregate function

   for this); List the employees who: a. have a salary greater than 15000, b. were hired between January 1, 2002 and January 1, 2005, c. have a phone number that doesn't start with 515

3. 3 List the names of the employees who are in the finance department. Try to format the names as firstname lastname using concatenation (i.e., ||) and order them alphabetically.

4. 4 List the city, state and country name for all locations in the Asian region.

5. 5 List the locations that have no state or province specified in the database.

6. 6 Create a query to display the highest, lowest, sum and average salary of all employees.

   Label the columns Maximum, Minimum, Sum and Average, respectively.

7. 7 Oracle provides a default table named dual, which you can use to verify that Oracle is up

   and running. Try to figure out the schema and the data values stored in dual. Give examples of why one might want to user such a table.