Retrieving Data using the SQL SELECT Statement

Using the SQL SELECT statement & Column aliases & Arithmetic expressions

I will use the test database included in Oracle Xe called HR:

Some examples o Select:

-- Selection all from employees table:
SELECT * FROM EMPLOYEES;

--Selection name, phone from employees:
SELECT FIRST_NAME, PHONE_NUMBER FROM EMPLOYEES;

-- Select street, city and state and using alias:
SELECT STREET_ADDRESS AS "Dirección", CITY AS CIUDAD, STATE_PROVINCE AS ESTADO FROM LOCATIONS;

— Examples o Arithmetic expressions:

SELECT FIRST_NAME, SALARY AS "Bruto", SALARY*0.2 AS "Impuestos", SALARY-(SALARY*0.2 ) AS "Neto" FROM EMPLOYEES;

SELECT FIRST_NAME AS "Nombre", SALARY AS "Salario", SALARY*14 AS "Salario Anual" FROM EMPLOYEES;

Using concatenation operator, literal character strings, and the DISTINCT keyword

Using CONCATENATION

-- ADDING A STRING BEFORE THE NAME IN A NEW COLUMN:
SELECT 'NOMBRE:', FIRST_NAME FROM EMPLOYEES;

-- THE SAME BUT CONCATENATED IN THE SAME COL. AND ADDING LAST NAME:
SELECT 'NOMBRE:' || FIRST_NAME || ' ' || LAST_NAME AS "Nombre Empleado" FROM EMPLOYEES;

-- CREATE THE SENTENCE SO THAT THE OUTPUT IS LIKE: El empleado Steven del departamento 90 tiene un salario de 24000
SELECT 'El empleado ' || first_name || ' del departamento ' || department_id || ' tiene un salario de ' || salary from employees;

-- CREATE THE SENTENCE SO THAT THE OUTPUT IS LIKE: La calle 1297 Via Cola di Rie pertenece a la ciudad: Roma
SELECT 'La calle ' || street_address || ' pertenece a la ciudad: ' || city as "Calle y Ciudad" from locations;

Using DISTINCT command

-- LIST ALL DEPARTMENTS
SELECT DEPARTMENT_ID FROM EMPLOYEES;

-- LIST ALL DEPARTMENTS WITHOUT DUPLICATES.
SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;

-- SELECT THE CITIES FROM THE LOCATIONS TABLE WITHOUT DUPLICATES
SELECT DISTINCT CITY FROM LOCATIONS;

--SELECT THE DIFFERENT TYPES OF JOB_ID BY DEPARTMENT FROM THE EMPLOYEES TABLE
SELECT DISTINCT DEPARTMENT_ID, JOB_ID FROM EMPLOYEES;

Using alternative quote operator

SELECT first_name, || q'{ salary's is }' || salary AS "Income" FROM employees;

Using  NULL values in the SELECT statement

SELECT * FROM TABLE WHERE COLUMNX is NULL;

SELECT count(COLUMNX) FROM TABLE WHERE COLUMNX is NULL;

Leave a Reply

Your email address will not be published. Required fields are marked *