Basic terminology

Question 1

What is the Unique Identifier in this table.
What is another name for a Unique Identifier

Answer 1

ID is one of 5 fields (collums are fields rows are records)
ID is the Unique Identifier.
Unique Identifier is often called the KEY

Question 2

Out of the Following what are correct and incorrect suggestions as to how to make the table better:
- Customers Field should be renamed
-Underscores in field names should be replaced with spaces
- The table name should be made singular
- The field names should be made singular
- The table name should not be Capital
- The field names should be made capital

Answer 2

Good improvements:
- Customers Field should be renamed
- The field names should be made singular
- The table name should not be Capital

Incorrect changes
- Underscores in field names should be replaced with spaces
- The table name should be made singular
- The field names should be made capital

Question 3

What is the data type for
words/Strings:
years, whole numbers etc:
fractions floats 2.05 etc:

Answer 3

Strings: VARCHAR
Integers: INT
floats: NUMERIC

Question 4

what does a Schema show

Answer 4

the Database design
- what tables are included
- any relationships between the tables
- what data type each field can hold

Question 5

What is the best way to store each of the data:
Phone number (321-123-321)
model year (1999)
product review written
number of subscribers (9020)
Weight in Tonnes (5.45)
Price in dollars ($4.50)

Answer 5

Phone number (321-123-321): VARCHAR
model year (1999): INT
product review written: VARCHAR
number of subscribers (9020): INT
Weight in Tonnes (5.45): NUMERIC
Price in dollars ($4.50): NUMERIC

Question 6

What are “Key Words”
and what are the 2 most common

Answer 6

reserved words for operations
SELECT
FROM

Question 7

What do the
SELECT and FROM keywords do?

Answer 7

SELECT: indicates which FIELDS should be selected
FROM: indicates from which TABLE these fields should be located

Question 8

How would you select the name filed from the patrons table?

How would you select all fields from the patrons table?

Answer 8

SELECT name
FROM patron ;

SELECT *
FROM patron;

Question 9

List the things wrong with this Query.

Answer 9

• make CARD_NUM and TOTAL_FINE lower case
• add an ; at the end of the query
• capitalise FROM

SELECT card_num, total_fine
FROM patrons;

Question 10

how do you change a field (column) header when you query?
what is this called?

use a table with fields, name (return as first_name) and year_hired from a Table employees

Answer 10

Aliasing AS:
SELECT name AS first_name, year_hired
FROM employees;

changes the header of name to first_name

Question 11

How can you remove doubled up information (list of unique values).
example: years hired may have multiple values of 2021 how can we group them?

Answer 11

DISTINCT
SELECT DISTINCT year_hired
FROM employees;

Question 12

write a query to look at the different years that departments hired employees.

Answer 12

Question 13

What is a View:

Answer 13

• Considered Virtual tables
• Data contained is not stored in the database
• It is query code stored for future use
• when the view is accessed it updates the query results to account for any updates to the underlying data base

Question 14

create a view with the name; employee hire years. With fields; id, name, year_hired. From the table; employees.

Answer 14

CREATE VIEW employee_hire_years AS
SELECT id, name, year_hired
FROM employees;