Miscellaneous SQL Statements

Aggregate Functions

I will discuss five important aggregate functions: SUM, AVG, MAX, MIN, and COUNT. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows.

SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric.
AVG () gives the average of the given column.
MAX () gives the largest figure in the given column.
MIN () gives the smallest figure in the given column.
COUNT(*) gives the number of rows satisfying the conditions.

Looking at the tables at the top of the document, let's look at three examples:

SELECT SUM(SALARY), AVG(SALARY) FROM EMPLOYEESTATISTICSTABLE;

This query shows the total of all salaries in the table, and the average salary of all of the entries in the table.

SELECT MIN(BENEFITS) FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Manager';

This query gives the smallest figure of the Benefits column, of the employees who are Managers, which is 12500.

SELECT COUNT(*) FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Staff';

This query tells you how many employees have Staff status (3).

Views

In SQL, you might (check your DBA) have access to create views for yourself. What a view does is to allow you to assign the results of a query to a new, personal table, that you can use in other queries, where this new table is given the view name in your FROM clause. When you access a view, the query that is defined in your view creation statement is performed (generally), and the results of that query look just like another table in the query that you wrote invoking the view. For example, to create a view:

CREATE VIEW ANTVIEW AS SELECT ITEMDESIRED FROM ORDERS;

Now, write a query using this view as a table, where the table is just a listing of all Items Desired from the Orders table:

SELECT SELLERID FROM ANTIQUES, ANTVIEW WHERE ITEMDESIRED = ITEM;

This query shows all SellerID's from the Antiques table where the Item in that table happens to appear in the Antview view, which is just all of the Items Desired in the Orders table. The listing is generated by going through the Antique Items one-by-one until there's a match with the Antview view. Views can be used to restrict database access, as well as, in this case, simplify a complex query.

Creating New Tables

All tables within a database must be created at some point in time...let's see how we would create the Orders table:

CREATE TABLE ORDERS (OWNERID INTEGER NOT NULL, ITEMDESIRED CHAR(40) NOT NULL);

This statement gives the table name and tells the DBMS about each column in the table. Please note that this statement uses generic data types, and that the data types might be different, depending on what DBMS you are using. As usual, check local listings. Some common generic data types are:

Char(x) - A column of characters, where x is a number designating the maximum number of characters allowed (maximum length) in the column.
Integer - A column of whole numbers, positive or negative.
Decimal(x, y) - A column of decimal numbers, where x is the maximum length in digits of the decimal numbers in this column, and y is the maximum number of digits allowed after the decimal point. The maximum (4,2) number would be 99.99.
Date - A date column in a DBMS-specific format.
Logical - A column that can hold only two values: TRUE or FALSE.

One other note, the NOT NULL means that the column must have a value in each row. If NULL was used, that column may be left empty in a given row.

Altering Tables

Let's add a column to the Antiques table to allow the entry of the price of a given Item:

ALTER TABLE ANTIQUES ADD (PRICE DECIMAL(8,2) NULL);

The data for this new column can be updated or inserted as shown later.

Adding Data

To insert rows into a table, do the following:

INSERT INTO ANTIQUES VALUES (21, 01, 'Ottoman', 200.00);

This inserts the data into the table, as a new row, column-by-column, in the pre-defined order. Instead, let's change the order and leave Price blank:

INSERT INTO ANTIQUES (BUYERID, SELLERID, ITEM) VALUES (01, 21, 'Ottoman');

Deleting Data

Let's delete this new row back out of the database:

DELETE FROM ANTIQUES WHERE ITEM = 'Ottoman';

But if there is another row that contains 'Ottoman', that row will be deleted also. Let's delete all rows (one, in this case) that contain the specific data we added before:

DELETE FROM ANTIQUES WHERE ITEM = 'Ottoman' AND BUYERID = 01 AND SELLERID = 21;

Updating Data

Let's update a Price into a row that doesn't have a price listed yet:

UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair';

This sets all Chair's Prices to 500.00. As shown above, more WHERE conditionals, using AND, must be used to limit the updating to more specific rows. Also, additional columns may be set by separating equal statements with commas.