Databases & SQL

2:07 Lesson 1 Databases and SELECT A database stores information in tables, and a table is a grid — not unlike a spreadsheet. Across, one row is one record: everything known about one student.… 1:26 Lesson 2 Choosing columns A star gives you every column, and usually you want only a few. So instead of the star, list the ones you want, separated by commas. The result keeps the two… 1:58 Lesson 3 Filtering with WHERE Every query so far returned all the rows. WHERE changes that: it keeps only the rows that match a condition. Scores of eighty or more, and the other two… 2:05 Lesson 4 AND, OR and NOT A WHERE can test more than one thing. AND requires both to be true. Take form equals eleven A on its own and three survive: Mei, Sara and Lin. Now add AND… 1:59 Lesson 5 Sorting with ORDER BY Rows come back in no particular order. It usually LOOKS like insertion order, but nothing promises that, and a database is free to change it. ORDER BY is how… 2:01 Lesson 6 Counting and averaging Sometimes you do not want the rows, you want a summary of them. An aggregate function does that, and it changes the shape of the answer: five rows go in and… 2:04 Lesson 7 LIKE, IN and BETWEEN Sometimes you do not know the exact value — you know the shape of it. LIKE compares against a pattern. Here are our five students, three times over. Anything… 2:05 Lesson 8 Grouping with GROUP BY Last lesson an aggregate folded the whole table into one number. Usually you want one number per something — per form, per customer, per month. GROUP BY does… 2:07 Lesson 9 Tables, keys and relationships Real databases hold several tables, and the reason is easiest to see by doing it wrong first. Put everything in one table and Ada's name and city are written… 2:03 Lesson 10 INNER JOIN Splitting the data was the right thing to do, but now a question about customers and their orders touches both tables. A join puts them back together, and ON… 1:59 Lesson 11 Joins with grouping Everything in this lesson you have already met. A join makes one wide table. GROUP BY sorts that into piles. An aggregate turns each pile into one row each.… 2:09 Lesson 12 Creating tables SQL splits into two halves, and it is worth being able to name them. DDL, the data definition language, changes the structure — the tables themselves. DML, the… 1:53 Lesson 13 Changing the schema A table's design is not fixed once it is made. ALTER TABLE ADD gives an existing table a new column, named and typed just as it would have been in the CREATE.… 1:51 Lesson 14 Inserting rows INSERT adds a row. Name the table, list the columns you are supplying, and then give the matching values. Run it and the product table has a fourth row — the… 1:53 Lesson 15 Updating rows UPDATE changes values in rows that already exist. SET says which column gets which new value, and WHERE says which rows it applies to. Here the WHERE picks the… 1:49 Lesson 16 Deleting rows DELETE removes rows. Name the table and give a WHERE, and that one row goes — the glue, here. Notice the two things that did not happen: the other rows are… 2:30 Lesson 17 Normalisation Put every club booking in one table and something goes wrong quietly. Look at the coach column: Mr Ng is written twice over, once for each Chess booking. If he…