8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

SQL for Beginners (Part 1) : The SELECT List

This is the first part of a series of articles showing the basics of SQL. In this article we take a look at the type of things you are likely to see in the SELECT list of queries.

Related articles.

Setup

You can perform all these queries online for free using SQL Fiddle.

The examples in this article require the following tables to be present.

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);

INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;


CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.

Wildcard "*"

The "*" is a wildcard. Using this in the SELECT list is like saying, "Bring me back all the columns that are visible in the table". You will often hear people using the expression, "select star", and this is what they mean.

SELECT *
FROM   employees
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                       20
       7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300            30
       7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500            30
       7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                       20
       7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400            30
       7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                       30
       7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                       10
       7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                       20
       7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                       10
       7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0            30
       7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                       20
       7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                       30
       7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                       20
       7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                       10

14 rows selected.

SQL>

You can see from the example above, when we execute the statement, all the data from all the columns is displayed.

The wildcard can also be used for multiple tables in the SELECT list. In the following example we have two tables (EMPLOYEES and DEPARTMENTS) joined together. We've given them both table aliases (e and d) and used those aliases in the SELECT list. When the query is executed, we get all the columns from both tables.

SELECT e.*, d.*
FROM   employees e
       JOIN departments d ON d.department_id = e.department_id
ORDER BY e.employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID DEPARTMENT_ID DEPARTMENT_NAM LOCATION
----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- ------------- -------------- -------------
       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                       20            20 RESEARCH       DALLAS
       7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300            30            30 SALES          CHICAGO
       7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500            30            30 SALES          CHICAGO
       7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                       20            20 RESEARCH       DALLAS
       7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400            30            30 SALES          CHICAGO
       7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                       30            30 SALES          CHICAGO
       7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                       10            10 ACCOUNTING     NEW YORK
       7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                       20            20 RESEARCH       DALLAS
       7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                       10            10 ACCOUNTING     NEW YORK
       7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0            30            30 SALES          CHICAGO
       7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                       20            20 RESEARCH       DALLAS
       7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                       30            30 SALES          CHICAGO
       7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                       20            20 RESEARCH       DALLAS
       7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                       10            10 ACCOUNTING     NEW YORK

14 rows selected.

SQL>

Columns

In many cases we would avoid wildcards and specify the columns by name. In the following example, we are pulling out two columns (EMPLOYEE_ID and EMPLOYEE_NAME) from from the table. When we execute the query we can see only those two columns are returned.

SELECT employee_id, employee_name
FROM   employees
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N
----------- ----------
       7369 SMITH
       7499 ALLEN
       7521 WARD
       7566 JONES
       7654 MARTIN
       7698 BLAKE
       7782 CLARK
       7788 SCOTT
       7839 KING
       7844 TURNER
       7876 ADAMS
       7900 JAMES
       7902 FORD
       7934 MILLER

14 rows selected.

SQL>

Column Aliases

We can alter the names of the columns returned in the result set using column aliases. In the following example we've got two different types. The first alias isn't case sensitive, but the second one, using double-quotes, forces the case of the column name. You can see the column names have changed in output below.

SELECT employee_id AS employee_no, employee_name AS "Name"
FROM   employees
ORDER BY employee_id;

EMPLOYEE_NO Name
----------- ----------
       7369 SMITH
       7499 ALLEN
       7521 WARD
       7566 JONES
       7654 MARTIN
       7698 BLAKE
       7782 CLARK
       7788 SCOTT
       7839 KING
       7844 TURNER
       7876 ADAMS
       7900 JAMES
       7902 FORD
       7934 MILLER

14 rows selected.

SQL>

Table Aliases

Table aliases make it a lot easier to see where the columns have come from. In the following example, we have used "e" as the alias for the EMPLOYEES table and "d" as the alias for the DEPARTMENTS table. Table aliases also prevent ambiguous references to columns. Both the EMPLOYEES and DEPARTMENTS tables have a column called DEPARTMENT_ID. Using the alias shows exactly which table we expect it to come from.

SELECT e.employee_id,
       e.employee_name,
       d.department_id,
       d.department_name
