UpWork MySQL Test Question and Answers

MySQL UpWork Test Answers

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

Answers:

1. Indexes can have NULL values
2. BLOB and TEXT columns can be indexed
3. Indexes per table cannot be more than 16
4. Columns per index cannot be more than 16

2. 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:

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

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

Answers:

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

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

Answers:

1. No
2. Yes

5. 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:

1. select authorname from authors where authorid in (select authorid from books where popularityrating<5)
2. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
3. select authorname from authors where authorid in (select bookid from books where popularityrating<5)
4. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))

6. The Flush statement cannot be used for:

Answers:

1. Closing any open tables in the table cache
2. Closing open connections
3. Flushing the log file
4. Flushing the host cache

7. Consider the query:

SELECT name

FROM Students

WHERE name LIKE ‘_a%’;
Which names will be displayed?

Answers:

1. Names starting with “a”
2. Names containing “a” as the second lette
3. Names starting with “a” or “A”
4. Names containing “a” as any letter except the first

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

Answers:

1. SMALLINT
2. DECIMAL(19,4)
3. VARCHAR(32)
4. BIGINT

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

Answers:

1. GEOMETRY
2. CIRCLE
3. SQUARE
4. POINT
5. POLYGON

10. 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:

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

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

Answers:

1. SIGNAL
2. RAISE
3. ERROR
4. None of these.

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

Answers:

1. SELECT * FROM fiberbox WHERE field REGEXP ‘Pizza|Burger|Hotdog’;
2. SELECT * FROM fiberbox WHERE field LIKE ‘%Pizza%’ OR field LIKE ‘%Burger%’ OR field LIKE ‘%Hotdog%’;
3. SELECT * FROM fiberbox WHERE field = ‘%Pizza%’ OR field = ‘%Burger%’ OR field = ‘%Hotdog%’;
4. SELECT * FROM fiberbox WHERE field = ‘?Pizza?’ OR field = ‘?Burger?’ OR field = ‘?Hotdog?’;

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

Answers:

1. BLOB
2. BIGINT
3. INT
4. Both BLOB and BIGINT

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

Answers:

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

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

Answers:

1. ALTER TABLE table_name MODIFY column_name varchar(255) null
2. ALTER TABLE table_name MODIFY column_name VARCHAR(255)
3. ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL
4. ALTER table_name MODIFY column_name varchar(255) null

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

Answers:

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

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

Answers:

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

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

Answers:

1. Using subqueries
2. Using variables in a MySQL query
3. Using self-join
4. MySQL also supports ROW_NUMBER()

19. 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:

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

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

Answers:

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

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

Answers:

1. The ‘IN’ clause
2. Using temporary tables and inline views
3. The ‘INNER JOIN’ clause
4. Using subqueries

22. 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:

1. select item, brand, price from items where max(price) order by item
2. select * from items where price = max group by item
3. select item, brand, max(price) from items group by item
4. select * from items where price > 200 order by item

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

Answers:

1. mysql -u<user> -p < db_backup.dump
2. mysql -u<user> -p<password> < db_backup.dump
3. mysql -u<user> -p <password> < db_backup.dump
4. mysql -u<user> -p<password> > db_backup.dump

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

Answers:

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

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

Answers:

1. “EXPERTRATING” LIKE “EXP%”
2. “EXPERTRATING” LIKE “Exp%”
3. BINARY “EXPERTRATING” LIKE “EXP%”
4. BINARY “EXPERTRATING” LIKE “Exp%”
5. All will result in 1 (true)

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

Answers:

1. MySQL
2. SQLite
3. Both
4. None

27. What is true about the ENUM data type?

Answers:

1. An enum value may be a user variable
2. An enum may contain number enclosed in quotes
3. An enum cannot contain an empty string
4. An enum value may be NULL
5. None of the above is true

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

Answers:

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

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

Answers:

1. mysqldump –single-transaction db_name
2. mysqldump –force db_name
3. mysqldump –quick db_name
4. mysqldump –no-tablespaces db_name

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

Answers:

1. Temporary diskspace that the database uses to run some of the queries
2. The size of a table
3. A tablespace name
4. None of the above

31. 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:

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

32. 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:

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

33. What is NDB?

Answers:

1. An in-memory storage engine offering high-availability and data-persistence features
2. A filesystem
3. An SQL superset
4. MySQL scripting language
5. None of the above

34. Which of the following statements are true?

Answers:

1. Names of databases, tables and columns can be up to 64 characters in length
2. Alias names can be up to 255 characters in length
3. Names of databases, tables and columns can be up to 256 characters in length
4. Alias names can be up to 64 characters in length

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

Answers:

1. CHANGE TABLE
2. MODIFY TABLE
3. ALTER TABLE
4. UPDATE TABLE

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

Answers:

1. The function returns no value
2. The function always returns the same value for the same input
3. The function returns the input value
4. None of the above

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

Answers:

1. GRANT ALL ON testdb.* TO peter PASSWORD ‘Software’
2. GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
3. GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
4. GRANT ALL OF testdb.* TO peter IDENTIFIED by ‘Software’

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

select empno, DISTINCT ename, Salary from emp;

Answers:

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

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

Answers:

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

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

Answers:

1. No limit
2. OS specific
3. 65,534
4. 2’147’483’648
5. 128

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

Answers:

1. No
2. Yes

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

Answers:

1. DD-MM-YYYY
2. YYYY-DD-MM
3. YYYY-MM-DD
4. MM-DD-YY
5. MMDDYYYY

43. 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:

1. True
2. False

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

Answers:

1. ndb_config
2. cluster_config
3. ndb –config
4. configNd
5. None of the above

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

Answers:

1. Table Name
2. Database name
3. LIMIT clause
4. WHERE clause

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

Answers:

1. BIGINT
2. LARGEINT
3. SMALLINT
4. DOUBLE
5. DECIMAL

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

Answers:

