Introduction to SQL
SQL: Language we use to talk to the database management system
Two Parts
Data Definition Language (DDL)
Data Manipulation Language (DML)
CREATE is used to create new database objects like tables
Name of new table
Name of each column
Declared type of each column in the table → options: sqlite.org/datatype3.html
NULL
Missing information
INTEGER
Whole numbers
Can have size constraints
REAL
Real numbers with decimal values
TEXT
Store strings of text
BLOB
Binary Large OBject
Stores any kind of data
Default value or expression for each column
Default collation sequence for each column
Primary key
If PRIMARY KEY added to one column definition, then the primary key is that one column
If PRIMARY KEY is a table constraint, the primary key of the table consists of the list of columns specified as part of the primary key.
Constraints on the table content
CREATE TABLE Name(
Col1 DATATYPE PRIMARY KEY,
Col2 DATATYPE NOT NULL,
Col3 DATATYPE NOT NULL DEFAULT val
Col4 DATATYPE CHECK(Col4 IN ('item1','item2'))
);DROP is used to delete an existing database object like a table
DROP TABLE Table_name;ALTER is used to modify an existing database object, such as adding, deleting, or modifying columns of a table
ALTER TABLE Table_name
DROP COLUMN col1;
ALTER TABLE Table_name
ADD col5 DATATYPE;
ALTER TABLE Table_name
RENAME COLUMN column_name TO new_name;Data Manipulation
SELECT picks certain columns
AS renames a column
expressions can also appear in a SELECT clause
FROM picks a table
WHERE checks a condition to choose specific rows
SELECT Number AS Acc_Num, Owner, Balance * 120 AS balance_in_yen
FROM Account
WHERE Type = 'savings';SELECT price
FROM Sells
WHERE cafe = 'Caffe bene' AND drink = 'Mocha';Patterns are possible to check with LIKE and NOT LIKE
% means any string
_ means any character
ORDER BY sorts items ascending unless the keyword DESC is added. It can also take multiple columns for sorting
SELECT name
FROM Customers
WHERE phone LIKE '%555-____'
ORDER BY name;