MySQL Upwork Test

0
575

We don’t guarantee that all provided answers are correct.

Which of the following are correct about HEAP tables? Check all that apply
Note: They may be more than one right answer.
Answer

  • Heap Tables are in-memory
  • Text fields are allowed within heap table
  • Heap tables generally used for high-speed temporary storage
  • Heap table support AUTO_INCREMENT

Which database is ignored by ‘mysqldump’ while dumping a collection of database for backup?
Answer

  • INFORMATION_SCHEMA
  • All the test databases
  • mysql database
  • All database with MYISAM tables

Which statement is used to disable autocommit mode implicity for a single series statements?
Answer

  • START TRANSACTION
  • COMMIT
  • ROLLBACK
  • NOCOMIT

Which of the following Storage Engines in MySQL supports Foreign Keys?
Answers

  • MyISAM
  • Memory
  • InnoDB
  • Archive

Which MySQL function is used to measure the speed of a specific MySQL expression or function?
Answers

  • VALUES()
  • SPEED()
  • BENCHMARK()
  • SCHEMA()

How many Triggers are possible in MySQL?
Answer

  • 4
  • 5
  • 6
  • 7

What is difference between NOW() and CURRENT_DATE()?
Answer

  • CURRENT_DATE()command is used to show current year, month, date with our minutes and seconds whereas CURRENT_DATE() Shows current year, month, and date only.
  • NOW()command is used to show current year, month, date with our minutes and seconds whereas NOW() Shows current year, month, and date only.
  • There is no difference as both are same.
  • None of the above.

What is the default port for MySQL Server?
Answer

  • 5432
  • 3306
  • 27017
  • 27018

What is the output when the following query executes?

CREATE TABLE t2 (val INT)
PARTITION BY LIST(val)(
PARTITION mypart VALUES IN (1,3,5)
PARTITION MyPart VALUES IN (2,4,6)
);
Answer

  • A table ‘t2’ is created with List Partitioning
  • Failure/Error occurs
  • No partitions are created

What is the correct syntax to create a view?
Answer

  • create VIEW view_name AS Select cloumn_name(s) from table_name where condition
  • create VIEW view_name Select cloumn_name(s) from table_name where condition
  • create VIEW view_name AS Select cloumn_name(s) from table_name where condition
  • None of the above

A user with which of the following privileges can perform an update even if the read_only system variable is enabled?
Answer

  • Shutdown_priv
  • Super_priv
  • Process_priv
  • Repl_client_priv

Which of the following will show when a table in a mysql database was last updaed?
Answer

  • Using the following query: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = ‘database_name’ AND TABLE_NAME = ‘table_name’ A
  • Creating an on-update trigger to write timestamp in a custom table, then querying the custom table
  • Getting the “last modified” timestamp of the corresponding database file in the file system
  • None

How would you return a random nickname from a very large “users” table?
Answer

  • Like I normally do: SELECT nickname FROM users ORDER BY RAND() LIMIT 1;
  • I would first run a query to get a total number of rows: SELECT COUNT(*) FROM users;. After that I would select a random number with the scripting language (e.g. mt_rand in case of PHP) and then perform a query like this: SELECT nickname FROM users LIMIT $random_number, 1;
  • I would first run a query to get all the nicknames: SELECT nickname FROM USERS;. After that I would select a random nickname with the scripting language (e.g. mt_rand in case of PHP).

Which is the correct syntax to restore a database from command line
Answer

  • mysqlrestore -u username -p database_name < database_name.sql
  • mysql -u username -p database_name database_name.sql
  • mysql -u username -p database_name << database_name.sql
  • mysql -u username -p database_name < database_name.sql

What are the properties of transacions?
Answer

  • Automiciy, Consistencyv
  • Durability, Isolation
  • Automiciy, Consistency, Isolation
  • Automiciy, Consistency, Durability, Isolation

Which of the following is not a valid Comparison operator?
Answer

  • == Answer
  • <=>
  • !=
  • <>
  • REGEXP

What is the max number of connections allowed by default in MySQL 5.0?
Answers

  • 100
  • 101
  • 150
  • 151

Which correct syntax to view the global event scheduler’s status in mysql process list?
Answer

  • SHOW PROCESSLIST\G;
  • DISPLAY PROCESSLIST\G;
  • SHOW PROCESSLISTG();
  • DISPLAY PROCESSLISTG();

Which SQL statement is used to return only different values?
Answer

  • SELECT UNIQUE
  • SELECT DISTINCT
  • SELECT DIFFERENT
  • SELECT DIFF

