sqlite3 first.db
relational database
must define names of table and number of colums
dbbrowser sqlitebroweser.org
keywords are case insensitive
CREATE TABLE students (id INTEGER PRIMARY KEY AUTOINCREMENT, first_name VARCHAR(50),
last_name VARCHAR(50), email VARCHAR(255), phone_number VARCHAR(25), phone_number VARCHAR(25), bio TEXT);
.table
type of data and restriction
INTEGER
VARCHAR (255 max, used for small string)
TEXT (No restrictions)
DECIMAL
FLOAT
BOOLEAN (True,false,null)
DATETIME (stores day and time)
PRIMARY KEYS
id INTEGER PRIMARY KEY AUTOINCREMENT
use single quotes
use the semi colon at the end of every query
Upcase SQL-related keywords
add --------------------- ALTER TABLE students ADD COLUMN age INTEGER;
delete ----------------------- DROP TABLE at
CRUID
Create Read Update Delete
Create
INSERT INTO students(first_name, last_name, email) VALUES ('Tony', 'Kim', 'dohoonk.smg@gmail.com');
SELEcT * FROM students;
READ
SELECT * FROM students WHERE id=1;
SELECT first_name, last_name FROM students;
SELECT first_name, last_name FROM students WHERE age=45;
= equals
combining codtions
AND/OR
SELECT * FROM students; SELECT * FROM students WHERE id > 1000; SELECT * FROM students WHERE age > 40; SELECT * FROM students WHERE registration_date > '2016-01-18'; SELECT * FROM students WHERE age > 40 OR age < 20;
IS NULL / IS NOT NULL
SELECT * FROM students WHERE first_name ILIKE 'peter'; --------------case inseseitve
SELECT * FROM students WHERE first_name ILIKE '%ph';
SELECT * FROM students; SELECT * FROM students WHERE id > 1000; SELECT * FROM students WHERE age > 40; SELECT * FROM students WHERE registration_date > '2016-01-18'; SELECT * FROM students WHERE age > 40 OR age < 20; SELECT * FROM students WHERE age is NULL OR age < 20; SELECT * FROM students WHERE first_name ILIKE '%ph'; SELECT * FROM students WHERE age BETWEEN 25 AND 35; SELECT * FROM students WHERE registration_date BETWEEN '2016-01-18' AND '2016-01-23' ORDER BY first_name, last_name DESC; SELECT * FROM students WHERE registration_date BETWEEN '2016-01-18' AND '2016-01-23' ORDER BY first_name DESC, last_name ASC;
must be placed at the very end
SELECT * FROM students WHERE age > 30 ORDER BY first_name ASC, last_name DESC LIMIT 10;
OFFSET SELECT * FROM students WHERE first_name ILIKE 'ke%' ORDER BY age, last_name LIMIT 10 OFFSET 10; (num - 1) * 10
UPDATE students SET first_name='TAM' WHERE id=48; UPDATE students SET first_name = 'TAM', last_name ='Kbeili' WHERE id=48;
SELECT * FROM students WHERE id = 48
DELETE
DELET FROM students WHERE id=2; DELET FROM enrolments WHERE student_id=2; DELETE FROM students WHERE id=2;
COUNT
SELECT COUNT(*) FROM students;
you can also add condtions like
SELECT COUNT(*) FROM students WHERE age > 25;
AS
This changes the allias of returned value
SELECT COUNT(*) AS student_count FROM students WHERE age > 25;
SUM
You can pass condtions to filter returns sum of given condtion
SELECT SUM(age) AS total_life_experience FROM students;
SELECT SUM(id) AS sum_id FROM students;
AVG finds the average
If you wanted to find the average age of students who have registered after jan 1 2015 ROUND will round the number SELECT ROUND(AVG(age)) AS average_of_age FROM students WHERE registration_date > '2015-01-1';
MAX gives you the maximum age
This will give you the age of oldest person named jonathan. SELECT MAX(age) AS oldest FROM students WHERE first_name = 'Jonathan';
GROUP BY This will give you number of occurances of an name SELECT first_name, COUNT(*) AS occurance_count FROM students GROUP BY first_name;
This will group students by their age and count the number of students that are in that age and return the value SELECT age, COUNT(id) AS number_students FROM students GROUP BY age ORDER BY number_students;