SQL, or Structured Query Language, is the language used to create, manage, and query relational database tables. It requests exactly the data an application needs and supports persistent storage beyond a program’s runtime. section 8.2.1 section 8.2.4
A table contains rows and columns, while a schema defines column types and relationships. Start with CREATE TABLE, then use INSERT, SELECT, UPDATE, and DELETE to implement CRUD workflows. section 8.2.1 section 8.2.4
Build queries incrementally:
- Use SELECT * FROM users to retrieve every column and row. section 8.2.4
- Select specific columns and filter records with WHERE, AND, and LIKE, such as finding Alice’s haikus containing “fox”. section 8.2.4
- Change matching records with UPDATE and remove them with DELETE; test each operation by running a follow-up SELECT. section 8.2.4
- Use constraints such as PRIMARY KEY and FOREIGN KEY to identify rows and connect related tables. section 8.2.4
Loading warehouse data
A BigQuery-backed dashboard can construct SELECT * FROM a fully qualified project, dataset, and table name, execute the query, wait for its result, and convert the result to a pandas dataframe. Filtering large datasets in BigQuery can avoid loading all data into memory first. section 7.7.3
CREATE TABLE users (
username VARCHAR(100) PRIMARY KEY,
password_hash VARCHAR(128)
);
Inspect a complete CREATE TABLE statement defining users, a VARCHAR column, and a PRIMARY KEY. section 8.2.4
