Upwork MySQL 5.0 Skill Test Questions
Question: 01
Examine the code given below:
SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000 Which of the following statements 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
c. It returns employees whose salary is 50% less than $23,000
d. None of the above
Question: 02
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
Question: 03
The STUDENT_GRADES table has these columns:
STUDENT_ID INT
SEMESTER_END DATE
GPA FLOAT
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: 04
Can you run multiple MySQL servers on a single machine?
a. No
b. Yes
Question: 05
Which of the following formats does the date field accept by default?
a. DD-MM-YYYY
b. YYYY-DD-MM
c. YYYY-MM-DD
d. MM-DD-YY
e. MMDDYYYY
Question: 06
Which of the following are not String column types?
a. BLOB
b. ENUM
c. SET
d. TEXT
e. LONGCHAR
Question: 07
Which of the following queries is valid?
a. Select * from students where marks > avg(marks);
b. Select * from students order by marks where subject = ‘SQL’;
c. Select * from students having subject =’SQL’;
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: 08
Which of the following are aggregate functions in SQL?
a. Avg
b. Select
c. Order By
d. Sum
e. Union
f. Group by
g. Having
Question: 09
What is true about the ENUM data type?
a. An enum value may be a user variable
b. An enum may contain number enclosed in quotes
c. An enum cannot contain an empty string
d. An enum value may be NULL
e. None of the above is true
Question: 10
Which of the following is not a valid Arithmetic operator?
a. +
b. -
c. *
d. \
e. %
f. All are valid
Question: 11
Which of the following operators has the highest precedence?
a. BINARY
b. NOT
c. <<
d. %
Question: 12
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 255 characters in length
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: 13
Which of the following is not a valid Logical operator?
a. &
b. &&
c. AND
d. !
e. NOT
Question: 14
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
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: 15
Which of the following statements are true?
a. BLOB and TEXT columns cannot have DEFAULT values
b. BLOB columns are treated as binary strings (byte strings)
c. BLOB columns have a charset
d. TEXT columns cannot be indexed
e. None of the above is true
Question: 16
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
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 statments is correct
Question: 17
Is it possible to insert several rows into a table with a single INSERT statement?
a. No
b. Yes
Question: 18
What kind of joins does MySQL support?
a. dual join
b. right join
c. natural join
d. middle join
e. STRAIGHT_JOIN
Question: 19
Which of the following results in 0 (false)?
a. "EXPERTRATING" LIKE "EXP%"
b. "EXPERTRATING" LIKE "Exp%"
c. BINARY "EXPERTRATING" LIKE "EXP%"
d. BINARY "EXPERTRATING" LIKE "Exp%"
e. All will result in 1 (true)
Question: 20
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"?
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’
d. SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’
Question: 21
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
d. Flushing the host cache
Question: 22
Which query will display data from the Pers table relating to Analysts, Clerks and Salesmen 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
Question: 23
To quote a string within a string, which of the following can you use?
a. "This is the "quoted" message"
b. "This is the ""quoted"" message"
c. ‘This is the "quoted" message’
d. "This is the \"quoted\" message"
Question: 24
What is wrong with the following statement?
create table foo (id int auto_increment, name int);
a. Nothing
b. The id column cannot be auto incremented because it has not been defined as a primary key
c. It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int);
Question: 25
What is true regarding the SET data type?
a. A SET can have zero or more values
b. A SET value may contain a comma
c. A SET can have a maximum of 64 different members
d. MySQL stores SET values as strings
e. None of the above is true
Question: 26
Which one of the following correctly selects rows from the table myTable that have NULL in column column1?
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: 27
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)
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: 27
Which of the following statements grants permission to Peter with password Software?
a. GRANT ALL ON testdb.* TO peter PASSWORD ‘Software’
b. GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
c. GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
d. GRANT ALL OF testdb.* TO peter IDENTIFIED by ‘Software’
Question: 28
Which of the following statements relating to Alias names is true?
a. Alias names are case sensitive
b. Alias names are case in-sensitive
c. Alias names are case sensitive on UNIX and not on Windows
d. Alias names are case sensitive on Windows and not on UNIX<
e. Alias names case sensitivity depends on lower_case_table_names system setting
Question:29
If you try to perform an arithmetic operation on a column containing NULL values, the output will be:
a. 0 *
b. NULL
c. An error will be generated
d. Cannot be determined
Question: 30
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);
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);
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));
Question: 31
Which of the following is not a MySQL statement?
a. ENUMERATE
b. EXPLAIN
c. KILL
d. LOAD DATA
e. SET
Question: 32
Consider the following select statement and its output:
SELECT * FROM table1 ORDER BY column1;
Column1
——–
1
2
2
2
2
2
3
Question: 33
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
e. DELETE FROM table1 WHERE column1=2 LEAVING 1
Question: 34
You are maintaining data for a Products table, and want to see the products which have a current stock 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
d. (a) and (b) both are incorrect
Question: 35
Which operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 – 8) from emp
a. +
b. -
c. /
d. *
Question: 36
State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine
a. True
b. False
Question: 37
Is the following query valid?
create table foo (id int primary key auto_increment, name varchar);
a. No
b. Yes
Question: 38
MySQL supports 5 different int types. Which one takes 3 bytes?
a. TINYINT
b. MEDIUMINT
c. SMALLINT
d. INT
e. BIGINT
Question:39
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
Question: 40
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
f. 0
g. 8
h. 24
Examine the code given below:
SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000 Which of the following statements 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
c. It returns employees whose salary is 50% less than $23,000
d. None of the above
Question: 02
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
Question: 03
The STUDENT_GRADES table has these columns:
STUDENT_ID INT
SEMESTER_END DATE
GPA FLOAT
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: 04
Can you run multiple MySQL servers on a single machine?
a. No
b. Yes
Question: 05
Which of the following formats does the date field accept by default?
a. DD-MM-YYYY
b. YYYY-DD-MM
c. YYYY-MM-DD
d. MM-DD-YY
e. MMDDYYYY
Question: 06
Which of the following are not String column types?
a. BLOB
b. ENUM
c. SET
d. TEXT
e. LONGCHAR
Question: 07
Which of the following queries is valid?
a. Select * from students where marks > avg(marks);
b. Select * from students order by marks where subject = ‘SQL’;
c. Select * from students having subject =’SQL’;
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: 08
Which of the following are aggregate functions in SQL?
a. Avg
b. Select
c. Order By
d. Sum
e. Union
f. Group by
g. Having
Question: 09
What is true about the ENUM data type?
a. An enum value may be a user variable
b. An enum may contain number enclosed in quotes
c. An enum cannot contain an empty string
d. An enum value may be NULL
e. None of the above is true
Question: 10
Which of the following is not a valid Arithmetic operator?
a. +
b. -
c. *
d. \
e. %
f. All are valid
Question: 11
Which of the following operators has the highest precedence?
a. BINARY
b. NOT
c. <<
d. %
Question: 12
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 255 characters in length
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: 13
Which of the following is not a valid Logical operator?
a. &
b. &&
c. AND
d. !
e. NOT
Question: 14
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
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: 15
Which of the following statements are true?
a. BLOB and TEXT columns cannot have DEFAULT values
b. BLOB columns are treated as binary strings (byte strings)
c. BLOB columns have a charset
d. TEXT columns cannot be indexed
e. None of the above is true
Question: 16
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
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 statments is correct
Question: 17
Is it possible to insert several rows into a table with a single INSERT statement?
a. No
b. Yes
Question: 18
What kind of joins does MySQL support?
a. dual join
b. right join
c. natural join
d. middle join
e. STRAIGHT_JOIN
Question: 19
Which of the following results in 0 (false)?
a. "EXPERTRATING" LIKE "EXP%"
b. "EXPERTRATING" LIKE "Exp%"
c. BINARY "EXPERTRATING" LIKE "EXP%"
d. BINARY "EXPERTRATING" LIKE "Exp%"
e. All will result in 1 (true)
Question: 20
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"?
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’
d. SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’
Question: 21
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
d. Flushing the host cache
Question: 22
Which query will display data from the Pers table relating to Analysts, Clerks and Salesmen 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
Question: 23
To quote a string within a string, which of the following can you use?
a. "This is the "quoted" message"
b. "This is the ""quoted"" message"
c. ‘This is the "quoted" message’
d. "This is the \"quoted\" message"
Question: 24
What is wrong with the following statement?
create table foo (id int auto_increment, name int);
a. Nothing
b. The id column cannot be auto incremented because it has not been defined as a primary key
c. It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int);
Question: 25
What is true regarding the SET data type?
a. A SET can have zero or more values
b. A SET value may contain a comma
c. A SET can have a maximum of 64 different members
d. MySQL stores SET values as strings
e. None of the above is true
Question: 26
Which one of the following correctly selects rows from the table myTable that have NULL in column column1?
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: 27
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)
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: 27
Which of the following statements grants permission to Peter with password Software?
a. GRANT ALL ON testdb.* TO peter PASSWORD ‘Software’
b. GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
c. GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
d. GRANT ALL OF testdb.* TO peter IDENTIFIED by ‘Software’
Question: 28
Which of the following statements relating to Alias names is true?
a. Alias names are case sensitive
b. Alias names are case in-sensitive
c. Alias names are case sensitive on UNIX and not on Windows
d. Alias names are case sensitive on Windows and not on UNIX<
e. Alias names case sensitivity depends on lower_case_table_names system setting
Question:29
If you try to perform an arithmetic operation on a column containing NULL values, the output will be:
a. 0 *
b. NULL
c. An error will be generated
d. Cannot be determined
Question: 30
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);
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);
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));
Question: 31
Which of the following is not a MySQL statement?
a. ENUMERATE
b. EXPLAIN
c. KILL
d. LOAD DATA
e. SET
Question: 32
Consider the following select statement and its output:
SELECT * FROM table1 ORDER BY column1;
Column1
——–
1
2
2
2
2
2
3
Question: 33
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
e. DELETE FROM table1 WHERE column1=2 LEAVING 1
Question: 34
You are maintaining data for a Products table, and want to see the products which have a current stock 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
d. (a) and (b) both are incorrect
Question: 35
Which operator will be evaluated first in the following statement:
select (age + 3 * 4 / 2 – 8) from emp
a. +
b. -
c. /
d. *
Question: 36
State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine
a. True
b. False
Question: 37
Is the following query valid?
create table foo (id int primary key auto_increment, name varchar);
a. No
b. Yes
Question: 38
MySQL supports 5 different int types. Which one takes 3 bytes?
a. TINYINT
b. MEDIUMINT
c. SMALLINT
d. INT
e. BIGINT
Question:39
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
Question: 40
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
f. 0
g. 8
h. 24
Don't Miss A Single Updates
✓Remember to check your email account to confirm your subscription.
