> > MySQL MCQs > Basic MySQL MCQs

Basic MySQL MCQ

1. By default, all print jobs have a priority of ____.

-50

100

-100

Answer

Correct Answer: 50

Note: This Question is unanswered, help us to find answer for this one

2. The like conditional operator is used by the _____ operand1.

P_PRICE

V_STATE

P_QOH

V_CONTACT

Answer

Correct Answer: V_CONTACT

Note: This Question is unanswered, help us to find answer for this one

3. Which choice is an example of an aggregate function?Which choice is an example of an aggregate function?

NOW()NOW()

MID()MID()

FORMAT()FORMAT()

COUNT()

Answer

Correct Answer: COUNT()

Note: This Question is unanswered, help us to find answer for this one

4. The mysqldump command cannot generate output in _.

JSON

CSV

XML

TXT

Answer

Correct Answer: JSON

Note: This Question is unanswered, help us to find answer for this one

5. What is the correct usage of ENUM in MySQL?

Create table size (ENUM ('Small','Medium','Large'));

Create table ENUM (name ('Small','Medium','Large'));

Create table size (name: ENUM['Small','Medium','Large']);

Create table size (name ENUM('Small','Medium','Large'));

Answer

Correct Answer: Create table size (name ENUM('Small','Medium','Large'));

Note: This Question is unanswered, help us to find answer for this one

6. What function finds the current time or date in MySQL?

DATE()

GETDATE()

CURDATE()

CURRENT()

Answer

Correct Answer: CURDATE()

Note: This Question is unanswered, help us to find answer for this one

7. Inside a transaction, several operations need to be performed. What would you do if an exception happens during that transaction?

UNDO

UNCOMMIT

ROLLBACK

REVERSE

Answer

Correct Answer: ROLLBACK

Note: This Question is unanswered, help us to find answer for this one

8. How are permissions implemented in MySQL?

Encrypted algorithms

Access control lists

User settings

Administrator schema

Answer

Correct Answer: Access control lists

Note: This Question is unanswered, help us to find answer for this one

9. What is the valid way to create a database view in MySQL?

CREATE VIEW v1 SELECT * FROM t1 WHERE col1 > 10;

CREATE VIEW v1 AS BEGIN SELECT * FROM t1 END;

CREATE VIEW v1 BEGIN SELECT * FROM t1 END;

CREATE VIEW v1 AS SELECT * FROM t1;

Answer

Correct Answer: CREATE VIEW v1 AS SELECT * FROM t1;

Note: This Question is unanswered, help us to find answer for this one

10. The left and right joins are also known as _.

Inner Join

Natural Join

Outer Join

Cartesian Join

Answer

Correct Answer: Outer Join

Note: This Question is unanswered, help us to find answer for this one

11. Which option modifier tells a program not to exit with an error if it does not recognize the option, but instead to issue a warning?

--verbose

--skip

--skip-error

--loose

Answer

Correct Answer: --loose

Note: This Question is unanswered, help us to find answer for this one

12. Why would you use a common table expression (CTE)?

To define queries for later reuse for the duration of the current session

To create temporary tables that can be used to pre-select often-used result sets.

To calculate a new single value from a result set and return it to the query parser.

To break down complex queries and allow reuse within a query.

Answer

Correct Answer: To break down complex queries and allow reuse within a query.

Note: This Question is unanswered, help us to find answer for this one

13. A stored routine is a set of SQL statements stored on the server and takes form as either a procedure or a function. Which statement cannot be used inside stored routines?

SELECT

USE

SET

DECLARE

Answer

Correct Answer: USE

Note: This Question is unanswered, help us to find answer for this one

14. Which choice is not an available string type for a column?

ENUM

SET

BIT

CHAR

Answer

Correct Answer: BIT

Note: This Question is unanswered, help us to find answer for this one

15. How would you retrieve data on all the customers where no phone number is stored?

SELECT * FROM customers WHERE PhoneNumber = NULL;

SELECT * FROM customers WHERE PhoneNumber IS NOT VALID;

SELECT * FROM customers WHERE PhoneNumber IS NULL;

SELECT * FROM customers WHERE PhoneNumber IS UNKNOWN;

Answer

Correct Answer: SELECT * FROM customers WHERE PhoneNumber IS NULL;

Note: This Question is unanswered, help us to find answer for this one

16. Explain the security aspect of stored procedures

Stored procedures are not secure, because they can be executed from the command line as the root user

Stored procedures are secure, because the owner of the stored procedure can decide to whom access is granted

Stored procedures are secure, because applications can be given access to stored procedures and not any underlying variables

Stored procedures are not secure, because they can execute statements to drop tables or bulk delete data

Answer

Correct Answer: Stored procedures are secure, because applications can be given access to stored procedures and not any underlying variables

Note: This Question is unanswered, help us to find answer for this one

17. What is the equivalent of the mysqladmin reload command?

Mysqladmin flush-threads

Mysqladmin flush-tables

Mysqladmin flush-privileges

Mysqladmin flush-all

Answer

Correct Answer: Mysqladmin flush-privileges

Note: This Question is unanswered, help us to find answer for this one

18. Which command will return a list of triggers in the current database?

DISPLAY TRIGGERS;

SHOW TRIGGERS;

SELECT ALL TRIGGERS;

SELECT * FROM information_schema.triggers;

Answer

Correct Answer: SHOW TRIGGERS;

Note: This Question is unanswered, help us to find answer for this one

19. What is the maximum number of columns that can be used by a single table index?

Answer

Correct Answer: 16

Note: This Question is unanswered, help us to find answer for this one

20. Which is a valid constructor for a class named User?

Public construct User() {}

Public User() {}

Public instance User() {}

Public init User() {}

Answer

Correct Answer: Public User() {}

Note: This Question is unanswered, help us to find answer for this one

21. What is the advantage of using a temporary table instead of a heap table?

The temporary table will be dropped when the database is restarted.

Temporary tables can be shared among clients, which makes them more usable in group development environments.

The temporary table will be dropped as soon as your session disconnects.

Creating a temporary table does not require any special privileges.

Answer

Correct Answer: The temporary table will be dropped as soon as your session disconnects.

Note: This Question is unanswered, help us to find answer for this one

22. Which choice is not a valid model for a stored procedure parameter?

INOUT

OUT

IN OUT

Answer

Correct Answer: IN OUT

Note: This Question is unanswered, help us to find answer for this one

23. What is the product of the database designing phase?

All tables, columns, data types, indexes and their relationships

A list of entities, their relationship, and constraints

All tables and their names, which are needed to implement the logical model

A list of entities, their relationship, constraints, data types, and cardinalities

Answer

Correct Answer: A list of entities, their relationship, constraints, data types, and cardinalities

Note: This Question is unanswered, help us to find answer for this one

24. Which query lists the databases on the current server?

SHOW DATABASES;

LIST ALL DATABASES;

LIST DATABASES;

SHOW DB;

Answer

Correct Answer: SHOW DATABASES;

Note: This Question is unanswered, help us to find answer for this one

25. If you want to use MyISAM instead of InnoDB, which option do you need to specify in the CREATE TABLE statement?

ENGINE

PARTITION

STORAGE

TABLESPACE

Answer

Correct Answer: ENGINE

Note: This Question is unanswered, help us to find answer for this one

26. You are importing data as JSON into a new table. You run CREATE TABLE json_data ( city JSON ); and insert rows into this table. What is the correct syntax to see the list of cities?

SELECT city FROM json_data;

SELECT city->>'$.name' city FROM json_data;

SELECT city.name city FROM json_data;

SELECT city->'$.name' city FROM json_data;

Answer

Correct Answer: SELECT city->>'$.name' city FROM json_data;

Note: This Question is unanswered, help us to find answer for this one

27. You need to restore a MySQL database from a backup file. Which command-line tool do you use for the actual data import, after re-creating the database?

Mysqld

Mysql

Mysqladmin

Mysqldump

Answer

Correct Answer: Mysql

Note: This Question is unanswered, help us to find answer for this one

28. Which type of backup includes all the changes made to the data since the last full backup was performed?

Snapshot

Logical

Differential

Incremental

Answer

Correct Answer: Differential

Note: This Question is unanswered, help us to find answer for this one

29. When working with MySQL cursor, what must you also declare?

DEFAULT value

RETURN variable

SQLEXCEPTION routine

NOT FOUND handler

Answer

Correct Answer: NOT FOUND handler

Note: This Question is unanswered, help us to find answer for this one

30. Which statement about the TRUNCATE TABLE statement is true?

It will stop and issue an error when it encounters a row that is referenced by a row in a child table.

It always first drops, then re-creates a new table.

It deletes rows one by one on tables with foreign key constraints.

It does not invoke the DELETE triggers associated with the table.

Answer

Correct Answer: It always first drops, then re-creates a new table.
It does not invoke the DELETE triggers associated with the table.

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

31. You are loading data into a table. Which command can you use to make sure that all data is inserted and duplicates rows are discarded?

INSERT IGNORE

INSERT UNIQUE

INSERT INTO

INSERT DISTINCT

Answer

Correct Answer: INSERT IGNORE

Note: This Question is unanswered, help us to find answer for this one

32. What is one reason to introduce data redundancy into a normalized database design?

To reduce corruption in data

To reduce storage space

To make the system faster

To prevent data anomalies

Answer

Correct Answer: To reduce corruption in data

Note: This Question is unanswered, help us to find answer for this one

33. In recent versions of MySQL (8.0+), what's the correct syntax to declare a CTE (Common Table Expression)?

WITH (SELECT id FROM users) as cte, SELECT ...

WITH (SELECT id FROM users) as cte SELECT ...

WITH cte as (SELECT id FROM users), SELECT ...

WITH cte as (SELECT id FROM users) SELECT ...

Answer

Correct Answer: WITH cte as (SELECT id FROM users) SELECT ...

Note: This Question is unanswered, help us to find answer for this one

34. Management has requested that you build an employee database. You need to include each employee's current position and salary, as well as all prior positions and salaries with the company. You decide to use a one-to-many structure: an employee table with the main information such as name and address, and an employment table with position and salary history. You can use the employeeID field to connect them. What is employment.employeeID an example of?

Primary key;

Secondary key;

Foreign key;

Alternate key;

Answer

Correct Answer: Foreign key;

Note: This Question is unanswered, help us to find answer for this one

35. In which table does MySQL store passwords for user accounts?

Mysql.accounts;

Mysql.passwords;

Mysql.admin;

Mysql.user;

Answer

Correct Answer: Mysql.user;

Note: This Question is unanswered, help us to find answer for this one

36. Which choice is not one of the table maintenance statements?

CHECK TABLE;

CREATE TABLE;

ANALYZE TABLE;

OPTIMIZE TABLE;

Answer

Correct Answer: CREATE TABLE;

Note: This Question is unanswered, help us to find answer for this one

37. How would you list the full set of tables in the currently selected database?

SELECT * FROM DATABASE;

SHOW TABLES;

LIST TABLES;

SELECT ALL TABLES;

Answer

Correct Answer: SHOW TABLES;

Note: This Question is unanswered, help us to find answer for this one

38. How can you list all columns for a given table?

SHOW table COLUMNS;

SHOW COLUMNS FROM table;

LIST table COLUMNS;

SELECT COLUMNS FROM table;

Answer

Correct Answer: SHOW COLUMNS FROM table;

Note: This Question is unanswered, help us to find answer for this one

39. What is the MySQL perror command-line utility used for?

To display your version of MySQL

To display operating system error codes

To display default settings that are in error

To display storage error codes

Answer

Correct Answer: To display storage error codes

Note: This Question is unanswered, help us to find answer for this one

40. Which choice is not a processing algorithm for database views?

Merge

Updatable

Temptable

Undefined

Answer

Correct Answer: Updatable

Note: This Question is unanswered, help us to find answer for this one

41. You need to run a complex query with recursive subqueries, but without creating a stored procedure or a function. Which command or clause do you use?

COLLATE

