Oracle SQL online course — lesson 1
In this lesson in the Oracle SQL course, you will log into Oracle Live SQL and write your first SQL query. Oracle Live SQL is for free. You get instant access to the Oracle Database in the cloud.
Enjoy the reading!
Preparing the environment
- Open your web browser and go to https://livesql.oracle.com
2. Click the Start Coding Now button
3. You will be redirected to login page. Sign in with your OTN account or create one if necessary.
4. After logging in, an SQL Worksheet is displayed. That means you can write your first SQL query using Oracle Live SQL! :)
5. Write a query (1) and click the Run button (2). You will find the results in the bottom area of an SQL worksheet (3).
Congratulations. You have successfully executed your first SQL query :)
SELECT statement
It is used to retrieve data from one or more tables. Data is being processed “on the fly” and the result is not stored in the database.
In Oracle, each query must consist of at least SELECT and FROM.
After SELECT, list the columns you want to display. You can also use expressions like 2+2. If you use an asterisk after SELECT, all columns will be displayed.
FROM clause lets you specify the tables from which data is selected.
What tables can you use?
We will use tables from the HR user. The HR user contains tables that are used to store company information:
employees — information about employees
locations — information about locations
regions — regions used for the location of countries
countries — country information for department locations
departments — information about departments
jobs — information about job positions
job_history — contains historical information about employees — what positions they have worked in
We will use these tables in the following examples.
Example 1. Select all records from the employees table. Please note that the table is Table is qualified with the HR schema in the FROM clause.
SELECT * FROM hr.employees;
Example 2. Select the First Name and Last Name columns from the Employees table
SELECT first_name, last_name FROM hr.employees;
Example 3. Select the city and state_province columns from the locations table.
SELECT city, state_province FROM hr.locations;
Notice that the state_province column cointains NULL values (no value has been assigned to the record). How do you turn it into the ‘none’ word? You can use the NVL function.
NVL function
NVL(p1, p2) — if p1 is NULL, then it returns p2. If p1 is not NULL, then it returns p1.
So let’s use it.
Example 4. Select the city and state_province columns from the locations table. Replace NULL values in state_province column with ‘none’ word
SELECT city, nvl(state_province, ‘none’) FROM hr.locations;
Does the result look nice enough? Not really. The second column is called NVL(STATE_PROVINCE,’none’). So what about the name of the column?
Aliases are used in the column heading of the result set.
SELECT column1 alias, column2 AS alias2 FROM table;
The AS keyword is optional.
Example 5. Continuing the previous example, name the city column as city and the state_province column as state_province.
SELECT city city, nvl(state_province, ‘none’) state_province FROM hr.locations;
Let’s see how math calculations work.
Example 6. Display the First Name, Last Name and Salary columns. In addition, the value of the salary increases for each employee after the increase by 15% as an additional column. Let’s call the last column — increase.
SELECT first_name, last_name, salary, salary*1.15 increase FROM hr. employees;
You can use arithmetic calculations (*, /, +, -). Brackets may also be used.
How to return only one copy of each set of duplicate rows selected?
If you want to select values without duplicates, you can use the DISTINCT keyword. The DISTINCT keyword is located right after the SELECT clause.
Example 7. Display the job_id column from the employees table by displaying the values without duplicates.
SELECT DISTINCT job_id FROM hr.employees;
Try to run the query without the DISTINCT keyword — you will see the difference.
What’s the next lesson?
In the next lesson, you will learn how to restrict the rows selected to those that satisfy one or more conditions.
Authors:
Rafal Stryjek
Tomasz Strawki
