Do you know that? 85% of your financial success is down to your EQ, MQ & BQ, only 15% can be attributed to IQ next

Contact Email: teammmrem@yahoo.com

<< All Upwork (oDesk) and Elance Tests << Elance test answers << IT & Programming category

Test answers for MySQL 2020

(167, CL) Last updated: January 27
Elance • IT & Programming
Questions and answers are regularly updated. Answered and not answered tests questions.

This helps getting job: Hundreds of (cover letter examples , interview questions , profile samples ) • Earn on Upwork (oDesk)
Job assistance: jobs popularityfreelance rates

Popular test answers: HTML, .Net, CSS, English, SEO, Photoshop, iOS, Java, Android, Ruby, Python, JavaScript

See all 6 tests answers updated

Find Upwork (oDesk) and Elance test answers on this website:



Collapse | Expand

167 Answered Test Questions:

1. What limits the type of data that goes into a table?

Answers:

• Constraints

• Prevents Restrictions Limitations

2. Which of the following queries returns all the rows whose names start with "S"?

Answers:

• SELECT * FROM table WHERE name LIKE = 'S*';

• None of these

• SELECT * FROM table WHERE name = 'S%';

• SELECT * FROM table WHERE name LIKE 'S%';

3. Which SQL statement is used to insert new data in a database?

Answers:

• Insert New

• Insert

• Insert into

• Insert data

4. What are the statements used to manage transaction processing in MySQL?

Answers:

• COMMIT and ROLLBACK

• SAVE and ROLLBACK

• SAVE and UNDO

• COMMIT and UNDO

5. With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

Answers:

• SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

• SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Persons

• SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen'

6. With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?

Answers:

• SELECT * FROM Persons WHERE FirstName LIKE '%a'

• SELECT * FROM Persons WHERE FirstName='a'

• SELECT * FROM Persons WHERE FirstName LIKE 'a%'

• SELECT * FROM Persons WHERE FirstName='%a%'

7. Which clause will order results by the last_name column?

Answers:

• ORDER BY last_name

• SELECT BY last_name

• WHERE last_name IS ALPHABETICAL

• All of these

8. What function eliminates beginning and ending white space characters from a string?

Answers:

• LTRIM()

• MTRIM()

• STRIP()

• None of these

• TRIM()

9. What locks can be applied to MyISAM tables?

Answers:

• Print

• Write

• Read and Write

• Read and Print

10. How do you rename a table?

Answers:

• DELETE

• UPDATE

• INSERT

• CHANGE

• ALTER

11. What is the maximum number of constituents for a SET datatype?

Answers:

• 62

• 64

• 60

• 66

12. With SQL, how can you return the number of records in the "Persons" table?

Answers:

• SELECT COUNT(*) FROM Persons

• SELECT COLUMNS(*) FROM Persons

• SELECT COUNT() FROM Persons

• SELECT COLUMNS() FROM Persons

13. How would you write a query to select all teams that won either 2, 4, 6 or 8 games?

Answers:

• SELECT * FROM teams WHERE team_won on (2, 4, 6, 8);

• SELECT * FROM teams WHERE team_won like (2, 4, 6, 8);

• SELECT * FROM teams WHERE team_won IN (2, 4, 6, 8);

• SELECT * FROM teams WHERE team_won join (2, 4, 6, 8);

14. Which SQL statement is used to return only different values?

Answers:

• SELECT DIFFERENT

• SELECT DISTINCT

• SELECT UNIQUE

15. Which port does MySQL Server use by default?

Answers:

• 8080

• 443

• 3306

• 80

16. Which SQL keyword is used to sort the result-set?

Answers:

• SORT BY

• SORT

• ORDER

• ORDER BY

17. How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

Answers:

• UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

• MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen'

• UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen'

• MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen

18. A SELECT statement without a WHERE clause returns _____.

Answers:

• All of the rows from a table.

• An error, because a SELECT statement is invalid without a WHERE clause.

• Nothing.

• All of the rows from a table that match the previous WHERE clause.

19. Which SQL statement is used to extract data from a database?

Answers:

• Select

• Get

• Extract

• Open

20. With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?

Answers:

• SELECT * FROM Persons ORDER FirstName DESC

• SELECT * FROM Persons SORT BY 'FirstName' DESC

• SELECT * FROM Persons ORDER BY FirstName DESC

• SELECT * FROM Persons SORT 'FirstName' DESC

21. A datatype can be changed after a table has been created.

Answers:

• False

• True

22. With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?

