Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

C175 Data Management Foundations > My Addition > Flashcards

My Addition Flashcards

1
Q

In refernce of ALTER TABLE statements

  1. What is the syntax for ADD?

2 As in add column ColumnName.

  1. As in include DataType
A 
ALTER TABLE TableName 
ADD ColumnName DataType;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

In refernce of ALTER TABLE statements

  1. What is the syntax for CHANGE?
  2. As in change a column CurrentColumnName to NewColumnName.
A 
ALTER TABLE TableName 
CHANGE CurrentColumnName NewColumnName NewDataType;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

In refernce of ALTER TABLE statements

  1. What is the syntax for DROP?
  2. As in DROP ColumnName
A 
ALTER TABLE TableName 
DROP ColumnName;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are the components of a table?

A
  1. A table must have at least one column and can have multiple rows.
  2. A table can also be empty (having no rows).

Each column has a name and a specific data type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What SQL statement is used to define a new table?

A

CREATE TABLE

This statement specifies table names and data types.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What does the DROP TABLE statement do?

A

Deletes a table and all its data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the purpose of the ALTER TABLE statement?

A

Used to modify an existing table by adding, changing, or deleting columns.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

List common data types used in tables.

A
  • integers (INT)
  • values with 0 to N characters (VARCHAR(N))
  • dates (DATE)
  • decimals (DECIMAL)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What rule governs the values in a cell of a table?

A

Exactly one value per cell.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What principle allows for optimization of storage in tables?

A

No significant row order

supporting the principle of data independence.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a NULL value in a table?

A

Represents the absence of data in a cell.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q
  1. What is the SQL syntax for creating a table TableName?
  2. As in with the Column$ & Data_Type.
A

CREATE TABLE TableName (Column1 DATA_TYPE, Column2 DATA_TYPE, ..., ColumnN DATA_TYPE);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Fill in the blank: The ID column is of data type _______.

A

Integer (INT)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is the appropriate data type for the ProductType column?

A

Variable-length string with maximum 3 characters (VARCHAR(3)).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What data type is used to store dates in a table?

A

Date

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

True or False: A table can have multiple rows with identical data.

A

False.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Explain the data type DECIMAL(6,1).

A

It can accurately represent decimal numbers with up to 6 total digits, of which 1 digit is after the decimal point.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is a data type in a database?

A

A data type is a named set of values from which column values in a database are derived.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What are the categories of data types?

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What do Integer Data Types represent?

A

Whole numbers, including positive and negative values.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q
  • 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?

A

These are examples of Integer Data Types

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What are Decimal Data Types used for?

A

Numbers that require a fractional component.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q
  • FLOAT
  • DECIMAL

Are examples of which Data Type?

A
  • FLOAT
  • DECIMAL

These are examples of Decimal types

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What do Character Data Types store?

A

Text strings.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What is the difference between CHAR and VARCHAR?

A
  • CHAR is fixed-length
  • VARCHAR is variable-length
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What do Date and Time Data Types store?

A

Dates, times, or both.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What are examples of Date and Time Data Types?

A
  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What do Binary Data Types store?

A

Raw binary data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What are some examples of Binary Data Types?

A
  • BLOB
  • BINARY
  • VARBINARY
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is the purpose of Spatial Data Types?

A

To manage geometric data like points and polygons.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

What do Document Data Types include?

A

Structured textual data formats

XML and JSON.

40
Q

These examples correlate to which Specialized Data Types?

  • MONEY
  • BOOLEAN
  • ENUM
A
  • MONEY for currency
  • BOOLEAN for true/false values
  • ENUM for pre-defined sets of values
41
Q

What is the recommended practice for using MySQL Data Types?

A

Use the smallest type sufficient for the required range to improve storage efficiency.

42
Q

What is the storage requirement for TINYINT?

A

1 byte

43
Q

What is the signed range for SMALLINT?

A

-32,768 to 32,767

Unsigned 0 to 65,535

