SQL 101

SQL is a simple languadge with a Simple Syntax to manage Relational Databases

SQL Statements:

A statement is the unit of execution in SQL

SELECT * FROM Countries WHERE Continent = 'Europe';

An SQL statement usually includes 1 or more Clauses

FROM clause

• This specifies which table the Statement will use

WHERE clause

• This specifies which rows will be selected

The WHERE clause requires an EXPRESSION: Logical Expression:

Continent = 'Europe'

The 4 Fundamental Database functions are CRUD

• Create

• Read

• Update

• Delete

The SELECT statement is used for ALL STATEMENTS that retrieve values. - (This is the R in CRUD)

The INSERT statement is used to ADD A ROW to a TABLE - (This is the C in CRUD)

The UPDATE statement is used to CHANGE DATA - (This is the U in CRUD)

The DELETE statemnt is used to DELETE ROWS from a TABLE - (This is the D in CRUD)

The WHERE clause is used to select which row or rows are to be UPDATED

Purpouse of a Database is to organize your data and make it available in convinient from. SQL is a Languadge for managing a RELATIONAL DATABASE. This is organized in 2 dimentional tables comprised of ROWS and COLUMNS

A Database has Tables. Can have 1 or more tables which are organized in ROWS and COLUMNS:

A ROW is like an individual RECORD in the table. A COLUMN is like a FIELD in the table. EACH ROW in the TABLE has a UNIQUE KEY. Sometimes the KEY is HIDDEN but a TABLE must have one.

When a COLUMN is used as a UNIQUE KEY it is often called the PRIMARY KEY

In the above example the DEPT_ID COLUMN is used as the PRIMARY KEY.

Note: Different Engines do this differently but there is ALWAYS a UNIQUE KEY.

The TABLES are RELATED by the TABLES KEYS

In this example the ADMIN TABLE has COLUMNS for DEPT_ID that make reference to the DEPT TABLE This is called aFORIGN KEYbecause it refers to the KEYS of OTHER TABLES.

These relationships allow to make JOINT QUERIES, to treat ROWS and SEVERAL TABLSE as JOINT UNITS.

SQL Statement Analysis

SELECT Statement:

SELECT ‘Hello, World’ AS Result;

• This uses a SELECT statement to retrieve information. Simple quotations ' ' are used to select a STRING.

• AS is a KEYWORD.

• When you execute the Query it changes the COLUMN name to Result.

To SELECT all from a TABLE use:

SELECT * FROM Country;

If we want them in a Different order you can use ORDER BY:

SELECT * FROM Country ORDER BY Name; 

If we don’t want all the rows you can specify the names denominated by a “,”:

SELECT Name, LifeExpectancy FROM Country ORDER BY Name; 

Example: This might not result right so you might want to give an alias for LifeExpectancy to be AS Life Expectancy

SELECT Name, LifeExpectancy AS “Life Expectancy” FROM Country ORDER BY Name; 

Note: You have to use Double Quote “ “ The Quotes are needed as the IDENTIFIER has SPACES in it, if it wouldn’t then you don’t even need the Quotes.

SELECT Name, Continent, Region FROM Country WHERE Continent = ‘Europe’; 

• This selects the 3 columns NAME, CONTINENT, REGION from the Table Country where the Continent Column is Europe.

If we want them in a particular order we can use ORDER BY for example:

SELECT Name, Continent, Region FROM Country WHERE Continent = ‘Europe’ ORDER BY name;

If we want to LIMIT the results we can use the LIMIT CLAUSE:

SELECT Name, Continent, Region FROM Country WHERE Continent = ‘Europe’ ORDER BY name LIMIT 5;

This LIMITS the results to the first 5 results. So the above, orders in Alphabetical order by NAME and then shows only the first 5 RESULTS as opposed to the 46 ROWS without LIMIT.

• If we want to skip the first 5 but want the next 5 we can use the OFFSET CLAUSE:

SELECT Name, Continent, Region FROM Country WHERE Continent = ‘Europe’ ORDER BY name LIMIT 5 OFFSET 5; 

QUIRCKS OF SQL:

• The ORDER BY clause has to be AFTER any WHERE clause (if used)

• LIMIT and OFFSET have to be LAST

• FROM needs to be FIRST after the SELECT STATEMENT.

SELECT Name AS Country, Continent, Region FROM Country;