1. JOIN queries are faster than WHERE queries.
2. WHERE queries are faster than JOIN queries.
3. INNER queries are faster than JOIN queries.
4. WHERE & INNER offer the same performance in terms of speed.

48. What is wrong with the following statement?

create table foo (id int auto_increment, name int);

Answers:

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

49. 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:

1. ALTER TABLE table1 ADD column2a INT AFTER column2
2. MODIFY TABLE table1 ADD column2a AFTER column2
3. INSERT INTO table1 column2a AS INT AFTER column2
4. ALTER TABLE table1 INSERT column2a INT AFTER column2
5. CHANGE TABLE table1 INSERT column2a BEFORE column3
6. Columns are always added after the last column

50. 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:

1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
3. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
4. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

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

Answers:

1. UPDATE
2. CREATE ROUTINE
3. SELECT
4. CREATE FUNCTION
5. No specific privilege

52. What is wrong with the following query:

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

Answers:

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

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

Answers:

1. 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
2. Edit the MySQL config file (mysql.con) and add the following line log = /var/log/mysql/mysql.log
3. Execute VIEW .mysql_history
4. Restart MySQL using the following line tail -f /var/log/mysql/mysql.log

54. 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:

1. Select * from Products order by CurrentStock,ProductGroup
2. Select * from Products order by CurrentStock DESC,ProductGroup
3. Select * from Products order by ProductGroup,CurrentStock
4. Select * from Products order by ProductGroup,CurrentStock DESC
5. None of the above

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

Answers:

1. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
2. SELECT * FROM Persons SORT REVERSE ‘FirstName’
3. SELECT * FROM Persons ORDER BY -‘FirstName’
4. SELECT * FROM Persons ORDER BY FirstName DESC

56. 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:

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

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

Answers:

1. True
2. False

58. 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:

1. Select course from students where count(course) > 5;
2. Select course from students where count(*) > 5 group by course;
3. Select course from students group by course;
4. Select course from students group by course having count(*) > 5;
5. Select course from students group by course where count(*) > 5;
6. Select course from students where count(group(course)) > 5;
7. Select count(course) > 5 from students;
8. None of the above

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

Answers:

1. TINYINT
2. MEDIUMINT
3. SMALLINT
4. INT
5. BIGINT

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

Answers:

1. SELECT column_duplicated, sum(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
2. SELECT column_duplicated, COUNT(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
3. SELECT column_duplicated, sum(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1
4. SELECT column_duplicated, COUNT(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1

61. Examine the query:-

select (2/2/4) from tab1;

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

Answers:

1. 4
2. 2
3. 1
4. .5
5. .25
6. 8
7. 24

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

Answers:

1. SHOW TABLES
2. DESCRIBE TABLES
3. SHOW ALL TABLES
4. LIST TABLES

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

Answers:

1. ENUMERATE
2. EXPLAIN
3. KILL
4. LOAD DATA
5. SET

64. 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:

1. SELECT ID FROM ( SELECT ID AS SECOND_ID, name FROM ( SELECT * FROM employee ) );
2. SELECT ID FROM ( SELECT ID, name AS NAME FROM ( SELECT * FROM employee ) );
3. SELECT ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) AS T ) AS T;
4. SELECT ID AS FIRST_ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) );

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

Answers:

1. InnoDB
2. MYISAM
3. BLACKHOLE
4. STACK

66. The REPLACE statement is:

Answers:

1. Same as the INSERT statement
2. 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
3. There is no such statement as REPLACE

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

Answers:

1. NULL
2. An error will be generated
3. Cannot be determined

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

Answers:

1. Use MERGE statement
2. Use INSERT … ON DUPLICATE KEY UPDATE statement
3. Use ADD UNIQUE statement
4. Use REPLACE statement

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

Answers:

1. UPDATE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
2. UPDATE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’
3. SAVE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
4. SAVE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’

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

Answers:

1. SELECT * FROM myTable WHERE column1 IS NULL
2. SELECT * FROM myTable WHERE column1 = NULL
3. SELECT * FROM myTable WHERE column1 EQUALS NULL
4. SELECT * FROM myTable WHERE column1 NOT NULL
5. SELECT * FROM myTable WHERE column1 CONTAINS NULL

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

Answers:

1. Yes
2. No

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

Answers:

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

73.Which of the following statements is true?

Answers:

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

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

Answers:

1. +
2. –
3. *
4. \
5. %
6. All are valid
75. What are MySQL Spatial Data Types in the following list?
Answers:
1. GEOMETRY
2. CIRCLE
3. SQUARE
4. POINT
5. POLYGON
76. Which of the following statements relating to Alias names is true?
Answers:
1. Alias names are case sensitive
2. Alias names are case sensitive
3. Alias names are case in-sensitive
4. Alias names are case sensitive on UNIX and not on Windows
5. Alias names are case sensitive on Windows and not on UNIX
6. Alias names case sensitivity depends on lower_case_table_names system setting
77. Examine the description of the STUDENTS table:
Answers:
STD_ID INT
COURSE_ID VARCHAR (10)
START_DATE DATE
END_DATE DATE
The aggregate functions valid on the START_DATE column are:
1. SUM(start_date)
2. AVG(start_date)
3. COUNT(start_date)
4. AVG(start_date, end_date)
5. AVG(start_date, end_date)
6. MIN(start_date)
78. Which of the following is the correct way to change the character set to UTF8?
Answers:
STD_ID INT
COURSE_ID VARCHAR (10)
START_DATE DATE
END_DATE DATE
The aggregate functions valid on the START_DATE column are:
1. Add the following lines in my.cnf:
default-character-set=utf8
[mysqld]
default-character-set = utf8
3. dd the following lines in my.cnf:
   [mysqld]
   character_set_server = utf8
4. Add the following lines in my.cnf:
   [mysqld]
   skip-character-set-client-handshake
   character_set_client=utf8
   character_set_server=utf8