UNION

FULL JOIN

WITH

Answer

Correct Answer: WITH

Note: This Question is unanswered, help us to find answer for this one

42. You manage a database with a table "customers". You created a temporary table also called "customers" with which you are working for the duration of your session. You need to recreate the temporary table with different specs. Which command do you need to run first?

Create temporary table customers;

Drop temp table customers;

Drop table customers;

Drop temporary table customers;

Answer

Correct Answer: Drop temporary table customers;

Note: This Question is unanswered, help us to find answer for this one

43. You need to make your mysql system secure against attackers. What are you not supposed to do?

Run MySQL server as a normal user.

Grant PROCESS or SUPER privilege to other users.

Run MySQL server as the unix root user.

Use the compressed protocol.

Answer

Correct Answer: Run MySQL server as the unix root user.

Note: This Question is unanswered, help us to find answer for this one

44. You need to make an exact copy of a table, with all columns and indexes. How can you get all of the information needed to accomplish this?

Create table

Clone table

Insert into

Show create table

Answer

Correct Answer: Show create table

Note: This Question is unanswered, help us to find answer for this one

45. Which is the correct syntax of an extended insert statement?

Insert into cars (make, model, year) values ('Ford', 'Mustang', 2002) ('Mercedes', 'C', 2003)

Insert into cars (make, model, year) values ('Ford', 'Mustang', 2002) values ('Mercedes', 'C', 2003)

Insert into cars (make, model, year) extended ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)

Insert into cars (make, model, year) values ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)

Answer

Correct Answer: Insert into cars (make, model, year) values ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)

Note: This Question is unanswered, help us to find answer for this one

46. Which statement can you use to load data from a file into the table?

Cat file|mysql

Load data infile (correct if the file is already on the server)

Load data local infile (also correct but only if the file is from the client)

Extended insert statement

Answer

Correct Answer: Load data infile (correct if the file is already on the server)

Note: This Question is unanswered, help us to find answer for this one

47. Later versions of mysql support the native json data type for storing json documents. What is a drawback of json columns?

Inefficient for storing json documents

Cannot be indexed directly

Documents cannot be validated when stored in json columns

Cannot be normalized

Answer

Correct Answer: Cannot be indexed directly

Note: This Question is unanswered, help us to find answer for this one

48. What table cannot have a trigger associated with it?

Temporary

System

Large

New

Answer

Correct Answer: System

Note: This Question is unanswered, help us to find answer for this one

49. What mysql statement is used to check which accounts have specific privileges?

Show grants (displays the privileges and roles that are assigned to a MySQL user account or role)

Show privileges (shows the list of system privileges that the MySQL server supports)

Show access

Show user permissions

Answer

Correct Answer: Show grants (displays the privileges and roles that are assigned to a MySQL user account or role)

Note: This Question is unanswered, help us to find answer for this one

50. Each time MySQL is upgraded, it is best to execute mysql_upgrade, which looks for incompatibilities with the upgraded MySQL server. What does this command do, upon finding a table with a possible incompatibility?

It performs a table check and, if problems are found, attempts a table repair.

It stops and notifies the server administrator that the upgrade cannot complete until the incompatibility issue are resolved.

It provides a full report of the table specifications and the incompatibilities to the server administrator.

It performs a table check and, if problems are found, displays the information for the server administrator to take action.

Answer

Correct Answer: It performs a table check and, if problems are found, attempts a table repair.

Note: This Question is unanswered, help us to find answer for this one

51. What is the requirement for using a subquery in the SELECT clause?

The subquery must use an aggregate function.

The subquery must refer to the same table as the main query.

The subquery must return a single value.

The subquery must return at least one value.

Answer

Correct Answer: The subquery must return a single value.

Note: This Question is unanswered, help us to find answer for this one

52. One form of backup, replication, enables you to maintain identical data on multiple servers, as a _ configuration.

Remote-local

Parent-child

Master-slave

Logical-physical

Answer

Correct Answer: Master-slave

Note: This Question is unanswered, help us to find answer for this one

53. You need to export the data in the customers table into a CSV file, with columns headers in the first row. Which clause do you add to your MySQL command?

JOIN

WITH HEADERS

UNION

WITH COLUMNS

Answer

Correct Answer: UNION

Note: This Question is unanswered, help us to find answer for this one

54. After installing MySQL, it may be necessary to initialize the _ which may be done automatically with some MySQL installation methods.

Storage engine

User accounts

Grant tables

Data directory

Answer

Correct Answer: Data directory

Note: This Question is unanswered, help us to find answer for this one

55. MySQL option files provide a way to specify commonly used options so that they need not be entered on the command line each time you run a program. What is another name for the option files?

Variable settings

Configuration files

Help files

Default settings

Answer

Correct Answer: Configuration files

Note: This Question is unanswered, help us to find answer for this one

56. In an efficiently designed relational database, what does every table have?

Set of triggers

Sequential id field

Minimum of three columns

Primary key

Answer

Correct Answer: Primary key

Note: This Question is unanswered, help us to find answer for this one

57. How do you select every row in a given table named "inventory"?

SELECT all FROM inventory;

FROM inventory SELECT all;

FROM inventory SELECT *;

SELECT * FROM inventory;

Answer

Correct Answer: SELECT * FROM inventory;

Note: This Question is unanswered, help us to find answer for this one

58. What is the difference between DROP and TRUNCATE?

They both refer to the same operation of deleting the table completely.

They both refer to the same operation of clearing the table, but keeping its definition intact.

TRUNCATE deletes table completely, removing its definition as well. DROP clears the table but does not delete the definition.

DROP deletes table completely, removing its definition as well. TRUNCATE clears the table but does not delete the definition.

Answer

Correct Answer: DROP deletes table completely, removing its definition as well. TRUNCATE clears the table but does not delete the definition.

Note: This Question is unanswered, help us to find answer for this one

59. How can you filter duplicate data while retrieving records from a table?

DISTINCT

WHERE

LIMIT

Answer

Correct Answer: DISTINCT

Note: This Question is unanswered, help us to find answer for this one

60. A trigger is a database object that is associated with a table, and that activates when a particular event occurs for the table. Which three events are these?

INSERT, UPDATE, DELETE

CREATE, ALTER, DROP

OPEN, FETCH, CLOSE

DECLARE, SET, SELECT

Answer

Correct Answer: INSERT, UPDATE, DELETE

Note: This Question is unanswered, help us to find answer for this one

61. If you need to order a table of movies by name, which query will work?

SELECT * FROM movies GROUP BY name

SELECT * FROM movies ORDER BY name

SELECT * FROM movies ORDER TABLE by name

SELECT * FROM movies FILTER BY name

Answer

Correct Answer: SELECT * FROM movies ORDER BY name

Note: This Question is unanswered, help us to find answer for this one

62. How does MySQL differ from SQL?

SQL is a standard language for retrieving and manipulating data from structured databases. MySQL is a nonrelational database management system that is used to manage SQL databases.

SQL is a standard language for retrieving and manipulating data from structured databases. MySQL is a relational database management system that is used to manage SQL databases.

They are not different. MySQL and SQL refer to the same thing.

My SQL is a language, and SQL is a software application.

Answer

Correct Answer: SQL is a standard language for retrieving and manipulating data from structured databases. MySQL is a relational database management system that is used to manage SQL databases.

Note: This Question is unanswered, help us to find answer for this one

63. In SELECT * FROM clients; what does clients represent?

A SQL query

A SQL statement

A database

A table

Answer

Correct Answer: A table

Note: This Question is unanswered, help us to find answer for this one

64. How can you remove a record using MySQL?

DELETE

DELETE FROM

REMOVE

REMOVE FROM

Answer

Correct Answer: DELETE FROM

Note: This Question is unanswered, help us to find answer for this one

65. In MySQL, queries are always followed by what character?

Line break

Colon

Semicolon

Period

Answer

Correct Answer: Semicolon

Note: This Question is unanswered, help us to find answer for this one

66. What is the best type of query for validating the format of an email address in a MySQL table?

A SQL query using partitions

A SQL query using IS NULL

A SQL query using a regular expression

A SQL query using LTRIM Or RTRIM

Answer

Correct Answer: A SQL query using a regular expression

Note: This Question is unanswered, help us to find answer for this one

67. Which MySQL command modifies data records in a table?

UPDATE

MODIFY

CHANGE

ALTER

Answer

Correct Answer: UPDATE

Note: This Question is unanswered, help us to find answer for this one

68. MySQL uses security based on _ for all connections, queries, and other operations that users can attempt to perform.

Administrator schema

Encrypted algorithms

User settings

Access control lists

Answer

Correct Answer: Access control lists

Note: This Question is unanswered, help us to find answer for this one

69. Which MySQL command shows the structure of a table?

INFO table;

SHOW table;

STRUCTURE table;

DESCRIBE table;

Answer

Correct Answer: DESCRIBE table;

Note: This Question is unanswered, help us to find answer for this one

70. MySQL programs are a set of command-line utilities that are provided with typical MySQL distributions. MySQL is designed to be a database.

Database and programming

User and administrator

Client and server

Syntax and objects

Answer

Correct Answer: Client and server

Note: This Question is unanswered, help us to find answer for this one

71. MySQL server can operate in different SQL modes, depending on the value of the sql_mode system variable. Which mode changes syntax and behavior to conform more closely to standard SQL?

TRADITIONAL

ANSI

MSSQL

STRICT

Answer

Correct Answer: ANSI

Note: This Question is unanswered, help us to find answer for this one

72. Which query would NOT be used to administer a MySQL server?

USE db

SELECT column FROM tbl

SHOW COLUMNS FROM tbl

SHOW TABLES

Answer

Correct Answer: SELECT column FROM tbl

Note: This Question is unanswered, help us to find answer for this one

73. If you were building a table schema to store student grades as a letter (A, B, C, D, or F) which column type would be the best choice?

ENUM

OTEXT

VARCHAR

LONGTEXT

Answer

Correct Answer: ENUM

Note: This Question is unanswered, help us to find answer for this one

74. MySQL uses environment variables in some of the programs and command-line operations. Which variable is used by the shell to find MySQL programs?

DIR

HOME

PATH

MYSQL_HOME

Answer

Correct Answer: PATH

Note: This Question is unanswered, help us to find answer for this one

75. Which option of most MySQL command-line programs can be used to get a description of the program's different options?

--options

--help

-h

Answer

Correct Answer: --help

Note: This Question is unanswered, help us to find answer for this one

76. You must ensure the accuracy and reliability of the data in your database. You assign some constraints to limit the type of data that can go into a table. What type of constraints are you assigning?

Row level

Database level

Column level

Function level

Answer

Correct Answer: Column level

Note: This Question is unanswered, help us to find answer for this one

77. You need to export the entire database, including the database objects, in addition to the data. Which command-line tool do you use?

Mysqlexport

Mysqladmin

Mysqldump

Mysqld

Answer

Correct Answer: Mysqldump

Note: This Question is unanswered, help us to find answer for this one

78. When you have a subquery inside of the main query, which query is executed first?

The subquery is never executed. Only the main query is executed.

They are executed at the same time

The main query

The subquery

Answer

Correct Answer: The subquery

Note: This Question is unanswered, help us to find answer for this one

79. If there is no index, the dbms will perform a _____ scan.

Loop

Range

Row ID table access

Full table

Answer

Correct Answer: Full table

Note: This Question is unanswered, help us to find answer for this one

80. If an error occurs, each of the numeric functions returns a/an ____________ value.

LTRIM

Ewilliam

Null

Parse

Answer

Correct Answer: Null

Note: This Question is unanswered, help us to find answer for this one

81. Databases are stored in ______ so that they are available when needed.

Data dashboards

Data mines

Data sources

Data warehouses

Data matrices

Answer

Correct Answer: Data warehouses

Note: This Question is unanswered, help us to find answer for this one

82. Attributes may share a ____.

Name

Location

Domain

Table

Answer

Correct Answer: Domain

