CRUD Basics
What does CRUD stand for?
Create, Read, Update, Delete
Which SQL command is used to read information from a table?
SELECT
What is an ID used for in a database?
To identify a record.
What is the purpose of a Foreign Key?
To connect records from related tables.
Is this query safe or dangerous? DELETE FROM players;
Dangerous. It deletes every row.
Which SQL command is normally used to add a new record?
INSERT
What does SELECT * FROM players; show?
All columns and all rows from players.
Can two rows have the same Primary Key value?
No
In a relationship between teams and players, which table would normally contain id_team as a Foreign Key?
players
What is missing here? UPDATE players SET wins = 10; if we only want to modify player 3.
WHERE id_player = 3
Which SQL command is used to modify an existing record?
UPDATE
Write the condition to show only players with more than 5 wins.
WHERE wins > 5
What does AUTO_INCREMENT do?
Automatically generates the next numeric ID.
What does a 1:N relationship mean?
One record from one table can be related to many records in another table.
Write a query to delete only the player with id_player = 5.
DELETE FROM players WHERE id_player = 5;
Which SQL command is used to remove a record from a table?
DELETE
What does ORDER BY wins DESC do?
Orders the results from highest wins to lowest wins.
Why is an ID usually safer than a name when using UPDATE or DELETE?
Because an ID is unique, while names can be repeated.
If one team can have many players, can the same id_team appear several times in the players table?
YES
A player with id_player = 2 earns 3 additional wins. Write the query without replacing the current value manually.
UPDATE players SET wins = wins + 3 WHERE id_player = 2;
A student says that CREATE TABLE represents the “Create” operation in CRUD. Why is this not correct when we are talking about records?
Because CRUD “Create” normally refers to creating a record with INSERT, not creating the table structure.
Write a query that shows only nickname and wins for players with 8 or more wins, ordered from highest to lowest wins.
SELECT nickname, wins FROM players WHERE wins >= 8 ORDER BY wins DESC;
What is the difference between an ID column and a Primary Key?
An ID is a column/value used for identification; PRIMARY KEY is the rule that guarantees uniqueness and prevents NULL values.
What should happen if a player is inserted with id_team = 99, but team 99 does not exist? Why?
MySQL should reject it because the Foreign Key would violate referential integrity.
Write the important line that connects players.id_team with teams.id_team when creating the players table.
FOREIGN KEY (id_team) REFERENCES teams(id_team)