Answers:

• INSERT INTO Persons ('Olsen') INTO LastName

• INSERT INTO Persons (LastName) VALUES ('Olsen')

• INSERT ('Olsen') INTO Persons (LastName)

23. Which is TRIGGER in Mysql ?

Answers:

• Before Insert

• All

• Before Update

• After Delete

24. * in a SELECT statement portion does what?

Answers:

• Assign a Pointer

• Group By

• Multiply

• Nothing

• Select All

25. Which of the following statements about Stored Procedures is true?

Answers:

• All of these

• They allow you to store libraries of functions inside the database server

• They can provide a common set of database operations to multiple applications

• They can improve security by limiting users to specific approved queries

26. Which SQL statement is used to delete data from a database?

Answers:

• COLLAPSE

• DELETE

• REMOVE

• ERASE

27. Which SQL statement is used to extract data from a database?

Answers:

• GET

• OPEN

• SELECT

• EXTRACT

28. With SQL, how do you select a column named "FirstName" from a table named "Persons"?

Answers:

• SELECT Persons.FirstName

• SELECT FirstName FROM Persons

• EXTRACT FirstName FROM Persons

29. How do you remove a table from the database

Answers:

• DROP TABLE

• DELETE TABLE

• REMOVE TABLE

• DESTROY TABLE

30. With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?

Answers:

• SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter'

• SELECT [all] FROM Persons WHERE FirstName='Peter'

• SELECT * FROM Persons WHERE FirstName='Peter'

• SELECT * FROM Persons WHERE FirstName<>'Peter'

31. Which SQL statement is used to update data in a database?

Answers:

• Alter

• Update

• Save as

• Save

32. It is possible to add a constraint after a table is created

Answers:

• true

• false

33. Which of the following is not a valid aggregate function?

Answers:

• MAX

• COMPUTE

• COUNT

• MIN

34. A table column can have null values

Answers:

• false

• true

35. With SQL, how can you insert a new record into the "Persons" table?

Answers:

• INSERT ('Jimmy', 'Jackson') INTO Persons

• INSERT VALUES ('Jimmy', 'Jackson') INTO Persons

• INSERT INTO Persons VALUES ('Jimmy', 'Jackson')

36. With SQL, how do you select all the columns from a table named "Persons"?

Answers:

• SELECT * FROM Persons

• SELECT [all] FROM Persons

• SELECT *.Persons

• SELECT Persons

37. With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

Answers:

• DELETE FirstName='Peter' FROM Persons

• DELETE ROW FirstName='Peter' FROM Persons

• DELETE FROM Persons WHERE FirstName = 'Peter'

38. What does SQL stand for?

Answers:

• Strong Question Language

• Structured Question Language

• Structured Query Language

39. What does SQL mean?

Answers:

• Structured Query Language

• Simplified Query Language

40. Which SQL statement is used to update data in a database?

Answers:

• SAVE

• SAVE AS

• UPDATE

• MODIFY

41. Which SQL statement is used to insert new data in a database?

Answers:

• INSERT NEW

• INSERT INTO

• ADD RECORD

• ADD NEW

42. What identifies a row in a table as unique?

Answers:

• Main Key

• Regular Key

• Primary Key

• Foreign Key

43. Which of the following is/are true about MyISAM vs InnoDB?

Answers:

• Both MyISAM and InnoDB can use full-text index searches

• All of these are true

• InnoDB tables can have relationship constraints, MyISAM tables do not

• You can have both types of tables in one database

44. To select the multiple values from a column as a string use the _________ function.

Answers:

• IMPLODE()

• JOIN()

• COMBINE()

• GROUP_CONCAT()

45. Which of the following is a synonym for "INDEX" in MySQL.

Answers:

• CURSOR

• POINTER

• KEY

• UNIQUE

• STORE

46. Which of the following options for the INSERT statement will cause an existing row to be updated in the event of  a duplicate Primary Key value?

Answers:

• ON REPEAT VALUE UPDATE

• ON REPEAT KEY UPDATE

• ON DUPLICATE VALUE UPDATE

• ON DUPLICATE KEY UPDATE

47. When joining tables using a left join, which values are always preserved?

Answers:

• Values from the right table are preserved for NULL values in the right table

• The join will delete rows which contain a NULL value in the right table and preserve all other rows

• Values from the left table are preserved for NULL values in the right table

• The join will delete rows which contain a NULL value in the left table and preserve all other rows

48. Which of the following table declarations creates column 'name' to hold a fixed-length string?

