Creat a new table

By specifying the table name, and the names and data types of all the columns in the table, we can create a new table. The following is a simplified version of the sample table emp, including only the most basic information needed when defining the table.

CREATE TABLE emp (
empno   NUMBER(4),
ename   VARCHAR2(10),
job  VARCHAR2(9),
mgr  NUMBER(4),
hiredate    DATE,
sal NUMBER(7,2),
comm    NUMBER(7,2),
deptno  NUMBER(2)
);

This command can be entered in PSQL as multiple lines. When a semicolon is encountered, PSQL considers the command input to be finished.

We can use as many spaces as we want in SQL commands (e.g., spaces, TABs, and line breaks). This allows typing the command in a different layout than above, and we can even write the entire command in one line. The start position of a comment is indicated by two dashes, and all characters following the dash until the end of the command are ignored. For keywords and identifiers, SQL statements are case-insensitive except for identifiers enclosed in double quotes that maintain their original case-sensitive form (which is not done in the above example).

VARCHAR2(10) is a data type that can store any string up to 10 characters in length. NUMBER(7,2) is a value with a precision of 7 and a range of 2. NUMBER(4) is a value with a precision of 4 and a range of 0.

AntDB supports common SQL data types such as INTEGER, SMALLINT, NUMBER, REAL, DOUBLE PRECISION, CHAR, VARCHAR2, DATE and TIMESTAMP and various synonyms of these types.

If a table is no longer needed or if you want to create a differently defined table with the same name, then it can be deleted using the following command:

DROP TABLE tablename; 

Insert a row into a table

The INSERT statement is used to insert a row into a table.

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);

It is important to note that all data types do not use an explicit input format. As in this example, non-numeric constants must be enclosed in single quotes. We can use a variety of formats for date type values, but for this tutorial we will stick to the format that is clear.

The syntax used so far in the SQL command requires that the order of the columns in the table must be remembered. But here is another syntax that allows all column names to be listed out of order in the table.

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); 

If it is desired that only certain columns appear in the statement, then they can be written in a different order, for example, if the commission is unknown, we can write it like this

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,20);

Many developers consider it a better writing style to write the columns explicitly than to rely on their order in an implicit way.

Query Tables

The SELECT statement in the SQL command is used to perform query operations on a table and retrieve data from it. The statement is divided into a select list (which lists the columns of data to be returned), a table list (which lists the tables from which data is to be retrieved), and an optional conditional part (which specifies the restriction). The following query does not list all columns in a particular order for all employee information.

SELECT * FROM emp;

Here, an "*" in the selection list indicates that all columns are output. The following is the output of this query.

EMPNO | ENAME  |    JOB    | MGR   |      HIREDATE        | SAL  | COMM | DEPTNO 
------+--------+-----------+-------+----------------------+------+------+--------
 7369 | SMITH  | CLERK     | 7902  | 1980-12-17 00:00:00  |  800 |      |     20
 7499 | ALLEN  | SALESMAN  | 7698  | 1981-02-20 00:00:00  | 1600 |  300 |     30
 7521 | WARD   | SALESMAN  | 7698  | 1981-02-22 00:00:00  | 1250 |  500 |     30
 7566 | JONES  | MANAGER   | 7839  | 1981-04-02 00:00:00  | 2975 |      |     20
 7654 | MARTIN | SALESMAN  | 7698  | 1981-09-28 00:00:00  | 1250 | 1400 |     30
 7698 | BLAKE  | MANAGER   | 7839  | 1981-05-01 00:00:00  | 2850 |      |     30
 7782 | CLARK  | MANAGER   | 7839  | 1981-06-09 00:00:00  | 2450 |      |     10
 7788 | SCOTT  | ANALYST   | 7566  | 1987-04-19 00:00:00  | 3000 |      |     20
 7839 | KING   | PRESIDENT |       | 1981-11-17 00:00:00  | 5000 |      |     10
 7844 | TURNER | SALESMAN  | 7698  | 1981-09-08 00:00:00  | 1500 |    0 |     30
 7876 | ADAMS  | CLERK     | 7788  | 1987-05-23 00:00:00  | 1100 |      |     20
 7900 | JAMES  | CLERK     | 7698  | 1981-12-03 00:00:00  |  950 |      |     30
 7902 | FORD   | ANALYST   | 7566  | 1981-12-03 00:00:00  | 3000 |      |     20
 7934 | MILLER | CLERK     | 7782  | 1982-01-23 00:00:00  | 1300 |      |     10
(14 rows)

Any form of expression can be specified in the select list. For example, this can be done like this:

SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;

 ENAME  | SAL  | YEARLY_SALARY | DEPTNO 
--------+------+---------------+--------
 SMITH  |  800 |         19200 |     20
 ALLEN  | 1600 |         38400 |     30
 WARD   | 1250 |         30000 |     30
 JONES  | 2975 |         71400 |     20
 MARTIN | 1250 |         30000 |     30
 BLAKE  | 2850 |         68400 |     30
 CLARK  | 2450 |         58800 |     10
 SCOTT  | 3000 |         72000 |     20
 KING   | 5000 |        120000 |     10
 TURNER | 1500 |         36000 |     30
 ADAMS  | 1100 |         26400 |     20
 JAMES  |  950 |         22800 |     30
 FORD   | 3000 |         72000 |     20
 MILLER | 1300 |         31200 |     10