44
Q

What is the unsigned range for MEDIUMINT?

A

0 to 16,777,215

Signed range -8,388,608 to 8,388,607

45
Q

What is the storage requirement for INT (INTEGER)?

A

4 bytes

46
Q

What is the approximate range for FLOAT?

A

-3.4E+38 to 3.4E+38

47
Q

What is the format for DATE data type?

A

YYYY-MM-DD

48
Q

What is the storage requirement for VARCHAR(N)?

A

Length of stored string + 1 byte for length information (up to 65,535 characters).

49
Q

What is the correct data type for storing a city’s population?

A
  1. unsigned INTEGER
  2. Used for values that are always positive (like total population).
50
Q

What is the correct data type for storing the annual gain or loss in a city’s population?

A
  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
Q

What is the correct data type for storing the price of an item ranging from a few dollars to a few hundred dollars?

A

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)

52
Q

What is the correct data type for storing the date and time an item is purchased?

A

DATETIME

53
Q

What is the correct data type for storing a student’s assigned letter grade?

A

CHAR(1)

54
Q

What is the correct data type for storing a student’s email address?

A

VARCHAR(100)

55
Q

What is the correct data type for storing a yes or no value?

A

TINYINT

56
Q

All relational databases support which data types?

A
  1. integer
  2. decimal
  3. date and time
  4. character data types.
57
Q

True or False: An unsigned number can be negative.

A

False

58
Q

To make all the rows and columns appear in the result table?

A

SELECT *

59
Q

How would you selects only columns CountryCode and Language, so only two columns appear in the result table.

A 
SELECT CountryCode, Language
FROM CountryLanguage;
60
Q

Refer to the following table.

Select all rows and only the Name and District columns.

A 
SELECT Name, District
FROM City;
61
Q

Refer to the following table.

Select all rows and columns.

A 
SELECT *
FROM City;
62
Q

Refer to the following table.

Select all rows and columns except ID in order of columns shown.

A 
SELECT Name, CountryCode, District, Population
FROM City;
63
Q

Refer to the following table

What values are returned?

SELECT Balance + Payment 
FROM Customer;
A
  1. 2400, 5250, 310, 1075
  2. Each customer’s balance is added to the payment.
64
Q

Refer to the following table

What values are returned?

SELECT 2 * (Balance - Payment)
FROM Customer;
A
  1. 3600, 9500, 580, 1650
  2. parentheses indicate subtraction is performed before multiplication
65
Q

Long Tables

SELECT *
FROM City
LIMIT 100;

This indicates what?

A

The SELECT statement returns only the first 100 rows from the City table.

66
Q

Refer to the following table

SELECT CountryCode, Language
FROM CountryLanguage
WHERE Percentage > 0.0
 AND Percentage < 10.0;

What would this outcome be?

A
  1. Two rows are returned.
  2. The statement selects rows with percentage between 0.0 and 10.0.
67
Q

Refer to the following table

SELECT CountryCode, Language
FROM CountryLanguage
WHERE Percentage < 5.0
 OR Percentage > 90.0;

What would this outcome be?

A
  1. Two rows are returned.
  2. The statement selects rows with percentage < 5.0 or percentage > 90.0.
68
Q

A NULL in the BirthDate column means

A

“unknown”, since all employees have a birth date

NULL either unknown or inapplicable data

69
Q

If Engineering employees are not paid a bonus what does this mean for LISA?

A

Lisa Ellison’s NULL bonus means “inapplicable”.

NULL either unknown or inapplicable data

70
Q

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?

A 
Name and Manager ID

The Name and ManagerID columns may contain NULL values, by default.

71
Q

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?

A 
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.

72
Q

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?

A
  1. Since Code is NOT NULL, the database does not accept an insert without a Code value.
  2. The insert statement fails.
73
Q

NULL arithmetic and comparisons.

SELECT Name
FROM Compensation
WHERE (Salary + Bonus) > 30000;

