1.4. The SQL Language

1.4.1.3. Aggregate Operators

SQL provides aggregate operators (e.g. AVG, COUNT, SUM, MIN, MAX) that take an expression as argument. The expression is evaluated at each row that satisfies the WHERE clause, and the aggregate operator is calculated over this set of input values. Normally, an aggregate delivers a single result for a whole SELECT statement. But if grouping is specified in the query, then a separate calculation is done over the rows of each group, and an aggregate result is delivered per group (see next section).

SELECT AVG(PRICE) AS AVG_PRICE
    FROM PART;
	

 AVG_PRICE
-----------
   14.5
	

SELECT COUNT(PNO)
    FROM PART;
	

 COUNT
-------
   4
	

1.4.1.4. Aggregation by Groups

SQL allows one to partition the tuples of a table into groups. Then the aggregate operators described above can be applied to the groups --- i.e. the value of the aggregate operator is no longer calculated over all the values of the specified column but over all values of a group. Thus the aggregate operator is evaluated separately for every group.

The partitioning of the tuples into groups is done by using the keywords GROUP BY followed by a list of attributes that define the groups. If we have GROUP BY A₁, ⃛, A_k we partition the relation into groups, such that two tuples are in the same group if and only if they agree on all the attributes A₁, ⃛, A_k.

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME;
	

 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  2  | Jones |   1
  3  | Adams |   2
  4  | Blake |   3
	

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
   1   |  Smith  |   2
   2   |  Jones  |   4
   3   |  Adams  |   1
   3   |  Adams  |   3
   4   |  Blake  |   2
   4   |  Blake  |   3
   4   |  Blake  |   4
	

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
                 |   2
--------------------------
   2   |  Jones  |   4
--------------------------
   3   |  Adams  |   1
                 |   3
--------------------------
   4   |  Blake  |   2
                 |   3
                 |   4
	

Note that for a query using GROUP BY and aggregate operators to make sense the target list can only refer directly to the attributes being grouped by. Other attributes may only be used inside the argument of an aggregate function. Otherwise there would not be a unique value to associate with the other attributes.

Also observe that it makes no sense to ask for an aggregate of an aggregate, e.g., AVG(MAX(sno)), because a SELECT only does one pass of grouping and aggregation. You can get a result of this kind by using a temporary table or a sub-SELECT in the FROM clause to do the first level of aggregation.

1.4.1.5. Having

The HAVING clause works much like the WHERE clause and is used to consider only those groups satisfying the qualification given in the HAVING clause. Essentially, WHERE filters out unwanted input rows before grouping and aggregation are done, whereas HAVING filters out unwanted group rows post-GROUP. Therefore, WHERE cannot refer to the results of aggregate functions. On the other hand, there's no point in writing a HAVING condition that doesn't involve an aggregate function! If your condition doesn't involve aggregates, you might as well write it in WHERE, and thereby avoid the computation of aggregates for groups that you're just going to throw away anyway.

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME
    HAVING COUNT(SE.PNO) > 1;
	

 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  3  | Adams |   2
  4  | Blake |   3
	

1.4.1.6. Subqueries

In the WHERE and HAVING clauses the use of subqueries (subselects) is allowed in every place where a value is expected. In this case the value must be derived by evaluating the subquery first. The usage of subqueries extends the expressive power of SQL.

SELECT * 
    FROM PART 
    WHERE PRICE > (SELECT PRICE FROM PART
                   WHERE PNAME='Screw');
	

 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25
	

SELECT * 
    FROM SUPPLIER S
    WHERE NOT EXISTS
        (SELECT * FROM SELLS SE
         WHERE SE.SNO = S.SNO);
	

1.4.1.7. Subqueries in FROM

A somewhat different way of using subqueries is to put them in the FROM clause. This is a useful feature because a subquery of this kind can output multiple columns and rows, whereas a subquery used in an expression must deliver just a single result. It also lets us get more than one round of grouping/aggregation without resorting to a temporary table.

SELECT MAX(subtable.avgprice)
    FROM (SELECT AVG(P.PRICE) AS avgprice
          FROM SUPPLIER S, PART P, SELLS SE
          WHERE S.SNO = SE.SNO AND
                P.PNO = SE.PNO
          GROUP BY S.SNO) subtable;
	

1.4.1.8. Union, Intersect, Except

These operations calculate the union, intersection and set theoretic difference of the tuples derived by two subqueries.

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Jones'
UNION
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Adams';    
	

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna
	

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 1
INTERSECT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO < 3;
	

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
	

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 1
EXCEPT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 3;
	

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna
	

1.4.2.1. Create Table

The most fundamental command for data definition is the one that creates a new relation (a new table). The syntax of the CREATE TABLE command is:

CREATE TABLE table_name
    (name_of_attr_1 type_of_attr_1
     [, name_of_attr_2 type_of_attr_2 
     [, ...]]);
      