(14 rows)

The important thing to note here is how to re-identify the output columns using the AS clause. (AS is an optional clause)

The WHERE clause can be used in a query to specify which rows are required. The WHERE clause contains a Boolean type expression that can only return rows that satisfy the Boolean expression return value of true. Usually Boolean operators (AND, OR and NOT) are used in qualifying conditions. For example, let's use the following query to get the records of employees working in department number 20 with a salary over $1000.00.

SELECT ename, sal, deptno FROM emp WHERE deptno = 20 AND sal > 1000;

 ENAME | SAL  | DEPTNO 
-------+------+--------
 JONES | 2975 |     20
 SCOTT | 3000 |     20
 ADAMS | 1100 |     20
 FORD  | 3000 |     20
(4 rows)

You can require the output of the query to be output in sorted order.

SELECT ename, sal, deptno FROM emp ORDER BY ename;

 ENAME  | SAL  | DEPTNO 
--------+------+--------
 ADAMS  | 1100 |     20
 ALLEN  | 1600 |     30
 BLAKE  | 2850 |     30
 CLARK  | 2450 |     10
 FORD   | 3000 |     20
 JAMES  |  950 |     30
 JONES  | 2975 |     20
 KING   | 5000 |     10
 MARTIN | 1250 |     30
 MILLER | 1300 |     10
 SCOTT  | 3000 |     20
 SMITH  |  800 |     20
 TURNER | 1500 |     30
 WARD   | 1250 |     30
(14 rows)

You can also request that duplicate records be removed from the query results:

SELECT DISTINCT job FROM emp;

    JOB    
------------
 CLERK
 PRESIDENT
 MANAGER
 SALESMAN
 ANALYST
(5 rows)

The next section shows how to use a query to fetch records from multiple tables.

Join operations between tables

Until now, we have used query statements that access only one table at a time. But query statements can actually access more than one table at a time, or access the same table in a way that processes multiple rows in the table at the same time. We call a query that accesses multiple rows in the same or different tables at the same time a join query. For example, if you want to list all the employee records and their associated department names and locations, you need to compare the deptno column of each record in the emp table with the deptno column of all the records in the dept table, and then query for the records in the two tables that match the relevant values. We use the following query to do this.

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno;

 ENAME  | SAL  | DEPTNO |   DNAME    |   LOC    
--------+------+--------+------------+----------
 SMITH  |  800 |     20 | RESEARCH   | DALLAS
 ALLEN  | 1600 |     30 | SALES      | CHICAGO
 WARD   | 1250 |     30 | SALES      | CHICAGO
 JONES  | 2975 |     20 | RESEARCH   | DALLAS
 MARTIN | 1250 |     30 | SALES      | CHICAGO
 BLAKE  | 2850 |     30 | SALES      | CHICAGO
 CLARK  | 2450 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000 |     20 | RESEARCH   | DALLAS
 KING   | 5000 |     10 | ACCOUNTING | NEW YORK
 TURNER | 1500 |     30 | SALES      | CHICAGO
 ADAMS  | 1100 |     20 | RESEARCH   | DALLAS
 JAMES  |  950 |     30 | SALES      | CHICAGO
 FORD   | 3000 |     20 | RESEARCH   | DALLAS
 MILLER | 1300 |     10 | ACCOUNTING | NEW YORK
(14 rows)

There are two things to note about the result set

• There are no rows in the result set that match the department number 40. This is because there are no records matching department number 40 in table emp, so the join ignores the records that do not match in table dept.

• In general, we would prefer to list the output columns qualified by the table name rather than using "*" or a column name that is not qualified by the table name, as in the following.
  

SELECT ename, sal, dept.deptno, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno; |

Since all columns have different names (except for columns that must be qualified because they are named deptno in both tables), the parser will automatically find out which table these columns belong to, but it is better to be able to use fully qualified column names in a join query.

The join queries seen so far can be written in another form.

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; 

This syntax is not as commonly used as the one above, but we show it here to help you understand what is to be illustrated below.

You will notice that in the result set of the join query above, no information is returned about employees belonging to department number 40, so the record with department number 40 does not appear in the output result set. Now, we want to display the records belonging to department number 40 in the results without the matching employee records. What we need to do is have the query scan the dept table and look for a matching record on the emp table for each record. If no matching row is found, a "null" value is used to replace the value of the column in the emp table. We call this query an outer join (all the joins we have seen so far are inner joins). The exact form of the command is as follows:

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept LEFT OUTER JOIN emp ON emp.deptno = dept.deptno;

 ENAME  | SAL  | DEPTNO   |   DNAME    |   LOC    