• This RENAMES the NAME Column as COUNTRY

You can also swap the order by changing the order of the COLUMNS after the SELECT STATEMENT:

SELECT Name AS Country, Region, Continent FROM Country;

COUNT function allows you to count the ROWS within a table.

SELECT COUNT (*) FROM Country; 

You can limit the RESULTS by doing

SELECT COUNT (*) FROM Country WHERE Population > 1000000;

• This will COUNT the rows only where the population is greater then 1 mil.

To limit by other factors too you can use AND

SELECT COUNT (*) FROM Country WHERE Population > 1000000 AND Continent = ‘Europe’;

COUNT (*) is a Special case as it counts all the rows, if we use COUNT (LifeExpectancy) it will have a different number as it will count ONLY THE ROWS WITH DATA IN THEM.

INSERT Statement:

To insert Values into a table you can use the insert statement:

AFTER:

INSERT INTO customer (name, address, city, state, zip)
    VALUES (‘Fred Flintstone’, ‘123 Cobblestone Way’, ‘Bedrock’, ‘CA’, ‘9123’);

• This inserts the values in each field. When you specify the name of the column you have to order the values as well.

• I.e. Name = Fred Flintstone.

You can also insert information just in some fields and the other ones will be denoted by NULL:

INSERT INTO customer (name, city, state)
	VALUES (‘Jimi Hendrix’, ‘Renton’, ‘WA’); 

UPDATE Statement:

TO CHANGE DATA in a TABLE we use the UPDATE STATEMENT:

UPDATE customer SET address = ‘123 Music Avenue’, zip = ‘98056’ WHERE id=5;

IF THE WHERE CLAUSE DOES NOT EXIST IT WILL DO IT TO THE ENTIRE TABLE! SO YOU HAVE TO LIMIT IT!

• This will populate the Address field and Zip field with the strings provided where the ID of the entry is 5.

To update already populated field it is the same command but with new strings:

UPDATE customer SET address = ‘Washington ST’, zip = ‘9999’ WHERE id=5;

To add NULL values you will have to specify this in the UPDATE statement:

UPDATE customer SET address = NULL, zip = NULL WHERE id=5;

MAKE SURE NOT TO SET THEM AS STRINGS AS THESE WILL BE POPULATED AS SUCH.

DELETE Statement:

DELETE FROM customer WHERE id = 4;
SELECT * FROM customer;

• This deletes from the customer TABLE the entry with ID = 4 and then SELECTS everything to see if this has been deleted by the user.

CREATING A TABLE:

CREATE TABLE test (
	a INTEGER,
	b TEXT
);

• The part between the () is the SCHEMA

• Syntax: COULMN NAME = A and the TYPE of the COLUM = INTEGER

• THE LAST ROWN WITHIN THE () CANNOT HAVE A COMMA (,) - This will generate a syntax error.

TO DELETE A TABLE we use the DROP TABLE Statement:

DROP TABLE test;

• This deletes the table

DROP TABLE IF EXISTS test; 

• This does not return an error if the table does not exist.

Inserting Rows:

INSERT INTO test ( b, c) VALUES ( ‘One’, ‘Two’); 

• Inserts values only in Column B and C leaving Column A with the NULL value.

If you want to insert a table with no values in it use:

INSERT INTO test DEFAULT VALUES;

• This creates a empty row with the NULL values

TO ADD VALUES FROM A DIFFERENT TABLE YOU CAN DO:

INSERT INTO test ( a, b, c, ) SELECT id, name, description from item 

• This selects the data from the ITEM TABLE to place it in each column a = id b = name c = description THIS ADDS ALL ROWS FROM THE TABLE SPECIFIED.

DELETING ROWS:

DELETE FROM test WHERE a = 3; 

• This will delete ROW where the INTEGER value = 3 on the A COLUMN.

DELETING ROWS IS DESTRUCTIVE SO IT IS GOOD TO AUDITION THIS BEFORE WITH:

SELECT * FROM test WHERE a = 3; 

The NULL value – Is not a value, is the absence of a value. To test for NULL you have to use

SELECT * FROM test WHERE a IS NULL; 

The opposite of NULL is:

SELECT * FROM test WHERE a IS NOT NULL; 

To CREATE a TABLE that does not accept NULL values use:

CREAT TABLE test (
a INTEGER NOT NULL,
b TEXT NOT NULL,
c TEXT
); 

