SQL Basics

most of it will be based on the sqlite database.

general:

• query language used to interact with relation dbs
• it's not case sensitive.

"SELECT" Statement - retrieve data

• select all fields - retrieve all the columns and rows from the users table.

SELECT * FROM users;

• select single field - retrieve the name column from the users table

SELECT name FROM users;

• multiple fields - retrieve the id & name columns from the users table

SELECT id, name FROM users;

• select distinct fields - retrieves only distinct values (non-duplicates) of city from the users table

SELECT DISTINCT city FROM users;

"CREATE TABLE" Statement - creating a table

• create a new table users with id as number, name as text and city as text as fields.

CREATE TABLE users(
    id INTEGER,
    name TEXT,
    city TEXT
);

"ALTER TABLE" - altering the table i.e, making changes to a table without deleting any data

• rename the people table to users

ALTER TABLE people RENAME TO users; 

• rename the handle column to username in users table

ALTER TABLE users RENAME COLUMN handle TO username;

• add a text column named password in users table

ALTER TABLE users ADD COLUMN password TEXT;

• drop the city column from users table

ALTER TABLE users DROP COLUMN city;

NULL

In SQL, a cell with a NULL value indicates that the value is missing.

Constraints

It enforces some specific behaviour. eg:

• PRIMARY KEY
• UNIQUE
• NOT NULL