My Addition Flashcards
1
Q
In refernce of ALTER TABLE statements
- What is the syntax for
ADD?
2 As in add column ColumnName.
- As in include
DataType
A
ALTER TABLE TableName ADD ColumnName DataType;
2
Q
In refernce of ALTER TABLE statements
- What is the syntax for
CHANGE? - As in change a column
CurrentColumnNametoNewColumnName.
A
ALTER TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType;
3
Q
In refernce of ALTER TABLE statements
- What is the syntax for
DROP? - As in
DROP ColumnName
A
ALTER TABLE TableName DROP ColumnName;
4
Q
What is the definition of tables in relational databases?
A
Tables are the core structure in relational databases containing a fixed sequence of columns and a varying set of rows.
Each column has a name and a specific data type, while each row consists of values that correspond to the column’s data types.
5
Q
What are the components of a table?
A
- A table must have at least one column and can have multiple rows.
- A table can also be empty (having no rows).
Each column has a name and a specific data type.
6
Q
How do databases handle duplicate rows?
A
Some databases may allow duplicate rows temporarily, particularly when loading external data into a temporary table.
7
Q
What SQL statement is used to define a new table?
A
CREATE TABLE
This statement specifies table names and data types.
8
Q
What does the DROP TABLE statement do?
A
Deletes a table and all its data.
9
Q
What is the purpose of the ALTER TABLE statement?
A
Used to modify an existing table by adding, changing, or deleting columns.
10
Q
List common data types used in tables.
A
- integers (INT)
- values with 0 to N characters (VARCHAR(N))
- dates (DATE)
- decimals (DECIMAL)
11
Q
What rule governs the values in a cell of a table?
A
Exactly one value per cell.
12
Q
Are duplicate column names allowed in a table?
A
No; duplicate column names are not allowed within the same table but are allowed in different tables.
13
Q
What principle allows for optimization of storage in tables?
A
No significant row order
supporting the principle of data independence.
14
Q
What does it mean that there can be no duplicate rows in a table?
A
No two rows in a table may have identical values across all columns.
15
Q
What is a NULL value in a table?
A
Represents the absence of data in a cell.
16
Q
- What is the SQL syntax for creating a table
TableName? - As in with the
Column$&Data_Type.
A
CREATE TABLE TableName (Column1 DATA_TYPE, Column2 DATA_TYPE, ..., ColumnN DATA_TYPE);
17
Q
Fill in the blank: The ID column is of data type _______.
A
Integer (INT)
18
Q
What data type is used for the Name column with a max 40 characters?
A
Variable-length string with maximum 40 characters (VARCHAR(40)).
19
Q
What is the appropriate data type for the ProductType column?
A
Variable-length string with maximum 3 characters (VARCHAR(3)).
20
Q
What data type is used to store dates in a table?
A
Date
21
Q
What is the format for the Weight column data type?
A
Decimal number with six significant digits and one digit after the decimal point (DECIMAL(6,1)).
22
Q
True or False: A table can have multiple rows with identical data.
A
False.
23
Q
Why are integers commonly used for IDs in databases?
A
They allow for efficient storage and quick retrieval when querying for specific products.
24
Q
What is the advantage of using VARCHAR for the Name column?
A
It allows for variable-length strings, using only the necessary space for each entry.
25
Explain the data type `DECIMAL(6,1)`.
It can accurately represent decimal numbers with *up to 6 total digits*, of which 1 digit is after the decimal point.
26
What is a data type in a database?
A data type is a **named set** of values from which *column values in a database* are derived.
27
What are the categories of [data types](https://g.co/gemini/share/e078b1973c4e)?
* Integer Data Types
* Decimal Data Types
* Character Data Types
* Date and Time Data Types
* Binary Data Types
* Spatial Data Types
* Document Data Types
* Specialized Data Types
28
What do Integer Data Types represent?
Whole numbers, including positive and negative values.
29
* INT (4 bytes): A standard integer, storing a wide range of whole numbers (e.g., -2 billion to +2 billion).
* SMALLINT (2 bytes): A smaller integer with a more limited range (e.g., -32,768 to +32,767).
## Footnote
These are examples of?
These are examples of Integer Data Types
30
What are Decimal Data Types used for?
Numbers that require a fractional component.
31
* FLOAT
* DECIMAL
Are examples of which Data Type?
* FLOAT
* DECIMAL
These are examples of `Decimal` types
32
What do Character Data Types store?
Text strings.
33
What is the difference between `CHAR` and `VARCHAR`?
* `CHAR` is fixed-length
* `VARCHAR` is variable-length
34
What do `Date` and `Time` Data Types store?
Dates, times, or both.
35
What are examples of `Date` and `Time` Data Types?
* DATE
* TIME
* DATETIME
* TIMESTAMP
36
What do Binary Data Types store?
Raw binary data.
37
What are some examples of Binary Data Types?
* BLOB
* BINARY
* VARBINARY
38
What is the purpose of Spatial Data Types?
To manage **geometric data** like points and polygons.
39
What do Document Data Types include?
Structured textual data formats
## Footnote
XML and JSON.
40
These examples correlate to which Specialized Data Types?
* MONEY
* BOOLEAN
* ENUM
* MONEY for currency
* BOOLEAN for true/false values
* ENUM for *pre-defined* sets of values
## Footnote
[Learn more](https://share.evernote.com/note/e2dd2f85-9ec0-02a3-20e8-7a93d7f76e41)
41
What is the recommended practice for using MySQL Data Types?
Use the *smallest type sufficient for the required range* to improve storage efficiency.
42
What is the storage requirement for `TINYINT`?
1 byte
43
What is the signed range for `SMALLINT`?
-32,768 to 32,767
## Footnote
Unsigned 0 to 65,535
44
What is the unsigned range for `MEDIUMINT`?
## Footnote
[📖](https://share.evernote.com/note/a912bcea-dcb9-b40f-512e-e2c75e380419)
0 to 16,777,215
## Footnote
Signed range -8,388,608 to 8,388,607
45
What is the storage requirement for `INT` (INTEGER)?
4 bytes
46
What is the approximate range for `FLOAT`?
-3.4E+38 to 3.4E+38
47
What is the format for `DATE` data type?
YYYY-MM-DD
48
What is the storage requirement for `VARCHAR(N)`?
Length of stored **string** + 1 byte for length information (up to 65,535 characters).
49
What is the correct data type for storing a city’s population?
1. unsigned `INTEGER`
2. Used for values that are always positive (like total population).
50
What is the **correct data type** for storing the *annual gain or loss* in a city’s population?
1. signed `MEDIUMINT`
2. This data type can store integers between -8,388,608 and 8,388,607, which is sufficient for storing annual population changes in most cities.
51
What is the correct data type for storing the *price of an item* ranging from a few dollars to a few hundred dollars?
## Footnote
[Notes](https://share.evernote.com/note/a912bcea-dcb9-b40f-512e-e2c75e380419)
`DECIMAL(5,2)`
* $12.34 (Fits easily)
* $999.99 (This is the biggest number it can hold)
* $0.05 (Small numbers are fine too)
* $123.40 (It can even store numbers with trailing zeros)
## Footnote
Here are some prices that would fit within DECIMAL(5,2)
52
What is the correct data type for storing the *date and time* an item is purchased?
`DATETIME`
53
What is the correct data type for storing a student’s assigned letter grade?
`CHAR(1)`
54
What is the correct data type for storing a student’s email address?
VARCHAR(100)
55
What is the correct data type for storing a yes or no value?
`TINYINT`
56
All relational databases support which data types?
1. integer
2. decimal
3. date and time
4. character data types.
57
True or False: An unsigned number can be negative.
False
58
To make all the rows and columns appear in the result table?
`SELECT *`
59
How would you selects only columns `CountryCode` and `Language`, so only two columns appear in the result table.
```
SELECT CountryCode, Language
FROM CountryLanguage;
```
60
# Refer to the following table.
Select all rows and only the Name and District columns.
```
SELECT Name, District
FROM City;
```
61
# Refer to the following table.
Select all rows and columns.
```
SELECT *
FROM City;
```
62
# Refer to the following table.
Select all rows and columns except ID in order of columns shown.
```
SELECT Name, CountryCode, District, Population
FROM City;
```
63
# Refer to the following table
What values are returned?
```
SELECT Balance + Payment
FROM Customer;
```
1. 2400, 5250, 310, 1075
2. Each customer's balance is added to the payment.
64
# Refer to the following table
What values are returned?
```
SELECT 2 * (Balance - Payment)
FROM Customer;
```
1. 3600, 9500, 580, 1650
2. parentheses indicate subtraction is performed before multiplication
65
# Long Tables
```
SELECT *
FROM City
LIMIT 100;
```
## Footnote
This indicates what?
The `SELECT` statement returns only the first 100 rows from the `City` table.
66
# Refer to the following table
```
SELECT CountryCode, Language
FROM CountryLanguage
WHERE Percentage > 0.0
AND Percentage < 10.0;
```
## Footnote
What would this outcome be?
1. Two rows are returned.
2. The statement selects rows with percentage between 0.0 and 10.0.
67
# Refer to the following table
```
SELECT CountryCode, Language
FROM CountryLanguage
WHERE Percentage < 5.0
OR Percentage > 90.0;
```
## Footnote
What would this outcome be?
1. Two rows are returned.
2. The statement selects rows with percentage < 5.0 or percentage > 90.0.
68
A NULL in the BirthDate column means
"unknown", since all employees have a birth date
## Footnote
NULL either unknown or inapplicable data
69
If Engineering employees are not paid a bonus what does this mean for LISA?
Lisa Ellison's `NULL` bonus means "inapplicable".
## Footnote
NULL either unknown or inapplicable data
70
# Not NULL constraint | Refer to the statement
```
CREATE TABLE Department (
Code TINYINT UNSIGNED NOT NULL,
Name VARCHAR(20),
ManagerID SMALLINT
);
```
## Footnote
Which columns may contain NULL values?
```
Name and Manager ID
```
## Footnote
The Name and ManagerID columns may contain NULL values, by default.
71
# Not NULL constraint | Refer to the statement
```
CREATE TABLE Department (
Code TINYINT UNSIGNED NOT NULL,
Name VARCHAR(20),
ManagerID SMALLINT
);
```
## Footnote
Which alteration to the `CREATE TABLE` statement prevents `ManagerID` from being `NULL`?
```
CREATE TABLE Department (
Code TINYINT UNSIGNED NOT NULL,
Name VARCHAR(20),
ManagerID SMALLINT NOT NULL
);
```
## Footnote
The `NOT NULL` constraint must be listed after the column name and _data type_.
72
Imagine you have a database table named `Departments` with a column called `Code`.
This `Code` column is set up so that it cannot be empty (it's a required field).
What would happen if someone tries to add a new department to the table but forgets to enter a value for the `Code`?
1. Since Code is `NOT NULL`, the database does not accept an insert without a Code value.
2. The insert statement fails.
73
# NULL arithmetic and comparisons.
```
SELECT Name
FROM Compensation
WHERE (Salary + Bonus) > 30000;
```
## Footnote
What are the results?
1. The `SELECT` statement does not select a name from a row when the `WHERE` clause is `NULL`, so Lisa Ellison is not selected.
2. When arithmetic or comparison operators have one or more `NULL` operands, the result is `NULL`.
3. When a `WHERE` clause evaluates to `NULL` for values in a row, the row is not selected.
## Footnote
Arithmetic: + (addition), - (subtraction), * (multiplication), / (division), % (modulo), ^ (exponentiation)
74
# Refer to the table
What name is selected?
```
SELECT Name
FROM Compensation
WHERE Salary = Bonus;
```
1. `Sam Snead`
2. Jiho Chen is not selected because NULL = NULL is NULL
75
# Refer to the table
What is the name selected?
```
SELECT Name
FROM Compensation
WHERE (Salary / Bonus) < 1.0;
```
1. `Maria Rodriguez`
2. The / and < operators return `NULL` when either operand is `NULL`.
3. As a result, Lisa Ellison and Jiho Chen are not selected.
76
What does NULL represent in databases?
NULL is a special marker used to indicate that a data value does not exist in the database, signifying unknown or inapplicable data.
77
How does `NULL` differ from **zero** and **blank** entries?
`NULL` is distinct from zero (a known value) and blank entries (which represent empty strings in character data types).
78
What is the `NOT NULL` constraint in SQL?
The `NOT NULL` constraint ensures that a column cannot have `NULL` values, commonly used for mandatory fields.
79
What happens during arithmetic or comparisons involving `NULL`?
The result will also be `NULL`, and the `IS NULL` or `IS NOT NULL` operators *must be used to check* for `NULL`.
80
What SQL command is used to add a `NOT NULL` constraint to a column?
```
ALTER TABLE table_name
MODIFY column_name data_type NOT NULL.
```
81
What is the outcome of violating a `NOT NULL` constraint during data insertion?
It results in an error, preventing the insertion of the row.
82
How can you enforce `NOT NULL` constraints when creating a table?
By appending `NOT NULL` right after *the column’s data type* in the `CREATE TABLE` statement.
83
1. If **logic** refers to the set of rules and constraints that govern how data is stored, accessed, and manipulated.
2. What does `NULL` logic refer to in databases?
`NULL` logic refers to how *logical expressions are handled* in the presence of `NULL` values, indicating uncertainty in truth values.
84
How do you select rows with `NULL` values in SQL?
Use the `IS NULL` operator in your query.
`SELECT *
FROM table_name
WHERE column_name IS NULL;`
85
What is the SQL command to find all records where a column has a `NULL` value?
```
SELECT *
FROM table_name
WHERE column_name IS NULL;
```
86
How is `NULL` represented internally in MySQL?
`NULL` is represented internally where `FALSE` is `0` and `TRUE` is `1`.
87
What is the result of a logical expression involving `NULL`?
The expression may evaluate to `NULL`, indicating uncertainty.
88
What SQL command can be used to find rows where a specific column does not contain `NULL` values?
```
SELECT *
FROM table_name
WHERE column_name IS NOT NULL;
```
89
# Refer to the table definition
Whats 1,2,3
1. Table Name
2. Columns
3. Column Values
90
# Refer to the table | Insert Statements
The first code is
```
INSERT INTO Employee (ID, Name, Salary)
VALUES (2538, 'Lisa Ellison', 45000);
```
## Footnote
How do you insert the second row?
```
INSERT INTO Employee
VALUES (5384, 'Sam Snead', 30500)
```
91
# Refer to the table definition
```
CREATE TABLE Department (
Code TINYINT UNSIGNED,
Name VARCHAR(20),
ManagerID SMALLINT UNSIGNED
);
INSERT INTO Department (Code, Name, ManagerID)
VALUES (44, 'Engineering', 2538);
```
## Footnote
Whats the **newest** `INSERT INTRO` & `VALUES`
* Tablename `'Department'`
* Columns `Code, Name, ManagerID`
* Values `44, 'Engineering', 2538`
## Footnote
When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.
92
# Refer to the table definition
```
CREATE TABLE Department (
Code TINYINT UNSIGNED,
Name VARCHAR(20),
ManagerID SMALLINT UNSIGNED
);
INSERT INTO Department (Code, Name, ManagerID)
VALUES (44, 'Engineering', 2538);
INSERT INTO Department (Code, Name)
VALUES (99, 'Technical Support');
```
## Footnote
Whats the **newest** `INSERT INTRO` & `VALUES`
* Tablename `'Department'`
* Columns `Code, Name, M̶a̶n̶a̶g̶e̶r̶I̶D̶`
* Values `99, 'Engineering'`
## Footnote
When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.
93
# Refer to the table definition
```
CREATE TABLE Department (
Code TINYINT UNSIGNED,
Name VARCHAR(20),
ManagerID SMALLINT UNSIGNED
);
INSERT INTO Department (Code, Name, ManagerID)
VALUES (44, 'Engineering', 2538);
INSERT INTO Department
VALUES (82, 'Sales', 6381)
```
## Footnote
Whats the **newest** `INSERT INTO` & `VALUES`
* Tablename `'Department'`
*̶ C̶o̶l̶u̶m̶n̶s̶ `̶C̶o̶d̶e̶, N̶a̶m̶e̶, M̶a̶n̶a̶g̶e̶r̶I̶D̶`̶
* Values `82, 'Sales', 6381`
## Footnote
When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.
94
# What does the `INSERT` syntax represent?
```
INSERT [INTO] TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);
```
1. The `INSERT INTO` clause **names** the table and columns involved.
2. The `VALUES` clause names the column values.
95
The `Department` table's `Manager` column is a $-key that refers to the $-key `ID` in the `Employee` table.
The `Department` table's `Manager` column is a foreign key that refers to the primary key `ID` in the `Employee` table.
96
The `Department` table's `Manager` column is a foreign key. How do we know that?
An empty circle (○) precedes **foreign keys** in table diagrams, and an arrow leads to the referenced **primary key** indicated with a (●)
97
What does *bidirectional* mean in relationships?
1. In database relationships, "bidirectional" means *both tables are aware* of each other.
2. They each have a *foreign key referencing the other's primary key*, allowing you to easily navigate between them and access related data from either side
98
# Match the SQL sublanguage to the statement behavior.
`INSERT` a data row into table `Product`.
* Data Manipulation Language
* DML *inserts, updates, and deletes* data in a table.
## Footnote
INSERT is a DML statement that inserts data into a table.
99
# Match the SQL sublanguage to the statement behavior.
Rollback database changes.
* Data Transaction language
* DTL commits data to a database, rolls back data from a database, and creates savepoints.
## Footnote
COMMIT is a DTL statement that saves a transaction to the database.
100
# Match the SQL sublanguage to the statement behavior.
`SELECT` all rows from table `Product`.
* Data Query Language
* (DQL) retrieves data from the database.
## Footnote
SELECT is a DQL statement that retrieves data from a table.
101
# Match the SQL sublanguage to the statement behavior.
Grant all permissions to user 'tester'.
* Data Control Language
* DCL grants and revokes permissions to and from users.
## Footnote
GRANT is a DCL statement used to give permissions to users.
102
# Match the SQL sublanguage to the statement behavior.
Create table Product.
* Data Definition Language
* DDL creates, alters, and drops tables.
## Footnote
CREATE is a DDL statement that creates a table.
103
How could you extract the time from the Due column in the Assignment table where ID is 2?
```
SELECT TIME(Due)
FROM Assignment
WHERE ID = 2;
```
## Footnote
TIME() extracts the time from '2019-11-02 23:59:00'.
104
How could you extract the day from the Due column in the Assignment table where ID is 4?
```
SELECT DAY(Due)
FROM Assignment
WHERE ID = 4;
```
## Footnote
DAY() extracts the day from '2019-11-14 08:00:00'.
105
For the Assignment with ID 2, how can I combine the 'hour' and 'minute' values from the 'Assigned' time to get a single number?
```
SELECT HOUR(Assigned) + MINUTE(Assigned)
FROM Assignment
WHERE ID = 2;
```
## Footnote
HOUR() extracts the hour from '2019-11-02 12:30:00' and MINUTE() extracts the minute. Thus, 12 + 30 = 42.
106
How could you calculate the number of days between Due and Assigned for the Assignment with ID 1?
```
SELECT DATEDIFF(Due, Assigned)
FROM Assignment
WHERE ID = 1;
```
## Footnote
DATEDIFF() calculates the number of days from '2019-11-01 08:00:00' to '2019-11-02 08:00:00', which is 1 day.
107
How could you find the time difference between Due and Assigned for the Assignment with ID 3?
```
SELECT TIMEDIFF(Due, Assigned)
FROM Assignment
WHERE ID = 3;
```
## Footnote
TIMEDIFF() calculates the time difference between '2019-11-05 11:15:00' and '2019-11-05 10:15:00', which is just 1 hour.
108
How would you concatenate 'Super ' with the `Name` of the avatar where `ID` is 1?
```
SELECT CONCAT('Super ', Name)
FROM Avatar
WHERE ID = 1;
```
## Footnote
This will return "Super Link".
109
How would you convert the `BestMove` of the avatar where `ID` is 3 to lowercase?
```
SELECT LOWER(BestMove)
FROM Avatar
WHERE ID = 3;
```
## Footnote
This will return "psystrike".
110
How would you extract a substring from the `BestMove` of the avatar where `ID` is 4, starting from position 7 and with a length of 6?
```
SELECT SUBSTRING(BestMove, 7, 6)
FROM Avatar
WHERE ID = 4;
```
## Footnote
This will return "Finale".
111
How would you replace 'Kn' with 'Fr' in the `Name` of the avatar where `ID` is 2?
```
SELECT REPLACE(Name, 'Kn', 'Fr')
FROM Avatar
WHERE ID = 2;
```
## Footnote
This will return "Meta Fright".
112
How would you select the `ID` and `Name` for all avatars where `BestMove` contains 'Final'?
```
SELECT ID, Name
FROM Avatar
WHERE BestMove LIKE '%Final%';
```
## Footnote
This will return the ID and Name of any avatar with "Final" in their BestMove.
113
What SQL statement retrieves the make of automobiles and the count of each make, ordered by make?
```
SELECT Make, COUNT(*)
FROM Auto
GROUP BY Make
ORDER BY Make;
```
## Footnote
This query counts and groups the automobiles by their make, then orders the results by make.
114
How can you get the average price of automobiles for each year, ordered by year?
```
SELECT Year, AVG(Price)
FROM Auto
GROUP BY Year
ORDER BY Year;
```
## Footnote
This query calculates the average price of cars grouped by year and orders the results by year.
115
Which SQL statement finds the maximum price of automobiles grouped by type, ordered by maximum price?
```
SELECT Type, MAX(Price)
FROM Auto
GROUP BY Type
ORDER BY MAX(Price);
```
## Footnote
This query retrieves the highest price for each type of automobile and orders the results by maximum price.
116
How would you display the maximum price of automobiles for each year and type, ordered by year and maximum price?
```
SELECT Year, Type, MAX(Price)
FROM Auto
GROUP BY Year, Type
ORDER BY Year, MAX(Price);
```
## Footnote
This query retrieves the maximum price of automobiles for each year and type, ordered by year and maximum price.
