SQL Tutorial
Intro
Sequence Query Language is a type of staticaly typed language for relational databases governed by ISO 63555.
SQL Tutorial Links
W3Schools has a basic SQL Tutorial.
SQL Syntax
- Note that statements are not case sensitive.
- Note that semicolon ";" is required.
SELECT
SELECT - extracts data from a database table.
SELECT is the most basic command in SQL. Given a tablename, you can select entire tables, for example, for table name, "Customers:
SELECT * FROM Customers
This will display the entire table.
Note, if working with SQL Alchemy, the "Query API" is the equivalent of SELECT in SQL.
SELECT DISTINCT
This extracts a specific column from a database table.
SELECT DISTINCT Country FROM Customers;
Will select a column of countries based upon the table Customers.
If we want to do a count of a number of entries in a given column, for example we could do:
SELECT COUNT(DISTINCT Country) FROM Customers;
This would result in the number of records in the Country column.
If you want a label applied to the top of that query, you can use "AS", for example:
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
Which will print out not just a number, but a new column with DistinctCountries as the header and the values in a cell below that.
WHERE
Where can filter a table based upon a condition. For example if you know the CustomerID of a particular customer, you can do:
SELECT * FROM Customers WHERE CustomerID = 1;
Which will display the corresponding row for that customer.
You can of course also display multiple rows, based upon a common criteria, for example:
SELECT * FROM Customers
WHERE Country='Mexico';
Which will display a table showing all data about all customers with the Country being Mexico.
If you wanted to filter that further, you could combine WHERE with SELECT DISTINCT to end up with:
SELECT DISTINCT PostalCode FROM Customers
WHERE CustomerID=1;
Which would display the PostalCode for that customer.
WHERE can operate on both text and numerical fields. Text simply needs '' to function to indicate that a particular value is a string. Numerical values have a variety of range operators that can be used with the WHERE clause, which include all of the standard =, >, >= type operators as well as BETWEEN, LIKE, and IN.
AND, OR, NOT
These can be used in combinaton with WHERE to SELECT data from a table meeting a particular condition. Basically these are logical unions and exclusion statements.
SELECT * FROM Customers
WHERE Country='Germany' OR Country='Spain';
The above would select all customers from either Germany and Spain frmo the Country column.
INSERT INTO
You can insert lines of data into a database with this command.
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
The above will insert the above data values into the table, "Customers."
UPDATE
UPDATE - updates data in a database
DELETE
DELETE - deletes data from a database
INSERT INTO
INSERT INTO - inserts new data into a database
CREATE DATABASE
CREATE DATABASE - creates a new database
ALTER DATABASE
ALTER DATABASE - modifies a database
CREATE TABLE
CREATE TABLE - creates a new table
ALTER TABLE
ALTER TABLE - modifies a table
DROP TABLE
DROP TABLE - deletes a table
CREATE INDEX
CREATE INDEX - creates an index (search key)
DROP INDEX
DROP INDEX - deletes an index