5. AVG(start_date, end_date)
6. AVG(start_date, end_date)
7. MIN(start_date)
78. To quote a string within a string, which of the following can you use?
Answers:
STD_ID INT
COURSE_ID VARCHAR (10)
START_DATE DATE
END_DATE DATE
The aggregate functions valid on the START_DATE column are:
1. «This is the «quoted» message»
2. «This is the «»quoted»» message»
3. ‘This is the «quoted» message’
4. «This is the \»quoted\» message»
79.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:
1. It returns employees whose salary is 50% more than $23,000
2. It returns employees who have 50% commission rate or salary greater than $23,000
3. It returns employees whose salary is 50% less than $23,000
4. None of the above
80.Select which of the following is the best way to duplicate MySql Database(DB1) in to another database(DB2) without using mysqldump?
Answers:
1. Create the target database using MySQLAdmin,
2. Execute the following MySql query CREATE TABLE t2 SELECT * FROM t1;
3. Execute the following MySql query CREATE TABLE x LIKE other_db.y;
4. CREATE TABLE DB2 SELECT * FROM DB1;
81.Which of the following statement will results in 0 (false)?
Answers:
1. SELECT «EXPERTRATING» LIKE «EXP%»
2. SELECT «EXPERTRATING» LIKE «Exp%»
3. SELECT BINARY «EXPERTRATING» LIKE «EXP%»
4. SELECT BINARY «EXPERTRATING» LIKE «Exp%»
5. All will result in 1 (true)
82.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:
1. select * from Pers where joining_date from #1/1/2005# to #1/2/2005#, job=Analyst or clerk or salesman
2. select * from Pers where joining_date between #1/1/2005# to #1/2/2005#, job=Analyst or job=clerk or job=salesm
3. select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and
4. None of the above
83. How can a InnoDB database be backed up without locking the tables?
Answers:
1. mysqldump –single-transaction db_name
2. mysqldump –force db_name
3. mysqldump –quick db_name
4. mysqldump –no-tablespaces db_name
85. Which of the following is not a valid Bit operator?
Answers:
1. &
2. &&
3. <<
4. |
5. >>
86. What is the main purpose of InnoDB over MyISAM?
Answers:
1. InnoDB is thread safe
2. InnoDB provides a transaction safe environment
3. InnoDB can handle table with more than 1000 columns
4. InnoDB provides FULLTEXT indexes
5. None of the above
87. 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)
Answers:
• 1
• 2
• 3
• 4
• 5
88.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
89.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
90.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
91. Which of the following is not a valid Logical operator?
Answers:
1. &
2. &&
3. AND
4. NOT
92. What is true regarding the TIMESTAMP data type?
Answers:
1. For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value
2. TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp
3. When the server runs with the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME
4. A TIMESTAMP column cannot have a default value
5. None of the above is true
93. Which of the following is not a SQL operator?
Answers:
1. Between..and..
2. Like
3. In
4. Is null
5. Not in
6. All of the above are SQL operators
94. Which of the following is not a SQL operator?
Answers:
1. Between..and..
2. Like
3. In
4. Is null
5. Not in
6. All of the above are SQL operators
95.Which of the following is not a valid Comparison operator?
Answers:
1. ==
2. <=>
3. !=
4. <>
5. REGEXP
6. All of the above are SQL operators
96.What will happen if you write the following statement on the MySQL prompt?
SELECT NOW();
Answers:
1. It will display the current date
2. It will display the error message as now does not exist.
3. It will display a syntax error near ‘()’
97.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:
1. 6
2. 5
3. 4
4. 3
98. 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:
1. SELECT * FROM Persons WHERE LastName > ‘Hansen’, LastName <‘Pettersen’
2. SELECT LastName > ‘Hansen’ AND LastName < ‘Pettersen’ FROM Persons
3. SELECT * FROM persons WHERE LastName > ‘Hansen’ AND LastName > ‘Pettersen’
4. SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’
99.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:
1. Using the older syntax is more subject to error. If use inner joins without an ON clause, will get a syntax error.
2. 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
3. (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.
4. All of the Above
100.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:
1. Yes
2. No
101.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:
1. SELECT MAX(GPA) FROM STUDENT_GRADES WHERE GPA IS NOT NULL
2. SELECT GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
3. SELECT MAX(GPA) FROM STUDENT_GRADES GROUP BY SEMESTER_END
4. SELECT TOP 1 GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
5. None of the above
102.Which of the following queries is valid?
Answers:
1. Select * from students where marks > avg(marks);
2. Select * from students order by marks where subject = ‘SQL’;
3. Select * from students having subject =’SQL’;
4. Select name from students group by subject, name;
5. Select group(*) from students;
6. Select name,avg(marks) from students;
7. None of the above
103.Which of the following are aggregate functions in MySQL?
Answers:
1. Avg
2. Select
3. Order By
4. Sum
5. Union
6. Group by
7. Having
104. Which of the following statements are true?
Answers:
1. BLOB and TEXT columns cannot have DEFAULT values
2. BLOB columns are treated as binary strings (byte strings)
3. BLOB columns have a charset
4. TEXT columns cannot be indexed
5. None of the above is true
105.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:
1. (a) is correct
2. (b) is correct
3. (a) and (b) both are correct
4. (a) and (b) both are incorrect
5. (a) and (b) both are incorrect
106. Which operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 ? 8) from emp
Answers:
1. +
2. —
3. /
4. *
107. Is the following query valid?
create table foo (id int primary key auto_increment, name varchar);
Answers:
1. No
2. Yes
108. What is the difference between the following commands? Command D: DELETE from customers; Command T: TRUCATE table customers;
Answers:
1. Command D removes all entries, Command T removes entries inserted since last commit.
2. Command D removes all entries, Command T removes all entries and resets auto-increment counters.
3. Command D removes all entries, Command T removes all entries and deletes table meta data.
4. Command D removes all entries, Command T recalculates indexes
109.What kind of joins does MySQL support?
Answers:
1. dual join
2. right join
3. natural join
4. middle join
5. STRAIGHT_JOIN
110. Why is the following GRANT statement considered a bad security practice:
GRANT ALL PRIVILEGES ON db_test.* TO ‘john’@’%’ IDENTIFIED BY ‘0!Nh6l7tEjohn’
Answers:
1. Because the username is contained in the password
2. Because it is granting all privileges to a user that is not the root
3. Because it is granting access from any IP address
4. Because the username ‘john’ does not fully identify the user
111. What is wrong with the following query:
select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)
Answers:
1. In the sub query, ‘*’ should be used instead of ‘OrderID’
2. The sub query can return more than one row, so, ‘=’ should be replaced with ‘in’
3. The sub query should not be in parenthesis
4. None of the above
112. Which approach can be used to find all occurrences of text “owner” in all fields of all tables in a MySQL database?
Answers:
1. Run the following query: SELECT * FROM * WHERE * LIKE ‘%owner%’
2. 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.
3. 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
4. None of the above
113. What is true regarding the SET data type?
Answers:
1. A SET can have zero or more values
2. A SET value may contain a comma
3. A SET can have a maximum of 64 different members
4. MySQL stores SET values as strings
5. None of the above is true
114. 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. 1,2,3,4,5
2. 1,3,5,4,2
3. 1,3,5,2,4
4. 1,3,2,5,4
5. 1,5,2,3,4
6. 1,4,2,3,5
7. 1,4,3,2,5
115. Consider the table employees with fields name and surname (nulls allowed in both fields) having following data:
nsert 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:
1. John Smith Peter NULL NULL Brown
2. John Smith Peter NULL
3. John Smith NULL Brown
4. John Smith Peter Brown
116. Consider the table employees with fields name and surname (nulls allowed in both fields) having following data:
nsert into employees (name,surname) values (‘John’, ‘Smith’);
insert into employees (name,surname) values (‘Peter’, NULL);
insert into employees (name,surname) values (NULL, ‘Brown’);
117. How could the following sql query be rewritten?
SELECT name, location FROM `venues` WHERE id IN ( SELECT venue_id FROM `events` );
Answers:
1. SELECT name, location FROM `venues` as v LEFT JOIN `events` as e ON e.venue_id = v.id;
2. SELECT name, location FROM `venues` AS v INNER JOIN `events` AS e ON e.venue_id = v.id;
3. SELECT name, location FROM `venues` as v, `events` as e WHERE e.venue_id = v.id;
4. SELECT name, location FROM `events` AS e RIGHT JOIN `venues` AS v ON v.id =e.venue_id;
5. SELECT name, location FROM `venues` UNION (SELECT id, venue_id FROM `events`);
118. Which command lists stored procedures in mysql?
SELECT name, location FROM `venues` WHERE id IN ( SELECT venue_id FROM `events` );
Answers:
1. show procedure status;
2. show variables like ‘%procedure%’;
3. select * from procedures;
4. show all procedures;
119. Which of the following operators has the highest precedence?
Answers:
1. BINARY
2. NOT
3. <<
4. %
120. Which of the following is a valid declaration?
Answers:
1. CHAR(0)
2. NOT
3. CHAR(1024)
4. CHAR(256)
5. CHAR(256)
6. VARCHAR(-1024)
7. All are invalid
8. All are valid
121.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:
1. SELECT hobbies
   FROM people_hobbies;
