sql_intro.md

Fundamental SQL: Hands On

Getting Started

Create a folder. sql_intro works if you're at a loss for something better.

Fire up SQLite: sqlite3 example.sqlite3

Let's create a new table.

CREATE TABLE students(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(31), age INT);

Getting Data into our Database

We have a table, but it doesn't have any data and that's no good.

INSERT INTO students(name, age) VALUES ('Alan', 35);

Let's do this a few more times with some other students and quantities.

INSERT INTO students(name, age) VALUES ('Ada', 30);
INSERT INTO students(name, age) VALUES ('Adele', 18);
INSERT INTO students(name, age) VALUES ('Steve', 30);

Radio silence.

Let's look at our table to see if anything happened.

SELECT * FROM students;

Look! It's our data!

But our data is ugly. Let's flip on some fancy pants settings and take another shot at this..

.mode column
.header on

SELECT * FROM students;

SELECT name FROM students;
SELECT age FROM students;

SELECT name AS first_name FROM students;
SELECT age AS years_since_birth FROM students;

SELECT * FROM students WHERE name='Alan';
SELECT * FROM students WHERE age = 18;
SELECT * FROM students WHERE age > 21;
SELECT * FROM students WHERE LENGTH(name)=7;
SELECT name FROM students WHERE id=3;

DELETE FROM students WHERE name='Steve';

That's it. It's gone. I hope you had back-ups.

Potentially even worse (unless you meant to do it, of course):

Okay, jokes aside: let's say you wanted to add an additional column of data.

ALTER TABLE students ADD COLUMN hometown VARCHAR(127);

Let's run this and make sure that it works.

SELECT * FROM students;

Unless we say otherwise, UPDATE will update all of the rows in the table.

UPDATE students SET hometown='Denver';

That's probably not what we want. So, let's say otherwise.

UPDATE students SET age=42 WHERE name='Alan';
UPDATE students SET age=37 WHERE name='Ada';
UPDATE students SET hometown='London' WHERE name='Ada';

Right now, we're getting our data in the order it was received.

SELECT * FROM students ORDER BY id;

Well, that's kind of the same, isn't it. Let's reverse it!

SELECT * from students ORDER BY id DESC;

Neat. Sorting by ids is kind of boring. Let's try something else.

SELECT * from students ORDER BY age;

And the other way.

SELECT * from students ORDER BY age DESC;

(Let's take a break…)

(…and, we're back.)

Putting stuff in and getting it back out is not too much different that what we could do with Excel.

Let's take a look at SQL's GROUP_BY function.

SELECT age FROM students GROUP BY age;

It's a start, but it's not really helpful.

SELECT age, COUNT(id) FROM students GROUP BY age;

Much nicer, but that column title is kind of ugly.

SELECT age, COUNT(id) AS number FROM students GROUP BY age;

AS let's use name a column something else. Sweet.

SELECT hometown, COUNT(id) as population FROM students GROUP BY hometown;

Our dataset is pretty small, but it looks like we can learn some pretty interesting things about our students.

Relational Databases are All About Relations

Let's create a second table called grades.

CREATE TABLE grades(id INTEGER PRIMARY KEY AUTOINCREMENT, grade INT, student_id INTEGER, created_at DATETIME);

Let's create some grades.

INSERT INTO grades(student_id, grade, created_at) VALUES(1, 95, CURRENT_TIMESTAMP);
INSERT INTO grades(student_id, grade, created_at) VALUES(2, 72, CURRENT_TIMESTAMP);
INSERT INTO grades(student_id, grade, created_at) VALUES(1, 84, CURRENT_TIMESTAMP);
INSERT INTO grades(student_id, grade, created_at) VALUES(3, 55, CURRENT_TIMESTAMP);
INSERT INTO grades(student_id, grade, created_at) VALUES(2, 72, CURRENT_TIMESTAMP);
INSERT INTO grades(student_id, grade, created_at) VALUES(3, 100, CURRENT_TIMESTAMP);
INSERT INTO grades(student_id, grade, created_at) VALUES(1, 70, CURRENT_TIMESTAMP);
SELECT * FROM grades;

I'm pretty smart, but I can't really remember which student_ids refer to which students.

But we have an id column on our students table and we have a students_id column on our grades table. So, I can like Vulcan mind-meld them on that shared common.

This is a called a JOIN.

SELECT * FROM students INNER JOIN grades ON students.id=grades.student_id;

When we're only using one table. We don't have to keep talking about what table we're using. As soon as we start talking about two tables. We have to be a little more specific. We do this with a dot notation.

SELECT students.name, grades.grade FROM students INNER JOIN grades ON students.id=grades.student_id;

So, we've selected just two column names.

SELECT students.name, COUNT(grades.grade) as number_of_tests_taken FROM students INNER JOIN grades ON students.id=grades.student_id GROUP BY students.id;

Now we can see the number of grades in the grades we have for each student in the students database.

SELECT students.name, AVG(grades.grade) as average FROM students INNER JOIN grades ON students.id=grades.student_id GROUP BY students.id;

Let's say we want the average instead. Because—let's face it—we probably want the average.

Three's Company

Students don't just give themselves grades. Teachers give them grades.

CREATE TABLE teachers(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(63));
INSERT INTO teachers(name) VALUES ('Mr. Belding');
INSERT INTO teachers(name) VALUES ('Mr. Feeny');
INSERT INTO teachers(name) VALUES ('Ms. Frizzle');
SELECT * FROM teachers;

So, now I have a table of teachers.

Great. But we forgot that relation part.

ALTER TABLE grades ADD COLUMN teacher_id INTEGER;
UPDATE grades SET teacher_id=2 WHERE id=1;
UPDATE grades SET teacher_id=1 WHERE id=2;
UPDATE grades SET teacher_id=2 WHERE id=3;
UPDATE grades SET teacher_id=3 WHERE id=4;
UPDATE grades SET teacher_id=1 WHERE id=5;
UPDATE grades SET teacher_id=2 WHERE id=6;
UPDATE grades SET teacher_id=3 WHERE id=7;
SELECT * FROM grades;

Much better.

Now, we can not only see which student received which grade, but we can also see which teacher gave them that grade.

SELECT students.name, grades.grade, teachers.name FROM students INNER JOIN grades ON students.id=grades.student_id INNER JOIN teachers ON grades.teacher_id=teachers.id;

We can also see how many students each teacher has.

SELECT teachers.name, COUNT(students.id) FROM students INNER JOIN grades ON students.id=grades.student_id INNER JOIN teachers ON grades.teacher_id=teachers.id GROUP BY grades.teacher_id;

As you can see, SQL statements can get a little unwieldy. Luckily, we have some tools to help us out.