MySQL MCQs (FREE Multiple-Choice Questions)

1: What identifies a row in a table as unique?

A. Regular Key

B. Main Key

C. Primary Key

D. Foreign Key

2: Which of the following is used to control case sensitivity?

A. bit_or()

B. binary

C. bit_count()

D. bit_and()

3: A ________ is a stored program that is attached to a table or a view.

A. pseudofile

B. trigger

C. None of the above is correct.

D. embedded SELECT statement

4: What are the statements used to manage transaction processing in MySQL?

A. COMMIT and UNDO

B. SAVE and ROLLBACK

C. COMMIT and ROLLBACK

D. SAVE and UNDO

5: What limits the type of data that goes into a table?

A. Prevents Restrictions Limitations

B. Constraints

6: With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?

A. SELECT * FROM Persons WHERE FirstName<>'Peter' AND LastName<>'Jackson'

B. SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

C. SELECT FirstName='Peter', LastName='Jackson' FROM Persons

7: Which SQL statement is used to update data in a database?

A. SAVE

B. MODIFY

C. SAVE AS

D. UPDATE

8: What does SQL mean?

A. Simplified Query Language

B. Structured Query Language

9: What does SQL stand for?

A. Structured Question Language

B. Structured Query Language

C. Strong Question Language

10: With SQL, how do you select all the columns from a table named "Persons"?

A. SELECT * FROM Persons

B. SELECT *.Persons

C. SELECT Persons

D. SELECT [all] FROM Persons

11: A table column can have null values

A. false

B. true

12: With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

A. DELETE FROM Persons WHERE FirstName = 'Peter'

B. DELETE ROW FirstName='Peter' FROM Persons

C. DELETE FirstName='Peter' FROM Persons

13: Which SQL statement is used to insert new data in a database?

A. ADD NEW

B. INSERT INTO

C. ADD RECORD

D. INSERT NEW

14: It is possible to add a constraint after a table is created

A. false

B. true

15: A datatype can be changed after a table has been created.

A. False

B. True

16: Which of the following is not a valid aggregate function?

A. COUNT

B. MAX

C. COMPUTE

D. MIN

17: With SQL, how can you insert a new record into the "Persons" table?

A. INSERT ('Jimmy', 'Jackson') INTO Persons

B. INSERT VALUES ('Jimmy', 'Jackson') INTO Persons

C. INSERT INTO Persons VALUES ('Jimmy', 'Jackson')

18: Which SQL statement is used to extract data from a database?

A. GET

B. EXTRACT

C. OPEN

D. SELECT

19: * in a SELECT statement portion does what?

A. Nothing

B. Multiply

C. Select All

D. Group By

E. Assign a Pointer

20: Which SQL statement is used to delete data from a database?

A. REMOVE

B. ERASE

C. COLLAPSE

D. DELETE

21: Which is TRIGGER in Mysql ?

A. Before Insert

B. After Delete

C. All

D. Before Update

22: With SQL, how do you select a column named "FirstName" from a table named "Persons"?

A. EXTRACT FirstName FROM Persons

B. SELECT Persons.FirstName

C. SELECT FirstName FROM Persons

23: With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?

A. SELECT * FROM Persons WHERE FirstName='Peter'

B. SELECT * FROM Persons WHERE FirstName<>'Peter'

C. SELECT [all] FROM Persons WHERE FirstName='Peter'

D. SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter'

24: MySQL is a tool to:

A. develope websites

B. manage users

C. make Apps

D. store data

E. Store and manipulate data

25: How do you remove a table from the database

A. REMOVE TABLE

B. DESTROY TABLE

C. DELETE TABLE

D. DROP TABLE

26: Which of the following statements about Stored Procedures is true?

A. They can provide a common set of database operations to multiple applications

B. They allow you to store libraries of functions inside the database server

C. They can improve security by limiting users to specific approved queries

D. All of these

27: With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?

A. INSERT INTO Persons ('Olsen') INTO LastName

B. INSERT ('Olsen') INTO Persons (LastName)

C. INSERT INTO Persons (LastName) VALUES ('Olsen')

28: With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?

A. SELECT * FROM Persons ORDER BY FirstName DESC

B. SELECT * FROM Persons ORDER FirstName DESC

C. SELECT * FROM Persons SORT 'FirstName' DESC

D. SELECT * FROM Persons SORT BY 'FirstName' DESC

29: How would you write a query to select all teams that won either 2, 4, 6 or 8 games?

A. SELECT * FROM teams WHERE team_won like (2, 4, 6, 8);

B. SELECT * FROM teams WHERE team_won IN (2, 4, 6, 8);

