Overview
In this chapter, we will begin the study of the SQL language. This is the Relational database language that will allow us to create a database, insert data into the database, manipulate the data in the database, and retrieve and display the data in the database.
Database Languages
As all flat files can be manipulated with programming languages, a decision had to be made to have more of a dis-connect between programming languages and database structures. When the Hierarchy database model was created, a new language was developed just to be used with the database itself - it was called PL1 (programming language 1).
SQL
When the Relational database model was created, it too needed a database language all it's own. The original language was called Sequel - and later changed to SQL - Structured Query Language. It is still called Sequel, however. Relational Database Management Systems have SQL as part of the package. SQL is the language of the Relational Database Model. It allows for the creation, manipulation, deletion, and physical manipulation of the database and all things it contains (relations, attributes, keys, etc.).
Terms
The full complement of SQL terms includes:
1. Catalog - a set of schemas that, when put together, constitute a description of the database
2. Schema - the structure that contains descriptions of tables, views, and constraints that are part of a database
3. SQL/DDL - data definition language - those commands used in SQL to define a database, creating, altering, and dropping tables and establishing constraints
4. SQL/DML - data manipulation language - those commands used in SQL to maintain and query a database, including updating, inserting, modifying and querying data
5. SQL/DCL - data control language - those commands used to in SQL to control a database, including administering privileges and the committing (saving) of data
SQL Format
The general format for SQL statements is:
SQL STATEMENT (may be more than 1 term together) User Defined Term followed by any additional information needed; (ends with a semi-colon)
Data Types
Here is a list of some of the SQL data types - there are many others:
a. string - CHARACTER or CHAR, CHARACTER VARYING or VARCHAR, BINARY LARGE OBJECT or BLOB
b. number - NUMERIC, INTEGER or INT
c. temporal - TIMESTAMP
d. Booleon - BOOLEON
SQL Statements for Creating a Database
The following are general SQL statements for creating a database:
1. CREATE SCHEMA Y; (used to define a portion of a database that a particular user owns)
2. CREATE TABLE CUSTOMER (Cust_ID INTEGER NOT NULL,Cust_lastname VARCHAR(15),Cust_firstname VARCHAR(12),Cust_street VARCHAR(20),Cust_city VARCHAR(10),Cust_zip NUMBER(5,0),CONSTRAINT CUSTOMER_PK PRIMARY KEY (Cust_ID));
The list of attributes in the table with their data types, length if applicable, followed by the constraints listing the Primary Key of the table is all in ONE set of parenthesis after the table name. Remember to end the statement with a semi-colon.
You also have the ability to add your Foreign Key constraints with the table definition. You need the NOT NULL statement with the primary key attribute.
3. ALTER TABLE CUSTOMER ADD COLUMN Cust_homephone VARCHAR(12);
Other ALTER TABLE actions are: ALTER COLUMN with SET DEFAULT, ALTER COLUMN with DROP DEFAULT; DROP COLUMN, ADD (table constraint)
4. DROP TABLE table name; (this will completely drop the table)
SQL Statement for Inserting, updating and deleting data
The following statements insert, update and delete data in the table:
1. INSERT INTO CUSTOMER VALUES (12345,"Jones","Mary","100 W. Peachtree","Atlanta","GA",30101); This will add one row of data into the CUSTOMER table defined above
**Note - all character data MUST be written inside quotation marks!
2. DELETE FROM CUSTOMER WHERE Cust_state = "AL"; this will delete all rows where the state equals Alabama
DELETE FROM CUSTOMER; this will delete ALL rows in the table - but does not delete the table itself, just the data!!
Querying Tables in SQL
The SELECT statement is the major SQL statement for querying data in the tables. The SELECT statement is very powerful and has many different clauses. The SELECT statement actually displays a "temporary table" on the screen of the user/program using the SELECT statement. Even if the "temporary table" only has one value, it is still a table. This is an important concept to grasp!!
The general format for the SELECT statement is as follows:
SELECT lists the columns that will be included in the display
FROM identifies the table or tables where the columns are located
WHERE includes conditions for selecting rows that meet certain criteria
Examples are:
SELECT ALL from CUSTOMER; This is a simple select statement that will display ALL rows of data (this is how you would print out the data from your table)
SELECT Cust_ID, Cust_firstname, Cust_lastname FROM CUSTOMER; this would list all rows in the table, but will ONLY show the customer ID, customer first name and customer last name.
**The order of the columns in the SELECT statement is how they will be displayed!!
SELECT Cust_ID, Cust_firstname,Cust_lastname FROM CUSTOMER WHERE Cust_zip = 12345; this will list all rows where the zip code is 12345 - but only display the customer ID, customer firstname and customer lastname.
SELECT Cust_ID, Cust_firstname, Cust_lastname, Cust_homephone FROM CUSTOMER WHERE Cust_zip - 30060 AND Cust_state = "GA"; this will display the customer ID, customer firstname and customer lastname only from rows where the zipcode = 30060 AND and customer state is Georgia.
The AND and OR statements can be used with the WHERE statement as needed.
Other SQL Expressions and Functions
Other SQL statements that are used with the SELECT statement are as follows:
AVG, ANY, COUNT, EVERY, GROUPING, MAX, MIN, SOME and SUM
Newer versions of SQL also add: LN, EXP, POWER, SQRT, FLOOR, CEILING, and WIDTH_BUCKET.
Operators
The SELECT statement can also use operators: > (greater than), < (less than) and = (equal). As mentioned above the AND, OR and NOT statements can also be used.
More Complex Statements
Other more complex SQL SELECT statements use additional terms. Some of these are:
a. DISTINCT - to display only unique row values
b. IN or NOT IN to match a list of values
c. ORDER BY sorts the final resulting rows in ascending or descending order
d. GROUP BY groups rows in an intermediate results table
e. HAVING can only be used following a GROUP BY and acts as a secondary WHERE clause, returning only those groups that meet a specific condition
Views in Relational Database Tables
There are times when we want to have a user only have access to certain parts of our tables (certain columns). In order to do this, we don't want to create whole NEW tables - but rather give the user a certain "view" of the table.
A view is a virtual table - it isn't a new table, but instead when we create a view we are actually creating a table of indexes that points to the actual table columns we have selected for the view.
We use the SQL statement CREATE VIEW as the one to create these views. We combine this with a SELECT statement that chooses the actual columns for the "view", and SQL sets up the corresponding indexing table. We will actually have a name for this view that we can query and assign to a user.
**Beware, since this is NOT the actual table - called the BASE TABLE, you do not want to add, change or delete data from the view. If you were to try to do so, you are actually changing the data in the real table.
An example of the statement is: CREATE VIEW NEWCUST AS SELECT Cust_Id,Cust_lastname,Cust_zip;