MySQL Test
MySQL Test Question & Answers from oEtab are really very effective for a new & most freelancers on UpWork (Formerly oDesk) & Elance to study about MySQL with our website www.oEtab.blogspot.com. You can also easily learn MySQL perfectly to apply your acquired skills at your another professional area even without freelancing marketplace. Actually our main aim is to tech you & develop your professional skills step by step to perform on any application software to earn money. Please Never try to Participate any kinds of skill test directly without doing perfect study. Because it is very bad idea to develop your professional skills at MySQL. If you are really searching to develope your professional skills so you have to study first correctly. After that you should take your skill test examination at any online exam center, freelancing marketplace or any educational & training institution. So, Lets start to study for developing our professional skills to develope our nation.Ques : Which of the following are true in case of Indexes for MYISAM Tables?
Ans : Indexes can have NULL values + BLOB and TEXT columns can be indexed
Ques : 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
Ans : select * from messages group by name Result: 1 A A_data_1 4 B B_data_1 6 C C_data_1
Ques : How can an user quickly rename a MySQL database for InnoDB?
Ans : By creating the new empty database, then rename each table using: RENAME TABLE db_old_name.table_name TO db_new_name.table_name
Ques : Is it possible to insert several rows into a table with a single INSERT statement?
Ans : Yes
Ques : 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?
Ans : select authorname from authors where authorid in (select authorid from books where popularityrating<5 b="">
Ques : The Flush statement cannot be used for:
Ans : Closing open connections
Ques : Consider the query:
SELECT name
FROM Students
WHERE name LIKE '_a%';
Which names will be displayed?
Ans : Names containing "a" as the second letter
Ques : Which of the following is the best MySQL data type for currency values?
Ans : DECIMAL(19,4)
Ques : What are MySQL Spatial Data Types in the following list?
Ans : GEOMETRY
Ques : 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?
Ans : The two statements produce identical results
Ques : Which of the following will raise MySQL's version of an error?
Ans : SIGNAL
Ques : Which query will return values containing strings "Pizza", "Burger", or "Hotdog" in the database?
Ans : SELECT * FROM fiberbox WHERE field LIKE '%Pizza%' OR field LIKE '%Burger%' OR field LIKE '%Hotdog%';
Ques : Which datatype is used to store binary data in MySQL?
Ans : BLOB
Ques : Which of the following will reset the MySQL password for a particular user?
Ans : None of the above.
Ques : Which of the following is the best way to modify a table to allow null values?
Ans : ALTER TABLE table_name MODIFY column_name varchar(255) null
Ques : Which of the following will dump the whole MySQL database to a file?
Ans : None of the above.
Ques : Which of the following is an alternative to groupwise maximum ranking (ex. ROW_NUMBER() in MS SQL)?
Ans : Using self-join
Ques : 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 query will determine how many books have a popularity rating of more than 7 on each subject?
Ans : select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating > 7 group by subjects.subject
Ques : Which of the following statements are true about SQL injection attacks?
Ans : Wrapping all variables containing user input by a call to mysql_real_escape_string() makes the code immune to SQL injections.
Ques : Which of the following is an alternative to Subquery Factoring (ex. the 'WITH' clause in MS SQL Server)?
Ans : The 'INNER JOIN' clause
Ques : Suppose a table has the following records:
+--------------+-------------+----------------+
| Item | Price | Brand |
+--------------+-------------+----------------+
| Watch | 100 | abc |
| Watch | 200 | xyz |
| Glasses | 300 | bcd |
| Watch | 500 | def |
| Glasses | 600 | fgh |
+--------------+-------------+----------------+
Which of the following will select the highest-priced record per item?
Ans : select item, brand, price from items where max(price) order by item
Ques : Which of the following will restore a MySQL DB from a .dump file?
Ans : mysql -u -p < db_backup.dump
Ques : Which of the following will show when a table in a MySQL database was last updated?
Ans : Using the following query: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name'
Ques : Which of the following results in 0 (false)?
Ans : BINARY "EXPERTRATING" LIKE "Exp%"
Ques : Which of the following relational database management systems is simple to embed in a larger program?
Ans : SQLite
Ques : What is true about the ENUM data type?
Ans : An enum may contain number enclosed in quotes
Ques : What will happen if two tables in a database are named rating and RATING?
Ans : This depends on lower_case_table_names system variable
Ques : How can a InnoDB database be backed up without locking the tables?
Ans : mysqldump --single-transaction db_name
Ques : What does the term "overhead" mean in MySQL?
Ans : Temporary diskspace that the database uses to run some of the queries
Ques : 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?
Ans : DELETE FROM table1 WHERE column1=2 LIMIT 3
Ques : 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);
Ans : If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted
Ques : What is NDB?
Ans : An in-memory storage engine offering high-availability and data-persistence features
Ques : Which of the following statements are true?
Ans : Names of databases, tables and columns can be up to 64 characters in length
Ques : Which of the following statements is used to change the structure of a table once it has been created?
Ans : ALTER TABLE
Ques : What does DETERMINISTIC mean in the creation of a function?
Ans : The function always returns the same value for the same input
Ques : Which of the following statements grants permission to Peter with password Software?
Ans : GRANT ALL ON testdb.* TO peter IDENTIFIED by 'Software'
Ques : What will happen if you query the emp table as shown below:
select empno, DISTINCT ename, Salary from emp;
Ans : No values will be displayed because the statement will return an error
Ques : Which of the following is the best way to disable caching for a query?
Ans : Use the SQL_NO_CACHE option in the query.
Ques : What is the maximum size of a row in a MyISAM table?
Ans : 65,534
Ques : Can you run multiple MySQL servers on a single machine?
Ans : Yes
Ques : hich of the following formats does the date field accept by default?
Ans : YYYY-MM-DD
Ques : State whether true or false:
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.
Ans : False
Ques : What is the name of the utility used to extract NDB configuration information?
Ans : ndb_config
Ques : Which one of the following must be specified in every DELETE statement?
Ans : Table Name
Ques : Which of the following are not Numeric column types?
Ans : LARGEINT
Ques : Which of the following statements is true regarding multi-table querying in MySQL?
Ans : JOIN queries are faster than WHERE queries.
Ques : What is wrong with the following statement?
create table foo (id int auto_increment, name int);
Ans : The id column cannot be auto incremented because it has not been defined as a primary key
Ques : 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?
Ans : ALTER TABLE table1 ADD column2a INT AFTER column2
Ques : 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?
Ans : SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
Ques : What privilege do you need to create a function?
Ans : CREATE ROUTINE
Ques : What is wrong with the following query:
select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)
Ans : The sub query can return more than one row, so, '=' should be replaced with 'in'
Ques : Which of the following is a correct way to show the last queries executed on MySQL?
Ans : First execute SET GLOBAL log_output = 'TABLE'; Then execute SET GLOBAL general_log = 'ON'; The last queries executed are saved in the table mysql.general_log
Ques : 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.
Ans : Select * from Products order by ProductGroup,CurrentStock DESC
Ques : What is the correct SQL syntax for returning all the columns from a table named "Persons" sorted REVERSE alphabetically by "FirstName"?
Ans : SELECT * FROM Persons ORDER BY FirstName DESC
Ques : 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?
Ans : SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '2002-11-11' ORDER BY purchase_date DESC;
Ques : State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine
Ans : False
Ques : 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?
Ans : Select course from students group by course having count(*) > 5;
Ques : MySQL supports 5 different int types. Which one takes 3 bytes?
Ans : MEDIUMINT
Ques : Which of the following is the correct way to determine duplicate values?
Ans : SELECT column_duplicated, COUNT(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1
Ques : Examine the query:-
select (2/2/4) from tab1;
where tab1 is a table with one row. This would give a result of:
Ans : .25
Ques : Which of the following commands will list the tables of the current database?
Ans : SHOW TABLES
Ques : Which of the following is not a MySQL statement?
Ans : ENUMERATE
Ques : When running the following SELECT query:
SELECT ID FROM (
SELECT ID, name FROM (
SELECT *
FROM employee
)
);
The error message 'Every derived table must have its own alias' appears.
Which of the following is the best solution for this error?
Ans : SELECT ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) AS T ) AS T;
Ques : Which of the following is not a Table Storage specifier in MySQL?
Ans : STACK
Ques : The REPLACE statement is:
Ans : 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
Ques : If you try to perform an arithmetic operation on a column containing NULL values, the output will be:
Ans : NULL
Ques : Which of the following is the best way to insert a row, and to update an existing row, using a MySQL query?
Ans : Use INSERT ... ON DUPLICATE KEY UPDATE statement
Ques : How will you change "Hansen" into "Nilsen" in the LastName column in the Persons Table?
Ans : UPDATE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen'
Ques : Which one of the following correctly selects rows from the table myTable that have NULL in column column1?
Ans : SELECT * FROM myTable WHERE column1 IS NULL
Ques : Is the FROM clause necessary in every SELECT statement?
Ans : No
We recommend you to subscribe us to get update from your email. EmoticonEmoticon