CS50 Cheatsheet for SQL

SQL Basics

What is SQL?

SQL stands for Structured Query Language.
SQL is a powerful language used to manage and query databases.
SQL is used to communicate with databases to perform tasks such as creating tables, inserting data, and querying data.

SQL vs. `sqlite3`

sqlite3 is a command-line utility that allows you to interact with, create, manage, and query SQLite databases.
SQL is the language used to interact with databases, while sqlite3 is a tool that allows you to run SQL commands.

SQL Syntax

SQL keywords are case-insensitive.
SQL statements end with a semicolon ;.
SQL comments start with -- and end at the end of the line:

-- This is a comment

`sqlite3` Commands

.open dbname: Open a database file.
.tables: List all tables in the database.
.schema tablename: Show the schema of a table.
.exit: Exit the sqlite3 utility.

Run SQL Commands

To run SQL commands, you can use the sqlite3 utility and pass the SQL commands as arguments:

sqlite3 dbname.db "SQL command;"

You can also run SQL commands interactively by running sqlite3 dbname.db and entering the commands one by one.

sqlite3 dbname.db

This will open the sqlite3 utility and allow you to enter SQL commands interactively.

SQL command;

Run SQL Commands from a File

If you have a file with SQL commands, you can run them using the sqlite3 utility:

You can also run SQL commands from a file using the following command:

sqlite3 dbname.db < filename.sql

This will run the SQL commands in the filename.sql file on the dbname.db database.

You can also run SQL commands from a file interactively by running sqlite3 dbname.db and entering the following command:

.read filename.sql

This will read and execute the SQL commands in the filename.sql file.

Finally, using cat and a pipe, you can run SQL commands from a file:

cat filename.sql | sqlite3 dbname.db

Output Queries to a File

You can output the results of a query to a file using the .output command:

.output filename.txt

SELECT column1, column2, ...
FROM tablename;
.output

This will save the output of the query to the filename.txt file.

You can also output the results of a query to a file using the following command:

sqlite3 dbname.db "SQL command;" > filename.txt

This will save the output of the query to the filename.txt file.

From an existing file, you can either run

sqlite3 dbname.db < filename.sql > output.txt

cat filename.sql | sqlite3 dbname.db > output.txt

SQL Data Types

INT: Integer
FLOAT: Floating-point number
CHAR(n): Fixed-length character string
VARCHAR(n): Variable-length character string
DATE: Date
TIME: Time
BOOLEAN: Boolean

`CREATE`, `USE`, and `INSERT`

Create a Database

CREATE DATABASE dbname;

Use a Database

USE dbname;

Create a Table

CREATE TABLE tablename (
    column1 datatype,
    column2 datatype,
    ...
);

Insert Data into a Table

INSERT INTO tablename (column1, column2, ...)
VALUES (value1, value2, ...);

SQL Constraints

`NOT NULL` Constraint

CREATE TABLE tablename (
    column1 datatype NOT NULL,
    column2 datatype,
    ...
);

`UNIQUE` Constraint

CREATE TABLE tablename (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

`PRIMARY KEY` Constraint

CREATE TABLE tablename (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

`FOREIGN KEY` Constraint

CREATE TABLE table1 (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

CREATE TABLE table2 (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column1) REFERENCES table1(column1)
);

`CHECK` Constraint

CREATE TABLE tablename (
    column1 datatype,
    column2 datatype,
    ...
    CHECK (condition)
);

`DEFAULT` Constraint

CREATE TABLE tablename (
    column1 datatype DEFAULT value,
    column2 datatype,
    ...
);

`SELECT`

Select All Columns

SELECT *
FROM tablename;

Select Specific Columns

SELECT column1, column2, ...
FROM tablename;

Select Distinct Values

SELECT DISTINCT column
FROM tablename;

Filter Rows with a WHERE Clause

SELECT column1, column2, ...
FROM tablename
WHERE condition;

Sort Rows with an ORDER BY Clause

SELECT column1, column2, ...
FROM tablename
ORDER BY column ASC|DESC;

Limit the Number of Rows

SELECT column1, column2, ...
FROM tablename
LIMIT n;

Select Data from a Table

SELECT column1, column2, ...
FROM tablename;

Group Rows with an Aggregate Function

SELECT column1, aggregate_function(column2)
FROM tablename
GROUP BY column1;

Filter Groups with a HAVING Clause

SELECT column1, aggregate_function(column2)
FROM tablename
GROUP BY column1
HAVING condition;

Subqueries

SELECT column1, column2, ...
FROM (
    SELECT column1, column2, ...
    FROM tablename
) AS subquery;

`UPDATE` and `DELETE`

Update Data in a Table

UPDATE tablename
SET column1 = value1, column2 = value2, ...
WHERE condition;

Delete Data from a Table

DELETE FROM tablename
WHERE condition;

Drop a Table

DROP TABLE tablename;

Drop a Database

DROP DATABASE dbname;

`JOIN` and `INDEX`

Join Tables

SELECT column1, column2, ...
FROM table1
JOIN table2
ON table1.column = table2.column;

Create an Index

CREATE INDEX indexname
ON tablename (column);

Drop an Index

DROP INDEX indexname;

SQL Functions

Aggregate Functions (for `SELECT`)

COUNT(): Count the number of rows
SUM(): Calculate the sum of values
AVG(): Calculate the average of values
MIN(): Find the minimum value
MAX(): Find the maximum value

For example, you can return the number of rows in a table using COUNT():

SELECT COUNT(column)
FROM tablename;

Mathematical Functions

ABS(): Calculate the absolute value
ROUND(): Round a number
CEIL(): Round up to the nearest integer
FLOOR(): Round down to the nearest integer
RAND(): Generate a random number

String Functions

CONCAT(): Concatenate strings
UPPER(): Convert a string to uppercase
LOWER(): Convert a string to lowercase
LENGTH(): Get the length of a string
SUBSTRING(): Extract a substring

Date Functions

NOW(): Get the current date and time
YEAR(): Extract the year from a date
MONTH(): Extract the month from a date
DAY(): Extract the day from a date
DATE_FORMAT(): Format a date