So, back to tables! We've heard that they're made up of related data that's stored in columns and rows.
Before we move on to create a table, let's have a last look at friends using a select statement!
SELECT name, age, height FROM friends;
//Output Below
name | age | height -------+-----+-------- Robin | 42 | 1.72
See that? We can separate columns, or fields, with commas. Each row is referred to as a record.
But how do we actually create a table like friends? Well, there's a statement for that.
CREATE TABLE friends;
//Output Below
ERROR 1113 (42000): A table must have at least 1 column
Whoa! What happened? As it turns out, we need to add at least a single column at the same time we create a table.
Let's add that column, then! But which data type should the name column expect?
CREATE TABLE friends ( name TEXT );
//Output Below
Query OK, 0 rows affected (0.01 sec)
Right! Because we'll use the name column for strings, the TEXT type is an ideal fit. We use data types like INT and FLOAT to store numbers.
What if there are records with the same name value, though? Well, that's why we want every record in a table to have a unique identifier, or id.
CREATE TABLE friends ( id INT NOT NULL, name TEXT );
Sweet! We've also applied a so-called constraint to id: NOT NULL, which means that the column must contain a value.
Alright, we have a table with an id and a name column. That isn't a lot, is it? Let's add an age and a height column!
ALTER TABLE friends ADD name VARCHAR(20) NOT NULL, ADD age INT;
//Output Below
Query OK, 0 rows affected (0.01 sec)
Great! In an ALTER TABLE statement, we can use as many ADD clauses as we want, as long as we separate them with commas.
Psst: VARCHAR is similar to TEXT but allows us to set a maximum length for a string and, therefore, save space.
Alright, we have a friends table, but there's nothing in it. Let's use an insert statement to, well, insert a record, shall we?
INSERT INTO friends VALUES (1, 'Robin', 42, 1.72);
//Output Below
Query OK, 1 row affected (0.01 sec)
Nice! With an insert statement, we insert the values we provide in the parentheses of the VALUES clause into the sequential columns of the table.
Psst: if we don't specify the columns we want to insert values into, we have to provide values for all of them.
We can also insert records with specific values into a table and leave other fields blank.
INSERT INTO friends (id, name) VALUES (2, 'Win');
//Output Below
Query OK, 1 row affected (0.00 sec)
Perfect! The fields that don't receive a value use their default value or NULL. We can't skip fields that have a NOT NULL constraint, though.
What if we want to change, or update, information to an already existing record?
UPDATE friends SET age = 12 WHERE name = 'Win';
//Output Below
Query OK, 1 row affected (0.01 sec)
That's it! We can use an update statement with SET and WHERE clauses to set a different value for age in the record where the name equals 'Win'.
You know, we can remove records from tables and the statement starts with a keyword that sounds very much like what it's for.
DELETE FROM friends WHERE id = 2;
//Output Below
Query OK, 1 row affected (0.01 sec)
Poof! The record with an id of 2, is gone from the friends table.
Last but by no means least, let's revisit the select statement and have the results include multiple columns!
SELECT * FROM friends WHERE name = 'Robin';
//Output Below
id | name | age | height ----+-------+-----+-------- 1 | Robin | 42 | 1.72
Nice! We can use a * sign to select all of columns at once.
Psst: again, a select statement needs at least a SELECT and a FROM clause to indicate the table that we want to query.
Tables are a fundamental concept of SQL. We've learned how to create, alter and delete them and have seen ways to work with their records.
CREATE TABLE team ( number INT NOT NULL, name VARCHAR(20) ); INSERT INTO team VALUES (23, 'Michael'); SELECT number, name FROM team WHERE number = 23;
//Output Below
number | name --------+--------- 23 | Michael
Aren't they great, those tables? How about some tests?
As we already know, statements help us interact with databases and are made up of clauses.
Do you remember clauses that we've put at the beginning of statements?
Sweet! Usually, the name of the clause that a statements starts with is also the name of the statement.
Clauses start with a keyword and continue with an identifier, a so-called expression or some other parameter.
SELECT name FROM friends WHERE name = 'Robin';
//Output Below
name ------- Robin
See that? In a select statement, the SELECT and FROM keywords take identifiers and the WHERE keyword requires an expression.
How can we use a WHERE clause to find records in the family table where the age is greater than, say, 40?
SELECT * FROM family WHERE age > 40;
//Output Below
id | name | age ----+------+----- 1 | Dad | 50 2 | Mom | 48
Nice! With age > 40, a so-called boolean expression, we only select records where the expression evaluates to TRUE.
Psst: again, the splat operator asks for every column in the record.
We also have a keyword that we can use to chain clauses, which allows us to get more specific.
How can we get another expression into this query?
SELECT * FROM family WHERE age > 40 AND name = 'Dad';
//Output Below
id | name | age ----+------+----- 1 | Dad | 50
Great! We want records where the age is greater than 40 and the name is equal to 'Dad'.
Say we want a result set that requires at least one out of two expressions to evaluate to TRUE.
SELECT name FROM family WHERE age > 40 OR name = 'Dad';
//Output Below
name ------ Dad Mom
That's it! We can use an OR clause to select records with an age of more than 40 or a name that equals 'Dad', which brings back 'Mom'.
With an ORDER BY clause, we can sort a result set by any column.
Let's insert some more records into the family table, select them and sort the whole thing by id in descending order.
INSERT INTO family (id, name, age) VALUES (3, 'Robin', 12), (4, 'Win', 12); SELECT * FROM family ORDER BY id DESC;
//Output Below
id | name | age ----+-------+----- 4 | Win | 12 3 | Robin | 12 2 | Mom | 48 1 | Dad | 50
We can use the DESC keyword to specify a descending order. If we wanted an ascending order, we would have used the 'ASC' keyword.
Psst: if we would order by age alone, without specifying the order, the result set would be sorted in ascending order.
Columns other than id can contain duplicate values but, at times, we only want to retrieve the different, or distinct, values.
Did you notice the duplicate values in the age column of the family table?
SELECT DISTINCT age FROM family;
//Output Below
age ----- 50 48 12
Great! The DISTINCT keyword, which we can use in a SELECT clause, only selects the distinct values in the age column and skips the duplicate 12.
Depending on the number of records in a table, sometimes we get larger result sets than we bargained for. That's why we can use a LIMIT clause.
SELECT * FROM family LIMIT 2;
//Output Below
id | name | age ----+------+----- 1 | Dad | 50 2 | Mom | 48
Excellent! The LIMIT clause requires an INT-type value with the maximum number of records we want to have in the result set.
What if we don't have specific values but patterns that we want to query for? Well, that's what the LIKE keyword, or operator, is for.
SELECT * FROM friends WHERE name LIKE '%in%';
//Output Below
id | name | age ----+-------+----- 3 | Robin | 12 4 | Win | 12
Great! Because the % sign fills in for any characters before and after 'in', the LIKE operator looks for records that contain 'in'.
Psst: these substitutes for other characters are also known as wildcards.
There's another so-called wildcard that we can use with the LIKE operator, which fills in for any single character.
SELECT * FROM friends WHERE name LIKE '_in';
//Output Below
id |name | age ----+------+----- 4 | Win | 12
Fantastic! We can use the _ sign to look for records with a name field that consists of three characters and ends with 'in'.
Brilliant work! In this lesson, we've taken a closer look at clauses like WHERE, AND, OR, ORDER BY and LIMIT as well as the keywords DISTINCT and LIKE.
So, what if we just want to see the record in the pole position of the family table?
SELECT * FROM family LIMIT 1;
//Output Below
id |name | age ----+------+----- 1 | Dad | 50
Bingo! We've limited our result set to 1, and since the table is ordered by id by default, we got the first record!
In SQL, one of the most widely used operators is =, the assignment operator.
UPDATE friends SET age = 24;
//Output Below
Query OK, 1 row affected (0.01 sec)
See that? We can use = to assign 24 to the age field.
We can also use = as a comparison operator, though.
SELECT * FROM friends WHERE age = 24;
//Output Below
id | name | age | height ----+-------+-----+-------- 1 | Robin | 24 | 1.72
In this context, the = operator compares if age is equal to 24.
Psst: because age = 24 can either be TRUE or FALSE, it's also known as a boolean statement.
With comparison operators like =, >, and <, we can form conditions, or boolean statements.
What do you figure the condition below will return?
10 < 20
If you chose TRUE you are right! The < operator compares fields or values and returns TRUE, which is a BOOL-type value.
Negation operators look for results that don't match a condition.
SELECT * FROM friends WHERE age != 99;
//Output Below
id | name ----+------- 1 | Robin
Great! For comparisons, we can combine ! with other operators. != is not equal to, just as !> is not greater than and !< is not less than.
Psst: for logical expressions, we can use the NOT operator.
There's a logical operator that helps us look at ranges and works very well with the AND operator.
SELECT id, name, age FROM friends WHERE age BETWEEN 0 AND 99;
//Output Below
id | name | age| ----+-------+--- 1 | Robin | 24 |
Great! This query fetches every record with an age field between 0 and 99.
Psst: this is an inclusive range, which means that 0 and 99 are part of it.
If we want to exclude any records that have fields between certain values, we use the art of negation.
SELECT * FROM people WHERE age NOT BETWEEN 10 AND 50;
//Output Below
id | name | age ----+----------+----- 3 | Lancelot | 800 4 | Tom | 75 5 | Jerry | 75
Pretty straight forward, right? Let's move on!
We can get results based on whether or not a record has a specific field.
SELECT *
FROM people;
WHERE age IN ('75', '20');//Output Below
id | name | age ----+-------+----- 1 | Bob | 20 4 | Tom | 75 5 | Jerry | 75
Sweet! We can add multiple values to the IN operator, and it'll give us all of the records that meet the condition.
Again, we can negate conditions.
SELECT * FROM people
WHERE age NOT IN ('75', '20');//Output Below
id | name | age ----+----------+----- 2 | Smith | 35 3 | Lancelot | 800
Like it's opposite day! Have you noticed how easy it is to read these queries in plain English?
In programming, we tend to refer to empty as NULL. With this operator, we can find empty fields in our records.
SELECT * FROM people WHERE age IS NULL;
That's right! There are no empty fields in our table, though, which means that there's no result!
Let's check the people table for fields that aren't empty, shall we?
SELECT name FROM people; WHERE age IS NOT NULL;
//Output Below
id | name | age ----+----------+----- 1 | Bob | 20 2 | Smith | 35 3 | Lancelot | 800 4 | Tom | 75 5 | Jerry | 75
Well, that was to be expected because none of the fields in the people table are empty.
So, we've ventured into assignment, logic, comparison, and negation operators. Phew!
Let's get name values based on age values that are greater than 20, shall we?
SELECT name FROM people WHERE age > 20;
//Output Below
name ------- Bob Smith
Great! You've really gotten a hang of this operator stuff, haven't you?
So now that we're equipped with lots of statements and clauses, let's look into queries with more detail!
WHOLE TABLE?
For this chapter, our people table has been extended to make room for more queries.
We've got the following fields now: id, first_name, last_name, age, email, and city. Let's dive in!
How about we get to know all of the people in our table?
SELECT first_name , last_name FROM people;
//Output Below
first_name | last_name ------------+------------- Bob | Jones Smith | Agent Lancelot | du Lac Sherlock | Holmes Margaret | Hamilton Ada | Lovelace Joan | of Arc Amelia | Earhart SpongeBob | SquarePants Leia | Organa
We can separate our fields with commas! Cool, right? Again, our results are stored in a temporary result table.
How would we go about finding the cities our people live in and showing them in ascending order?
SELECT city, first_name FROM people ORDER BY city ASC;
//Output Below
city | first_name ---------------+------------ Atchison | Amelia Atlantis | Bob Bikini Bottom | SpongeBob Camelot | Lancelot London | Ada London | Sherlock Mega City | Smith Paoli | Margaret Polis Massa | Leia Rouen | Joan
Great! We can use the ASC keyword to order fields such as city.
Psst: just using 'city' would work as well since that would ask is implied when using ORDER BY.
So this screen is a bit small to keep showing us all of the records in a table.
How do we limit these to 5?
SELECT last_name, email FROM people LIMIT 5;
//Output Below
last_name | email -----------+----------------------- Jones | bob@explorer.com Agent | agent@smith.com du Lac | sir@roundtable.com Holmes | private@detective.com Hamilton | genius@nasa.com
That's right! We're limiting our result set to five records.
We'll be seeing how to pick the first or the last records in a later chapter.
Say we want to see a record where 3 conditions are true.
SELECT email FROM people WHERE city = 'London' AND age > 50 AND last_name = 'Holmes';
//Output Below
email ----------------------- private@detective.com
That's right! We can chain together as many conditions as we like with AND.
We can use multiple conditions with different clauses as well!
SELECT email, age FROM people WHERE city = 'London' AND (age > 50 OR age < 50);
//Output Below
email | age -----------------------+----- private@detective.com | 100 countess@math.com | 36
That's right! We can use OR to determine either condition as true, which means we'll get a result set that fits both bills.
Wait a minute, doesn't that look similar to another clause we learned about?
SELECT email, age FROM people WHERE age BETWEEN 36 AND 45;
//Output Below
email | age ---------------------------+----- agent@smith.com | 45 countess@math.com | 36 aviator@atlantic.com | 41
Ok, so it's not the same. The comparison operators we used in the last lesson (> and <) are not inclusive, whereas BETWEEN, is!
Note that if we don't specify an order, the result set will order itself by the record's position in the table.
How can we use patterns to find multiple fields in our table?
SELECT first_name, city FROM people WHERE first_name LIKE '%ob' ;
//Output Below
first_name | city ------------+--------------- Bob | Atlantis SpongeBob | Bikini Bottom
That's it! % matches any characters that come before ob in this case.
What if we want a very specific pattern?
SELECT email FROM people WHERE email LIKE '__________@%';
//Output Below
email --------------------------- private@detective.com heroine@france.com aviator@atlantic.com general@rebelalliance.com
Ha! We've asked for emails that have 7 characters before the @. Remember, each _ represents an individual character, and % represents any.
Let's work with some negation and sorting. Let's sort last names in descending order, as long as the city for the record is not London.
SELECT first_name, last_name
FROM people
WHERE city NOT IN ('London')
ORDER BY last_name DESC;//Output Below
first_name | last_name ------------+------------- Joan | of Arc Lancelot | du Lac SpongeBob | SquarePants Leia | Organa Bob | Jones Margaret | Hamilton Amelia | Earhart Smith | Agent
Yeah! Did you notice that SQL sorts capital letters first?
Psst: we can also use the != operator instead of writing NOT IN.
We've really got a handle on it now, seeing as we've learned to combine multiple statements, clauses and operators to get the results we want from our table!
How do we get the first 5 distinct cities in our table, in alphabetical order?
SELECT DISTINCT city FROM people ORDER BY city LIMIT 5;
Yep! We need to select the cities, order them, and then limit the results!
SQL also has built in functions. A function combines many lines of code into one word we can use.
Aggregate functions give us a single value from a calculation performed on all values in a column.
FUNCTION(column_name)
This is the conventional syntax for aggregate functions
But calculating values isn't the only thing we can do.
Scalar functions returns a value based on the input we have given.
FUNCTION(input)
The input we give a function is called a parameter. All parameters are placed in between parentheses.
This aggregate function counts the number of records in whatever field we give it.
SELECT COUNT(*) FROM people;
//Output Below
count
-------
10
Tada! That's pretty simple, isn't it? We're asking for a count of all records in the people table!.
This function works with integers and will give us the largest number in a field.
SELECT MAX(age) FROM people;
//Output Below
max
-----
800
That's it! The oldest person in our table is 800 years old!
And as for the opposite? The youngest person in our table?
SELECT MIN(age) FROM people;
//Output Below
min
-----
15
Yeah! MIN will give us the minimum value in a column.
We have the oldest and youngest ages in our table. What about adding all the ages in our table together?
SELECT SUM(age) FROM people;
//Output Below
sum
------
1209
Whew, that's a lot of years!
While we're tackling ages, what is the average age of the records in people?
SELECT AVG(age) FROM people;
//Output Below
avg
----------------------
120.9000000000000000
Yes it is! It's also very precise isn't it?
We can put functions inside functions. That average is kind of unwieldy, so why don't we round it up to something more useful?
SELECT ROUND(AVG(age)) FROM people;
//Output Below
round
-------
121
ROUND automatically rounds up. Since we need something to round, the AVG function gets called first.
What about those moments when we want to know how long a text field is?
SELECT LENGTH(email) from people;
//Output Below
length
--------
16
15
18
21
15
17
18
20
20
25
This brings up the length of each record's email address.
We can also modify the result set as it is returned to us.
SELECT first_name, UCASE(last_name)
FROM people;,
//Output Below
first_name | upper
------------+-------------
Bob | JONES
Smith | AGENT
Lancelot | DU LAC
Sherlock | HOLMES
Margaret | HAMILTON
Ada | LOVELACE
Joan | OF ARC
Amelia | EARHART
SpongeBob | SQUAREPANTS
Leia | ORGANA
Pretty cool, huh? Can you think of cases where it could be useful to have result sets that are all upper or lower case?
Psst: if we wanted to change all of the letters to lowercase, we'd need to use the LCASE keyword instead.
Now that we know how to work with aggregate and scalar functions, how would we find out the number of people who live in London?
SELECT COUNT(*)
FROM people
WHERE city = 'London';
//Output Below
count------- 8
Brilliant! We've got this under control. IN and = both work.
Psst: we can also use the IN statement instead of =.
We've seen NOT NULL a few times before. But let us iterate that it is valid for any data type, not just integers.
CREATE TABLE playlist( id INT NOT NULL, song_title TEXT NOT NULL, length INT, album_id INT);
By default, fields can be NULL, in other words, have no value.
Another useful constraint is UNIQUE. We add a unique column slightly differently.
Let's modify the id in the table we just created. We may have many songs in our playlist, but each one is unique.
ALTER TABLE playlist ADD UNIQUE (id)
That's right! UNIQUE constraint requires a value for a field, but this value cannot be duplicated anywhere in that column.
There is a constraint that checks whether a value we want to add meets a certain condition.
Let us make sure that songs in the playlist table can not be less than three minutes long!
ALTER TABLE playlist( ADD CONSTRAINT length CHECK (length >= 3);
Spot on! With the CHECK constraint, we can make sure that any value in the length column is greater than 3.
When we do not have a value to add to a column, we can set a DEFAULT constraint that will fill it in for us.
If we're just building a playlist, surely we haven't played the song yet!
ALTER TABLE playlist ADD played BOOLEAN DEFAULT true;
Great! So we're adding a new column that accepts the data type BOOLEAN to our table.
If we don't specify whether or not we've played a song, it will automatically be filled in as true.
You know, I don't think we need the length column. How do we get rid of it?
ALTER TABLE playlist DROP length;
Excellent! Now that column, and any constraints associated with it, have been dropped from the table.
Keys are an important concept in relational databases.
They are primarily used to create relationships among the different tables in a database. Remember we did this?
CREATE TABLE playlist( id INT NOT NULL);
A key is essentially an id, much like the one we illustrate above.
But if we assign a key constraint to a field, that field will automatically be populated with a unique integer every time we add a new record to a table.
We have mentioned that all records need unique identifiers and have used a number of constraints to build that field.
However, PRIMARY KEY does that for us.
ALTER TABLE playlist ADD PRIMARY KEY(id);
That's correct! This automatically requires that the field be unique and that it cannot be empty. A primary key can only be used in one field per table.
With SQL, we can use multiple tables, and relate them to each other in order to compartmentalize information.
How relevant is it to our playlist table to have every song's album information on it?
CREATE TABLE albums( id INT PRIMARY KEY, name TEXT, year INT, artist TEXT);
We can just create a new table that has album information and relate it to our playlist!
It's important to understand that tables in the same database relate to one another through keys.
A foreign key in a table is a primary key from a different one!
ALTER TABLE playlist ADD FOREIGN KEY (album_id) REFERENCES albums(id);
Bingo! We're using the primary key from our new albums table in the album_id field in the playlist table.
Have a look at our related tables:
-------- albums table --------- id | name | year | artist ----+----------+------+-------- 1 | Jazz | 1978 | Queen 2 | The Game | 1980 | Queen ------- playlist table -------- id | song_title | album_id ----+-------------------+---------- 1 | Save Me | 2 2 | Don't Stop Me Now | 1 3 | Bicycle Race | 1
Unlike primary keys, a table can have multiple foreign keys, and foreign keys can be empty fields.
Two songs in our playlist belong to the same album. SQL is pretty cool, huh?
Great! Since we've covered primary keys, foreign keys, and constraints, let's recap and make sure that the artist field in the albums table cannot be empty.
ALTER TABLE albums ALTER COLUMN artist SET NOT NULL;
Yippee! Now we will always have to provide an artist name when creating a new record for this table.
SQL (Standard Query Language) is the standard language for anything database related.
To build a web site that shows data from a database, you will need to get and install the following;
An RDBMS ( Relational Database Management System) program like MySQL from https://dev.mysql.com/downloads/ or,
You can also install SQL Server Express from https://www.microsoft.com/en-in/download/details.aspx?id=30438 . It is a free version of Microsoft SQL Server which allows up to 10GB of database storage.
Alternatively, if you're planning to develop an application with PHP and MySQL you can install WampServer or XAMPP. WampServer is a Windows web development environment.
It allows you to create web applications with Apache2, PHP and a MySQL database. It will also provide the MySQL administrative tool PhpMyAdmin to easily manage your databases using a web browser.
You can download it from here http://www.wampserver.com/en/.
And that's all you'll need to get started.