Section 6 - CRUD Challenge Section

Question 1

What does CRUD stand for?

Answer 1

c REATING
r EADING
u PDATING
d ELETING

Question 2

What is one reason should we name our shirt_size column “shirt_size” and not just size?

Answer 2

MySQL uses a host of different terms internally, and “size” is one of them. So that MySQL doesn’t get mixed up, it’s best to be as specific as possible.

This may also help you when you have multiple tables interacting with each other later on. For instance, maybe you have one table with shirt sizes and another table with pant sizes. Differentiating between them in the column names can be handy.

Question 3

REVIEW:

What are the 2 ways you can write PRIMARY KEY when you create a table?

Create a table named cities, with a column named name, and a PRIMARY KEY named gps_coordinates, and write it both possible ways.

Answer 3

CREATE TABLE cities(
name VARCHAR(100) NOT NULL,
gps_coordinates INT NOT NULL PRIMARY KEY
);

   OR

CREATE TABLE cities(
            name VARCHAR(100) NOT NULL,
            gps_coordinates INT NOT NULL
            PRIMARY KEY(gps_coordinates)
            );

Question 4

What is the below command doing?

UPDATE shirts SET shirt_size = ‘L’ WHERE article = ‘polo shirt’;

Answer 4

• This is updating the shirts table
• This is modifying all items in the article column marked ‘polo shirt’.
  Any entry in the article column marked ‘polo shirt’ is having it’s corresponding shirt_size column entry updated to ‘L’.

Question 5

Now, let’s try updating two columns in a table at once.

Pretend you have a table named shirts, and you want to update the color column to ‘off white’ and the shirt_size column to ‘XS’ for all shirts who’s color is currently white.

Write this command.

Answer 5

UPDATE shirts SET color = ‘off white’, shirt_size = ‘XS’ WHERE color = ‘white’;

Question 6

Write this query:

Select all data from our shirts table where the last_worn column is over 197 [days].

Answer 6

SELECT * FROM shirts WHERE last_worn > 197;

Question 7

Exercise:

Write a command that deletes any entry from our shirts table where column last_worn is greater than 197 [days].

Answer 7

DELETE FROM shirts WHERE last_worn > 197;

Question 8

Write a command for deleting all data within our stocks table, but not deleting the table itself.

Answer 8

DELETE FROM stocks;