2. SELECT CONCAT_WS(*)
   FROM people_hobbies;
3. SELECT person_id, GROUP_CONCAT(hobbies separator ‘, ‘)
   FROM peoples_hobbies GROUP BY person_id;
4. SELECT CONCAT(hobbies separator ‘, ‘)
   FROM people_hobbies;
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:
1. SELECT hobbies
   FROM people_hobbies;
2. SELECT CONCAT_WS(*)
   FROM people_hobbies;
3. SELECT person_id, GROUP_CONCAT(hobbies separator ‘, ‘)
   FROM peoples_hobbies GROUP BY person_id;
4. 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:
1. +—-+——+
   | id | u |
   +—-+——+
   | 1 | 20 |
   +—-+——+
2. +—-+——+
   | id | u |
   +—-+——+
   | 2 | 10 |
   +—-+——+
3. +—-+——+
   | id | u |
   +—-+——+
   | 2 | 20 |
   +—-+——+
4. Syntax error message
124. Which function is used to get the higher of two values, a and b, in MySQL?
Answers:
1. GREATEST(a,b)
2. MAX(a,b)
3. MAXIMUM(a,b)
4. 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:
1. The value displayed in the CALC_VALUE column will be lowe
2. The value displayed in the CALC_VALUE column will be highe
3. There will be no difference in the value displayed in the CALC_VALUE column
4. 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:
1. SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘APPLICATIONS’ AND REFERENCED_COLUMN_NAME = ‘ID’ AND TABLE_SCHEMA=’PROD_DB’;
2. SELECT * FROM ALL_TABLES.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘APPLICATIONS’ AND REFERENCED_COLUMN_NAME = ‘ID’ AND TABLE_SCHEMA=’PROD_DB’;
3. SELECT * FROM ALL_TABLES.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘APPLICATIONS’ AND REFERENCED_COLUMN_NAME = ‘ID’;
4. 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:
1. UPDATE T1
   LEFT JOIN
   T2
   ON T2.ID = T1.ID
   SET T1.COL1 = NEWVALUE
   WHERE T2.ID IS NULL
2. UPDATE T1
   LEFT JOIN
   T2
   SET T1.COL1 = NEWVALUE
   ON T2.ID = T1.ID
   WHERE T2.ID IS NULL
3. UPDATE T1
   LEFT JOIN
   T2
   ON T2.ID = T1.ID
   WHERE T2.ID IS NULL
   SET T1.COL1 = NEWVALUE
4. 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:
1. The correct use of REPAIR TABLE to rebuild .myi files is:
   REPAIR TABLE sometable USE_FRM;
