Databases and SQL#
SQL stands for Structured Query Language. No mater which (relational) database vendor being used (PostgreSQL, MariaDB, etc.), the language to work with the data and other aspects is the SQL language. SQL does much more than just query data, though.
It is common to pronounce SQL as three letters S Q L (and not as the word “sequel”), which means we say “an SQL statement” rather than “a SQL (sequel) statement”.
Here you’ll find some notes, concepts, ideas, examples and guides on how to do work with:
Data Definition Language (DDL).
Data Manipulation Language (DML).
Data Control Language (DCL).
A few other types of queries.
All of the above are different kinds of SQL statements, but all SQL statements nonetheless. DDL is about the structure of the database structure, while DML operates on the data.
I use mostly PostgreSQL and MariaDB, so expect most examples to work on those.
SQL is a standardized language (even though each vendor adds their own features, commands and other facilities) on top of the standard language. The standard is NOT free (like HTML, CSS or ECMAScript, among others). One has to purchase it on their website. Thankfully, we can be learn for free on the web by reading DB vendors documentation and doing online challenges. Same resources:
SQL statements are composed of keywords (defined in the standard), identifiers (names of tables, columns, views, etc., defined by the DB user or admin), and constants.
SELECT id , name , skill , power FROM jedis WHERE power >= 78 ;
We wrote the keywords in UPPERCASE (common practice).
The identifiers are
jedis (table name) and
power (column names).
78 is a numeric constant.
>= is an operator, which is a special kind of keyword.
WHERE (among many others) are also clauses. We say “the WHERE clause”, or “the SELECT clause”, etc.
Data Definition Language#
The most used DDL languages involve
Let’s create a new database in PostgreSQL:
CREATE DATABASE starwars_dev WITH OWNER = devel ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = 3 IS_TEMPLATE = False;
And then create a table:
CREATE TABLE jedis ( id INTEGER NOT NULL PRIMARY KEY , name VARCHAR(64) NOT NULL , power SMALLINT NOT NULL DEFAULT 50 );
name to be
ALTER TABLE jedis ALTER COLUMN name TYPE VARCHAR(128);
ALTER TABLE jedis ALTER COLUMN name SET NOT NULL;
Make column name not nullable:
ALTER TABLE jedis ALTER COLUMN name DROP NOT NULL;
Remove a column:
ALTER TABLE jedis DROP COLUMN power;
Data Manipulation Language#
The most used DML language statements involve