1. What limits the type of data that goes into a table?
Answers:
• Constraints
• Prevents Restrictions Limitations
2. Which of the following queries returns all
the rows whose names start with "S"?
Answers:
• SELECT * FROM table
WHERE name LIKE = 'S*';
• None of these
• SELECT * FROM table
WHERE name = 'S%';
• SELECT
* FROM table WHERE name LIKE 'S%';
3. Which SQL statement is used to insert new
data in a database?
Answers:
• Insert New
• Insert
• Insert
into
• Insert data
4. What are the statements used to manage
transaction processing in MySQL?
Answers:
• COMMIT
and ROLLBACK
• SAVE and ROLLBACK
• SAVE and UNDO
• COMMIT and UNDO
5. With SQL, how do you select all the records
from a table named "Persons" where the "LastName" is
alphabetically between (and including) "Hansen" and
"Pettersen"?
Answers:
• SELECT
* FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
• SELECT
LastName>'Hansen' AND LastName<'Pettersen' FROM Persons
• SELECT * FROM Persons
WHERE LastName>'Hansen' AND LastName<'Pettersen'
6. With SQL, how do you select all the records
from a table named "Persons" where the value of the column
"FirstName" starts with an "a"?
Answers:
• SELECT * FROM Persons
WHERE FirstName LIKE '%a'
• SELECT * FROM Persons
WHERE FirstName='a'
• SELECT
* FROM Persons WHERE FirstName LIKE 'a%'
• SELECT * FROM Persons
WHERE FirstName='%a%'
7. Which clause will order results by the
last_name column?
Answers:
• ORDER
BY last_name
• SELECT BY last_name
• WHERE last_name IS
ALPHABETICAL
• All of these
8. What function eliminates beginning and ending
white space characters from a string?
Answers:
• LTRIM()
• MTRIM()
• STRIP()
• None of these
• TRIM()
9. What locks can be applied to MyISAM tables?
Answers:
• Print
• Write
• Read
and Write
• Read and Print
10. How do you rename a table?
Answers:
• DELETE
• UPDATE
• INSERT
• CHANGE
• ALTER
11. What is the maximum number of constituents
for a SET datatype?
Answers:
• 62
• 64
• 60
• 66
12. With SQL, how can you return the number of
records in the "Persons" table?
Answers:
• SELECT
COUNT(*) FROM Persons
• SELECT COLUMNS(*) FROM
Persons
• SELECT COUNT() FROM
Persons
• SELECT COLUMNS() FROM
Persons
13. How would you write a query to select all
teams that won either 2, 4, 6 or 8 games?
Answers:
• SELECT * FROM teams
WHERE team_won on (2, 4, 6, 8);
• SELECT * FROM teams
WHERE team_won like (2, 4, 6, 8);
• SELECT
* FROM teams WHERE team_won IN (2, 4, 6, 8);
• SELECT * FROM teams
WHERE team_won join (2, 4, 6, 8);
14. Which SQL statement is used to return only
different values?
Answers:
• SELECT DIFFERENT
• SELECT
DISTINCT
• SELECT UNIQUE
15. Which port does MySQL Server use by default?
Answers:
• 8080
• 443
• 3306
• 80
16. Which SQL keyword is used to sort the
result-set?
Answers:
• SORT BY
• SORT
• ORDER
• ORDER
BY
17. How can you change "Hansen" into
"Nilsen" in the "LastName" column in the Persons table?
Answers:
• UPDATE
Persons SET LastName='Nilsen' WHERE LastName='Hansen'
• MODIFY Persons SET
LastName='Nilsen' WHERE LastName='Hansen'
• UPDATE Persons SET
LastName='Hansen' INTO LastName='Nilsen'
• MODIFY Persons SET
LastName='Hansen' INTO LastName='Nilsen
18. A SELECT statement without a WHERE clause
returns _____.
Answers:
• All of
the rows from a table.
• An error, because a
SELECT statement is invalid without a WHERE clause.
• Nothing.
• All of the rows from a
table that match the previous WHERE clause.
19. Which SQL statement is used to extract data
from a database?
Answers:
• Select
• Get
• Extract
• Open
20. With SQL, how can you return all the records
from a table named "Persons" sorted descending by
"FirstName"?
Answers:
• SELECT * FROM Persons
ORDER FirstName DESC
• SELECT * FROM Persons
SORT BY 'FirstName' DESC
• SELECT
* FROM Persons ORDER BY FirstName DESC
• SELECT * FROM Persons
SORT 'FirstName' DESC
21. A datatype can be changed after a table has
been created.
Answers:
• False
• True
22. With SQL, how can you insert
"Olsen" as the "LastName" in the "Persons" table?
Answers:
• INSERT INTO Persons
('Olsen') INTO LastName
• INSERT
INTO Persons (LastName) VALUES ('Olsen')
• INSERT ('Olsen') INTO
Persons (LastName)
23. Which is TRIGGER in Mysql ?
Answers:
• Before Insert
• All
• Before Update
• After Delete
24. * in a SELECT statement portion does what?
Answers:
• Assign a Pointer
• Group By
• Multiply
• Nothing
• Select
All
25. Which of the following statements about
Stored Procedures is true?
Answers:
• All of
these
• They allow you to
store libraries of functions inside the database server
• They can provide a
common set of database operations to multiple applications
• They can improve
security by limiting users to specific approved queries
26. Which SQL statement is used to delete data
from a database?
Answers:
• COLLAPSE
• DELETE
• REMOVE
• ERASE
27. Which SQL statement is used to extract data
from a database?
Answers:
• GET
• OPEN
• SELECT
• EXTRACT
28. With SQL, how do you select a column named
"FirstName" from a table named "Persons"?
Answers:
• SELECT
Persons.FirstName
• SELECT
FirstName FROM Persons
• EXTRACT FirstName FROM
Persons
29. How do you remove a table from the database
Answers:
• DROP
TABLE
• DELETE TABLE
• REMOVE TABLE
• DESTROY TABLE
30. With SQL, how do you select all the records
from a table named "Persons" where the value of the column
"FirstName" is "Peter"?
Answers:
• SELECT [all] FROM
Persons WHERE FirstName LIKE 'Peter'
• SELECT [all] FROM
Persons WHERE FirstName='Peter'
• SELECT
* FROM Persons WHERE FirstName='Peter'
• SELECT * FROM Persons
WHERE FirstName<>'Peter'
31. Which SQL statement is used to update data
in a database?
Answers:
• Alter
• Update
• Save as
• Save
32. It is possible to add a constraint after a
table is created
Answers:
• true
• false
33. Which of the following is not a valid
aggregate function?
Answers:
• MAX
•
COMPUTE
• COUNT
• MIN
34. A table column can have null values
Answers:
• false
• true
35. With SQL, how can you insert a new record
into the "Persons" table?
Answers:
• INSERT ('Jimmy',
'Jackson') INTO Persons
• INSERT VALUES
('Jimmy', 'Jackson') INTO Persons
• INSERT
INTO Persons VALUES ('Jimmy', 'Jackson')
36. With SQL, how do you select all the columns
from a table named "Persons"?
Answers:
• SELECT
* FROM Persons
• SELECT [all] FROM
Persons
• SELECT *.Persons
• SELECT Persons
37. With SQL, how can you delete the records
where the "FirstName" is "Peter" in the Persons Table?
Answers:
• DELETE
FirstName='Peter' FROM Persons
• DELETE ROW
FirstName='Peter' FROM Persons
• DELETE
FROM Persons WHERE FirstName = 'Peter'
38. What does SQL stand for?
Answers:
• Strong Question
Language
• Structured Question
Language
•
Structured Query Language
39. What does SQL mean?
Answers:
•
Structured Query Language
• Simplified Query
Language
40. Which SQL statement is used to update data
in a database?
Answers:
• SAVE
• SAVE AS
• UPDATE
• MODIFY
41. Which SQL statement is used to insert new
data in a database?
Answers:
• INSERT NEW
• INSERT
INTO
• ADD RECORD
• ADD NEW
42. What identifies a row in a table as unique?
Answers:
• Main Key
• Regular Key
•
Primary Key
• Foreign Key
43. Which of the following is/are true about
MyISAM vs InnoDB?
Answers:
• Both MyISAM and InnoDB
can use full-text index searches
• All of
these are true
• InnoDB tables can have
relationship constraints, MyISAM tables do not
• You can have both
types of tables in one database
44. To select the multiple values from a column
as a string use the _________ function.
Answers:
• IMPLODE()
• JOIN()
• COMBINE()
•
GROUP_CONCAT()
45. Which of the following is a synonym for
"INDEX" in MySQL.
Answers:
• CURSOR
• POINTER
• KEY
• UNIQUE
• STORE
46. Which of the following options for the INSERT
statement will cause an existing row to be updated in the event of a duplicate Primary Key value?
Answers:
• ON REPEAT VALUE UPDATE
• ON REPEAT KEY UPDATE
• ON DUPLICATE VALUE
UPDATE
• ON
DUPLICATE KEY UPDATE
47. When joining tables using a left join, which
values are always preserved?
Answers:
• Values from the right
table are preserved for NULL values in the right table
• The join will delete
rows which contain a NULL value in the right table and preserve all other rows
• Values
from the left table are preserved for NULL values in the right table
• The join will delete
rows which contain a NULL value in the left table and preserve all other rows
48. Which of the following table declarations
creates column 'name' to hold a fixed-length string?
Answers:
• CREATE TABLE test
(column: name, type:CHAR(20));
• CREATE TABLE test
(VARCHAR(20));
• CREATE TABLE test (
name(20) );
• CREATE
TABLE test (name CHAR(20));
49. A ________ is a stored program that is
attached to a table or a view.
Answers:
•
trigger
• pseudofile
• None of the above is
correct.
• embedded SELECT
statement
50. How can you do an UPDATE?
Answers:
• UPDATE tab SET f1=1
AND f2=2;
• UPDATE tab.f1=1 ,
tab.f2=2;
• UPDATE tab.f1=1 AND
tab.f2=2;
• UPDATE
tab SET f1=1 , f2=2;
51. HAVING clause is used in combination with
_____.
Answers:
• GROUP
BY
• SELECT
• WHERE
• FROM
52. _____ displays the hour as an integer with
zero padding, in 24H format.
Answers:
• %H
• %h
• %K
• %k
53. Which of the following is the correct way to
retrieve rows with no duplicates?
Answers:
• SELECT * FROM TableA
WHERE ColumnX IS DISTINCT;
• SELECT * FROM TableA
WHERE ColumnX=UNIQUE("SomeValue");
• SELECT
DISTINCT * FROM TableA WHERE ColumnX="SomeValue";
• SELECT UNIQUE * FROM
TableA WHERE ColumnX="SomeValue";
• SELECT * FROM TableA
WHERE ColumnX IS UNIQUE;
54. What is the BDB Configuration for RAM
dedicated to holding rows and indexes?
Answers:
• bdb_max_lock
•
bdb_cache_size
• bdb_logdir
• bdb_home
55. The data type BLOB stands for:
Answers:
• None of these
• Binary
Large Object
• Big List Object
• Binary List Object
56. What is the default port number for MySQL?
Answers:
• 80
• 3396
• 3306
• 8080
• 3006
57. What does DCL stand for?
Answers:
• Data Context Language
• Define Context
Language
• Define Control
Language
• Data
Control Language
58. What statement would delete the data in a
table without deleting the table structure?
Answers:
• Alter Table
• Delete Table
• Drop Table
•
Truncate Table
59. What represents an unknown or unspecified
value?
Answers:
• UNSPEC
• NULL
• UNSPECIFIED
• ANY
• ()
60. How would you select all the user_name
values from records whose user_phonenumber field is null?
Answers:
• Any of these three
statements may work, depending on the version of MySQL being used.
• SELECT user_name FROM
users WHERE user_phonenumber;
• SELECT
user_name FROM users WHERE user_phonenumber IS NULL;
• SELECT user_name FROM
users WHERE user_phonenumber = NULL;
61. How would you write a query to select all
orders that contain either 3, 5, 7 or 9 items?
Answers:
• SELECT * FROM orders
WHERE LIMIT item_count (3, 5, 7, 9);
• SELECT * FROM orders
WHERE item_count = (3, 5, 7, 9);
• SELECT * FROM orders
WHERE item_count LIKE (3, 5, 7, 9);
• SELECT
* FROM orders WHERE item_count IN (3, 5, 7, 9);
62. You have just inserted a new row into a
table whose Primary Key has the AUTO_INCREMENT property, without specifying the
Primary Key. How would you retrieve the new row's Primary Key from the
database?
Answers:
• SELECT LAST_KEY_INC();
• GET_AUTO_ID();
• SELECT
LAST_INSERT_ID();
• You can't; the row
creation would have failed with a Key Constraint error.
63. What is the difference between an inner join
and an outer join?
Answers:
• Inner join deletes
rows that don't match; outer join does not delete any rows
• Inner
join returns only rows from both tables that match on the given conditional
expression
• Inner join returns
only rows from the first table; outer join returns rows from both tables
64. Which option gets the current time in the
following format? 2012-06-18 19:47:31
Answers:
• GET_FORMAT(NOW(),
'%Y-%m-%d %T')
• FORMAT_DATE(NOW(),
'%Y-%m-%d %T')
• UNIX_TIMESTAMP(NOW())
•
DATE_FORMAT(NOW(), '%Y-%m-%d %T');
65. What is the maximum length of a table name ?
Answers:
• 16 byte
• 64
byte
• 4 byte
• 32 byte
66. True or False? A table may be joined to
itself.
Answers:
• True
• False
67. The _____ statement enables listing of
tables that match a certain pattern.
Answers:
• SHOW OPEN TABLES
• SHOW DATABASE
• SHOW COLUMNS
• SHOW
TABLES
68. Having a key with a unique value for each
row, best describes?
Answers:
• FIRST KEY
• REFERENCE KEY
• UNIQUE KEY
• FOREIGN KEY
•
PRIMARY KEY
69. The correct way to average all of the values
from a column "values" from table "foo" is
Answers:
• SELECT AVERAGE(values)
FROM foo
• SELECT values.AVERAGE
FROM foo
• SELECT
AVG(values) FROM foo
• SELECT AVERAGE OF
values FROM foo
70. Which statement is required to add a column
to an existing table?
Answers:
• CREATE
• ALTER
• APPEND
• UPDATE
• NEW
71. The USE keyword is used to select a _____.
Answers:
• Row
• Table
• Column
•
Database
72. Which of these is not a valid name for a
column
Answers:
• Near
• Under
• From
• Far
73. Which of the following can add a row to a
table?
Answers:
• Alter
• Add
• Update
• Insert
74. The __________ application performs
verification, repair, analysis, and optimization of MySQL databases, tables,
and data.
Answers:
• mysqlshow
• mysqladmin
• mysqldump
•
mysqlcheck
75. Which of the following is a correct use of
the UNION operator?
Answers:
• SELECT * FROM Table A
UNION TableB
• SELECT
* FROM TableA UNION SELECT * FROM TableB
• SELECT * FROM UNION
TableA, TableB
• SELECT UNION * FROM
TableA, TableB
76. Which one of the following statements is
correct?
Answers:
• All of
these
• The ROLLUP modifier
can only be used with the GROUP BY clause
• The WITH ROLLUP
modifier will add an extra row to the results of a query.
77. What tests whether integer column i is at
least 1 but no more than 10?
Answers:
• i IN INTERVAL(1, 10)
• i IN INTERVAL(0, 11)
• i BETWEEN 0 AND 11
• i
BETWEEN 1 AND 10
78. Which set operator is used to combine the
result sets of two or more SELECT statements?
Answers:
• None of these
• UNION
• COMBINE
• MERGE
79. What is a surrogate Key ?
Answers:
• A key
that acts as a substitution to other natural keys
• a type of join
• Same as Natural key
• Same as Primary key
80. Which of the following statements about
MySQL optimization is True?
Answers:
• If GROUP BY is not
used, the HAVING and WHERE clauses will be merged
• Each table Index is
queried, and the best Index is used.
• Constant expressions
used with Indexes are only evaluated once
• All of
these
81. The OR operator displays a record if ANY
conditions listed are true. The AND operator displays a record if ALL of the
conditions listed are true
Answers:
• False
• False-True
• True-False
• True
82. Which of the following correctly calls the
stored procedure "test" which has no parameters?
Answers:
• CALL
test;
• Set @variable = test;
• SELECT test;
• SELECT test FROM
procedures;
83. Which of the following clauses can be used
in a SELECT statement to reduce the results to a unique list of values?
Answers:
• UNIQUE
• REDUCE
• PRIMARY
•
DISTINCT
84. I would like to retrieve the date with this
format : dd/mm/YYYY in a field called btimestamp. Choose the correct way.
Answers:
• SELECT FORMAT_DATE(btimestamp,'%d/%m/%Y')
AS bdate FROM ...
• SELECT
DATE_FORMAT(btimestamp,'%d/%m/%Y') AS bdate FROM ...
85. When a MySQL database is
"normalized", by common definition, it means which of the following?
Answers:
• That the data returned
within any given row is converted to a single object type as most appropriate
(string, int, etc.).
• That
the database is organized in a way to reduce data redundancy. 1NF, 2NF, and 3NF
forms are used as common guidelines.
• That the MySQL
"strict" mode is invoked to avoid incompatibilities with legacy SQL.
• That non-common
characters are stripped from values to ensure compatibility with given
character set.
• That common queries
assigned to functions to simplify statements.
86. Which of the following is used to count the
number of rows with different items?
Answers:
•
count(distinct)
• count(<column
name>)
• count(*)
• count(all)
87. Which of the following is used to control
case sensitivity?
Answers:
• binary
• bit_or()
• bit_count()
• bit_and()
88. Assign the custom `foo` variable with the
value `bar`
Answers:
• SET
@foo = 'bar';
• VAR @foo = 'bar';
• VAR $foo ='bar';
• SET $foo= 'bar';
• $foo = 'bar';
89. What does this statement: DELETE FROM xpto
WHERE 1;
Answers:
• Delete all records in
table 'xpto' and reset the AUTO_INCREMENT value to 0;
• Delete
all records in table 'xpto' but does not reset the AUTO_INCREMENT sequence
• Throw an error
depending on sql mode
• Delete all records in
table 'xpto' and resets the AUTO_INCREMENT sequence
90. True of false? TEXT is case-insensitive in
BLOB.
Answers:
• False
• True
91. Which of the following are true concerning
indexes?
Answers:
• Indexes can only be
done on primary key columns
• Indexes will slow down
selects, but speed up inserts
• Indexes can only be
done on columns containing numerical data
•
Indexes will slow down inserts, but speed up selects
• None are correct
92. What does the following command output:
'SELECT 1'
Answers:
• "1"
• 1
• '1'
93. Which of the following queries returns 100
results, starting from the 25th result?
Answers:
• SELECT book_title FROM
books LIMIT 25 UP TO 100
• SELECT book_title FROM
books LIMIT 25, 125
• SELECT
book_title FROM books LIMIT 24, 100
• SELECT book_title FROM
books LIMIT 24 TO 124
94. How would you remove all rows from a table
"STAFF"
Answers:
• REMOVE * FROM STAFF
• DELETE ALL STAFF
• DELETE
FROM STAFF
• REMOVE FROM STAFF
• DELETE * FROM STAFF
95. Which of the following is used to access a
result set stored in memory after execution of a query, allowing the result
data to be manipulated on a row-by-row basis?
Answers:
• Cursor
• View
• Trigger
• Index
96. What happens when a Unique or Primary Key
column is set to AUTO INCREMENT, and an INSERT would exceed the maximum value
for that column?
Answers:
• Further rows will be
inserted with the same value.
• The value "wraps
around" to the lowest value and continues incrementing.
• None of these
• It
stops incrementing, producing a duplicate key error.
97. In a WAMP environment, what is the correct
way to permanently change the largest possible packet that can be transmitted
to or from a MySQL server?
Answers:
• Edit
the my.ini file and change the setting max_allowed_packet. Then restart the
service.
• Using the Windows
command prompt, execute "SET GLOBAL max_allowed_packet=MB;". You do
not need to restart the service when changing globals directly on the server.
• Edit the my.ini file
and change the setting max_allowed_packet=M. You do not need to restart the
service when changing globals directly on the server.
• Open the MySQL network
data config tool and simply change the setting. The service will be restarted
automatically.
• Using the Windows
command prompt, execute "SET max_allowed_packet=M;". Then restart the
service.
98. The user-created stored function 'test'
takes a varchar(5) and an integer and returns a varchar. Which of the following
correctly calls the function?
Answers:
• CALL FUNCTION test
('asdfg', 6);
• SELECT test('asdfg',
6);
• CALL
test('asdfg', 6);
• SELECT test USING
'asdfg', 6;
99. What term describes the number of unique values
in an index?
Answers:
• Sub-part
• Seq-in-index
• Collation
•
Cardinality
100. Which foreign keys do MyISAM tables
support?
Answers:
• Foreign keys on the
primary key only
• Foreign keys on
columns other than the primary key.
• Foreign keys on
indexed columns.
• None,
MyISAM tables do not support foreign keys.
101. Given two tables, t1 and t2, the join
condition "t1.t2_id = t2.id" how do you get rows from t1 that have NO
counterparts in t2?
Answers:
• JOIN t2 EXCLUDING
t1.t2_id = t2.id
• LEFT
JOIN t2 ON t1.t2_id = t2.id WHERE t2.id IS NULL
• RIGHT JOIN t2 ON
t1.t2_id = t2.id
• EXCLUDING JOIN t2 ON
t1.t2_id = t2.id
• LEFT OUTER JOIN t2 ON
t1.t2_id = t2.id
102. Which of the following statements can be
used to remove user accounts from MySQL?
Answers:
• None of these
• DELETE USER user;
• REVOKE USER user;
• DROP
USER user;
103. What command executed by a client exports
data to a file?
Answers:
• SELECT
... INTO OUTFILE
• EXPORT ... INTO
OUTFILE
• COPY ... INTO OUTFILE
• MOVE ... INTO OUTFILE
104. The _____________ privilege is often used
when loading a file into a database table.
Answers:
• Process_priv
•
File_priv
• Grant_priv
• Reload_priv
105. What is a major difference between MyISAM
and InnoDB storage engines?
Answers:
• InnoDB is innovative
where MyISAM is not.
• InnoDB uses table
locking where MyISAM uses row locking.
• InnoDB
uses row locking where MyISAM uses table locking.
• All of these.
• None of these.
106. When using the grant command which phrase
sets the password of the user?
Answers:
• password
• using
•
Identified By
• SET passwd
• passwd
107. Which of the following MySQL client command
line arguments would you use to connect to a server?
Answers:
• % mysql servers
[options]
• %
mysql [options]
• % mysql server
[options]
• % mysql connect [options]
108. The GROUP BY clause of a SELECT statement
requires which of the following?
Answers:
• ORDER BY clause
• WHERE clause
• LIMIT clause
• HAVING clause
• None
of these
109. What is the maximum allowed length of a
column name?
Answers:
• 64
• 255
• 128
• 256
110. How would you select a value that lets
column B (if specified) override column A?
Answers:
• COALESCE(A, B)
• B IF A IS NOT NULL
• A IF B IS NOT NULL
•
COALESCE(B, A)
111. Which of these queries will return the
values of the "order" column in the "slides" table in
descending order?
Answers:
• All of these return
identical results
• SELECT order FROM
`slides` ORDER BY order DESC
• SELECT -order FROM
-slides ORDER BY -order DESC
• SELECT
`order` FROM `slides` ORDER BY `order` DESC
112. Which is NOT an available storage engine?
Answers:
• InnoDB
• CSV
• BDB
• MyISAM
• SQLite
113. In a LIKE clause, you can ask for any 6
letter value by writing:
Answers:
• LIKE .{6} Answer 5:
LIKE ^.{6}$
• LIKE
______ (six underscore characters)
• LIKE ??????
• LIKE ...... (six
periods)
114. Which of the following correctly redirects
the results of a select statement to a file?
Answers:
• SELECT * FROM TableA
where SomeColumn="a value" INTO OUTFILE 'out.txt';
• SELECT
* INTO OUTFILE 'out.txt' FROM TableA where SomeColumn="a value";
• SELECT * FROM TableA
where SomeColumn="a value" AND OUTFILE = 'out.txt';
115. Within a transaction, how do you apply an
exclusive lock with a SELECT?
Answers:
• SELECT ... DELETE
• SELECT ... ALTER
• SELECT
... FOR UPDATE
• SELECT ... EXCLUSIVE
116. How do you get the execution plan of a
query?
Answers:
• EXPLAIN SELECT
select_options
• DESCRIBE SELECT
select_options
• It's not possible
• QUERY_PLAN SELECT
select_options
• Both
EXPLAIN and DESCRIBE will work
117. Which one of these queries will result in
an error?
Answers:
• SELECT NULL
• SELECT 0
• SELECT
*
• All are incorrect,
because they are missing the FROM clause
• SELECT ''
118. Which of the following statements about
Views is FALSE?
Answers:
• Their SELECT
statements cannot contain subqueries in the FROM clause.
• They can save time and
effort on queries that are frequently executed.
• They
are not connected to a SELECT statement
• A trigger cannot be
associated with a View.
119. What switch will prevent MySQL from
listening via TCP?
Answers:
• networking false
• protocol tcp=false
• skip-tcp
•
skip-networking
• networking=off
120. How can you set "less" utility as
the default one for reading queries results?
Answers:
• Pager
less
• Redirect less
• Reader less
• Set less
• Viewer less
121. If you specify a column type as DECIMAL
(6,2), what range of values will it accept?
Answers:
• -999.99 to 99.99
• -999999.99 to
999999.99
• -9999.99 to 999.99
•
-9999.99 to 9999.99
122. What MySQL variable is used to limit a
single SQL statement's size?
Answers:
•
max_allowed_packet
• max_query_length
• max_length_query
• max_packet_size
• max_key_buffer
123. What will the following SQL return for the
`part` column: SELECT SUBSTRING_INDEX('my.delimited.string', '.', -1) AS `part`
Answers:
• my.delimited
• my
• string
• delimited.string
124. Which statements are used for changing the
default character set within a table?
Answers:
• ALTER
... CONVERT
• CHANGE ... COLLATION
• SET ... COLLATION
• ALTER ... SET
• MODIFY ... CONVERT
125. Which statement returns the current date in
YYYY-MM-DD format?
Answers:
• SELECT
CURDATE();
• SELECT DATE();
• SELECT NOW();
• All of these
126. When executing an equi-join, what syntax
can you use to specify the order in which the tables are joined?
Answers:
•
STRAIGHT_JOIN
• OUTER JOIN
• LEFT OUTER JOIN
• MULTIPLE JOIN
127. Within a transaction, how do you apply an
exclusive lock with a SELECT?
Answers:
• SELECT
... FOR UPDATE
• SELECT ... ALTER
• SELECT ... DELETE
• SELECT ... EXCLUSIVE
128. Which of the following statements about
Views is FALSE?
Answers:
• A trigger cannot be
associated with a View.
• They can save time and
effort on queries that are frequently executed.
• Their SELECT
statements cannot contain subqueries in the FROM clause.
• They
are not connected to a SELECT statement
129. Which one of the following flags causes the
MySQL engine to refuse UPDATE and DELETE commands that don't include a WHERE
clause?
Answers:
• --no-blank
• None of these
•
--i-am-a-dummy
• --safe-delete
130. What does this query result in? SELECT IF(
1, 2, 3 ) + IF(0, 1, 2 ) ;
Answers:
• 9
• 3
• Syntax Error
• 4
• 5
131. Which date calculation interval works on
days, hours, and minutes?
Answers:
• DAY_HOUR_MINUTE
•
DAY_MINUTE
• DAY_SECOND
• DAY_ALL
132. The __________ function allows the addition
of months to a given date.
Answers:
•
PERIOD_ADD
• MONTH_DIFF
• MONTH_ADD
• PERIOD_DIFF
133. Which of the following queries returns the
String "come"?
Answers:
• SELECT SUBSTRING
('welcome to MySQL', 4, 7);
• SELECT SUBSTRING
('welcome to MySQL' FROM -4 FOR 2);
• None
of these
• SELECT SUBSTRING
('welcome to MySQL', 4);
134. The _______________ function returns the
time in the HH:MM:SS format from the given number of seconds.
Answers:
• SYSDATE (seconds)
•
SEC_TO_TIME (seconds)
• NOW (seconds)
• TIME_FORMAT (seconds)
135. What privilege must you have to execute a
FLUSH statement?
Answers:
• GRANT_OPTION
• RELOAD
• ALTER
• DROP
• PROCESS
136. If you grant a user FILE priveliges, you're
allowing:
Answers:
• Read files on the
server, write new files and append to existing ones
• Read
and write (but not overwrite) files on the server
• Read files on the
server, but not write them
• Read, write and
overwrite files on the server
137. Which one of the following data types
causes a column to have the NOT NULL, PRIMARY KEY and AUTO_INCREMENT
properties?
Answers:
• INDEX
• PRIMARY
• UNIQUE
• SERIAL
138. A MySQL server supports connection pooling.
Answers:
• False
• True
139. Which option gets the current time in the
following format? 2012-06-18 19:47:31
Answers:
• UNIX_TIMESTAMP(NOW())
• GET_FORMAT(NOW(),
'%Y-%m-%d %T')
• FORMAT_DATE(NOW(),
'%Y-%m-%d %T')
•
DATE_FORMAT(NOW(), '%Y-%m-%d %T');
140. You have just inserted a new row into a
table whose Primary Key has the AUTO_INCREMENT property, without specifying the
Primary Key. How would you retrieve the new row's Primary Key from the
database?
Answers:
• You can't; the row
creation would have failed with a Key Constraint error.
• GET_AUTO_ID();
• SELECT
LAST_INSERT_ID();
• SELECT LAST_KEY_INC();
141. I would like to retrieve the date with this
format : dd/mm/YYYY in a field called btimestamp. Choose the correct way.
Answers:
• SELECT
DATE_FORMAT(btimestamp,'%d/%m/%Y') AS bdate FROM ...
• SELECT
FORMAT_DATE(btimestamp,'%d/%m/%Y') AS bdate FROM ...
142. When a MySQL database is
"normalized", by common definition, it means which of the following?
Answers:
• That common queries
assigned to functions to simplify statements.
• That
the database is organized in a way to reduce data redundancy. 1NF, 2NF, and 3NF
forms are used as common guidelines.
• That non-common
characters are stripped from values to ensure compatibility with given
character set.
• That the MySQL
"strict" mode is invoked to avoid incompatibilities with legacy SQL.
• That the data returned
within any given row is converted to a single object type as most appropriate
(string, int, etc.).
143. In linux how do you restart the MySQL
service?
Answers:
• restart mysql service
init 0 reboot
•
/etc/init.d/mysql restart service mysqld restart
144. Which SQL statement is used to insert new
data in a database?
Answers:
• Insert
• Insert data
• Insert
into
• Insert New
145. Which SQL statement is used to update data
in a database?
Answers:
• Alter
• Update
• Save as
• Save
146. Can I create another MySQL instance in the
same machine?
Answers:
• False
• True
147. With SQL, how do you select all the records
from a table named "Persons" where the "FirstName" is
"Peter" and the "LastName" is "Jackson"?
Answers:
• SELECT * FROM Persons
WHERE FirstName<>'Peter' AND LastName<>'Jackson'
• SELECT
FirstName='Peter', LastName='Jackson' FROM Persons
• SELECT
* FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'
148. Which SQL statement is used to update data
in a database?
Answers:
• SAVE AS
• UPDATE
• SAVE
• MODIFY
149. Which SQL statement is used to insert new
data in a database?
Answers:
• ADD NEW
• ADD RECORD
• INSERT
INTO
• INSERT NEW
150. What does the following command output:
'SELECT 1'
Answers:
• 1
• '1'
• "1"
151. What is A in ACID?
Answers:
• Analysis Allow Alter
•
Atomicity
152. Which of the following is the correct way
to retrieve rows with no duplicates?
Answers:
• SELECT
DISTINCT * FROM TableA WHERE ColumnX="SomeValue";
• SELECT * FROM TableA
WHERE ColumnX=UNIQUE("SomeValue");
• SELECT * FROM TableA
WHERE ColumnX IS UNIQUE;
• SELECT UNIQUE * FROM
TableA WHERE ColumnX="SomeValue";
• SELECT * FROM TableA
WHERE ColumnX IS DISTINCT;
153. What is the difference between an inner
join and an outer join?
Answers:
• Inner join returns
only rows from the first table; outer join returns rows from both tables
• Inner join deletes
rows that don't match; outer join does not delete any rows
• Inner
join returns only rows from both tables that match on the given conditional
expression
154. Which of the following is not a Spatial Data
Type?
Answers:
• CIRCLE
• GEOMETRY CIRCLE POINT
POLYGON
155. Assign the custom `foo` variable with the
value `bar`
Answers:
• $foo = 'bar';
• SET
@foo = 'bar';
• VAR $foo ='bar';
• SET $foo= 'bar';
• VAR @foo = 'bar';
156. In a WAMP environment, what is the correct
way to permanently change the largest possible packet that can be transmitted
to or from a MySQL server?
Answers:
• Edit
the my.ini file and change the setting max_allowed_packet. Then restart the
service.
• Open the MySQL network
data config tool and simply change the setting. The service will be restarted
automatically.
• Edit the my.ini file
and change the setting max_allowed_packet=M. You do not need to restart the
service when changing globals directly on the server.
• Using the Windows command
prompt, execute "SET GLOBAL max_allowed_packet=MB;". You do not need
to restart the service when changing globals directly on the server.
• Using the Windows
command prompt, execute "SET max_allowed_packet=M;". Then restart the
service.
157. ______ - Nothing that’s being read is
actively being changed by another transaction.
Answers:
•
ISOLATION
• ACID Transaction
Rollback
158. What does this statement: DELETE FROM xpto
WHERE 1;
Answers:
• Delete
all records in table 'xpto' but does not reset the AUTO_INCREMENT sequence
• Delete all records in
table 'xpto' and resets the AUTO_INCREMENT sequence
• Delete all records in
table 'xpto' and reset the AUTO_INCREMENT value to 0;
• Throw an error
depending on sql mode
159. What is the maximum size of the buffer for
client/server communication?
Answers:
• 2GB Unlimited 24MB
• 1GB
160. The user-created stored function 'test'
takes a varchar(5) and an integer and returns a varchar. Which of the following
correctly calls the function?
Answers:
• CALL FUNCTION test
('asdfg', 6);
• SELECT test USING
'asdfg', 6;
• CALL
test('asdfg', 6);
• SELECT test('asdfg',
6);
161. The default size of the buffer for
client/server communication is _______?
Answers:
• 1GB No Default Size
500MB
• 24MB
162. What happens when a Unique or Primary Key
column is set to AUTO INCREMENT, and an INSERT would exceed the maximum value
for that column?
Answers:
• Further rows will be
inserted with the same value.
• The value "wraps
around" to the lowest value and continues incrementing.
• It
stops incrementing, producing a duplicate key error.
• None of these
163. Which of the following correctly redirects
the results of a select statement to a file?
Answers:
• SELECT * FROM TableA
where SomeColumn="a value" INTO OUTFILE 'out.txt';
• SELECT * FROM TableA
where SomeColumn="a value" AND OUTFILE = 'out.txt';
• SELECT
* INTO OUTFILE 'out.txt' FROM TableA where SomeColumn="a value";
164. What parameters needed to make mysqldump as
a hot backup?
Answers:
• --hot-dump
--no-lock-tables --databases-no-lock
•
--single-transaction
165. How do you get the execution plan of a
query?
Answers:
• Both
EXPLAIN and DESCRIBE will work
• EXPLAIN SELECT
select_options
• QUERY_PLAN SELECT
select_options
• It's not possible
• DESCRIBE SELECT
select_options
166. Which one of these queries will result in
an error?
Answers:
• SELECT 0
• All are incorrect,
because they are missing the FROM clause
• SELECT
*
• SELECT NULL
• SELECT ''
167. Which of the following queries returns the
String "come"?
Answers:
• SELECT SUBSTRING ('welcome
to MySQL', 4);
• SELECT SUBSTRING
('welcome to MySQL' FROM -4 FOR 2);
• SELECT SUBSTRING
('welcome to MySQL', 4, 7);
• None
of these
No comments:
Post a Comment