- SELECT Statement
- AS -> Column Name
SELECT ‘Hello, World’ AS Result;
** If desired result column name has ” “(space) between the words, need to use double-quote – “
- Limit the result (LIMIT Statement)
SELECT Name, Continent, Region FROM Country WHERE Continent = ‘Europe’ ORDER BY Name LIMIT 5 OFFSET 5;
** OFFSET means showing results after first 5 rows (second five rows)
- Give an alias to a column
SELECT Name AS Country, Continent, Region FROM Country;
** AS – Change the column name
- COUNT Statement
a. SELECT COUNT(*) FROM Country;
b. SELECT COUNT(*) FROM Country WHERE Population > 100000 AND Continent = ‘Europe’;
c. SELECt COUNT(LifeExpectancy) FROM Country;
** Count rows in Country table or with conditions.
- INSERT Statement
a. SELECT * FROM Customer;
INSERT INTO Customer (Name, Address, City, State, Zip)
VALUES ( ‘Fred Flintstone’, ‘123 Cobblestone Way’, ‘Bedrock’, ‘CA’, ‘91234’);
INSERT INTO Customer (Name, City, State)
VALUES ( ‘Jimi Hendrix’, ‘Renton’, ‘WA’);
b. INSERT INTO test DEFAULT VALUES; // insert NULL values
c. INSERT INTO test ( a, b, c ) SELECT id, name, description FROM item
- UPDATE Statement
a. SELECT * FROM Customer
UPDATE Customer SET Address = ‘123 Music Avenue’, Zip = ‘98056’ WHERE ID = 5;
b. SELECT * FROM Customer
UPDATE Customer SET Address = NULL, Zip = NULL WHERE ID = 5
- DELETE Statement
SELECT * FROM Customer WHERE ID = 4;
DELETE FROM Customer WHERE ID =4;
- CREATE TABLE Statement
a. CREATE TABLE test (
a INTEGER,
b TEXT
);
**// test -> Table Name
a, b -> Column Names
INTEGER, TEXT -> Column Data Type
b. INSERT INTO text VALUES (1, ‘a’);
INSERT INTO text VALUES (2, ‘b’);
INSERT INTO text VALUES (3, ‘c’);
SELECT * FROM test;
**// Insert data into each column in the table.
- Deleting a Table (DROP TABLE Statement)
CREATE TABLE test ( a TEXT, b TEXT);
INSERT INTO test VALUES ( ‘ one’, ‘two’ );
SELECT * FROM test;
DROP TABLE test;
DROP TABLE IF EXISTS test;
- Select NULL Value (NULL Statement)
a. SELECT * FROM test WHERE a IS NULL;
b. SELECT * FROM test WHERE a IS NOT NULL;
- Change a Schema (ALTER TABLE Statement)
a. ALTER TABLE test ADD d TEXT; // Add a new column d with no data
b. ALTER TABLE test ADD e TEXT DEFAULT ‘panda’; // Add a new column e with ‘panda’ for all rows.
- Create ID Column
CREATE TABLE test (
id INTEGER PRIMARY KEY,
a INTEGER,
b TEXT
);
INSERT INTO test (a, b) VALUES ( 10, ‘a’ );
INSERT INTO test (a, b) VALUES (11, ‘b’ );
INSERT INTO test (a, b) VALUES (12, ‘c’ );
SELECT * FROM test;
** PRIMARY KEY will auto-generate running number (SQLite)
- Filter Data
a. SELECT Name, Continent, Population FROM Country
WHERE Name LIKE ‘%island’ ORDER BY Name; // Return result that has ‘island’ in the name field.
b. SELECT Name, Continent, Population FROM Country
WHERE Population < 10000 OR Population IS NULL ORDER BY Population DESC;
c SELECT Name, Continent, Population FROM Country
WHERE Name LIKE ‘_a%’ ORDER BY Name; //Return result that the second character in the name has ‘a’.
d. SELECT Name, Continent, Population FROM Country
WHERE Continenet IN (‘Europe’, ‘Asia’) ORDER BY Name;
- Remove Duplicates (SELECT DISTINCT Statement)
a. SELECT DISTINCT Continent FROM Country;
b. SELECT DISTINCT a, b FROM test;
- Conditional Expressions
a. SELECT
CASE WHEN a THEN ‘true’ ELSE ‘false’ END as boolA,
CASE WHEN b THEN ‘true’ ELSE ‘false’ END as boolB
FROM booltest
b. SELECT
CASE a WHEN 1 THEN ‘true’ ELSE ‘false’ END as boolA,
CASE b WHEN 1 THEN ‘true’ ELSE ‘false’ END as boolB
FROM booltest
- Join Tables (JOIN Statement)
a. SELECT l.description AS left, r.description AS right
FROM left AS l
JOIN right AS r ON l.id = r.id
;
b. SELECT c.name AS Cust, c.zip, i.name AS Item, i.description, s.quantity AS Quan, s.price AS Price
FROM sale AS s
JOIN item AS i ON s.item = i.id
JOIN customer AS c ON s.customer_id = c.id
ORDER BY Cust, Item
;
** JOIN -> Inner Join
** LEFT JOIN -> Left Outer Join
** ON -> Join Condition
- Concatenation
a. SELECT ‘This’ || ‘ & ‘ || ‘that’; // Standard SQL Concatenation.
b. SELECT CONCAT(‘This’, ‘ & ‘, ‘that’); // MySQL Concatenation.
c. SELECT ‘This’ + ‘ & ‘ + ‘that’; // Microsoft SQL Server Concatenation.
- Finding the length of a string (LENGTH() Statement)
a. SELECT LENGTH(‘string’);
b. SELECT Name, LENGTH(Name) AS Len FROM City ORDER BY Len DESC, Name; //Returns Name and Length of name ordered by length of name in descending order and Name order.
- Select part of a string (SUBSTR() Statement)
a. SELECT SUBSTR(‘this string’, 6, 3); // Return the 3 character starting with the 6th character.
b. SELECT released,
SUBSTR(released, 1, 4) AS Year,
SUBSTR(released, 6, 2) AS Month,
SUBSTR(released, 9, 2) AS Day
FROM album ORDER BY released
;
- Removing spaces/character (TRIM() Statement)
a. SELECT TRIM(‘ string ‘); //Remove all blank space.
b. SELECT LTRIM(‘ string ‘); //Remove all blank space on the left.
c. SELECT RTRIM(‘ string ‘); //Remove all blank space on the right.
d. SELECT RTRIM(‘…..string…..’, ‘.’); //Remove ‘.’ on the right.
- Folding case (LOWER()/UPPER() Statements)
SELECT UPPER(Name) FROM City ORDER BY Name;
SELECT LOWER(Name) FROM City ORDER BY Name;
- Numeric Types
// Integer Types
INTEGER(precision)
DECIMAL(precision, scale)
MONEY(precision, scale)
// Real Types -> Scarifice the accuracy for scale
REAL(precision)
FLOAT(precision)
- Types of Value (TYPEOF() Statement)
SELECT TYPEOF( 1+1 ); // Integer
SELECT TYPEOF( 1+1.0 ); // Real
SELECT TYPEOF( ‘panda’ ); // Text
SELECT TYPEOF( ‘panda’ + ‘koala’ ); // Integer, based on system, in this case, result is 0.
- Integer Division
SELECT 1 / 2; // 0, integer.
SELECT 1.0 / 2; // 0.5, real.
SELECT CAST(1 AS REAL) / 2; // 0.5, real.
SELECT 17 / 5; // 3, no remainer.
SELECT 17 / 5, 17 % 5; //3, 2, show the remainer – 2.
- Rounding Numbers (ROUND() Statement)
SELECT ROUND(2.55555); // 3
SELECT ROUND(2.55555, 3); // 2.556
SELECT ROUND(2.55555, 0); // 3
- Date & Time
UTC (Coordinated Universal Time)
// For SQLite.
SELECT DATETIME(‘now’);
SELECT DATE(‘now’);
SELECT TIME(‘now’);
SELECT DATETIME(‘now’, ‘+1 day’);
SELECT DATETIME(‘now’, ‘+3 days’);
SELECT DATETIME(‘now’, ‘-1 month’);
SELECT DATETIME(‘now’, ‘+1 month’);
SELECT DATETIME(‘now’, ‘+3 hours’, ‘+minutes’, ‘-1 day’, ‘ ‘+3 years’);
- Data Aggregation (GROUP BY Statement )
a. SELECT Region, COUNT(*)
FROM Country
GROUP BY Region
;
b. SELECT a.title AS Album, COUNT(t.track_number) as Tracks
FROM track AS t
JOIN album AS a
ON a.id = t.album_id
GROUP BY a.id
HAVING Tracks >= 10
ORDER BY Tracks DESC, Album
;
** HAVING clause is same as WHERE in data aggregation – GROUP BY.
- Transaction (BEGIN/END TRANSACTION)
Used to increase the performance
BEGIN TRANSACTION;
END TRANSCATION;
- Trigger (CREATE TRIGGER)
SELECT * FROM widgetCustomer;
CREATE TRIGGER newWidgetSale AFTER INSERT ON widgetSale
BEGIN
UPDATE widgetCustomer SET last_order_id = NEW.id WHERE widgetCustomer.id = NEW.customer_id;
END
;
** NEW. represents a virtual row in the widgetSale table, this refers to the ID column in the new row.
- Preventing Updates
CREATE TRIGGER updateWidgetSale BEFORE UPDATE ON widgetSale
BEGIN
SELECT RAISE(ROLLBACK, ‘cannot update table “widgetSale”‘) FROM widgetSale
WHERE id = NEW.id AND reconciled = 1;
END
;
- Timestamp (Using TRIGGER)
CREATE TIGGER stampSale AFTER INSERT ON widgetSale
BEGIN
UPDATE widgetSale SET Stamp = DATETIME(‘now’ WHERE id = NEW.id;
UPDATE widgetCustomer SET last_order_id = NEW.id, stamp = DATETIME(‘now’)
WHERE widgetCustomer.id = NEW.customer_id;
INSERT INTO widgetLog (stamp, event, username, tablename, table_id)
VALUES( DATETIME(‘now’), ‘INSERT’, ‘TRIGGER’, ‘widgetSale’, NEW.id);
END
;
- Sub-select