If you do:

INSERT INTO test (a, c) VALUES (‘one’, ‘two’); 

It will result in a ERROR:

Error while executing SQL query on database ‘test’: NOT NULL constraint failed test.b.

• This is because we delimited the B COLUMN to not accept NULL and we are not inserting a value in it with the INSERT above

CONSTRAINING COLUMNS:

To add constraints when creating a TABLE you can do:

CREATE TABLE test (a TEXT, b TEXT, c TEXT NOT NULL);
INSERT INTO test (a, b) VALUES (‘one’, ‘two’);
SELECT * FROM test;

• THIS WILL FAIL. It fails because we have set a constraint for the C COLUMN to be any value except NULL.

• And then we are trying to insert values in A & B but NOT C and the C Column does not accept to be NULL.

DEFAULT VALUE:

CREATE TABLE test (a TEXT, b TEXT, c DEFAULT ‘panda’);
INSERT INTO test (a, b) VALUES (‘one’, ‘two’);
SELECT * FROM test;

• NOW with this when we fail to add data to C it will set the DEFAULT value of the entry to ‘panda’.

UNIQUE VALUE:

CREATE TABLE test (a TEXT UNIQUE, b TEXT, c DEFAULT ‘panda’);
INSERT INTO test (a, b) VALUES (‘one’, ‘two’);
INSERT INTO test (a, b) VALUES (‘one’, ‘two’);
SELECT * FROM test;

• THIS WILL FAIL because we have set the A COLUMN to accept only UNIQUE values and then we are trying to insert the same values 2 times. Fails with: UNIQUE constraint failed test.a

IF WE CHANGE THE VALUE FOR A IT WILL WORK:

CREATE TABLE test (a TEXT UNIQUE, b TEXT, c DEFAULT ‘panda’);
INSERT INTO test (a, b) VALUES (‘one’, ‘two’);
INSERT INTO test (a, b) VALUES (‘uno’, ‘two’);
SELECT * FROM test;

• NULL is Exempted from the UNIQUE constraint.

You can also combine CONSTRAINTS:

CREATE TABLE test (a TEXT UNIQUE NOT NULL, b TEXT, c DEFAULT ‘panda’);

CHANGING A SCHEMA:

If you need to change the schema for example add a new COLUM you can do:

ALTER TABLE test ADD d TEXT;

ID COLUMNS:

When creating a table:

CREAT TABLE test (
	id INTEGER PRIMARY KEY,
	a INTEGER,
	b TEXT
); 

The ID COLUMN will be used as a KEY and it will give SEQUENCIAL UNIQUE NUMBERS to then field. It does this whether you have it or not, these are very common in RELATIONAL DATABASES. It makes it easier to create database RELATIONS between tables and rows.

FILTERING DATA:

SELECT Name, Continent, Population FROM Country
	WHERE Population < 100000 ORDER BY Population DESC;

THE WHERE CLAUSE ALLOWS YOU TO FILTER DATA: KIND OF LIKE PIPE IN LINUX:

ls -al | grep something

• The SELECT STATEMENT will bring DATA from the COLUMNS Name, Continent, Population from the COUNTRY TABLE

• WHERE Population is less then 100 000 and it will ORDER it BY DESCENDING order of the POPULATION COLUM

SELECT Name, Continent, Population FROM Country
	WHERE Population < 100000 OR Population IS NULL ORDER BY Population DESC;

• This will do the same as above but it will also include the POPULATION COLUMN where the NULL STATE is found.

• OR is a powerful BOOLEAN operator that allows you to combine MULTIPLE EXPRESSIONS such as:

WHERE Population < 100000 OR Population IS NULL ORDER BY Population DESC;

• This looks for Population that is less the 100.000 OR the STATE in NULL

The other powerful operator is AND This is like piping a pipe in Linux:

ls -al | grep something | grep somethingelse

WHERE Population < 100000 AND  Continent = ‘Oceania’ ORDER BY Population DESC;

• This statement will check that both conditions are met. The Population has to be less then 100.000 AND the Continent has to be Oceania

THE LIKE OPERATIOR

SELECT Name, Continent, Population FROM Country
	WHERE Name LIKE ‘%island%’

% is a wildcard quivalant to * in Linux

• Basically what it means * island* Anything before and after as long as it contains the STRIG ISLAND

