Someone needs to create a new MySQL Certification. The only test around is based on MySQL 5.0 and is just drastically old.
So... Oracle, Percona, SkySQL who will claim ownership and the dedication of all the MySQL DBAs and related certifications?
This is part of a multi-part blog post.
Anyway, make sure you can at get past the MySQL 5.0 questions. Below is a few to get you started.
The point here is to know
WHY not WHAT the answer are.
UPDATED added 10-10-13 --> "About time
https://blogs.oracle.com/certification/entry/0875_01 "
MySQL Technical Questions
Question 1:
Question 6:
Which of the following statement will results in 0 (false)?
- a. SELECT "EXPERTRATING" LIKE "EXP%"
+----------------------------+
| "EXPERTRATING" LIKE "EXP%" |
+----------------------------+
| 1 |
+----------------------------+
- b. SELECT "EXPERTRATING" LIKE "Exp%"
+----------------------------+
| "EXPERTRATING" LIKE "Exp%" |
+----------------------------+
| 1 |
+----------------------------+
- c. SELECT BINARY "EXPERTRATING" LIKE "EXP%"
+-----------------------------------+
| BINARY "EXPERTRATING" LIKE "EXP%" |
+-----------------------------------+
| 1 |
+-----------------------------------+
+-----------------------------------+
| BINARY "EXPERTRATING" LIKE "Exp%" |
+-----------------------------------+
| 0 |
+-----------------------------------+
- e. All will result in 1 (true)
Question 7:
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?
- a. The two statements produce identical results
- 1:
2844047 rows
- 2:
2844047 rows
- b. The second statement returns an error
false is gave back 2844047 rows
- c. There is no need to specify DESC because the results are sorted in descending order by default
- Not true see example 7-C below.
- d. None of the above statments is correct
Test them using the
employee data database and a view:
CREATE VIEW employees_salary AS
SELECT e.emp_no , e.first_name, e.last_name , e.hire_date , s.salary as salary
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no;
SELECT last_name, salary, hire_date FROM employees_salary ORDER BY salary DESC ;
+-----------+--------+------------+
| last_name | salary | hire_date |
+-----------+--------+------------+
....
| Kuhnemann | 38786 | 1991-06-07 |
| Unno | 38735 | 1992-09-18 |
| Baek | 38623 | 1994-07-04 |
+------------------+--------+------------+
2844047 rows in set (35.78 sec)
SELECT last_name, salary, hire_date FROM employees_salary ORDER BY 2 DESC ;
+-----------+--------+------------+
| last_name | salary | hire_date |
+-----------+--------+------------+
....
| Kuhnemann | 38786 | 1991-06-07 |
| Unno | 38735 | 1992-09-18 |
| Baek | 38623 | 1994-07-04 |
+------------------+--------+------------+
2844047 rows in set (37.28 sec)
Example 7-C
[employees]> SELECT last_name, salary, hire_date FROM employees_salary ORDER BY salary limit 10;
+-----------+--------+------------+
| last_name | salary | hire_date |
+-----------+--------+------------+
| Baek | 38623 | 1994-07-04 |
| Unno | 38735 | 1992-09-18 |
| Kuhnemann | 38786 | 1991-06-07 |
| Narwekar | 38812 | 1993-09-14 |
| Langford | 38836 | 1985-10-21 |
| Kobuchi | 38849 | 1985-05-24 |
| Biran | 38850 | 1993-09-23 |
| Syang | 38851 | 1985-07-23 |
| Unno | 38859 | 1992-09-18 |
| Rohrbach | 38864 | 1992-09-02 |
+-----------+--------+------------+
10 rows in set (27.37 sec)
[employees]> SELECT last_name, salary, hire_date FROM employees_salary ORDER BY salary DESC limit 10;
+-----------+--------+------------+
| last_name | salary | hire_date |
+-----------+--------+------------+
| Pesch | 158220 | 1985-03-26 |
| Pesch | 157821 | 1985-03-26 |
| Mukaidono | 156286 | 1986-08-08 |
| Whitcomb | 155709 | 1985-07-18 |
| Luders | 155513 | 1987-04-15 |
| Alameldin | 155377 | 1985-02-15 |
| Alameldin | 155190 | 1985-02-15 |
| Alameldin | 154888 | 1985-02-15 |
| Alameldin | 154885 | 1985-02-15 |
| Baca | 154459 | 1985-02-26 |
+-----------+--------+------------+
10 rows in set (26.99 sec)
Question 8:
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?
- a. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
- ERROR 1242 (21000): Subquery returns more than 1 row
- b. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
- ERROR 1242 (21000): Subquery returns more than 1 row
- c. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
- d. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
- e. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
-
ERROR 1111 (HY000): Invalid use of group function
Question 9:
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?
- a. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '2002-11-11' ORDER BY purchase_date;
- 500 and 900 makes this invalid
- b. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< '2002- 11-11' ORDER BY purchase date ASC;
- We need a range between not IN 2 values
- c. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
- d. SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '2002-11-11' ORDER BY purchase_date DESC;
- Yes this covers all the requirements.
Question 10:
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 ?
- a. select * from Pers where joining_date from #1/1/2005# to #1/2/2005#, job=Analyst or clerk or salesman
- invalid # are comments and from is a reserved word
- b. select * from Pers where joining_date between #1/1/2005# to #1/2/2005#, job=Analyst or job=clerk or job=salesman
- c. select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and (job=Analyst or clerk or salesman)
- d. None of the above
Question 11:
The
REPLACE statement is:
- a. Same as the INSERT statement
- b. 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
- True : "
REPLACE works exactly 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. " -- MySQL Documentation
- c. There is no such statement as REPLACE
Question 12:What is wrong with the following query:
select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)
- a. In the sub query, '*' should be used instead of 'OrderID'
- b. The sub query can return more than one row, so, '=' should be replaced with 'in'
- ERROR 1242 (21000): Subquery returns more than 1 row
- c. The sub query should not be in parenthesis
- d. None of the above
- false it does work with the IN statement
Question 13:
Which of the following is
not a valid
Bit operator?
Question 14:
What is the main purpose of
InnoDB over MyISAM?
Question 15:
What will happen if two tables in a database are named rating and RATING?
- a. This is not possible as table names are case in-sensitive (rating and RATING are treated as same name)
- b. This is possible as table names are case sensitive (rating and RATING are treated as different names)
- true if
lower_case_table_names is set to 0
show variables like '%lower_case_table_names%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
[test]> desc rating; desc RATING;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
- c. This is possible on UNIX/LINUX and not on Windows platform
- "Consequently, the case
sensitivity of the underlying operating system plays a part in
the case sensitivity of database and table names. This means
database and table names are not case sensitive in Windows, and
case sensitive in most varieties of Unix. " --Manual
- d. This is possible on Windows and not on UNIX/LINUX platforms
- "Consequently, the case
sensitivity of the underlying operating system plays a part in
the case sensitivity of database and table names. This means
database and table names are not case sensitive in Windows, and
case sensitive in most varieties of Unix. " --Manual
- e. This depends on lower_case_table_names system variable
- "If set to 0, table names are stored as specified and
comparisons are case sensitive.
- If set to 1, table names are
stored in lowercase on disk and comparisons are not case
sensitive.
- If set to 2, table names are stored as given but
compared in lowercase."--Manual
More on
Identifier Case Sensitivity
Question 16:
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?
- a. 1
- b. 2
- c. 3
- d. 4
- e. 5
- f. No error in any line
Question 17:
What will happen if you query the emp table as shown below:
select empno,
DISTINCT ename, Salary from emp;
- a. EMPNO, unique value of ENAME and then SALARY are displayed
- b. EMPNO, unique value ENAME and unique value of SALARY are displayed
- c. DISTINCT is not a valid keyword in SQL
- d. No values will be displayed because the statement will return an error
Question 18:
Which of the following is
not a
String column types?
Question 19:
Below is the table “messages,” please find proper query and result from the choices below.
Id Name Other_Columns
-------------------------
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B B_data_1
5 B B_data_2
6 C C_data_1
- a. select * from (select * from messages GROUP BY id DESC) AS x ORDER BY name Result:
- 3 A A_data_3
- 5 B B_data_2
- 6 C C_data_1
- Real result is
- +----+------+---------------+
| Id | Name | Other_Columns |
+----+------+---------------+
| 3 | A | A_data_3 |
| 2 | A | A_data_2 |
| 1 | A | A_data_1 |
| 4 | B | B_data_1 |
| 5 | B | B_data_2 |
| 6 | C | C_data_1 |
+----+------+---------------+
6 rows in set (0.00 sec)
- b. select * from messages where name =Desc
- Result:
- 1 A A_data_1
- 2 A A_data_2
- 3 A A_data_3
- c. select * from messages group by name
- Result:
- 1 A A_data_1
- 4 B B_data_1
- 6 C C_data_1
- VALID Answer
- +----+------+---------------+
| Id | Name | Other_Columns |
+----+------+---------------+
| 1 | A | A_data_1 |
| 4 | B | B_data_1 |
| 6 | C | C_data_1 |
+----+------+---------------+
3 rows in set (0.00 sec)
- d. Answer A and B
Question 20:What will happen if some of the columns in a table are of char datatype and others are of varchar datatype?
- a. Nothing will happen
- b. MySQL will generate an error
- c. MySQL will convert all varchar datatypes into char
- d. MySQL will convert all char datatypes into varchar
show create table example\G
*************************** 1. row ***************************
Table: example
Create Table: CREATE TABLE `example` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(14) NOT NULL,
`Other_Columns` char(4) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Question 21:
If you insert
(00) as the value of the year in a
date column, what will be stored in the database?
show create table testdate\G
*************************** 1. row ***************************
Table: testdate
Create Table: CREATE TABLE `testdate` (
`datetest` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[test]> insert into testdate VALUES ('00,01,01');
Query OK, 1 row affected (0.04 sec)
root@localhost [test]> select * from testdate;
+------------+
| datetest |
+------------+
| 2000-01-01 |
+------------+
1 row in set (0.00 sec)
- d. Ambiguous, cannot be determined
Question 22:
Which of the following statements is true is regards to whether the schema integration problem between development, test, and production servers can be solved?
- a. True, only can create migration solution in .NET programming language.
- b. True, it can be solve by migration solution. These solutions vary by programming language.
- c. Both A and B
- d. None of the above
Question 23:
Which of the following statements are true?
- a. Names of databases, tables and columns can be up to 64 characters in length
- b. Alias names can be up to 256 characters in length
- As of MySQL 5.0.52, aliases for column names in
CREATE VIEW statements are checked
against the maximum column length of 64 characters (not the
maximum alias length of 256 characters).--Manual
- c. Names of databases, tables and columns can be up to 256 characters in length
- d. Alias names can be up to 64 characters in length
- As of MySQL 5.0.52, aliases for column names in
CREATE VIEW statements are checked
against the maximum column length of 64 characters (not the
maximum alias length of 256 characters).--Manual
Question 24:Which command will make a backup on the whole database except the tables sessions and log?
- a. mysqldump db_name sessions log > backup.sql
- b. mysqldump db_name | grep -vv -E "sessions|log" > backup.sql
- c. mysqldump db_name --ignore-table db_name.sessions db_name.log > backup.sql
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
--ignore-table=database.table.
- d. mysqldump db_name --except-table=db_name.sessions --except-table=db_name.log > backup.sql
Question 25:
Which of the following is not a valid
Logical operator?
- a. &
- b. &&
- c. AND
- d. !
- e. NOT
Question 26:
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.
CREATE TABLE `Products` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`CurrentStock` int(11) DEFAULT 0,
`ProductGroup` int(11) DEFAULT 0,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB;
[test]> INSERT INTO Products VALUES (NULL,1,10),(NULL,2,10), (NULL,3,12), (NULL,4,11), (NULL,5,120), (NULL,6,130), (NULL,7,140), (NULL,8,160), (NULL,8,510), (NULL,10,160)
- a. Select * from Products order by CurrentStock,ProductGroup
- b. Select * from Products order by CurrentStock DESC,ProductGroup
- c. Select * from Products order by ProductGroup,CurrentStock
- d. Select * from Products order by ProductGroup,CurrentStock DESC
- e. None of the above
Question 27:
What is true regarding the
TIMESTAMP data type?
- a. For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value
- <= MySQL 5.5 : It is not possible to have the current timestamp be
the default value for one column and the auto-update value
for another column.
- b. TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp
- " By default,
TIMESTAMP columns
are NOT NULL, cannot contain
NULL values, and assigning
NULL assigns the current timestamp.
" -- Manual
- c. When the server runs with the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME
- "The MySQL server can be run with the
MAXDB SQL mode enabled. In
this case, TIMESTAMP is identical with
DATETIME. If this mode is enabled at the
time that a table is created, TIMESTAMP
columns are created as DATETIME columns.
As a result, such columns use DATETIME
display format, have the same range of values, and there is
no automatic initialization or updating to the current date
and time." -- Manual
- d. A TIMESTAMP column cannot have a default value
- "One
TIMESTAMP column in a
table can have the current timestamp as the default value
for initializing the column, as the auto-update value, or
both. It is not possible to have the current timestamp be
the default value for one column and the auto-update value
for another column.
" -- Manual
- e. None of the above is true
Question 28:
Which of the following is
not a
SQL operator?
- a. Between..and..
- b. Like
- c. In
- d. Is null
- e. Not in
- f. All of the above are SQL operators
- Looks like this is all that is left
Question 29:
What does
DETERMINISTIC mean in the creation of a function?
- a. The function returns no value
- b. The function always returns the same value for the same input
- "A routine is considered “deterministic” if it always
produces the same result for the same input parameters, and
“not deterministic” otherwise." -- Manual
- c. The function returns the input value
- d. None of the above
Question 30:
Which of the following commands will list the tables of the current database?
- a. SHOW TABLES
- b. DESCRIBE TABLES
- c. SHOW ALL TABLES
- d. LIST TABLES
Question 31:
Which of the following is not a valid
Comparison operator?
- a. ==
- b. <=>
- c. !=
- d. <>
- e. REGEXP
Question 32:
What will happen if you write the following statement on the MySQL prompt?
SELECT NOW();
- a. It will display the current date
- b. It will display the error message as now does not exist.
- c. It will display a syntax error near '()'
Question 33:
Is the FROM clause necessary in every SELECT statement?
- a. Yes
- b. No
- see example below or example in question 32.
select 1 = 1;
+-------+
| 1 = 1 |
+-------+
| 1 |
+-------+
Question 34:
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?
- a. ALTER TABLE table1 ADD column2a INT AFTER column2
- b. MODIFY TABLE table1 ADD column2a AFTER column2
- c. INSERT INTO table1 column2a AS INT AFTER column2
- d. ALTER TABLE table1 INSERT column2a INT AFTER column2
- e. CHANGE TABLE table1 INSERT column2a BEFORE column3
- f. Columns are always added after the last column
Question 35:
How will you change "Hansen"
into "Nilsen" in the LastName column in the Persons Table?
CREATE TABLE `Persons` (
`LastName` varchar(244) NOT NULL DEFAULT ''
) ENGINE=InnoDB;
INSERT INTO Persons VALUE ('Hansen');
- a. UPDATE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen'
- b. UPDATE Persons SET LastName = 'Hansen' INTO LastName = 'Nilsen'
- c. SAVE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen'
- d. SAVE Persons SET LastName = 'Hansen' INTO LastName = 'Nilsen'
Question 36:
Assuming the column col1 in table tab1 has the following values:
2,3,NULL,2,3,1
What will be the output of the select statement below?
SELECT count(col1) FROM tab1
- a. 6
- b. 5
-
CREATE TABLE `tab1` (
`col1` int(11)
) ENGINE=InnoDB;
INSERT INTO tab1 VALUE (2),(3),(NULL),(2),(3),(1);
SELECT count(col1) FROM tab1;
+-------------+
| count(col1) |
+-------------+
| 5 |
+-------------+
- c. 4
- d. 3
Question 37:
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
CREATE TABLE `books` (
`bookid` int(11) NOT NULL AUTO_INCREMENT,
`bookname` varchar(244) NOT NULL DEFAULT '',
`authorid` int(11) DEFAULT 0,
`subjectid` int(11) DEFAULT 0,
`popularityrating` enum('1','2','3','4','5','6','7','8','9','10') DEFAULT NULL,
`language` enum('French','English','German','Spanish','Finnish','Dutch','Norwegian','Portuguese','Swedish') DEFAULT NULL,
PRIMARY KEY (`bookid`)
) ENGINE=InnoDB;
CREATE TABLE authors (
`authorid` int(11) NOT NULL AUTO_INCREMENT,
`authorname` varchar(255) NOT NULL DEFAULT '',
`country` char(52) NOT NULL DEFAULT '',
PRIMARY KEY (`authorid`)
) ENGINE=InnoDB;
CREATE TABLE `Subjects` (
`subjectid` int(11) NOT NULL AUTO_INCREMENT,
`subject` enum('History','English','Geography','Mathematics','Science','Social Studies','Foreign Languages','Visual and Performing Arts') DEFAULT NULL,
PRIMARY KEY (`subjectid`)
) ENGINE=InnoDB;
Which is the query to determine the
Authors who have written at least 1
book with a popularity rating
of less than 5?
INSERT INTO authors VALUES(1,"Paul DuBois",'United States'), (2,"David Hunter",'United States'),(3,"Paul Deitel",'United States'),(4,"Bruce Eckel",'United States');
INSERT INTO Subjects VALUES (NULL,'History'),(NULL,'English'),(NULL,'Geography'),(NULL,'Mathematics'),(NULL,'Science'),(NULL,'Social Studies'),(NULL,'Foreign Languages'),(NULL,'Visual and Performing Arts');
INSERT INTO books VALUES (NULL,"MySQL", 1, 5, 8, 'English'),(NULL,"Beginning XML", 2, 5, 4, 'English'),(NULL,"Java How to Program", 3, 5, 3, 'English'),(NULL,"Thinking in Java", 4, 5, 7, 'English'), (NULL,"MySQL 2nd Edition", 1, 5, 5, 'English'),(NULL,"MySQL History", 1, 1, 8, 'English');
- a. select authorname from authors where authorid in (select authorid from books where popularityrating < 5)
- b. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
- c. select authorname from authors where authorid in (select bookid from books where popularityrating<5)
- d. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))
Question 38:
The
Flush statement
cannot be used for:
- a. Closing any open tables in the table cache
- b. Closing open connections
- c. Flushing the log file
- "Closes and reopens all log files." --manual
- d. Flushing the host cache
Question 39:
What is
true regarding the
SET data type?
- a. A SET can have zero or more values
- "A
SET is a string object that can have zero
or more values"--manual
- b. A SET value may contain a comma
- "values that consist of multiple
set members are specified with members separated by commas" --manual
- c. A SET can have a maximum of 64 different members
- "A
SET column can have a maximum
of 64 distinct members. "--manual
- d. MySQL stores SET values as strings
- e. None of the above is true
Question 40:
MySQL supports 5 different int types. Which one takes 3 bytes?
- a. TINYINT
- b. MEDIUMINT
- c. SMALLINT
- d. INT
- e. BIGINT
Question 41:
What is the correct SQL syntax for selecting all the columns from the table Persons where the LastName is alphabetically between (and including) "Hansen" and "Pettersen"?
CREATE TABLE `Persons` (
`LastName` varchar(244) NOT NULL DEFAULT ''
) ENGINE=InnoDB;
REPLACE INTO Persons VALUE ('Hansen'),('Pettersen'),('Nilsen'),('Smith');
- a. SELECT * FROM Persons WHERE LastName > 'Hansen', LastName < 'Pettersen'
- b. SELECT LastName > 'Hansen' AND LastName < 'Pettersen' FROM Persons
- c. SELECT * FROM persons WHERE LastName > 'Hansen' AND LastName > 'Pettersen'
- ERROR 1146 (42S02): persons should be Persons
- d. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
Question 42:
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)
CREATE TABLE `table1` (
`foreignkey` int(11) NOT NULL AUTO_INCREMENT,
`this` int(11) DEFAULT 0,
PRIMARY KEY (`foreignkey`)
) ENGINE=InnoDB;
CREATE TABLE `table2` (
`primarykey` int(11) NOT NULL AUTO_INCREMENT,
`that` int(11) DEFAULT 0,
`somethingelse` int(11) DEFAULT 0,
PRIMARY KEY (`primarykey`),
CONSTRAINT FOREIGN KEY (`primarykey`) REFERENCES `table1` (`foreignkey`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
- a. Using the older syntax is more subject to error. If use inner joins without an ON clause, will get a syntax error.
- b. 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
- c. (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.
- "Generally, you should use the
ON clause
for conditions that specify how to join tables, and the
WHERE clause to restrict which rows you
want in the result set.
" --manual
- d. All of the Above
Question 43:
Is it possible to insert several rows into a table with a single INSERT statement?
- a. No
- b. Yes
INSERT INTO authors VALUES(1,"Paul DuBois",'United States'),
(2,"David Hunter",'United States'),(3,"Paul Deitel",'United
States'),(4,"Bruce Eckel",'United States');
Question 44:
What is the correct SQL syntax for returning all the columns from a table named "Persons" sorted REVERSE alphabetically by "FirstName"?
- a. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
Warning | 1292 | Truncated incorrect INTEGER value:
- b. SELECT * FROM Persons SORT REVERSE 'FirstName'
- c. SELECT * FROM Persons ORDER BY -'FirstName'
- d. SELECT * FROM Persons ORDER BY FirstName DESC
Question 45:
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;
- a. Yes
- b. No
-
desc foo;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
insert into foo values (NUll,1),(NULL,2);
select * from foo;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
+----+------+
2 rows in set
delete from foo where id = id-1;
Query OK, 0 rows
Question 46:
The STUDENT_GRADES table has these columns:
STUDENT_ID INT
SEMESTER_END DATE
GPA FLOAT
CREATE TABLE `STUDENT_GRADES` (
`STUDENT_ID` int(5) ,
SEMESTER_END DATE,
GPA FLOAT(5,2)
) ENGINE=InnoDB;
INSERT INTO STUDENT_GRADES VALUES (1,CURDATE(),3.4), (2,CURDATE(),2.4) , (3,CURDATE(),3.2) , (4,CURDATE(),3.9) , (5,CURDATE(),1.4) , (6,CURDATE(),3.1), (7,CURDATE(),3.3) , (8,CURDATE(),2.4),(9,CURDATE(),NULL);
Which of the following statements finds the
highest Grade Point Average (
GPA)
per semester?
- a. SELECT MAX(GPA) FROM STUDENT_GRADES WHERE GPA IS NOT NULL
- b. SELECT GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
- c. SELECT MAX(GPA) FROM STUDENT_GRADES GROUP BY SEMESTER_END
- d. SELECT TOP 1 GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
- e. None of the above
Question 47:
Can you run
multiple MySQL servers on a single machine?
- a. No
- b. Yes
- "In some cases, you might want to run multiple instances of MySQL on
a single machine" --manual
Question 48:
Which of the following formats does the
date field accept by default?
- a. DD-MM-YYYY
- b. YYYY-DD-MM
- c. YYYY-MM-DD
- "The
DATE type is used for values with a
date part but no time part. MySQL retrieves and displays
DATE values in
'YYYY-MM-DD' format" --manual
- d. MM-DD-YY
- e. MMDDYYYY
Question 49:
Which of the following queries is valid?
- a. Select * from students where marks > avg(marks);
- ERROR 1111 (HY000): Invalid use of group function
- b. Select * from students order by marks where subject = ‘SQL’;
- ERROR 1064 (42000): You have an error in your SQL syntax;
- c. Select * from students having subject =’SQL’;
- ERROR 1054 (42S22): Unknown column '’SQL’' in 'having clause'
- d. Select name from students group by subject, name;
- e. Select group(*) from students;
- f. Select name,avg(marks) from students;
- g. None of the above
Question 50:
Which of the following are
aggregate functions in MySQL?
- a. Avg
- b. Select
- c. Order By
- d. Sum
- e. Union
- f. Group by
- g. Having
Question 51:
What is
true about the ENUM data type?
- a. An enum value may be a user variable
- "You also may not employ a user variable as an enumeration
value." --manual
- b. An enum may contain number enclosed in quotes
- "An enumeration value must be a quoted string literal; it may
not be an expression, even one that evaluates to a string
value." --manual
- c. An enum cannot contain an empty string
- "The value may also be the empty string (
'')
or NULL under certain circumstances"
- If you insert an invalid value into an
ENUM the empty string is
inserted instead as a special error value.
- If strict SQL mode is enabled, attempts to insert invalid
ENUM values result in an error.
- --manual
- d. An enum value may be NULL
- "The value may also be the empty string (
'')
or NULL under certain circumstances"
- If you insert an invalid value into an
ENUM the empty string is
inserted instead as a special error value.
- If strict SQL mode is enabled, attempts to insert invalid
ENUM values result in an error.
- --manual
- e. None of the above is true
Question 52:
Which of the following operators has the
highest precedence?
- a. BINARY
- b. NOT
- c. <<
- d. %
Question 53:
What is the correct order of clauses in the
select statement?
- select
- order by
- where
- having
- group by
- a. 1,2,3,4,5
- b. 1,3,5,4,2
- select - where - group by - having - order by
- c. 1,3,5,2,4
- d. 1,3,2,5,4
- e. 1,3,2,4,5
- f. 1,5,2,3,4
- g. 1,4,2,3,5
- h. 1,4,3,2,5
Question 54:
Which of the following statements are true?
- a. BLOB and TEXT columns cannot have DEFAULT values
"BLOB and TEXT
columns cannot have DEFAULT values." -- Manual
- b. BLOB columns are treated as binary strings (byte strings)
- "
BLOB values are treated as binary strings
(byte strings)" -- Manual
- c. BLOB columns have a charset
- "They have no character set, and sorting and
comparison are based on the numeric values of the bytes in
column values." -- Manual
- d TEXT columns cannot be indexed
- "
If a
TEXT column is indexed, index entry
comparisons are space-padded at the end." -- Manual
- e. None of the above is true
Question 55:
What kind of joins does
MySQL support?
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
- a. dual join
- b.
RIGHT JOIN
- c.
NATURAL JOIN
- d. middle join
- e.
STRAIGHT_JOIN
Question 56:
What is wrong with the following statement?
create table foo
(id int auto_increment, name int);
- a. Nothing
- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
- b. The id column cannot be auto incremented because it has not been defined as a primary key
- create table foo (id int auto_increment, name int, PRIMARY KEY (`id`));
- c. It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int);
- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
Question 57:
Which one of the following correctly selects rows from the table myTable that have NULL incolumn column1?
CREATE TABLE `myTable` (
`column1` varchar(244) NULL DEFAULT ''
) ENGINE=InnoDB;
INSERT INTO myTable VALUES (1),(2),(NULL),(4);
- a. SELECT * FROM myTable WHERE column1 IS NULL
- b. SELECT * FROM myTable WHERE column1 = NULL
- c. SELECT * FROM myTable WHERE column1 EQUALS NULL
- d. SELECT * FROM myTable WHERE column1 NOT NULL
- e. SELECT * FROM myTable WHERE column1 CONTAINS NULL
Question 58:
Which of the following statements
grants permission to Peter with password Software?
- a. GRANT ALL ON test.* TO peter PASSWORD 'Software';
- b. GRANT ALL ON test.* TO peter IDENTIFIED by 'Software';
- c. GRANT ALL OF test.* TO peter PASSWORD ‘Software’
- d. GRANT ALL OF test.* TO peter IDENTIFIED by ‘Software’
Question 59:
Which of the following is
not a MySQL statement?
- a. ENUMERATE
- b. EXPLAIN
- c. KILL
- d. LOAD DATA
- e. SET
Question 60:
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?
- a. DELETE FIRST 4 FROM table1 WHERE column1=2
- b. DELETE 4 FROM table1 WHERE column1=2
- c. DELETE WHERE column1=2 LIMIT 4
- d. DELETE FROM table1 WHERE column1=2 LIMIT 3
- Query OK, 3 rows affected
- e. DELETE FROM table1 WHERE column1=2 LEAVING 1
Question 61:
You are maintaining data for a Products table, and want to see the products which have a currentstock of at least 50 more than the minimum stock limit.
The structure of the Products table is:
ProductID
ProductName
CurrentStock
MinimumStock
Two possible queries are:
(a)select * from products where currentStock > MinimumStock + 50
(b)select * from products where currentStock - 50 > MinimumStock
Choose the appropriate option with regard to the above queries.
- a. (a) is correct
- b. (b) is correct
- c. (a) and (b) both are correct
- If they used the Products table they both would be valid
- d. (a) and (b) both are incorrect
- They are both wrong because they use the products table and not the Products table
Question 62:
Which
operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 ? 8) from emp
from
highest precedence to the lowest. Operators that are shown
together on a line have the same precedence.
INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
= (assignment), :=
Question 63:
State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine
- a. True
- b. False
- nontransactional storage engines in MySQL Server
(such as
MyISAM) --Manual
Question 64:
Is the following query valid?
create table foo (id int primary key auto_increment, name varchar);
- a. No
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
- b. Yes
Question 65:
What are
MySQL Spatial Data Types in the following list?
- a. GEOMETRY
GEOMETRY can store geometry values of any
type. --Manual
- b. CIRCLE
- c. SQUARE
- d. POINT
- The other single-value types (
POINT,
LINESTRING, and POLYGON)
restrict their values to a particular geometry type.
--Manual
- e. POLYGON
- The other single-value types (
POINT,
LINESTRING, and POLYGON)
restrict their values to a particular geometry type.
--Manual
Question 66:
What is the difference between the following commands? Command D:
DELETE from customers; Command T:
TRUCATE table customers;
- a. Command D removes all entries, Command T removes entries inserted since last commit.
- b. Command D removes all entries, Command T removes all entries and resets auto-increment counters.
- c. Command D removes all entries, Command T removes all entries and deletes table meta data.
- d. Command D removes all entries, Command T recalculates indexes
" TRUNCATE TABLE empties a table
completely. Logically, this is equivalent to a
DELETE statement that deletes all
rows, but there are practical differences under some
circumstances. "
--Manual
Question 67:
What
privilege do you need to
create a function?
- a. UPDATE
- b. CREATE ROUTINE
- "The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines (procedures and functions). They can be granted at the global and database levels" --Manual
- c. SELECT
- d. CREATE FUNCTION
- "The CREATE FUNCTION statement is used to create stored functions and user-defined functions" --Manual
- e. No specific privilege
Question 68:
Consider the table employees with fields name and surname (nulls allowed in both fields) having following data:
insert into employees (name,surname) values ('John', 'Smith');
insert into employees (name,surname) values ('Peter', NULL);
insert into employees (name,surname) values (NULL, 'Brown');
What will be the output of the following query:
select CONCAT( name, ' ', COALESCE( surname,'' ) ) FROM employees;
+---------------------------------------------+
| CONCAT( name, ' ', COALESCE( surname,'' ) ) |
+---------------------------------------------+
| John Smith |
| Peter |
| NULL |
+---------------------------------------------+
- a. John Smith Peter NULL NULL Brown
- b. John Smith Peter NULL
- c. John Smith NULL Brown
- d. John Smith Peter Brown
Question 69:
What is NDB?
- a. An in-memory storage engine offering high-availability and data-persistence features
- "NDBCLUSTER (also known as NDB) is an in-memory storage engine offering high-availability and data-persistence features." --Manual
- b. A filesystem
- c. An SQL superset
- d. MySQL scripting language
- e. None of the above
Question 70:
How could the following sql query be rewritten?
SELECT name, location FROM `venues` WHERE id IN ( SELECT venue_id FROM `events` );
+-------+----------+
| name | location |
+-------+----------+
| Demo1 | US |
| Demo2 | UK |
+-------+----------+
- a. SELECT name, location FROM `venues` as v LEFT JOIN `events` as e ON e.venue_id = v.id;
- This works as long as nothing extra in the tables that would enhance the LEFT JOIN
- b. SELECT name, location FROM `venues` AS v INNER JOIN `events` AS e ON e.venue_id = v.id;
- c. SELECT name, location FROM `venues` as v, `events` as e WHERE e.venue_id = v.id;
-
+-------+----------+
| name | location |
+-------+----------+
| Demo1 | US |
| Demo2 | UK |
+-------+----------+
- d. SELECT name, location FROM `events` AS e RIGHT JOIN `venues` AS v ON v.id =e.venue_id;
- This works as long as nothing extra in the tables that would enhance the RIGHT JOIN
- e. SELECT name, location FROM `venues` UNION (SELECT id, venue_id FROM `events`);
Question 71:
Which command lists stored procedures in mysql?
- a. show procedure status;
- b. show variables like '%procedure%';
- c. select * from procedures;
- d. show all procedures;
Question 72:
Which of the following operators has the
lowest precedence?
- a. BINARY
- b. NOT
- c. <<
- d. %
Question 73:
How can a InnoDB database be backed up without locking the tables?
- a. mysqldump --single-transaction db_name
- "LOCK TABLES if the --single-transaction option is not used" --Manual
- b. mysqldump --force db_name
- c. mysqldump --quick db_name
- d. mysqldump --no-tablespaces db_name
Question 74:
Consider the following table structure of students:
rollno int
name varchar(20)
course varchar(20)
What will be the query to display the courses in which the number of students enrolled is more than 5?
- a. Select course from students where count(course) > 5;
- ERROR 1111 (HY000): Invalid use of group function
- b. Select course from students where count(*) > 5 group by course;
- ERROR 1111 (HY000): Invalid use of group function
- c. Select course from students group by course;
- d. Select course from students group by course having count(*) > 5;
- e. Select course from students group by course where count(*) > 5;
- f. Select course from students where count(group(course)) > 5;
- g. Select count(course) > 5 from students;
- h. None of the above
