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.

Step Four

select 'Hello from the console!';

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.

• integers, or whole numbers, like 0, 1, 2…

• text, which stores any length of text, which can include letters, punctuation marks, letters, and emoji

• dates, which stores dates and times.

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');

select * from albums;

insert into albums values (2, 'Remain in Light', 'The 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;

Step Ten

select title, artist from albums;

-- select * from albums;

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

Step One

select count(*) from albums;

Step Two - Using where

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

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

select distinct(artist) from albums;