You can also do:

%island = *island
island% = island*

Another wildcard is _ which is the equivalent of . In Linux

_a% = .a*

• A is the second character followed by any other letter STRINGS.

The IN operator:

SELECT Name, Continent, Population FROM Country
	WHERE Continent IN (‘Europe’, ‘Asia’); 

• This will select the COLUMNS specified after SELECT from the Country Table WHERE the Continents are Europe or Asia

REMOVING DUPLICATES:

In order to check for Entries that are not duplicates you can use the DISTINCT statement:

SELECT DISTINCT Continent FROM Country;

ORDERING OUTPUT:

You can use the ORDER BY Statement to order things more specifically:

SELECT Name FROM Country ORDER BY Name; - Sorts stuff Ascii Alphabetical

SELECT Name FROM Country ORDER BY Name DESC;

• This sorts Alphabetically but Descendent (Last in First Out)

SELECT Name FROM Country ORDER BY Name ASC; – This is the Default

• This sorts Alphabetically but Ascendent (First in Frist Out)

You can also order by continent first and then by name with

SELECT Name, Continent FROM Country ORDER BY Continent, Name;

SELECT Name, Continent, Region FROM Contry ORDER BY Contient DESC, Region, Name;

• This orders by Continent first in Descending order, then Region and Name ASC

CONDITIONAL EXPRESSIONS:

In standard SQL 0 is considered FALSE and anything that IS NOT 0 is Considered TRUE

SELECT 
	CASE WHEN a THEN ‘true’ ELSE ‘false’ END AS boolA,
	CASE WHEN b THEN ‘true’ ELSE ‘false’ END AS boolB
FROM booltest;

• CASE is usefull to check for values. For example the above tests if the A COLUMN is Anything but 0 and then you will get the THEN clause = True

• If the value is 0 then you will get the ELSE clause = False

UNDERSTANDING JOIN:

The JOIN command is useful for executing queries from multiple tables that have RELATIONS:

• Most Common JOIN is the INNER JOIN that only takes data/values that are in both tables:

• Less Common but still useful is OUTER JOIN (which can be left or right) This takes the values that are common and the values that are in the table specified (LEFT or RIGHT)

• FULL OUTER JOIN combines the effects of a LEFT AND RIGHT OUTER JOIN:

SELECT l.description AS left, r.description AS right
	FROM left AS l
	JOIN right AS r on l.id = r.id
;

SELECT l.description ← This selects the description column AS left, r.description AS right
	FROM left AS l ←This creates alias for LEFT as L
	JOIN right AS r ON l.id = r.id ← This then joins the RIGHT table as R onto the LEFT table where the condition that ID from the LEFT Table matches the ID from the RIGHT Table. 
;

• Basically what it does is SELECTS the DESCRIPTION COLUMN from the RIGHT TABLE with an alias of L and then merges it VIA JOIN with the RIGHT table and then DISPLAYS THE ID VALUES THAT MATCH.

To make this an OUTER JOIN you have to specify LEFT JOIN:

SELECT l.description AS left, r.description AS right
FROM left AS l
	LEFT JOIN right AS r on l.id = r.id
;

• This brings the values of the INNER JOIN above but also adds the values that do not match from the LEFT table.

STRINGS: If you need to use single Quotes in a string you will have to use 2 of them like this: SELECT ‘Here’’s a single quote mark’;

Length:

SELECT LENGTH (‘string’) – Tells us how long the string is I.e length of 6. 
SELECT SUBSTR (‘this string’, 6); - This selects a value from the string starting with the 6th position. 

We can also specify the number of characters to retruns for example:

 SELECT SUBSTR (‘this string’, 6, 3);

TRIM: Trims text:

SELECT TRIM (‘    string     ‘)
SELECT LTRIM – Removes Left spaces but not Right
SELECT RTRIM – Removes Right spaces but not Left

You can also capitalize or lower case strings by using the LOWER or UPPER arguments:

SELECT LOWER (‘StRIng’) = LOWER(‘string’)
SELECT UPPER (‘StRing’) = UPPER (‘string’)

INTEGER TYPES:

• INTEGER (precision)

• DECIMAL (precision, scale)

• MONEY (precision, scale)

REAL TYPES:

• REAL (precision)

• FLOAT (precision)

• PRECISION vs SCALE: