Upwork MySQL Test Answers 2018

Which of the following is the best way to modify a table to allow null values? A• ALTER TABLE table_name MODIFY column_name varchar(255) null B• ALTER TABLE table_name MODIFY column_name VARCHAR(255) C• ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL D• ALTER table_name MODIFY column_name varchar(255) null

A• ALTER TABLE table_name MODIFY column_name varchar(255) null

What is NDB? A• An in-memory storage engine offering high-availability and data-persistence features B• A filesystem C• An SQL superset D• MySQL scripting language E• None of the above

A• An in-memory storage engine offering high-availability and data-persistence features

Which datatype is used to store binary data in MySQL? A• BLOB B• BIGINT C• INT D• Both BLOB and BIGINT

A• BLOB

The Flush statement cannot be used for: A• Closing any open tables in the table cache B• Closing open connections C• Flushing the log file D• Flushing the host cache

A• Closing any open tables in the table cache

What are MySQL Spatial Data Types in the following list? A• GEOMETRY B• CIRCLE C• SQUARE D• POINT E• POLYGON

A• GEOMETRY

Which of the following are true in case of Indexes for MYISAM Tables? A• Indexes can have NULL values B• BLOB and TEXT columns can be indexed C• Indexes per table cannot be more than 16 D• Columns per index cannot be more than 16

A• Indexes can have NULL values B• BLOB and TEXT columns can be indexed

Which of the following statements are true? A• Names of databases, tables and columns can be up to 64 characters in length B• Alias names can be up to 255 characters in length C• Names of databases, tables and columns can be up to 256 characters in length D• Alias names can be up to 64 characters in length

A• Names of databases, tables and columns can be up to 64 characters in length

Which of the following will raise MySQL's version of an error? A• SIGNAL B• RAISE C• ERROR D• None of these.

A• SIGNAL

What does the term "overhead" mean in MySQL? A• Temporary diskspace that the database uses to run some of the queries B• The size of a table C• A tablespace name D• None of the above

A• Temporary diskspace that the database uses to run some of the queries

Examine the two SQL statements given below: SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC What is true about them? A• The two statements produce identical results B• The second statement returns an error C• There is no need to specify DESC because the results are sorted in descending order by default D• None of the above statments is correct

A• The two statements produce identical results

Which of the following will show when a table in a MySQL database was last updated? A• Using the following query: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name' B• Creating an on-update trigger to write timestamp in a custom table, then querying the custom table C• Getting the "last modified" timestamp of the corresponding database file in the file system D• None of these.

A• Using the following query: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name'

Which of the following statements are true about SQL injection attacks? A• Wrapping all variables containing user input by a call to mysql_real_escape_string() makes the code immune to SQL injections. B• Parametrized queries do not make code less vulnearable to SQL injections. C• SQL injections are not possible, if only emulated prepared statements are used. D• Usage of later versions of MySQL, validation, and explicit setting of the charset of user input are valid measures to decrease vulnerability to SQL injections.

A• Wrapping all variables containing user input by a call to mysql_real_escape_string() makes the code immune to SQL injections.

How can a InnoDB database be backed up without locking the tables? A• mysqldump --single-transaction db_name B• mysqldump --force db_name C• mysqldump --quick db_name D• mysqldump --no-tablespaces db_name

A• mysqldump --single-transaction db_name

Which of the following will select the highest-priced record per item? A• select item, brand, price from items where max(price) order by item B• select * from items where price = max group by item C• select item, brand, max(price) from items group by item D• select * from items where price > 200 order by item

A• select item, brand, price from items where max(price) order by item

What is true about the ENUM data type? A• An enum value may be a user variable B• An enum may contain number enclosed in quotes C• An enum cannot contain an empty string D• An enum value may be NULL E• None of the above is true

B• An enum may contain number enclosed in quotes

Which of the following is the best MySQL data type for currency values? A• SMALLINT B• DECIMAL(19,4) C• VARCHAR(32) D• BIGINT

B• DECIMAL(19,4)

Which of the following statements grants permission to Peter with password Software? A• GRANT ALL ON testdb.* TO peter PASSWORD 'Software' B• GRANT ALL ON testdb.* TO peter IDENTIFIED by 'Software' C• GRANT ALL OF testdb.* TO peter PASSWORD 'Software' D• GRANT ALL OF testdb.* TO peter IDENTIFIED by 'Software'

B• GRANT ALL ON testdb.* TO peter IDENTIFIED by 'Software'

Consider the query: SELECT name FROM Students WHERE name LIKE '_a%'; Which names will be displayed? A• Names starting with "a" B• Names containing "a" as the second letteR C• Names starting with "a" or "A" D• Names containing "a" as any letter except the first

B• Names containing "a" as the second letteR

Which query will return values containing strings "Pizza", "Burger", or "Hotdog" in the database? A• SELECT * FROM fiberbox WHERE field REGEXP 'Pizza|Burger|Hotdog'; B• SELECT * FROM fiberbox WHERE field LIKE '%Pizza%' OR field LIKE '%Burger%' OR field LIKE '%Hotdog%'; C• SELECT * FROM fiberbox WHERE field = '%Pizza%' OR field = '%Burger%' OR field = '%Hotdog%'; D• SELECT * FROM fiberbox WHERE field = '?Pizza?' OR field = '?Burger?' OR field = '?Hotdog?';