Answers:

• CREATE TABLE test (column: name, type:CHAR(20));

• CREATE TABLE test (VARCHAR(20));

• CREATE TABLE test ( name(20) );

• CREATE TABLE test (name CHAR(20));

49. A ________ is a stored program that is attached to a table or a view.

Answers:

• trigger

• pseudofile

• None of the above is correct.

• embedded SELECT statement

50. How can you do an UPDATE?

Answers:

• UPDATE tab SET f1=1 AND f2=2;

• UPDATE tab.f1=1 , tab.f2=2;

• UPDATE tab.f1=1 AND tab.f2=2;

• UPDATE tab SET f1=1 , f2=2;

51. HAVING clause is used in combination with _____.

Answers:

• GROUP BY

• SELECT

• WHERE

• FROM

52. _____ displays the hour as an integer with zero padding, in 24H format.

Answers:

• %H

• %h

• %K

• %k

53. Which of the following is the correct way to retrieve rows with no duplicates?

Answers:

• SELECT * FROM TableA WHERE ColumnX IS DISTINCT;

• SELECT * FROM TableA WHERE ColumnX=UNIQUE("SomeValue");

• SELECT DISTINCT * FROM TableA WHERE ColumnX="SomeValue";

• SELECT UNIQUE * FROM TableA WHERE ColumnX="SomeValue";

• SELECT * FROM TableA WHERE ColumnX IS UNIQUE;

54. What is the BDB Configuration for RAM dedicated to holding rows and indexes?

Answers:

• bdb_max_lock

• bdb_cache_size

• bdb_logdir

• bdb_home

55. The data type BLOB stands for:

Answers:

• None of these

• Binary Large Object

• Big List Object

• Binary List Object

56. What is the default port number for MySQL?

Answers:

• 80

• 3396

• 3306

• 8080

• 3006

57. What does DCL stand for?

Answers:

• Data Context Language

• Define Context Language

• Define Control Language

• Data Control Language

58. What statement would delete the data in a table without deleting the table structure?

Answers:

• Alter Table

• Delete Table

• Drop Table

• Truncate Table

59. What represents an unknown or unspecified value?

Answers:

• UNSPEC

• NULL

• UNSPECIFIED

• ANY

• ()

60. How would you select all the user_name values from records whose user_phonenumber field is null?

Answers:

• Any of these three statements may work, depending on the version of MySQL being used.

• SELECT user_name FROM users WHERE user_phonenumber;

• SELECT user_name FROM users WHERE user_phonenumber IS NULL;

• SELECT user_name FROM users WHERE user_phonenumber = NULL;

61. How would you write a query to select all orders that contain either 3, 5, 7 or 9 items?

Answers:

• SELECT * FROM orders WHERE LIMIT item_count (3, 5, 7, 9);

• SELECT * FROM orders WHERE item_count = (3, 5, 7, 9);

• SELECT * FROM orders WHERE item_count LIKE (3, 5, 7, 9);

• SELECT * FROM orders WHERE item_count IN (3, 5, 7, 9);

62. You have just inserted a new row into a table whose Primary Key has the AUTO_INCREMENT property, without specifying the Primary Key. How would you retrieve the new row's Primary Key from the database?

Answers:

• SELECT LAST_KEY_INC();

• GET_AUTO_ID();

• SELECT LAST_INSERT_ID();

• You can't; the row creation would have failed with a Key Constraint error.

63. What is the difference between an inner join and an outer join?

Answers:

• Inner join deletes rows that don't match; outer join does not delete any rows

• Inner join returns only rows from both tables that match on the given conditional expression

• Inner join returns only rows from the first table; outer join returns rows from both tables

64. Which option gets the current time in the following format? 2012-06-18 19:47:31

Answers:

• GET_FORMAT(NOW(), '%Y-%m-%d %T')

• FORMAT_DATE(NOW(), '%Y-%m-%d %T')

• UNIX_TIMESTAMP(NOW())

• DATE_FORMAT(NOW(), '%Y-%m-%d %T');

65. What is the maximum length of a table name ?

Answers:

• 16 byte

• 64 byte

• 4 byte

• 32 byte

66. True or False? A table may be joined to itself.

Answers:

• True

• False

67. The _____ statement enables listing of tables that match a certain pattern.

Answers:

• SHOW OPEN TABLES

• SHOW DATABASE

• SHOW COLUMNS

• SHOW TABLES

68. Having a key with a unique value for each row, best describes?

Answers:

• FIRST KEY

• REFERENCE KEY

• UNIQUE KEY

• FOREIGN KEY

• PRIMARY KEY

69. The correct way to average all of the values from a column "values" from table "foo" is

Answers:

• SELECT AVERAGE(values) FROM foo

• SELECT values.AVERAGE FROM foo

• SELECT AVG(values) FROM foo

• SELECT AVERAGE OF values FROM foo

70. Which statement is required to add a column to an existing table?

Answers:

• CREATE

• ALTER

• APPEND

• UPDATE

• NEW

71. The USE keyword is used to select a _____.

Answers:

• Row

• Table

• Column

• Database

72. Which of these is not a valid name for a column

Answers:

• Near

• Under

• From

• Far

73. Which of the following can add a row to a table?

Answers:

• Alter

• Add

• Update

• Insert

74. The __________ application performs verification, repair, analysis, and optimization of MySQL databases, tables, and data.

Answers:

• mysqlshow

• mysqladmin

• mysqldump

• mysqlcheck

75. Which of the following is a correct use of the UNION operator?

Answers:

• SELECT * FROM Table A UNION TableB

• SELECT * FROM TableA UNION SELECT * FROM TableB

• SELECT * FROM UNION TableA, TableB

• SELECT UNION * FROM TableA, TableB

76. Which one of the following statements is correct?

Answers:

• All of these

• The ROLLUP modifier can only be used with the GROUP BY clause

• The WITH ROLLUP modifier will add an extra row to the results of a query.

77. What tests whether integer column i is at least 1 but no more than 10?

Answers:

• i IN INTERVAL(1, 10)

• i IN INTERVAL(0, 11)

• i BETWEEN 0 AND 11

• i BETWEEN 1 AND 10

78. Which set operator is used to combine the result sets of two or more SELECT statements?

Answers:

• None of these

• UNION

• COMBINE

• MERGE

79. What is a surrogate Key ?

Answers:

• A key that acts as a substitution to other natural keys

• a type of join

• Same as Natural key

• Same as Primary key

80. Which of the following statements about MySQL optimization is True?

Answers:

• If GROUP BY is not used, the HAVING and WHERE clauses will be merged

• Each table Index is queried, and the best Index is used.

• Constant expressions used with Indexes are only evaluated once

• All of these

81. The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true

Answers:

• False

• False-True

• True-False

• True

82. Which of the following correctly calls the stored procedure "test" which has no parameters?

Answers:

• CALL test;

• Set @variable = test;

• SELECT test;

• SELECT test FROM procedures;

83. Which of the following clauses can be used in a SELECT statement to reduce the results to a unique list of values?

Answers:

• UNIQUE

• REDUCE

• PRIMARY

• DISTINCT

84. I would like to retrieve the date with this format : dd/mm/YYYY in a field called btimestamp. Choose the correct way.

Answers:

• SELECT FORMAT_DATE(btimestamp,'%d/%m/%Y') AS bdate FROM ...

• SELECT DATE_FORMAT(btimestamp,'%d/%m/%Y') AS bdate FROM ...

85. When a MySQL database is "normalized", by common definition, it means which of the following?

Answers:

• That the data returned within any given row is converted to a single object type as most appropriate (string, int, etc.).

• That the database is organized in a way to reduce data redundancy. 1NF, 2NF, and 3NF forms are used as common guidelines.

• That the MySQL "strict" mode is invoked to avoid incompatibilities with legacy SQL.

• That non-common characters are stripped from values to ensure compatibility with given character set.

• That common queries assigned to functions to simplify statements.

86. Which of the following is used to count the number of rows with different items?

Answers:

• count(distinct)

• count(<column name>)

• count(*)

• count(all)

87. Which of the following is used to control case sensitivity?

Answers:

• binary

• bit_or()

• bit_count()

• bit_and()

88. Assign the custom `foo` variable with the value `bar`

Answers:

• SET @foo = 'bar';

• VAR @foo = 'bar';

• VAR $foo ='bar';

• SET $foo= 'bar';

• $foo = 'bar';

89. What does this statement: DELETE FROM xpto WHERE 1;

Answers:

• Delete all records in table 'xpto' and reset the AUTO_INCREMENT value to 0;

• Delete all records in table 'xpto' but does not reset the AUTO_INCREMENT sequence

• Throw an error depending on sql mode

• Delete all records in table 'xpto' and resets the AUTO_INCREMENT sequence

90. True of false? TEXT is case-insensitive in BLOB.

Answers:

• False

• True

