3. SQL Basics

🎯 Learning Goals

• Understand that SQL is used to store, retrieve, and modify data in (relational) databases

• Recognize common data types in SQL and understand when to use each

• Confidently create tables in a database using SQL

• Confidently insert and retrieve data using SQL

📗 Technical Vocabulary

• Database

• Relational database

• Query

• Table

• Record

• Field

• Primary key

• Data type

• The “L” stands for “Language.”
• SQL is a computer language. Just like a human language, like English or Tagalog, computer languages have vocabulary (words and phrases) and syntax (rules for how to use words and phrases to convey meaning).
• Luckily, both the vocabulary and syntax of SQL are way smaller and and more straightforward than those of English, so we’ll be able to pick it up more quickly than a new human language. SQL has keywords, commands, and syntax rules that define how we can use it to direct a computer to take specific actions.

• The “S” stands for “Structured.” The databases that we interact with through SQL store data in a highly structured way. Data are stored in tables with rows and columns. Here’s an example of how data in our kwk_scholars table might look.
• Each row, or record, contains information about a single scholar. We can say that each row “represents” one scholar.
• Each column, or field, stores a specific data type. We’ll talk more about data types later, but for now, notice that there are different kinds of data in the different columns. The age column stores numbers, while the name column stores text.

select 'Hello world!';

select 'Hello from main.sql!';

1. A name: albums

2. 4 fields:
1. id, an integer, which also serves as the unique primary key for the table. A primary key is an identifier that is guaranteed to be unique for each row of the table.
2. title, a text field, which will store the title of the album.
3. artist, a text field, which will store the name of the artist.
4. release_date, a date field, which will store the date the album came out.

drop table if exists albums;

create table albums (
  id integer primary key,
  title text,
  artist text,
  release_date date
);

insert into albums values (1, '1989', 'Taylor Swift', '2014-10-27');

• insert into albums tells SQL that we want to add a new record to the albums table.

• values (...) provides the data for that record. The values are listed in the same order as the fields we defined when we created the table: id, title, artist, and release_date.

select * from albums;

insert into albums values (2, 'Remain in Light', 'Talking Heads', '1980-10-08');
insert into albums values (3, 'Sour', 'Olivia Rodrigo', '2021-05-21');
insert into albums values (4, 'Hurry Up Tomorrow', 'The Weeknd', '2025-01-31');
insert into albums values (5, 'Guts', 'Olivia Rodrigo', '2023-09-08');

select * from albums;

select title, artist from albums;

-- select * from albums;

-- Print only the title and artist fields.
select title, artist from albums;

select count(*) from albums;

select count(*) from albums where artist = 'Olivia Rodrigo';

select * from albums where release_date < '2022-01-01';

select distinct(artist) from albums;