Section 5 - CRUD Commands

Question 1

What are the 4 basic actions we can do with our data?

Answer 1

C - CREATE
R - READ
U - UPDATE
D - DELETE / DESTROY

Question 2

What does CRUD stand for in MySQL? (As well as in other programming languages.

Answer 2

C - CREATE
R - READ
U - UPDATE
D - DELETE / DESTROY

Question 3

Fill in the missing information:

C -
R - READ
U - UPDATE
D - DELETE

Answer 3

C - CREATE

Question 4

Fill in the missing information:

C - CREATE
R -
U - UPDATE
D - DELETE

Answer 4

R - READ

Question 5

Fill in the missing information:

C - CREATE
R - READ
U -
D - DELETE

Answer 5

U - UPDATE

Question 6

Fill in the missing information:

C - CREATE
R - READ
U - UPDATE
D -

Answer 6

D - DELETE / DESTROY

Question 7

What are the 4 fundamental manipulations of data?

Answer 7

Create, Read, Update, Delete / Destroy

Question 8

Describe each of the 4 fundamental manipulations of data:

Create
Read
Update
Delete

Answer 8

Create - Add Data In
Read - Search It / Read it Back Out
Update It - Self-explanatory
Delete It - Self explanatory

Question 9

Is CRUD unique to databases, or is it common in other practices of web development?

Answer 9

Common in other practices of web development.

Question 10

What the command for deleting a table?

Answer 10

DROP TABLE ;

Question 11

What is the basic command for retrieving and searching data from a database?

Answer 11

SELECT

Question 12

What is the command for retrieving ALL of the data in a table?

Answer 12

SELECT * FROM ;

Question 13

What is the command for retrieving a specific column of data in a table?

Answer 13

SELECT FROM ;

Question 14

What is the command / syntax for retrieving multiple columns of data from a table?

Answer 14

SELECT , FROM ;

Question 15

If you do a SELECT * FROM command, what order is the data shown in?

Answer 15

The data was shown in the order the table was initially setup.

For example, let's reference the following:
CREATE TABLE dogs(
        name VARCHAR(100),
        breed VARCHAR(100),
        age INT
        );

If we did a SELECT * FROM dogs, the data would show in the order of name, breed, age.

Question 16

When using SELECT statements, what command do we add to it to get more specific?

Answer 16

SELECT * FROM WHERE = [value]

Question 17

Read the following statement:

SELECT * FROM cats WHERE breed=’tabby’;

Should there be single quotes around breed?

Answer 17

NO.

The syntax is correct as-is.

Question 18

SELECT * FROM cats WHERE age=4;

Should there be quotes around ‘4’?

Why or why not?

Answer 18

No.

You don’t need quotes around 4, because it is an integer.

Question 19

SELECT * FROM dogs WHERE name=Bartholemew;

Should there be quotes [ ‘ ‘ ] around Bartholemew?

Why or why not?

Answer 19

YES.

Because ‘Bartholemew’ is a string.

Correct syntax should be:

SELECT * FROM dogs WHERE name=’Bartholemew’;

Question 20

YES or NO:

By default, are SELECT queries cAsE SenSitive?

Answer 20

NO, they are NOT case sensitive.

Question 21

Write a command for selecting rows named ‘name’ and ‘breed’ from a table named ‘dogs’.

Answer 21

SELECT name, breed FROM dogs;

Question 22

Write a query for a table named cats, where you select all cats’ names where their breed is ‘tabby’.

Answer 22

SELECT name FROM cats WHERE breed=’tabby’;

Question 23

Write a query for a table named cats. SELECT all name and age for cats where the cat_id column equals the age column.

Answer 23

SELECT name, age FROM cats WHERE cat_id=age;

Question 24

SELECT name, breed FROM dogs WHERE dog_id = age;

What does the above query do?

Answer 24

This prints all dog names and breeds where the dog_id (primary key) matches the dogs age.

Question 25

When you select columns of data, can you print the data back in a different column order than the order in which you originally setup the table?

Answer 25

YES.

Question 26

Write the following command:

Select all names from the dogs table; print the names back as “dog name”.

Answer 26

SELECT name FROM dogs AS ‘dog name’;

Question 27

Pretend you have a table named ‘dogs’, with columns named ‘name’ and ‘age’. Select all names and ages. Print the names as ‘doggie name’ and the ages as ‘years old’.

Answer 27

SELECT name AS ‘doggie name’, age AS ‘years old’ FROM dogs;

Question 28

SYNTAX:
Is the comma after ‘kitty breed’ correct or not?

SELECT name AS ‘cat name’, breed AS ‘kitty breed’, FROM cats;

Answer 28

No, the comma is not correct. This would return an error.

Question 29

What is the basic syntax for updating a record?

Answer 29

UPDATE SET = ‘’ WHERE = ‘’;

For example:

UPDATE dogs SET breed = ‘Saint Bernard’ WHERE breed = ‘alps rescue dog’;

In the above example, all instances of ‘alps rescue dog’ would be replaced with ‘Saint Bernard’.

Question 30

UPDATE cats SET breed = ‘shorthair’ WHERE breed = ‘Tabby’;

In the above example, what does UPDATE cats signify?

Answer 30

This signifies which table to update.

Question 31

UPDATE students SET grade = ‘A’ WHERE grade = ‘A-‘;

In the above example, what does SET refer to?

Answer 31

SET refers to what we’re ‘setting’ (ie changing) the data to be. We’re setting all grade to A in instances where the current grade is A-.

Question 32

UPDATE students SET level = ‘Sophomore’ WHERE level = ‘Freshman’;

What is happening to level in the above example?

Answer 32

In this case, ‘level’ is a column of data within the students table.

We’re setting all instances of ‘Freshman’ in the level table to the new value of ‘Sophomore’.

Question 33

Updating records:

Pretend we have a table named children. Update any any instance in the name column where the name is “Augie”. For these instances, update the data in the relevant age column to 12.

Answer 33

UPDATE children SET age = 12 WHERE name = ‘Augie’;

Question 34

What’s a good best practice / rule of thumb for updating data? What’s something that’s good to do before you actually update the data?

Answer 34

It’s a good idea to run a SELECT command before you update data in order to verify whether you’re updating the correct data.

Question 35

Does DELETE-ing use a *, as SELECT-ing does?

For example, SELECT * FROM dogs

Answer 35

NO, deleting does NOT use a *.

Question 36

What is the appropriate syntax for deleting a record?

Answer 36

DELETE FROM WHERE = ‘’;

For example, DELETE FROM students WHERE name = ‘unknown’;

The above example would delete every student who was currently listed in the database as unknown.

Question 37

Will deleting an entry change the PRIMARY KEY id?

Answer 37

NO, it will not.

Question 38

TRUE or FALSE:

DELETE-ing an entry changes the PRIMARY KEY id.

Answer 38

FALSE.

Any PRIMARY KEY data stays the same.

For instance, if you only had one user on your site, with an id of one, and you deleted that user, the next user to register on your site would have an id of 2.

Question 39

What’s a good reason for MySQL/SQL not changing the PRIMARY KEY id after deleting a record?

Answer 39

Most sites use an enormous amount of data, utilizing many different tables across the website. Updating a PRIMARY KEY in one table could wreak havoc across multiple other tables.

Question 40

DELETE:

DELETE FROM dogs;

What do you think the above command would do?

Answer 40

It would delete ALL information from the dogs table.

Note that the DELETE command does not need a *

Question 41

Write the following command:

Delete all name from the prisoners table where jail_sentence is 0 [years].

Answer 41

DELETE name FROM prisoners WHERE jail_sentence = 0;