Section 4 - Inserting Data Flashcards by Augie Fash

Write syntax for INSERTing two names and ages into a table named cats.
The column names are and .

INSERT INTO cats(name, age)
VALUES (‘Dobby’, 7),
(‘Panther’, 10);

How well did you know this?

Not at all

Perfectly

What is the name of the command you use to put data into tables?

INSERT

How well did you know this?

Not at all

Perfectly

When you INSERT data into a table, does the order of the data matter?

YES

How well did you know this?

Not at all

Perfectly

Why does the order of the data matter when INSERT-ing data into a table.

The data needs to match the order of the column you’re inserting the data into.

Essentially, you would be attempting to INSERT data into incorrect columns.

For example, if you were inserting data into a VARCHAR column named and into a INT column named , if you tried to insert 31 and ‘Chad’, into the columns in that order, you would return an error.

Why would you return an error? Because you’d be trying to insert text into an INT column.

You would also not only be putting the age into the wrong column, but it would also be inserted as the wrong data type, because it would be read by the system as a string/VARCHAR/letters/characters, and not as a number.

How well did you know this?

Not at all

Perfectly

Create a table called employees.

Have a column for first name and a column for years worked.

CREATE TABLE employees(
first_name VARCHAR(100),
years_worked INT
);

How well did you know this?

Not at all

Perfectly

What is the general command for seeing the data we have in a table.

Eg, how do we check out data?

Use the SELECT command.

How well did you know this?

Not at all

Perfectly

What does the SELECT command do?

This allows us to see the data we have in our table.

How well did you know this?

Not at all

Perfectly

What would be our command for seeing all of the data we have in a table called sports_teams?

SELECT * FROM sports_teams;

How well did you know this?

Not at all

Perfectly

What would this command do?

SELECT * FROM world_record_holders;

This would show you all the data held in the table named world_record_holders.

How well did you know this?

Not at all

Perfectly

Can you insert your data in a different order than the order of the columns in the table?

YES

For instance, if you had a cats table, with a breed column and an average_life _span column, in that order, you could insert data in the opposite order.

When setting up the command, you would simply write it as INSERT INTO cats(average_life_span, breed) rather than writing breed and average_life_span in the opposite order.

How well did you know this?

Not at all

Perfectly

What is the syntax for bulk (more than 1) values into a table?

Write an example for inserting two dogs into a table named dogs, with columns named and .

INSERT INTO dogs(name, breed)
VALUES(‘Joe’, ‘lapdog’),
(‘Good Boy’, ‘golden doodle’);

How well did you know this?

Not at all

Perfectly

Write syntax for creating a table named , which includes columns named , , and .

CREATE TABLE people(
             first_name VARCHAR(30),
             last_name VARCHAR(50),
             age INT
              );

How well did you know this?

Not at all

Perfectly

How can you use quotes inside of an inserted value?

1) Escape the quotes with a backslash. For example:
“This text has "quotes" in it.
OR
“This text has 'quotes' in it.

2) You can also alternate single and double quotes. For example:
‘This text has “quotes” in it.’
OR
“This text has ‘quotes’ in it.”

How well did you know this?

Not at all

Perfectly

Pretend we’re inserting the below text. In this example, what is this particular syntax being used for? / What is this syntax doing?

‘What is happening in “this” instance.’

We are alternating single and double quotes in order to be able to use quotation marks inside of a value insertion.

For example, maybe we want to insert the last name of O’Connel into our last_name column, but we don’t want the apostrophe/quote mark in O’Connel to make us return a synax error.

How well did you know this?

Not at all

Perfectly

What is the command for seeing Warnings?

SHOW WARNINGS;

How well did you know this?

Not at all

Perfectly

What does the SHOW WARNINGS command do?

Shows any warnings, such as if the data is truncated, we inserted the wrong type of data in a particular column, etc.

How well did you know this?

Not at all

Perfectly

Can we SHOW WARNINGS anytime?

NO.
You can only show a warning immediately after the warning is noted. If you perform another action before using the SHOW WARNINGS command, you’ll return “empty set”.

What does NULL mean?

It means that the value of the entry is not known.

If the value of an entry is not known, what is it?

It is NULL.

TRUE or FALSE:

NULL means the value equals zero.

FALSE.

This is false false false false false. VERY FALSE!

