Stuctered Query Language is a language that describes a method of fetching and describing the relationship between different types of 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
This system also works with dates, where all dates are in format
To see if an attribute is in a set we can use the
IN keyword in conjunction
WHERE statement. For example,
SELECT * FROM holidays WHERE Country IN ('spain', 'portugaul', 'USA');
NOT keyword can be placed before a conditional to specify that we want
everything that does NOT mean that condition.
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 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;
ORDER BY allows us to descide how the query will be shown. For example,
SELECT Firstname, Lastname FROM users ORDER BY Lastname [SORT];
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 allows us to retstrict the number or rows returned.
SELECT * FROM users ORDER BY name DESC LIMIT 1
If we want just the number of rows, we can use count.
SELECT COUNT(*) FROM users WHERE dob > '2000-01-01'
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 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 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
INNER JOINreturns all records that having matching records in both tables
LEFT OUTER JOINreturns all records from the left (Orders in above example) table, and the matched records from the right table
RIGHT OUTER JOINreturns all records from the right (Customers in above example) table, and the matched records from the left table
FULL OUTER JOINreturns all records when there is a match in either the left or right table
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.
Two commands for managing access rights
Typical access rights include