C. SELECT * FROM teams WHERE team_won join (2, 4, 6, 8);

D. SELECT * FROM teams WHERE team_won on (2, 4, 6, 8);

30: A SELECT statement without a WHERE clause returns _____.

A. An error, because a SELECT statement is invalid without a WHERE clause.

B. All of the rows from a table.

C. All of the rows from a table that match the previous WHERE clause.

D. Nothing.

31: Can I create another MySQL instance in the same machine?

A. False

B. True

32: What is the maximum number of constituents for a SET datatype?

A. 60

B. 66

C. 64

D. 62

33: With SQL, how can you return the number of records in the "Persons" table?

A. SELECT COUNT(*) FROM Persons

B. SELECT COUNT() FROM Persons

C. SELECT COLUMNS(*) FROM Persons

D. SELECT COLUMNS() FROM Persons

34: Which one of the following statements is correct?

A. The ROLLUP modifier can only be used with the GROUP BY clause

B. The WITH ROLLUP modifier will add an extra row to the results of a query.

C. All of these

35: What is A in ACID?

A. Atomicity

B. Analysis Allow Alter

36: Which of the following clauses can be used in a SELECT statement to reduce the results to a unique list of values?

A. REDUCE

B. PRIMARY

C. DISTINCT

D. UNIQUE

37: What locks can be applied to MyISAM tables?

A. Write

B. Print

C. Read and Print

D. Read and Write

38: What does the following command output: 'SELECT 1'

A. '1'

B. 1

C. "1"

39: Which of the following queries returns all the rows whose names start with "S"?

A. SELECT * FROM table WHERE name LIKE 'S%';

B. None of these

C. SELECT * FROM table WHERE name = 'S%';

D. SELECT * FROM table WHERE name LIKE = 'S*';

40: Which SQL keyword is used to sort the result-set?

A. ORDER

B. ORDER BY

C. SORT

D. SORT BY

41: Having a key with a unique value for each row, best describes?

A. PRIMARY KEY

B. FIRST KEY

C. FOREIGN KEY

D. REFERENCE KEY

E. UNIQUE KEY

42: Which set operator is used to combine the result sets of two or more SELECT statements?

A. MERGE

B. COMBINE

C. UNION

D. None of these

43: What function eliminates beginning and ending white space characters from a string?

A. MTRIM()

B. None of these

C. STRIP()

D. LTRIM()

E. TRIM()

44: How do you rename a table?

A. UPDATE

B. CHANGE

C. ALTER

D. INSERT

E. DELETE

45: In linux how do you restart the MySQL service?

A. restart mysql service init 0 reboot

B. /etc/init.d/mysql restart service mysqld restart

46: When a MySQL database is "normalized", by common definition, it means which of the following?

A. That the database is organized in a way to reduce data redundancy. 1NF, 2NF, and 3NF forms are used as common guidelines.

B. That the data returned within any given row is converted to a single object type as most appropriate (string, int, etc.).

C. That the MySQL "strict" mode is invoked to avoid incompatibilities with legacy SQL.

D. That common queries assigned to functions to simplify statements.

E. That non-common characters are stripped from values to ensure compatibility with given character set.

47: Which of the following can add a row to a table?

A. Alter

B. Insert

C. Add

D. Update

48: I would like to retrieve the date with this format : dd/mm/YYYY in a field called btimestamp. Choose the correct way.

A. SELECT DATE_FORMAT(btimestamp,'%d/%m/%Y') AS bdate FROM ...

B. SELECT FORMAT_DATE(btimestamp,'%d/%m/%Y') AS bdate FROM ...

49: Which of these is not a valid name for a column

A. Far

B. From

C. Near

D. Under

50: TCL stands for ?

A. Transactional Control Language

B. Truncate Command Language

C. Traffic Control Language

51: 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

A. False-True

B. True

C. True-False

D. False

52: Which clause will order results by the last_name column?

A. All of these

B. WHERE last_name IS ALPHABETICAL

C. SELECT BY last_name

D. ORDER BY last_name

53: Which of the following statements about MySQL optimization is True?

A. If GROUP BY is not used, the HAVING and WHERE clauses will be merged

B. Each table Index is queried, and the best Index is used.

C. All of these

D. Constant expressions used with Indexes are only evaluated once

54: The DELETE command in MySQL need an *

A. True

B. False

55: The USE keyword is used to select a _____.

A. Table

B. Column

C. Row

D. Database

56: _____ displays the hour as an integer with zero padding, in 24H format.

A. %h

B. %H

C. %k

D. %K