--------+------+----------+------------+----------
 SMITH  |  800 |       20 | RESEARCH   | DALLAS
 ALLEN  | 1600 |       30 | SALES      | CHICAGO
 WARD   | 1250 |       30 | SALES      | CHICAGO
 JONES  | 2975 |       20 | RESEARCH   | DALLAS
 MARTIN | 1250 |       30 | SALES      | CHICAGO
 BLAKE  | 2850 |       30 | SALES      | CHICAGO
 CLARK  | 2450 |       10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000 |       20 | RESEARCH   | DALLAS
 KING   | 5000 |       10 | ACCOUNTING | NEW YORK
 TURNER | 1500 |       30 | SALES      | CHICAGO
 ADAMS  | 1100 |       20 | RESEARCH   | DALLAS
 JAMES  |  950 |       30 | SALES      | CHICAGO
 FORD   | 3000 |       20 | RESEARCH   | DALLAS
 MILLER | 1300 |       10 | ACCOUNTING | NEW YORK
        |      |       40 | OPERATIONS | BOSTON
(15 rows)

The query above is called a left outer join because each row in the table to the left of the join operator will appear at least once in the output, while the table to the right of the join operator will output only those rows that match the rows in the table to the left of the join operator. When a row is queried from the table on the left side of the join operator and there is no matching row on the right side of the operator, then the column in the table on the right will be replaced with a null value.

Another syntax for outer joins is to use the outer join operator "(+)" in the join condition of the WHERE clause. The outer join operator is positioned after the column names in the table, and for rows that do not match, the null value is used instead of the value of those columns. So for all rows in the dept table that do not match the emp table, AntDB will return a null value for any expression that contains the emp column in the select list. Therefore, the above example can be rewritten as follows.

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept, emp WHERE emp.deptno(+) = dept.deptno;

 ENAME  | SAL  | DEPTNO |   DNAME    |   LOC    
--------+------+--------+------------+----------
 SMITH  |  800 |     20 | RESEARCH   | DALLAS
 ALLEN  | 1600 |     30 | SALES      | CHICAGO
 WARD   | 1250 |     30 | SALES      | CHICAGO
 JONES  | 2975 |     20 | RESEARCH   | DALLAS
 MARTIN | 1250 |     30 | SALES      | CHICAGO
 BLAKE  | 2850 |     30 | SALES      | CHICAGO
 CLARK  | 2450 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000 |     20 | RESEARCH   | DALLAS
 KING   | 5000 |     10 | ACCOUNTING | NEW YORK
 TURNER | 1500 |     30 | SALES      | CHICAGO
 ADAMS  | 1100 |     20 | RESEARCH   | DALLAS
 JAMES  |  950 |     30 | SALES      | CHICAGO
 FORD   | 3000 |     20 | RESEARCH   | DALLAS
 MILLER | 1300 |     10 | ACCOUNTING | NEW YORK
        |      |     40 | OPERATIONS | BOSTON
(15 rows)

We can also perform join operations on the same table, and this join is called a self-join. For example, suppose we want to find out the name of each employee and the manager in charge of that employee, then we need to compare the mgr column in each record of table emp with the empno column in the other records of table emp.

SELECT e1.ename || ' works for ' || e2.ename AS "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;

Employees and their Managers 
-----------------------------
 SMITH works for FORD
 ALLEN works for BLAKE
 WARD works for BLAKE
 JONES works for KING
 MARTIN works for BLAKE
 BLAKE works for KING
 CLARK works for KING
 SCOTT works for JONES
 TURNER works for BLAKE
 ADAMS works for SCOTT
 JAMES works for BLAKE
 FORD works for JONES
 MILLER works for CLARK
(13 rows)

Here, we have re-identified table emp as e1 in the select list and join condition to represent the employee record, and table emp as e2 in the select list and join condition to represent the employee record that matches as the manager. This form of pseudonym can be used in queries to reduce input, e.g.

SELECT e.ename, e.mgr, d.deptno, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno;

 ENAME  | MGR   |   DEPTNO |   DNAME    |   LOC    
--------+-------+----------+------------+-----------
 SMITH  |  7902 |       20 | RESEARCH   | DALLAS
 ALLEN  |  7698 |       30 | SALES      | CHICAGO
 WARD   |  7698 |       30 | SALES      | CHICAGO
 JONES  |  7839 |       20 | RESEARCH   | DALLAS
 MARTIN |  7698 |       30 | SALES      | CHICAGO
 BLAKE  |  7839 |       30 | SALES      | CHICAGO
 CLARK  |  7839 |       10 | ACCOUNTING | NEW YORK
 SCOTT  |  7566 |       20 | RESEARCH   | DALLAS
 KING   |       |       10 | ACCOUNTING | NEW YORK
 TURNER |  7698 |       30 | SALES      | CHICAGO
 ADAMS  |  7788 |       20 | RESEARCH   | DALLAS
 JAMES  |  7698 |       30 | SALES      | CHICAGO
 FORD   |  7566 |       20 | RESEARCH   | DALLAS
 MILLER |  7782 |       10 | ACCOUNTING | NEW YORK
(14 rows)

This form of abbreviation will be frequently encountered later.