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;