Section 5 - CRUD Commands Flashcards by Augie Fash

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

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

How well did you know this?

Not at all

Perfectly

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

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

How well did you know this?

Not at all

Perfectly

Fill in the missing information:

C -
R - READ
U - UPDATE
D - DELETE

C - CREATE

How well did you know this?

Not at all

Perfectly

Fill in the missing information:

C - CREATE
R -
U - UPDATE
D - DELETE

R - READ

How well did you know this?

Not at all

Perfectly

Fill in the missing information:

C - CREATE
R - READ
U -
D - DELETE

U - UPDATE

How well did you know this?

Not at all

Perfectly

Fill in the missing information:

C - CREATE
R - READ
U - UPDATE
D -

D - DELETE / DESTROY

How well did you know this?

Not at all

Perfectly

What are the 4 fundamental manipulations of data?

Create, Read, Update, Delete / Destroy

How well did you know this?

Not at all

Perfectly

Describe each of the 4 fundamental manipulations of data:

Create
Read
Update
Delete

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

How well did you know this?

Not at all

Perfectly

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

Common in other practices of web development.

How well did you know this?

Not at all

Perfectly

What the command for deleting a table?

DROP TABLE ;

How well did you know this?

Not at all

Perfectly

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

SELECT

How well did you know this?

Not at all

Perfectly

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

SELECT * FROM ;

How well did you know this?

Not at all

Perfectly

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

SELECT FROM ;

How well did you know this?

Not at all

Perfectly

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

SELECT , FROM ;

How well did you know this?

Not at all

Perfectly

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

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.

How well did you know this?

Not at all

Perfectly

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

SELECT * FROM WHERE = [value]

How well did you know this?

Not at all

Perfectly

Read the following statement:

SELECT * FROM cats WHERE breed=’tabby’;

Should there be single quotes around breed?

NO.

The syntax is correct as-is.

SELECT * FROM cats WHERE age=4;

Should there be quotes around ‘4’?

Why or why not?

No.

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

SELECT * FROM dogs WHERE name=Bartholemew;

Should there be quotes [ ‘ ‘ ] around Bartholemew?

Why or why not?

YES.

Because ‘Bartholemew’ is a string.

Correct syntax should be:

SELECT * FROM dogs WHERE name=’Bartholemew’;

YES or NO:

By default, are SELECT queries cAsE SenSitive?

NO, they are NOT case sensitive.

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

SELECT name, breed FROM dogs;

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

SELECT name FROM cats WHERE breed=’tabby’;

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

SELECT name, age FROM cats WHERE cat_id=age;

SELECT name, breed FROM dogs WHERE dog_id = age;

What does the above query do?

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

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?

YES.

Write the following command: Select all names from the dogs table; print the names back as "dog name".

SELECT name FROM dogs AS 'dog name';

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'.

SELECT name AS 'doggie name', age AS 'years old' FROM dogs;

SYNTAX: Is the comma after 'kitty breed' correct or not? SELECT name AS 'cat name', breed AS 'kitty breed', FROM cats;

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

What is the basic syntax for updating a record?

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'.

UPDATE cats SET breed = 'shorthair' WHERE breed = 'Tabby'; In the above example, what does UPDATE cats signify?

This signifies which table to update.

UPDATE students SET grade = 'A' WHERE grade = 'A-'; In the above example, what does SET refer to?

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-.

UPDATE students SET level = 'Sophomore' WHERE level = 'Freshman'; What is happening to level in the above example?

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'.

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.

UPDATE children SET age = 12 WHERE name = 'Augie';

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?

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.

Does DELETE-ing use a *, as SELECT-ing does? For example, SELECT * FROM dogs

NO, deleting does NOT use a *.

What is the appropriate syntax for deleting a record?

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.

Will deleting an entry change the PRIMARY KEY id?

NO, it will not.

TRUE or FALSE: DELETE-ing an entry changes the PRIMARY KEY id.

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.

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

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.

DELETE: DELETE FROM dogs; What do you think the above command would do?

It would delete ALL information from the dogs table. Note that the DELETE command does not need a *

Write the following command: Delete all name from the prisoners table where jail_sentence is 0 [years].

DELETE name FROM prisoners WHERE jail_sentence = 0;