91. Which of the following are true concerning indexes?

Answers:

• Indexes can only be done on primary key columns

• Indexes will slow down selects, but speed up inserts

• Indexes can only be done on columns containing numerical data

• Indexes will slow down inserts, but speed up selects

• None are correct

92. What does the following command output: 'SELECT 1'

Answers:

• "1"

• 1

• '1'

93. Which of the following queries returns 100 results, starting from the 25th result?

Answers:

• SELECT book_title FROM books LIMIT 25 UP TO 100

• SELECT book_title FROM books LIMIT 25, 125

• SELECT book_title FROM books LIMIT 24, 100

• SELECT book_title FROM books LIMIT 24 TO 124

94. How would you remove all rows from a table "STAFF"

Answers:

• REMOVE * FROM STAFF

• DELETE ALL STAFF

• DELETE FROM STAFF

• REMOVE FROM STAFF

• DELETE * FROM STAFF

95. Which of the following is used to access a result set stored in memory after execution of a query, allowing the result data to be manipulated on a row-by-row basis?

Answers:

• Cursor

• View

• Trigger

• Index

96. What happens when a Unique or Primary Key column is set to AUTO INCREMENT, and an INSERT would exceed the maximum value for that column?

Answers:

• Further rows will be inserted with the same value.

• The value "wraps around" to the lowest value and continues incrementing.

• None of these

• It stops incrementing, producing a duplicate key error.

97. In a WAMP environment, what is the correct way to permanently change the largest possible packet that can be transmitted to or from a MySQL server?

Answers:

• Edit the my.ini file and change the setting max_allowed_packet. Then restart the service.

• Using the Windows command prompt, execute "SET GLOBAL max_allowed_packet=MB;". You do not need to restart the service when changing globals directly on the server.

• Edit the my.ini file and change the setting max_allowed_packet=M. You do not need to restart the service when changing globals directly on the server.

• Open the MySQL network data config tool and simply change the setting. The service will be restarted automatically.

• Using the Windows command prompt, execute "SET max_allowed_packet=M;". Then restart the service.

98. The user-created stored function 'test' takes a varchar(5) and an integer and returns a varchar. Which of the following correctly calls the function?

Answers:

• CALL FUNCTION test ('asdfg', 6);

• SELECT test('asdfg', 6);

• CALL test('asdfg', 6);

• SELECT test USING 'asdfg', 6;

99. What term describes the number of unique values in an index?

Answers:

• Sub-part

• Seq-in-index

• Collation

• Cardinality

100. Which foreign keys do MyISAM tables support?

Answers:

• Foreign keys on the primary key only

• Foreign keys on columns other than the primary key.

• Foreign keys on indexed columns.

• None, MyISAM tables do not support foreign keys.

101. Given two tables, t1 and t2, the join condition "t1.t2_id = t2.id" how do you get rows from t1 that have NO counterparts in t2?

Answers:

• JOIN t2 EXCLUDING t1.t2_id = t2.id

• LEFT JOIN t2 ON t1.t2_id = t2.id WHERE t2.id IS NULL

• RIGHT JOIN t2 ON t1.t2_id = t2.id

• EXCLUDING JOIN t2 ON t1.t2_id = t2.id

• LEFT OUTER JOIN t2 ON t1.t2_id = t2.id

102. Which of the following statements can be used to remove user accounts from MySQL?

Answers:

• None of these

• DELETE USER user;

• REVOKE USER user;

• DROP USER user;

103. What command executed by a client exports data to a file?

Answers:

• SELECT ... INTO OUTFILE

• EXPORT ... INTO OUTFILE

• COPY ... INTO OUTFILE

• MOVE ... INTO OUTFILE

104. The _____________ privilege is often used when loading a file into a database table.

Answers:

• Process_priv

• File_priv

• Grant_priv

• Reload_priv

105. What is a major difference between MyISAM and InnoDB storage engines?

Answers:

• InnoDB is innovative where MyISAM is not.

• InnoDB uses table locking where MyISAM uses row locking.

• InnoDB uses row locking where MyISAM uses table locking.

• All of these.

• None of these.

106. When using the grant command which phrase sets the password of the user?

Answers:

• password

• using

• Identified By

• SET passwd

• passwd

107. Which of the following MySQL client command line arguments would you use to connect to a server?

Answers:

• % mysql servers [options]

• % mysql [options]

• % mysql server [options]

• % mysql connect [options]

108. The GROUP BY clause of a SELECT statement requires which of the following?

Answers:

• ORDER BY clause

