# SQL 101
### SQL Statements:
#### A statement is the unit of execution in SQL
```sql
SELECT * FROM Countries WHERE Continent = 'Europe';
```
**An SQL statement usually includes 1 or more Clauses**
```sql
FROM clause
```
* This specifies which table the Statement will use
```sql
WHERE clause
```
* This specifies which rows will be selected
**The WHERE clause requires an EXPRESSION:** Logical Expression:
```sql
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
{% hint style="info" %}
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
{% endhint %}
**A Database has Tables. Can have 1 or more tables which are organized in ROWS and COLUMNS:**
{% hint style="info" %}
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.
{% endhint %}
**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`.
{% hint style="info" %}
Note: Different Engines do this differently but there is ALWAYS a `UNIQUE KEY`.
{% endhint %}
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 a`FORIGN KEY`because 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:
```sql
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:**
```sql
SELECT * FROM Country;
```
**If we want them in a Different order you can use ORDER BY:**
```sql
SELECT * FROM Country ORDER BY Name;
```
**If we don’t want all the rows you can specify the names denominated by a ****`“,”`****:**
```sql
SELECT Name, LifeExpectancy FROM Country ORDER BY Name;
```
{% hint style="info" %}
Example: This might not result right so you might want to give an alias for LifeExpectancy to be AS Life Expectancy
{% endhint %}
```sql
SELECT Name, LifeExpectancy AS “Life Expectancy” FROM Country ORDER BY Name;
```
{% hint style="info" %}
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.
{% endhint %}
```sql
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:
```sql
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:
```sql
SELECT Name, Continent, Region FROM Country WHERE Continent = ‘Europe’ ORDER BY name LIMIT 5;
```
{% hint style="info" %}
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.
{% endhint %}
* If we want to skip the first 5 but want the next 5 we can use the OFFSET CLAUSE:
```sql
SELECT Name, Continent, Region FROM Country WHERE Continent = ‘Europe’ ORDER BY name LIMIT 5 OFFSET 5;
```
{% hint style="warning" %}
### 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.
{% endhint %}
```sql
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:
```sql
SELECT Name AS Country, Region, Continent FROM Country;
```
COUNT function allows you to count the ROWS within a table.
```sql
SELECT COUNT (*) FROM Country;
```
You can limit the RESULTS by doing
```sql
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
```sql
SELECT COUNT (*) FROM Country WHERE Population > 1000000 AND Continent = ‘Europe’;
```
{% hint style="info" %}
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.
{% endhint %}
#### INSERT Statement:
To insert Values into a table you can use the insert statement:
AFTER:
```sql
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:
```sql
INSERT INTO customer (name, city, state)
VALUES (‘Jimi Hendrix’, ‘Renton’, ‘WA’);
```
#### UPDATE Statement:
TO CHANGE DATA in a TABLE we use the UPDATE STATEMENT:
```sql
UPDATE customer SET address = ‘123 Music Avenue’, zip = ‘98056’ WHERE id=5;
```
{% hint style="danger" %}
**IF THE WHERE CLAUSE DOES NOT EXIST IT WILL DO IT TO THE ENTIRE TABLE! SO YOU HAVE TO LIMIT IT!**
{% endhint %}
* 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:
```sql
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:
```sql
UPDATE customer SET address = NULL, zip = NULL WHERE id=5;
```
{% hint style="warning" %}
**MAKE SURE NOT TO SET THEM AS STRINGS AS THESE WILL BE POPULATED AS SUCH.**
{% endhint %}
#### DELETE Statement:
```sql
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.
```sql
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:
```sql
DROP TABLE test;
```
* This deletes the table
```sql
DROP TABLE IF EXISTS test;
```
* This does not return an error if the table does not exist.
#### **Inserting Rows:**
```sql
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:
```sql
INSERT INTO test DEFAULT VALUES;
```
* This creates a empty row with the NULL values
TO ADD VALUES FROM A DIFFERENT TABLE YOU CAN DO:
```sql
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:**
```sql
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:**
```sql
SELECT * FROM test WHERE a = 3;
```
{% hint style="info" %}
*The NULL value – Is not a value, is the absence of a value.* To test for NULL you have to use
{% endhint %}
```sql
SELECT * FROM test WHERE a IS NULL;
```
The opposite of NULL is:
```sql
SELECT * FROM test WHERE a IS NOT NULL;
```
To CREATE a TABLE that does not accept NULL values use:
```sql
CREAT TABLE test (
a INTEGER NOT NULL,
b TEXT NOT NULL,
c TEXT
);
```
If you do:
```sql
INSERT INTO test (a, c) VALUES (‘one’, ‘two’);
```
It will result in a ERROR:
```sql
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:
```sql
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:
```sql
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:
```sql
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:
```sql
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:
```sql
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:
```sql
ALTER TABLE test ADD d TEXT;
```
#### ID COLUMNS:
When creating a table:
```sql
CREAT TABLE test (
id INTEGER PRIMARY KEY,
a INTEGER,
b TEXT
);
```
{% hint style="info" %}
**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.
{% endhint %}
#### FILTERING DATA:
```sql
SELECT Name, Continent, Population FROM Country
WHERE Population < 100000 ORDER BY Population DESC;
```
{% hint style="info" %}
THE WHERE CLAUSE ALLOWS YOU TO FILTER DATA: KIND OF LIKE PIPE IN LINUX:
```bash
ls -al | grep something
```
{% endhint %}
* 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
```sql
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:**
```sql
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
{% hint style="info" %}
The other powerful operator is AND This is like piping a pipe in Linux:
```bash
ls -al | grep something | grep somethingelse
```
{% endhint %}
```sql
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**
```sql
SELECT Name, Continent, Population FROM Country
WHERE Name LIKE ‘%island%’
```
{% hint style="info" %}
**% is a wildcard quivalant to \* in Linux**
{% endhint %}
* Basically what it means \* island\* Anything before and after as long as it contains the STRIG ISLAND
You can also do:
```sql
%island = *island
island% = island*
```
{% hint style="info" %}
**Another wildcard is \_ which is the equivalent of . In Linux**
{% endhint %}
```sql
_a% = .a*
```
* A is the second character followed by any other letter STRINGS.
#### **The IN operator:**
```sql
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:
```sql
SELECT DISTINCT Continent FROM Country;
```
#### **ORDERING OUTPUT:**
You can use the ORDER BY Statement to order things more specifically:
```sql
SELECT Name FROM Country ORDER BY Name; - Sorts stuff Ascii Alphabetical
```
```sql
SELECT Name FROM Country ORDER BY Name DESC;
```
* This sorts Alphabetically but Descendent (Last in First Out)
```sql
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
```sql
SELECT Name, Continent FROM Country ORDER BY Continent, Name;
```
```sql
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
```sql
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:**
```sql
SELECT l.description AS left, r.description AS right
FROM left AS l
JOIN right AS r on l.id = r.id
;
```
```sql
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:
```sql
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:**
```sql
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:
```sql
SELECT SUBSTR (‘this string’, 6, 3);
```
#### **TRIM: Trims text:**
```sql
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:
```sql
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:
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.arkannis.net/database/general-sql/sql-101.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.