FROM   employees e
       JOIN departments d ON e.department_id = d.department_id
ORDER BY e.employee_id;

EMPLOYEE_ID EMPLOYEE_N DEPARTMENT_ID DEPARTMENT_NAM
----------- ---------- ------------- --------------
       7369 SMITH                 20 RESEARCH
       7499 ALLEN                 30 SALES
       7521 WARD                  30 SALES
       7566 JONES                 20 RESEARCH
       7654 MARTIN                30 SALES
       7698 BLAKE                 30 SALES
       7782 CLARK                 10 ACCOUNTING
       7788 SCOTT                 20 RESEARCH
       7839 KING                  10 ACCOUNTING
       7844 TURNER                30 SALES
       7876 ADAMS                 20 RESEARCH
       7900 JAMES                 30 SALES
       7902 FORD                  20 RESEARCH
       7934 MILLER                10 ACCOUNTING

14 rows selected.

SQL>

We can see from the output above, the table alias is not included in the column name. If you need any indication of the table where the column came from, you should use a column alias.

Some people prefer to fully qualify all the columns using the table name and column name together. That's OK for small table names, but I think it gets quite ugly for large table names.

SELECT employees.employee_id,
       employees.employee_name,
       departments.department_id,
       departments.department_name
FROM   employees
       JOIN departments ON employees.department_id = departments.department_id
ORDER BY employees.employee_id;

EMPLOYEE_ID EMPLOYEE_N DEPARTMENT_ID DEPARTMENT_NAM
----------- ---------- ------------- --------------
       7369 SMITH                 20 RESEARCH
       7499 ALLEN                 30 SALES
       7521 WARD                  30 SALES
       7566 JONES                 20 RESEARCH
       7654 MARTIN                30 SALES
       7698 BLAKE                 30 SALES
       7782 CLARK                 10 ACCOUNTING
       7788 SCOTT                 20 RESEARCH
       7839 KING                  10 ACCOUNTING
       7844 TURNER                30 SALES
       7876 ADAMS                 20 RESEARCH
       7900 JAMES                 30 SALES
       7902 FORD                  20 RESEARCH
       7934 MILLER                10 ACCOUNTING

14 rows selected.

SQL>

Like the table aliases, the table name is not included in the column name returned.

Functions

The SELECT list can also include function calls. They can be built-in functions, or PL/SQL functions you've coded yourself. In the following example we've used the UPPER function to convert a string literal containing lower case text into upper case text.

SELECT UPPER('lowercase text') AS text
FROM   dual;

TEXT
--------------
LOWERCASE TEXT

1 row selected.

SQL>

Oracle contains many built-in functions to handle strings, numbers and dates etc. Any discussion about them is beyond the scope of this article.

Expressions

The SELECT list can include a variety of expressions, including mathematical operations.

SELECT 1+2 AS addition
FROM   dual;

  ADDITION
----------
         3

1 row selected.

SQL>

Scalar Subqueries

It is possible to source data for a column using a scalar subquery. A scalar subquery is a query that returns a single row of a single column or object. The example below uses a scalar subquery to return the number of employees for each department.

SELECT d.department_id, d.department_name,
       (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) AS emp_count
FROM   departments d
ORDER BY d.department_id;

DEPARTMENT_ID DEPARTMENT_NAM  EMP_COUNT
------------- -------------- ----------
           10 ACCOUNTING              3
           20 RESEARCH                5
           30 SALES                   6
           40 OPERATIONS              0

4 rows selected.

SQL>

It is easy to become "addicted" to scalar subqueries when you are new to SQL, as they often seem like the "obvious" solution. In many cases, it is probably better to attempt to use conventional joins. The data from the previous example might be better sourced using the following query.

SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS emp_count
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;

DEPARTMENT_ID DEPARTMENT_NAM  EMP_COUNT
------------- -------------- ----------
           10 ACCOUNTING              3
           20 RESEARCH                5
           30 SALES                   6
           40 OPERATIONS              0

4 rows selected.

SQL>

Unfortunately, it is rarely possible to give a definite, "this is the best approach", statement where SQL is concerned. You need to make judgements on a case-by-case basis.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.