• WHERE clause

• LIMIT clause

• HAVING clause

• None of these

109. What is the maximum allowed length of a column name?

Answers:

• 64

• 255

• 128

• 256

110. How would you select a value that lets column B (if specified) override column A?

Answers:

• COALESCE(A, B)

• B IF A IS NOT NULL

• A IF B IS NOT NULL

• COALESCE(B, A)

111. Which of these queries will return the values of the "order" column in the "slides" table in descending order?

Answers:

• All of these return identical results

• SELECT order FROM `slides` ORDER BY order DESC

• SELECT -order FROM -slides ORDER BY -order DESC

• SELECT `order` FROM `slides` ORDER BY `order` DESC

112. Which is NOT an available storage engine?

Answers:

• InnoDB

• CSV

• BDB

• MyISAM

• SQLite

113. In a LIKE clause, you can ask for any 6 letter value by writing:

Answers:

• LIKE .{6} Answer 5: LIKE ^.{6}$

• LIKE ______ (six underscore characters)

• LIKE ??????

• LIKE ...... (six periods)

114. Which of the following correctly redirects the results of a select statement to a file?

Answers:

• SELECT * FROM TableA where SomeColumn="a value" INTO OUTFILE 'out.txt';

• SELECT * INTO OUTFILE 'out.txt' FROM TableA where SomeColumn="a value";

• SELECT * FROM TableA where SomeColumn="a value" AND OUTFILE = 'out.txt';

115. Within a transaction, how do you apply an exclusive lock with a SELECT?

Answers:

• SELECT ... DELETE

• SELECT ... ALTER

• SELECT ... FOR UPDATE

• SELECT ... EXCLUSIVE

116. How do you get the execution plan of a query?

Answers:

• EXPLAIN SELECT select_options

• DESCRIBE SELECT select_options

• It's not possible

• QUERY_PLAN SELECT select_options

• Both EXPLAIN and DESCRIBE will work

117. Which one of these queries will result in an error?

Answers:

• SELECT NULL

• SELECT 0

• SELECT *

• All are incorrect, because they are missing the FROM clause

• SELECT ''

118. Which of the following statements about Views is FALSE?

Answers:

• Their SELECT statements cannot contain subqueries in the FROM clause.

• They can save time and effort on queries that are frequently executed.

• They are not connected to a SELECT statement

• A trigger cannot be associated with a View.

119. What switch will prevent MySQL from listening via TCP?

Answers:

• networking false

• protocol tcp=false

• skip-tcp

• skip-networking

• networking=off

120. How can you set "less" utility as the default one for reading queries results?

Answers:

• Pager less

• Redirect less

• Reader less

• Set less

• Viewer less

121. If you specify a column type as DECIMAL (6,2), what range of values will it accept?

Answers:

• -999.99 to 99.99

• -999999.99 to 999999.99

• -9999.99 to 999.99

• -9999.99 to 9999.99

122. What MySQL variable is used to limit a single SQL statement's size?

Answers:

• max_allowed_packet

• max_query_length

• max_length_query

• max_packet_size

• max_key_buffer

123. What will the following SQL return for the `part` column: SELECT SUBSTRING_INDEX('my.delimited.string', '.', -1) AS `part`

Answers:

• my.delimited

• my

• string

• delimited.string

124. Which statements are used for changing the default character set within a table?

Answers:

• ALTER ... CONVERT

• CHANGE ... COLLATION

• SET ... COLLATION

• ALTER ... SET

• MODIFY ... CONVERT

125. Which statement returns the current date in YYYY-MM-DD format?

Answers:

• SELECT CURDATE();

• SELECT DATE();

• SELECT NOW();

• All of these

126. When executing an equi-join, what syntax can you use to specify the order in which the tables are joined?

Answers:

• STRAIGHT_JOIN

• OUTER JOIN

• LEFT OUTER JOIN

• MULTIPLE JOIN

127. Within a transaction, how do you apply an exclusive lock with a SELECT?

Answers:

• SELECT ... FOR UPDATE

• SELECT ... ALTER

• SELECT ... DELETE

• SELECT ... EXCLUSIVE

128. Which of the following statements about Views is FALSE?

Answers:

• A trigger cannot be associated with a View.

• They can save time and effort on queries that are frequently executed.

• Their SELECT statements cannot contain subqueries in the FROM clause.

• They are not connected to a SELECT statement

129. Which one of the following flags causes the MySQL engine to refuse UPDATE and DELETE commands that don't include a WHERE clause?