Note: This Question is unanswered, help us to find answer for this one

83. A distributed database is composed of several parts known as database _____.

Horizontal

Vertical

Fragments

None of these

Answer

Correct Answer: Fragments

Note: This Question is unanswered, help us to find answer for this one

84. A centralized database management is subject to a problem such as _____.

Maintaining and operating small database systems

Dependence on multiple sites

Growing numbers of remote locations

Answer

Correct Answer: Growing numbers of remote locations

Note: This Question is unanswered, help us to find answer for this one

85. Which of the following operators have the same precedence as the <= operator?

<=>, >=, >, <=, <

<=>

Answer

Correct Answer: <=>, >=, >, <=, <
<=>

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

86. Which of the following data types can have an additional attribute AUTO_iNCREMENT?

lNT

FLOAT

DECIMAL

DOUBLE

BIT

Answer

Correct Answer: lNT
FLOAT

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

87. Which of the following values do logical operators evaluate to in MySQL?

El 1

TRUE

FALSE

NULL

Answer

Correct Answer: 0
TRUE
FALSE

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

88.
What will be the result of the following query?
SELECT ExtractValue()

a/b

None of the above

Answer

Correct Answer:

Note: This Question is unanswered, help us to find answer for this one

89.
What will be the result of the following query?
SELECT STRCMP()

-1

None of the above

Answer

Correct Answer:

Note: This Question is unanswered, help us to find answer for this one

90.
What will be the result of the following query?
SELECT(This Is four lines)

This

Four

Lines

This Is Four Lines

This\is\Four\Lines

None of the above

Answer

Correct Answer:

This

Four

Lines

Note: This Question is unanswered, help us to find answer for this one

91. Which of the following correctly describes the RLIKE operator?

It performs simple pattern matching.

It is a synonym for the REGEXP Operator.

It is used for comparing sounds.

It performs negation of simple pattern matching.

Answer

Correct Answer: It is a synonym for the REGEXP Operator.

Note: This Question is unanswered, help us to find answer for this one

92. For the COERCIBILITYO function. what does coercibility value of 0 mean?

Ignorable

Implicit collation

Coercible

Explicit collation

Answer

Correct Answer: Explicit collation

Note: This Question is unanswered, help us to find answer for this one

93.
What will be the result of the following two queries?
1. SELECT POW(2.2):
2. SELECT POWER(2.3):

Query 1 will return 4r. while and query 2 will return an error.

Query 1 will return an error. while query 2 will return 8.

Query 1 will return 4. while query 2 will return 8.

None of the above.

Answer

Correct Answer:

Query 1 will return 4. while query 2 will return 8.

Note: This Question is unanswered, help us to find answer for this one

94. Which of the following operators are supported by boolean full-text search?

()

Answer

Correct Answer: +
-
()
u

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

95. Invalid year data type values are converted to which of the following?

9999

0000

None of the above

Answer

Correct Answer: 0000

Note: This Question is unanswered, help us to find answer for this one

96. In MySQL 5.6.5. how many bytes are required to store the DATETIME data type?

0 8 bytes

0 5 bytes + fractional seconds storage

0 4 bytes + fractional seconds storage

0 3 bytes

Answer

Correct Answer: 0 5 bytes + fractional seconds storage

Note: This Question is unanswered, help us to find answer for this one

97.
What will be the result of the following query?
SELECT lNSERT()

EquWhere

EquWherer

ator

WheEquator

Answer

Correct Answer:

EquWherer

Note: This Question is unanswered, help us to find answer for this one

98.
Which of the following holds correct while optimizing the query?
1) Use ((a AND D) AND c OR (((a AND D) AND (c AND d)))) instead of
(a AND D AND c) on (3 AND b AND c AND d)
2)Use b>5 AND b=c AND 825 instead of (a
3}Use (8>=5 AND 8:5) OR (8:6 AND 5:5) 0R (8:7 AND 5:6) instead of B=5 OR 8=6

2 and 3

Only1

Only 3

None of the above

Answer

Correct Answer:
2 and 3

Note: This Question is unanswered, help us to find answer for this one

99. Which of the following can be done using the ALTER TABLE statement?

Add or delete columns of a table.

Create or destroy indexes.

Change the type of existing columns.

Delete the table.

Answer

Correct Answer: Add or delete columns of a table.
Create or destroy indexes.
Change the type of existing columns.

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

100. Which Of the following statements are correct in the context of the lNSERT statement?

When inserting into a partitioned table. you can control which partitions and subpartitions accept new rows.

You can use REPLACE instead of INSERT to overwrite old rows.

The SET clause indicates the column names explicitly.

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

101. Which of the following should be done for optimizing SELECT statements?

Add an Index to a table to make the SELECT...WHERE query run faster.

Minimize the number of full table scans in your queries. particularly for big tables.

Deal with locking issues. as the speed of your query might be affected by other sessions accessing the tables at the same time.

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

102. Which of the following queries will return 2010-02-02 as the result?

