📋Steps in this guide1/8

SQL for Beginners (Part 1) : The SELECT List

In this article we take a look at the type of things you are likely to see in the SELECT list of queries.

oracle miscconfigurationintermediate

by OracleDba

12 views

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. 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.

Code/Command (click line numbers to comment):

--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;

Wildcard "*"

The "*" is a wildcard. Using this in the 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. 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 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 list. When the query is executed, we get all the columns from both tables.

Code/Command (click line numbers to comment):

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>

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.

Code/Command (click line numbers to comment):

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.

Code/Command (click line numbers to comment):

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. 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. Like the table aliases, the table name is not included in the column name returned.

Code/Command (click line numbers to comment):

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>

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>

Functions

The 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 function to convert a string literal containing lower case text into upper case text. Oracle contains many built-in functions to handle strings, numbers and dates etc. Any discussion about them is beyond the scope of this article.

Code/Command (click line numbers to comment):

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

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

1 row selected.

SQL>

Expressions

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

Code/Command (click line numbers to comment):

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. 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. 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: - SQL for Beginners (Part 1) : The SELECT List - SQL for Beginners - Full Playlist - Oracle SQL Articles - Getting Started - Database SQL Language Reference : SELECT Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!