Answers:

• --no-blank

• None of these

• --i-am-a-dummy

• --safe-delete

130. What does this query result in? SELECT IF( 1, 2, 3 ) + IF(0, 1, 2 ) ;

Answers:

• 9

• 3

• Syntax Error

• 4

• 5

131. Which date calculation interval works on days, hours, and minutes?

Answers:

• DAY_HOUR_MINUTE

• DAY_MINUTE

• DAY_SECOND

• DAY_ALL

132. The __________ function allows the addition of months to a given date.

Answers:

• PERIOD_ADD

• MONTH_DIFF

• MONTH_ADD

• PERIOD_DIFF

133. Which of the following queries returns the String "come"?

Answers:

• SELECT SUBSTRING ('welcome to MySQL', 4, 7);

• SELECT SUBSTRING ('welcome to MySQL' FROM -4 FOR 2);

• None of these

• SELECT SUBSTRING ('welcome to MySQL', 4);

134. The _______________ function returns the time in the HH:MM:SS format from the given number of seconds.

Answers:

• SYSDATE (seconds)

• SEC_TO_TIME (seconds)

• NOW (seconds)

• TIME_FORMAT (seconds)

135. What privilege must you have to execute a FLUSH statement?

Answers:

• GRANT_OPTION

• RELOAD

• ALTER

• DROP

• PROCESS

136. If you grant a user FILE priveliges, you're allowing:

Answers:

• Read files on the server, write new files and append to existing ones

• Read and write (but not overwrite) files on the server

• Read files on the server, but not write them

• Read, write and overwrite files on the server

137. Which one of the following data types causes a column to have the NOT NULL, PRIMARY KEY and AUTO_INCREMENT properties?

Answers:

• INDEX

• PRIMARY

• UNIQUE

• SERIAL

138. A MySQL server supports connection pooling.

Answers:

• False

• True

139. Which option gets the current time in the following format? 2012-06-18 19:47:31

Answers:

• UNIX_TIMESTAMP(NOW())

• GET_FORMAT(NOW(), '%Y-%m-%d %T')

• FORMAT_DATE(NOW(), '%Y-%m-%d %T')

• DATE_FORMAT(NOW(), '%Y-%m-%d %T');

140. You have just inserted a new row into a table whose Primary Key has the AUTO_INCREMENT property, without specifying the Primary Key. How would you retrieve the new row's Primary Key from the database?

Answers:

• You can't; the row creation would have failed with a Key Constraint error.

• GET_AUTO_ID();

• SELECT LAST_INSERT_ID();

• SELECT LAST_KEY_INC();

141. I would like to retrieve the date with this format : dd/mm/YYYY in a field called btimestamp. Choose the correct way.

Answers:

• SELECT DATE_FORMAT(btimestamp,'%d/%m/%Y') AS bdate FROM ...

• SELECT FORMAT_DATE(btimestamp,'%d/%m/%Y') AS bdate FROM ...

142. When a MySQL database is "normalized", by common definition, it means which of the following?

Answers:

• That common queries assigned to functions to simplify statements.

• That the database is organized in a way to reduce data redundancy. 1NF, 2NF, and 3NF forms are used as common guidelines.

• That non-common characters are stripped from values to ensure compatibility with given character set.

• That the MySQL "strict" mode is invoked to avoid incompatibilities with legacy SQL.

• That the data returned within any given row is converted to a single object type as most appropriate (string, int, etc.).

143. In linux how do you restart the MySQL service?

Answers:

• restart mysql service init 0 reboot

• /etc/init.d/mysql restart service mysqld restart

144. Which SQL statement is used to insert new data in a database?

Answers:

• Insert

• Insert data

• Insert into

• Insert New

145. Which SQL statement is used to update data in a database?

Answers:

• Alter

• Update

• Save as

• Save

146. Can I create another MySQL instance in the same machine?

Answers:

• False

• True

147. With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?

Answers:

• SELECT * FROM Persons WHERE FirstName<>'Peter' AND LastName<>'Jackson'

• SELECT FirstName='Peter', LastName='Jackson' FROM Persons

• SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

148. Which SQL statement is used to update data in a database?

Answers:

• SAVE AS

• UPDATE

• SAVE

• MODIFY

149. Which SQL statement is used to insert new data in a database?

Answers:

• ADD NEW

• ADD RECORD

• INSERT INTO

• INSERT NEW

150. What does the following command output: 'SELECT 1'

Answers:

• 1

• '1'

• "1"

151. What is A in ACID?

Answers:

• Analysis Allow Alter

• Atomicity

152. Which of the following is the correct way to retrieve rows with no duplicates?

Answers:

• SELECT DISTINCT * FROM TableA WHERE ColumnX="SomeValue";

• SELECT * FROM TableA WHERE ColumnX=UNIQUE("SomeValue");

• SELECT * FROM TableA WHERE ColumnX IS UNIQUE;

• SELECT UNIQUE * FROM TableA WHERE ColumnX="SomeValue";

• SELECT * FROM TableA WHERE ColumnX IS DISTINCT;

153. What is the difference between an inner join and an outer join?

Answers:

• Inner join returns only rows from the first table; outer join returns rows from both tables

• Inner join deletes rows that don't match; outer join does not delete any rows

• Inner join returns only rows from both tables that match on the given conditional expression

154. Which of the following is not a Spatial Data Type?

Answers:

• CIRCLE

• GEOMETRY CIRCLE POINT POLYGON

155. Assign the custom `foo` variable with the value `bar`

Answers:

• $foo = 'bar';

• SET @foo = 'bar';

• VAR $foo ='bar';

• SET $foo= 'bar';

• VAR @foo = 'bar';

156. In a WAMP environment, what is the correct way to permanently change the largest possible packet that can be transmitted to or from a MySQL server?

Answers:

• Edit the my.ini file and change the setting max_allowed_packet. Then restart the service.

• Open the MySQL network data config tool and simply change the setting. The service will be restarted automatically.

• Edit the my.ini file and change the setting max_allowed_packet=M. You do not need to restart the service when changing globals directly on the server.

• Using the Windows command prompt, execute "SET GLOBAL max_allowed_packet=MB;". You do not need to restart the service when changing globals directly on the server.

• Using the Windows command prompt, execute "SET max_allowed_packet=M;". Then restart the service.

157. ______ - Nothing that???s being read is actively being changed by another transaction.

Answers:

• ISOLATION

• ACID Transaction Rollback

158. What does this statement: DELETE FROM xpto WHERE 1;

Answers:

• Delete all records in table 'xpto' but does not reset the AUTO_INCREMENT sequence

• Delete all records in table 'xpto' and resets the AUTO_INCREMENT sequence

• Delete all records in table 'xpto' and reset the AUTO_INCREMENT value to 0;

• Throw an error depending on sql mode

159. What is the maximum size of the buffer for client/server communication?

Answers:

• 2GB Unlimited 24MB

• 1GB

160. The user-created stored function 'test' takes a varchar(5) and an integer and returns a varchar. Which of the following correctly calls the function?

Answers:

• CALL FUNCTION test ('asdfg', 6);

• SELECT test USING 'asdfg', 6;

• CALL test('asdfg', 6);

• SELECT test('asdfg', 6);

161. The default size of the buffer for client/server communication is _______?

Answers:

• 1GB No Default Size 500MB

• 24MB

162. What happens when a Unique or Primary Key column is set to AUTO INCREMENT, and an INSERT would exceed the maximum value for that column?

Answers:

• Further rows will be inserted with the same value.

• The value "wraps around" to the lowest value and continues incrementing.

• It stops incrementing, producing a duplicate key error.

• None of these

163. Which of the following correctly redirects the results of a select statement to a file?

Answers:

• SELECT * FROM TableA where SomeColumn="a value" INTO OUTFILE 'out.txt';

• SELECT * FROM TableA where SomeColumn="a value" AND OUTFILE = 'out.txt';

• SELECT * INTO OUTFILE 'out.txt' FROM TableA where SomeColumn="a value";

164. What parameters needed to make mysqldump as a hot backup?

Answers:

• --hot-dump --no-lock-tables --databases-no-lock

• --single-transaction

165. How do you get the execution plan of a query?

Answers:

• Both EXPLAIN and DESCRIBE will work

• EXPLAIN SELECT select_options

• QUERY_PLAN SELECT select_options

• It's not possible

• DESCRIBE SELECT select_options

166. Which one of these queries will result in an error?

Answers:

• SELECT 0

• All are incorrect, because they are missing the FROM clause

• SELECT *

• SELECT NULL

• SELECT ''

167. Which of the following queries returns the String "come"?

Answers:

• SELECT SUBSTRING ('welcome to MySQL', 4);

• SELECT SUBSTRING ('welcome to MySQL' FROM -4 FOR 2);

• SELECT SUBSTRING ('welcome to MySQL', 4, 7);

• None of these