4. DML Practice with customer_info

DML Practice with customer_info

Data Manipulation Language, or DML, is the part of SQL used to work with rows:

INSERT adds rows.
SELECT reads rows.
UPDATE changes rows.
DELETE removes rows.

This practice uses the existing companydb database from the previous lesson.

Create the table

sql

USE companydb;

CREATE TABLE customer_info (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(25),
  last_name VARCHAR(25),
  salary INT
);

AUTO_INCREMENT lets MySQL generate the next id value for each inserted row.

Insert rows

Insert one customer:

sql

INSERT INTO customer_info (first_name, last_name, salary)
VALUES
  ("Krish", "Naik", 50000);

Insert multiple customers:

sql

INSERT INTO customer_info (first_name, last_name, salary)
VALUES
  ("Darius", "N", 50000),
  ("Mahendra", "B", 50000),
  ("Ankit", "Sharma", NULL);

Check the table:

sql

SELECT *
FROM customer_info;

Find missing and present salaries

Find customers whose salary is missing:

sql

SELECT first_name, last_name
FROM customer_info
WHERE salary IS NULL;

SELECT *
FROM customer_info
WHERE salary IS NULL;

Find customers whose salary is present:

sql

SELECT first_name, last_name
FROM customer_info
WHERE salary IS NOT NULL;

SELECT *
FROM customer_info
WHERE salary IS NOT NULL;

Replace a NULL value

Update the customer whose generated ID is 4.

sql

UPDATE customer_info
SET salary = 60000
WHERE id = 4;

Delete a row

sql

DELETE FROM customer_info
WHERE id = 3;

Then inspect the remaining data:

sql

SELECT *
FROM customer_info;

Insert another row correctly

The original practice script had a mismatched insert: it listed three target columns but supplied four values. Because id is auto-generated, the clean version is:

sql

INSERT INTO customer_info (first_name, last_name, salary)
VALUES
  ("Darius", "N", 50000);

If you want to set the ID yourself, include the id column and choose an unused value.

sql

INSERT INTO customer_info (id, first_name, last_name, salary)
VALUES
  (10, "Darius", "N", 50000);

Alter the table

Add one column:

sql

ALTER TABLE customer_info
ADD COLUMN dob DATE;

Add multiple columns:

sql

ALTER TABLE customer_info
ADD COLUMN email VARCHAR(25),
ADD COLUMN address VARCHAR(25);

Inspect the schema:

sql

DESC customer_info;

Modify the column size:

sql

ALTER TABLE customer_info
MODIFY COLUMN address VARCHAR(30);

Drop a column:

sql

ALTER TABLE customer_info
DROP COLUMN email;

This exercise is small, but it covers the core row-level operations used in real data cleanup work.