2. MySQL DBs can be restored directly from those files.
3. Those files will recover the database, but not the index file.
4. 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:
1. EMPNO, unique value of ENAME and then SALARY are displayed
2. MySQL DBs can be restored directly from those files.
3. EMPNO, unique value ENAME and unique value of SALARY are displayed
4. DISTINCT is not a valid keyword in SQL
5. 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:
1. Author probably intent to retrieve empty set value.
2. Easier way to generate complex WHERE statements — 1=1 is true always (NOT NULL), so all other conditions can be append with AND clause.
3. It may even help the optimazation of the query very much.
4. Not always true expression.
131. Is it possible to use group by clause to concatenate strings?
Answers:
1. Yes, use SELECT identifier, GROUP_CONCAT (string SEPARATOR ‘ ’) FROM table_name GROUP BY identifier.
2. No.
3. Yes, use SELECT identifier, CONCAT (string, ‘’) FROM table_name GROUP BY identifier.
4. Yes, use SELECT identifier, STRING_CONCAT (string SEPARATOR ‘ ’) FROM table_name GROUP BY identifier.
132. Which of the following statements is correct?
Answers:
1. TEXT datatype can store more characters then VARCHAR datatype
2. TEXT datatype supports binary and non-binary string storages but VARCHAR supports only binary strings
3. TEXT is stored outside the mysql row and VARCHAR is stored as part of the row
4. 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:
1. Cannot modify the same table which was used in the SELECT part.
2. 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 … ;
3. Both A and B
4. DELETE FROM somelog WHERE user = ‘jcole’
   ORDER BY timestamp_column LIMIT 1;
134. Which datatype is recommended for storing SHA-1 hash values?
Answers:
1. Char
2. Binary
3. Varchar
4. Varbinary
135. What is the difference between INSERT INTO table VALUES & INSERT INTO table SET in MySQL?
Answers:
1. INSERT INTO table VALUES is performance-optimized while INSERT INTO table SET is not.
2. INSERT INTO table SET is performance-optimized while INSERT INTO table VALUES is not.
3. Both are equal in performance.
4. 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:
1. By specifying on the command line:
   Mysql —max_allowed_packet=32M
2. By specifying on the command line:
   Mysql —max_allowed_packet=100M -u root
