SQL Module #58

Question 1

What does SQL mean?

Answer 1

Structured Query Language

Question 2

What command is used in SQL in order to create a table?

Answer 2

CREATE TABLE

Question 3

What other information is needed when creating a table?

Answer 3

Table names and the data types that they will hold.

Question 4

What are some of the most common data types you’ll see in an SQL table?

Answer 4

• CHAR
• TEXT
• VARCHAR
• DATE
• TIME
• DATETIME
• TIMESTAMP
---------------------------------------------------
Numeric Types include:
• TINYINT 1 byte (0-255)
• INT 4 bytes
• BIGINT 8 bytes
• SMALLINT 2 bytes
• DECIMAL
• FLOAT

Question 5

What does the syntax look like for a table with 2 columns (1 integer, 1 variable string lenght)?

Answer 5

CREATE TABLE people (
age INT,
name CHAR(20)
);

Question 6

Once the table is created, how are values added to it?

Answer 6

INSERT INTO people VALUES (37, ‘Flavio’)

Question 7

How are multiple values specified?

Answer 7

By separating the values with a comma. Example:

37, ‘Flavio’ ), (8, ‘Roger’

Question 8

How is data added into a table?

Answer 8

Use the INSERT INTO command, example:

INSERT INTO people VALUES ( 37, ‘Flavio’ ), (8, ‘Roger’)

Question 9

How can you query data from SQL?

Answer 9

By using the SELECT command.

You can narrow the query by selecting a single column:

SELECT name FROM people; OR
SELECT COUNT(*) from people;

Question 10

How are rows in a table filtered in SQL?

Answer 10

By adding the WHERE clause:

SELECT age FROM people WHERE name=’Flavio’;

Question 11

When data is returned from a query, how can we adjust the order of the data? For example descending to acending?

Answer 11

By using the ORDER BY clause:

SELECT * FROM people ORDER BY name DESC;

or order by name:

SELECT * FROM people ORDER BY name;

Question 12

Are empty values (empty cells) acceptable in SQL?

Answer 12

Yes, and they are represented as NULL, example:

INSERT INTO people VALUES (null, null); in this instance both columns have an empty cell or NULL value.

Question 13

If a an entire row has a value of null, that can be problematic. How can we solve for that?

Answer 13

Declare constraints on the table preventing null values. Essentially write a rule, that forbids it. Example: Use NOT NULL

CREATE TABLE people (
age INT NOT NULL,
name CHAR(20) NOT NULL
);

Question 14

Sometimes data contains a lot of repeated values. Say we didn’t want that to happen, what is the solution?

Answer 14

By using the UNIQUE key constraint. Example:

CREATE TABLE people (
age INT NOT NULL,
name CHAR(20) NOT NULL UNIQUE
);

Question 15

What would happen if you used the UNIQUE key constraint and then tried to add duplicate values to the table?

Answer 15

You’d get an error:

ERROR: duplicate key value violates unique constraint “people_name_key”
DETAIL: Key (name)=(Flavio) already exists.

Question 16

Say you wanted to find a specific row. How are rows identified in a table?

Answer 16

By identifying the primary key for that row. Primary keys are unique ID’s assigned to rows automatically. For example, the email address for a list of users. Whatever the value is, that’s how to reference rows.

Question 17

How is data updated in an SQL table?

Answer 17

Buy invoking the UPDATE command. Here’s an example:

UPDATE people SET age=2 WHERE name=’Roger’

Question 18

When updating values in a table/DB how is the location of the update specified?

Answer 18

By using the WHERE clause. Example:

UPDATE people SET age=2 WHERE name=’Roger’

Question 19

What if the WHERE clause isn’t specified in your update?

Answer 19

ALL tables get the update. So be careful.

Question 20

What if it was necessary to alter the table structure? For example, adding a column.

Answer 20

Use the ALTER TABLE command followed by the alteration that you want to make?

ALTER TABLE people ADD COLUMN born_year INT;

Question 21

How can we remove a column from the table?

Answer 21

Again using the ALTER TABLE command but with DROP COLUMN.

ALTER TABLE people DROP COLUMN born_yea

Question 22

What is the syntax for deleting data?

Answer 22

Use the DELETE FROM command, in order to delete all of the rows from a table.

But, you can use the WHERE clause to specify the data that you want to remove. Example:

DELETE FROM people WHERE name=’Flavio’;

Question 23

How are entire tables deleted?

Answer 23

By using the DROP TABLE command. Example:

DROP TABLE people;

Question 24

What is the concept behind “Joins” ?

Answer 24

Thay allow for adding a relationship between data sets. The way that’s achieved is through relational algebra. And the syntax looks like this:

SELECT age FROM people JOIN cars ON people.name = cars.owner WHERE cars.model=’Mustang’;