20.3 SQL

One obviously cannot write a meaningful JDBC or Mysql++ database program without reasonable familiarity with SQL. This section explains the syntax of some of the commonly used SQL commands. It is obviously not possible to list here all of the different ways in which a client can interact with a database server using SQL, or even all of the commands that are supported by MySQL. However, we will give a sufficient introduction to SQL so that the reader can start writing useful JDBC and Mysql++ programs.

In what follows, we will first describe some basic SQL commands and list some of the more commonly used data types in SQL. This will be followed by two interactive terminal sessions for demonstrating how one actually uses SQL. The interactive sessions will introduce additional commands and functions of SQL.

Some Basic SQL Commands

To get started with SQL, we will now explain the syntax of the following very commonly used SQL commands:

    CREATE TABLE : creates a new table with given column headings    INSERT       : inserts a new row in a table    SELECT       : retrieves entries from single or multiple tables                   with or without constraints on the entries    UPDATE       : changes table entries

Although, in keeping with the convention, we have shown the SQL commands in upper case, their usage is case insensitive. Let's look at a typical CREATE TABLE statement:

 CREATE TABLE BookTable ( Title           CHAR(20),                          Author          CHAR(20),                          ISBN            CHAR(13) PRIMARY KEY NOT NULL,                          PublisherID     INT,                          PublisherRepID  INT,                          Year            INT,                          NumCopies       INT )

This will create a database table with seven attributes, each corresponding to a separate column in the table. It also declares the data type of each attribute. The first three are of type CHAR(n), where n is the maximum number of characters that the column will hold. The last four are of type INT. With regards to the data types allowed, the SQL 92 specification recognizes the following general types:

exact numerics
approximate numerics
character strings
bit strings
datetimes
intervals

Within each of these general types, there are subtypes:

 exact numerics: INTEGER (or, INT), SMALLINT, NUMERIC, DECIMAL approximate numerics: REAL, DOUBLE PRECISION, FLOAT character strings: CHARACTER or CHAR, CHARACTER(n) or CHAR(n),      CHARACTER VARYING(n)or VARCHAR(n), NATIONAL CHARACTER(n) bit strings: BIT, BIT(n), BIT VARYING(n) datetimes: DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, TIMES      TAMP WITH TIME ZONE intervals: INTERVAL DAY (is the difference between two datetime values)

SQL does not specify the precision associated with the different data types—that's left to the implementations. However, SQL does place some constraints on the relative precision of the related types. For example, the precision of SMALLINT can be no larger than that of INT. However, commonly a SMALLINT is represented by two bytes, and an INT is represented by four. A NUMERIC type has a fractional component, in addition to its integer component. For this type, precision refers to the maximum number of digits to be used for both parts together and scale refers to the number of digits to be used for just the integer part. For example, if you know that all your numbers in a column are going to less than 999.99, you could specify the data type for that column as NUMERIC (5,2). If you don't specify the precision and scale, the system will use a default of 12 for precision and 6 for scale. The DECIMAL type is closely related to the NUMERIC type. While a NUMERIC type can be used to place a constraint on the largest value in a column, there is no such enforcement for a DECIMAL type. For a column type specified as DECIMAL (5,2), a value such as 99999.999 will be acceptable.

The difference between CHAR (n)and VARCHAR (n)is that for the former the system will pad a string with blanks if the number of characters in the data string is fewer than n. No padding is used for the latter.

Getting back to the CREATE TABLE statement shown earlier, note that we declared the ISBN column to be PRIMARY KEY NOT NULL. A KEY is an attribute (meaning, a column heading) that can be used to uniquely identify a row in a table. For a system to access a row, it must have some unique way to identify that row. An attribute declared as a KEY is supposed to serve that function. If you know that no two rows in a table will have the same value for a given attribute, use that attribute as the PRIMARY KEY for the table. Characterization of a key as PRIMARYis supposed to distinguish it from its declaration as a FOREIGN KEY. When an attribute is declared to be a foreign key in a table, it informs the table that the key in question is a primary key in another named table.

So, by definition, an attribute declared to be a PRIMARY KEYwill take unique values in the different rows of a table. Moreover, this attribute will never be allowed to be NULL. In that sense, the declaration PRIMARY KEY NOT NULLis redundant. Another way to declaring an attribute to be a primary key is by using the UNIQUE label, as in

 CREATE TABLE BookTable( Title          CHAR(20),                         Author         CHAR(20),                           ISBN           CHAR(13) PRIMARY KEY NOT NULL,                         PublisherID    INT,                         PublisherRepID INT,                         Year           INT,                         NumCopies      INT,                         UNIQUE( ISBN ) )