3. 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;
4. 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:
1. To debug syntax errors in queries.
2. To analyze the execution of a query.
3. To automatically optimize a query.
4. 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:
1. SELECT count(*) FROM information_schema.columns WHERE data_type = ‘enum’;
2. select count(*) from information_schema.column where column_name=»ENUM(‘M’,’F’,’NA’)»;
3. SELECT count(*) FROM information_schema.columns WHERE data_type = «ENUM(‘M’,’F’,’NA’)» ;
4. 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:
1. 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
2. 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
3. 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
4. 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:
1. By using TIMESTAMP (defaul_value TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
2. By using DATE (defaul_value DATE DEFAULT CURRENT_DATE)
3. By using DATEDEFAULT (defaul_value SET DATEDEFAULT)
4. By using DATETIME (defaul_value DATETIME DEFAULT CURRENT_DATETIME)
141. The CAST() function does not support cast to which of the following data types?
Answers:
1. SIGNED
2. BINARY
3. REAL
4. BIGINT
142. What value return “type” column in EXPLAIN plans for a full table scan of select query?
Answers:
1. FULL TABLE SCAN
2. range
3. ALL
4. const
143. Which SQL statement is used to extract data from a database?
Answers:
1. EXTRACT
2. SELECT
3. GET
4. OPEN
144. How can database versioning be achieved in MySQL?
Answers:
1. It is not possible to implement database versioning in MySQL.
2. Using external version control systems to back up the database files
3. Using built-in tools included in MySQL
4. 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:
1. The two queries will produce the same output.
2. The two queries will produce different outputs.
3. An error will be generated while running the two queries.
4. 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:
1. An error is produced as there is no column named ‘_rowid’
2. All the rows are fetched and are sorted in a descending order based on the column ‘ID’
3. MySQL will return an empty result set
4. 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:
1. 100
2. 101
3. 150
4. 151
148. Which of the following will reclaim unused space in all databases of a MySQL server instance?
Answers:
1. Execute the following command for every table in every database:
   OPTIMIZE LOCAL TABLE table_name
2. Execute the following command for every database:
   mysqlcheck -o database_name
3. Execute the following command:
   mysqlcheck -o —all-databases
4. 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:
1. ALTER TABLE votes ADD UNIQUE INDEX(user, email, address);
2. UNIQUE KEY ‘votes’ (‘user’, ‘email’, ‘address’);
3. ALTER TABLE votes ADD UNIQUE (user, email, address);
4. 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:
1. SELECT * from PERSONS where LASTNAME LIKE ‘JOH%’;
2. SELECT * from PERSONS where LASTNAME IS LIKE ‘JOH%’;
3. SELECT * from PERSONS where LASTNAME LIKE ‘JOH*’;
4. 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:
1. The WHERE clause works with aliases, while the HAVING clause does not.
2. The HAVING clause works with aliases, while the WHERE clause does not.
3. The HAVING clause works with aliases, while the WHERE clause needs to have expressions repeated, and will not work with aggregating functions like SUM.
4. 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:
1. DATETIME
2. TIMEZONE
3. TIMESTAMP
4. 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:
1. SELECT * FROM Customers WHERE FirstName=’b’
2. SELECT * FROM Customers WHERE FirstName=’%b%’
3. SELECT * FROM Customers WHERE FirstName LIKE ‘b%’
4. SELECT * FROM Customers WHERE FirstName LIKE ‘%b’
154. Which of the following is the correct datatype to store a bcrypt hash password?
Answers:
1. BYNARY (60)
2. TEXT (60)
3. VARCHAR (60)
4. 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:
1. SELECT TITLE FROM APPLICATIONS ORDER BY RAND() LIMIT 10
2. SELECT TITLE FROM APPLICATIONS WHERE RAND() > 10/(SELECT COUNT(*) FROM APPLICATIONS)
3. SELECT TITLE FROM APPLICATIONS LIMIT 10 BY RAND()
4. 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:
1. BLOB
2. LONGTEXT
3. VARCHAR (4096)
4. VARCHAR (4)
157 .Which of the following are ways to detect changes in a MySQL database schema?
Answers:
1. Using a programming language/framework that supports database schema change detection
2. Commit SQL scripts with DDL scripts to alter the schema; if there are new scripts, then the database schema has changed.
3. Using an (Object-relational mapping) ORM tool
4. None of these.
158 .Which of the following are ways to detect changes in a MySQL database schema?
Answers:
1. Using a programming language/framework that supports database schema change detection
2. Commit SQL scripts with DDL scripts to alter the schema; if there are new scripts, then the database schema has changed.
3. Using an (Object-relational mapping) ORM tool
4. None of these.
159. Which of the following statements is incorrect?
Answers:
1. Each InnoDB table created using the file-per-table mode goes into its own tablespace file, with a .ibd
2. A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace.
3. 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.
4. InnoDB system tablespace files can automatically shrink/purge when it reach maximum limit
160. Which of the following are valid encryption functions?
Answers:
1. MD5
2. AES_ENCRYPT
3. SHA512
4. SHA2
161. What are the Privileges required if the read_only system variable is enabled, to explicitly start a transaction with START TRANSACTION READ WRITE?
Answers:
1. Super_priv
2. Show_db_priv
3. Create_tablespace_priv
4. Execute_priv
162. With which of the following you can not associate a Trigger?
Answers:
1. Permanent tabe
2. Temporary Table
3. View
163. What is the default maximum number of indexes per MyISAM table?
Answers:
1. There is no limit to that
2. 64
3. 32
4. 16
164. Which variable can be modified to change the maximum size of a heap table?
Answers:
1. heap_table_size
2. max_heap_table_size
3. max_table_size
4. None of the above.
165. What is the default maximum number of columns per index in MyISAM?
Answers:
1. 4
2. 8
3. 12
4. 16
166. 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:
1. 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);
2. SELECT first_name FROM users WHERE age IN (15,16,17, 18, 19, 20, 21, 22, 23, 24);
3. SELECT first_name FROM users WHERE age BETWEEN 15 AND 24;
167. How would you return a random nickname from a very large “users” table?
Answers:
1. Like I normally do: SELECT nickname FROM users ORDER BY RAND() LIMIT 1;
2. 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;
3. 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).
168. How do you set execution timeout for SELECT statements in MySQL?
Answers:
1. By setting the system variable ‘exec_time_out’ to an integer value
2. There is a default value set in milliseconds that cannot be changed
3. By setting the system variable ‘max_execution_time’ to an integer value
4. The SELECT statements cannot have an execution timeout
169. Which database provides a standards-compliant means for accessing the MySQL Server’s metadata?
Answers:
1. INFORMATION_SCHEMA
2. META_SCHEMA
3. INFORMATION_METADATA
4. METADATA_SCHEMA
170. Which MySQL function is used to measure the speed of a specific MySQL expression or function?
Answers:
1. VALUES()
2. SPEED()
3. BENCHMARK()
4. SCHEMA()
171. Which storage engine in MySQL supports Transactions?
Answers:
1. MyISAM
2. Memory
3. BInnoDB
4. SCHEMA()
172. Which of the following Storage Engines in MySQL supports Foreign Keys?
Answers:
1. MyISAM
2. Memory
3. BInnoDB
4. SCHEMA()
173. Which storage engine in MySQL supports Transactions?
Answers:
1. MyISAM
2. Memory
3. InnoDB
4. Archive
174. What is the default TCP/IP port on which the MySQL server listens for connections?
Answers:
1. 80
2. 8080
3. 3306
4. 556
175. Which of the following are the new storage engines added in MySQL 5.0?
Answers:
1. InnoDB
2. ARCHIVE
3. MyISAM
4. FEDERATED
176. 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:
1. A table ‘t2’ is created with List Partitioning
2. Failure/Error occurs
3. No partitions are created
177. 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:
1. A table ‘t2’ is created with List Partitioning
2. Failure/Error occurs
3. No partitions are created
178. Which command would allow SELECT statements to be executed on a MySQL database without causing locks??
Answers:
1. SELECT * FROM TABLE_NAME WITH (nolock)
2. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
3. No commands are necessary: SELECT statements do not change data, and thus do not cause locks.
4. SET TRANSACTION-ISOLATION = READ-UNCOMMITTED
179. 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:
1. Select count(*) from EMP where empJoinDate between ‘2012-10-01′ and 2012-12-30’;
2. Select count(1) from EMP where empJoinDate between ‘2012-10-01′ and 2012-12-30’;
3. Select count(*) from EMP where empJoinDate between ‘2012-10-01′ and 2012-12-30’ group by EXTRACT(YEAR_MONTH from empJoinDate );
4. Select sum(1) from EMP where empJoinDate >=»2012-10-01″ and empJoinDate <=»2012-12-30″;
5. Non of the above
180. Which of the following is not a valid Trigger?
Answers:
1. Before Insert
2. Before Update
3. Before Drop
4. Before Delete
181. ENUM column can have a maximum of ____ distinct elements.
Answers:
1. 65,535
2. 64
3. 64,535
4. 65
182. Which statement is used to disable autocommit mode implicitly for a single series of statements?
Answers:
1. START TRANSACTION
2. COMMIT
3. ROLLBACK
4. NOCOMMIT
183. Which «MySQL» client utility option is useful for MySQL query execution on the command line?
Answers:
1. —line-numbers
2. —named-commands, -G
3. —pager[=command]
4. —execute=statement, -e statement
184. How many Auto Increment columns can be present per table in MySQL?
Answers:
1. 1
2. 5
3. As per the max value set in environment variables
4. There is no upper limit on the number of Auto Increment colums
185. What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?
Answers:
1. It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.
2. 5
3. It will still increment the value by 1.
4. It will reset the auto increment index.
5. None of the above.
186. Which of the following statements is true?
Answers:
1. When using a fixed-length type such as CHAR(n), stored values require n x 4 bytes for utf8
2. MySQL stores metadata in a Unicode character set, namely UTF-8
3. It will still increment the value by 1.
4. The default MySQL character set is UTF-8
5. All statements are true
187. Based on performance, which of the following is the quickest way to select a random row from a large table in MySQL?
Answers:
1. $result = mysql_query(“SELECT * from TABLE order by RAND() LIMIT 1”);
2. $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 «);
3. $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 «);
4. SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1
SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1
188. Full-text indexes can be used only with tables of which of the following Storage Engines?
Answers:
1. MyISAM
2. Memory
3. InnoDB
4. Archive
189. Which of the following is not a valid MyISAM table format?
Answers:
1. .FYI
2. frm
3. MYD
4. .MYI
190. 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?
Answers:
1. It is very quick and easy to cache.
2. There are no limitations regarding column types.
3. It requires much less disk space than the other two storage formats.
4. It is easy to reconstruct after a crash, because rows are located in fixed positions
191. What is the maximum length of a VARCHAR column?
Answers:
1. The length can be up to 255 bytes, however, it is shared among all columns and the character set used in a row.
2. The length can be up to 65,535 bytes, however, it is shared among all columns and the character set used in a row.
3. The length can be up to 32,765 bytes, however, it is shared among all columns and the character set used in a row.
192. What is the maximum length of a VARCHAR column?
Answers:
1. The length can be up to 255 bytes, however, it is shared among all columns and the character set used in a row.
2. The length can be up to 65,535 bytes, however, it is shared among all columns and the character set used in a row.
3. The length can be up to 32,765 bytes, however, it is shared among all columns and the character set used in a row.
193. Which of the following is not a valid statement?
Answers:
1. Double have accuracy up to eight place whereas float upto 18 places
2. Floating point numbers are stored in FLOAT whereas Double are stored in DOUBLE.
3. Float takes 4 bytes whereas DOUBLE takes eight bytes.
4. FLOAT is for single-precision whereas DOUBLE is for double-precision numbers.
194. 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:
1. Use «INSERT DELAYED» instead of «INSERT» to record visitors
2. 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.
3. 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.
4. There is no specialized setup necessary; 5-6 simple inserts per second is normal load for a MySQL database.
195. Is there any function in MySQL that allows for replace through a regular expression?
Answers:
1. SELECT();
   SELECT REPLACE(‘stackowerflow’, ‘ower’, ‘over’);
   Output: «stackoverflow»