What is the correct syntax to get the MySQL Version?
Answer

  • SELECT VERSION
  • SELECT VERSION();
  • SELECT VERSION FROM mysql
  • None of the mentioned

Which of the following command is used to create a stored routine?
Answer

  • CREATE ROUTINE
  • CREATE PROCDURE
  • CREATE FUNCTION
  • None of the mentioned

InnoDB prevents which of the following operations when innodb_force_recovery is greater than 0?
Answer

  • INSERT
  • UPDATE
  • DELETE
  • SELECT

Which of the following are valid encryption functions?
Note: There may be more than one right answer.
Answer

  • MD5
  • AES_ENCRYPT
  • SHA512
  • SHA2

Which database provides a standards-compliant means for accessing the MySQL Server’s metadata?
Answer

  • INFORMATION_SCHEMA
  • META_SCHEMA
  • INFORMATION_METADATA
  • METADATA_SCHEMA

How do you control the maximum size of a heap table?
Answer

  • MySQL config variable called heap_table_size
  • MySQL config variable called max_heap_table_size
  • MySQL config variable called max_table_size
  • None of the above.

What is the syntax to backup a database from command line?
Answer

  • mysql [option] database_name > database_name.sql
  • mysqldump [option] database_name > database_name.sql
  • mysqlbackup [option] database_name > database_name.sql
  • mysqldump [option] database_name >> database_name.sql

What is the effective maximum length of a VARCHAR column?
Answer

  • 65,535 bytes
  • 64 bytes
  • 255 bytes
  • 256 bytes

Which of the following is not a valid BLOB type?
Answer

  • TINYBLOB
  • MEDIUMBLOB
  • LONGBLOB
  • LARGEBLOB

MYISAM tables can have three different storage formats: static (fixed-length), dynamic or compressed. Which of the following statements are true for static format?
Note: There may be more than on right answer
Answer

  • It is very quick and easy to cache.
  • There are no limitations regarding column types.
  • It requires much less disk space than the other two storage formats.
  • It is easy to reconstruct after a crash, because rows are located in fixed position.

Which of the following is not a valid trigger?
Answer

  • Before Insert
  • Before Update
  • Before Drop
  • Before Delete

Which of the following statement is valid for % and _ in the like clause in a mysql query?
Answer

  • % corresponds to 1 or more characters; _ is exactly one character
  • % corresponds to 0 or more characters; _ is exactly one character
  • % corresponds to 1 or more characters; _ is 0 or more character
  • % is exactly one characters; _ is corresponding to 0 or more character

Which variable can be modified to change the maximum size of a heap table?
Answers

  • heap_table_size
  • max_heap_table_size
  • max_table_size
  • None of the above.

How do you print the version of MySQL server?
Answer

  • SELECT VERSION()
  • SELECT MYSQL_VERSION()
  • SELECT –VERSION
  • None of the above

What are the valid types of table present in MySQL? Check all that apply?
Answer

  • MyISAM
  • Heap
  • Merge
  • INNO DB
  • ISAM

What is the full form of ACL?
Answer

  • Access Control List
  • Access Control Library
  • Access Center Library
  • Access Center List

What is the default TCP/IP port on which the MySQL server listens for connections?
Answer

  • 80
  • 8080
  • 3306
  • 556

How do you return fifty events starting from 40th? Assume table name is events and you need to select only the column event_title()?
Answer

  • SELECT event_title FROM events LIMIT 39,50;
  • SELECT event_title FROM events LIMIT 40,50;
  • SELECT event_title FROM events LIMIT [40,50];
  • SELECT event_title FROM events LIMIT [50,40];

MySQL supports some extensions that you probably cannot find in other SQL DBMs. Which SQL statements are part of these extensions?
Note: There may be more than right answer.
Answer

  • ALTER EVENT, CREATE EVENT DROP EVENT
  • ALTER SERVER, CREATE SERVER AND DROP SERVER
  • CREATE DATABASE, DROP DATABASE AND ALTER DATABASE
  • ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE

What is the correct syntax to turn on the global event scheduler?
Answer

  • SET GLOBAL event_scheduler = ON;
  • SET GLOBAL event_scheduler = True;
  • SET GLOBAL EVENT SCHEDULER = ON;
  • SET GLOBAL EVENT SCHEDULER = True;

Heap table support which of the following data types?
Note: There may be more than one right answer
Answer

  • BLOB
  • FLOAT
  • TEXT
  • BIT

LEAVE A REPLY

Please enter your comment!
Please enter your name here