SELECT DATE_ADD('2OIO-01-02’, INTERVAL 31 DAY);

SELECT ADDDATE(‘ 2010-01-O2'. INTERVAL 31 DAY);

SELECT ADDDATE('2010-01-02'. 31):

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

103. Which of the following queries will return ONE as the result?

SELECT CASE 1 WHEN 1>O THEN 'ONE‘ WHEN 2 THEN 'TWO' ELSE 'MORE' END:

SELECT CASE 1 WHEN 1 THEN 'ONE' WHEN 2 THEN 'TWO' ELSE 'MORE' END;

SELECT CASE 1 WHEN 'ONE' WHEN 2 THEN 'TWO' ELSE 'MORE' END;

None of the above.

Answer

Correct Answer: SELECT CASE 1 WHEN 1>O THEN 'ONE‘ WHEN 2 THEN 'TWO' ELSE 'MORE' END:
SELECT CASE 1 WHEN 1 THEN 'ONE' WHEN 2 THEN 'TWO' ELSE 'MORE' END;

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

104. Which of the following Bit functions is used to invert all bits?

&

~

^

<<

Answer

Correct Answer: ~

Note: This Question is unanswered, help us to find answer for this one

105. Which of the following is an integer division operator?

/

-

MOD

DIV

Answer

Correct Answer: DIV

Note: This Question is unanswered, help us to find answer for this one

106. How many bytes are required to store the MEDIUMINT numeric data type?

3 bytes

4 bytes

2 bytes

8 bytes

Answer

Correct Answer: 3 bytes

Note: This Question is unanswered, help us to find answer for this one

107. In MySQL 5.6, if either operand has a noninteger type. the operands are converted to DECIMAL and divided using DECIMAL arithmetic before converting the result to BIGINT.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

108. Which of the following statements are correct regarding the CREATE TABLE statement?

The AUTO_lNCREMENT attribute applies only to integer and floating-point data types.

The DEFAULT attribute applies to the BLOB or TEXT types.

It If neither NULL nor NOT NULL is specified. the column is treated as though NOT NULL had been specified.

CHAR, VARCHAR, TEXT data types can include the CHARACTER SET and COLLATE attributes.

Answer

Correct Answer: CHAR, VARCHAR, TEXT data types can include the CHARACTER SET and COLLATE attributes.

Note: This Question is unanswered, help us to find answer for this one

109. For GRANT statement. which of the following is used to grant all permissions to a user?

ON *.*

*.*

Answer

Correct Answer: ON *.*

Note: This Question is unanswered, help us to find answer for this one

110. Which of the following constructs for flow control are supported by MySQL?

IF

CASE

LOOP

lTERATE

Answer

Correct Answer: IF
CASE
LOOP
lTERATE

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

111. Which of the following are valid MySQL DATETIME and TIMESTAMP value formats?

2012-12-3111:30:45

2012“12“3111+30+45

20070523091528

Tl 071122129015

Answer

Correct Answer: 2012-12-3111:30:45
2012“12“3111+30+45
20070523091528

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

112. Which of the following statements regarding the TRUNCATE TABLE statement are correct in MySOL 5.6?

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

Truncate operations cause an implicit commit, and so cannot be rolled back.

Truncation operations cannot be performed if the session holds an active table lock.

The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

113. Which of the following is NOT a constraint of the INSERT DELAYED statement?

INSERT DELAYED works only with MyISAM, MEMORY. ARCHIVE, and BLACKHOLE tables.

INSERT DELAYED is supported for views.

INSERT DELAYED is not supported for partitioned tables.

None of the above.

Answer

Correct Answer: INSERT DELAYED is not supported for partitioned tables.

Note: This Question is unanswered, help us to find answer for this one

114. Which of the following relational comparison operators can be used to compare row operands as well as scalar operands?

!=

=

>

<

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

115.
What will be the result of the following query?
SELECT "hello hel lo he 0"

hel"

hel‘lo ‘'hello

hel'lo hello

hello l \'hello

Answer

Correct Answer:
hel‘lo ‘'hello

Note: This Question is unanswered, help us to find answer for this one

116. Which Of the following can be used instead Of the ALTER DATABASE statement?

ALTER TABLE

ALTER SCHEMA

ALTER COLLATE

None of the above

Answer

Correct Answer: ALTER SCHEMA

Note: This Question is unanswered, help us to find answer for this one

117. Spatial indexes have which of the following characteristics?

Indexed columns must be NOT NULL.

They are available only for MylSAM tables.

In MySQL 5.6. column pref‌ix lengths are prohibited. The full width of each column is indexed.

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

118. Which of the following is correct regarding the ALTER PROCEDURE statement?

This statement can be used to change the characteristics of a stored procedure.

This statement can be used to change the parameters of a stored procedure.

Thls statement can be used to change the body of a stored procedure.

None of the above.

Answer

Correct Answer: This statement can be used to change the characteristics of a stored procedure.

Note: This Question is unanswered, help us to find answer for this one

119.
What will be the result of the following query?
SELECT 105/(1-1):

NULL

Out-of-Range error

Incorrect statement

None of the above

Answer

Correct Answer:
NULL

Note: This Question is unanswered, help us to find answer for this one

120. Which of the following statements are INVALID?

ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;

ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;

ALTER TABLE t1 ANALYZE PARTITION p1. p2:

ALTER TABLE t1 ANALYZE PARTITION p1;

Answer

Correct Answer: ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;
ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

121. Which of the following are Fixed-Point data types?

DEClMAL

INT

NUMERIC

SMALLINT

TINYlNT

Answer

Correct Answer: DEClMAL
NUMERIC

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

122. Which of the following statements is INCORRECT?

For TEXT and BLOB columns, there is no padding on insert and no bytes are stripped on select.

For indexes on BLOB and TEXT columns. you must specify an index pref‌ix length.

For indexes on CHAR and VARCHAR, a pref‌ix length is required. 0.02

BLOB and TEXT columns cannot have DEFAULT values.

Answer

Correct Answer: For indexes on CHAR and VARCHAR, a pref‌ix length is required. 0.02

Note: This Question is unanswered, help us to find answer for this one

123. Which of the following are valid forms of the SHOW statement?

SHOW AUTHORS

SHOW TABLES

SHOW COLLATION

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

124.
What is the correct order in which the clauses of the DELETE statement are used?
1. DELETE
2. WHERE
3. ORDER BY
4. FROM
5. LIMIT

1.4, 3, 2. 5

1. 4. 2. 5. 3

1, 4, 2, 3, 5

None of the above

Answer

Correct Answer:
1, 4, 2, 3, 5

Note: This Question is unanswered, help us to find answer for this one

125. Which of the following functions will return a set of comma-separated strings that have the corresponding bit in bits set?

MAKE_SET()

OCTET_LENGTH()

OCT ()

LOCATED()

Answer

Correct Answer: MAKE_SET()

Note: This Question is unanswered, help us to find answer for this one

126. Which of the following functions decodes to a base-64 string and returns result?

TO_BASE64()

FROM_BASE6()

UNHEXO

RPADO

Answer

Correct Answer: FROM_BASE6()

Note: This Question is unanswered, help us to find answer for this one

127. Which of the following account management statements was added in MySQL 5.6?

DROP USER

ALTER USER

CREATE USER

GRANT

Answer

Correct Answer: ALTER USER

Note: This Question is unanswered, help us to find answer for this one

128.
In the following example, what will be the return value in case str is not found?
FiELD(str.str1.str2.str3,...)

0

NULL

Not found

1

Answer

Correct Answer:
0

Note: This Question is unanswered, help us to find answer for this one

129. Which of the following queries will return NULL records from the table myTable?

SELECT ' FROM myTable WHERE column1 IS NOT NULL:

SELECT ' FROM myTable WHERE columnis

SELECT ‘ FROM myTable WHERE column1 IS NULL:

None of the above

Answer

Correct Answer: SELECT ‘ FROM myTable WHERE column1 IS NULL:

Note: This Question is unanswered, help us to find answer for this one

130. Which of the following are reserved words in MySQL 5.6?

ACCESSIBLE

INNER

ONE_SHOT

AVAILABLE

Answer

Correct Answer: ACCESSIBLE
INNER
ONE_SHOT

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

131. Which of the following statements is INCORRECT regarding the date data type?

MySQL permits you to store a “zero

Year values in the range 70-99 are converted to 1970-1999.

MySQL never converts a date or time value to a number automatically.

MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column.

Answer

Correct Answer: MySQL never converts a date or time value to a number automatically.

Note: This Question is unanswered, help us to find answer for this one

132. Which of the following is correct regarding the CREATE DATABASE statement?

CREATE SCHEMA is a synonym for CREATE DATABASE.

In MySQL 5.6. CREATE DATABASE is not permitted within a session that has an active LOCK TABLES statement.

An error occurs if the database exists and you did not specify lF NOT EXISTS.

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

133. Which of the following are valid INSERT statements?

INSERT INTO tbl_hname (coll.col2) VALUES[15,col1'2};

INSERT INTO tbl_name (coll.co|2) VALUES(coI2’2,15]:

INSERT INTO tbl_name (a.b,c) VALUESI1.2,3).(4,5.6].(7.8.9}:

INSERT INTO tbl_name (a,b,c) VALUES(1.2,3,4,5.6.28.9):

Answer

Correct Answer: INSERT INTO tbl_hname (coll.col2) VALUES[15,col1'2};
INSERT INTO tbl_name (a.b,c) VALUESI1.2,3).(4,5.6].(7.8.9}:

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

134. Which of the following functions are synonyms of NOW()?

LOCALTIMESTAMP()

El SYSDATE()

CURRENT_TIMESTAMP()

TIME()

Answer

Correct Answer: LOCALTIMESTAMP()
CURRENT_TIMESTAMP()

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

135.
What will be the result of the following two queries?
1. SELECT 2+3
2. SELECT 2+(3'3)-5

Query 1 will return 6, while query 2 will return 17.

Query 1 will return 6. while query 2 will return 6.

Query 1 will return 10. while query 2 will retun 17.

Query 1 will return 17, while query 2 will return 6.

Answer

Correct Answer:
Query 1 will return 6. while query 2 will return 6.

Note: This Question is unanswered, help us to find answer for this one

136.
What is the correct order in which the clauses of the SELECT statement are used?
1. SELECT
2. LIMIT
3. WHERE
4. ORDER BY
5. HAVING
6. FROM

1.6.3.5.2.4

1.6.3.5.4.2

1,6,5.3,4,2

None of the above

Answer

Correct Answer:
1.6.3.5.4.2

Note: This Question is unanswered, help us to find answer for this one

137. Prior to MySOL 5.6.1. what did the isSimpie(g) function always return?

1

NULL

0

None of the above

Answer

Correct Answer: 0

Note: This Question is unanswered, help us to find answer for this one

138. Which of the following methods can speed up the INSERT statements?

While inserting many rows from the same client at the same time, use lNSERT statements with multiple VALUES lists to insert several rows at a time.

When loading a table from a text file, use LOAD DATA INFILE.

For columns with default values. insert values explicitly only when the value to be inserted differs from the default.

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

139. Which of the following optimization tips holds true?

Using persistent connections to the database helps avoid connection overhead.

Performance can be increased by caching queries or answers in your application and then executing many inserts or updates together.

Using AUTO_lNCREMENT columns helps identify each row in a table by a single unique value.

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

140. Which of the following forms of the SELECT statement writes a single row to a f‌ile without any formatting?

SELECT INTo OUTFILE

SELECT INTO DUMPFILE

SELECT INTo var_list

None of the above

Answer

Correct Answer: SELECT INTO DUMPFILE

Note: This Question is unanswered, help us to find answer for this one

141. Which of the following is iNCORRECT regarding the DECIMAL data type?

MySQL 5.6 stores DECIMAL values in binary format.

in a DECIMAL column declaration. the precision and scale cannot be specified.

In DECIMAL(M). the default value of M is 10.

lf the scale is 0, DECIMAL values contain no decimal point or fractional parL

Answer

Correct Answer: in a DECIMAL column declaration. the precision and scale cannot be specified.

Note: This Question is unanswered, help us to find answer for this one

142. Which of the following data types has been deprecated in MySQL 5.6?

YEAR(2)

DATE

YEAR(4)

TIME

Answer

Correct Answer: YEAR(2)

Note: This Question is unanswered, help us to find answer for this one

143.
What will be the result of the following query?
SELECT CONCAT('MY', 'S', 'QL')

MySQL NULL

MyQL MySQL

NULL MySQL

NULL NULL

Answer

Correct Answer:
NULL MySQL

Note: This Question is unanswered, help us to find answer for this one

144. Which of the following SQL statements will change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set?

ALTER TABLE tbi_name MODIFY latin1_text_col TEXT CHARACTER SET utf8;

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;

None of the above.

Answer

Correct Answer: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Note: This Question is unanswered, help us to find answer for this one

145.
Consider the following table named TESTTABLE:
tbIName tbILname
FN LN
FN LN
What will be the result of the following query?
SELECT 1 FROM TESTTABLE WHERE tblName iN ()

1
1

o
0

Neither a nor b

Answer

Correct Answer:
1
1

Note: This Question is unanswered, help us to find answer for this one

146. Which of the following ALTER TABLE partition options have been added in MySQL 5.6?

DROP PARTITION

TRUNCATE PARTITION

ADD PARTITION lF [NOT] EXISTS

None of the above

Answer

Correct Answer: DROP PARTITION

Note: This Question is unanswered, help us to find answer for this one

147. in which of the following types can the CONVERT() function return the results?

BINARY

CHAR

DATE

All Of the above

Answer

Correct Answer: All Of the above

Note: This Question is unanswered, help us to find answer for this one

148. Which of the following functions return the smallest integer value not less than the argument specif‌ied?

LN()

CEIL()

CONV()

CEILING()

Answer

Correct Answer: CEIL()
CEILING()

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

149.
What will be the output of the following query?
SELECT ELT(5,'dev', 'developer', 'develop'. 'dev')

4

5

NULL

0

Answer

Correct Answer:
NULL

Note: This Question is unanswered, help us to find answer for this one

150. Which of the following is NOT a valid comment style in MySQL?

Select 1+2; '1‘ Comment started

Select1)“ this is an in-iine comment ‘/ +2;

Select1 /# this Is an In-iine comment #/ +2;

Select 1+2; -— Comment started

Answer

Correct Answer: Select1 /# this Is an In-iine comment #/ +2;

Note: This Question is unanswered, help us to find answer for this one

151. Which of the following functions were introduced in MySQL 5.6.3?

E] lS_lPV6(expr)

NAME_CONST(name,vaIue)

Ll IS_USED_LOCK(str)

Il lS_lPV4_COMPATO() 1-4

Answer

Correct Answer: E] lS_lPV6(expr)
Il lS_lPV4_COMPATO() 1-4

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

152. Which of the following are properties of MySQL Cursors?

They are asensitive.

They are read-only.

They are nonscroiiabie.

All of the above.

Answer

Correct Answer: All of the above.

Note: This Question is unanswered, help us to find answer for this one

