MySQL Certification Quiz

1

Instructions For Quiz

This Mock Test has 40 Questions
Each Question is of 1 marks so total 40 marks
MCQ(Multiple Choice Questions) with only ONE valid answer

2

1) A systematic collection of data stored in a central location is known as?* This field is required.

csv

Database

Flat file

Excel

3

2) Which type of database management system represents relations using tables?* This field is required.

Network DBMS

Hierachical DBMS

Relational DBMS

Object Oriented DBMS

4

3) MySQL supports multiple database storage engines.* This field is required.

YES

NO

5

4) MySQL workbench decreases the productivity of a MySQL developer compared to other server access tools such as phpMyAdmin?* This field is required.

YES

NO

6

5) The logic model of a database is?* This field is required.

concerned with the planning of the entire database development life cycle

concerned with the physical implementation of the database

concerned with developing the database model taking into consideration the physical implementation

concerned with developing the database model without taking into consideration the physical implementation or the DBMS

7

6) The database development lifecycle is very important to the successful implementation of a database system.* This field is required.

YES

NO

8

7) Normalizations is...* This field is required.

a database design technique used to minimize data redundancy and duplication

a database design technique that eliminates relationships from a database

concerned with the physical implementation of the database

a database design technique that encourages storing data in multiple databases

9

8) What is a composite key?* This field is required.

its is a key that is defined as the primary key in another table

it is a key that uniquely identifies a record in a database

its is an optional key and allows null values

it is a primary key that consists of more than one field that uniquely identifies a record

10

9) Which of the following is not YES regarding ER modeling* This field is required.

ER models act as non technical communication tools

ER models increase the database developer's productivity

ER models can be converted into a database

ER models waste the database developer's time

11

10) a one to many relationship can only contain multiple entries of both primary and foreign keys.* This field is required.

YES

NO

12

11) Which of the following SQL statements will generate an error when executed.* This field is required.

CREATE DATABASE students

CREATE DATABASE students;

create database if not exists students

IF NOT EXISTS CREATE DATABASE `STUDENTS`;

13

12) the command DROP SCHEMA `STUDENTS`; will delete the students database.* This field is required.

YES

NO

14

13) The WHERE clause is mandatory when using the SELECT command* This field is required.

YES

NO

15

14) Which of the following scripts will run successfully* This field is required.

SELECT customer name FROM customers;

SELECT FROM `customers` 'customer name';

SELECT `customer name` FROM customers ORDER BY zone WHERE cat_id = 12;

SELECT `customer name` FROM customers WHERE cat_id = 12 ORDER BY cat_id;

16

15) The WHERE clause is exactly HAVING clause* This field is required.

YES

NO

17

16) What will be the output of the following query. SELECT * FROM `sales` WHERE amount > 200.* This field is required.

the query will display all the sales records with amounts starting from 200 and above

the query will display all the sales records with amounts less than 200

the query will display all the sales records with amounts greater than 200

the query will output sales records with the amount equal to 200.

18

17) The query SELECT * FROM `movies` INSERT INTO `movies_archive` executes without any errors.* This field is required.

YES

NO

19

18) What will be the output of the following query INSERT INTO students (student name) VALUE ('James Lenon');* This field is required.

a student record with the name James Lenon will be added into the students table

the above query will generate an error message.

20

19) What command is used to permanently remove a record from a database table?* This field is required.

DROP

REMOVE

DELETE

CUT

21

20) What improvements can be made to the query UPDATE students SET `contact number` = 0999;* This field is required.

The query does not need any improvements

enclose the contact number in single quotes

22

21) The clause ORDER is used to sort records in either ascending or descending order.* This field is required.

YES

NO

23

22) The ASC and DESC keywords can be used in the same statement* This field is required.

YES

NO

24

23) The GROUP BY command cannot be used with aggregate functions together* This field is required.

YES

NO

25

24) Wildcards are* This field is required.

not supported by MySQL

used to perform pattern matches

use characters such as $ to perform pattern matches

use characters such as % to perform single character matching

26

25) MySQL supports user defined functions* This field is required.

YES

NO

27

26) Which of the following is a valid aggregate function* This field is required.

CURDATE()

COUNT

AVERAGE

MAXIMUM

28

27) What will be the output of the query SELECT MINIMUM (amount) FROM sales;* This field is required.

return the lowest sales value

the query will generate an error

29

28) Null is used as a place holder for optional fields* This field is required.

YES

NO

30

29) What will be the output of the SELECT CASE WHEN NULL = NULL THEN 'YES' ELSE 'NO' END* This field is required.

YES

NO

31

30) MySQL does not support auto generating numbers* This field is required.

YES

NO

32

31) What will be the effect of inserting 500 rows in a table with the following definition. CREATE TABLE `categories` ( `category_id` tinyint(3) AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KE* This field is required.

500 rows will be inserted into the table

half the records will be inserted then an error will be generated

a table with the above definition is not possible

only 100 rows will be inserted

33

32) Once a table has been created in MySQL, its structure cannot be altered* This field is required.

YES

NO

34

33) The syntax for adding a new column to an existing table is ALTER TABLE `table_name` ADD COLUMN `column_name` `data_type`;* This field is required.

YES

NO

35

34) The employees table has 20 records, what will be the output of the query SELECT gender FROM employees GROUP BY gender;* This field is required.

the query will return two rows only

the query will return 20 records

the query will generate an error message

the query will not return any records

36

35) What will be the effect of the query SELECT TOP (100) FROM customers when executed against MySQL database.* This field is required.

The query will return 100 records from the database table sorted in descending order

The query will generate an error

The query will return 100 records from the database table sorted in ascending order

The query will return all records from the database table

37

36) What will be the output of the query SELECT * FROM `members` LIMIT 1, 2;* This field is required.

the query will generate an error

the query will return the first 2 records

the query will return skip the first record then return the following 2 records only

the query will return all records from the members table

38

37) the query SELECT name, (SELECT study_program FROM programs) study_program FROM students will return the student name and the program that the student is studying.* This field is required.

YES

NO

39

38) Sub queries have better performance compared to JOIN queries* This field is required.

YES

NO

40

39) the UNION command is used to combine resultsets from more than one query into a single result set* This field is required.

YES

NO

41

40) What will be the effect of the query SELECT c.*,e.* FROM clients c INNER JOIN employees e ON c.emp_no = e.emp_no;* This field is required.

return all the records from the clients table and only those that match the emp_no from the employees table

return all the records from the employees table and only those that match the emp_no from the clients table

return only records that have matching emp_no values in both tables

The query will generate an error