DML queries: working with the data inside tables

DDL shaped the structure. DML (Data Manipulation Language) works on the data: INSERT, SELECT, UPDATE, DELETE. These are the CRUD operations from Chapter 2, now written by hand. Of the four, SELECT (reading) is what you'll use most as a data scientist, so it gets most of the time.

INSERT: adding rows

Continuing with the students table from the DDL chapter (student_id auto-increment, name, college, email unique):

-- 1) values for ALL columns, in table order (NULL lets auto-increment fill the id)
INSERT INTO students VALUES (NULL, 'Ankit', 'IIT Delhi', 'ankit@gmail.com');

-- 2) name the columns you're filling; skip the auto-increment one
INSERT INTO students (name, college, email) VALUES ('Neha', 'NIT Trichy', 'neha@gmail.com');

-- 3) column order is up to you, as long as the values follow it
INSERT INTO students (email, name, college) VALUES ('rahul@gmail.com', 'Rahul', 'BITS Pilani');

SELECT * FROM students;

Several rows at once: one VALUES, many brackets (Q1 of Practice 1):

INSERT INTO students (name, college, email) VALUES
    ('Riya',  'IIT Bombay', 'riya@gmail.com'),
    ('Kabir', 'IIT Kanpur', 'kabir@gmail.com'),
    ('Sara',  'DTU',        'sara@gmail.com');

SELECT * FROM students;

SELECT: reading data

For reading, Nitish imports the Titanic dataset as a table named train. Its columns: passenger_id, survived (1/0), pclass (1st, 2nd or 3rd class, like train coaches: 1st is the best), name, sex, age, sibsp (siblings + spouse aboard), parch (parents + children aboard), fare and embarked (port: Southampton, Cherbourg, Queenstown).

-- everything: * means "all columns"
SELECT * FROM train;

-- only some columns, in the order you list them
SELECT name, sex, survived FROM train;

Renaming columns with AS

SELECT name AS passenger_name, sex AS gender, survived
FROM train;

Computed columns

You can create new columns on the fly with arithmetic. Family members aboard = siblings/spouse + parents/children:

SELECT name, sibsp + parch AS family FROM train;

Nitish's playful examples: how old would each passenger be today? (The Titanic sank in 1912; he added 106 because the class was in 2018.) And a constant column, say a ₹1 lakh compensation for every passenger:

SELECT name, age + 106 AS current_age FROM train;

SELECT name, 100000 AS compensation FROM train;

In MySQL you could compute the year gap live with YEAR(CURDATE()) - 1912.

DISTINCT: unique values

SELECT DISTINCT sex FROM train;        -- 2 values
SELECT DISTINCT pclass FROM train;     -- 3 values

-- on several columns: every unique COMBINATION
SELECT DISTINCT sex, pclass FROM train;

WHERE: filtering rows

-- all 1st-class passengers
SELECT * FROM train WHERE pclass = 1;

-- combine conditions with AND / OR
-- 1st-class passengers who died
SELECT * FROM train WHERE pclass = 1 AND survived = 0;

-- passengers aged 10 to 20 (BETWEEN includes both ends)
SELECT name, age FROM train WHERE age BETWEEN 10 AND 20;

The order SQL actually runs a query

