Odesk MySQL Test Answers

MySQL Test 

Question: 1

What privilege do you need to create a function?

a. UPDATE

b. CREATE ROUTINE (Answer)

c. SELECT

d. CREATE FUNCTION

Question: 2

What is NDB?

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

b. A filesystem

c. An SQL superset

d. MySQL scripting language

e. None of the above

Question: 3

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

a. ndb_config

b. cluster_config

c. ndb --config

d. configNd

e. None of the above

Question: 4

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?

Note: There may be more than one right answer.

a. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);

b. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

c. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id); (Answer)

d. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id); (Answer)

e. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

Question: 5

Which of the following are true in case of Indexes for MYISAM Tables?

Note: There may be more than one right answer.

a. Indexes can have NULL values (Answer)

b. BLOB and TEXT columns can be indexed (Answer)

c. Indexes per table cannot be more than 16

d. Columns per index cannot be more than 16

Question: 6

Consider the following queries:

create table foo (id int primary key auto_increment, name int);

create table foo2 (id int auto_increment primary key, foo_id int references foo(id) on delete cascade);

Which of the following statements is true?

Note: There may be more than one right answer.

a. Two tables are created (Answer)

b. If a row in table foo2, with a foo_id of 2 is deleted, then the row with id = 2 in table foo is automatically deleted

c. Those queries are invalid

d. If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted (Answer)

Question: 7

What kind of joins does MySQL support?

Note: There may be more than one right answer.

a. dual join

b. right join (Answer)

c. natural join (Answer)

d. middle join

e. STRAIGHT_JOIN (Answer)

Question: 8

Examine the query:-

         select (2/2/4) from tab1;

Where tab1 is a table with one row. This would give a result of:

a. 4

b. 2

c. 1

d. .5

e. .25 (Answer)

f. 8

g. 24

Question: 9

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

a. MySQL

b. SQLite (Answer)

c. Both

d. None

Question: 10

Which of the following statements are true?

Note: There may be more than one right answer.

a. Names of databases, tables and columns can be up to 64 characters in length (Answer)

b. Alias names can be up to 255 characters in length (Answer)

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

Question: 11

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

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

Consider the following tables:

books

------

bookid

bookname

authorid

subjectid

popularityrating (the popularity of the book on a scale of 1 to 10)

language (such as French, English, German etc)

Subjects

---------

subjectid

subject (such as History, Geography, Mathematics etc)

authors

--------

authorid

authorname

country

Which is the query to determine the Authors who have written at least 1 book with a popularity rating of less than 5?

a. select authorname from authors where authorid in (select authorid from books where popularityrating<5) (Answer)

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

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)

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.

d. All of the Above (Answer)

Question: 14

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

c. The function returns the input value

d. None of the above

Question: 15

What does the term "overhead" mean in MySQL?

a. Temporary diskspace that the database uses to run some of the queries (Answer)

b. The size of a table

c. A tablespace name

d. None of the above

Question: 16

Consider the query:

SELECT name

FROM Students

WHERE name LIKE '_a%';

a. Which names will be displayed?

b. Names starting with "a"

c. Names containing "a" as the second lette (Answer)

d. Names starting with "a" or "A"

e. Names containing "a" as any letter except the first

Question: 17

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

b. SELECT * FROM Persons SORT REVERSE 'FirstName'

c. SELECT * FROM Persons ORDER BY -'FirstName' (Answer)

d. SELECT * FROM Persons ORDER BY FirstName DESC (Answer)

Question: 18

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;

b. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< '2002-11-11' ORDER BY purchase date ASC;

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

Question: 19

What are MySQL Spatial Data Types in the following list?

Note: There may be more than one right answer.

a. GEOMETRY (Answer)

b. CIRCLE

c. SQUARE

d. POINT (Answer)

e. POLYGON (Answer)

Question: 20

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

a. No limit

b. OS specific

c. 65,534 (Answer)

d. 2'147'483'648

e. 128

Question: 21

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

a. CHANGE TABLE

b. MODIFY TABLE

c. ALTER TABLE (Answer)

d. UPDATE TABLE

Question: 22

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

a. SHOW TABLES (Answer)

b. DESCRIBE TABLES

c. SHOW ALL TABLES

d. LIST TABLES

Question: 23

SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000;

Which of the following statement is correct with regard to this code?

a. It returns employees whose salary is 50% more than $23,000

b. It returns employees who have 50% commission rate or salary greater than $23,000 (Answer)

