9/11/24, 2:17 Preassessment
PM notes
NOTES from Preassement
• In which ways can data redundancy affect managing disk storage space and calculation
speed.
o The problems caused by data redundancy are threefold:
First, the redundant data takes up a great deal of extra disk space.
Second, if the redundant data has to be updated, additional time is needed to do so
since, if done correctly, every copy of the redundant data must be updated. This can
create a major performance issue.
Third and potentially the most significant is the potential for data integrity problems.
The term data integrity refers to the accuracy of the data
• With witch command can a DBA allow a user to query the table by executing “select”
commands on it?
o GRANT SELECT ON (TABLENAME) TO ‘USER’@’LOCALHOST’;
• Which command terminates a session with a server at any time?
o Quit
• Which command you use to connect to a server running on another machine?
o Mysql -h MACHINENAME -p -u USERNAME
• Which command creates a database only if it does not already exist?
o CREATE DATABASE IF NOT EXISITS db_name;
• What does “WHERE” identify in a basic SQL “Select” statement?
o The rows to be included
• Which data definition language statement affects database or objects in them?
o Create
o Alter
o Drop
o Truncate
o Comment
o Rename
• Data Manipulation language statements:
o Select
o Insert
o Update
o Delete
o Merge
o Call
o Explain Plan
o Lock Table
• Data Control Language
o Grant
o Revoke
about:bl 1
, 9/11/24, 2:17 Preassessment
PM notes
• What does the DELETE statement do?
o It removes rows from a table
• Which condition must be in effect to use the “Insert Into…Values” syntax for an “INSERT”
Statement?
o The “Values” list must contain a value for each row in the table
• Which kind of data type is FLOAT in a statement?
o Integer – No fractional Part, 43,-3
o Decimal- 3.14
• What MYSQL data types can represent images or sounds?
o STRING DATA TYPES
▪ CHAR
▪ VARCHAR
▪ BINARY
▪ VARBINARY
▪ TINYBLOB
▪ MEDIUMBLOB
▪ LONGBLOB
▪ TINYTEXT
▪ MEDIUMTEXT
▪ LONGTEXT
▪ ENUM
▪ SET
o Other data types
▪ NUMERIC DATA TYPES
• TINYINT
• SMALLINT
• MEDIUMINT
• INT
• BIGINT
• DECIMAL
• FLOAT
• DOUBLE
• BIT
• Why is a view used to give controlled access to data?
o To restrict access to persons retrieving and modifying sensitive information
• What 3 elements will a DBA need to create a view of a table?
o The “create view” privilege for the table
o A level of privilege for every column selected by the “select”
o The “Select” privilege for every column referred to elsewhere in the statement
• What statement, added before a “Select” Statement is needed to generate a view?
o CREATE VIEW viewname AS
• What optional clause added to a GRANT statement enables the account to give its own
privileges to other users?
about:bl 2
PM notes
NOTES from Preassement
• In which ways can data redundancy affect managing disk storage space and calculation
speed.
o The problems caused by data redundancy are threefold:
First, the redundant data takes up a great deal of extra disk space.
Second, if the redundant data has to be updated, additional time is needed to do so
since, if done correctly, every copy of the redundant data must be updated. This can
create a major performance issue.
Third and potentially the most significant is the potential for data integrity problems.
The term data integrity refers to the accuracy of the data
• With witch command can a DBA allow a user to query the table by executing “select”
commands on it?
o GRANT SELECT ON (TABLENAME) TO ‘USER’@’LOCALHOST’;
• Which command terminates a session with a server at any time?
o Quit
• Which command you use to connect to a server running on another machine?
o Mysql -h MACHINENAME -p -u USERNAME
• Which command creates a database only if it does not already exist?
o CREATE DATABASE IF NOT EXISITS db_name;
• What does “WHERE” identify in a basic SQL “Select” statement?
o The rows to be included
• Which data definition language statement affects database or objects in them?
o Create
o Alter
o Drop
o Truncate
o Comment
o Rename
• Data Manipulation language statements:
o Select
o Insert
o Update
o Delete
o Merge
o Call
o Explain Plan
o Lock Table
• Data Control Language
o Grant
o Revoke
about:bl 1
, 9/11/24, 2:17 Preassessment
PM notes
• What does the DELETE statement do?
o It removes rows from a table
• Which condition must be in effect to use the “Insert Into…Values” syntax for an “INSERT”
Statement?
o The “Values” list must contain a value for each row in the table
• Which kind of data type is FLOAT in a statement?
o Integer – No fractional Part, 43,-3
o Decimal- 3.14
• What MYSQL data types can represent images or sounds?
o STRING DATA TYPES
▪ CHAR
▪ VARCHAR
▪ BINARY
▪ VARBINARY
▪ TINYBLOB
▪ MEDIUMBLOB
▪ LONGBLOB
▪ TINYTEXT
▪ MEDIUMTEXT
▪ LONGTEXT
▪ ENUM
▪ SET
o Other data types
▪ NUMERIC DATA TYPES
• TINYINT
• SMALLINT
• MEDIUMINT
• INT
• BIGINT
• DECIMAL
• FLOAT
• DOUBLE
• BIT
• Why is a view used to give controlled access to data?
o To restrict access to persons retrieving and modifying sensitive information
• What 3 elements will a DBA need to create a view of a table?
o The “create view” privilege for the table
o A level of privilege for every column selected by the “select”
o The “Select” privilege for every column referred to elsewhere in the statement
• What statement, added before a “Select” Statement is needed to generate a view?
o CREATE VIEW viewname AS
• What optional clause added to a GRANT statement enables the account to give its own
privileges to other users?
about:bl 2
