SQL - Structured Query Language

Introduction

SQL is the main language used to create and manage databases. SQL has built-in functions for inserting, extracting and sortting database data.

Relational Databases

The Rise of relational databases
History of Databses
Short History of Databases
What are relational databases



Programming and Administration

SQL is used in both database programming and database administration, but it is important to understand that these are different diciplines. Programming typically involves creating structuires, ways to store data, transactions, and building interfaces for the database so users are not directly accessing the tables. Administration calls for troubleshooting database problems, scheduling and monitoring backups, supervising upgrades and knowledge of the hardware needs.



Concepts

Databases are made up of tables. Each table is a two-dimentional structure of rows and columns. Data in rows(or tuples) represent individual records. Data in columns represent attributes of various records.

For example, if there is a blue house at 123 Smith Street with 3 bedrooms and a green house at 125 Smith Street with 2 bedrooms, the table might look like this:

HOUSES
ADDRESSCOLORNOBDRMS
123 Smith St.blue3
125 Smith St.green2

ADDRESS, COLOR, and NOBDRMS are column names.
Blue, green, 3, and 2 are column values.
The values in the ADDRESS column are table keys. These keys are unique. There may be several blue houses or several houses with 3 bedrooms, but there is only one 123 Smith St. The key values are used for locating the records which is why they have to be unique.



Heirarchy:

Database Server
    |
    Database
      |
      Table
        |
        Row or Record
          |
          Field or column

A server may have many databases. A database has many tables. A table one or more records. A record has one or more fields.

Queries

Quries are used to pull or modify table data.




Commands and Clauses

 Commands, like SELECT, tell the intepreter what type of query we want to perform and clauses, like WHERE, set the conidtions for the query.


Data Types

Each column in a table has a set format for what kind of data it can hold. Database fields must be assigned one of the built-in data types which tell the database the size and format of each field.






SELECT

SELECT is one of the most common and most basic query commands. SELECT requests information from a database table or tables.

SELECT * FROM jobs

In this case we are requesting all of the data in the jobs(a real jobs table can be found in pubs, a test database found in MSSQL. The FROM keyword is required whenever data is pulled from a table using SELECT. The * is a wildcard which means "anything", usually we would put in more specific information like: SELECT job_id FROM jobs would pull data just from the job_id field in jobs. If the table is empty, a message like no rows selected might be returned.

So, the basic structure is:
SELECT column or criteria FROM tablename


SELECT is also used to obtain a count or rows in a table(or how many records):
SELECT COUNT(*) FROM jobs should return a number like 14.


The WHERE clause is added to a SELECT statement to add conditions to the query and filter the results:

SELECT *
FROM jobs WHERE job_id = 5

This will pull only the row or rows where the job_id is 5.

SELECT *
FROM jobs WHERE job_id > 5

Pulls all the rows with a job_id greater than 5


ORDER BY helps sort output in a format we want:

SELECT *
FROM jobs ORDER BY job_desc

Places the output in alphabetical order by the job_desc


In addtion to using operators like =, < and > we may also use the LIKE keyword, which allows us to build wildcard strings in queries.


SELECT *
from jobs
WHERE job_desc LIKE '%Chief%'

This will pull any record that contains with string "Chief" in job_desc. The % act as wildcards, which means "any character." If we changed it to LIKE 'Chief%' the query would produce only the row with "Chief" at the begining of the string.


Cartesian Product: The result of a poorly constructed query where the total contents of two tables are combined and displayed in a useless order. If there are 25 rows in each table the query will produce 625 results, the total possible number of combinations.

Example:
SELECT * from countries, regions



DESCRIBE

Shows the table attributes, how many columns and what the data types are.

DESCRIBE jobs

Not available in MS SQL, use SP_COLUMNS



INSTERT INTO

Puts values into tables, creates rows.

INSERT INTO jobs
VALUES ('plumber', 101, 10);

This will create a new row in jobs with these values in the fields. The first column is the job_id and it is left out of the parenthesis next to VALUES beacuse it is primary key generated by the database when a new row is created in this table. The other values, for the columns job_desc, min_lvl, and max_lvl must be in the same order as they are in the table and use the format of the specified data type. If you only wanted add one or two fields to a record you would have to be more specific in the INSERT statement:

INSERT INTO jobs(job_desc, max_lvl)
VALUES ('driver', 75);

However, you may not be able to do this because many columns will not accept null values. Use DESCRIBE on the table to see what the attributes are.



CREATE TABLE

Creates a table structure in the database with our attributes. You must put all the column names and data types in the statement:

CREATE TABLE employees
(
name      varchar2(20),
address      varchar2(20),
employee_number      number(4),
salary      number(6,2)
);



DROP TABLE

Deletes or removes a table:

DROP TABLE employees



ALTER TABLE

Allows you to change exisiting table structures.

ALTER TABLE jobs
ADD floor int

Will add a column to jobs called "floor" of type integer



Functions

These are built-in mathematical and string procedures for counting, obtaining sums and calculating averages and more. COUNT, sum, min, max, avg, others.



MS SQL Stored Procedures

MS SQL has a large number of built-in stored prodecures which can be called from the query window. See a full list here.


Common database applications

Oracle
MySql
Sybase
MSSQL Server
MS Access
dBase
Pervasive SQL
Visual FoxPro
MiniSQL


Resource Links

Teach Yourself Microsoft SQL Server 6.5 in 21 Days(full text!)
Tutorial: SQL Databases
Swynk.com:SQL library
Full SQL-92 commands
A list of tutorials related to Structured Query Language (SQL).
SQL-Zone
Live Practice Database(Great!)
SQLSchool
Chapter 1 of O'Reilly's T-SQL Book
Using SQL Server Stored Procedures with ASP
MySQL SQL Manual Version 0.41
Creating a SQL Server database
Programming Data Access Pages


Oracle/SQLPlus Cheatsheet and Glossary

By Topic

The Relational Database and the Structure of SQL

Simple two-dimensional tables developed by E.F. Codd. The dimensions are rows and columns. A Row or tuple holds data for a record. Each piece of data for the row exists in separate columns. All items in a row are part of the same record.A Column or an attribute in a table holds a particular type of data that changes from row to row. Items in columns are parts of different records. Parent-child relationships.

SQL Commands

SELECT, INSERT, CREATE, UPDATE, DELETE, ROLLBACK, COMMIT, SAVEPOINT, ALTER, etc.

The Cartesian Product

The result of a poorly constructed query where the total contents of two tables are combined and displayed in a useless order. If there are 25 rows in each table the query will produce 625 results, the total possible number of combinations. SELECT * from countries, regions;

Join

A SELECT statement that connects two tables. A join:
SELECT *
FROM emps, jobs
WHERE emps.jobcode = jobs.jobc;

Aliases

Allows the creation of abbreviated table names, temporary, stored in memory for run of query.
select stu.sname
from student stu, grade_report g
where stu.major = 'cosc'
and stu.stno = g.student_number

Functions

Named PL/SQL blocks that return a value and can be called with arguments. Aggregates: count, sum, min, max, avg. Return results based on multiple rows(sometimes called group-functions). Row functions Or String functions, as opposed to aggregate functions. Used for sorting: SUBSTR, INSTR, RPAD, LPAD, LENGTH, LIKE, UPPER, LOWER, NVL.

Matching using LIKE

Allows SELECT statements with partial information.
SELECT * FROM student
WHERE sname LIKE ‘%SMITH%’
Privileges – Who can do what on a table Give privileges to users:
GRANT SELECT
ON student
TO jsmith;
Remove previously granted privileges:
REVOKE SELECT
ON student
FROM jsmith;

Derived Structures

Views, snapshots, temporary tables, inline views. A view:
CREATE OR REPLACE VIEW a_studs AS
select s.sname, g.grade, s.major
from student s, grade_report g
where s.stno = g.student_number
and g.grade = 'A'
A snapshot:
CREATE SNAPSHOT qsnap1
REFRESH COMPLETE NEXT SYSDATE + 7 AS
SELECT * FROM Student WHERE stno < 20;

Set Operations

UNION, INTERSECT, MINUS. Binary union – Set operation on two sets where the result contains all the unique elements in both sets Binary intersection – Generates unique values in common between two sets Binary set difference – Returns the difference in one set less those contained in the other set Relations – Sets of rows

Joins V. Sub Queries

A question of efficiency.

Inner and outer querries:
SELECT student.sname
FROM student
WHERE student.stno IN <-- outer query runs second
(SELECT g.student_number
FROM Grade_report g
WHERE g.grade = 'A'); <-- inner query, runs first

GROUP BY

To be used with aggregate functions. Return one row for each value of the columns that is grouped.
SELECT class, COUNT(*)
FROM student
GROUP BY class

HAVING

A filter at the tail end of a select statement, usually after the group by has been applied.
SELECT class, COUNT(*)
FROM student
GROUP BY class
HAVING COUNT(*) > 9;

Correlated subqueries

A subquery with a subquery within which is referenced by an outer main query. Correlated subquries cannot stand alone, they depend on the outer query.
SELECT *
FROM STUDENT S
WHERE EXISTS
(SELECT g.student_number
FROM Grade_report g
WHERE grade = 'B'
AND s.stno = g.student_number);
Non-correlated version:
SELECT *
FROM STUDENT S
WHERE s.stno IN
(SELECT g.student_number
FROM Grade_report g
WHERE grade = 'B');

CREATE TABLE

Creating tables
CREATE TABLE CUSTOMER
(
CNO NUMBER(4) NOT NULL,
CUSTOMER_TYPE VARCHAR2(10),
BALANCE NUMBER(8,2) DEFAULT 0
);
START FILES – *.sql files that hold instructions or querries. Called from the command line like this SQL> START myScript or SQL> @mySript

Reports and formatting

Special sequences to add headers and formatting to query results.
Add column headings:
select student_name "Student Name",
address "Student Address"
from students

set linesize 50
set pagesize 500
ttitle 'My query'

col COURSE_ID heading 'Course No' format a6
col SECTION_ID heading 'Section' format 999
col STUDENT_ID heading 'Student No' format 999
col GRADE heading 'Student Grade' format 99.99
ttitle sets the title name
col sets the column parameters
heading indicates the heading name
format sets the formatting for the column
btitle places title at bottom
linesize is the number of chars that appear on a line
pagesize is the number of lines per page
newpage prints a blank line

Number formatting
999
999.99
9999.99
9990.00

Letter formatting
A2, A10

SQLLOADER

Used to populate tables from a host file

Alphabetical


ACCEPT

in a .sql script file tells SQLPLUS to accept input from the keyboard.
ACCEPT tab1 PROMPT ‘Enter table name -->’
SELECT * FROM &tab1

ADD

Used with ALTER TABLE
ALTER TABLE
ADD col-name type

Aggregate Functions

count, sum, min, max, avg. Return results based on multiple rows(sometimes called group-functions).

Aliases

Allows the creation of abbreviated table names, temporary, stored in memory for run of query.
select stu.sname
from student stu, grade_report g
where stu.major = 'cosc'
and stu.stno = g.student_number

ALTER

ALTER TABLE changes the table parameters(the columns). ALTER TABLE tablename ADD col-name type Example:
ALTER TABLE customer
ADD address VARCHAR2(20);
Adds a column called address that holds strings
ALTER TABLE customer
MODIFY balance NUMBER (8,2)
Changes the balance column from whatever type it was before to NUMBER
ALTER TABLE STUDENTS
ADD CONSTRAINT STUDENT_ID_PK PRIMARY KEY (STUDENT_ID)
Changes a constraint on a table

AND

For narrowing query results.
select s.sname, g.grade, s.major
from student s, grade_report g
where s.stno = g.student_number
and g.grade = 'A'

AS

Used when creating views or snapshots.
CREATE SNAPSHOT qsnap1
REFRESH COMPLETE NEXT SYSDATE + 7 AS
SELECT * FROM Student WHERE stno < 20;

AVG

Aggregate function that returns the average of various row values.
SELECT AVG(SALARY)
FROM EMPLOYEES

AUTOTRACE

Creates a stack trace of the query, what resources were used, etc.
SET AUTOTRACE ON

SET AUTOTRACE OFF
Cartesian Product - The result of a poorly constructed query where the total contents of two tables are combined and displayed in a useless order. If there are 25 rows in each table the query will produce 625 results, the total possible number of combinations.
SELECT * from countries, regions;

Checks

(C), a constraint. Ensures that a field value falls within certain parameters like a range of accepted values.

COLUMN

Used for formatting.
SELECT grades
FROM students
COLUMN “Grades” FORMAT a15
Puts “Grades” at the top of the output column

Commenting

Entries in SQL statements that are not read by the query engine. -- single line comment
/* c-style multi-line comments */

COMMIT

finalizes changes made to a database during a session.
COMMIT;

COMPLETE

Used with SNAPSHOTS following REFESH
CREATE SNAPSHOT empData
REFRESH COMPLETE NEXT SYSDATE + 7 AS
SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY FROM employees

CONSTRAINT

Primary Keys, Unique Constraints, Foreign Keys, Checks, With Check Option. Limits what data can or cannot go into a column.

Correlated Subquery

A subquery with a subquery within which is referenced by an outer main query. Correlated subquries cannot stand alone, they depend on the outer query.
SELECT *
FROM STUDENT S
WHERE EXISTS
(SELECT g.student_number
FROM Grade_report g
WHERE grade = 'B'
AND s.stno = g.student_number);
Non-correlated version:
SELECT *
FROM STUDENT S
WHERE s.stno IN
(SELECT g.student_number
FROM Grade_report g
WHERE grade = 'B');

COUNT

Aggregate function that returns the count of row values.
SELECT COUNT(*)
FROM STUDENTS

CREATE

Used for building tables, views, snapshots, etc.
CREATE TABLE CUSTOMER
(
CNO NUMBER(4) NOT NULL,
CUSTOMER_TYPE VARCHAR2(10),
BALANCE NUMBER(8,2) DEFAULT 0
);

DATE

The Date data type. Use the TO_DATE function to manipulate data into dates.

DATA TYPES

Main variations: characters, numbers, data/time, and binary. Specific types: BOOELAN, CHAR, DATE, INTEGER, LONG, LONG RAW, NUMBER, VARCHAR2, RAW, LOB, ADT, SMALLINT

DDL

Data Definition Language. ALTER, CREATE

DELETE

Remove data from a table.
DELETE FROM Customer
WHERE balance < 0;

DESC

Same as DESCRIBE

DESCRIBE

Describes the structure of a table.
DESCRIBE customer

Derived Structures

Views, temporary tables, inline views, snapshots.

DISTINCT

Ignores duplicates.
SELECT DISTINCT grade
FROM grade_report
Might produce: a, b, c, d, f where SELECT grade would produce all the values in the table.

DML

Data Manipulation Language. SELECT, INSERT

DROP

As in DROP TABLE, removes tables from the db.
DROP TABLE EMPLOYEE

FROM

Specifies which table a SELECT statement refers to.
SELECT class
FROM students

FOREIGN KEY

References a PRIMARY KEY in another table

FORMAT

For reports, sets the column format.

Functions

Named PL/SQL blocks that return a value and can be called with arguments.

GRANT

Give privileges to users.
GRANT SELECT
ON student
TO jsmith;

GROUP BY

To be used with aggregate functions. Return one row for each value of the columns that is grouped.
SELECT class, COUNT(*)
FROM student
GROUP BY class

HAVING

A filter at the tail end of a select statement, usually after the group by has been applied.
SELECT class, COUNT(*)
FROM student
GROUP BY class
HAVING COUNT(*) > 9;

IN

Used in subqurries to refer to the subquery.
SELECT COURSE_NAME, COUNT(*)
FROM COURSE
WHERE COURSE_NUMBER IN
(SELECT COURSE_NUMBER
FROM PREREQ)
GROUP BY COURSE_NAME

INSERT INTO

Place values into table rows.
INSERT into employee
VALUES ('Joe Smith', '123 4th St.', 101, 2500);
INSERT into employee(name, address, salary)
VALUES ('Frank Jones', '123 Main St St.', 2500);

INSTR

Checks to see if a value is in the string.
select instr(last_name,'a'),last_name
from employees;

INTEGER

A data type, holds whole numbers.

INTERSECT

Binary intersect, generates unique values in common between two sets.
select sname, major
from student
where major = 'COSC'
INTERSECT
SELECT sname, major
from student
where major = 'MATH';

JOIN

A SELECT statement that connects two tables.
SELECT *
FROM emps, jobs
WHERE emps.jobcode = jobs.jobc;

Keys

Primary, Foreign, Concatenated key

LIKE

Allows SELECT statements with partial information.
SELECT * FROM student
WHERE sname LIKE ‘%SMITH%’

LOWER

Force query results to lower case.
SELECT *
FROM student
WHERE LOWER(sname) LIKE ‘%smith%’

Materialized views

See snapshots.

MAX

Aggregate function that returns the largest item in a set.
SELECT MAX(SALARY)
FROM JOBS

MIN

Aggregate function that returns the smallest item in a set.
SELECT MIN(SALARY)
FROM JOBS

MINUS

Return the difference of two sets
select sname, major
from student
where major = 'COSC'
MINUS
SELECT sname, major
from student
where major = 'MATH';

MODIFY

Used with ALTER to change column parameters.
ALTER TABLE STUDENTS
MODIFY FIRST_NAME NOT NULL

NEXT

Used with snapshots.
CREATE SNAPSHOT empData
REFRESH COMPLETE NEXT SYSDATE + 7 AS
SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM employees

Non-Correlated Subquery

Non-correlated
select s.sname
from student s
where s.stno in (select gr.student_number
from grade_report gr
where gr.grade = 'A');
Correlated
select s.sname
from student s
where s.stno in
(select gr.student_number
from grade_report gr
where gr.student_number = s.stno
and gr,grade = 'A');

NOT IN

Used in subqurries to refer to the subquery, opposite of IN.
SELECT COURSE_NAME, COUNT(*)
FROM COURSE
WHERE COURSE_NUMBER NOT IN
(SELECT COURSE_NUMBER
FROM PREREQ)
GROUP BY COURSE_NAME

NOT NULL

A constraint on a column, field must be populated.
CREATE TABLE STUDENTS
(YOG DATE NOT NULL);

NULL

An empty field. Use NVL to replace a NULL with a designated value.

NUMBER

A data type. NUMBER(4) - four digits no decimals, NUMBER(6,2) – 6 digits including 2 decimals.

NVL

Use NVL to replace a NULL with a designated value
SELECT NVL(SALARY, 0)
FROM EMPLOYEE
Replaces a blank column with zero

ORDER BY

Sets return sequence by a designated value.
select d.DEPARTMENT_NAME, count(*), min(SALARY), max(SALARY)
from EMPLOYEES e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
group by d.DEPARTMENT_NAME
ORDER BY count(*)

Outer Join

A join where all the rows from one table are kept in the result set even they may not match rows in the other table. The “driving table” is the table accessed first in the join. In the case below, “regions” is the driving table.
select COUNTRY_NAME, region_name
from countries, regions
where countries.region_id(+) = regions.region_id

PRIMARY KEY

(P) - These keep duplicate entries for the field that is a record’s primary key. The unique key allows records (tuple or row) to be found easily. Assumed NOT NULL.

PROMPT

in a .sql script file tells SQLPLUS to issue a message to the user.
ACCEPT tab1 PROMPT ‘Enter table name -->’
SELECT * FROM &tab1

REFRESH

Used with snapshots.
CREATE SNAPSHOT empData
REFRESH COMPLETE NEXT SYSDATE + 7 AS
SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY
FROM employees

REM

comment out lines in a script file

REPLACE

Called to update a derived structure.
create or REPLACE VIEW a_studs as
select s.sname, g.grade, s.major
from student s, grade_report g
where s.stno = g.student_number
and g.grade = 'A'

REVOKE

Remove previously granted privileges.
REVOKE SELECT
ON student
FROM jsmith;

ROLLBACK to

If later we entered “ROLLBACK TO SAVEPOINT backup1” the state of the database would be restored and the changes made since would be deleted. You cannot ROLLBACK if you have used COMMIT or logged out. ROLLBACK is like an “undo” command.

Row functions

Or String functions, as opposed to aggregate functions. Used for sorting.

SAVEPOINT

A SAVEPOINT is a kind of bookmark that we may revert to with ROLLBACK. Entering “SAVEPOINT backup1” during a session will preserve the state of the database before we created the SAVEPOINT

SELECT

Query tables.
SELECT *
FROM EMPLOYEES
Gets all the data
SELECT ATTRIBUTES
FROM Table;
Information about the columns

SNAPSHOT

Also called materialized views. Where views contain updated, current data. Snapshots must be refreshed.
CREATE SNAPSHOT qsnap1
REFRESH COMPLETE NEXT SYSDATE + 7 AS
SELECT * FROM Student WHERE stno < 20;

SPOOL

Print the result of a query command
SQL> SPOOL out
Sends results to a file called “out”

SUBSTR

A string function. SUBSTR(column, start-point, how-many).
SELECT SUBSTR(name, 2, 4)
FROM employees
Would return characters 2, 3, 4, 5 in name

SUM

Aggregate function that returns the total of items in a set.
SELECT SUM(SALARY)
FROM EMPLOYEES

TABLE

The basic structure of a database, made up of columns and rows. A Row or tuple holds data for a record. Each piece of data for the row exists in separate columns. All items in a row are part of the same record. A Column or an attribute in a table holds a particular type of data that changes from row to row. Items in columns are parts of different records.
CREATE TABLE CUSTOMER
(
CNO NUMBER(4) NOT NULL,
CUSTOMER_TYPE VARCHAR2(10),
BALANCE NUMBER(8,2) DEFAULT 0
);

TIMING

Turn on or off timing statistics.
SQL> SET TIMING ON
SQL> SET TIMING OFF

WHERE

Adds a condition to a SELECT statement.
SELECT LAST_NAME, CITY
FROM EMPLOYEES e, LOCATION_INFO lo
WHERE e.DEPARTMENT_ID = lo.DEPARTMENT_ID
ORDER BY CITY;

Wild Cards

In order to retrieve data when not all the search criteria is known. With *, % and LIKE.
SELECT * FROM employees <-- get “whatever”, all rows
SELECT COUNT(*) FROM EMPLOYEES <-- count rows
SELECT name
FROM students
WHERE name LIKE ‘%son%’ <-- if “son” appears in the string
SELECT *
FROM section
WHERE course_num LIKE ‘__2___’
any char in the first two slots, then “2” then any three chars

With Check Option

(V) A view-wide constraint similar to Checks

UNION

Returns unique items of two sets.
select sname, major
from student
where major = 'COSC'
UNION
SELECT sname, major
from student
where major = 'MATH';
Unique Constraints - (U) Similar to primary in that the value is unique in the table, but a field that is a unique constraint is not the primary key for the record. Can be NULL.

UPDATE

Modify the values in more than one row.
UPDATE Customer
SET balance = 0;
Changes all the balance column values to zero

UPPER

Force query results to upper case.
SELECT *
FROM student
WHERE UPPER(sname) LIKE ‘%SMITH%’

VALUES

Refers to data being populated in rows.
INSERT into employee(name, address, salary)
VALUES ('Frank Jones', '123 Main St St.', 2500);

VARCHAR

A data type. VARCHAR(20) a string with 20 characters.

VIEW

Creates a derived structure that refers to tables.
CREATE OR REPLACE VIEW a_studs AS
select s.sname, g.grade, s.major
from student s, grade_report g
where s.stno = g.student_number
and g.grade = 'A'

/

Used to end an SQL statement, especially in a .sql file.

*

Wildcard, used with COUNT or SELECT
SELECT * FROM employees
SELECT COUNT(*) FROM EMPLOYEES

;

Used to end SQL statements

(+)

Used in outer joins to indicate the non-driving table, the one where all the rows are called.
SELECT e.FIRST_NAME, e.LAST_NAME,
j.JOB_TITLE, e.SALARY, j.MAX_SALARY
FROM employees e, JOBS j
WHERE e.JOB_ID(+) = j.JOB_ID AND e.HIRE_DATE > '01-JAN-99'

%

Wildcard, used with LIKE