Introduction of SQL

Structured Query Language

Attributes

1 row == 1 record == 1 object

Data types classes

• TEXT
• VARCHAR
• NUMERIC
• INTEGER
• REAL
• BLOB
• others

Create a Table

CREATE TABLE users (

    id INTEGER PRIMARY KEY,

    email VARCHAR(255)

);

• CREATE TABLE + name of the table
• INTEGER, VARCHAR - types of attributes

Alter a Table

ALTER TABLE usres

    RENAME TO users;

ALTER TABLE users

    ADD COLUMN is_student BOOLEAN;

• CREATE    ==    INSERT

• READ        ==    SELECT

• UPDATE    ==    UPDATE

• DELETE     ==    DELETE

SELECT

SELECT language

FROM languages

WHERE answer="200 OK";

• FROM - the name of the table, you want to select from
• SELECT - the names of the attributes you want to select
• WHERE - the SELECT clause filters the table's rows by these conditions
• ; is required for most RDBMS;

SELECT with AND, OR, NOT

SELECT language

FROM languages

WHERE answer="200 OK" OR answer="NDI="

SELECT language

FROM languages

WHERE answer="200 OK" AND id=2;

SELECT with LIKE

SELECT language
FROM languages
WHERE answer LIKE "%o%";

• LIKE - it's used as a `regex`

SELECT from more tables

SELECT moviestar.name, movie.title
FROM moviestar, movie ;

INSERT INTO

INSERT INTO language

VALUES (9, "PHP", "$$$", 0, "WHY?");

• INSERT INTO - the name of the table we want to insert the values
• VALUES - set a value for every attribute of the table 

UPDATE

UPDATE languages
SET answer = 8
WHERE language = "Python";

• UPDATE - the name of the table we want to update
• SET - set values to all attributes we want to update
• WHERE - we need to SELECT the rows before updating them so we need this condition too

DELETE

DELETE FROM languages
WHERE language = "PHP";

• DELETE FROM - the name of the table
• WHERE - we need to SELECT the rows before deleting them so we need the WHERE conditions