CREATE TABLE SUPPLIER
    (SNO   INTEGER,
     SNAME VARCHAR(20),
     CITY  VARCHAR(20));
     

CREATE TABLE PART
    (PNO   INTEGER,
     PNAME VARCHAR(20),
     PRICE DECIMAL(4 , 2));
     

CREATE TABLE SELLS
    (SNO INTEGER,
     PNO INTEGER);
	

1.4.2.2. Data Types in SQL

The following is a list of some data types that are supported by SQL:

• INTEGER: signed fullword binary integer (31 bits precision).

• SMALLINT: signed halfword binary integer (15 bits precision).

• DECIMAL (p[,q]): signed packed decimal number of up to p digits, with q digits to the right of the decimal point. If q is omitted it is assumed to be 0.

• FLOAT: signed doubleword floating point number.

• CHAR(n): fixed length character string of length n.

• VARCHAR(n): varying length character string of maximum length n.

also type TEXT is added by PostGres SQL which is similar to VARCHAR except the database keeps track of the length needed.

1.4.2.3. Create Index

Indices are used to speed up access to a relation. If a relation R has an index on attribute A then we can retrieve all tuples t having t(A) = a in time roughly proportional to the number of such tuples t rather than in time proportional to the size of R.

To create an index in SQL the CREATE INDEX command is used. The syntax is:

CREATE INDEX index_name 
    ON table_name ( name_of_attribute );
      

CREATE INDEX I ON SUPPLIER (SNAME);
      

1.4.2.4. Create View

A view may be regarded as a virtual table, i.e. a table that does not physically exist in the database but looks to the user as if it does. By contrast, when we talk of a base table there is really a physically stored counterpart of each row of the table somewhere in the physical storage.

Views do not have their own, physically separate, distinguishable stored data. Instead, the system stores the definition of the view (i.e. the rules about how to access physically stored base tables in order to materialize the view) somewhere in the system catalogs (see System Catalogs). For a discussion on different techniques to implement views refer to SIM98.

In SQL the CREATE VIEW command is used to define a view. The syntax is:

CREATE VIEW view_name
    AS select_stmt
      

Let the following view definition be given (we use the tables from The Suppliers and Parts Database again):

CREATE VIEW London_Suppliers
    AS SELECT S.SNAME, P.PNAME
        FROM SUPPLIER S, PART P, SELLS SE
        WHERE S.SNO = SE.SNO AND
              P.PNO = SE.PNO AND
              S.CITY = 'London';
      

Now we can use this virtual relation London_Suppliers as if it were another base table:

SELECT * FROM London_Suppliers
    WHERE PNAME = 'Screw';
      

 SNAME | PNAME
-------+-------
 Smith | Screw                 
      

To calculate this result the database system has to do a hidden access to the base tables SUPPLIER, SELLS and PART first. It does so by executing the query given in the view definition against those base tables. After that the additional qualifications (given in the query against the view) can be applied to obtain the resulting table.

1.4.2.5. Drop Table, Drop Index, Drop View

To destroy a table (including all tuples stored in that table) the DROP TABLE command is used:

DROP TABLE table_name;
       

To destroy the SUPPLIER table use the following statement:

DROP TABLE SUPPLIER;
      

The DROP INDEX command is used to destroy an index:

DROP INDEX index_name;
      

Finally to destroy a given view use the command DROP VIEW:

DROP VIEW view_name;
      

1.4.3.1. Insert Into

Once a table is created (see Create Table), it can be filled with tuples using the command INSERT INTO. The syntax is:

INSERT INTO table_name (name_of_attr_1 
    [, name_of_attr_2 [,...]])
    VALUES (val_attr_1 [, val_attr_2 [, ...]]);
      

To insert the first tuple into the relation SUPPLIER (from The Suppliers and Parts Database) we use the following statement:

INSERT INTO SUPPLIER (SNO, SNAME, CITY)
    VALUES (1, 'Smith', 'London');
      

To insert the first tuple into the relation SELLS we use:

INSERT INTO SELLS (SNO, PNO)
    VALUES (1, 1);
      

1.4.3.2. Update

To change one or more attribute values of tuples in a relation the UPDATE command is used. The syntax is:

UPDATE table_name
    SET name_of_attr_1 = value_1 
        [, ... [, name_of_attr_k = value_k]]
    WHERE condition;
      

To change the value of attribute PRICE of the part 'Screw' in the relation PART we use:

UPDATE PART
    SET PRICE = 15
    WHERE PNAME = 'Screw';
      

The new value of attribute PRICE of the tuple whose name is 'Screw' is now 15.

1.4.3.3. Delete

To delete a tuple from a particular table use the command DELETE FROM. The syntax is:

DELETE FROM table_name
    WHERE condition;
      

To delete the supplier called 'Smith' of the table SUPPLIER the following statement is used:

DELETE FROM SUPPLIER
    WHERE SNAME = 'Smith';