1/68
Looks like no tags are added yet.
They are commands/languages used to communicate with a DBMS and perform operations on data.
What are database languages used for in a DBMS?
Create, Read, Update, Delete.
What does CRUD stand for?
Data Definition, Data Manipulation, Data Control, Transaction Control
What are the four types of database languages?
To define and modify database structure and metadata.
What is the main purpose of DDL?
DDL
If you need to create, modify, or delete a table or index, which language would you use?
They are auto-committed, meaning the changes are saved permanently.
According to the material, what happens to DDL changes?
To access and modify data in pre-existing schema objects.
What is the main purpose of DML?
To define and modify database structure and metadata.
What operations are associated with DML?
DDL.
If you need to create, modify, or delete a table or index, which language would you use?
They are auto-committed, meaning the changes are saved permanently.
According to the material, what happens to DDL changes?
To access and modify data in pre-existing schema objects.
What is the main purpose of DML?
Inserting, deleting, updating, and retrieving data.
What operations are associated with DML?
The DML instructions that deal with data retrieval.
What is DQL according to the material?
According to the material, can DML changes be reversed? Why?
Yes. They are not automatically committed, so the changes can be reversed.
What is the main purpose of DCL?
To manage user access and permissions.
GRANT and REVOKE.
What two actions can DCL use to control access?
DCL.
If you need to control whether a user can access or modify database data, which language is relevant?
A group of related tasks treated by the DBMS as one execution unit.
What is a transaction?
To manage transactions.
What is the main purpose of TCL?
COMMIT and ROLLBACK.
What are the two key TCL actions mentioned in the material?
DML manipulates data; TCL controls the effects of those changes through transactions.
DML manipulates data; TCL controls the effects of those changes through transactions.
DDL = Structure, DML = Data, DCL = Access, TCL = Transactions.
What is the easiest way to distinguish DDL, DML, DCL, and TCL?
A name used for a database object, such as a database, table, column, alias, or index.
What is a database identifier?
Database names must be unique.
What naming rule applies to database names?
Within a table
Where must column and index names be unique?
Enclose the name in quotation marks, according to the material.
What should you do if an identifier is a reserved MySQL word?
They are not permitted unless the name is enclosed in quotation marks.
What happens if a name contains embedded spaces or special characters?
No. They are treated as different names.
On a case-sensitive server, are CUSTOMER and Customer the same name?
What kind of value a column can store.
What does a MySQL data type tell you?
A name and a data type.
What two things must every database column have?
String, Numeric, and Date and Time.
What are the three main MySQL data type categories in the material?
CHAR is fixed-length; VARCHAR is variable-length.
What is the difference between CHAR and VARCHAR?
CHAR.
If a column stores text with a fixed length, which type would you choose?
VARCHAR.
If a column stores text with a variable length, which type would you choose?
BINARY stores fixed-length binary strings; VARBINARY stores variable-length binary strings.
What is the difference between BINARY and VARBINARY?
Binary Large Objects (binary data).
What is a BLOB used for?
ENUM allows one value from a list; SET allows zero or more values from a list.
What is the difference between ENUM and SET?
ENUM
If a column should allow only one option from a predefined list, which type would you use?
If a column should allow multiple options from a predefined list, which type would you use?
SET
0 is false; nonzero values are true.
What does BOOL mean in the material?
BOOLEAN is equal to BOOL according to the material.
What is the relationship between BOOL and BOOLEAN?
INTEGER is equal to INT
What is the relationship between INT and INTEGER?
INT stores integers; DECIMAL stores exact fixed-point numbers
What is the difference between INT and DECIMAL?
size = total digits; d = digits after the decimal point.
What do size and d mean in DECIMAL(size,d)?
10 total digits, with 2 digits after the decimal point.
What does DECIMAL(10,2) mean?
DECIMAL.
If you need an exact decimal value, such as a monetary amount, which numeric type from the notes is appropriate?
It prevents negative values.
What does UNSIGNED do to a numeric column?
It automatically also adds the UNSIGNED attribute.
What does ZEROFILL do?
Whether MySQL uses FLOAT or DOUBLE.
What does the p value determine in FLOAT(p)?
Whether MySQL uses FLOAT or DOUBLE.
What does the p value determine in FLOAT(p)?
In FLOAT(p), what happens when p is 0–24?
The resulting type is FLOAT.
In FLOAT(p), what happens when p is 25–53?
The resulting type is DOUBLE.
DATE.
Which data type should you use for a value containing only a date?
TIME
Which data type should you use for a value containing only a time?
DATETIME.
Which data type stores both a date and time?
TIMESTAMP
Which data type represents a timestamp and can use automatic current-time initialization/updating?
YEAR
Which data type stores a four-digit year?
YYYY-MM-DD
What is the format of DATE?
hh:mm:ss
What is the format of TIME?
1901–2155 and 0000.
What is the allowed YEAR range stated in the material?
DATE = date; TIME = time; DATETIME = date + time; YEAR = year.
What is the key difference between DATE, TIME, DATETIME, and YEAR?
DDL.
If you need to create a table, which database language category should you think of first?
DML.
If you need to insert or update a student's information, which language should you think of?
DCL.
If you need to give a user permission to access database data, which language should you think of?
TCL.
If you need to commit or roll back changes made by DML, which language should you think of?
VARCHAR.
If a field stores a student's first name and its length can vary, which data type would you choose?
BOOL/BOOLEAN.
If a field stores whether something is true or false, which data type would you choose?
DECIMAL.
If a field stores an exact decimal value, which data type would you choose?
DATE
If a field stores a student's birthdate, which data type would you choose?