57: which is not engine of msyql

A. INNODB

B. A

C. ISYAM

58: Which statement is required to add a column to an existing table?

A. APPEND

B. ALTER

C. NEW

D. UPDATE

E. CREATE

59: 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"?

A. SELECT * FROM Persons WHERE FirstName='%a%'

B. SELECT * FROM Persons WHERE FirstName LIKE '%a'

C. SELECT * FROM Persons WHERE FirstName='a'

D. SELECT * FROM Persons WHERE FirstName LIKE 'a%'

60: What is a surrogate Key ?

A. A key that acts as a substitution to other natural keys

B. Same as Natural key

C. Same as Primary key

D. a type of join

61: How can you do an UPDATE?

A. UPDATE tab SET f1=1 AND f2=2;

B. UPDATE tab SET f1=1 , f2=2;

C. UPDATE tab.f1=1 AND tab.f2=2;

D. UPDATE tab.f1=1 , tab.f2=2;

62: HAVING clause is used in combination with _____.

A. GROUP BY

B. WHERE

C. FROM

D. SELECT

63: The correct way to average all of the values from a column "values" from table "foo" is

A. SELECT AVERAGE(values) FROM foo

B. SELECT AVG(values) FROM foo

C. SELECT values.AVERAGE FROM foo

D. SELECT AVERAGE OF values FROM foo

64: How would you write a query to select all orders that contain either 3, 5, 7 or 9 items?

A. SELECT * FROM orders WHERE LIMIT item_count (3, 5, 7, 9);

B. SELECT * FROM orders WHERE item_count IN (3, 5, 7, 9);

C. SELECT * FROM orders WHERE item_count LIKE (3, 5, 7, 9);

D. SELECT * FROM orders WHERE item_count = (3, 5, 7, 9);

65: 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

A. select employee_name, company_name from employee and company

B. select e.name, c.name from employee e left join company c

C. This is not possible

D. select e.name, c.name from employee e left join company c on c.id = e.company

E. select e.name, c.name from employee e, company c

66: True or False? A table may be joined to itself.

A. False

B. True

67: The default size of the buffer for client/server communication is _______?

A. 24MB

B. 1GB No Default Size 500MB

68: What represents an unknown or unspecified value?

A. NULL

B. UNSPEC

C. UNSPECIFIED

D. ()

E. ANY

69: The data type BLOB stands for:

A. Binary List Object

B. None of these

C. Big List Object

D. Binary Large Object

70: The _____ statement enables listing of tables that match a certain pattern.

A. SHOW COLUMNS

B. SHOW OPEN TABLES

C. SHOW DATABASE

D. SHOW TABLES

71: Which of the following is the correct way to retrieve rows with no duplicates?

A. SELECT * FROM TableA WHERE ColumnX IS DISTINCT;

B. SELECT UNIQUE * FROM TableA WHERE ColumnX="SomeValue";

C. SELECT DISTINCT * FROM TableA WHERE ColumnX="SomeValue";

D. SELECT * FROM TableA WHERE ColumnX=UNIQUE("SomeValue");

E. SELECT * FROM TableA WHERE ColumnX IS UNIQUE;

72: What does DCL stand for?

A. Data Control Language

B. Data Context Language

C. Define Context Language

D. Define Control Language

73: 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?

A. SELECT LAST_INSERT_ID();

B. You can't; the row creation would have failed with a Key Constraint error.

C. GET_AUTO_ID();

D. SELECT LAST_KEY_INC();

74: How would you select all the user_name values from records whose user_phonenumber field is null?

A. SELECT user_name FROM users WHERE user_phonenumber;

B. SELECT user_name FROM users WHERE user_phonenumber IS NULL;

C. Any of these three statements may work, depending on the version of MySQL being used.

D. SELECT user_name FROM users WHERE user_phonenumber = NULL;

75: What is the BDB Configuration for RAM dedicated to holding rows and indexes?

A. bdb_max_lock

B. bdb_logdir

C. bdb_home

D. bdb_cache_size

76: What statement would delete the data in a table without deleting the table structure?

A. Drop Table

B. Truncate Table

C. Delete Table

D. Alter Table

77: What is the difference between an inner join and an outer join?

A. Inner join returns only rows from the first table; outer join returns rows from both tables

B. Inner join deletes rows that don't match; outer join does not delete any rows

C. Inner join returns only rows from both tables that match on the given conditional expression

78: Which option gets the current time in the following format? 2012-06-18 19:47:31

A. FORMAT_DATE(NOW(), '%Y-%m-%d %T')

B. UNIX_TIMESTAMP(NOW())

C. DATE_FORMAT(NOW(), '%Y-%m-%d %T');

D. GET_FORMAT(NOW(), '%Y-%m-%d %T')

79: With SQL you can manage files

A. False

B. True

80: The __________ application performs verification, repair, analysis, and optimization of MySQL databases, tables, and data.

A. mysqlshow

B. mysqladmin

C. mysqldump

D. mysqlcheck

81: Which of the following correctly calls the stored procedure "test" which has no parameters?

A. SELECT test;

B. CALL test;

C. SELECT test FROM procedures;

D. Set @variable = test;

82: 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?

A. ON REPEAT VALUE UPDATE

B. ON DUPLICATE VALUE UPDATE

C. ON DUPLICATE KEY UPDATE

D. ON REPEAT KEY UPDATE

83: Which of the following is a correct use of the UNION operator?

A. SELECT * FROM UNION TableA, TableB

B. SELECT * FROM TableA UNION SELECT * FROM TableB

C. SELECT * FROM Table A UNION TableB

D. SELECT UNION * FROM TableA, TableB

84: True of false? TEXT is case-insensitive in BLOB.

A. False

B. True

85: When joining tables using a left join, which values are always preserved?

A. Values from the left table are preserved for NULL values in the right table

B. Values from the right table are preserved for NULL values in the right table

C. The join will delete rows which contain a NULL value in the left table and preserve all other rows

D. The join will delete rows which contain a NULL value in the right table and preserve all other rows

86: What is the maximum length of a table name ?

A. 16 byte

B. 64 byte

C. 32 byte

D. 4 byte

87: Which of the following is/are true about MyISAM vs InnoDB?

A. InnoDB tables can have relationship constraints, MyISAM tables do not

B. All of these are true

C. Both MyISAM and InnoDB can use full-text index searches

D. You can have both types of tables in one database

88: Which of the following is a synonym for "INDEX" in MySQL.

A. POINTER

B. CURSOR

C. STORE

D. KEY

E. UNIQUE

89: Which of the following table declarations creates column 'name' to hold a fixed-length string?

A. CREATE TABLE test ( name(20) );

B. CREATE TABLE test (VARCHAR(20));

C. CREATE TABLE test (column: name, type:CHAR(20));

D. CREATE TABLE test (name CHAR(20));

90: Why would you turn on delay_key_write?

A. You do not need to turn this on, delay_key_write is enabled by default after the installation of MySQL.

B. Because turning delay_key_write on ensures that the database will not flush the MyISAM key file after every single write.

C. Turning delay_key_write on is actually a bad idea since it would delay everything whenever a new key is automatically generated.

91: Assign the custom `foo` variable with the value `bar`

A. SET $foo= 'bar';

B. $foo = 'bar';

C. VAR $foo ='bar';

D. VAR @foo = 'bar';

E. SET @foo = 'bar';

92: What does this statement: DELETE FROM xpto WHERE 1;

A. Delete all records in table 'xpto' but does not reset the AUTO_INCREMENT sequence

B. Delete all records in table 'xpto' and reset the AUTO_INCREMENT value to 0;

C. Throw an error depending on sql mode

D. Delete all records in table 'xpto' and resets the AUTO_INCREMENT sequence

93: What tests whether integer column i is at least 1 but no more than 10?

A. BETWEEN 1 AND 10

B. IN INTERVAL(1, 10)

C. IN INTERVAL(0, 11)

D. BETWEEN 0 AND 11

94: 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?

A. None of these

B. It stops incrementing, producing a duplicate key error.

C. The value "wraps around" to the lowest value and continues incrementing.

D. Further rows will be inserted with the same value.

95: What term describes the number of unique values in an index?

A. Cardinality

B. Seq-in-index

C. Collation

D. Sub-part

96: Which of the following statements can be used to remove user accounts from MySQL?

A. DELETE USER user;

B. DROP USER user;

C. None of these

D. REVOKE USER user;

97: Which of the following is used to count the number of rows with different items?

A. count(*)

B. count(all)

C. count()

D. count(distinct)

98: Which of the following is not a Spatial Data Type?

A. CIRCLE

B. GEOMETRY CIRCLE POINT POLYGON

99: Which of the following are true concerning indexes?

A. None are correct

B. Indexes can only be done on columns containing numerical data

C. Indexes can only be done on primary key columns

D. Indexes will slow down selects, but speed up inserts

E. Indexes will slow down inserts, but speed up selects

100: 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)?

A. alter table inventory modify price decimal(10,2)

B. Both are correct.

C. alter table inventory change column price decimal(10,2)

D. This is not possible.