Wednesday, May 1, 2013

MySQL Technical Questions


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)
    • see results above
Question 7:

Examine the two SQL statements given below:
    1. SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC ;
    2. 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
    • see above
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);
    •  9 rows 
  • d. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
    •  518789 rows
  • 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;
    •  no purchase_date
  • 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
    •  invalid
  • c. select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and (job=Analyst or clerk or salesman)
    •   invalid
  • d. None of the above 
    •  true
 Question 11:
The REPLACE statement is:
  • a. Same as the INSERT statement
    •  close but no.
  • 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' 
    • false
  • 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 
    • false 
  • 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?
  • a. & 
    • operator_bitwise-and
  • b. && 
    • Only one left.
  • c. << 
    • operator_left-shift
  • d. | 
    • operator_bitwise-or
  • e. >>
    • operator_right-shift
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)
    •  not true
  • 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
    • Query OK
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
    • ERROR 1064 (42000)
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
        • ERROR 1064 (42000): 
  • 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 
    • see example below
  • b. MySQL will generate an error 
    • see example below 
  • c. MySQL will convert all varchar datatypes into char 
    • see example below 
  • d. MySQL will convert all char datatypes into varchar
    • see example below 
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?
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?


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 
    • +----+--------------+--------------+
      | Id | CurrentStock | ProductGroup |
      +----+--------------+--------------+
      | 1 | 1 | 10 |
      | 2 | 2 | 10 |
      | 3 | 3 | 12 |
      | 4 | 4 | 11 |
      | 5 | 5 | 120 |
      | 6 | 6 | 130 |
      | 7 | 7 | 140 |
      | 8 | 8 | 160 |
      | 9 | 8 | 510 |
      | 10 | 10 | 160 |
      +----+--------------+--------------+
      10 rows in set (0.00 sec)
  • b. Select * from Products order by CurrentStock DESC,ProductGroup 
    • +----+--------------+--------------+
      | Id | CurrentStock | ProductGroup |
      +----+--------------+--------------+
      | 10 | 10 | 160 |
      | 8 | 8 | 160 |
      | 9 | 8 | 510 |
      | 7 | 7 | 140 |
      | 6 | 6 | 130 |
      | 5 | 5 | 120 |
      | 4 | 4 | 11 |
      | 3 | 3 | 12 |
      | 2 | 2 | 10 |
      | 1 | 1 | 10 |
      +----+--------------+--------------+
      10 rows in set (0.00 sec)
  • c. Select * from Products order by ProductGroup,CurrentStock 
    • +----+--------------+--------------+
      | Id | CurrentStock | ProductGroup |
      +----+--------------+--------------+
      | 1 | 1 | 10 |
      | 2 | 2 | 10 |
      | 4 | 4 | 11 |
      | 3 | 3 | 12 |
      | 5 | 5 | 120 |
      | 6 | 6 | 130 |
      | 7 | 7 | 140 |
      | 8 | 8 | 160 |
      | 10 | 10 | 160 |
      | 9 | 8 | 510 |
      +----+--------------+--------------+
      10 rows in set (0.00 sec)
  • d. Select * from Products order by ProductGroup,CurrentStock DESC 
    • +----+--------------+--------------+
      | Id | CurrentStock | ProductGroup |
      +----+--------------+--------------+
      | 2 | 2 | 10 |
      | 1 | 1 | 10 |
      | 4 | 4 | 11 |
      | 3 | 3 | 12 |
      | 5 | 5 | 120 |
      | 6 | 6 | 130 |
      | 7 | 7 | 140 |
      | 10 | 10 | 160 |
      | 8 | 8 | 160 |
      | 9 | 8 | 510 |
      +----+--------------+--------------+
      10 rows in set (0.00 sec)
  • 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?


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 
    •  x rows in set
  • b. DESCRIBE TABLES 
    •  ERROR 1146 (42S02)
  • c. SHOW ALL TABLES 
    •  ERROR 1064 (42000):
  • d. LIST TABLES
    •  ERROR 1064 (42000):

Question 31:
Which of the following is not a valid Comparison operator?


