My Addition Flashcards
In refernce of ALTER TABLE statements
- What is the syntax for
ADD?
2 As in add column ColumnName.
- As in include
DataType
ALTER TABLE TableName ADD ColumnName DataType;
In refernce of ALTER TABLE statements
- What is the syntax for
CHANGE? - As in change a column
CurrentColumnNametoNewColumnName.
ALTER TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType;
In refernce of ALTER TABLE statements
- What is the syntax for
DROP? - As in
DROP ColumnName
ALTER TABLE TableName DROP ColumnName;
What is the definition of tables in relational databases?
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.
What are the components of a table?
- 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.
How do databases handle duplicate rows?
Some databases may allow duplicate rows temporarily, particularly when loading external data into a temporary table.
What SQL statement is used to define a new table?
CREATE TABLE
This statement specifies table names and data types.
What does the DROP TABLE statement do?
Deletes a table and all its data.
What is the purpose of the ALTER TABLE statement?
Used to modify an existing table by adding, changing, or deleting columns.
List common data types used in tables.
- integers (INT)
- values with 0 to N characters (VARCHAR(N))
- dates (DATE)
- decimals (DECIMAL)
What rule governs the values in a cell of a table?
Exactly one value per cell.
Are duplicate column names allowed in a table?
No; duplicate column names are not allowed within the same table but are allowed in different tables.
What principle allows for optimization of storage in tables?
No significant row order
supporting the principle of data independence.
What does it mean that there can be no duplicate rows in a table?
No two rows in a table may have identical values across all columns.
What is a NULL value in a table?
Represents the absence of data in a cell.
- What is the SQL syntax for creating a table
TableName? - As in with the
Column$&Data_Type.
CREATE TABLE TableName (Column1 DATA_TYPE, Column2 DATA_TYPE, ..., ColumnN DATA_TYPE);
Fill in the blank: The ID column is of data type _______.
Integer (INT)
What data type is used for the Name column with a max 40 characters?
Variable-length string with maximum 40 characters (VARCHAR(40)).
What is the appropriate data type for the ProductType column?
Variable-length string with maximum 3 characters (VARCHAR(3)).
What data type is used to store dates in a table?
Date
What is the format for the Weight column data type?
Decimal number with six significant digits and one digit after the decimal point (DECIMAL(6,1)).
True or False: A table can have multiple rows with identical data.
False.
Why are integers commonly used for IDs in databases?
They allow for efficient storage and quick retrieval when querying for specific products.
What is the advantage of using VARCHAR for the Name column?
It allows for variable-length strings, using only the necessary space for each entry.
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.
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.
What are the categories of data types?
- 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
What do Integer Data Types represent?
Whole numbers, including positive and negative values.
- 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).
These are examples of?
These are examples of Integer Data Types
What are Decimal Data Types used for?
Numbers that require a fractional component.
- FLOAT
- DECIMAL
Are examples of which Data Type?
- FLOAT
- DECIMAL
These are examples of Decimal types
What do Character Data Types store?
Text strings.
What is the difference between CHAR and VARCHAR?
-
CHARis fixed-length -
VARCHARis variable-length
What do Date and Time Data Types store?
Dates, times, or both.
What are examples of Date and Time Data Types?
- DATE
- TIME
- DATETIME
- TIMESTAMP
What do Binary Data Types store?
Raw binary data.
What are some examples of Binary Data Types?
- BLOB
- BINARY
- VARBINARY
What is the purpose of Spatial Data Types?
To manage geometric data like points and polygons.
What do Document Data Types include?
Structured textual data formats
XML and JSON.
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
What is the recommended practice for using MySQL Data Types?
Use the smallest type sufficient for the required range to improve storage efficiency.
What is the storage requirement for TINYINT?
1 byte
What is the signed range for SMALLINT?
-32,768 to 32,767
Unsigned 0 to 65,535
What is the unsigned range for MEDIUMINT?
0 to 16,777,215
Signed range -8,388,608 to 8,388,607
What is the storage requirement for INT (INTEGER)?
4 bytes
What is the approximate range for FLOAT?
-3.4E+38 to 3.4E+38
What is the format for DATE data type?
YYYY-MM-DD
What is the storage requirement for VARCHAR(N)?
Length of stored string + 1 byte for length information (up to 65,535 characters).
What is the correct data type for storing a city’s population?
- unsigned
INTEGER - Used for values that are always positive (like total population).
What is the correct data type for storing the annual gain or loss in a city’s population?
- signed
MEDIUMINT - 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.
What is the correct data type for storing the price of an item ranging from a few dollars to a few hundred dollars?
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)
Here are some prices that would fit within DECIMAL(5,2)
What is the correct data type for storing the date and time an item is purchased?
DATETIME
What is the correct data type for storing a student’s assigned letter grade?
CHAR(1)
What is the correct data type for storing a student’s email address?
VARCHAR(100)
What is the correct data type for storing a yes or no value?
TINYINT
All relational databases support which data types?
- integer
- decimal
- date and time
- character data types.
True or False: An unsigned number can be negative.
False
To make all the rows and columns appear in the result table?
SELECT *
How would you selects only columns CountryCode and Language, so only two columns appear in the result table.
SELECT CountryCode, Language FROM CountryLanguage;
Refer to the following table.
Select all rows and only the Name and District columns.
SELECT Name, District FROM City;
Refer to the following table.
Select all rows and columns.
SELECT * FROM City;
Refer to the following table.
Select all rows and columns except ID in order of columns shown.
SELECT Name, CountryCode, District, Population FROM City;
Refer to the following table
What values are returned?
SELECT Balance + Payment FROM Customer;
- 2400, 5250, 310, 1075
- Each customer’s balance is added to the payment.
Refer to the following table
What values are returned?
SELECT 2 * (Balance - Payment) FROM Customer;
- 3600, 9500, 580, 1650
- parentheses indicate subtraction is performed before multiplication
Long Tables
SELECT * FROM City LIMIT 100;
This indicates what?
The SELECT statement returns only the first 100 rows from the City table.
Refer to the following table
SELECT CountryCode, Language FROM CountryLanguage WHERE Percentage > 0.0 AND Percentage < 10.0;
What would this outcome be?
- Two rows are returned.
- The statement selects rows with percentage between 0.0 and 10.0.
Refer to the following table
SELECT CountryCode, Language FROM CountryLanguage WHERE Percentage < 5.0 OR Percentage > 90.0;
What would this outcome be?
- Two rows are returned.
- The statement selects rows with percentage < 5.0 or percentage > 90.0.
A NULL in the BirthDate column means
“unknown”, since all employees have a birth date
NULL either unknown or inapplicable data
If Engineering employees are not paid a bonus what does this mean for LISA?
Lisa Ellison’s NULL bonus means “inapplicable”.
NULL either unknown or inapplicable data
Not NULL constraint | Refer to the statement
CREATE TABLE Department ( Code TINYINT UNSIGNED NOT NULL, Name VARCHAR(20), ManagerID SMALLINT );
Which columns may contain NULL values?
Name and Manager ID
The Name and ManagerID columns may contain NULL values, by default.
Not NULL constraint | Refer to the statement
CREATE TABLE Department ( Code TINYINT UNSIGNED NOT NULL, Name VARCHAR(20), ManagerID SMALLINT );
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 );
The NOT NULL constraint must be listed after the column name and data type.
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?
- Since Code is
NOT NULL, the database does not accept an insert without a Code value. - The insert statement fails.
NULL arithmetic and comparisons.
SELECT Name FROM Compensation WHERE (Salary + Bonus) > 30000;
What are the results?
- The
SELECTstatement does not select a name from a row when theWHEREclause isNULL, so Lisa Ellison is not selected. - When arithmetic or comparison operators have one or more
NULLoperands, the result isNULL. - When a
WHEREclause evaluates toNULLfor values in a row, the row is not selected.
Arithmetic: + (addition), - (subtraction), * (multiplication), / (division), % (modulo), ^ (exponentiation)
Refer to the table
What name is selected?
SELECT Name FROM Compensation WHERE Salary = Bonus;
Sam Snead- Jiho Chen is not selected because NULL = NULL is NULL
Refer to the table
What is the name selected?
SELECT Name FROM Compensation WHERE (Salary / Bonus) < 1.0;
Maria Rodriguez- The / and < operators return
NULLwhen either operand isNULL. - As a result, Lisa Ellison and Jiho Chen are not selected.
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.
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).
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.
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.
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.
What is the outcome of violating a NOT NULL constraint during data insertion?
It results in an error, preventing the insertion of the row.
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.
- If logic refers to the set of rules and constraints that govern how data is stored, accessed, and manipulated.
- What does
NULLlogic refer to in databases?
NULL logic refers to how logical expressions are handled in the presence of NULL values, indicating uncertainty in truth values.
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;
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;
How is NULL represented internally in MySQL?
NULL is represented internally where FALSE is 0 and TRUE is 1.
What is the result of a logical expression involving NULL?
The expression may evaluate to NULL, indicating uncertainty.
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;
Refer to the table definition
Whats 1,2,3
- Table Name
- Columns
- Column Values
Refer to the table | Insert Statements
The first code is
INSERT INTO Employee (ID, Name, Salary) VALUES (2538, 'Lisa Ellison', 45000);
How do you insert the second row?
INSERT INTO Employee VALUES (5384, 'Sam Snead', 30500)
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);
Whats the newest INSERT INTRO & VALUES
- Tablename
'Department' - Columns
Code, Name, ManagerID - Values
44, 'Engineering', 2538
When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.
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');
Whats the newest INSERT INTRO & VALUES
- Tablename
'Department' - Columns
Code, Name, M̶a̶n̶a̶g̶e̶r̶I̶D̶ - Values
99, 'Engineering'
When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.
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)
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
When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.
What does the INSERT syntax represent?
INSERT [INTO] TableName (Column1, Column2, ...) VALUES (Value1, Value2, ...);
- The
INSERT INTOclause names the table and columns involved. - The
VALUESclause names the column values.
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.
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 (●)
What does bidirectional mean in relationships?
- In database relationships, “bidirectional” means both tables are aware of each other.
- 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
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.
INSERT is a DML statement that inserts data into a table.
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.
COMMIT is a DTL statement that saves a transaction to the database.
Match the SQL sublanguage to the statement behavior.
SELECT all rows from table Product.
- Data Query Language
- (DQL) retrieves data from the database.
SELECT is a DQL statement that retrieves data from a table.
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.
GRANT is a DCL statement used to give permissions to users.
Match the SQL sublanguage to the statement behavior.
Create table Product.
- Data Definition Language
- DDL creates, alters, and drops tables.
CREATE is a DDL statement that creates a table.
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;
TIME() extracts the time from ‘2019-11-02 23:59:00’.
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;
DAY() extracts the day from ‘2019-11-14 08:00:00’.
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;
HOUR() extracts the hour from ‘2019-11-02 12:30:00’ and MINUTE() extracts the minute. Thus, 12 + 30 = 42.
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;
DATEDIFF() calculates the number of days from ‘2019-11-01 08:00:00’ to ‘2019-11-02 08:00:00’, which is 1 day.
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;
TIMEDIFF() calculates the time difference between ‘2019-11-05 11:15:00’ and ‘2019-11-05 10:15:00’, which is just 1 hour.
How would you concatenate ‘Super ‘ with the Name of the avatar where ID is 1?
SELECT CONCAT('Super ', Name)
FROM Avatar
WHERE ID = 1;
This will return “Super Link”.
How would you convert the BestMove of the avatar where ID is 3 to lowercase?
SELECT LOWER(BestMove) FROM Avatar WHERE ID = 3;
This will return “psystrike”.
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;
This will return “Finale”.
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;
This will return “Meta Fright”.
How would you select the ID and Name for all avatars where BestMove contains ‘Final’?
SELECT ID, Name FROM Avatar WHERE BestMove LIKE '%Final%';
This will return the ID and Name of any avatar with “Final” in their BestMove.
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;
This query counts and groups the automobiles by their make, then orders the results by make.
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;
This query calculates the average price of cars grouped by year and orders the results by year.
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);
This query retrieves the highest price for each type of automobile and orders the results by maximum price.
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);
This query retrieves the maximum price of automobiles for each year and type, ordered by year and maximum price.
