Return to index
SQL
Tables are the basic structure where data is stored in the database. Given that in most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, you will need to create tables in the database yourself. Many database tools allow you to create tables without writing SQL, but given that the table is the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.
Before we dive into the SQL syntax for CREATE TABLE, it is a good idea to understand what goes into a table. Tables are divided into rows and columns. Each row represents one piece of data ('record'), and each column can be thought of as representing a component of that piece of data. So, for example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, Address, City, Country, Birth Date, and so on. As a result, when we specify a table, we include the column headers and the data types for these columns.
So what are data types? Typically, data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a string (such as 'sql'), a date (such as '2000-01-25'), or even in binary format. When we specify a table, we need to specify the data type associated with each column. For example, we will specify that 'First Name' is of type CHAR(50) - meaning it is a string with a maximum length of 50 characters). The reason that one needs to specify a maximum length is to increase database access speed by making all records the same size (so increased speed is achieved at the expense of a larger storage size). One thing to note is that different relational databases allow for different data types, so it is wise to consult with a database-specific reference first.
The SQL syntax for CREATE TABLE is
CREATE TABLE table_name
(column_1_name data_type_for_column_1,
column_2_name data_type_for_column_2,
... )
For example :
CREATE TABLE customer
(First_Name CHAR(20),
Last_Name CHAR(20),
Address CHAR(50),
City CHAR(30),
CountryCode INTEGER)
SQL DROP TABLE
Sometimes we may decide that we need to remove - that is, delete - a table in the database for some reason. In fact, it would be problematic if we cannot do so because this would create a maintenance nightmare. Fortunately, SQL allows us to do it by using the DROP TABLE command. The syntax for DROP TABLE is
DROP TABLE table_name
So, if we wanted to drop the table called customer, we can use :
DROP TABLE customer
Sometimes we may wish to remove all the data in a table. One way of doing this is with DROP TABLE. But what if we wish to simply get rid of the data but not the table itself? For this, we can use the TRUNCATE TABLE command. The syntax for TRUNCATE TABLE is:
TRUNCATE TABLE table_name
So, if we wanted to truncate the table called customer, we can use :
TRUNCATE TABLE customer
The easiest way to insert data into a table is one row (record) at a time.
The syntax for inserting data into a table one row at a time is as follows:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
Assume we have a table that has the following structure:
Table Store_Information
Column Name Data Type Store_Name CHAR(50) Num_Sales INTEGER
If we wish to insert one additional row into the table representing the sales data for Los Angeles, we can use the following SQL script:
INSERT INTO Store_Information (Store_Name, Num_Sales)
VALUES ('Los Angeles', 900)
Once we have the information in the table, we might find that there is a need to modify the data. To do so, we can use the UPDATE command. The syntax for this is:
UPDATE table_name
SET column_1 = new_value
[WHERE condition]
The conditional WHERE clause is optional but is normally required as without it, every row will have that column's value set to new_value.
It is easiest to use an example. Say we currently have a table as below:
Table Store_Information
Store_Name Num_Sales Area Los Angeles 1500 West San Diego 250 All Los Angeles 300 East Boston 700 All
We find that the sales for Los Angeles is actually 500 rather than 300, and we need to update that particular entry. To do so, we can use the following SQL:
UPDATE Store_Information
SET Num_Sales = 500
WHERE Store_Name = 'Los Angeles'
AND Area = 'West'
The resulting table would look like this:
Table Store_Information
Store_Name Num_Sales Area Los Angeles 1500 West San Diego 250 All Los Angeles 500 East Boston 700 All
In this case, there is only one row that satisfies the condition in the WHERE clause. If there are two rows that satisfy the condition, both rows will be modified.
It is also possible to UPDATE multiple columns at the same time. The syntax in this case would look like the following:
UPDATE TABLE table_name
SET (column_1, column_2, ...) = (new_value_1, new_value_2, ...)
WHERE condition
Sometimes rather than updating, we wish to remove records (rows) from the table. To do this we can use the DELETE FROM command. The syntax for this is
DELETE FROM table_name
WHERE condition
It is easiest to use an example. Say we currently have a table as below:
Table Store_Information
Store_Name Num_Sales Los Angeles 1500 San Diego 250 Los Angeles 300 Boston 700
and we decide not to keep any information on Los Angeles in this table. We can use the following SQL:
DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles'
The resulting table would look like :
Table Store_Information
Store_Name Num_Sales San Diego 250 Boston 700
The SQL SELECT command is used for retrieving data from the tables in a database. Immediately, we see two keywords: we need to SELECT information FROM a table. (Note that a table is a container (file) that resides in the database where the data is stored). Hence we have the most commonly used SQL statement:
SELECT column_name FROM table_name [WHERE condition]
To illustrate the above example, assume that we have the following table:
Table Store_Information
Store_Name Sales Area Los Angeles 1500 West San Diego 250 All Los Angeles 300 East Boston 700 All
To retrieve a list of all the stores names in table Store_Information we can use the following SQL statement:
SELECT Store_Name FROM Store_Information
Result :
Los Angeles San Diego Los Angeles Boston
Next, we might want to conditionally select - that is, filter - the data from a table. For example, we may want to only retrieve stores with sales above 500 units. To do this, we include the WHERE keyword. The syntax is as follows:
SELECT column_name
FROM table_name
WHERE condition
For example, to select all stores with sales above 500 units in Table Store_Information,
Table Store_Information
Store_Name Num_Sales Los Angeles 1500 San Diego 250 Los Angeles 300 Boston 700
we can use the SQL statement:
SELECT Store_Name
FROM Store_Information
WHERE Num_Sales > 500
Result :
Los Angeles Boston
One can also request more than one column to be returned. The order will be as wriiten in the SELECT statement:
SELECT Num_Sales, Store_Name
FROM Store_Information
WHERE Num_Sales > 500
Result :
1500 Los Angeles 700 Boston
Now consider the case that there are two tables in the database. One holds person names plus an id - and also the id of the car they drive. A second table - that can be referred to as a 'look-up' table - holds the car name for each car id. Database tables usually include at least one column that contains the 'key' - a unique identifier; two people may have the same name, but will have different IDs.
Table person
Id Name Car_id 1 Fred 2 2 Jo 3 3 Kate 1 Table car
code car_name 1 Astra 2 Jeep 3 Ferrari
If we wish to get the name of person with id=2, and the name of the car they drive, we can use the following SELECT statement:
SELECT name, car_name
FROM person, car
WHERE Id=2 AND Car_id=code
Result :
Jo Ferrari
If two tables both have a column with the same name, the table name can be used as a prefix plus '.' to differentiate them. For example: car.id
However, currently TJI does not support this.
| Download and run ... Example JDBC / SQL projects are available from the 'Resources' page of our web site. |