153. Which of the following escape sequences represents the backslash ("\") character?

\\

\o

\'

\””

Answer

Correct Answer: \\

Note: This Question is unanswered, help us to find answer for this one

154. What is the default value of locale for the FORMAT(X,D[locale])D function?

en_US

de_DE

en

en_GB

Answer

Correct Answer: en_US

Note: This Question is unanswered, help us to find answer for this one

155. What will be the output if you try to perform an arithmetic operation on a column containing NULL values?

0

NULL

An error will be generated.

None of the above.

Answer

Correct Answer: NULL

Note: This Question is unanswered, help us to find answer for this one

156. In MySOL 5.6, you can write triggers containing direct references to tables by name.

True

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

157.
What will be the result of the following query?
SELECT 5 <=> 5, NULL <=> NULL, 5 <=> NULL

1 1 0

01 0

11 1

0 0 0

Answer

Correct Answer:
1 1 0

Note: This Question is unanswered, help us to find answer for this one

158.
What will be the output of the following query?
SELECT DATE_FORMAT(‘2006-06-00'. '%d')

0

00

06

None of the above

Answer

Correct Answer:
0

Note: This Question is unanswered, help us to find answer for this one

159. Which of the following numeric data types has a storage value of 8 bytes?

BIGINT

INT

SMALLINT

DECIMAL

Answer

Correct Answer: BIGINT

Note: This Question is unanswered, help us to find answer for this one

160.
Which of the following are correct about HEAP tables? Check all that apply.

HEAP tables are in-memory.

Text fields are allowed within HEAP tables.

HEAP tables are generally used for high-speed temporary storage.

HEAP tables support AUTO_INCREMENT.

Answer

Correct Answer: HEAP tables are in-memory.

Note: This Question is unanswered, help us to find answer for this one

161.
How many Triggers are possible in MySQL?

4

5

6

7

Answer

Correct Answer: 6

Note: This Question is unanswered, help us to find answer for this one

162.
What are the properties of transactions?

Atomicity, Consistency

Durability, Isolation

Atomicity, Consistency, Isolation

Atomicity, Consistency, Durability, Isolation

Answer

Correct Answer: Atomicity, Consistency, Durability, Isolation

Note: This Question is unanswered, help us to find answer for this one

163.
What is the full form of ACL?

Access Control List

Access Control Library

Access Center Library

Access Center List

Answer

Correct Answer: Access Control List

Note: This Question is unanswered, help us to find answer for this one

164.
Which of the following command is used to create a stored routine?

CREATE ROUTINE

CREATE PROCEDURE

CREATE FUNCTION

None of the mentioned

Answer

Correct Answer: CREATE PROCEDURE

Note: This Question is unanswered, help us to find answer for this one

165.
What is the correct command to check if MySql service is running or not in RedHat?

service mysqld status

mysql status

mysqld status

None of the mentioned

Answer

Correct Answer: service mysqld status

Note: This Question is unanswered, help us to find answer for this one

166.
What is the correct syntax to create a new user in MySQL?

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

CREATE USER 'newuser'@'localhost' SET PASSWORD 'password';

CREATE USER 'newuser'@'localhost' PASSWORD ‘'password';

CREATE USER 'newuser'@'localhost' ASSOCIATED BY 'password';

Answer

Correct Answer: CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Note: This Question is unanswered, help us to find answer for this one

167.
What happens to privileges for a table when you delete the table in MySQL?

The privileges to the table are automatically revoked

The privileges to the table are not automatically revoked

As the table is deleted the privileges do not exist

As the table has been deleted, you cannot explicitly issue a REVOKE statement to revoke privileges for that table

Answer

Correct Answer: As the table has been deleted, you cannot explicitly issue a REVOKE statement to revoke privileges for that table

Note: This Question is unanswered, help us to find answer for this one

168.
What is the correct syntax to view the global event scheduler’s status in MySQL process list?

SHOW PROCESSLIST\G;

DISPLAY PROCESSLISTG;

SHOW PROCESSLISTG();

DISPLAY PROCESSLISTG();

Answer

Correct Answer: SHOW PROCESSLIST\G;

Note: This Question is unanswered, help us to find answer for this one

169.
What is the syntax to backup a database from command line?

mysql [options] database_name > database_name.sql

mysqldump [options] database_name > database_name.sql

mysqlbackup [options] database_name > database_name.sql

mysqldump [options] database_name >> database_name.sql

Answer

Correct Answer: mysqldump [options] database_name > database_name.sql

Note: This Question is unanswered, help us to find answer for this one

170.
What is the correct syntax to turn on the global event scheduler?

SET GLOBAL event_scheduler = ON;

SET GLOBAL event_scheduler = True;

SET GLOBAL EVENT SCHEDULER = ON;

SET GLOBAL EVENT SCHEDULER = True;

Answer

Correct Answer: SET GLOBAL event_scheduler = ON;

Note: This Question is unanswered, help us to find answer for this one

171.
What is the correct syntax to get the MySQL Version?

SELECT VERSION;

SELECT VERSION();

SELECT VERSION FROM mysql;

None of the mentioned

Answer

Correct Answer: SELECT VERSION();

Note: This Question is unanswered, help us to find answer for this one

172.
What is the correct syntax to create a view?

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

CREATE VIEW view_name SELECT column_name(s) FROM table_name WHERE condition

CREATE VIEW view_name AS USING column_name(s) FROM table_name WHERE condition

None of the above

Answer

Correct Answer: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

Note: This Question is unanswered, help us to find answer for this one

173.
What is the default port for MySQL Server?

5432

3306

27017

27018

Answer

Correct Answer: 3306

Note: This Question is unanswered, help us to find answer for this one

174.
Which of the following are valid Scalar Functions? Check all that apply.

UPPER()

MID()

LEN()

ROUND()

Answer

Correct Answer: UPPER()

Note: This Question is unanswered, help us to find answer for this one

175.
How do you control the max size of a HEAP table?

MySQL config variable called max_heap_table_size.

MySQL config variable called max_heap_size.

MySQL config variable called max_heap_table.

None of the above

Answer

Correct Answer: MySQL config variable called max_heap_table_size.

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: UPDATE T1 LEFT JOIN T2 ON T2.ID = T1.ID SET T1.COL1 = NEWVALUE WHERE T2.ID IS NULL

Note: This Question is unanswered, help us to find answer for this one

177.
Which of the statements are true for user-defined variables?

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.

User variable names are case sensitive.

Answer

Correct Answer: User variables are written as $var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “@”.

Note: This Question is unanswered, help us to find answer for this one

178.
What are the valid types of table present in MySQL? Check all that apply.

MyISAM

Heap

Merge

INNO DB

ISAM

Answer

Correct Answer: MyISAM

Note: This Question is unanswered, help us to find answer for this one

179.
Which of the following are valid MySQL functions? Check all that apply.

FORMAT()

NOW()

HOUR()

DATEDIFFERENCE()

Answer

Correct Answer: FORMAT()

Note: This Question is unanswered, help us to find answer for this one

180.
Which of the following will truncate all tables in a MySQL database?

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

Answer

Correct Answer: SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES

Note: This Question is unanswered, help us to find answer for this one

181.
What are the valid modes of parameters in stored routines? Check all that apply.

IN

OUT

INOUT

OUTIN

Answer

Correct Answer: IN

Note: This Question is unanswered, help us to find answer for this one

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

SELECT UNIQUE

SELECT DISTINCT

SELECT DIFFERENT

SELECT DIFF

Answer

Correct Answer: SELECT DISTINCT

Note: This Question is unanswered, help us to find answer for this one

183.
Which of the following is NOT a valid Aggregation Function?

AVG()

COUNT()

LAST()

MAX()

SUM()

None of the mentioned

Answer

Correct Answer: LAST()

Note: This Question is unanswered, help us to find answer for this one

184.
How do you return fifty events starting from 40th? Assume table name is events and you need to select only the column event_title.

SELECT event_title FROM events LIMIT 39, 50;

SELECT event_title FROM events LIMIT 40, 50;

SELECT event_title FROM events LIMIT [40, 50];

SELECT event_title FROM events LIMIT [50, 40];

Answer

Correct Answer: SELECT event_title FROM events LIMIT 39, 50;

Note: This Question is unanswered, help us to find answer for this one

185.
Which of the following statement is valid for % and _ in the LIKE clause in a mysql query?

% corresponds to 1 or more characters; _ is exactly one character

% corresponds to 0 or more characters; _ is exactly one character

% corresponds to 1 or more characters; _ is 0 or more characters

% is exactly one character; _ is corresponds to 0 or more characters

Answer

Correct Answer: % corresponds to 0 or more characters; _ is exactly one character

Note: This Question is unanswered, help us to find answer for this one

186.
Which of the following is correct about CHAR_LENGTH and LENGTH?

CHAR_LENGTH is character count whereas the LENGTH is byte count.

CHAR_LENGTH is byte count whereas the LENGTH is character count.

CHAR_LENGTH and LENGTH both are character count.

CHAR_LENGTH and LENGTH both are byte count.

Answer

Correct Answer: CHAR_LENGTH is character count whereas the LENGTH is byte count.

Note: This Question is unanswered, help us to find answer for this one

187.
What is the correct syntax to switch to a new database in MySQL?

switch database_name;

connect database_name;

use database_name;

start database_name;

Answer

Correct Answer: use database_name;

Note: This Question is unanswered, help us to find answer for this one

188.
How do you get the field names and associated type of a MySQL table?

show schema table_name;

describe table_name;

schema table_name;

show details table_name;

Answer

Correct Answer: describe table_name;

Note: This Question is unanswered, help us to find answer for this one

189.
What is the syntax to list all databases in mysql?

list databases;

show databases;

all databases;

databases;

Answer

Correct Answer: show databases;

Note: This Question is unanswered, help us to find answer for this one

190.
Which command converts Unix Timestamp to Mysql Timestamp and vice versa?

FROM_UNIXTIME; FROM_MYSQLTIME

FROM_UNIXTIMESTAMP; UNIX_TIME

FROM_UNIXTIME; UNIX_TIMESTAMP

UNIX_TIMESTAMP; FROM_UNIXTIME

Answer

Correct Answer: FROM_UNIXTIME; UNIX_TIMESTAMP

Note: This Question is unanswered, help us to find answer for this one

191.
What is the correct syntax to grant all privileges on all databases to a user?

GRANT ALL_PRIVILEGES ON * . * TO 'newuser'@'localhost';

GRANT ALL PRIVILEGES ON * TO 'newuser'@'localhost';

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

GRANT ALL_PRIVILEGES ON * TO 'newuser'@'localhost';

Answer

Correct Answer: GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Note: This Question is unanswered, help us to find answer for this one

192.
The correct syntax to call a stored routine country_hos with a parameter of type VARCHAR is

CALL country_hos('Europe');

USE country_hos('Europe');

SELECT country_hos('Europe');

None of the mentioned

Answer

Correct Answer: CALL country_hos('Europe');

Note: This Question is unanswered, help us to find answer for this one

193.
What is the correct syntax to restore a database from command line?

mysqlrestore -u username -p database_name<database_name.sql

mysql -u username -p database_namedatabase_name.sql

mysql -u username -p database_name<<database_name.sql

mysql -u username -p database_name<database_name.sql

Answer

Correct Answer: mysql -u username -p database_name<database_name.sql

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: mysql -u<user> -p <db_backup.dump

Note: This Question is unanswered, help us to find answer for this one

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

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*';

Answer

Correct Answer: SELECT * from PERSONS where LASTNAME LIKE 'JOH%';

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: 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);

Note: This Question is unanswered, help us to find answer for this one

197.

Which of the following will check whether a MySQL database exists or not?

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.

Answer

Correct Answer: SHOW DATABASES LIKE 'DBname';

Note: This Question is unanswered, help us to find answer for this one

198.
Select examples with a valid comment syntax:

SELECT 1+1; # Comment

SELECT 1+1; --Comment

SELECT 1+1 /* Comment */;

SELECT 1+1 ' Comment #; -WRONG ANS

Answer

Correct Answer: SELECT 1+1 /* Comment */;

Note: This Question is unanswered, help us to find answer for this one

199.
InnoDB prevents which of the following operations when innodb_force_recovery is greater than 0?

INSERT

UPDATE

DELETE

SELECT

Answer

Correct Answer: INSERT

Note: This Question is unanswered, help us to find answer for this one

200.
Which of the following are valid encryption functions?

MD5

AES_ENCRYPT

SHA512

SHA2

Answer

Correct Answer: MD5

Note: This Question is unanswered, help us to find answer for this one

201.
With which of the following you can not associate a Trigger?

Permanent tabe

Temporary Table

View

Answer

Correct Answer: Permanent tabe

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: Select count(*) from EMP where empJoinDate between '2012-10-01' and 2012-12-30' group by EXTRACT(YEAR_MONTH from empJoinDate );

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: All of the Above

Note: This Question is unanswered, help us to find answer for this one

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

&

&&

##

|

Answer

Correct Answer: &&

Note: This Question is unanswered, help us to find answer for this one

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

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;

Answer

Correct Answer: 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;

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: None of the Above

Note: This Question is unanswered, help us to find answer for this one

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

To debug syntax error in queries.

To analyze the execution of a query.

To automatically optimize a query.

To get the documentation entry for a specific command.

Answer

Correct Answer: To analyze the execution of a query.

Note: This Question is unanswered, help us to find answer for this one

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

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

Add 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

Answer

Correct Answer: 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

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: SELECT ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) AS T ) AS T;

Note: This Question is unanswered, help us to find answer for this one

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

GREATEST(a,b)

MAX(a,b)

MAXIMUM(a,b)

HIGHER(a,b)

Answer

Correct Answer: GREATEST(a,b)

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: select item, brand, max(price) from items group by item

Note: This Question is unanswered, help us to find answer for this one

212.
Examine the code given below: SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000 Which of the following statements is correct with regard to this code?

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

Answer

Correct Answer: It returns employees who have 50% commission rate or salary greater than $23,000

Note: This Question is unanswered, help us to find answer for this one

213.
Which approach can be used to find all occurrences of text ‘’owner’’ in all fields of all tables in a MySQL, database?

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 appreciate command line tools

Discover tables using the “SHOW TABLES” query: than for each table discover its columns using “SHOW COLUMNS FROM ” query: than for each column run the query: SELECT *FROM
WHERE LIKE “%owner%”

Call the searchALLDB ($search) function

Answer

Correct Answer: 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 appreciate command line tools

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: Execute the following command: mysqlcheck -o --all-databases

Note: This Question is unanswered, help us to find answer for this one

215.
Which of the following represents a valid use of COUNT() in a WHERE clause?

select gid from `gd` group by gid having count(*) > 10 order by lastupdated desc

select gid from `gd` group by gid count(*) > 10 order by lastupdated desc

select gid from `gd` group by gid count() > 10 order by lastupdated desc

COUNT() cannot be used in a WHERE clause.

Answer

Correct Answer: select gid from `gd` group by gid having count(*) > 10 order by lastupdated desc

Note: This Question is unanswered, help us to find answer for this one

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

First execute SET GLOBAL log_output = ‘TABLE’: Than execute SET GLOBAL general_log = ‘ON’: The last queries executed are saved in the table mysql.general_log

Edit the MySQL config file (mysql . com) and add the following line Log = /var/log/mysql/mysql.log

Ececute view . mysql_history

Restart MySQL using the following line tail –f /var/log/mysql . log

Answer

Correct Answer: First execute SET GLOBAL log_output = ‘TABLE’: Than execute SET GLOBAL general_log = ‘ON’: The last queries executed are saved in the table mysql.general_log

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: mysqldump db_name --ignore-table db_name.sessions db_name.log > backup.sql

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: Alias names case sensitivity depends on lower_case_table_names system setting

