SQL CREATE, ALTER and DROP

SQL

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(50) NOT NULL,
    dept    VARCHAR(20),
    salary  DECIMAL(10, 2) CHECK (salary > 0),
    city    VARCHAR(30) DEFAULT 'Pune'
);

Common Data Types

Type	Use
INT, BIGINT	Whole numbers
DECIMAL(p, s)	Exact numbers such as money
FLOAT, DOUBLE	Approximate numbers
CHAR(n) / VARCHAR(n)	Fixed / variable-length text
TEXT	Long text
DATE, DATETIME, TIMESTAMP	Dates and times
BOOLEAN	True/false

ALTER TABLE

SQL

ALTER TABLE employees ADD email VARCHAR(100);
ALTER TABLE employees MODIFY name VARCHAR(80);       -- MySQL syntax
ALTER TABLE employees RENAME COLUMN city TO location;
ALTER TABLE employees DROP COLUMN email;

DROP vs TRUNCATE vs DELETE

DROP	TRUNCATE	DELETE
Removes the table and its structure	Removes all rows, keeps the structure	Removes selected rows (WHERE)
DDL	DDL, fast, usually can't be rolled back	DML, can be rolled back

Track your progress through the SQL course

← Prev Next →