Question 32:
What will happen if you write the following statement on the MySQL prompt?
SELECT NOW();
  • a. It will display the current date 
    • +---------------------+
      | NOW() |
      +---------------------+
      | 2013-04-30 18:20:27 |
      +---------------------+
      1 row in set (0.00 sec)
  • 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 
    •  Query OK
  • b. MODIFY TABLE table1 ADD column2a AFTER column2 
    •  ERROR 1064 (42000):
  • c. INSERT INTO table1 column2a AS INT AFTER column2 
    •  ERROR 1064 (42000):
  • d. ALTER TABLE table1 INSERT column2a INT AFTER column2 
    •  ERROR 1064 (42000):
  • e. CHANGE TABLE table1 INSERT column2a BEFORE column3 
    •   ERROR 1064 (42000):
  • 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' 
    •  Query OK
  • b. UPDATE Persons SET LastName = 'Hansen' INTO LastName = 'Nilsen' 
    •  ERROR 1064 (42000):
  • c. SAVE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen' 
    •  ERROR 1064 (42000):
  • d. SAVE Persons SET LastName = 'Hansen' INTO LastName = 'Nilsen'
    •  ERROR 1064 (42000):

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)
    •  2 rows
  • 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?
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' 
    •  ERROR 1064 (42000):
  • b. SELECT LastName > 'Hansen' AND LastName < 'Pettersen' FROM Persons 
    • +------------------------------------------------+
      | LastName > 'Hansen' AND LastName < 'Pettersen' |
      +------------------------------------------------+
      | 0 |
      | 0 |
      | 1 |
      | 0 |
      +------------------------------------------------+
      4 rows in set (0.00 sec)
  • 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'
    • +-----------+
      | LastName |
      +-----------+
      | Hansen |
      | Pettersen |
      | Nilsen |
      +-----------+
      3 rows
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
    • Go for it.
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' 
    • ERROR 1064 (42000):
  • c. SELECT * FROM Persons ORDER BY -'FirstName' 
    • +-----------+-----------+
      | LastName | FirstName |
      +-----------+-----------+
      | Hansen | Alan |
      | Pettersen | Zoolander |
      | Nilsen | Bob |
      | Smith | Henry |
      +-----------+-----------+
      4 rows in set (0.00 sec)
  • d. SELECT * FROM Persons ORDER BY FirstName DESC
    • +-----------+-----------+
      | LastName | FirstName |
      +-----------+-----------+
      | Pettersen | Zoolander |
      | Smith | Henry |
      | Nilsen | Bob |
      | Hansen | Alan |
      +-----------+-----------+
      4 rows in set (0.00 sec)

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
    • +----------+
      | MAX(GPA) |
      +----------+
      | 3.90 |
      +----------+
  • b. SELECT GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
    • +------+
      | GPA |
      +------+
      | 3.40 |
      +------+
  • c. SELECT MAX(GPA) FROM STUDENT_GRADES GROUP BY SEMESTER_END
    • +----------+
      | MAX(GPA) |
      +----------+
      | 3.90 |
      +----------+
  • d. SELECT TOP 1 GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
    •  ERROR 1064 (42000):
  • e. None of the above
Question 47:
Can you run multiple MySQL servers on a single machine?
  • a. No
    •  Yes it is possible.
  • 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;
    •  (0.00 sec)
  • e. Select group(*) from students;
    • ERROR 1064 (42000):
  • f. Select name,avg(marks) from students;
    • +------+------------+
      | name | avg(marks) |
      +------+------------+
      | NULL | NULL |
      +------+------------+
      1 row in set (0.00 sec)
  • g. None of the above

Question 50:
Which of the following are aggregate functions in MySQL?
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?
  1. select
  2. order by
  3. where
  4. having
  5. 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
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
    •  1 row
  • b. SELECT * FROM myTable WHERE column1 = NULL
    •  Empty set
  • c. SELECT * FROM myTable WHERE column1 EQUALS NULL
    • ERROR 1064 (42000):
  • d. SELECT * FROM myTable WHERE column1 NOT NULL
    • ERROR 1064 (42000): 
  • e. SELECT * FROM myTable WHERE column1 CONTAINS NULL
    • ERROR 1064 (42000): 
Question 58:
Which of the following statements grants permission to Peter with password Software?
  • a. GRANT ALL ON test.* TO peter PASSWORD  'Software';
    • ERROR 1064 (42000):
  • b. GRANT ALL ON test.* TO peter IDENTIFIED by 'Software';
    •  Query OK
  • c. GRANT ALL OF test.* TO peter PASSWORD ‘Software’
    •  ERROR 1064 (42000):   OF ?
  • d. GRANT ALL OF test.* TO peter IDENTIFIED by ‘Software’
    •   ERROR 1064 (42000):  OF ?

Question 59:
Which of the following is not a MySQL statement?
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
    •  ERROR 1064 (42000)
  • b. DELETE 4 FROM table1 WHERE column1=2
    •   ERROR 1064 (42000)
  • c. DELETE WHERE column1=2 LIMIT 4
    •   ERROR 1064 (42000)
  • d. DELETE FROM table1 WHERE column1=2 LIMIT 3
    •  Query OK, 3 rows affected
  • e. DELETE FROM table1 WHERE column1=2 LEAVING 1
    •  ERROR 1064 (42000)

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
  • a. +
  • b. -
  • c. / 
  • d. *
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
    • +-------+----------+
      | name | location |
      +-------+----------+
      | Demo1 | US |
      | Demo2 | UK |
      +-------+----------+
  • b. SELECT name, location FROM `venues` AS v INNER JOIN `events` AS e ON e.venue_id = v.id;
    • +-------+----------+
      | name | location |
      +-------+----------+
      | Demo1 | US |
      | Demo2 | UK |
      +-------+----------+
  • 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
    •  +-------+----------+
      | name | location |
      +-------+----------+
      | Demo1 | US |
      | Demo2 | UK |
      +-------+----------+
  • e. SELECT name, location FROM `venues` UNION (SELECT id, venue_id FROM `events`);
    • +-------+----------+
      | name | location |
      +-------+----------+
      | Demo1 | US |
      | Demo2 | UK |
      | 1 | 1 |
      | 2 | 2 |
      +-------+----------+
Question 71:
Which command lists stored procedures in mysql?
  • a. show procedure status; 
    •  x rows
  • b. show variables like '%procedure%'; 
    •  Empty set
  • c. select * from procedures; 
    •  ERROR 1146 (42S02)
  • d. show all procedures;
    • ERROR 1064 (42000)
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; 
    •  no limit of > 5
  • d. Select course from students group by course having count(*) > 5; 
    •  x row in set
  • e. Select course from students group by course where count(*) > 5; 
    • ERROR 1064 (42000):
  • f.  Select course from students where count(group(course)) > 5; 
    • ERROR 1064 (42000): 
  • g. Select count(course) > 5 from students; 
    •  displays a count
  • h. None of the above


Posted by at
Labels: ,