c. It returns employees who salary is 50% less than $23,000

d. None of the above

Question: 24

Which of the following statement will results in 0 (false)?

a. SELECT “EXPERTRATING” LIKE “EXP%”

b. SELECT “EXPERTRATING” LIKE “Exp%”

c. SELECT BINARY “EXPERTRATING” LIKE “Exp%”

d. SELECT BINARY “EXPERTRATING” LIKE “Exp%” (Answer)

Question: 25

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

b. The second statement returns an error

c. There is no need to specify DESC because the results are sorted in descending order by default.

d. None of the above statement is correct

Question: 26

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

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

Question: 27

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

c. There is no such statement as REPLACE

Question: 28

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

c. The sub query should not be in parenthesis

d. None of the above 

Question: 29

Which of the following is not a valid Bit operator?

a. &

b. && (Answer)

c. <<

d. !

e. >>

Question: 30

Which of the following is not a string column types?

a. BLOB

b. ENUM

c. SET

d. TEXT

e. LONGCHAR (Answer)

Question: 31

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

b. MySQL will generate an error

c. MySQL will convert all varchar datatypes into char

d. MySQL will convert all char datatypes into varchar

Question: 32

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

a. 0000

b. 1900

c. 2000 (Answer)

d. Ambiguous, cannot be determined

Question: 33

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

d. None of the above

Question: 34

Which of the following is not a valid Logical operator?

a. & (Answer)

b. &&

c. AND

d. !

e. NOT

Question: 35

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

Question: 36

Which of the following is not a valid Comparison operator?

a. == (Answer)

b. <=>

c. !=

d. <>

e. REGEXP

Question: 37

The Flush statement cannot be used for:

a. Closing any open tables in the table cache

b. Closing open connections (Answer)

c. Flushing the log file

d. Flushing the lost cache

Question: 38

What is true regarding the SET data type?

a. A SET can have zero or more values (Answer)

b. A SET value may contain a comma

c. A SET can have a maximum of 64 different numbers (Answer)

d. MySQL stores SET values as strings (Answer)

e. None of the above is true

Question: 39

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

a. TINYINT

b . MEDIUMNT (Answer)

c. SMALLINT

d. INT

e. BIGINT

Question: 40

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

a. DD-MM-YYYY

b. YYYY-DD-MM

c. YYYY-MM-DD (Answer)

d. MM-DD-YY

e. MMDDYYYY

Question: 41

Which of the following are aggregate functions in MySQL?

a. Avg

b. Select

c. Order By

d. Sum (Answer)

e. Union

f. Group by

g. Having

Question: 42

What is true about the ENUM data type?

a. An enum value may be user variable

b. An enum may contain number enclosed in quotes (Answer)

c. An enum cannot contain any empty string

d. An enum value may be NULL

e. None of the above is true

Question: 43

Which of the following operators has the highest precedence?

a. BINARY (Answer)

b. NOT

c. <<

d. %

Question: 44

Which of the following is not a MySQL statement?

a. ENUMERATE (Answer)

b. EXPLAIN

c. KILL

d. LOAD DATA

e. SET

Question: 45

Which operator will be evaluated first in the following statement:

Select (age + 3 * 4 / 2 ? 8) from emp

a. +

b. –

c. /

d. * (Answer)

Question: 46

What privilege do you need to create a function?

a. UPDATE

b. CREATE ROUTINE (Answer)

c. SELECT

d. CREATE FUNCTION

e. No specific privilege

Question: 47

Which of the following operators has the lowest precedence?

a. BINARY

b. NOT (Answer)

c. <<

d. %

Question: 48

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

a. No

b. Yes (Answer)

Question: 49

Considering table foo has been created with: create 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 (Answer)

Question: 50

Can you run multiple MySQL servers on a single machine?

a. No

b. Yes (Answer)

Question: 51

State whether true or false:

Transactions and commit/rollback are supported by MySQL using the MyISAM engine

a. True

b. False (Answer)

Question: 52

Can you run multiple MySQL servers on a single machine?

a. No

b. Yes (Answer)

Question: 53

In the ‘where clause’ of a select statement, the AND operator displays a row if any of the conditions listed are true. The OR operator displays a row if all of the conditions listed are true.

a. True

b. False (Answer)

Question: 54

State whether true or false:

Transactions and commit/rollback are supported by MySQL using the MyISAM engine

a. True

b. False (Answer)