SQL

Stuctered Query Language is a language that describes a method of fetching and describing the relationship between different types of data

Filtering data

To filter data we by some specifiers we use the WHERE clause. The where clause can filter based on the default comparison operators. It can also do string comparisons.

This system also works with dates, where all dates are in format YYYY-MM-DD.

IN

To see if an attribute is in a set we can use the IN keyword in conjunction with a WHERE statement. For example, SELECT * FROM holidays WHERE Country IN ('spain', 'portugaul', 'USA');

NOT

The NOT keyword can be placed before a conditional to specify that we want everything that does NOT mean that condition.

LIKE

LIKE lets us filter on patterns. We can use the character % to specify a wildcard, for any number of characters. We can use the character _ to give an exact number of wildcard characters we are looking for. For example,

SELECT * FROM Bands WHERE BandName LIKE '%light%';

will return all bands with the string 'light' in their name.

DISTINCT

DISTINCT filters results by only returning unique values of a column. Therefore, we can only be used to return a single column.

SELECT DISTINCT nationality FROM users;

Presentation

Order by

ORDER BY allows us to descide how the query will be shown. For example,

SELECT Firstname, Lastname
FROM users
ORDER BY Lastname [SORT];

where [SORT] is

CASE

Case is a statement block where we can choose exactly what text is returned based on values in the database.

SELECT Name,
CASE
    WHEN Country = 'USA' THEN 'North America'
    WHEN Country = 'UK' THEN 'Europe'
    ELSE 'Wolrd'
END
FROM Users;

LIMIT

LIMIT allows us to retstrict the number or rows returned.

SELECT * FROM users ORDER BY name DESC LIMIT 1

COUNT

If we want just the number of rows, we can use count.

SELECT COUNT(*) FROM users WHERE dob > '2000-01-01'

math functions

If we want to sum the values in ONE column we can use sum

SELECT SUM(cash_balance) FROM users

If we want to find an average,

SELECT AVG(price) FROM products

This same principle applies for

GROUP BY

GROUP BY allows us to combine with the above math and COUNT functions by grouping totals based upon some other row.

SELECT Team, COUNT(*) FROM Players GROUP BY Team

JOIN

JOIN allows two tables to be returned together, where they are 'joined' upon a shared field.

SELECT Orders.ID, Customers.Name, Orders.Name
FROM Orders
INNER JOIN Customers ON Orders.CustomerId=Customers.Id

There are four types of joins

Views

A view can be created with the following command

CREATE VIEW [view_name] AS
SELECT column1, column2, ...
FROM table
WHERE condition;

For example, this view returns all products witha price higher than the average price of the table

CREATE VIEW [Products above average price] AS
SELECT Name, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)

SELECT * FROM [Prodcuts above average price]

Views can be inserted into, you simply must specify the columns being inserted into.

Acess control commands

Two commands for managing access rights

Typical access rights include

Also see

databases

index