SQL

Relational Databasw

Query - checking info

Storing data

Stored in table

Data Types

Stores data in one or more tables

SELECT * FROM celebs; - show data

  1. Integer, a positive or negative whole number
  2. Text, a text string
  3. Date, the date formatted as YYYY-MM-DD for the year, month, and day
  4. Real, a decimal value

CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);

INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);\

UPDATE celebs
SET age = 22
WHERE id = 1;

ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

DELETE FROM celebs WHERE twitter_handle IS NULL;

SELECT * FROM movies
WHERE name LIKE 'Se_en';

multiple tables

related by primary and foreign keys

cross join

Inner Join


SELECT * specifies the columns our result set will have. Here, we want to include every column in both tables.


FROM albums specifies the first table we are querying.


JOIN artists ON specifies the type of join we are going to use as well as the name of the second table.


Here, we want to do an inner join and the second table we want to query is artists.
albums.artist_id = artists.id is the join condition that describes how the two tables are related to each other.


Here, SQL uses the foreign key column artist_id in the albums table to match it with exactly one row in the artists table with the same value in the id column. We know it will only match one row in the artists table because id is the PRIMARY KEY of artists.

left join

click to edit

SELECT albums.name, albums.year, artists.name FROM albums, artists;

everything plus nulls for em[ties

AS is a keyword in SQL that allows you to rename a column or table using an alias.

SELECT

albums.name AS 'Album',