Sometimes it is not possible to identify an attribute that will take unique values in a table. In these cases, multiple rows can be declared to be NOT NULL, and the system will use a composite key based on all those rows. Of course, you have to be sure that the entries for those attributes will never be null.

After you have declared a new table with the CREATE TABLE command, you can start inserting information into it with the SQL command INSERT, as demonstrated by

       INSERT INTO BookTable VALUES ( 'Noxious Fumes',                        'Sul Phuric', '3636-7737-10', 3, 101, 1935, 3 )

Each such invocation will enter a new row into the table BookTable. After a table is thus brought into existence, you can examine all its contents by the SELECT command, as in

      SELECT * FROM BookTable

where * will cause all the columns of the table BookTable to be shown for every row. If you only wanted to see the table entries corresponding to the books published in year 2002, you'd say

      SELECT * FROM BookTable WHERE Year = 2002

The following retrieval command shows how the information contained in two different tables of a database can be merged:

      SELECT BookTable.Title,             BookTable.Author,             PublisherTable.PublisherName      FROM   BookTable, PublisherTable      WHERE  BookTable.PublisherID = PublisherTable.PublisherID

In the interactive sessions that follow, also note the use of the following SQL commands:

    UPDATE    ALTER TABLE     DROP TABLE

for doing exactly what the command names imply.

Interactive Session 1

We will now show an interactive session with a MySQL database that demonstrates how SQL can be used to create, query, and modify a database. (This is also a good way to practice SQL.) To start the interactive session, you'd need to invoke the MySQL terminal monitor that allows you to execute command-line SQL. If the database server is running on the same machine on which you will be executing command-line SQL, you may be able to start up the terminal monitor by simply entering in a terminal window the following^[5]

     mysql

If your request for a connection succeeds, you will see a welcome message on your terminal followed by prompt

     mysql>

Now you are ready to type in your SQL statements.

In what follows, we will show the MySQL prompt mysql> flush with the left margin. A user's entries will follow such prompts. The information returned by the database will be shown with an offset from the left margin. All words that are all in uppercase are either SQL commands or SQL keywords. This is just a convention that makes it easy for a human to parse the SQL syntax, but, as mentioned before, SQL itself is case insensitive. Bear in mind that a requirement of MySQL is that each SQL statement be terminated in either a ‘ or ‘\g’. The interactive session that follows will create a rudimentary relational database for the library example of Section 20.1.

One of the first things you'd need to do after firing up the terminal monitor is to tell MySQL which database you'd be using. We will assume that you'll use the "test" database that is supplied with the MySQL installation for testing purposes. To tell the database server that you'd be using the "test" database, you'd need to enter

 mysql> USE test;

Now enter the following SQL statement:

 mysql> CREATE TABLE BookTable ( Title CHAR(20), Author CHAR(20),                ISBN CHAR(13) PRIMARY KEY NOT NULL, PublisherID INT,                   PublisherRepID INT, Year INT, NumCopies INT );

MySQL allows a single SQL statement to be in multiple lines, but the portion of the statement in the last line must terminate in either a ‘;’ or ‘/g’. If you want to review the structure of the table that was just created, you could now enter

 mysql> DESCRIBE BookTable;

MySQL will come back with

Field	Type	Null	Key	Default
Title	char(20)	YES		NULL
Author	char(20)	YES		NULL
ISBN	char(13)		PRI
PublisherID	int(11)	YES		NULL
PublisherRepID	int(11)	YES		NULL
Year	int(11)	YES		NULL
NumCopies	int(11)	YES		NULL

SQL statements such as the following would now insert rows of information into this table:

 mysql> INSERT INTO BookTable VALUES( 'Noxious Fumes',                     'Sul Phuric', '3636-7737-10', 3, 101, 1935, 3 ); mysql> INSERT INTO BookTable VALUES( 'Nasty Nabobs',                     'Dem Ented', '3636-9983-10', 2, 101, 1943, 2 ); mysql> .....

As you are entering new rows into the table, at any time you can see the contents of the table by typing

 mysql> SELECT * from BookTable;

Assuming that you entered the previously shown two INSERT statements and three more (now shown), MySQL will respond to the SELECT command by returning the following for the contents of the BookTable:

Title	Author	ISBN	PID	PRID	Year	NumCopies
Noxious Fumes	Sul Phuric	3636-7737-10	3	101	1935	3
Nasty Nabobs	Dem Ented	3636-9983-10	2	101	1943	2
Nosy Nostrils	Lus Hairs	3687-9983-10	3	103	1998	1
Attila the Hun	Wus Nofun	3687-9944-98	2	101	1989	10
Hairy Hoods	LoLifes	4303-9944-98	3	101	1978	2
Dancing Dollies	Boggy Boogers	4303-0000-98	1	121	1986	1

where we have used the abbreviation PID for "PublisherID" and PRID for "PublisherRepID".

To expand on the interactive session in progress, let's now create a new table, PublisherTable, to hold information about the different publishers.

 mysql> CREATE TABLE PublisherTable ( PublisherID INT,           PublisherName CHAR(20), PublisherLocation CHAR(20),                              PublisherURL CHAR(20) );

If you want to see all the tables currently in the database, you can do so by entering

      SHOW TABLES;

The server will come back with

Tables_in_test
BookTable PublisherTable

To insert an item into the new table, PublisherTable, we will use the INSERT command as before:

 mysql> INSERT INTO PublisherTable VALUES ( 1,               'Warring Warriors', 'New York', 'www.warriors.com' );

If we use a couple of more INSERT commands for this table, in accordance with the second and the third rows shown below, the command

     Select * from PublisherTable

will then yield

PublisherID	PublisherName	PublisherLocation	PublisherURL
1	Warring Warriors	New York	http://www.warriors.com
2	WordSmiths	New Delhi	http://www.wordsmiths.com
3	WordMasters	New Brunswick	http://www.wordmasters.com

We need one more table for our example, which we create with the command

 mysql> CREATE TABLE PublisherRepTable ( PublisherRepID INT,           RepName CHAR(30), RepPhone CHAR(12), RepEmail CHAR(20) );

Inserting items into this table as shown by the rows below, we get the following for its contents:

PublisherRepID	RepName	RepPhone	RepEmail
101	Rip Offer	2347626266	rip@wol.com
103	Hory Details	3527622776	hory@wol.com
121	Nuts Galore	1117232776	nuts@wol.com

With our three-table relational database in place, we are now all set to demonstrate complex queries to the database that require accessing more than one table. For example, the query

 mysql> SELECT BookTable.Title, BookTable.Author,                                 PublisherTable.PublisherName        FROM BookTable, PublisherTable            WHERE BookTable.PublisherID = PublisherTable.PublisherID;

returns

Title	Author	PublisherName
Dancing Dollies	Boggy Boogers	Warring Warriors
Nasty Nabobs	Dem Ented	WordSmiths
Attila The Hun	Wus Nofun	WordSmiths
Noxious Fumes	Sul Phuric	WordMasters
Nosy Nostrils	Lus Hairs	WordMasters
Hairy Hoods	Lo Lifes	WordMasters

We will next illustrate how you can use the UPDATE command to alter a table at any time. Often, any updates to a table would be conditional, meaning that a row would be updated only if it satisfied certain requirements. For example,

 mysql> UPDATE BookTable SET Author = 'Long Hairs'                          WHERE Title = 'Nosy Nostrils';

Now if we enter again

 mysql>     SELECT * FROM BookTable;

we get

Title	Author	ISBN	PID	PRID	Year	NumCopies
Noxious Fumes	Sul Phuric	3636-7737-10	3	101	1935	3
Nasty Nabobs	Dem Ented	3636-9983-10	2	101	1943	2
Nosy Nostrils	Long Hairs	3687-9983-10	3	103	1998	1
Attila the Hun	Wus Nofun	3687-9944-98	2	101	1989	10
Hairy Hoods	Lo Lifes	4303-9944-98	3	101	1978	2
Dancing Dollies	Boggy Boogers	4303-0000-98	1	121	1986	1

where we have again used the abbreviation PID for "PublisherID" and PRID for "PublisherRepID". Here is another updating of BookTable that shows how rows can be updated selectively:

 mysql>    UPDATE BookTable SET NumCopies = NumCopies + 1                                                WHERE Year > 1980;

Displaying BookTable now yields:

Title	Author	ISBN	PID	PRID	Year	NumCopies
Noxious Fumes	Sul Phuric	3636-7737-10	3	101	1935	3
Nasty Nabobs	Dem Ented	3636-9983-10	2	101	1943	2
Nosy Nostrils	Lus Hairs	3687-9983-10	3	103	1998	2
Attila the Hun	Wus Nofun	3687-9944-98	2	101	1989	11
Hairy Hoods	Lo Lifes	4303-9944-98	3	101	1978	2
Dancing Dollies	Boggy Boogers	4303-0000-98	1	121	1986	2

