SQL database in table structure

Explore databases with SELECT

Contents overview

Advanced database queries with SELECT from SQL
The SELECT command is the central element of the SQL command range. With SELECT, the required data is filtered from the tables and can then be edited. The result of a SELECT query is a so-called SQL cursor, a temporary table that contains an image of the data at the time of the query.

The following examples are based on Oracle 7.3 / SQLPlus. The table structure and the data it contains come from a sample database from the TBZ technical school in Zurich.
First get an overview
Two basic commands are SELECT and DESCRIBE. These commands can be used to research the structures of a database at any time. Each command is terminated with a semicolon (;) in SQLPlus (terminal window of the Oracle server).

Database catalog
In order to display all available tables or views ("Views") in a database, the "Table catalog" can be displayed with the following command:

SELECT * FROM CAT;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
BONUS TABLE
DALLAS VIEW
DATA VIEW
DEPT TABLE
EMP TABLE
EMPDEPT VIEW
YEAR VIEW
NEWCOMM TABLE
SALGRADE TABLE
CUT VIEW
10 rows selected.
Table structure
The structure of a table or its field definition can be «written» with the DESCRIBE command from Oracle. The EMP table is examined in more detail below:

DESCRIBE emp;

Name zero? Type
------------------------------- --------
EMPNO NOTNULL NUMBER (4)
ENAME VARCHAR2 (10)
JOB VARCHAR2 (9)
MGR NUMBER (4)
HIREDATE DATE
SAL NUMBER (7.2)
COMM NUMBER (7.2)
DEPTNO NUMBER (2)


DESCRIBE shows the individual attributes or fields of the table as well as their further definition:
  • NOTNULL means that this field must not remain empty. A NOT NULL field is usually a primary or foreign key field for which an entry is mandatory
  • TYPE defines the content of the field which can be defined either as a character string (VARCHAR), number (NUMBER) or date (DATE). Depending on the database, other data types are also possible.
So-called BLOBs - Binary Large Objects - are also popular as a data type in “modern” databases. A BLOB can be an embedded photo, a sound or video sequence or any other binary object.
Show field contents
To display field contents from tables, the FROM clause must be used for each SELECT command. The table names after the FROM clause define the origin of the data. To display all the information in a table with the name EMP, it is sufficient to enter the following SQL command:

SELECT * FROM emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- --------- --------- --------- --------- --------- --------- ---------
6420 HARTMANN NETGURU
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7788 SCOTT ANALYST 7566 APR 1987 3000 20th
7934 MILLER CLERK 7782 23-JAN-82 1300 10

11 rows selected.

This simple SELECT command does not contain any condition clauses (WHERE) or grouping functions such as GROUP BY. For example, to filter out all people who have the letter H at the beginning of the ENAME field, the following WHERE condition is used:

SELECT empno, ename, job
FROM emp
WHERE ename like 'H%';

The result of the query from the EMP table looks like this:

EMPNO ENAME JOB
--------- ---------- ---------
6420 HARTMANN NETGURU

The SQL SELECT command is a very powerful command. With specific arguments, for example, column headings or additional texts can be added to the existing attributes:

SELECT ename || 'employed as' || job 'employed as'
FROM emp;

Hired as
-----------------------------------
HARTMANN employed as NETGURU
SMITH employed as CLERK
ALLEN employed as a SALESMAN
WARD employed as a SALESMAN
JONES employed as MANAGER
MARTIN employed as SALESMAN
BLAKE employed as MANAGER
CLARK employed as MANAGER
SCOTT employed as ANALYST
KING employed as PRESIDENT
TURNER employed as a SALESMAN
ADAMS employed as CLERK
JAMES employed as CLERK
FORD employed as ANALYST
MILLER employed as CLERK
SMITH employed as FRITZ

16 rows selected.
Connect tables with "SQL-JOIN"
A so-called SQL-JOIN («connect») is used to join two tables from the database in one query. On the basis of the database («SELECT * from cat;») described at the beginning of this page, some tasks should now be solved.

example 1
  • Output of employee name, task, department name
  • The department name will be composed by connecting DEPTNO in table EMP and DEPTNO in table DEPT
The SELECT command required for this contains two tables in the FROM clause, for both tables an alias (alternative designation) is specified within FROM for EMP the alias E for DEPT the alias D.

SELECT E.ename, E.job, E.deptno, D.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Variant of the table connection with INNER JOIN:

SELECT E.ename, E.job, E.deptno, D.dname
FROM emp e
INNER JOIN dept d ONe.deptno = d.deptno;

Note: JOIN is not used as a keyword within the SELECT command. Specifying at least two table names in the FROM clause is known as an SQL JOIN.

ENAME JOB DEPTNO DNAME
--------- --------- --------- ---------
ALLEN SALESMAN 30 SALES
WARD SALESMAN 30 SALES
JONES MANAGER 20 RESEARCH
MARTIN SALESMAN 30 SALES
BLAKE MANAGER 30 SALES
CLARK MANAGER 10 ACCOUNTING
SCOTT ANALYST 20 RESEARCH
KING PRESIDENT 10 ACCOUNTING
TURNER SALESMAN 30 SALES
ADAMS CLERK 20 RESEARCH
JAMES CLERK 30 SALES
FORD ANALYST 20 RESEARCH
MILLER CLERK 10 ACCOUNTING
SMITH FRITZ 10 ACCOUNTING