Note: This Question is unanswered, help us to find answer for this one

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

BLOB

BIGINT

INT

Both BLOB and BIGINT

Answer

Correct Answer: BLOB

Note: This Question is unanswered, help us to find answer for this one

220.
Which function is used to return a JSON representation of a MySQL query?

json_encode()

mysql_json_encode()

mysql_json()

json()

Answer

Correct Answer: json_encode()

Note: This Question is unanswered, help us to find answer for this one

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

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.

Answer

Correct Answer: Yes, use SELECT identifier, GROUP_CONCAT(string SEPARATOR ' ') FROM table_name GROUP BY identifier.

Note: This Question is unanswered, help us to find answer for this one

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

mysqldump --single-transaction db_name

mysqldump --force db_name

mysqldump --quick db_name

mysqldump --no-tablespaces db_name

Answer

Correct Answer: mysqldump --single-transaction db_name

Note: This Question is unanswered, help us to find answer for this one

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

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')" ;

Answer

Correct Answer: SELECT count(*) FROM information_schema.columns WHERE data_type = 'enum' ;

Note: This Question is unanswered, help us to find answer for this one

224.
Which of the following statements is true?

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.

Answer

Correct Answer: Replication can be a part of a load balancing strategy.

Note: This Question is unanswered, help us to find answer for this one

225.
What value return "type" column in EXPLAIN plans for a full table scan of select query?

FULL TABLE SCAN

range

ALL

const

Answer

Correct Answer: FULL TABLE SCAN

Note: This Question is unanswered, help us to find answer for this one

226.
Why is the following GRANT statement considered a bad security practice: GRANT ALL PRIVILEGES ON db_test.* TO 'john'@'%' IDENTIFIED BY '0!Nh6l7tEjohn'

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

Answer

Correct Answer: Because it is granting all privileges to a user that is not the root

Note: This Question is unanswered, help us to find answer for this one

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

MySQL

SQLite

Both

None

Answer

Correct Answer: SQLite

Note: This Question is unanswered, help us to find answer for this one

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

1

'id'

NULL

These queries are invalid.

Answer

Correct Answer: NULL

Note: This Question is unanswered, help us to find answer for this one

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

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.

Answer

Correct Answer: None of the above.

Note: This Question is unanswered, help us to find answer for this one

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

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.

Answer

Correct Answer: 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

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

==

=

!=

REGEXP

Answer

Correct Answer: ==

Note: This Question is unanswered, help us to find answer for this one

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

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.

Answer

Correct Answer: None of these.

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: 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);

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: Select * from Products order by ProductGroup,CurrentStock DESC

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: SELECT * FROM myTable WHERE column1 IS NULL

Note: This Question is unanswered, help us to find answer for this one

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

Nothing will happen

MySQL will generate an error

MySQL will convert all varchar datatypes into char

MySQL will convert all char datatypes into varchar

Answer

Correct Answer: MySQL will convert all char datatypes into varchar

Note: This Question is unanswered, help us to find answer for this one

238.
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"?

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'

Answer

Correct Answer: SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

Note: This Question is unanswered, help us to find answer for this one

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

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'

Answer

Correct Answer: GRANT ALL ON testdb.* TO peter IDENTIFIED by 'Software'

Note: This Question is unanswered, help us to find answer for this one

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

TINYINT

MEDIUMINT

SMALLINT

INT

BIGINT

Answer

Correct Answer: MEDIUMINT

Note: This Question is unanswered, help us to find answer for this one

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

&

&&

AND

!

NOT

Answer

Correct Answer: &

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: SELECT * FROM Persons ORDER BY FirstName DESC

Note: This Question is unanswered, help us to find answer for this one

243.
Examine the query:-
select (2/2/4) from tab1;
where tab1 is a table with one row. This would give a result of:

4

2

1

.5

.25

8

24

Answer

Correct Answer: .25

Note: This Question is unanswered, help us to find answer for this one

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

DD-MM-YYYY

YYYY-DD-MM

YYYY-MM-DD

MM-DD-YY

MMDDYYYY

Answer

Correct Answer: YYYY-MM-DD

Note: This Question is unanswered, help us to find answer for this one

245.
What is true regarding the TIMESTAMP data type?

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

Answer

Correct Answer: 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

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

ndb_config

cluster_config

ndb --config

configNdb

None of the above

Answer

Correct Answer: ndb_config

Note: This Question is unanswered, help us to find answer for this one

247.
What is true regarding the SET data type?

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

Answer

Correct Answer: A SET can have zero or more values
A SET can have a maximum of 64 different members

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

248.
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 ?
Which query will display data from the Pers table relating to Analysts, Clerks and Salesmen who joined
between 1/1/2005 and 1/2/2005 ?

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=salesman

select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and (job=Analyst or clerk or salesman)

None of the above

Answer

Correct Answer: select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and (job=Analyst or clerk or salesman)

Note: This Question is unanswered, help us to find answer for this one

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

It will display the current date

It will display the error message as now does not exist.

It will display a syntax error near '()'

Answer

Correct Answer: It will display the error message as now does not exist.

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: InnoDB provides a transaction safe environment

Note: This Question is unanswered, help us to find answer for this one

251.
Consider the following query:
create table foo (id int);
Which of the following statements is true?

The id column will be the default primary key

This query is invalid, a table must have a primary key

This query creates a table with one column

The id column is auto incremented

All statements are false

Answer

Correct Answer: This query creates a table with one column

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: This depends on lower_case_table_names system variable

Note: This Question is unanswered, help us to find answer for this one

253.
Is the following query valid?
create table foo (id int primary key auto_increment, name varchar);

No

Yes

Answer

Correct Answer: No

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: select authorname from authors where authorid in (select authorid from books where popularityrating<5)

Note: This Question is unanswered, help us to find answer for this one

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

ENUMERATE

EXPLAIN

KILL

LOAD DATA

SET

Answer

Correct Answer: ENUMERATE

Note: This Question is unanswered, help us to find answer for this one

256.
Which of the following is a valid declaration?

CHAR(0)

CHAR(1024)

CHAR(256)

VARCHAR(-1024)

All are invalid

All are valid

Answer

Correct Answer: CHAR(0)
CHAR(1024)
CHAR(256)

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

InnoDB

MYISAM

BLACKHOLE

STACK

Answer

Correct Answer: STACK

Note: This Question is unanswered, help us to find answer for this one

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

Yes

No

Answer

Correct Answer: No

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: ALTER TABLE table1 ADD column2a INT AFTER column2

Note: This Question is unanswered, help us to find answer for this one

260.
To quote a string within a string, which of the following can you use?

"This is the "quoted" message"

"This is the ""quoted"" message"

'This is the "quoted" message'

"This is the \"quoted\" message"

Answer

Correct Answer: "This is the ""quoted"" message"
'This is the "quoted" message'
"This is the \"quoted\" message"

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

SHOW TABLES

DESCRIBE TABLES

SHOW ALL TABLES

LIST TABLES

Answer

Correct Answer: SHOW TABLES

Note: This Question is unanswered, help us to find answer for this one

262.
Consider the query:
SELECT name
FROM Student
WHERE name LIKE '_a%';
Which names will be displayed?

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

Answer

Correct Answer: Names containing "a" as the second letter

Note: This Question is unanswered, help us to find answer for this one

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

No

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

264.
What is wrong with the following query:
select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)

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

Answer

Correct Answer: The sub query can return more than one row, so, '=' should be replaced with 'in'

Note: This Question is unanswered, help us to find answer for this one

265.
The REPLACE statement is:

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

Answer

Correct Answer: 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

Note: This Question is unanswered, help us to find answer for this one

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

BINARY

NOT

<<

%

Answer

Correct Answer: NOT

Note: This Question is unanswered, help us to find answer for this one

267.
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 mentioned below?
SELECT count(DISTINCT col1) FROM tab1

6

5

4

3

Answer

Correct Answer: 3

Note: This Question is unanswered, help us to find answer for this one

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

UPDATE

CREATE ROUTINE

SELECT

CREATE FUNCTION

No specific privilege

Answer

Correct Answer: CREATE ROUTINE

Note: This Question is unanswered, help us to find answer for this one

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

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

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

GEOMETRY

CIRCLE

SQUARE

POINT

POLYGON

Answer

Correct Answer: GEOMETRY
POINT
POLYGON

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

271.
What is NDB?

An in-memory storage engine offering high-availability and data-persistence features

A filesystem

An SQL superset

MySQL scripting language

None of the above

Answer

Correct Answer: An in-memory storage engine offering high-availability and data-persistence features

Note: This Question is unanswered, help us to find answer for this one

272.
You are maintaining data for a Products table, and want to see the products which have a current stock
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.

(a) is correct

(b) is correct

(a) and (b) both are correct

(a) and (b) both are incorrect

Answer

Correct Answer: (a) and (b) both are correct

Note: This Question is unanswered, help us to find answer for this one

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

Between..and..

Like

In

Is null

Not in

All of the above are SQL operators

Answer

Correct Answer: All of the above are SQL operators

Note: This Question is unanswered, help us to find answer for this one

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

0000

1900

2000

Ambiguous, cannot be determined

Answer

Correct Answer: 2000

Note: This Question is unanswered, help us to find answer for this one

275.
Which of the following are not String column types?

BLOB

ENUM

SET

TEXT

LONGCHAR

Answer

Correct Answer: LONGCHAR

Note: This Question is unanswered, help us to find answer for this one

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

No

Yes

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

277.
What kind of joins does MySQL support?

dual join

right join

natural join

middle join

STRAIGHT_JOIN

Answer

Correct Answer: right join
natural join
STRAIGHT_JOIN

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: The two statements produce identical results

Note: This Question is unanswered, help us to find answer for this one

279.
Which of the following queries is valid?

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

Answer

Correct Answer: Select name from students group by subject, name;

Note: This Question is unanswered, help us to find answer for this one

280.
The Flush statement cannot be used for:

Closing any open tables in the table cache

Closing open connections

Flushing the log file

Flushing the host cache

Answer

Correct Answer: Closing open connections

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: SELECT MAX(GPA) FROM STUDENT_GRADES GROUP BY SEMESTER_END

Note: This Question is unanswered, help us to find answer for this one

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

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;

Answer

Correct Answer: SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '2002-11-11' ORDER BY purchase_date DESC;

Note: This Question is unanswered, help us to find answer for this one

283.
What is the correct order of clauses in the select statement?
1 select
2 order by
3 where
4 having
5 group by

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

Answer

Correct Answer: 1,3,5,4,2

Note: This Question is unanswered, help us to find answer for this one

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

No limit

OS specific

65,534

2'147'483'648

128

Answer

Correct Answer: 65,534

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: The id column cannot be auto incremented because it has not been defined as a primary key

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: The function always returns the same value for the same input

Note: This Question is unanswered, help us to find answer for this one

287.
Which operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 - 8) from emp

+

-

/

*

Answer

Correct Answer: *

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: None of the above

Note: This Question is unanswered, help us to find answer for this one

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

1

2

3

4

5

No error in any line

Answer

Correct Answer: No error in any line

Note: This Question is unanswered, help us to find answer for this one

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

CHANGE TABLE

MODIFY TABLE

ALTER TABLE

UPDATE TABLE

Answer

Correct Answer: ALTER TABLE

Note: This Question is unanswered, help us to find answer for this one

291.
Which of the following is not a MySQL Data type?

INT

DOUBLE PRECISION

INTEGER

BIT

None of the above

Answer

Correct Answer: None of the above

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: DELETE FROM table1 WHERE column1=2 LIMIT 3

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: No values will be displayed because the statement will return an error

Note: This Question is unanswered, help us to find answer for this one

294.
In which sequence are queries and sub-queries executed by the SQL Engine?

primary query -> sub query -> sub sub query and so on

primary query -> sub query -> sub sub query and so on

The whole query is interpreted at one time

There is no fixed sequence of interpretation, the query parser takes a decision on the fly

Answer

Correct Answer: primary query -> sub query -> sub sub query and so on
primary query -> sub query -> sub sub query and so on

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

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 del

Answer

Correct Answer: Two tables are created
If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are del

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

296.
Consider the following table structure of students:
rollno number(4)
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?

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

Answer

Correct Answer: Select course from students group by course having count(*) > 5;

Note: This Question is unanswered, help us to find answer for this one

297.
Which of the following statements are true?

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

Answer

Correct Answer: Names of databases, tables and columns can be up to 64 characters in length
Alias names can be up to 255 characters in length

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

"EXPERTRATING" LIKE "EXP%"

"EXPERTRATING" LIKE "Exp%"

BINARY "EXPERTRATING" LIKE "EXP%"

BINARY "EXPERTRATING" LIKE "Exp%"

All will result in 1 (true)

Answer

Correct Answer: BINARY "EXPERTRATING" LIKE "Exp%"

Note: This Question is unanswered, help us to find answer for this one

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

BIGINT

LARGEINT

SMALLINT

DOUBLE

DECIMAL

Answer

Correct Answer: LARGEINT

Note: This Question is unanswered, help us to find answer for this one

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

Table Name

Database name

LIMIT clause

WHERE clause

Answer

Correct Answer: Table Name

Note: This Question is unanswered, help us to find answer for this one

301.
Which of the following are aggregate functions in SQL?

Avg

Select

Order By

Sum

Union

Group by

Having

Answer

Correct Answer: Avg
Sum

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

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

BINARY

NOT

<<

%

Answer

Correct Answer: BINARY

Note: This Question is unanswered, help us to find answer for this one

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

+

-

*

\

%

All are valid

Answer

Correct Answer: \

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: Indexes can have NULL values
BLOB and TEXT columns can be indexed

Note: This question has more than 1 correct answers

Note: This Question is unanswered, help us to find answer for this one

305.
If you use NULL with Logical operators, NULL is considered as:

NULL

False

True

An error message is generated

Cannot be determined

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

306.
Which of the following is the best way to store variable length text data in a MYSQL database?

Varchar(255);

Char(255);

Varchar

Char

Answer

Correct Answer: Varchar(255);

Note: This Question is unanswered, help us to find answer for this one

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

Select * FROM periods WHERE NOT (range_start > @check_period_end OR range_end < @check_period_start)

SELECT * FROM periods WHERE range_start <= @check_period_end AND range_end >= @check_period_start

Both Option A & B

None of the above

Answer

Correct Answer: Both Option A & B

Note: This Question is unanswered, help us to find answer for this one

308.
Which “MySQL” client utility option is useful for MySQL query execution on the command line?

--line-numbers

--named-commands, -G

--pager [=command]

--execute=statement, -e statement

Answer

Correct Answer: --execute=statement, -e statement

Note: This Question is unanswered, help us to find answer for this one

309. Which of the following is a valid declaration?

1) CHAR(0)
2) CHAR(1024)
3) CHAR(256)
4) VARCHAR(-1024)

1

2

3

4

All are invalid

All are valid<br>

Answer

Correct Answer: 1

Note: This Question is unanswered, help us to find answer for this one

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

BLOB

ENUM

SET

TEXT

LONGCHAR

Answer

Correct Answer: LONGCHAR

Note: This Question is unanswered, help us to find answer for this one

311. Which of the following statements are true?

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<br><br>

Answer

Correct Answer: BLOB and TEXT columns cannot have DEFAULT values

Note: This Question is unanswered, help us to find answer for this one

312. Which of the following are valid MySQL Data type?

INT

DOUBLE PRECISION

INTEGER

BIT

All of the above

Answer

Correct Answer: All of the above

Note: This Question is unanswered, help us to find answer for this one

313. 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;

Yes

No

Answer

Correct Answer: Yes

Note: This Question is unanswered, help us to find answer for this one

314. Which SQL function returns the current date?

CURDATE()

CURTIME()

NOW()

DATE()

Answer

Correct Answer: CURDATE()

Note: This Question is unanswered, help us to find answer for this one

315. What will be returned? SELECT column_name FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

All rows from table1, without the matching rows in the table2.

All rows from table2, with the matching rows in the table1.

All rows from table1, with the matching rows in the table2.

Answer

Correct Answer: All rows from table1, with the matching rows in the table2.

Note: This Question is unanswered, help us to find answer for this one

316. How many triggers can be created for each tables ?

6

3

4

2

5

Answer

Correct Answer: 6

Note: This Question is unanswered, help us to find answer for this one

317. Which of the following is not a MySQL Operator?a. Between..and..,b. Like,c. IN,d. Is NULL

== NULL

IS NULL

= NULL

Answer

Correct Answer: IS NULL

Note: This Question is unanswered, help us to find answer for this one

318. How do you get the number of rows affected by query?

SELECT COUNT (user_id) FROM * would only return the number of user_id’s.

SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

Answer

Correct Answer: SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

Note: This Question is unanswered, help us to find answer for this one

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

Trigger

Index

View

Cursor

Answer

Correct Answer: Cursor

Note: This Question is unanswered, help us to find answer for this one

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

MOVE ... INTO OUTFILE

COPY ... INTO OUTFILE

EXPORT ... INTO OUTFILE

SELECT ... INTO OUTFILE

Answer

Correct Answer: SELECT ... INTO OUTFILE

Note: This Question is unanswered, help us to find answer for this one

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

SELECT -order FROM -slides ORDER BY -order DESC

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

SELECT order FROM `slides` ORDER BY order DESC

All of these return identical results

Answer

Correct Answer: SELECT `order` FROM `slides` ORDER BY `order` DESC

Note: This Question is unanswered, help us to find answer for this one

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

SELECT book_title FROM books LIMIT 25, 125

SELECT book_title FROM books LIMIT 24, 100

SELECT book_title FROM books LIMIT 25 UP TO 100

SELECT book_title FROM books LIMIT 24 TO 124

Answer

Correct Answer: SELECT book_title FROM books LIMIT 24, 100

Note: This Question is unanswered, help us to find answer for this one

323. Which foreign keys do MyISAM tables support?

Foreign keys on the primary key only

None, MyISAM tables do not support foreign keys.

Foreign keys on columns other than the primary key.

Foreign keys on indexed columns.

Answer

Correct Answer: None, MyISAM tables do not support foreign keys.

Note: This Question is unanswered, help us to find answer for this one

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

All of these.

InnoDB is innovative where MyISAM is not.

InnoDB uses row locking where MyISAM uses table locking.

None of these.

InnoDB uses table locking where MyISAM uses row locking.

Answer

Correct Answer: InnoDB uses row locking where MyISAM uses table locking.

Note: This Question is unanswered, help us to find answer for this one

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

File_priv

Grant_priv

Process_priv

Reload_priv

Answer

Correct Answer: File_priv

Note: This Question is unanswered, help us to find answer for this one

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

COMBINE()

JOIN()

IMPLODE()

GROUP_CONCAT()

Answer

Correct Answer: GROUP_CONCAT()

Note: This Question is unanswered, help us to find answer for this one

327. ______ - Nothing that’s being read is actively being changed by another transaction.

ACID Transaction Rollback

ISOLATION

Answer

Correct Answer: ISOLATION

Note: This Question is unanswered, help us to find answer for this one

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

CALL FUNCTION test ('asdfg', 6);

SELECT test('asdfg', 6);

CALL test('asdfg', 6);

SELECT test USING 'asdfg', 6;

Answer

Correct Answer: CALL test('asdfg', 6);

Note: This Question is unanswered, help us to find answer for this one

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

EXCLUDING JOIN t2 ON t1.t2_id = t2.id

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

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

JOIN t2 EXCLUDING t1.t2_id = t2.id

RIGHT JOIN t2 ON t1.t2_id = t2.id

Answer

Correct Answer: LEFT JOIN t2 ON t1.t2_id = t2.id WHERE t2.id IS NULL

Note: This Question is unanswered, help us to find answer for this one

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

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

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.

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.

Answer

Correct Answer: Edit the my.ini file and change the setting max_allowed_packet. Then restart the service.

Note: This Question is unanswered, help us to find answer for this one

331. Assume, you have a table inventory(id int, description text, price int). How can you change the type of price from int to decimal(10,2)?

alter table inventory modify price decimal(10,2)

Both are correct.

alter table inventory change column price decimal(10,2)

This is not possible.

Answer

Correct Answer: alter table inventory modify price decimal(10,2)

Note: This Question is unanswered, help us to find answer for this one

332. Which of the following are true concerning indexes?

None are correct

Indexes can only be done on columns containing numerical data

Indexes can only be done on primary key columns

Indexes will slow down selects, but speed up inserts

Indexes will slow down inserts, but speed up selects

Answer

Correct Answer: Indexes will slow down inserts, but speed up selects

Note: This Question is unanswered, help us to find answer for this one

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

CIRCLE

GEOMETRY CIRCLE POINT POLYGON

Answer

Correct Answer: CIRCLE

Note: This Question is unanswered, help us to find answer for this one

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

count(*)

count(all)

count()

count(distinct)

Answer

Correct Answer: count(distinct)

Note: This Question is unanswered, help us to find answer for this one

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

DELETE USER user;

DROP USER user;

None of these

REVOKE USER user;

Answer

Correct Answer: DROP USER user;

Note: This Question is unanswered, help us to find answer for this one

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

Cardinality

Seq-in-index

Collation

Sub-part

Answer

Correct Answer: Cardinality

Note: This Question is unanswered, help us to find answer for this one

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

None of these

It stops incrementing, producing a duplicate key error.

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

Further rows will be inserted with the same value.

Answer

Correct Answer: It stops incrementing, producing a duplicate key error.

Note: This Question is unanswered, help us to find answer for this one

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

BETWEEN 1 AND 10

IN INTERVAL(1, 10)

IN INTERVAL(0, 11)

BETWEEN 0 AND 11

Answer

Correct Answer: BETWEEN 1 AND 10

Note: This Question is unanswered, help us to find answer for this one

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

SET $foo= 'bar';

$foo = 'bar';

VAR $foo ='bar';

VAR @foo = 'bar';

SET @foo = 'bar';

Answer

Correct Answer: SET @foo = 'bar';

Note: This Question is unanswered, help us to find answer for this one

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

POINTER

CURSOR

STORE

KEY

UNIQUE

Answer

Correct Answer: KEY

Note: This Question is unanswered, help us to find answer for this one

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

InnoDB tables can have relationship constraints, MyISAM tables do not

All of these are true

Both MyISAM and InnoDB can use full-text index searches

You can have both types of tables in one database

Answer

Correct Answer: All of these are true

Note: This Question is unanswered, help us to find answer for this one

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

16 byte

64 byte

32 byte

4 byte

Answer

Correct Answer: 64 byte

Note: This Question is unanswered, help us to find answer for this one

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

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

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 left table and preserve all other rows

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

Answer

Correct Answer: Values from the left table are preserved for NULL values in the right table

Note: This Question is unanswered, help us to find answer for this one

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

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

SELECT * FROM UNION TableA, TableB

SELECT * FROM TableA UNION SELECT * FROM TableB

SELECT * FROM Table A UNION TableB

SELECT UNION * FROM TableA, TableB

Answer

Correct Answer: SELECT * FROM TableA UNION SELECT * FROM TableB

Note: This Question is unanswered, help us to find answer for this one

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

ON REPEAT VALUE UPDATE

ON DUPLICATE VALUE UPDATE

ON DUPLICATE KEY UPDATE

ON REPEAT KEY UPDATE

Answer

Correct Answer: ON DUPLICATE KEY UPDATE

Note: This Question is unanswered, help us to find answer for this one

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

SELECT test;

CALL test;

SELECT test FROM procedures;

Set @variable = test;

Answer

Correct Answer: CALL test;

Note: This Question is unanswered, help us to find answer for this one

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

mysqlshow

mysqladmin

mysqldump

mysqlcheck

Answer

Correct Answer: mysqlcheck

