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