You write SELECT … FROM … WHERE …, but the database executes clauses in a different order. Knowing this explains many errors (for example, why a WHERE can't use an alias defined in SELECT).

The orange number on each written clause is its execution step. Hover (or tap) a clause to see where it sits in the other list. SELECT is written first but runs 6th, which is why WHERE can't use a SELECT alias but ORDER BY can.

IN and NOT IN

For the next topics Nitish switches to a Bollywood movies dataset: title, actor, director, genre, budget, india_gross, worldwide, screens (opening screens), runtime (minutes), release_date.

Movies that are either Action, Horror or Drama? With OR you'd write the condition three times (and five times for five genres). IN is shorter and clearer:

-- the long way
SELECT title, genre FROM movies
WHERE genre = 'Action' OR genre = 'Horror' OR genre = 'Drama';

-- the same with IN
SELECT title, genre FROM movies
WHERE genre IN ('Action', 'Horror', 'Drama');

-- everything else
SELECT title, genre FROM movies
WHERE genre NOT IN ('Action', 'Horror', 'Drama');

Wildcards with LIKE

-- titles starting with 'A'
SELECT title FROM movies WHERE title LIKE 'A%';

-- 'Tiger' anywhere in the title
SELECT title FROM movies WHERE title LIKE '%Tiger%';

-- titles ending with 'a'
SELECT title FROM movies WHERE title LIKE '%a';

-- actor surname Khan OR Kapoor
SELECT title, actor FROM movies
WHERE actor LIKE '%Khan' OR actor LIKE '%Kapoor';

-- titles of exactly 5 characters
SELECT title FROM movies WHERE title LIKE '_____';

UPDATE: changing rows

For updates Nitish uses the small passengers table (id, name, email) so the real datasets stay untouched.

SELECT * FROM passengers;

-- WITHOUT a WHERE, every row changes (rarely what you want!)
-- UPDATE passengers SET name = 'Rahul';

-- only rows that match: Gmail users become 'Rohit'
UPDATE passengers SET name = 'Rohit' WHERE email LIKE '%gmail%';

-- several columns at once: Yahoo users get a new name and email
UPDATE passengers
SET name = 'Anant', email = 'anant@gmail.com'
WHERE email LIKE '%yahoo%';

SELECT * FROM passengers;

DELETE: removing rows

-- one row
DELETE FROM passengers WHERE id = 1;

-- rows matching several conditions
DELETE FROM passengers WHERE id > 2 AND email LIKE '%yahoo%';

SELECT * FROM passengers;

-- every row (the table stays, now empty)
-- DELETE FROM passengers;

Built-in functions

From here SQL gets more powerful: functions, sorting and (next chapter) grouping. Functions come in two kinds:

-- ABS: drop the minus sign (profit of a flop becomes positive)
SELECT title, worldwide - budget AS profit, ABS(worldwide - budget) AS abs_profit
FROM movies;

-- ROUND: runtime in hours, rounded (optionally to N decimals)
SELECT title, runtime / 60 AS hours,
       ROUND(runtime / 60) AS rounded,
       ROUND(runtime / 60, 1) AS one_decimal
FROM movies;

-- CEIL rounds up, FLOOR rounds down
SELECT title, CEIL(runtime / 60) AS up, FLOOR(runtime / 60) AS down FROM movies;

-- text functions
SELECT UPPER(title), LOWER(title) FROM movies;

SELECT CONCAT(title, ' - ', actor) AS movie_with_star FROM movies;

SELECT title, LENGTH(title) AS chars FROM movies;

-- SUBSTRING(text, start, how_many): positions start at 1, not 0!
SELECT title, SUBSTRING(title, 1, 5) AS first5, SUBSTRING(title, 3, 5) AS from3 FROM movies;

SELECT MAX(budget) FROM movies;          -- costliest budget
SELECT MIN(india_gross) FROM movies;     -- lowest India collection
SELECT SUM(india_gross) FROM movies;     -- everything earned in India
SELECT AVG(india_gross) FROM movies;     -- average per movie
SELECT COUNT(*) FROM movies;             -- number of rows
SELECT COUNT(DISTINCT actor) FROM movies;  -- number of different actors

ORDER BY and LIMIT

Profit = worldwide − budget. Most profitable first:

SELECT title, worldwide - budget AS profit
FROM movies
ORDER BY profit DESC;       -- ASC is the default

-- just the top 5
SELECT title, worldwide - budget AS profit
FROM movies
ORDER BY profit DESC
LIMIT 5;

-- the 5 biggest flops
SELECT title, worldwide - budget AS profit
FROM movies
ORDER BY profit
LIMIT 5;

Sorting on several columns: the first column decides, ties are broken by the next.

-- genres A→Z, and inside each genre titles Z→A
SELECT genre, title FROM movies
ORDER BY genre, title DESC;

Notice that ORDER BY profit may use the alias from SELECT. That works because ORDER BY runs after SELECT (see the execution-order diagram). WHERE profit > 0 would not.

SQL lab

All four datasets are loaded: train (Titanic), movies (Bollywood), students, passengers. Write anything; Reset restores the original data.

Conceptual questions

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