Section 4 - Inserting Data

Question 1

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

Answer 1

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

Question 2

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

Answer 2

INSERT

Question 3

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

Answer 3

YES

Question 4

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

Answer 4

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.

Question 5

Create a table called employees.

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

Answer 5

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

Question 6

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

Eg, how do we check out data?

Answer 6

Use the SELECT command.

Question 7

What does the SELECT command do?

Answer 7

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

Question 8

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

Answer 8

SELECT * FROM sports_teams;

Question 9

What would this command do?

SELECT * FROM world_record_holders;

Answer 9

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

Question 10

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

Answer 10

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.

Question 11

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 .

Answer 11

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

Question 12

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

Answer 12

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

Question 13

How can you use quotes inside of an inserted value?

Answer 13

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

Question 14

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

Answer 14

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.

Question 15

What is the command for seeing Warnings?

Answer 15

SHOW WARNINGS;

Question 16

What does the SHOW WARNINGS command do?

Answer 16

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

Question 17

Can we SHOW WARNINGS anytime?

Answer 17

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

Question 18

What does NULL mean?

Answer 18

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

Question 19

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

Answer 19

It is NULL.

Question 20

TRUE or FALSE:

NULL means the value equals zero.

Answer 20

FALSE.

This is false false false false false. VERY FALSE!

Question 21

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

Answer 21

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

Question 22

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

Answer 22

NOT NULL

Question 23

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

Answer 23

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

Question 24

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

Answer 24

When you setup the table initially.

Question 25

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

Answer 25

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

Question 26

What is a default value?

Answer 26

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.

Question 27

Why are default values helpful?

Answer 27

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.

Question 28

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)

Answer 28

YES

Question 29

How do you set a default value?

Answer 29

Use the DEFAULT command when creating a table.

Question 30

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

Answer 30

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

Question 31

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

Answer 31

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.

Question 32

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

Answer 32

PRIMARY KEY

Question 33

What is a PRIMARY KEY?

Answer 33

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

Question 34

Create a table called dogs with a row called dog_id.

Write the 2 ways you can syntactically make dog_id a PRIMARY KEY.

Answer 34

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)
           );

Question 35

Should PRIMARY KEYs be NOT NULL?

Answer 35

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.

Question 36

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

Answer 36

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

Question 37

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

Answer 37

AUTO_INCREMENT

Question 38

What does the AUTO_INCREMENT command do?

Answer 38

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

Question 39

What’s something that using AUTO_INCREMENT solves?

Answer 39

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.

Question 40

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’

Answer 40

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)
);