If we our table doesn’t allow a value to be NULL, what will happen if you try to insert data that’s NULL?

The data would not be inserted, and you would return an error.

What is the command from preventing NULL values from being inserted in our tables/databases?

NOT NULL

Create a table named cars, with one column of variable character length, that cannot be null.

CREATE TABLE cars(
type_of_car VARCHAR(100) NOT NULL
);

At what point do you specify whether a field can be NULL?

When you setup the table initially.

What happens if you do not have a default value set and your field is set to NOT NULL.

The entry will generally default to 0 / zero for an INT field, and defaults to an empty string for a string field (such as VARCHAR).

What is a default value?

If you try to insert data into a field marked NOT NULL that would result in a NULL value, the entry defaults to whatever value you have set for the default value.

Why are default values helpful?

This helps us specify a fallback if no value is specified. This also allows us to more easily search / aggregate any entries that would have been NULL. For instance, if there's no default value for age specified, and we try to enter a NULL value for age, the system will automatically default to 0. This is confusing, because some of our entries may actually have a legitimate entry of 0. The system automatically defaulting NULL values to 0 means that our NULL values would be intermixed with our values that *should* be 0, causing confusion in our data set.

If NULL is allowed in the table, when we insert data into the table, can we specifically specify that the value is NULL? (Yes or No)

YES

How do you set a default value?

Use the DEFAULT command when creating a table.

Create a table called tools, with columns and , and give default values for both.

CREATE TABLE tools( (type_of_tool VARCHAR(100) DEFAULT 'Unknown Tool', price_of_tool INT DEFAULT 999999999 );

Being able to add completely identical data for multiple entries poses a lot of problems. Describe possible problems.

If the data is identical in every way, we have NO way of telling the data apart, or of telling which data is legitimate and which data isn't. For example, if we had 4 name entries in our database, all named Chad Watkins, we would have no way to tell the data apart, to know whether each Chad Watkins is in fact distinct, or whether they are simply duplicate entries.

What is the term / command for having a unique identifier on a row?

PRIMARY KEY

What is a PRIMARY KEY?

This is a command specifying a unique identifier on a given row. For example, User #1, User #2, User #3 etc.

Create a table called dogs with a row called dog_id. | Write the 2 ways you can syntactically make dog_id a PRIMARY KEY.

``` CREATE TABLE dogs( dog_id INT NOT NULL PRIMARY KEY ); OR you can write it this way: CREATE TABLE dogs( dog_id INT NOT NULL, PRIMARY KEY(dog_id) ); ```

Should PRIMARY KEYs be NOT NULL?

YES. If PRIMARY KEYs are NULL, it defeats the purpose of having a unique identifier, as allowing them to be NULL would allow the unique identifier to not be known.

What happens if you try to insert a duplicate entry into a PRIMARY KEY field?

You would return an error. Why? Because that's the whole point of a primary key.

What's a command we can use so that we don't have to manually specify the PRIMARY KEY?

AUTO_INCREMENT

What does the AUTO_INCREMENT command do?

It automatically adds 1 (increments 1) when a new entry is inserted.

What's something that using AUTO_INCREMENT solves?

It allows us to not have to manually type ID's or keep track of what PRIMARY KEY number comes next. Alongside of PRIMARY KEY, AUTO_INCREMENT allows us to have multiple duplicate entries in our database. For example, I could have 5 different John Smith's in my school directory, and have each one be uniquely identifiable, as PRIMARY KEY and AUTO_INCREMENT allows us to have a unique identifier for each one.

# Define the following table: Table Name: employees Columns: id = number (auto-increments), mandatory, primary key last_name = text, mandatory first_name = text, mandatory middle_name = text, not mandatory age = number, mandatory current_status = text, mandatory, defaults to 'employed'

CREATE TABLE employees( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, last_name VARCHAR(100) NOT NULL, first_name VARCHAR(100) NOT NULL, middle_name VARCHAR(100), age INT NOT NULL, current_status VARCHAR(100) NOT NULL DEFAULT 'employed' ); OR CREATE TABLE employees( id INT AUTO_INCREMENT NOT NULL, last_name VARCHAR(100) NOT NULL, first_name VARCHAR(100) NOT NULL, middle_name VARCHAR(100), age INT NOT NULL, current_status VARCHAR(100) NOT NULL DEFAULT 'employed', PRIMARY KEY(id) );