15 rows selected.

Example 2
In the following example, a column label «Manager» is added to the JOIN.
  • Output of employee number, employee name, employee number of the superior and his name
  • The superior is in the same table. MGR number points to EMPNO
SELECT E1.empno, E1.ename, E1.mgr, E2.ename Manager
FROM EMP E1, EMP E2
WHERE E1.mgr = E2.empno;

The result of this query brings the following screen output as an answer:

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

14 rows selected.

Example 3
The name of the employee as well as wages and starting date should be displayed. In addition, only data records from employees who have been employed longer than the employee «TURNER» should be listed.

SELECT E1.ename, E1.sal, E1.hiredate
FROM emp E1, emp E2
WHERE E2.hiredate> E1.hiredate
AND E2.ename = 'TURNER';

In this query, the EMP table is used with two aliases, E1 on the one hand and E2 on the other. In the WHERE clause, an AND connection is used which, on the one hand, compares the date between the two tables (E1 and E2, actually only EMP), and on the other hand, initially only filters out the data record with the name TURNER for table E2. The following result is correct:

ENAME SAL HIREDATE
--------- ---------- ---------
SMITH 800 17-DEC-80
ALLEN 1600 20-FEB-81
WARD 1250 FEB 22-81
JONES 2975 02-APR-81
BLAKE 2850 01-MAY-81
CLARK 2450 09-JUN-81

6 rows selected.

Principle:
Every SQL query in SQL Plus (Oracle) is first checked by the SQL interpreter for correctness with regard to commands, arrangement and field names. The query is then resolved backwards.
Calculations in SQL queries
Similar to a spreadsheet like Microsoft Excel, SQL can also use formulas to perform mathematical operations and calculations. In contrast to an interactive application such as Excel, however, with SQL the mathematical functions must be integrated into the query command. The resulting table already contains the result.

Form sums with SUM ()
In the database that has already been used, the sum of the salary column is to be formed in the EMP table. The data records should be sorted according to the amount of the salary. Data records without a salary ("Frondienst") are filtered out by the query.

SELECT ename, empno, sal
FROM emp
WHERE sal> 0
UNION SELECT 'Total', 0, SUM (sal)
FROM emp
ORDER BY sal;

In this SQL query, two queries are executed in succession using the UNION clause. The result is as follows:

ENAME EMPNO SAL
---------    ----------    ---------
SMITH 7369 800
JAMES 7900 950
SMITH 6666 1200
MARTIN 7654 1250
WARD 7521 1250
ADAMS 7876 1300
MILLER 7934 1300
TURNER 7844 1500
ALLEN 7499 1600
CLARK 7782 2450
BLAKE 7698 2850
JONES 7566 2975
FORD 7902 3000
SCOTT 7788 3000
KING 7839 5000
Total 0 30 425

16 rows selected.

The total line represents a virtual data record, it is formed in the second SQL query. The specification of "0" is necessary because each column of a virtual data record must contain a value.

Counting records with COUNT ()
Basically, all calculations of an SQL statement must be written between SELECT and the FROM clause. The COUNT () function is used to count data records. In order to count all data records in the EMP table, for example, the following query is formulated:

SELECT count (empno)
FROM emp;

The output of this query only includes the number of data records found; the field contents are not displayed.

Calculate mean value with AVG ()
For example, the AVERAGE () or AVG () function can be used to calculate the average salary of all employees. In the following question, the mean salaries of the individual departments are also to be calculated using the GROUP BY keyword. The following columns should be displayed:
  • Department name
  • Place where the department is located
  • Number of employees per department
  • Average salary of employees per department
The data records are aggregated or grouped for each department; the number of data records per department is also displayed.

SELECT dname, loc, COUNT (empno), AVG (sal)
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname, loc;

The result of this sophisticated query looks like this:

DNAME LOC COUNT (EMPNO) AVG (SAL)
-------------- ------------- ------------ ---------
ACCOUNTING NEW YORK 4 2487.5
RESEARCH DALLAS 5 2215
SALES CHICAGO 6 1566.6667

The somewhat illegible column headings such as COUNT (EMPNO) could still be optimized, an alias can also be assigned for the column name.

If, in addition to the previous criteria, only the data records with more than 5 employees are to be displayed, the SQL command must be expanded as follows:

SELECT dname, loc, COUNT (empno), AVG (sal)
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname, loc
HAVING COUNT (empno)> = 5;

The grouping can be further restricted with the help of the HAVING clause:

DNAME LOC COUNT (EMPNO) AVG (SAL)
-------------- ------------- ------------ ---------
RESEARCH DALLAS 5 2215
SALES CHICAGO 6 1566.6667


Since there are only 4 employees in NEW YORK, this grouping is not displayed.

Minimum and maximum values
In order to display the values ​​within the upper and lower limit of a column, SQL provides the functions MIN () and MAX (). In order to be able to check the information, the columns
are displayed. Since two functions are used at the same time, an OR link is used:

SELECT ename, sal
FROM emp
WHERE sal = (SELECT MIN (sal) FROM emp) OR
sal = (SELECT MAX (sal) FROM emp);

In this query, a table with the two columns ENAME and SAL is first provided, then the integrated SELECT commands (within the brackets) are interpreted.

ENAME SAL
---------- ---------
SMITH 800
KING 5000


On the basis of this small collection of examples it becomes clear which powerful queries are possible with commands such as SQL-SELECT.