Ashutosh Patidar · notes

SQL for Data Science › Chapter 5

Chapter 5

Types of SQL commands

1:51:00 → 1:57:30DDL · DML · TCL · DCLData typesOperators & comments

The four families of SQL commands

Keep this chart in your head whenever you work with a database: every SQL command belongs to one of four groups. The names sound confusing at first, but each one says what it does.

DDL
Data Definition Language
CREATEALTERDROPTRUNCATE

Defines the structure: databases, tables, columns, constraints. CREATE makes things, ALTER modifies them, DROP deletes them, TRUNCATE empties them.

DML
Data Manipulation Language
INSERTUPDATEDELETESELECT

Works on the data inside tables, and is the most frequently used group. INSERT adds rows, UPDATE changes them, DELETE removes them, SELECT fetches them (CRUD!).

TCL
Transaction Control Language
COMMITROLLBACK

Controls transactions. COMMIT saves changes permanently (like conn.commit() in Chapter 2); ROLLBACK undoes them if something goes wrong. A transaction either completes fully or not at all.

DCL
Data Control Language
GRANTREVOKE

Controls permissions. GRANT gives a user access; REVOKE takes it back.

Good to know

Some books put SELECT in its own group, DQL (Data Query Language), and add SAVEPOINT to TCL. The course follows the four-group version above.

Mini-game · sort the commands
Tap a command, then tap its family.
CREATEALTERDROPTRUNCATE INSERTUPDATEDELETESELECT COMMITROLLBACK GRANTREVOKE
DDL
DML
TCL
DCL

Classic confusion: DROP vs TRUNCATE vs DELETE
  • DROP TABLE t (DDL): the table itself is gone, structure and data.
  • TRUNCATE TABLE t (DDL): the table stays but all rows are removed.
  • DELETE FROM t WHERE … (DML): removes the chosen rows only.

SQL is a standard; each DBMS has a dialect

SQL is the standard language all relational DBMSs use. But Oracle, MySQL, PostgreSQL and SQL Server each have their own implementation of it, so a data type or function in one may not exist in another. Since we use MySQL, look up MySQL's flavour when unsure. A handy reference is w3schools: SQL data types for MySQL, SQL Server and MS Access.

Data types (MySQL)

Like every programming language, SQL has data types; you pick one per column. You don't need to memorise them all: you'll pick them up by using them. Nitish's advice: skim the list once during free time, and in interviews you're rarely asked their exact ranges.

TypeUse it for
TINYINT, SMALLINT, INT, BIGINTWhole numbers of increasing size (INT ≈ ±2.1 billion).
BOOL / BOOLEANTrue/false (stored as TINYINT(1): 0 or 1).
DECIMAL(p, s)Exact numbers with a fixed number of decimals: money, ratings like DECIMAL(2,1) → 4.2.
FLOAT, DOUBLEApproximate decimals (scientific values).
TypeUse it for
CHAR(n)Fixed length text, e.g. CHAR(2) state codes.
VARCHAR(n)Variable length text up to n characters: names, emails (VARCHAR(255)).
TEXTLong text: reviews, descriptions.
BLOBBinary Large OBject: raw bytes such as files/images.
ENUM('a','b',…)One value from a fixed list, e.g. ENUM('veg','non-veg').
TypeFormat / use
DATEYYYY-MM-DD: date of birth.
TIMEhh:mm:ss
DATETIMEYYYY-MM-DD hh:mm:ss: order time.
TIMESTAMPLike DATETIME, often used with DEFAULT CURRENT_TIMESTAMP.
YEARA 4-digit year.
Pick the best data type
For each column, choose the type you'd use. (These mirror choices made in the course's ER-diagram example.)

Operators

SQL has operators just like other languages. You'll learn them by using them in queries, so there's no need to sit and memorise them; skim once.

Arithmetic

+ - * / %

Comparison

= > < >= <= <>
Note: equality is a single =, and "not equal" is <> (MySQL also accepts !=).

Logical

AND OR NOT IN BETWEEN LIKE EXISTS ALL ANY

Try them in a real SQL engine (runs in your browser):

Playground · operators
or Ctrl/⌘ + Enter

Comments

Writing comments in SQL is simple:

-- a single-line comment (note the space after --)

/* a multi-line
   comment */

SELECT * FROM users; -- comments can also follow code
# MySQL also accepts a hash for single-line comments

Conceptual questions

These check whether you understand why, not just what. Several go one step beyond the video.

You ran DELETE FROM orders; inside a transaction and haven't committed. Can you undo it?

DML changes inside an open transaction can be rolled back. (In MySQL, DDL like DROP/TRUNCATE auto-commits, so it can't be.)

Why is DECIMAL preferred over FLOAT for money?

Rounding errors are unacceptable in money.

What does WHERE dept = NULL return?

NULL means "unknown", so = NULL is never true. Always use IS NULL / IS NOT NULL.

A user should be able to read the sales table but not change it. Which command family handles that?

GRANT SELECT ON sales TO user; is DCL.

VARCHAR(255) vs CHAR(255) for names: what's the practical difference?

Use CHAR only for truly fixed-length codes.