B• SELECT * FROM fiberbox WHERE field LIKE '%Pizza%' OR field LIKE '%Burger%' OR field LIKE '%Hotdog%';

Which of the following relational database management systems is simple to embed in a larger program? A• MySQL B• SQLite C• Both D• None

B• SQLite

What does DETERMINISTIC mean in the creation of a function? A• The function returns no value B• The function always returns the same value for the same input C• The function returns the input value D• None of the above

B• The function always returns the same value for the same input

Which of the following statements is used to change the structure of a table once it has been created? A. CHANGE TABLE B• MODIFY TABLE C• ALTER TABLE D• UPDATE TABLE

C• ALTER TABLE

Which of the following is an alternative to Subquery Factoring (ex. the 'WITH' clause in MS SQL Server)? A• The 'IN' clause B• Using temporary tables and inline views C• The 'INNER JOIN' clause D• Using subqueries

C• The 'INNER JOIN' clause

Which of the following is an alternative to groupwise maximum ranking (ex. ROW_NUMBER() in MS SQL)? A• Using subqueries B• Using variables in a MySQL query C• Using self-join D• MySQL also supports ROW_NUMBER()

C• Using self-join

Below is the table "messages," please find proper query and result from the choices below. Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 A• select * from (select * from messages GROUP BY id DESC) AS x ORDER BY name Result: 3 A A_data_3 5 B B_data_2 6 C C_data_1 B• select * from messages where name =Desc Result: 1 A A_data_1 2 A A_data_2 3 A A_data_3 C• select * from messages group by name Result: 1 A A_data_1 4 B B_data_1 6 C C_data_1 D• Answer A and B

C• select * from messages group by name Result: 1 A A_data_1 4 B B_data_1 6 C C_data_1

Which of the following results in 0 (false)? A• "EXPERTRATING" LIKE "EXP%" B• "EXPERTRATING" LIKE "Exp%" C• BINARY "EXPERTRATING" LIKE "EXP%" D• BINARY "EXPERTRATING" LIKE "Exp%" E• All will result in 1 (true)

D• BINARY "EXPERTRATING" LIKE "Exp%"

How can an user quickly rename a MySQL database for InnoDB? A• He cannot rename any MySQL database B• By using: RENAME DATABASE db_old_name TO db_new_name C• By using: RENAME DATABASE TO db_new_name D• By creating the new empty database, then rename each table using: RENAME TABLE db_old_name.table_name TO db_new_name.table_name

D• By creating the new empty database, then rename each table using: RENAME TABLE db_old_name.table_name TO db_new_name.table_name

Consider the following queries: create table foo (id int primary key auto_increment, name int); create table foo2 (id int auto_increment primary key, foo_id int references foo(id) on delete cascade); Which of the following statements is true? A• Two tables are created B• If a row in table foo2, with a foo_id of 2 is deleted, then the row with id = 2 in table foo is automatically deleted C• Those queries are invalid D• If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted

D• If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted

What will happen if you query the emp table as shown below: select empno, DISTINCT ename, Salary from emp; _____________________________________________________________________________ A• EMPNO, unique value of ENAME and then SALARY are displayed B• EMPNO, unique value ENAME and unique value of SALARY are displayed C• DISTINCT is not a valid keyword in SQL D• No values will be displayed because the statement will return an error

D• No values will be displayed because the statement will return an error

Which of the following will dump the whole MySQL database to a file? A• mysql -e "select * from myTable" -u myuser -pxxxxxxxxx mydatabase > mydumpfile.txt B• mysql -e "select * from myTable" mydatabase > mydumpfile.txt C• SELECT * from myTable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' D• None of the above.

D• None of the above.

Which of the following will reset the MySQL password for a particular user? A• UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='username'; B• UPDATE mysql.user SET Password='password' WHERE User='username'; C• UPDATE mysql.user SET Password=RESET('password') WHERE User='username'; D• None of the above.

D• None of the above.

Which of the following is the best way to disable caching for a query? A• Add the /*!no_query_cache*/ comment to the query. B• Flush the whole cache with the command: FLUSH QUERY CACHE C• Reset the query cache with the command: RESET QUERY CACHE D• Use the SQL_NO_CACHE option in the query.

D• Use the SQL_NO_CACHE option in the query.

Which of the following will restore a MySQL DB from a .dump file? A• mysql -u<user> -p < db_backup.dump D• mysql -u<user> -p<password> < db_backup.dump B• mysql -u<user> -p <password> < db_backup.dump B• mysql -u<user> -p<password> > db_backup.dump

D• mysql -u<user> -p<password> < db_backup.dump

What will happen if two tables in a database are named rating and RATING? A• This is not possible as table names are case in-sensitiCve (rating and RATING are treated as same name) B• This is possible as table names are case sensitive (rating and RATING are treated as different names) C• This is possible on UNIX/LINUX and not on Windows platform D• This is possible on Windows and not on UNIX/LINUX platforms E• This depends on lower_case_table_names system variable

E• This depends on lower_case_table_names system variable

Is it possible to insert several rows into a table with a single INSERT statement? • No • Yes

• Yes