Section 3 - Creating Databases and Tables

Question 1

Can you have more than one database on the same database server? (YES / NO)

Answer 1

YES

Question 2

What is the naming convention for when and when not to use CAPITAL letters?

Answer 2

• Use UPPERCASE for SQL commands
• Use lowercase for custom names

For example, “CREATE DATABASE augiesdatabase”

Question 3

What is the command for deleting a database?

Answer 3

DROP DATABASE ;

Question 4

Write a command for deleting a database named useless_database.

Answer 4

DROP DATABASE useless_database;

Question 5

Broadly-speaking, what is a database composed of? (At least, a what is a relational database composed of?)

Answer 5

A bunch of tables.

These tables contain data.

Question 6

What is a table?

Answer 6

A table holds the data.

Specifically, a table is a collection of related data held in a structured format within a database.

Question 7

A database is structured via ________s and ________s.

Answer 7

A database is structured via COLUMNs and ROWs.

Question 8

What is a column in a database?

Answer 8

Think of a column like a “Header” or category.

For example, first_name, age, last_name, major, relatives, hobbies might all be columns.

Question 9

What is a ROW in a table composed of?

Answer 9

A ROW contains the actual data in the tables.

For example, if we have a column named, “first_name”, the corresponding row might contain data like, “Adam”, “Adrian”, “Penelope”, “Vivian”, “Gina”, “Cooper”, etc.

Question 10

Most Databases are Composed of Lots and Lots of Tables: TRUE or FALSE

Answer 10

TRUE

Question 11

What is a Data Type?

Give two examples.

Answer 11

A data type is a structured data format by which we can input/aggregate similar data.

For example, VARCHAR is a text data type, whereas INT (integer) is a number data type.

If we had a column of people’s ages, we would definitely want this to be an INT data type, because age is never (or very rarely should be) a text field.

Question 12

Give one example where data type could be helpful.

Answer 12

You have an age column for keeping track of your employees health insurance.

Keeping the column as an INT is helpful. If you could type any type of text, it’d be much more difficult to read and manipulate the data.

For example, people could list their age as “Twenty-seven”, “Twenty seven”, “27”, “twenty 7” or whatever else if you did not restrict your data type to INT.

As you can see, that would get confusing real fast!

Question 13

What is a STRING data type?

Answer 13

STRING data types are data types involving letters/text. (As opposed to Numeric data types, which involve numbers.)

Question 14

What is a NUMERIC data type?

Answer 14

NUMERIC data types involve numbers. (As opposed to text / letters.)

Question 15

WHAT IS a DATE data type?

Answer 15

A DATE data type involves various DATE/Calendar/day of year formats. This is different and more specific than the numerica

Question 16

How many of the data types will you use? Should you memorize them?

Answer 16

You’ll likely only need to use a small subset of the huge variety of datatypes. DON’T memorize them.

Question 17

What type of data is a VARCHAR?

Answer 17

VARCHAR is a text data type. The VAR part of VARCHAR means that it a variable length. (As opposed to a set length.)

Question 18

What does VARCHAR stand for?

Answer 18

VARCHAR stands for Variable Character, which means that the character length is variable.
This means you can input character “strings” of different lengths.

Question 19

What is the basic syntax for creating a table?

Answer 19

CREATE TABLE tablename
(
column_name DATA_TYPE,
column_name DATA_TYPE
)

Question 20

Write syntax for the following:
      - Create a table
      - Name the table my_first_table
      - Create a text column named 
        column_1, which should be variable 
        characters up to 100 characters in 
        length.
     - Create an integer column named 
       column_2

Answer 20

CREATE TABLE my_first_table
          (
                    column_1 VARCHAR(100),
                    column_2 INT
           );

Question 21

Naming Conventions:

Should table names be singular, plural, or either?

Answer 21

PLURAL

Question 22

What is the command for selecting and working out of a particular database?

Answer 22

USE ;

Question 23

What is the USE command used for?

Answer 23

It is USEd for selecting and working within a particular database.

Question 24

What is the command for displaying the names of all the tables in the currently-selected database?

Answer 24

SHOW TABLES;

Question 25

What does the SHOW TABLES command do?

Answer 25

This returns the names of all the tables in the associated database.

Question 26

What is the command for displaying all the columns in a given table?

Answer 26

SHOW COLUMNS FROM ;

Question 27

What does the SHOW COLUMNS command do?

Answer 27

This displays all the columns within a given table.

Question 28

Write a command for displaying all the columns from a table name named bora_bora.

Answer 28

SHOW COLUMNS FROM bora_bora;

Question 29

What does the DESC command do?

Answer 29

This returns the details of all the columns within a table.

Question 30

What does the DROP TABLE command do?

Answer 30

This deletes the table and it’s contents.

Question 31

What is the command for deleting a table and its contents?

Answer 31

DROP TABLE ;

Question 32

Write a command for deleting a table named sodas.

Answer 32

DROP TABLE sodas;

Question 33

If you created a table, then dropped it (deleted it), then wrote a command to DESC the table, what would happen? Why?

Answer 33

• You would return an error.

- Why? Because the table no longer exists.

Question 34

Exercise:

• Create a pastries table.
• It should have two columns, one called name, one called quantity.
• Name should have 50 characters max.
• Quantity should be an integer.

Answer 34

CREATE TABLE pastries(
name VARCHAR(50),
quantity INT
)