Skip to content

SQL functions

ERD

Although SQL Developer provides some features for building database models using different types of diagram, they are quite complicated to use and we will be avoiding them. However, it is often useful to have a visual representation of the schema you are using. SQL Developer provides a query-by-example (QBE) interface for manipulating data by point-and-click which can do this in a simple way.

Click on the Query Builder tab as shown in the graphic below (1), highlight all of the tables in the HR schema (2) and drag them into the query builder pane (3). SQL Developer will use the information about the table constraints to build a simple ERD. You can then improve the layout by dragging the tables to avoid crossing relationship lines, etc.

SQLD QBE

Take a screenshot of the table layout (CTRL + Print Scrn) for later reference. Once you start writing queries in the Worksheet tab, SQL Developer will update the tables in the Query Builder.

Practice with SQL functions

Produce queries that satisfy the following requirements and execute them in the SQL Developer Worksheet. You may need to refer to the Oracle SQL language reference to find out how some of the functions are formatted.

  1. Assuming that the email addresses of all employees end in @hr.co.uk, list all full email addresses making sure that they are entirely in lowercase.

  2. Display the message Salaries in the company range from $x to $y per month. Replace x and y with the correct values from the EMPOYEES table.

  3. List the names of all the programmers in alphabetical order by surname in the format Surname, First_initial

  4. List each department id and the average salary for that department.

  5. Repeat the last problem, but display the average salary in thousands with one decimal place (eg 4150 would be displayed as 4.2).

  6. List all jobs with their respective minimum and maximum salaries, and also show the range of salaries for each job.

  7. Repeat the last query ordering the jobs in descending order of range (ie. from the one with highest range to the lowest).

  8. For each year, show the number of employees that were hired. Put the results in date order (Remember to use the Oracle date functions).

  9. List the surnames of all sales reps and their department id. If the department id is null, display zero.

  10. Repeat the previous query, but if the department id is null display the message ‘No dept id’ instead of zero.

Check your answers

Check your answers

Further reading

Oracle function reference