Elastic notes are in a sibling page.
Notes from "SQL QuickStart Guide"
Author: Walter Shields
-- Single line comment /* Multi Line Comment */ SELECT FirstName, LastName, Email, City from customers;
Once more, using aliases:
SELECT FirstName AS "First Name", LastName AS "Surname", Email, City from customers;
And again, with sorting:
SELECT FirstName AS "First Name", LastName AS "Surname", Email, City from customers ORDER BY Surname DESC;
Show only first 10 records: use the keyword
/Operators/ are used with other clauses like
- Comparison operators like
- Logical operators:
- Arithmetic operators like
SELECT Total AS [Original Amount], Total + 10 AS [Addition example], Total - 10 AS [Subtraction example] FROM invoices ORDER BY Total DESC;
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE Total = 1.98 ORDER BY InvoiceDate;
WHERE always comes after the
FROM but before the
BETWEEN gives a range, e.g.
WHERE Total BETWEEN 1.98 AND 5.00
Searching in text:
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE BillingCity IN ('Tucson', 'Paris', 'London') ORDER BY Total
Wildcard search with LIKE
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE BillingCity LIKE 'T%' ORDER BY Total
(You can do a
NOT LIKE 'T%' to invert the results)
Filtering by date
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE InvoiceDate = '2009-01-03 00:00:00' ORDER BY Total
Something more interesting:
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE InvoiceDate BETWEEN '2009-01-01 00:00:00' AND '2009-12-31 23:59:59' ORDER BY InvoiceDate
Using the Date() function
Let's you skip the time, for instance.
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE DATE(InvoiceDate) = '2009-01-03' ORDER BY Total
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE BillingCity LIKE 'p%' OR BillingCity LIKE 'd%' ORDER BY Total
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE Total > 1.98 AND (BillingCity LIKE 'p%' OR BillingCity LIKE 'd%') ORDER BY Total
The CASE operator
Lets you create a new, temporary field based on some conditions.
SELECT InvoiceDate, BillingAddress, BillingCity, Total, CASE WHEN TOTAL < 2.00 THEN 'Baseline Purchase' WHEN TOTAL BETWEEN 2.00 AND 6.99 THEN 'Low Purchase' WHEN TOTAL BETWEEN 7.00 AND 15.00 THEN 'Target Purchase' ELSE 'Top Performers' END AS PurchaseType FROM invoices ORDER BY BillingCity
- Wrapped with
- Alias using
ASto create the new label
ELSEto specify the conditions
Now to filter only top performers in the above case, it is trivial to add a
WHERE PurchaseType = 'Top Performers' to the query.
Working with multiple tables
Example of a
SELECT * FROM invoices INNER JOIN customers ON invoices.CustomerId = customers.CustomerId
Here the CustomerId column in customers table is a primary key, while CustomerId in invoices is a foreign key.
With an alias this time:
SELECT c.FirstName, c.LastName, i.InvoiceId, i.InvoiceDate, i.Total FROM invoices as i INNER JOIN customers as C ON i.CustomerId = c.CustomerId ORDER BY c.LastName
INNER JOIN shows the intersection of 2 tables. Any info in one table that is missing in the other is just ignored.
LEFT OUTER JOIN
Combines all records in left with matching ones in right. Example:
SELECT * FROM invocies AS i LEFT OUTER JOIN customers AS c ON i.CustomerId = c.CustomerId
SQLite will insert
NULL data when there are no matching records in the right table.
RIGHT OUTER JOIN
Same as LEFT, as you'd expect.
Joining more than 2 tables
SELECT e.FirstName AS EmpFirstName, e.LastName AS EmpLastName, e.EmployeeId, c.FirstName AS CustomerFirstName, c.LastName AS CustomerLastName, c.SupportRepId, i.CustomerId, i.Total FROM invoices as i INNER JOIN customers as c ON i.CustomerId = c.CustomerId INNER JOIN employees as e ON c.SupportRepId = e.EmployeeId ORDER BY i.Total DESC LIMIT 10
Using NULL, IS and NOT
Example: show all artists that do not have a corresponding entry in the album table:
SELECT ar.ArtistId AS [ArtistId from Artists table], al.ArtistId AS [ArtistId from Albums table], ar.Name AS [Artist Name], al.Title AS [Album] FROM artists AS ar LEFT OUTER JOIN albums AS al ON ar.ArtistId = al.ArtistId WHERE al.ArtistId IS NULL
Example: Counting occurrences:
SELECT COUNT(LastName) AS [Name Count] FROM customers WHERE LastName LIKE 'B%'
Types of Functions
- String: INSTR(), LENGTH(), LOWER(), LTRIM(), REPLACE(), RTRIM(), SUBSTR(), TRIM(), UPPER()
- Date: DATE(), DATETIME(), JULIANDAY(), STRFTIME(), TIME(), 'NOW'
- Aggregate: AVG(), COUNT(), MAX(), MIN(), SUM()
Adds a space followed by LastName to FirstName:
SELECT FirstName || ' ' || LastName AS [Full Name] etc.
Substring example, take first five chars of PostalCode:
SELECT PostalCode, SUBSTR(PostalCode,1,5) AS [Five-digit Postal] etc.
Arguments for STRFTIME
'%d'- day of month: 00
'%f'- fractional seconds: SS.SSS
'%H'- hour: 00-24
'%j'- day of year 001-366
'%J'- Julian day number
'%m'- month: 01-12
'%M'- minute: 00-59
'%s'- seconds since 1970-01-01
'%S'- seconds: 00-59
'%w'- day of week: 0-6 (Sunday is 0)
'%W'- week of year: 00-53
'%Y'- year: 0000-9999
'YYY-MM-DD'- A date typed in Year-Month-Day format
'now'- Current date and time
'DATETIME' field- A databse field in a date and/or time format
'+ X days'- Add X days to the result
- Same as above for
'start of the day'- modifies the time code to represent the beginning of the day
- Same as above for
SELECT STRFTIME('The year is %Y and day is %d and month is %m', 'now') AS [Text with Conversion specifications]
Example to calculate age:
SELECT LastName, FirstName, STRFTIME('%Y-%m-%d', BirthDate) AS [Birthday No Timecode], STRFTIME('%Y-%m-%d','now') - STRFTIME('%Y-%m-%d',BirthDate) AS [Age] FROM employees ORDER BY Age
Main ones are SUM(), AVG(), MIN(), MAX(), COUNT().
SELECT SUM(Total) AS TotalSales, AVG(Total) AS AverageSales, ROUND(AVG(Total), 2) AS RoundedAverageSales, MAX(Total) AS MaxSale, MIN(Total) AS MinSale, COUNT(*) AS SalesCount FROM invoices
COUNT(*) ensures that all values are counted, even records with errors or nulls.
SELECT BillingCity, AVG(Total) FROM invoices GROUP BY BillingCity ORDER BY BillingCity
Run this without GROUP BY and you will see that the response is messed up, i.e. it tries to print the BillingCity which is a multi-line reponse, and also the AVG(Total) which is a single row.
Filtering based on Aggregates with HAVING
WHERE does not work with functions. So if you want to show only rows where AVG exceeds 6, use
SELECT BillingCity, AVG(Total) FROM invoices GROUP BY BillingCity HAVING AVG(Total) > 6 ORDER BY BillingCity
HAVING always comes after the
GROUP BY clause.
WHERE clause tells SQL what data to include in the table. Once the information is filtered and
aggregate functions are applied,
HAVING acts as a further filter.
Basically, one query inside another.
Example: Find the Average of sales, and find all rows that were below this average. Since we can't
WHERE when you're using an aggregate function, you'd have to make a subquery and embed it in
SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE Total < (select AVG(Total) from invoices) ORDER BY Total DESC)
Another example, in a
SELECT BillingCity, AVG(Total) AS [City Average], (select avg(total) from invoices) AS [Global Average] FROM invoices GROUP BY BillingCity ORDER BY BillingCity
Another, in a
WHERE clause, in this case we want to see which sales in 2014
has beaten 2013's highest sale.
SELECT InvoiceDate, BillingCity, Total FROM invoices WHERE InvoiceDate >= '2013-01-01' AND total > (select max(Total) from invoices where InvoiceDate < '2013-01-01')
Here's one where we're interested in 3 invoces. We write a query to extract the dates they were purchased. Then we pipe that into another query and see all sales that happened in those dates.
SELECT InvoiceDate, BillingAddress, BillingCity FROM invoices WHERE InvoiceDate IN (select InvoiceDate from invoices where InvoiceId in (251,252,255))
Here we want all tracks that do not appear in invoice_items i.e have never been bought. This is the query:
select Name, composer from tracks where trackid not in (select DISTINCT TrackId from invoice_items)
Note that without the
DISTINCT keyword, we would get a large result with lots
of repetitions. So this is the equivalent of doing a
A virtual table that's basically a stored query that can be executed/references by other queries.
Prefix a statement with
CREAE VIEW <blah> AS to make one. Example:
CREATE VIEW V_AvgTotal AS SELECT ROUND(AVG(Total), 2) AS [Average Total] FROM invoices
Naming with a
V_ prefix is a good convention to follow.
Another example for a joined query:
CREATE VIEW V_Tracks_InvoiceItems AS SELECT ii.InvoiceId, ii.UnitPrice, ii.Quantity, t.Name, t.Composer, t.Milliseconds FROM invoice_items ii INNER JOIN tracks t ON ii.TrackId = t.Trackid
Now you can use this in some other query.
To remove it:
DROP VIEW V_Tracks_InvoiceItems
No data is deleted, just the view is removed.
DML (Data Manipulation Language)
This is about inserting/updating/deleting data. Simplest example:
INSERT INTO artists (Name) VALUES ('Bob Marley')
UPDATE employees SET PostalCode = '11202' WHERE EmployeeId = 9
DELETE FROM employees WHERE EmployeeId = 9
I'm actually using ScyllaDB which is compatible with Cassandra.
cqlsh is it's shell.
DESCRIBE keyspaces; USE <name-of-keyspace>; DESCRIBE tables; SELECT * FROM <name-of-table>;
sudo -i -u postgres postgresql quickstart createuser --interactive createdb ttrssdb psql >alter user ttrssuser with encrypted password 'blah'; >grant all privileges on database ttrssdb to ttrssuser;
MySql quick start
mysql> create database habari; Query OK, 1 row affected (0.02 sec) mysql> grant all on habari.* to 'habariuser'@'localhost' identified by 'blah'; Query OK, 0 rows affected (0.06 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
thaum ~/code/app$ sqlite perl.db SQLite version 2.8.17 Enter ".help" for instructions sqlite> .tables sqlite> .schema sqlite> create table perltest (id integer PRIMARY KEY,name varchar(10), salary integer); sqlite> .tables perltest sqlite> .headers on sqlite> .mode column sqlite> select * from perltest; sqlite> insert into perltest values(1,'arun',12345); sqlite> insert into perltest values(2,'brun',23456); sqlite> select * from perltest; id name salary ---------- ---------- ---------- 1 arun 12345 2 brun 23456