SQL
a language to interact with RDBMS
RDBMS
a software application we use to create and maintain a relational db

RDBMS don’t speak english. They speak in SQL.

There are small differences between the SQL of different RDBMS-es:

  • MySQL
  • Postgres
  • Oracle
  • Microsoft SQL Server
  • MariaDB
  • SQLite
Name Initials Purpose
DQL Data Query Language Fetching Data
DML Data Manipulation Language Insert, Update and Delete
DDL Data Definition Language Define Schemas
DCL Data Control Language Determine Access and Permissions

Comments

-- this is a comment
SELECT name, age FROM employee;
/*
comment in
multiple lines
*/

Basic Rules

  • We prefer to write reserved words in uppercase.
  • Any command end with a semi colon.

Main Data Types

Type Description
INT integer
DECIMAL(M,N) M - total of digits, N - digits after point
VARCHAR(N) string, N - max num of characters
BLOB large data like images
DATE YYYY-MM-DD
TIMESTAMP YYYY-MM-DD HH:MM:SS

Creating DB

CREATE DATABASE school;

Use DB

needed before refering any tables

USE school;

Creating a Table

CREATE TABLE student (
	student_id INT PRIMARY KEY,
	name VARCHAR(20),
	major VARCHAR(20)
);

equivalent to

CREATE TABLE student (
	student_id INT,
	name VARCHAR(20),
	major VARCHAR(20),
	PRIMARY KEY(student_id)
);

Changing the definition of a table after it was created:

ALTER TABLE student ADD gpa DECIMAL(3, 2);

ALTER TABLE student DROP COLUMN gpa;

Columns Constraints

  • NOT NULL
  • UNIQUE

Not Null

CREATE TABLE student (
	student_id INT,
	name VARCHAR(20) NOT NULL,
	major VARCHAR(20),
	PRIMARY KEY(student_id)
);

Unique

CREATE TABLE company (
	company_id INT,
	name VARCHAR(20) UNIQUE,
	PRIMARY KEY(company_id)
);

Default

CREATE TABLE student (
	student_id INT,
	name VARCHAR(20),
	major VARCHAR(20) DEFAULT 'undecided',
	PRIMARY KEY(student_id)
);

Auto

CREATE TABLE student (
	student_id INT AUTO_INCREMENT,
	name VARCHAR(20),
	major VARCHAR(20),
	PRIMARY KEY(student_id)
);

List All Tables

SHOW TABLES;

List Table Definition

DESCRIBE student;

Droping Table

DROP TABLE student;