SQL_Read, Update, Delete

★ When deleting or updating data,

it is important to first verify the data using the 'SELECT' statement! ★

 

 

Accessing all columns in a table

-> SELECT * FROM cats;

 

Retrieving only the 'name' column data

-> SELECT name FROM cats;

 

Retrieving multiple specific columns

-> SELECT name, age FROM cats;

 

Using 'WHERE' to specify conditions

-> SELECT * FROM cats WHERE age = 4; -> SELECT * FROM cats WHERE name = 'Egg';

 

 

Given Task: Retrieve the name and age data of cats whose breed is 'Tabby'.

-> SELECT name, age FROM cats WHERE breed = 'Tabby';

 

Given Task: Retrieve the name data of cats where 'cat_id' is equal to 'age'.

-> SELECT name FROM cats WHERE cat_id = age;

 

 

Using 'AS' to set an alias for the result columns

(This does not actually change the column names in the table)

-> SELECT cat_id AS id, name FROM cats;

 

Updating Data

-> UPDATE cats SET breed = 'Shorthair' WHERE breed = 'Tabby';

-> UPDATE cats SET age = 14 WHERE name = 'Misty';

 

 

Given Task: Change the data of the cat whose name is 'Jackson' to 'Jack'.

-> SELECT * FROM cats WHERE name = 'Jackson';

-> UPDATE cats SET name = 'Jack' WHERE name = 'Jackson';

 

Given Task: Change the breed of the cat whose name is 'Ringo' to 'British Shorthair'.

-> SELECT * FROM cats WHERE name = 'Ringo';

-> UPDATE cats SET breed = 'British Shorthair' WHERE name = 'Ringo';

 

Given Task: Update the age of cats whose breed is 'Maine Coon' to 12 years.

-> SELECT * FROM cats WHERE breed = 'Maine Coon';

-> UPDATE cats SET age = 12 WHERE breed = 'Maine Coon';

 

 

Deleting Data

-> DELETE FROM cats;

-> DELETE FROM cats WHERE name = 'Egg';

 

 

Given Task: Delete the data of cats that are 4 years old.

-> SELECT * FROM cats WHERE age = 4;

-> DELETE FROM cats WHERE age = 4;

 

Given Task: Delete the data of cats where 'id' is equal to 'age'.

-> SELECT * FROM cats WHERE cat_id = age;

-> DELETE FROM cats WHERE cat_id = age;

 

Given Task: Delete all cat data without deleting the table!

-> SELECT * FROM cats;

-> DELETE FROM cats;