where, once again, we have used the abbreviation PID for "PublisherID" and PRID for "PublisherRepID". To close the session, we say

 mysql>     QUIT

Interactive Session 2

Our previous session showed the basic workings of SQL. We will now show SQL that demonstrates

Entering multiple rows simultaneously into a table using a feature provided by MySQL.
Using the command SELECT with the ORDER BY option to display an ordered version of a database table.
Invoking the SQL set functions to calculate the various statistics of the numerical values in the individual columns of a database table.
Modifying a table structurally with the ALTER command after it is created and populated with data.

We will use the database "test" for this session also. Since we have no further need for the tables we created in the previous session, we can drop them by

 mysql>       DROP TABLE BookTable; mysql>       DROP TABLE PublisherTable; mysql>       DROP TABLE PublisherRepTable;

That should basically empty out the database "test". For the purpose of this interactive session, let's now create a new table TestTable1 by

 mysql>      CREATE TABLE TestTablel (Id INT PRIMARY KEY NOT NULL,                               price DOUBLE(10,2) DEFAULT '0.00' NOT NULL );

If we now enter

 mysql>     DESCRIBE TestTable1;

to review the structure of the table, we get back

Field	Type	Null	Key	Default	Extra
id	int(11)		PRI	0
price	double(10,2)			0.00

So TestTable1 is a table with two columns, id and price, the former of type INT and the latter of type DOUBLE. To insert a row of information into the table, we can say

 mysql>    INSERT INTO TestTable1 VALUES ( 127, 4.98 );

Let's now say that we have a large number of additional entries to make like those shown above. MySQL gives us a way to enter them all at one time in a single SQL statement. In order to demonstrate how that can be done, let's first delete the row just entered:

   mysql>      DELETE FROM TestTable1 WHERE id = 127;

If this table contained multiple rows and we wanted to delete them all with one command, we could just say

 mysql>      DELETE FROM TestTable1;

In either case, TestTable1 is now empty. The following command will now enter multiple rows simultaneously into the table:

 mysql> INSERT INTO TestTable1 VALUES (127, 4.98),                                   (222, 22.22), (111, 11.11);

The command

 mysql>  SELECT * FROM TestTable1;

will now return

id	price
127	4.98
222	22.22
111	11.11

If we want the displayed output to be ordered by price, we can say

 mysql>  SELECT * FROM TestTable1 ORDER BY price;

Now the displayed output would be

id	price
127	4.98
111	11.11
222	22.22

Many useful operations on databases consist of determining the various statistics for the entries in the individual columns of a table. For the example at hand, we may wish to find out the average price charged for all the items in the database, the max price, the min price, and so on. Shown below is a SELECT command that computes such values from the price column of the above table and that also returns the number of non-null items in the id column:

 mysql> SELECT MAX( price ), MIN( price ), AVG( price ),                          SUM( price ), COUNT( Id ) FROM TestTable1;

This returns

MAX(price)	MIN(price)	AVG(price)	SUM(price)	COUNT( Id )
22.22	4.98	12.770000	38.31	3

The functions such as MAX, MIN, and so on, are known as the set functions.

It is possible to modify the structure of a table after it has been created and populated with data. Let's say we wish to add a third column to TestTable1 to keep track of the quantity of each item in the database. We could use the ALTER command for this, as in

 mysql>  ALTER TABLE TestTable1 ADD quantity INT;

Now if we say

 mysql>  DESCRIBE TestTable1;

we will get back

Field	Type	Null	Key	Default
id	int(11)		PRI	0
price	double(10,2)			0.00
quantity	int(11)	YES		NULL

Examining the contents of the table after this structural modification

 mysql>SELECT * FROM TestTable1 ORDER BY price;

we get

id	price	quantity
127	4.98	NULL
111	11.11	NULL
222	22.22	NULL

An updating of this table with

 mysql> UPDATE TestTable1 SET quantity = 2 WHERE price > 10.00;

yields the following for TestTable1 in the database

id	price	quantity
127	4.98	NULL
111	11.11	2
222	22.22	2

^[5]If this does not work, you may have to supply a user name and a password. In general, if the database server is running on a remote machine for which you have access privileges, you can start up the terminal monitor on your local machine by

          mysql -h hostName -u userName -p

where hostName is the name of the remote machine and userName the name you can use to enter that machine. This more general command will prompt you for a password.