Хотите пройти тесты на Upwork на хороший результат?
Подавайте заявку и мы поможем Вам это сделать!

Do You want to pass tests on Upwork with good scores?
Leave the request and we will help You to do that!

MySQL Test 2017 (Top 10%)

Share on RedditPin on PinterestEmail this to someoneShare on TumblrShare on LinkedInShare on Google+Tweet about this on TwitterShare on FacebookShare on VK

Мы не гарантируем правильность всех предоставленных ответов.


225 test Questions:

1. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating(the popularity of the book on a scale of 1 to 10)
Language(such as French, English, German etc)

Subjects
———
SubjectId
Subject(such as History, Geography, Mathematics etc)

Authors
———
AuthorId
AuthorName
Country
Which is the query to determine the number of Authors who have written books on more than 2 subjects?

Answers:

• select AuthorName from Authors where Authorid in
(select Authorid from Books group by SubjectId having count(*)>1)
• select AuthorName from Authors where BookId in
(select BookId from Books group by BookId having count(*)>1)
• select AuthorName from Authors where Authorid in
(select Authorid from Books group by SubjectId,Authorid having count(*)>1)
• select AuthorName from Authors where Authorid in
(select Authorid from Books group by Authorid having count(*)>1)
• None of the above

2. Which of the following statements are true about SQL injection attacks?

Answers:

• Wrapping all variables containing user input by a call to mysql_real_escape_string() makes the code immune to SQL injections.
• Parametrized queries do not make code less vulnearable to SQL injections.
• SQL injections are not possible, if only emulated prepared statements are used.
• 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.

3. Which of the following is an alternative to Subquery Factoring (ex. the ‘WITH’ clause in MS SQL Server)?

Answers:

• The ‘IN’ clause
• Using temporary tables and inline views
• The ‘INNER JOIN’ clause
• Using subqueries

4. Suppose a table has the following records:

+————–+————-+—————-+
| Item | Price | Brand |
+————–+————-+—————-+
| Watch | 100 | abc |
| Watch | 200 | xyz |
| Glasses | 300 | bcd |
| Watch | 500 | def |
| Glasses | 600 | fgh |
+————–+————-+—————-+

Which of the following will select the highest-priced record per item?

Answers:

• select item, brand, price from items where max(price) order by item
• select * from items where price = max group by item
• select item, brand, max(price) from items group by item
• select * from items where price > 200 order by item

5. Which of the following will restore a MySQL DB from a .dump file?

Answers:

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

6. 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

Answers:

• 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
• select * from messages where name =Desc Result: 1 A A_data_1 2 A A_data_2 3 A A_data_3
• select * from messages group by name Result: 1 A A_data_1 4 B B_data_1 6 C C_data_1
• Answer A and B

7. How can an user quickly rename a MySQL database for InnoDB?

Answers:

• He cannot rename any MySQL database
• By using: RENAME DATABASE db_old_name TO db_new_name
• By using: RENAME DATABASE TO db_new_name
• By creating the new empty database, then rename each table using: RENAME TABLE db_old_name.table_name TO db_new_name.table_name

8. Is it possible to insert several rows into a table with a single INSERT statement?

Answers:

• No
• Yes

9. Consider the following tables:

books
——
bookid
bookname
authorid
subjectid
popularityrating (the popularity of the book on a scale of 1 to 10)
language (such as French, English, German etc)
Subjects
———
subjectid
subject (such as History, Geography, Mathematics etc)
authors
——–
authorid
authorname
country

Which is the query to determine the Authors who have written at least 1 book with a popularity rating of less than 5?

Answers:

• select authorname from authors where authorid in (select authorid from books where popularityrating<5)
• select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
• select authorname from authors where authorid in (select bookid from books where popularityrating<5)
• select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))

10. The Flush statement cannot be used for:

Answers:

• Closing any open tables in the table cache
• Closing open connections
• Flushing the log file
• Flushing the host cache

11. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?

Answers:

• SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
• SELECT * FROM Persons SORT REVERSE ‘FirstName’
• SELECT * FROM Persons ORDER BY -‘FirstName’
• SELECT * FROM Persons ORDER BY FirstName DESC

12. You want to display the titles of books that meet the following criteria:
1. Purchased before November 11, 2002
2. Price is less than $500 or greater than $900
You want to sort the result by the date of purchase, starting with the most recently bought book.
Which of the following statements should you use?

Answers:

• SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ‘2002-11-11’ ORDER BY purchase_date;
• SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< ‘2002-11-11’ ORDER BY purchase date ASC;
• SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
• SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ‘2002-11-11’ ORDER BY purchase_date DESC;

13. State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine

Answers:

• True
• False

14. Consider the following table structure of students:

rollno int
name varchar(20)
course varchar(20)

What will be the query to display the courses in which the number of students enrolled is more than 5?

Answers:

• Select course from students where count(course) > 5;
• Select course from students where count(*) > 5 group by course;
• Select course from students group by course;
• Select course from students group by course having count(*) > 5;
• Select course from students group by course where count(*) > 5;
• Select course from students where count(group(course)) > 5;
• Select count(course) > 5 from students;
• None of the above

15. Which of the following statements is true?

Answers:

• Replication can be a part of a load balancing strategy.
• Replication and clustering are complete synonyms in the context of MySQL server administration.
• MySQL supports only master-slave replication.
• MySQL does not support master-master replication.

16. Which of the following is not a valid Arithmetic operator?

Answers:

• +
• –
• *
• \ 
• %
• All are valid

17. Consider the query:

SELECT name
FROM Students
WHERE name LIKE ‘_a%’;

Which names will be displayed?

Answers:

• Names starting with “a”
• Names containing “a” as the second letter
• Names starting with “a” or “A”
• Names containing “a” as any letter except the first

18. Which of the following is the best MySQL data type for currency values?

Answers:

• SMALLINT
• DECIMAL(19,4)
• VARCHAR(32)
• BIGINT

19. What are MySQL Spatial Data Types in the following list?

Answers:

• GEOMETRY
• CIRCLE
• SQUARE
• POINT
• POLYGON

20. 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?

Answers:

• The two statements produce identical results
• The second statement returns an error
• There is no need to specify DESC because the results are sorted in descending order by default
• None of the above statments is correct

21. Can you run multiple MySQL servers on a single machine?

Answers:

• No
• Yes

22. Which of the following formats does the date field accept by default?

Answers:

• DD-MM-YYYY
• YYYY-DD-MM
• YYYY-MM-DD
• MM-DD-YY
• MMDDYYYY

23. Which of the following statements relating to Alias names is true?

Answers:

• Alias names are case sensitive
• Alias names are case in-sensitive
• Alias names are case sensitive on UNIX and not on Windows
• Alias names are case sensitive on Windows and not on UNIX
• Alias names case sensitivity depends on lower_case_table_names system setting

24. What is the name of the utility used to extract NDB configuration information?

Answers:

• ndb_config
• cluster_config
• ndb –config
• configNd
• None of the above

25. Examine the description of the STUDENTS table:

STD_ID INT
COURSE_ID VARCHAR (10)
START_DATE DATE
END_DATE DATE

The aggregate functions valid on the START_DATE column are:

Note: There may be more than one right answer.

Answers:

• SUM(start_date)
• AVG(start_date)
• COUNT(start_date)
• AVG(start_date, end_date)
• MIN(start_date)

26. Which of the following is the correct way to change the character set to UTF8?

Answers:

• Add the following lines in my.cnf:
[client]
default-character-set=utf8
[mysqld]
default-character-set = utf8

• dd the following lines in my.cnf:
[mysqld]
character_set_server = utf8

• Add the following lines in my.cnf:
[mysqld]
skip-character-set-client-handshake
character_set_client=utf8
character_set_server=utf8

• Add the following lines in my.cnf:
[mysqld]
init_connect=’SET collation_connection = utf8_unicode_ci’
character-set-server = utf8
collation-server = utf8_unicode_ci
[client]
default-character-set = utf8

27. State whether true or false:
In the ‘where clause’ of a select statement, the AND operator displays a row if any of the conditions listed are true. The OR operator displays a row if all of the conditions listed are true.

Answers:

• True
• False

28. To quote a string within a string, which of the following can you use?
Answers:

• «This is the «quoted» message»
• «This is the «»quoted»» message»
• ‘This is the «quoted» message’
• «This is the \»quoted\» message»

29. Which one of the following must be specified in every DELETE statement?

Answers:

• Table Name
• Database name
• LIMIT clause
• WHERE clause

30. Which of the following will raise MySQL’s version of an error?

Answers:

• SIGNAL
• RAISE
• ERROR
• None of these.

31. Which query will return values containing strings “Pizza”, “Burger”, or “Hotdog” in the database?

Answers:

• SELECT * FROM fiberbox WHERE field REGEXP ‘Pizza|Burger|Hotdog’;
• SELECT * FROM fiberbox WHERE field LIKE ‘%Pizza%’ OR field LIKE ‘%Burger%’ OR field LIKE ‘%Hotdog%’;
• SELECT * FROM fiberbox WHERE field = ‘%Pizza%’ OR field = ‘%Burger%’ OR field = ‘%Hotdog%’;
• SELECT * FROM fiberbox WHERE field = ‘?Pizza?’ OR field = ‘?Burger?’ OR field = ‘?Hotdog?’;

32. Which datatype is used to store binary data in MySQL?

Answers:

• BLOB
• BIGINT
• INT
• Both BLOB and BIGINT

33. Which of the following will reset the MySQL password for a particular user?

Answers:

• UPDATE mysql.user SET Password=PASSWORD(‘password’) WHERE User=’username’;
• UPDATE mysql.user SET Password=’password’ WHERE User=’username’;
• UPDATE mysql.user SET Password=RESET(‘password’) WHERE User=’username’;
• None of the above.

34. Which of the following is the best way to modify a table to allow null values?

Answers:

• ALTER TABLE table_name MODIFY column_name varchar(255) null
• ALTER TABLE table_name MODIFY column_name VARCHAR(255)
• ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL
• ALTER table_name MODIFY column_name varchar(255) null

35. Which of the following will dump the whole MySQL database to a file?

Answers:

• mysql -e “select * from myTable” -u myuser -pxxxxxxxxx mydatabase > mydumpfile.txt
• mysql -e “select * from myTable” mydatabase > mydumpfile.txt
• SELECT * from myTable FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘n’
• None of the above.

36. Which of the following statements is true regarding character sets in MySQL?

Answers:

• The default character set of MySQL is UTF-8.
• lang.cnf sets the default character set for MySQL databases.
• SET CHARSET utf8 will set the character set of data to be imported to UTF-8.
• None of these.

37. Which of the following is an alternative to groupwise maximum ranking (ex. ROW_NUMBER() in MS SQL)?

Answers:

• Using subqueries
• Using variables in a MySQL query
• Using self-join
• MySQL also supports ROW_NUMBER()

38. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating (the popularity of the book on a scale of 1 to 10)
Language (such as French, English, German etc)
Subjects
———
SubjectId
Subject (such as History, Geography, Mathematics etc)
Authors
——–
AuthorId
AuthorName
Country

Which query will determine how many books have a popularity rating of more than 7 on each subject?

Answers:

• select subject,count(*) as Books from books,subjects where books.popularityrating > 7
• select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject
• select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject
• select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating > 7 group by subjects.subject

39. Which of the following are true in case of Indexes for MYISAM Tables?

Answers:

• Indexes can have NULL values
• BLOB and TEXT columns can be indexed
• Indexes per table cannot be more than 16
• Columns per index cannot be more than 16

40. What will happen if two tables in a database are named rating and RATING?

Answers:

• This is not possible as table names are case in-sensitive (rating and RATING are treated as same name)
• This is possible as table names are case sensitive (rating and RATING are treated as different names)
• This is possible on UNIX/LINUX and not on Windows platform
• This is possible on Windows and not on UNIX/LINUX platforms
• This depends on lower_case_table_names system variable

41. SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000;
Which of the following statements is correct with regard to this code?

Answers:

• It returns employees whose salary is 50% more than $23,000
• It returns employees who have 50% commission rate or salary greater than $23,000
• It returns employees whose salary is 50% less than $23,000
• None of the above

42. Select which of the following is the best way to duplicate MySql Database(DB1) in to another database(DB2) without using mysqldump?

Answers:

• Create the target database using MySQLAdmin,
• Execute the following MySql query CREATE TABLE t2 SELECT * FROM t1;
• Execute the following MySql query CREATE TABLE x LIKE other_db.y;
• CREATE TABLE DB2 SELECT * FROM DB1;

43. Which of the following statement will results in 0 (false)?

Answers:

• SELECT «EXPERTRATING» LIKE «EXP%»
• SELECT «EXPERTRATING» LIKE «Exp%»
• SELECT BINARY «EXPERTRATING» LIKE «EXP%»
• SELECT BINARY «EXPERTRATING» LIKE «Exp%»
• All will result in 1 (true)

44. Which query will display data from the Pers table relating to Analyst, Clerk and Salesman who joined between 1/1/2005 and 1/2/2005 ?

Answers:

• select * from Pers where joining_date from #1/1/2005# to #1/2/2005#, job=Analyst or clerk or salesman
• select * from Pers where joining_date between #1/1/2005# to #1/2/2005#, job=Analyst or job=clerk or job=salesm
• select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and
• None of the above

45. How can a InnoDB database be backed up without locking the tables?

Answers:

• mysqldump –single-transaction db_name
• mysqldump –force db_name
• mysqldump –quick db_name
• mysqldump –no-tablespaces db_name

46. What does the term “overhead” mean in MySQL?

Answers:

• Temporary diskspace that the database uses to run some of the queries
• The size of a table
• A tablespace name
• None of the above

47. Consider the following select statement and its output:

SELECT * FROM table1 ORDER BY column1;
Column1
——–
1
2
2
2
2
2
3

Given the above output, which one of the following commands deletes 3 of the 5 rows where column1 equals 2?

Answers:

• DELETE FIRST 4 FROM table1 WHERE column1=2
• DELETE 4 FROM table1 WHERE column1=2
• DELETE WHERE column1=2 LIMIT 4
• DELETE FROM table1 WHERE column1=2 LIMIT 3
• DELETE FROM table1 WHERE column1=2 LEAVING 1

48. 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?

Answers:

• Two tables are created
• 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
• Those queries are invalid
• If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted

49. What is NDB?

Answers:

• An in-memory storage engine offering high-availability and data-persistence features
• A filesystem
• An SQL superset
• MySQL scripting language
• None of the above

50. Which of the following statements are true?

Answers:

• Names of databases, tables and columns can be up to 64 characters in length
• Alias names can be up to 255 characters in length
• Names of databases, tables and columns can be up to 256 characters in length
• Alias names can be up to 64 characters in length

51. Which of the following statements is used to change the structure of a table once it has been created?

Answers:

• CHANGE TABLE
• MODIFY TABLE
• ALTER TABLE
• UPDATE TABLE

52. Which of the following will show when a table in a MySQL database was last updated?

Answers:

• Using the following query: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = ‘database_name’ AND TABLE_NAME = ‘table_name’
• 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 of these.

53. Which of the following results in 0 (false)?

Answers:

• “EXPERTRATING” LIKE “EXP%”
• “EXPERTRATING” LIKE “Exp%”
• BINARY “EXPERTRATING” LIKE “EXP%”
• BINARY “EXPERTRATING” LIKE “Exp%”
• All will result in 1 (true)

54. Which of the following is not a valid Bit operator?

Answers:

• &
• &&
• <<
• |
• >>

55. What is the main purpose of InnoDB over MyISAM?

Answers:

• InnoDB is thread safe
• InnoDB provides a transaction safe environment
• InnoDB can handle table with more than 1000 columns
• InnoDB provides FULLTEXT indexes
• None of the above

56. View the following Create statement:

1. Create table Pers
2. (EmpNo Int not null,
3. EName Char not null,
4. Join_dt Date not null,
5. Pay Int)

Which line contains an error?

Answers:

• 1
• 2
• 3
• 4
• 5
• No error in any line

57. Which of the following is not a String column types?

Answers:

• BLOB
• ENUM
• SET
• TEXT
• LONGCHAR

58. What will happen if some of the columns in a table are of char datatype and others are of varchar datatype?

Answers:

• Nothing will happen
• MySQL will generate an error
• MySQL will convert all varchar datatypes into char
• MySQL will convert all char datatypes into varchar

59. If you insert (00) as the value of the year in a date column, what will be stored in the database?

Answers:

• 0000
• 1900
• 2000
• Ambiguous, cannot be determined

60. Which of the following statements is true is regards to whether the schema integration problem between development, test, and production servers can be solved?

Answers:

• True, only can create migration solution in .NET programming language.
• True, it can be solve by migration solution. These solutions vary by programming language.
• Both A and B
• None of the above

61. Which of the following is not a valid Logical operator?

Answers:

• &
• &&
• AND
• !
• NOT

62. What is true regarding the TIMESTAMP data type?

Answers:

• For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value
• TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp
• When the server runs with the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME
• A TIMESTAMP column cannot have a default value
• None of the above is true

63. Which of the following is not a SQL operator?

Answers:

• Between..and..
• Like
• In
• Is null
• Not in
• All of the above are SQL operators

64. Which of the following is not a valid Comparison operator?

Answers:

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

65. What will happen if you write the following statement on the MySQL prompt?
SELECT NOW();

Answers:

• It will display the current date
• It will display the error message as now does not exist.
• It will display a syntax error near ‘()’

66. Assuming the column col1 in table tab1 has the following values:

2,3,NULL,2,3,1

What will be the output of the select statement below?

SELECT count(col1) FROM tab1

Answers:

• 6
• 5
• 4
• 3

67. What is the correct SQL syntax for selecting all the columns from the table Persons where the LastName is alphabetically between (and including) «Hansen» and «Pettersen»?

CREATE TABLE `Persons` (
`LastName` varchar(244) NOT NULL DEFAULT »
) ENGINE=InnoDB;
REPLACE INTO Persons VALUE (‘Hansen’),(‘Pettersen’),(‘Nilsen’),(‘Smith’);

Answers:

• SELECT * FROM Persons WHERE LastName > ‘Hansen’, LastName < ‘Pettersen’
• SELECT LastName > ‘Hansen’ AND LastName < ‘Pettersen’ FROM Persons
• SELECT * FROM persons WHERE LastName > ‘Hansen’ AND LastName > ‘Pettersen’
• SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’

68. Which of the following relational database management systems is simple to embed in a larger program?

Answers:

• MySQL
• SQLite
• Both
• None

69. What is true about the ENUM data type?

Answers:

• An enum value may be a user variable
• An enum may contain number enclosed in quotes
• An enum cannot contain an empty string
• An enum value may be NULL
• None of the above is true

70. What does DETERMINISTIC mean in the creation of a function?

Answers:

• The function returns no value
• The function always returns the same value for the same input
• The function returns the input value
• None of the above

71. Which of the following statements grants permission to Peter with password Software?

Answers:

• GRANT ALL ON testdb.* TO peter PASSWORD ‘Software’
• GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
• GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
• GRANT ALL OF testdb.* TO peter IDENTIFIED by ‘Software’

72. What will happen if you query the emp table as shown below:

select empno, DISTINCT ename, Salary from emp;

Answers:

• EMPNO, unique value of ENAME and then SALARY are displayed
• EMPNO, unique value ENAME and unique value of SALARY are displayed
• DISTINCT is not a valid keyword in SQL
• No values will be displayed because the statement will return an error

73. Which of the following commands will list the tables of the current database?

Answers:

• SHOW TABLES
• DESCRIBE TABLES
• SHOW ALL TABLES
• LIST TABLES

74. Which of the following is not a MySQL statement?

Answers:

• ENUMERATE
• EXPLAIN
• KILL
• LOAD DATA
• SET

75. When running the following SELECT query:

SELECT ID FROM (
SELECT ID, name FROM (
SELECT *
FROM employee
)
);
The error message ‘Every derived table must have its own alias’ appears.

Which of the following is the best solution for this error?

Answers:

• SELECT ID FROM ( SELECT ID AS SECOND_ID, name FROM ( SELECT * FROM employee ) );
• SELECT ID FROM ( SELECT ID, name AS NAME FROM ( SELECT * FROM employee ) );
• SELECT ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) AS T ) AS T;
• SELECT ID AS FIRST_ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) );

76. Which of the following is not a Table Storage specifier in MySQL?

Answers:

• InnoDB
• MYISAM
• BLACKHOLE
• STACK

77. The REPLACE statement is:

Answers:

• Same as the INSERT statement
• Like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted
• There is no such statement as REPLACE

78. If you try to perform an arithmetic operation on a column containing NULL values, the output will be:

Answers:

• NULL
• An error will be generated
• Cannot be determined

79. Which of the following is the best way to disable caching for a query?

Answers:

• Add the /*!no_query_cache*/ comment to the query.
• Flush the whole cache with the command: FLUSH QUERY CACHE
• Reset the query cache with the command: RESET QUERY CACHE
• Use the SQL_NO_CACHE option in the query.

80. What is the maximum size of a row in a MyISAM table?

Answers:

• No limit
• OS specific
• 65,534
• 2’147’483’648
• 128

81. Which of the following are not Numeric column types?

Answers:

• BIGINT
• LARGEINT
• SMALLINT
• DOUBLE
• DECIMAL

82. Which of the following statements is true regarding multi-table querying in MySQL?

Answers:

• JOIN queries are faster than WHERE queries.
• WHERE queries are faster than JOIN queries.
• INNER queries are faster than JOIN queries.
• WHERE & INNER offer the same performance in terms of speed.

83. What is wrong with the following statement?
create table foo (id int auto_increment, name int);

Answers:

• Nothing
• The id column cannot be auto incremented because it has not been defined as a primary key
• It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int);

84. Consider the following table definition:

CREATE TABLE table1 (
column1 INT,
column2 INT,
column3 INT,
column4 INT
)

Which one of the following is the correct syntax for adding the column, “column2a” after column2, to the table shown above?

Answers:

• ALTER TABLE table1 ADD column2a INT AFTER column2
• MODIFY TABLE table1 ADD column2a AFTER column2
• INSERT INTO table1 column2a AS INT AFTER column2
• ALTER TABLE table1 INSERT column2a INT AFTER column2
• CHANGE TABLE table1 INSERT column2a BEFORE column3
• Columns are always added after the last column

85. Examine the data in the employees table given below:

last_name department_id salary
ALLEN 10 3000
MILLER 20 1500
King 20 2200
Davis 30 5000

Which of the following Subqueries will execute well?

Answers:

• SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
• SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
• SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
• SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
• SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

86. Which of the following statements is correct in regards to the syntax of the code below?

SELECT table1.this, table2.that, table2.somethingelse
FROM table1
INNER JOIN table2 ON table1.foreignkey = table2.primarykey
WHERE (some other conditions)

Answers:

• Using the older syntax is more subject to error. If use inner joins without an ON clause, will get a syntax error.
• INNER JOIN is ANSI syntax. It is generally considered more readable, especially when joining lots of tables. It can also be easily replaced with an OUTER JOIN whenever a need arises
• (INNER JOIN) ON will filter the data before applying WHERE clause. The subsequent join conditions will be executed with filtered data which makes better performance. After that only WHERE condition will apply filter conditions.
• All of the Above

87. Considering table foo has been created with:create table foo (id int primary key auto_increment, name varchar(100));
Is the following query syntactically valid?
delete from foo where id = id-1;

Answers:

• Yes
• No

88. The STUDENT_GRADES table has these columns:

STUDENT_ID INT
SEMESTER_END DATE
GPA FLOAT

Which of the following statements finds the highest Grade Point Average (GPA) per semester?

Answers:

• SELECT MAX(GPA) FROM STUDENT_GRADES WHERE GPA IS NOT NULL
• SELECT GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
• SELECT MAX(GPA) FROM STUDENT_GRADES GROUP BY SEMESTER_END
• SELECT TOP 1 GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
• None of the above

89. Which of the following queries is valid?

Answers:

• Select * from students where marks > avg(marks);
• Select * from students order by marks where subject = ‘SQL’;
• Select * from students having subject =’SQL’;
• Select name from students group by subject, name;
• Select group(*) from students;
• Select name,avg(marks) from students;
• None of the above

90. Which of the following are aggregate functions in MySQL?

Answers:

• Avg
• Select
• Order By
• Sum
• Union
• Group by
• Having

91. Which of the following statements are true?

Answers:

• BLOB and TEXT columns cannot have DEFAULT values
• BLOB columns are treated as binary strings (byte strings)
• BLOB columns have a charset
• TEXT columns cannot be indexed
• None of the above is true

92. You are maintaining data for a Products table, and want to see the products which have a currentstock of at least 50 more than the minimum stock limit.
The structure of the Products table is:

ProductID
ProductName
CurrentStock
MinimumStock

Two possible queries are:

(a)select * from products where currentStock > MinimumStock + 50
(b)select * from products where currentStock — 50 > MinimumStock

Choose the appropriate option with regard to the above queries.

Answers:

• (a) is correct
• (b) is correct
• (a) and (b) both are correct
• (a) and (b) both are incorrect

93. Which operator will be evaluated first in the following statement:

select (age + 3 * 4 / 2 ? 8) from emp

Answers:

• +
• —
• /
• *

94. Is the following query valid?

create table foo (id int primary key auto_increment, name varchar);

Answers:

• No
• Yes

95. What are MySQL Spatial Data Types in the following list?

Answers:

• GEOMETRY
• CIRCLE
• SQUARE
• POINT
• POLYGON

96. What is the difference between the following commands? Command D: DELETE from customers; Command T: TRUCATE table customers;

Answers:

• Command D removes all entries, Command T removes entries inserted since last commit.
• Command D removes all entries, Command T removes all entries and resets auto-increment counters.
• Command D removes all entries, Command T removes all entries and deletes table meta data.
• Command D removes all entries, Command T recalculates indexes

97. What privilege do you need to create a function?

Answers:

• UPDATE
• CREATE ROUTINE
• SELECT
• CREATE FUNCTION
• No specific privilege

98. What kind of joins does MySQL support?

Answers:

• dual join
• right join
• natural join
• middle join
• STRAIGHT_JOIN

99. Why is the following GRANT statement considered a bad security practice:

GRANT ALL PRIVILEGES ON db_test.* TO ‘john’@’%’ IDENTIFIED BY ‘0!Nh6l7tEjohn’

Answers:

• Because the username is contained in the password
• Because it is granting all privileges to a user that is not the root
• Because it is granting access from any IP address
• Because the username ‘john’ does not fully identify the user

100. What is wrong with the following query:

select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)

Answers:

• In the sub query, ‘*’ should be used instead of ‘OrderID’
• The sub query can return more than one row, so, ‘=’ should be replaced with ‘in’
• The sub query should not be in parenthesis
• None of the above

101. Which of the following is a correct way to show the last queries executed on MySQL?

Answers:

• First execute SET GLOBAL log_output = ‘TABLE’; Then execute SET GLOBAL general_log = ‘ON’; The last queries executed are saved in the table mysql.general_log
• Edit the MySQL config file (mysql.con) and add the following line log = /var/log/mysql/mysql.log
• Execute VIEW .mysql_history
• Restart MySQL using the following line tail -f /var/log/mysql/mysql.log

102. Choose the appropriate query for the Products table where data should be displayed primarily in ascending order of the ProductGroup column. Secondary sorting should be in descending order of the CurrentStock column.

Answers:

• Select * from Products order by CurrentStock,ProductGroup
• Select * from Products order by CurrentStock DESC,ProductGroup
• Select * from Products order by ProductGroup,CurrentStock
• Select * from Products order by ProductGroup,CurrentStock DESC
• None of the above

103. MySQL supports 5 different int types. Which one takes 3 bytes?

Answers:

• TINYINT
• MEDIUMINT
• SMALLINT
• INT
• BIGINT

104. Which of the following is the correct way to determine duplicate values?

Answers:

• SELECT column_duplicated, sum(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
• SELECT column_duplicated, COUNT(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
• SELECT column_duplicated, sum(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1
• SELECT column_duplicated, COUNT(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1

105. Examine the query:

select (2/2/4) from tab1;

where tab1 is a table with one row. This would give a result of:

Answers:

• 4
• 2
• 1
• .5
• .25
• 8
• 24

106. Which of the following is the best way to insert a row, and to update an existing row, using a MySQL query?

Answers:

• Use MERGE statement
• Use INSERT … ON DUPLICATE KEY UPDATE statement
• Use ADD UNIQUE statement
• Use REPLACE statement

107. Which approach can be used to find all occurrences of text “owner” in all fields of all tables in a MySQL database?

Answers:

• Run the following query: SELECT * FROM * WHERE * LIKE ‘%owner%’
• Create an SQL dump of the database and its data, then search the dump file for occurrences of “owner” using an appropriate text editor or appropriate command line tools.
• Discover tables using the “SHOW TABLES” query; then for each table discover its columns using “SHOW COLUMNS FROM” query; then for each column run the query: SELECT * FROMWHERE like ‘%owner%’                    • Call the searchAllDB($search) function

108. What is true regarding the SET data type?

Answers:

• A SET can have zero or more values
• A SET value may contain a comma
• A SET can have a maximum of 64 different members
• MySQL stores SET values as strings
• None of the above is true

109. Consider the following SQL queries:

create table foo (id int primary key auto_increment, name varchar(200)); insert into foo (name) values (id); select * from foo;

What will be the value of the ‘name’ column returned by the select query?

Answers:

• 1
• ‘id’
• NULL
• These queries are invalid.

110. What is the correct order of clauses in the select statement?

1 select
2 order by
3 where
4 having
5 group by

Answers:

• 1,2,3,4,5
• 1,3,5,4,2
• 1,3,5,2,4
• 1,3,2,5,4
• 1,3,2,4,5
• 1,5,2,3,4
• 1,4,2,3,5
• 1,4,3,2,5

111. How will you change “Hansen” into “Nilsen” in the LastName column in the Persons Table?

Answers:

• UPDATE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
• UPDATE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’
• SAVE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
• SAVE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’

112. Which one of the following correctly selects rows from the table myTable that have NULL in column column1?

Answers:

• SELECT * FROM myTable WHERE column1 IS NULL
• SELECT * FROM myTable WHERE column1 = NULL
• SELECT * FROM myTable WHERE column1 EQUALS NULL
• SELECT * FROM myTable WHERE column1 NOT NULL
• SELECT * FROM myTable WHERE column1 CONTAINS NULL

113. Is the FROM clause necessary in every SELECT statement?

Answers:

• Yes
• No

114. Which command will make a backup on the whole database except the tables sessions and log?

Answers:

• mysqldump db_name sessions log > backup.sql
• mysqldump db_name | grep -vv -E “sessions|log” > backup.sql
• mysqldump db_name –ignore-table db_name.sessions db_name.log > backup.sql
• mysqldump db_name –except-table=db_name.sessions –except-table=db_name.log > backup.sql

115. Consider the table employees with fields name and surname (nulls allowed in both fields) having following data:

insert into employees (name,surname) values (‘John’, ‘Smith’);
insert into employees (name,surname) values (‘Peter’, NULL);
insert into employees (name,surname) values (NULL, ‘Brown’);

What will be the output of the following query:

select CONCAT( name, ‘ ‘, COALESCE( surname,» ) ) FROM employees;

Answers:

• John Smith Peter NULL NULL Brown
• John Smith Peter NULL
• John Smith NULL Brown
• John Smith Peter Brown

116. How could the following sql query be rewritten?

SELECT name, location FROM `venues` WHERE id IN ( SELECT venue_id FROM `events` );

Answers:

• SELECT name, location FROM `venues` as v LEFT JOIN `events` as e ON e.venue_id = v.id;
• SELECT name, location FROM `venues` AS v INNER JOIN `events` AS e ON e.venue_id = v.id;
• SELECT name, location FROM `venues` as v, `events` as e WHERE e.venue_id = v.id;
• SELECT name, location FROM `events` AS e RIGHT JOIN `venues` AS v ON v.id =e.venue_id;
• SELECT name, location FROM `venues` UNION (SELECT id, venue_id FROM `events`);

117. Which command lists stored procedures in mysql?

Answers:

• show procedure status;
• show variables like ‘%procedure%’;
• select * from procedures;
• show all procedures;

118. Which of the following operators has the highest precedence?

Answers:

• BINARY
• NOT
• <<
• %

119. Which of the following operators has the lowest precedence?

Answers:

• BINARY
• NOT
• << (+)
• %

120. Consider the following tables:

Books
——
BookId
BookName
AuthorId
SubjectId
PopularityRating(the popularity of the book on a scale of 1 to 10)
Language(such as French, English, German etc)

Subjects
———
SubjectId
Subject(such as History, Geography, Mathematics etc)

Authors
———
AuthorId
AuthorName
Country

Which is the query to determine the number of Authors who have written books on more than 2 subjects?

Answers:

• select AuthorName from Authors where Authorid in
(select Authorid from Books group by SubjectId having count(*)>1)
• select AuthorName from Authors where BookId in
(select BookId from Books group by BookId having count(*)>1)
• select AuthorName from Authors where Authorid in
(select Authorid from Books group by SubjectId,Authorid having count(*)>1)
• select AuthorName from Authors where Authorid in
(select Authorid from Books group by Authorid having count(*)>1)
• None of the above

121. Which of the following is a valid declaration?
Note: There may be more than one right answer.

Answers:

• CHAR(0)
• CHAR(1024)
• CHAR(256)
• VARCHAR(-1024)
• All are invalid
• All are valid

122. The next MySQL database has one table called people_hobbies which has who columns: person_id and hobbies. Which statement should be used to concatenate multiple hobbies into one single field?

Answers:

• SELECT hobbies
FROM people_hobbies;
• SELECT CONCAT_WS(*)
FROM people_hobbies;
• SELECT person_id, GROUP_CONCAT(hobbies separator ‘, ‘)
FROM peoples_hobbies GROUP BY person_id;
• SELECT CONCAT(hobbies separator ‘, ‘)
FROM people_hobbies;

123. Consider the following table

CREATE TABLE `foo` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`u` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> select * from foo;
+—-+——+
| id | u |
+—-+——+
| 1 | 10 |
+—-+——+

What is output for «select * from foo ;» command after executed following SQL command ?

replace into foo (u) values (20);

Answers:

• +—-+——+
| id | u |
+—-+——+
| 1 | 20 |
+—-+——+
• +—-+——+
| id | u |
+—-+——+
| 2 | 10 |
+—-+——+
• +—-+——+
| id | u |
+—-+——+
| 2 | 20 |
+—-+——+
• Syntax error message

124. Which function is used to get the higher of two values, a and b, in MySQL?

Answers:

• GREATEST(a,b)
• MAX(a,b)
• MAXIMUM(a,b)
• HIGHER(a,b)

125. Evaluate the following SQL statement:

SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id;

What will happen if all the parentheses are removed from the calculation?

Answers:

• The value displayed in the CALC_VALUE column will be lowe
• The value displayed in the CALC_VALUE column will be highe
• There will be no difference in the value displayed in the CALC_VALUE column
• An error will be reported

126. Which of the following queries will find all constraints that reference the field ID of table APPLICATIONS in database PROD_DB?

Answers:

• SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘APPLICATIONS’ AND REFERENCED_COLUMN_NAME = ‘ID’ AND TABLE_SCHEMA=’PROD_DB’;
• SELECT * FROM ALL_TABLES.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘APPLICATIONS’ AND REFERENCED_COLUMN_NAME = ‘ID’ AND TABLE_SCHEMA=’PROD_DB’;
• SELECT * FROM ALL_TABLES.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘APPLICATIONS’ AND REFERENCED_COLUMN_NAME = ‘ID’;
• SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE = ‘APPLICATIONS’ AND REFERENCED_COLUMN = ‘ID’ AND SCHEMA=’PROD_DB’;

127. What is the correct syntax for updating a table using LEFT OUTER JOIN?

Answers:

• UPDATE T1
LEFT JOIN
T2
ON T2.ID = T1.ID
SET T1.COL1 = NEWVALUE
WHERE T2.ID IS NULL
• UPDATE T1
LEFT JOIN
T2
SET T1.COL1 = NEWVALUE
ON T2.ID = T1.ID
WHERE T2.ID IS NULL
• UPDATE T1
LEFT JOIN
T2
ON T2.ID = T1.ID
WHERE T2.ID IS NULL
SET T1.COL1 = NEWVALUE
• UPDATE T1, T2
LEFT JOIN
ON T2.ID = T1.ID
SET T1.COL1 = NEWVALUE
WHERE T2.ID IS NULL

128. Which of the following statements are true regarding recovering MySQL DBs from .myd, .myi, and .frm files?

Answers:

• The correct use of REPAIR TABLE to rebuild .myi files is:
REPAIR TABLE sometable USE_FRM;
• MySQL DBs can be restored directly from those files.
• Those files will recover the database, but not the index file.
• Those files will only recover the database blob field data.

129. What will happen if you query the emp table as shown below:
select empno, DISTINCT ename, Salary from emp;

Answers:

• EMPNO, unique value of ENAME and then SALARY are displayed
• EMPNO, unique value ENAME and unique value of SALARY are displayed
• DISTINCT is not a valid keyword in SQL
• No values will be displayed because the statement will return an error

130. Many people use MySql statements as : —
Select * from car_table where 1=1 and value=”TOYOTA”
Exactly what does (where 1=1 and value=”TOYOTA”) mean here?

Answers:

• Author probably intent to retrieve empty set value.
• Easier way to generate complex WHERE statements — 1=1 is true always (NOT NULL), so all other conditions can be append with AND clause.
• It may even help the optimazation of the query very much.
• Not always true expression.

131. Is it possible to use group by clause to concatenate strings?

Answers:

• Yes, use SELECT identifier, GROUP_CONCAT (string SEPARATOR ‘ ’) FROM table_name GROUP BY identifier.
• No.
• Yes, use SELECT identifier, CONCAT (string, ‘’) FROM table_name GROUP BY identifier.
• Yes, use SELECT identifier, STRING_CONCAT (string SEPARATOR ‘ ’) FROM table_name GROUP BY identifier.

132. Which of the following statements is correct?

Answers:

• TEXT datatype can store more characters then VARCHAR datatype
• TEXT datatype supports binary and non-binary string storages but VARCHAR supports only binary strings
• TEXT is stored outside the mysql row and VARCHAR is stored as part of the row
• Max length of VARCHAR datatype is 16,777,215 characters

133. DELETE FROM story_category WHERE category_id NOT IN (
SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_id=category.id);

Which of the following is a possible reason why this statement is showing an error?

Answers:

• Cannot modify the same table which was used in the SELECT part.
• Select the rows not to be deleted into an empty table that has the same structure as the original table:
INSERT INTO t_copy SELECT * FROM t WHERE … ;
• Both A and B
• DELETE FROM somelog WHERE user = ‘jcole’
ORDER BY timestamp_column LIMIT 1;

134. Which datatype is recommended for storing SHA-1 hash values?

Answers:

• Char
• Binary
• Varchar
• Varbinary

135. What is the difference between INSERT INTO table VALUES & INSERT INTO table SET in MySQL?

Answers:

• INSERT INTO table VALUES is performance-optimized while INSERT INTO table SET is not.
• INSERT INTO table SET is performance-optimized while INSERT INTO table VALUES is not.
• Both are equal in performance.
• INSERT INTO table VALUES is performance-optimized for MyISAM while INSERT INTO table SET is performance-optimized for InnoDB.

136. How can the following error be solved in MySQL?
ERROR 1153: Got a packet bigger than ‘max_allowed_packet’ bytes

Answers:

• By specifying on the command line:
Mysql —max_allowed_packet=32M
• By specifying on the command line:
Mysql —max_allowed_packet=100M -u root
• By specifying on the command line:
Mysql —max_allowed_packet=100M -u root -p database < dump.sql
And updating the my.cnf or my.ini files with the following lines:
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
• By updating my.ini file with the following line:
set global net_buffer_length=32000;

137. What is the most common use of the mysql EXPLAIN statement?

Answers:

• To debug syntax errors in queries.
• To analyze the execution of a query.
• To automatically optimize a query.
• To get the documentation entry for a specific command.

138. Which is the correct query to find out the number of ENUM(‘M’, ‘F’, ‘NA’) datatypes columns that exist in all databases?

Answers:

• SELECT count(*) FROM information_schema.columns WHERE data_type = ‘enum’;
• select count(*) from information_schema.column where column_name=»ENUM(‘M’,’F’,’NA’)»;
• SELECT count(*) FROM information_schema.columns WHERE data_type = «ENUM(‘M’,’F’,’NA’)» ;
• SELECT count(*) FROM information_schema.columns WHERE column_type = «ENUM(‘M’,’F’,’NA’)» ;

139. Which of the following will join the same table twice?

Answers:

• SELECT toD.dom_url AS ToURL,
fromD.dom_url AS FromUrl,
rvw.*
FROM reviews AS rvw
LEFT JOIN domain AS toD
ON toD.Dom_ID = rvw.rev_dom_for
LEFT JOIN domain AS fromD
ON fromD.Dom_ID = rvw.rev_dom_from
• SELECT toD.dom_url AS ToURL,
fromD.dom_url AS FromUrl,
rvw.*
FROM reviews AS rvw
RIGHTJOIN domain AS toD
ON toD.Dom_ID = rvw.rev_dom_for
RIGHTJOIN domain AS fromD
ON fromD.Dom_ID = rvw.rev_dom_from
• SELECT toD.dom_url AS ToURL,
fromD.dom_url AS FromUrl,
rvw.*
FROM reviews AS rvw
FULLJOIN domain AS toD
ON toD.Dom_ID = rvw.rev_dom_for
FULLJOIN domain AS fromD
ON fromD.Dom_ID = rvw.rev_dom_from
• It is not possible to join the same table twice in MySQL.

140. How can a default value be set for a MySQL Datetime column?

Answers:

• By using TIMESTAMP (defaul_value TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
• By using DATE (defaul_value DATE DEFAULT CURRENT_DATE)
• By using DATEDEFAULT (defaul_value SET DATEDEFAULT)
• By using DATETIME (defaul_value DATETIME DEFAULT CURRENT_DATETIME)

141. The CAST() function does not support cast to which of the following data types?
Note: There may be more than one right answer.

Answers:

• SIGNED
• BINARY
• REAL
• BIGINT

142. What value return “type” column in EXPLAIN plans for a full table scan of select query?

Answers:

• FULL TABLE SCAN
• range
• ALL
• const

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

Answers:

• EXTRACT
• SELECT
• GET
• OPEN

144. How can database versioning be achieved in MySQL?

Answers:

• It is not possible to implement database versioning in MySQL.
• Using external version control systems to back up the database files
• Using built-in tools included in MySQL
• None of these.

145. Consider the queries mentioned below:

Query 1:
SELECT ‘id’, ‘name’, ‘anotherfield’ …
Query 2:
SELECT id, name, anotherfield …

Which of the following is true regarding the results of these queries?

Answers:

• The two queries will produce the same output.
• The two queries will produce different outputs.
• An error will be generated while running the two queries.
• None of the above.

146. For a table called PEOPLE with the following columns:

ID, NAME, GENDER, AGE, PHONE, EMAIL

ID is integer and PRIMARY KEY,
NAME is varchar,
GENDER is varchar,
AGE is integer,
PHONE is integer,
EMAIL is varchar

What happens if the following query is executed?

SELECT * FROM PEOPLE ORDER BY _rowid DESC;

Answers:

• An error is produced as there is no column named ‘_rowid’
• All the rows are fetched and are sorted in a descending order based on the column ‘ID’
• MySQL will return an empty result set
• All the rows are fetched and are not sorted in any order

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

Answers:

• 100
• 101
• 150
• 151

148. Which of the following will reclaim unused space in all databases of a MySQL server instance?

Answers:

• Execute the following command for every table in every database:
OPTIMIZE LOCAL TABLE table_name
• Execute the following command for every database:
mysqlcheck -o database_name
• Execute the following command:
mysqlcheck -o —all-databases
• Execute the following command for every database:
OPTIMIZE DATABASE database_name

149.

table votes (
id,
user,
email,
address,
primary key(id),
);

Which of the following is the correct code to change the columns (user, email, address) as UNIQUE KEY in MySQL?

Answers:

• ALTER TABLE votes ADD UNIQUE INDEX(user, email, address);
• UNIQUE KEY ‘votes’ (‘user’, ‘email’, ‘address’);
• ALTER TABLE votes ADD UNIQUE (user, email, address);
• Answer A and C

150. Which of the following is correct syntax for selecting all rows from a table ‘PERSONS’, with strings in the column ‘LASTNAME’ starting with ‘JOH’?

Answers:

• SELECT * from PERSONS where LASTNAME LIKE ‘JOH%’;
• SELECT * from PERSONS where LASTNAME IS LIKE ‘JOH%’;
• SELECT * from PERSONS where LASTNAME LIKE ‘JOH*’;
• SELECT * from PERSONS where LASTNAME IS LIKE ‘JOH*’;

151. Which of the following statements is true regarding the WHERE and HAVING clauses in MySQL?

Answers:

• The WHERE clause works with aliases, while the HAVING clause does not.
• The HAVING clause works with aliases, while the WHERE clause does not.
• The HAVING clause works with aliases, while the WHERE clause needs to have expressions repeated, and will not work with aggregating functions like SUM.
• The WHERE clause works with aliases, while the HAVING clause needs to have expressions repeated, and will not work with aggregating functions like SUM.

152. Which data type is recommended to be used in MySQL when the date value must be converted from the current time zone to Coordinated Universal Time (UTC) for storage, and converted back from Coordinated Universal Time (UTC) to the current time zone for retrieval?

Answers:

• DATETIME
• TIMEZONE
• TIMESTAMP
• DATE

153. With SQL, how do you select all the records from a table named “Customers” where the value of the column “FirstName” starts with an “b”?

Answers:

• SELECT * FROM Customers WHERE FirstName=’b’
• SELECT * FROM Customers WHERE FirstName=’%b%’
• SELECT * FROM Customers WHERE FirstName LIKE ‘b%’
• SELECT * FROM Customers WHERE FirstName LIKE ‘%b’

154. Which of the following is the correct datatype to store a bcrypt hash password?

Answers:

• BYNARY (60)
• TEXT (60)
• VARCHAR (60)
• VARBINARY (60)

155. A MySQL database has a table named APPLICATIONS with a column named TITLE. The goal is to randomly select 10 applications and allow web site users a 20% discount on buying these applications. Which query can be used to obtain the list of titles of applications to be included in the list?

Answers:

• SELECT TITLE FROM APPLICATIONS ORDER BY RAND() LIMIT 10
• SELECT TITLE FROM APPLICATIONS WHERE RAND() > 10/(SELECT COUNT(*) FROM APPLICATIONS)
• SELECT TITLE FROM APPLICATIONS LIMIT 10 BY RAND()
• SELECT TITLE FROM APPLICATIONS WHERE RAND() > 0.20*(SELECT COUNT(*) FROM APPLICATIONS)

156. Which of the following is the best data type for a field that will hold a URL with an undetermined length?

Answers:

• BLOB
• LONGTEXT
• VARCHAR (4096)
• VARCHAR (4)

157. Which of the following are ways to detect changes in a MySQL database schema?
Note: There may be more than one right answer.

Answers:

• Using a programming language/framework that supports database schema change detection
• Commit SQL scripts with DDL scripts to alter the schema; if there are new scripts, then the database schema has changed.
• Using an (Object-relational mapping) ORM tool
• None of these.

158. Which of the following statements is incorrect?

Answers:

• Each InnoDB table created using the file-per-table mode goes into its own tablespace file, with a .ibd
• A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace.
• A set of files, typically named ib_logfile0 and ib_logfile1, that form the redo log. These files record statements that attempt to change data in InnoDB tables.
• InnoDB system tablespace files can automatically shrink/purge when it reach maximum limit

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

Answers:

• MD5
• AES_ENCRYPT
• SHA512
• SHA2

160. What are the Privileges required if the read_only system variable is enabled, to explicitly start a transaction with START TRANSACTION READ WRITE?

Answers:

• Super_priv
• Show_db_priv
• Create_tablespace_priv
• Execute_priv

161. With which of the following you can not associate a Trigger?
Note: There may be more than one right answer.

Answers:

• Permanent tabe
• Temporary Table
• View

162. What is the default maximum number of indexes per MyISAM table?

Answers:

• There is no limit to that
• 64
• 32
• 16

163. 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.

164. What is the default maximum number of columns per index in MyISAM?

Answers:

• 4
• 8
• 12
• 16

165. Let’s say that you have a very large table “users” with an index on the column “age”. How would you most efficiently select all the first names of users that are between 15 and 24 years old?

Answers:

• SELECT first_name FROM users WHERE (age=15 OR age=16 OR age=17 OR age=18 OR age=19 OR age=20 OR age=21 OR age=22 OR age=23 OR age=24);
• SELECT first_name FROM users WHERE age IN (15,16,17, 18, 19, 20, 21, 22, 23, 24);
• SELECT first_name FROM users WHERE age BETWEEN 15 AND 24;

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

Answers:

• 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).

167. How do you set execution timeout for SELECT statements in MySQL?

Answers:

• By setting the system variable ‘exec_time_out’ to an integer value
• There is a default value set in milliseconds that cannot be changed
• By setting the system variable ‘max_execution_time’ to an integer value
• The SELECT statements cannot have an execution timeout

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

Answers:

• INFORMATION_SCHEMA
• META_SCHEMA
• INFORMATION_METADATA
• METADATA_SCHEMA

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

Answers:

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

170. Which storage engine in MySQL supports Transactions?

Answers:

• MyISAM
• Memory
• InnoDB
• Archive

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

Answers:

• MyISAM
• Memory
• InnoDB
• Archive

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

Answers:

• 80
• 8080
• 3306
• 556

173. Which of the following are the new storage engines added in MySQL 5.0?
Note: There may be more than one right answer.

Answers:

• InnoDB
• ARCHIVE
• MyISAM
• FEDERATED

174. 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)
);

Answers:

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

175. Which command would allow SELECT statements to be executed on a MySQL database without causing locks?

Answers:

• SELECT * FROM TABLE_NAME WITH (nolock)
• SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
• No commands are necessary: SELECT statements do not change data, and thus do not cause locks.
• SET TRANSACTION-ISOLATION = READ-UNCOMMITTED

176. Consider the following Table

EMP:
—————
empId
empName
empSalary
empJoinDate

Which SQL is incorrect for finding out the count of records on EMP table where empJoinDate is between 2012-10-01 and 2012-12-30?

Answers:

• Select count(*) from EMP where empJoinDate between ‘2012-10-01′ and 2012-12-30’;
• Select count(1) from EMP where empJoinDate between ‘2012-10-01′ and 2012-12-30’;
• Select count(*) from EMP where empJoinDate between ‘2012-10-01′ and 2012-12-30’ group by EXTRACT(YEAR_MONTH from empJoinDate );
• Select sum(1) from EMP where empJoinDate >=»2012-10-01″ and empJoinDate <=»2012-12-30″;
• Non of the above

177. Which of the following is not a valid Trigger?

Answers:

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

178. ENUM column can have a maximum of ____ distinct elements.

Answers:

• 65,535
• 64
• 64,535
• 65

179. Which statement is used to disable autocommit mode implicitly for a single series of statements?

Answers:

• START TRANSACTION
• COMMIT
• ROLLBACK
• NOCOMMIT

180. Which «MySQL» client utility option is useful for MySQL query execution on the command line?

Answers:

• —line-numbers
• —named-commands, -G
• —pager[=command]
• —execute=statement, -e statement

181. How many Auto Increment columns can be present per table in MySQL?

Answers:

• 1
• 5
• As per the max value set in environment variables
• There is no upper limit on the number of Auto Increment colums

182. What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?

Answers:

• It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.
• It will still increment the value by 1.
• It will reset the auto increment index.
• None of the above.

183. Which of the following statements is true?

Answers:

• When using a fixed-length type such as CHAR(n), stored values require n x 4 bytes for utf8
• MySQL stores metadata in a Unicode character set, namely UTF-8
• The default MySQL character set is UTF-8
• All statements are true

184. Based on performance, which of the following is the quickest way to select a random row from a large table in MySQL?

Answers:

• $result = mysql_query(“SELECT * from TABLE order by RAND() LIMIT 1”);
• $range_result = mysql_query(«SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM `table`»);
$range_row = mysql_fetch_object( $range_result );
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$result = mysql_query( » SELECT * FROM `table` WHERE `id` >= $random LIMIT 0,1 «);
• $offset_result = mysql_query(«SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table`»);
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset
$result = mysql_query(» SELECT * FROM `table` LIMIT $offset, 1 «);
• SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1

185. Full-text indexes can be used only with tables of which of the following Storage Engines?
Note: There may be more than one right answer.

Answers:

• MyISAM
• Memory
• InnoDB
• Archive

186. Which of the following is not a valid MyISAM table format?

Answers:

• .FYI
• .frm
• .MYD
• .MYI

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

Answers:

• 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 positions

188. What is the maximum length of a VARCHAR column?

Answers:

• The length can be up to 255 bytes, however, it is shared among all columns and the character set used in a row.
• The length can be up to 65,535 bytes, however, it is shared among all columns and the character set used in a row.
• The length can be up to 32,765 bytes, however, it is shared among all columns and the character set used in a row.

189. Which of the following is not a valid statement?

Answers:

• Double have accuracy up to eight place whereas float upto 18 places
• Floating point numbers are stored in FLOAT whereas Double are stored in DOUBLE.
• Float takes 4 bytes whereas DOUBLE takes eight bytes.
• FLOAT is for single-precision whereas DOUBLE is for double-precision numbers.

190. A system that uses a MySQL database has around 500,000 daily visitors. There is a requirement to record these visitors (IP address and timestamp). Which is the most effective solution performance-wise?

Answers:

• Use «INSERT DELAYED» instead of «INSERT» to record visitors
• Record the data about the visitors into a flat file on the local hard disc drive on the web server. Set up a scheduled job to write multiple records at once into the database.
• Set up a scheduled job to analyze the Apache access.log to retrieve the information about the visitors and write the data into the database, multiple records at once.
• There is no specialized setup necessary; 5-6 simple inserts per second is normal load for a MySQL database.

191. Is there any function in MySQL that allows for replace through a regular expression?

Answers:

• SELECT();
SELECT REPLACE(‘stackowerflow’, ‘ower’, ‘over’);
Output: «stackoverflow»
• USER DEFINED FUNCTIONS
SELECT regex_replace(‘[^a-zA-Z0-9]+‘, ”, “%$&?/’|test><+-,][)(» );
• Both A and B
• None of the Above

192. Consider a list of date ranges (range-start and range-end):

10/06/1983 to 14/06/1983
15/07/1983 to 16/07/1983
18/07/1983 to 19/07/1983

What will be the best way to find out if another date range containing any of the ranges already exists in the above list?

Answers:

• Option A:
SELECT *
FROM periods
WHERE NOT (range_start > @check_period_end
OR range_end < @check_period_start)
• Option B:
SELECT *
FROM periods
WHERE range_start <= @check_period_end AND range_end >= @check_period_start
• Both options A & B
• None of the above

193. Which of the following will find the distance between two pairs of latitude and longitude points?
Note: There may be more than one right answer.

Answers:

• select . . .
where locations.lat between X1 and X2
and locations.Long between y1 and y2;
• select . . .
where locations.lat between X1 and X2
or locations.Long between y1 and y2;
• SELECT *
FROM table
WHERE MBRContains(LineFromText(CONCAT(
‘(‘
, @lon + 10 / ( 111.1 / cos(RADIANS(@lon)))
, ‘ ‘
, @lat + 10 / 111.1
, ‘,’
, @lon — 10 / ( 111.1 / cos(RADIANS(@lat)))
, ‘ ‘
, @lat — 10 / 111.1
, ‘)’ )
,mypoint)
• SELECT *
FROM table
WHERE MBRContains(LineFromText(CONCAT(
‘(‘
, @lon — 10 / ( 111.1 / cos(RADIANS(@lon)))
, ‘ ‘
, @lat — 10 / 111.1
, ‘,’
, @lon — 10 / ( 111.1 / cos(RADIANS(@lat)))
, ‘ ‘
, @lat — 10 / 111.1
, ‘)’ )
,mypoint)

194. What is the effective maximum length of a VARCHAR column?

Answers:

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

195. What is the best way to select all the columns from the ’employee’ table except one column, ‘old_salary’, in the ‘factory’ database?

Answers:

• SET @sql = CONCAT(‘SELECT ‘, (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), ‘old_salary’) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ’employee’ AND TABLE_SCHEMA = ‘factory’), ‘ FROM employee’);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
• Use DESCRIBE and with the results another SELECT query can be generated.
• Select all the other columns from the ’employee’ table except the ‘old_salary’ column.
• SELECT * FROM employee;

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

Answers:

• ADD NEW
• INSERT INTO
• INSERT NEW
• ADD RECORD

197. Which of the following will get the record count for all tables in a MySQL database?

Answers:

• SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘{your_db}’;
• SELECT TOTAL(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘{your_db}’;
• SELECT COUNT(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘{your_db}’;
• SELECT ROUND(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘{your_db}’;

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

Answers:

• SAVE
• MODIFY
• SAVE AS
• UPDATE

199. Which of the following is the best way to update multiple rows?

Example:
Name id Col1 Col2
name1 1 6 1
name2 2 2 3
name3 3 9 5
name4 4 16 8

Update Col1= 1 where id = 1
Update Col1= 2 where id = 2
Update Col2= 3 where id = 3
Update Col1= 10 and col2= 12 where id = 4

Answers:

• UPDATE table_name SET Col1 = CASE id
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 4 THEN 10
ELSE Col1
END,
Col2 = CASE id
WHEN 3 THEN 3
WHEN 4 THEN 12
ELSE Col2
END
WHERE id IN (1, 2, 3, 4);
• UPDATE table_name SET Col1 = 1 WHERE id = 1;
UPDATE table_name SET Col1 = 2 WHERE id = 2;
UPDATE table_name SET Col2 = 3 WHERE id = 3;
UPDATE table_name SET Col1 = 10 WHERE id = 4;
UPDATE table_name SET Col2 = 12 WHERE id = 4;
• INSERT INTO table_name (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);
• UPDATE table_name SET col1 = ‘1’ WHERE id IN (1);
UPDATE table_name SET col1 = ‘2’ WHERE id IN (2);
UPDATE table_name SET col2 = ‘3’ WHERE id IN (3);
UPDATE table_name SET col1 = ’10’, col2=’12’ WHERE id IN (4);

200. Which of the following will check whether a MySQL database exists or not?
Note: There may be more than one right answer.

Answers:

• SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ‘DBName’
• CREATE DATABASE IF NOT EXISTS ‘DBName’;
• SHOW DATABASES LIKE ‘DBname’;
• None of the above.

201. Which among the following statements are true about using «NULL» in database columns?

Answers:

• The LENGTH of NULL is NULL.
• NULLs are sorted before empty strings.
• The COUNT() function will count empty strings but not NULLs.
• All of the above.

202. Which of the following will truncate all tables in a MySQL database?
Note: There may be more than one right answer.

Answers:

• SELECT ‘TRUNCATE TABLE ‘ + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
• SELECT Concat(‘TRUNCATE TABLE ‘, TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
• SELECT Concat(‘TRUNCATE TABLE ‘, TABLE_NAME, ‘\n’) FROM INFORMATION_SCHEMA.TABLES
• SELECT Concat(‘TRUNCATE TABLE ‘, TABLE_NAME, ‘;’) FROM INFORMATION_SCHEMA.TABLES

203. A table called report has three columns: id, type and amount. From this table two columns must be selected: id and amount. If type is ‘P’, then amount has be amount and if type is ‘N’, then amount has to be -amount. What is the best way to select those two columns?

Answers:

• SELECT id,
IF type = ‘P’ THEN amount = amount
ELSE amount = amount — 1
FROM report;
• SELECT id,
CASE type
IF ‘P’ THEN amount = amount
IF ‘N’ THEN amount = -amount
END AS amount
FROM report;
• SELECT id, IF(type = ‘P’?amount:amount * -1) AS amount
FROM report;
• SELECT id, IF(type = ‘P’, amount, amount * -1) AS amount
FROM report;

204. Which of the following can be used to check if a column in a row of a table is NULL?

Answers:

• IS NULL
• =
• LIKE
• <>

205. InnoDB prevents which of the following operations when innodb_force_recovery is greater than 0?
Note: There may be more than one right answer.

Answers:

• INSERT
• UPDATE
• DELETE
• SELECT

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

Answers:

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

207. How do you print the version of MySQL server?

Answers:

• SELECT VERSION()
• SELECT MYSQL_VERSION()
• SELECT —VERSION
• None of the above.

208. What is the maximum value of an unsigned TINYINT integer type?

Answers:

• 256
• 255
• 127
• 128

209. With SQL, how do you select all the records from a table named «Customers» where the «LastName» is alphabetically between (and including) «Martinez» and «Smith»?

Answers:

• SELECT * FROM Customers WHERE LastName BETWEEN ‘Martinez’ AND ‘Smith’
• SELECT LastName>’Martinez’ AND LastName<‘Smith’ FROM Customers • SELECT * FROM Customers WHERE LastName>’Martinez’ AND LastName<‘Smith’
• SELECT Customers WHERE LastName BETWEEN ‘Martinez’ AND ‘Smith’

210. How can you see all indexes defined for a table?

Answers:

• SHOW INDEX ;
• SHOW INDEX FROM ;
• SHOW *INDEX FROM ;
• None of the above.

211. Heap tables support which of the following data types?
Note: There may be more than one right answer.

Answers:

• BLOB
• FLOAT
• TEXT
• BIT

212. How many bytes of storage does the BIGINT integer type requires?

Answers:

• 4
• 8
• 3
• 2

213. Which of the following is not a valid BLOB type?

Answers:

• TINYBLOB
• MEDIUMBLOB
• LONGBLOB
• LARGEBLOB

214. 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 one right answer.

Answers:

• ALTER EVENT, CREATE EVENT and 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

215. Which of the following is not a valid comparison operator?

Answers:

• <>
• >=
• <=
• ^=

216. With SQL, how can you insert «Smith» as the «LastName» in the «Customers» table?

Answers:

• INSERT INTO Customers (LastName) VALUES (‘Smith’)
• INSERT INTO Customers (‘Smith’) INTO LastName
• INSERT (‘Smith’) INTO Customers (LastName)
• INSERT (LastName) VALUES (‘Smith’) INTO Customers

217. With SQL, how can you insert a new record into the «Customers» table?

Answers:

• INSERT (‘John’, ‘Smith’) INTO Customers
• INSERT VALUES (‘John’, ‘Smith’) INTO Customers
• INSERT INTO Customers VALUES (‘John’, ‘Smith’)
• INSERT VALUES (‘John’, ‘Smith’) INTO Customers

218. How can you change «Martinez» into «Smith» in the «LastName» column in the Customers table?

Answers:

• UPDATE Customers SET LastName=’Martinez’ INTO LastName=’Smith’
• MODIFY Customers SET LastName=’Smith’ WHERE LastName=’Martinez’
• UPDATE Customers SET LastName=’Smith’ WHERE LastName=’Martinez’
• MODIFY Customers SET LastName=’Martinez’ INTO LastName=’Smith’

219. Select valid numeric data types:
Note: There may be more than one right answer.

Answers:

• TINYINT
• FLOAT
• LARGEINT
• DOUBLEINT
• DECIMAL

220. Which of the statements are true for user-defined variables?
Note: There may be more than one right answer.

Answers:

• User variable names are not case sensitive and have a maximum length of 64 characters.
• User variables are written as $var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “@”.
• User-defined variables are session-specific. A user variable defined by one client cannot be seen or used by other clients.

221. What is the range of the values that can go in a column defined as DECIMAl(6,2) ?

Answers:

• 9999.99 to -9999.99
• 9999.99 to -999.99
• 999999.99 to -999999.99
• 999999.99 to -99999.99

222. Is select count(*) faster than select count(identifier) ?

Answers:

• select count(*) is faster in InnoDB than in MyISAM
• both are equal on MyISAM
• select count(*) is faster in MyISAM than in InnoDB
• both are equal on InnoDB

223. Which of the following is not a valid nonstandard string types?

Answers:

• TINYTEXT
• BLOBTEXT
• MEDIUMTEXT
• LONGTEXT

224. Select valid date and time functions in MySQL:
Note: There may be more than one right answer.

Answers:

• GMT_TIME
• DAYOFWEEK
• STR_TO_MONTH
• UTC_DATE
• YEARWEEK

225. For some particular assignment , you need to compare two values, if both are equal, the result would be null, and if the value are not equal then the first value should be returned. Which function should you use?

Answers:

• NVL
• NVL2
• NULLIF
• COALESCE