2. USER DEFINED FUNCTIONS
   SELECT regex_replace(‘[^a-zA-Z0-9]+‘, ”, “%$&?/’|test><+-,][)(» );
3. Both A and B
4. None of the Above.
196. 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:
1. SELECT();
   SELECT REPLACE(‘stackowerflow’, ‘ower’, ‘over’);
   Output: «stackoverflow»
2. USER DEFINED FUNCTIONS
   SELECT regex_replace(‘[^a-zA-Z0-9]+‘, ”, “%$&?/’|test><+-,][)(» );
3. Option B:
   SELECT *
   FROM periods
   WHERE range_start <= @check_period_end AND range_end >= @check_period_start
4. Both options A & B
5. None of the above
197. Which of the following will find the distance between two pairs of latitude and longitude points?
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:
1. select . . .
   where locations.lat between X1 and X2
   and locations.Long between y1 and y2;
2. select . . .
   where locations.lat between X1 and X2
   or locations.Long between y1 and y2;
3. 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)
4. 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)
198. What is the effective maximum length of a VARCHAR column?
Answers:
1. 65,535 bytes
2. 64 bytes
3. 255 bytes
4. 256 bytes
199. What is the best way to select all the columns from the ’employee’ table except one column, ‘old_salary’, in the ‘factory’ database?
Answers:
1. 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;
2. Use DESCRIBE and with the results another SELECT query can be generated.
3. Select all the other columns from the ’employee’ table except the ‘old_salary’ column.
4. SELECT * FROM employee;
200. Which SQL statement is used to insert new data in a database?
Answers:
1. ADD NEW
2. INSERT INTO
3. INSERT NEW
4. ADD RECORD
201.Which of the following will get the record count for all tables in a MySQL database?
Answers:
1. SELECT SUM(TABLE_ROWS)
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = ‘{your_db}’;
2. SELECT TOTAL(TABLE_ROWS)
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = ‘{your_db}’;
3. SELECT COUNT(TABLE_ROWS)
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = ‘{your_db}’;
4. SELECT ROUND(TABLE_ROWS)
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = ‘{your_db}’;
202. Which SQL statement is used to update data in a database?
Answers:
1. SAVE
2. MODIFY
3. SAVE AS
4. UPDATE
203. 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:
1. 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);
2. 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;
3. 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);
4. 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);
204. 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:
1. 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);
2. 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;
3. 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);
4. 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);
205. Which among the following statements are true about using «NULL» in database columns?
Answers:
1. The LENGTH of NULL is NULL.
2. NULLs are sorted before empty strings.
3. The COUNT() function will count empty strings but not NULLs.
4. All of the above.
206. Which of the following will truncate all tables in a MySQL database?
Answers:
1. SELECT ‘TRUNCATE TABLE ‘ + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
2. SELECT Concat(‘TRUNCATE TABLE ‘, TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
3. SELECT Concat(‘TRUNCATE TABLE ‘, TABLE_NAME, ‘\n’) FROM INFORMATION_SCHEMA.TABLES
4. SELECT Concat(‘TRUNCATE TABLE ‘, TABLE_NAME, ‘;’) FROM INFORMATION_SCHEMA.TABLES
207. 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:
1. SELECT id,
   IF type = ‘P’ THEN amount = amount
   ELSE amount = amount — 1
   FROM report;
2. SELECT id,
   CASE type
   IF ‘P’ THEN amount = amount
   IF ‘N’ THEN amount = -amount
   END AS amount
   FROM report;
3. SELECT id, IF(type = ‘P’?amount:amount * -1) AS amount
   FROM report;
4. SELECT id, IF(type = ‘P’, amount, amount * -1) AS amount
   FROM report;
208. Which of the following can be used to check if a column in a row of a table is NULL?
Answers:
1. IS NULL
2. =
3. LIKE
4. <>
209. InnoDB prevents which of the following operations when innodb_force_recovery is greater than 0?
Answers:
1. INSERT
2. UPDATE
3. DELETE
4. SELECT
210. Which database is ignored by ‘mysqldump’ while dumping a collection of databases for backup?
Answers:
1. INFORMATION_SCHEMA
2. All the test databases
3. mysql database
4. All databases with MyISAM tables
211. How do you print the version of MySQL server?
Answers:
1. SELECT VERSION()
2. SELECT MYSQL_VERSION()
3. SELECT —VERSION
4. None of the above.
212. What is the maximum value of an unsigned TINYINT integer type?
Answers:
1. 256
2. 255
3. 127
4. 128
213. 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:
1. SELECT * FROM Customers WHERE LastName BETWEEN ‘Martinez’ AND ‘Smith’
2. SELECT LastName>’Martinez’ AND LastName<‘Smith’ FROM Customers • SELECT * FROM Customers WHERE LastName>’Martinez’ AND LastName<‘Smith’
3. SELECT Customers WHERE LastName BETWEEN ‘Martinez’ AND ‘Smith’
4. SELECT Customers WHERE LastName BETWEEN ‘Martinez’ AND ‘Smith’
214. How can you see all indexes defined for a table?
Answers:
1. SHOW INDEX ;
2. SHOW INDEX FROM ;
3. SHOW *INDEX FROM ;
4. None of the above.
215. Heap tables support which of the following data types?
Answers:
1. BLOB
2. FLOAT
3. TEXT
4. BIT
216. How many bytes of storage does the BIGINT integer type requires?
Answers:
1. 4
2. 8
3. 3
4. 2
217. Which of the following is not a valid BLOB type?
Answers:
1. TINYBLOB
2. MEDIUMBLOB
3. LONGBLOB
4. LARGEBLOB
218. MySQL supports some extensions that you probably cannot find in other SQL DBMs. Which SQL statements are part of these extensions?
Answers:
1. ALTER EVENT, CREATE EVENT and DROP EVENT
2. ALTER SERVER, CREATE SERVER and DROP SERVER
3. CREATE DATABASE, DROP DATABASE and ALTER DATABASE
4. ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE
219. Which of the following is not a valid comparison operator?
Answers:
1. <>
2. >=
3. <=
4. ^=
220. With SQL, how can you insert «Smith» as the «LastName» in the «Customers» table?
Answers:
1. INSERT INTO Customers (LastName) VALUES (‘Smith’)
2. INSERT INTO Customers (‘Smith’) INTO LastName
3. INSERT (‘Smith’) INTO Customers (LastName)
4. INSERT (LastName) VALUES (‘Smith’) INTO Customers
221. With SQL, how can you insert a new record into the «Customers» table?
Answers:
1. INSERT (‘John’, ‘Smith’) INTO Customers
2. INSERT VALUES (‘John’, ‘Smith’) INTO Customers
3. INSERT INTO Customers VALUES (‘John’, ‘Smith’)
4. INSERT VALUES (‘John’, ‘Smith’) INTO Customers
222. With SQL, how can you insert a new record into the «Customers» table?
Answers:
1. INSERT (‘John’, ‘Smith’) INTO Customers
2. INSERT VALUES (‘John’, ‘Smith’) INTO Customers
3. INSERT INTO Customers VALUES (‘John’, ‘Smith’)
4. INSERT VALUES (‘John’, ‘Smith’) INTO Customers
223. How can you change «Martinez» into «Smith» in the «LastName» column in the Customers table?
Answers:
1. UPDATE Customers SET LastName=’Martinez’ INTO LastName=’Smith’
2. MODIFY Customers SET LastName=’Smith’ WHERE LastName=’Martinez’
3. UPDATE Customers SET LastName=’Smith’ WHERE LastName=’Martinez’
4. MODIFY Customers SET LastName=’Martinez’ INTO LastName=’Smith’
224. Select valid numeric data types:
Answers:
1. TINYINT
2. FLOAT
3. LARGEINT
4. DOUBLEINT
5. DECIMAL
225. Which of the statements are true for user-defined variables?
Answers:
1. User variable names are not case sensitive and have a maximum length of 64 characters.
2. User variables are written as $var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “@”.
3. User-defined variables are session-specific. A user variable defined by one client cannot be seen or used by other clients.
226. What is the range of the values that can go in a column defined as DECIMAl(6,2) ?
Answers:
1. 9999.99 to -9999.99
2. 9999.99 to -999.99
3. 999999.99 to -999999.99
4. 999999.99 to -99999.99
227. Is select count(*) faster than select count(identifier) ?
Answers:
1. select count(*) is faster in InnoDB than in MyISAM
2. both are equal on MyISAM
3. select count(*) is faster in MyISAM than in InnoDB
4. both are equal on InnoDB
228. Which of the following is not a valid nonstandard string types?
Answers:
1. TINYTEXT
2. BLOBTEXT
3. MEDIUMTEXT
4. LONGTEXT
229. Select valid date and time functions in MySQL:
Answers:
1. GMT_TIME
2. DAYOFWEEK
3. STR_TO_MONTH
4. UTC_DATE
5. YEARWEEK
230. 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:
1. NVL
2. NVL2
3. NULLIF
4. COALESCE