Note: This Question is unanswered, help us to find answer for this one

349. With SQL you can manage files

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

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

UNIX_TIMESTAMP(NOW())

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

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

Answer

Correct Answer: DATE_FORMAT(NOW(), '%Y-%m-%d %T');

Note: This Question is unanswered, help us to find answer for this one

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

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

Answer

Correct Answer: Inner join returns only rows from both tables that match on the given conditional expression

Note: This Question is unanswered, help us to find answer for this one

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

Drop Table

Truncate Table

Delete Table

Alter Table

Answer

Correct Answer: Truncate Table

Note: This Question is unanswered, help us to find answer for this one

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

SELECT user_name FROM users WHERE user_phonenumber;

SELECT user_name FROM users WHERE user_phonenumber IS NULL;

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

SELECT user_name FROM users WHERE user_phonenumber = NULL;

Answer

Correct Answer: SELECT user_name FROM users WHERE user_phonenumber IS NULL;

Note: This Question is unanswered, help us to find answer for this one

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

SELECT LAST_INSERT_ID();

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

GET_AUTO_ID();

SELECT LAST_KEY_INC();

Answer

Correct Answer: SELECT LAST_INSERT_ID();

Note: This Question is unanswered, help us to find answer for this one

355. What does DCL stand for?

Data Control Language

Data Context Language

Define Context Language

Define Control Language

Answer

Correct Answer: Data Control Language

Note: This Question is unanswered, help us to find answer for this one

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

SELECT * FROM TableA WHERE ColumnX IS DISTINCT;

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

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

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

SELECT * FROM TableA WHERE ColumnX IS UNIQUE;

Answer

Correct Answer: SELECT DISTINCT * FROM TableA WHERE ColumnX="SomeValue";

Note: This Question is unanswered, help us to find answer for this one

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

SHOW COLUMNS

SHOW OPEN TABLES

SHOW DATABASE

SHOW TABLES

Answer

Correct Answer: SHOW TABLES

Note: This Question is unanswered, help us to find answer for this one

358. The data type BLOB stands for:

Binary List Object

None of these

Big List Object

Binary Large Object

Answer

Correct Answer: Binary Large Object

Note: This Question is unanswered, help us to find answer for this one

359. What represents an unknown or unspecified value?

NULL

UNSPEC

UNSPECIFIED

()

ANY

Answer

Correct Answer: NULL

Note: This Question is unanswered, help us to find answer for this one

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

24MB

1GB No Default Size 500MB

Answer

Correct Answer: 24MB

Note: This Question is unanswered, help us to find answer for this one

361. Assume, you have two tables company (id int, name text) and employee(id int, company int, name text). How would you select all employee names and their corresponding company names, e.g. John, Microsoft Mary, IBM Jane, Oracle

select employee_name, company_name from employee and company

select e.name, c.name from employee e left join company c

This is not possible

select e.name, c.name from employee e left join company c on c.id = e.company

select e.name, c.name from employee e, company c

Answer

Correct Answer: select e.name, c.name from employee e left join company c on c.id = e.company

Note: This Question is unanswered, help us to find answer for this one

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

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

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

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

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

Answer

Correct Answer: SELECT * FROM orders WHERE item_count IN (3, 5, 7, 9);

Note: This Question is unanswered, help us to find answer for this one

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

SELECT AVERAGE(values) FROM foo

SELECT AVG(values) FROM foo

SELECT values.AVERAGE FROM foo

SELECT AVERAGE OF values FROM foo

Answer

Correct Answer: SELECT AVG(values) FROM foo

Note: This Question is unanswered, help us to find answer for this one

364. HAVING clause is used in combination with _____.

GROUP BY

WHERE

FROM

SELECT

Answer

Correct Answer: GROUP BY

Note: This Question is unanswered, help us to find answer for this one

365. How can you do an UPDATE?

UPDATE tab SET f1=1 AND f2=2;

UPDATE tab SET f1=1 , f2=2;

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

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

Answer

Correct Answer: UPDATE tab SET f1=1 , f2=2;

Note: This Question is unanswered, help us to find answer for this one

366. What is a surrogate Key ?

A key that acts as a substitution to other natural keys

Same as Natural key

Same as Primary key

a type of join

Answer

Correct Answer: A key that acts as a substitution to other natural keys

Note: This Question is unanswered, help us to find answer for this one

367. 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"?

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

SELECT * FROM Persons WHERE FirstName LIKE '%a'

SELECT * FROM Persons WHERE FirstName='a'

SELECT * FROM Persons WHERE FirstName LIKE 'a%'

Answer

Correct Answer: SELECT * FROM Persons WHERE FirstName LIKE 'a%'

Note: This Question is unanswered, help us to find answer for this one

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

APPEND

ALTER

NEW

UPDATE

CREATE

Answer

Correct Answer: ALTER

Note: This Question is unanswered, help us to find answer for this one

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

%h

%H

%k

%K

Answer

Correct Answer: %H

Note: This Question is unanswered, help us to find answer for this one

370. The DELETE command in MySQL need an *

True

False

Answer

Correct Answer: False

Note: This Question is unanswered, help us to find answer for this one

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

All of these

WHERE last_name IS ALPHABETICAL

SELECT BY last_name

ORDER BY last_name

Answer

Correct Answer: ORDER BY last_name

Note: This Question is unanswered, help us to find answer for this one

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

False-True

True

True-False

False

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

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

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

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

That common queries assigned to functions to simplify statements.

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

Answer

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

Note: This Question is unanswered, help us to find answer for this one

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

restart mysql service init 0 reboot

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

Answer

Correct Answer: /etc/init.d/mysql restart service mysqld restart

Note: This Question is unanswered, help us to find answer for this one

375. How do you rename a table?

UPDATE

CHANGE

ALTER

INSERT

DELETE

Answer

Correct Answer: ALTER

Note: This Question is unanswered, help us to find answer for this one

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

MTRIM()

None of these

STRIP()

LTRIM()

TRIM()

Answer

Correct Answer: TRIM()

Note: This Question is unanswered, help us to find answer for this one

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

MERGE

COMBINE

UNION

None of these

Answer

Correct Answer: UNION

Note: This Question is unanswered, help us to find answer for this one

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

PRIMARY KEY

FIRST KEY

FOREIGN KEY

REFERENCE KEY

UNIQUE KEY

Answer

Correct Answer: PRIMARY KEY

Note: This Question is unanswered, help us to find answer for this one

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

ORDER

ORDER BY

SORT

SORT BY

Answer

Correct Answer: ORDER BY

Note: This Question is unanswered, help us to find answer for this one

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

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

None of these

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

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

Answer

Correct Answer: SELECT * FROM table WHERE name LIKE 'S%';

Note: This Question is unanswered, help us to find answer for this one

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

'1'

1

"1"

Answer

Correct Answer: 1

Note: This Question is unanswered, help us to find answer for this one

382. What locks can be applied to MyISAM tables?

Write

Print

Read and Print

Read and Write

Answer

Correct Answer: Read and Write

Note: This Question is unanswered, help us to find answer for this one

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

REDUCE

PRIMARY

DISTINCT

UNIQUE

Answer

Correct Answer: DISTINCT

Note: This Question is unanswered, help us to find answer for this one

384. What is A in ACID?

Atomicity

Analysis Allow Alter

Answer

Correct Answer: Atomicity

Note: This Question is unanswered, help us to find answer for this one

385. Which one of the following statements is correct?

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.

All of these

Answer

Correct Answer: All of these

Note: This Question is unanswered, help us to find answer for this one

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

SELECT COUNT(*) FROM Persons

SELECT COUNT() FROM Persons

SELECT COLUMNS(*) FROM Persons

SELECT COLUMNS() FROM Persons

Answer

Correct Answer: SELECT COUNT(*) FROM Persons

Note: This Question is unanswered, help us to find answer for this one

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

60

66

64

62

Answer

Correct Answer: 64

Note: This Question is unanswered, help us to find answer for this one

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

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

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

All of the rows from a table.

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

Nothing.

Answer

Correct Answer: All of the rows from a table.

Note: This Question is unanswered, help us to find answer for this one

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

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

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

They can improve security by limiting users to specific approved queries

All of these

Answer

Correct Answer: All of these

Note: This Question is unanswered, help us to find answer for this one

391. How do you remove a table from the database

REMOVE TABLE

DESTROY TABLE

DELETE TABLE

DROP TABLE

Answer

Correct Answer: DROP TABLE

Note: This Question is unanswered, help us to find answer for this one

392. MySQL is a tool to:

develope websites

manage users

make Apps

store data

Store and manipulate data

Answer

Correct Answer: Store and manipulate data

Note: This Question is unanswered, help us to find answer for this one

393. Which is TRIGGER in Mysql ?

Before Insert

After Delete

All

Before Update

Answer

Correct Answer: All

Note: This Question is unanswered, help us to find answer for this one

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

REMOVE

ERASE

COLLAPSE

DELETE

Answer

Correct Answer: DELETE

Note: This Question is unanswered, help us to find answer for this one

395. * in a SELECT statement portion does what?

Nothing

Multiply

Select All

Group By

Assign a Pointer

Answer

Correct Answer: Select All

Note: This Question is unanswered, help us to find answer for this one

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

GET

EXTRACT

OPEN

SELECT

Answer

Correct Answer: SELECT

Note: This Question is unanswered, help us to find answer for this one

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

COUNT

MAX

COMPUTE

MIN

Answer

Correct Answer: COMPUTE

Note: This Question is unanswered, help us to find answer for this one

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

False

True

Answer

Correct Answer: True

Note: This Question is unanswered, help us to find answer for this one

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

false

true

Answer

Correct Answer: true

Note: This Question is unanswered, help us to find answer for this one

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

ADD NEW

INSERT INTO

ADD RECORD

INSERT NEW

Answer

Correct Answer: INSERT INTO

Note: This Question is unanswered, help us to find answer for this one

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

DELETE FROM Persons WHERE FirstName = 'Peter'

DELETE ROW FirstName='Peter' FROM Persons

DELETE FirstName='Peter' FROM Persons

Answer

Correct Answer: DELETE FROM Persons WHERE FirstName = 'Peter'

Note: This Question is unanswered, help us to find answer for this one

402. A table column can have null values

false

true

Answer

Correct Answer: true

Note: This Question is unanswered, help us to find answer for this one

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

SELECT * FROM Persons

SELECT *.Persons

SELECT Persons

SELECT [all] FROM Persons

Answer

Correct Answer: SELECT * FROM Persons

Note: This Question is unanswered, help us to find answer for this one

404. What does SQL stand for?

Structured Question Language

Structured Query Language

Strong Question Language

Answer

Correct Answer: Structured Query Language

Note: This Question is unanswered, help us to find answer for this one

405. What does SQL mean?

Simplified Query Language

Structured Query Language

Answer

Correct Answer: Structured Query Language

Note: This Question is unanswered, help us to find answer for this one

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

SAVE

MODIFY

SAVE AS

UPDATE

Answer

Correct Answer: UPDATE

Note: This Question is unanswered, help us to find answer for this one

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

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

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

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

Answer

Correct Answer: SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

Note: This Question is unanswered, help us to find answer for this one

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

Prevents Restrictions Limitations

Constraints

Answer

Correct Answer: Constraints

Note: This Question is unanswered, help us to find answer for this one

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

COMMIT and UNDO

SAVE and ROLLBACK

COMMIT and ROLLBACK

SAVE and UNDO

Answer

Correct Answer: COMMIT and ROLLBACK

Note: This Question is unanswered, help us to find answer for this one

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

bit_or()

binary

bit_count()

bit_and()

Answer

Correct Answer: binary

Note: This Question is unanswered, help us to find answer for this one

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

Regular Key

Main Key

Primary Key

Foreign Key

Answer

Correct Answer: Primary Key

Note: This Question is unanswered, help us to find answer for this one

MySQL MCQs | Topic-wise

MCQ on Basic MySQL General MySQL MCQs