What are the results?

A
  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.
Lisa Ellison is not selected

Arithmetic: + (addition), - (subtraction), * (multiplication), / (division), % (modulo), ^ (exponentiation)

74
Q

Refer to the table

What name is selected?

SELECT Name
FROM Compensation
WHERE Salary = Bonus;
A
  1. Sam Snead
  2. Jiho Chen is not selected because NULL = NULL is NULL
75
Q

Refer to the table

What is the name selected?

SELECT Name
FROM Compensation
WHERE (Salary / Bonus) < 1.0;
A
  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
Q

What does NULL represent in databases?

A

NULL is a special marker used to indicate that a data value does not exist in the database, signifying unknown or inapplicable data.

77
Q

How does NULL differ from zero and blank entries?

A

NULL is distinct from zero (a known value) and blank entries (which represent empty strings in character data types).

78
Q

What is the NOT NULL constraint in SQL?

A

The NOT NULL constraint ensures that a column cannot have NULL values, commonly used for mandatory fields.

79
Q

What happens during arithmetic or comparisons involving NULL?

A

The result will also be NULL, and the IS NULL or IS NOT NULL operators must be used to check for NULL.

80
Q

What SQL command is used to add a NOT NULL constraint to a column?

A 
ALTER TABLE table_name 
MODIFY column_name data_type NOT NULL.
81
Q

What is the outcome of violating a NOT NULL constraint during data insertion?

A

It results in an error, preventing the insertion of the row.

82
Q

How can you enforce NOT NULL constraints when creating a table?

A

By appending NOT NULL right after the column’s data type in the CREATE TABLE statement.

83
Q
  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?
A

NULL logic refers to how logical expressions are handled in the presence of NULL values, indicating uncertainty in truth values.

84
Q

How do you select rows with NULL values in SQL?

A

Use the IS NULL operator in your query.

SELECT * FROM table_name WHERE column_name IS NULL;

85
Q

What is the SQL command to find all records where a column has a NULL value?

A 
SELECT * 
FROM table_name 
WHERE column_name IS NULL;
86
Q

How is NULL represented internally in MySQL?

A

NULL is represented internally where FALSE is 0 and TRUE is 1.

87
Q

What is the result of a logical expression involving NULL?

A

The expression may evaluate to NULL, indicating uncertainty.

88
Q

What SQL command can be used to find rows where a specific column does not contain NULL values?

A 
SELECT * 
FROM table_name
WHERE column_name IS NOT NULL;
89
Q

Refer to the table definition

Whats 1,2,3

A
  1. Table Name
  2. Columns
  3. Column Values
90
Q

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?

A 
INSERT INTO Employee
VALUES (5384, 'Sam Snead', 30500)
91
Q

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

A
  • Tablename 'Department'
  • Columns Code, Name, ManagerID
  • Values 44, 'Engineering', 2538
INSERT Syntax

When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.

92
Q

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

A
  • Tablename 'Department'
  • Columns Code, Name, M̶a̶n̶a̶g̶e̶r̶I̶D̶
  • Values 99, 'Engineering'
INSERT Syntax

When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.

93
Q

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

A
  • 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
INSERT Syntax

When column names are omitted, values must be in the order of columns in the CREATE TABLE statement.

94
Q

What does the INSERT syntax represent?

INSERT [INTO] TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);
A
  1. The INSERT INTO clause names the table and columns involved.
  2. The VALUES clause names the column values.
95
Q

The Department table’s Manager column is a $-key that refers to the $-key ID in the Employee table.

A

The Department table’s Manager column is a foreign key that refers to the primary key ID in the Employee table.

96
Q

The Department table’s Manager column is a foreign key. How do we know that?

A

An empty circle (○) precedes foreign keys in table diagrams, and an arrow leads to the referenced primary key indicated with a (●)

97
Q

What does bidirectional mean in relationships?

A
  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

C175 Data Management Foundations (20 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions