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 ...
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 3.0%
select * from messages where name =Desc Result: 1 A A_data_1 2 A A_data_2 3 A A_data_3 3.0%
select * from messages group by name Result: 1 A A_data_1 4 B B_data_1 6 C C_data_1 92.0%
Answer A and B 0.0%
Can you run multiple MySQL servers on a single machine?
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 Cu...
Select * from Products order by CurrentStock,ProductGroup 0.0%
Select * from Products order by CurrentStock DESC,ProductGroup 0.0%
Select * from Products order by ProductGroup,CurrentStock 0.0%
Select * from Products order by ProductGroup,CurrentStock DESC 100.0%
None of the above 0.0%
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 cascad...
Two tables are created 46.0%
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 0.0%
Those queries are invalid 0.0%
If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted 53.0%
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 comma...
DELETE FIRST 4 FROM table1 WHERE column1=2 0.0%
DELETE 4 FROM table1 WHERE column1=2 0.0%
DELETE WHERE column1=2 LIMIT 4 0.0%
DELETE FROM table1 WHERE column1=2 LIMIT 3 100.0%
DELETE FROM table1 WHERE column1=2 LEAVING 1 0.0%
Consider the following table definition: CREATE TABLE table1 ( column1 INT, column2 INT, column3 INT, column4 INT ) Which one of the following is the corre...
ALTER TABLE table1 ADD column2a INT AFTER column2 100.0%
MODIFY TABLE table1 ADD column2a AFTER column2 0.0%
INSERT INTO table1 column2a AS INT AFTER column2 0.0%
ALTER TABLE table1 INSERT column2a INT AFTER column2 0.0%
CHANGE TABLE table1 INSERT column2a BEFORE column3 0.0%
Columns are always added after the last column 0.0%
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 mor...
Select course from students where count(course) > 5; 0.0%
Select course from students where count(*) > 5 group by course; 0.0%
Select course from students group by course; 0.0%
Select course from students group by course having count(*) > 5; 100.0%
Select course from students group by course where count(*) > 5; 0.0%
Select course from students where count(group(course)) > 5; 0.0%
Select count(course) > 5 from students; 0.0%
None of the above 0.0%
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...
select authorname from authors where authorid in (select authorid from books where popularityrating<5) 100.0%
select authorname from authors where authorid in (select authorid from books where popularityrating<=5) 0.0%
select authorname from authors where authorid in (select bookid from books where popularityrating<5) 0.0%
select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5)) 0.0%
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) S...
select subject,count(*) as Books from books,subjects where books.popularityrating > 7 0.0%
select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject 7.0%
select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject 0.0%
select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating > 7 group by subjects.subject 92.0%
Consider the query: SELECT name FROM Students WHERE name LIKE '_a%'; Which names will be displayed?
Names starting with "a" 0.0%
Names containing "a" as the second lette 100.0%
Names starting with "a" or "A" 0.0%
Names containing "a" as any letter except the first 0.0%
Examine the data in the employees table given below: last_name department_id salary ALLEN 10 3000 MILLER 20 1500 King ...
SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id); 0.0%
SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id); 0.0%
SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id); 52.0%
SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id); 42.0%
SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY)); 4.0%
Examine the query:- select (2/2/4) from tab1; where tab1 is a table with one row. This would give a result of:
4 0.0%
2 0.0%
1 0.0%
.5 0.0%
.25 100.0%
8 0.0%
24 0.0%
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 tr...
The two statements produce identical results 62.0%
The second statement returns an error 37.0%
There is no need to specify DESC because the results are sorted in descending order by default 0.0%
None of the above statments is correct 0.0%
How can a InnoDB database be backed up without locking the tables?
mysqldump --single-transaction db_name 100.0%
mysqldump --force db_name 0.0%
mysqldump --quick db_name 0.0%
mysqldump --no-tablespaces db_name 0.0%
How can an user quickly rename a MySQL database for InnoDB?
He cannot rename any MySQL database 0.0%
By using: RENAME DATABASE db_old_name TO db_new_name 0.0%
By using: RENAME DATABASE TO db_new_name 0.0%
By creating the new empty database, then rename each table using: RENAME TABLE db_old_name.table_name TO db_new_name.table_name 100.0%
How will you change "Hansen" into "Nilsen" in the LastName column in the Persons Table?
UPDATE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen' 91.0%
UPDATE Persons SET LastName = 'Hansen' INTO LastName = 'Nilsen' 0.0%
SAVE Persons SET LastName = 'Nilsen' WHERE LastName = 'Hansen' 8.0%
SAVE Persons SET LastName = 'Hansen' INTO LastName = 'Nilsen' 0.0%
If you try to perform an arithmetic operation on a column containing NULL values, the output will be:
NULL 100.0%
An error will be generated 0.0%
Cannot be determined 0.0%
Is it possible to insert several rows into a table with a single INSERT statement?
Is the FROM clause necessary in every SELECT statement?
MySQL supports 5 different int types. Which one takes 3 bytes?
TINYINT 0.0%
MEDIUMINT 100.0%
SMALLINT 0.0%
INT 0.0%
BIGINT 0.0%
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 co...
State whether true or false: Transactions and commit/rollback are supported by MySQL using the MyISAM engine
Suppose a table has the following records: +--------------+-------------+----------------+ | Item | Price | Brand | +--------------+-------------+----------------+ | Watch ...
select item, brand, price from items where max(price) order by item 85.0%
select * from items where price = max group by item 0.0%
select item, brand, max(price) from items group by item 14.0%
select * from items where price > 200 order by item 0.0%
The Flush statement cannot be used for:
Closing any open tables in the table cache 0.0%
Closing open connections 90.0%
Flushing the log file 9.0%
Flushing the host cache 0.0%
The REPLACE statement is:
Same as the INSERT statement 0.0%
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 100.0%
There is no such statement as REPLACE 0.0%
What are MySQL Spatial Data Types in the following list?
GEOMETRY 36.0%
CIRCLE 0.0%
SQUARE 0.0%
POINT 33.0%
POLYGON 30.0%
What does DETERMINISTIC mean in the creation of a function?
The function returns no value 8.0%
The function always returns the same value for the same input 91.0%
The function returns the input value 0.0%
None of the above 0.0%
What does the term "overhead" mean in MySQL?
Temporary diskspace that the database uses to run some of the queries 83.0%
The size of a table 0.0%
A tablespace name 0.0%
None of the above 16.0%
What is NDB?
An in-memory storage engine offering high-availability and data-persistence features 100.0%
A filesystem 0.0%
An SQL superset 0.0%
MySQL scripting language 0.0%
None of the above 0.0%
What is the correct SQL syntax for returning all the columns from a table named "Persons" sorted REVERSE alphabetically by "FirstName"?
SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC 0.0%
SELECT * FROM Persons SORT REVERSE 'FirstName' 0.0%
SELECT * FROM Persons ORDER BY -'FirstName' 0.0%
SELECT * FROM Persons ORDER BY FirstName DESC 100.0%
What is the maximum size of a row in a MyISAM table?
No limit 10.0%
OS specific 0.0%
65,534 90.0%
2'147'483'648 0.0%
128 0.0%
What is the name of the utility used to extract NDB configuration information?
ndb_config 100.0%
cluster_config 0.0%
ndb --config 0.0%
configNd 0.0%
None of the above 0.0%
What is true about the ENUM data type?
An enum value may be a user variable 0.0%
An enum may contain number enclosed in quotes 55.0%
An enum cannot contain an empty string 0.0%
An enum value may be NULL 44.0%
None of the above is true 0.0%
What is wrong with the following query: select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)
In the sub query, '*' should be used instead of 'OrderID' 0.0%
The sub query can return more than one row, so, '=' should be replaced with 'in' 100.0%
The sub query should not be in parenthesis 0.0%
None of the above 0.0%
What is wrong with the following statement? create table foo (id int auto_increment, name int);
Nothing 8.0%
The id column cannot be auto incremented because it has not been defined as a primary key 91.0%
It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int); 0.0%
What privilege do you need to create a function?
UPDATE 0.0%
CREATE ROUTINE 100.0%
SELECT 0.0%
CREATE FUNCTION 0.0%
No specific privilege 0.0%
What will happen if two tables in a database are named rating and RATING?
This is not possible as table names are case in-sensitive (rating and RATING are treated as same name) 0.0%
This is possible as table names are case sensitive (rating and RATING are treated as different names) 0.0%
This is possible on UNIX/LINUX and not on Windows platform 0.0%
This is possible on Windows and not on UNIX/LINUX platforms 0.0%
This depends on lower_case_table_names system variable 100.0%
What will happen if you query the emp table as shown below: select empno, DISTINCT ename, Salary from emp;
EMPNO, unique value of ENAME and then SALARY are displayed 0.0%
EMPNO, unique value ENAME and unique value of SALARY are displayed 0.0%
DISTINCT is not a valid keyword in SQL 0.0%
No values will be displayed because the statement will return an error 100.0%
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...
SELECT ID FROM ( SELECT ID AS SECOND_ID, name FROM ( SELECT * FROM employee ) ); 0.0%
SELECT ID FROM ( SELECT ID, name AS NAME FROM ( SELECT * FROM employee ) ); 7.0%
SELECT ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) AS T ) AS T; 84.0%
SELECT ID AS FIRST_ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) ); 7.0%
Which command will make a backup on the whole database except the tables sessions and log?
mysqldump db_name sessions log > backup.sql 0.0%
mysqldump db_name | grep -vv -E "sessions|log" > backup.sql 33.0%
mysqldump db_name --ignore-table db_name.sessions db_name.log > backup.sql 66.0%
mysqldump db_name --except-table=db_name.sessions --except-table=db_name.log > backup.sql 0.0%
Which datatype is used to store binary data in MySQL?
BLOB 100.0%
BIGINT 0.0%
INT 0.0%
Both BLOB and BIGINT 0.0%
Which of the following are not Numeric column types?
BIGINT 0.0%
LARGEINT 100.0%
SMALLINT 0.0%
DOUBLE 0.0%
DECIMAL 0.0%
Which of the following are true in case of Indexes for MYISAM Tables?
Indexes can have NULL values 34.0%
BLOB and TEXT columns can be indexed 34.0%
Indexes per table cannot be more than 16 0.0%
Columns per index cannot be more than 16 30.0%
Which of the following commands will list the tables of the current database?
SHOW TABLES 100.0%
DESCRIBE TABLES 0.0%
SHOW ALL TABLES 0.0%
LIST TABLES 0.0%
Which of the following formats does the date field accept by default?
DD-MM-YYYY 0.0%
YYYY-DD-MM 0.0%
YYYY-MM-DD 100.0%
MM-DD-YY 0.0%
MMDDYYYY 0.0%
Which of the following is a correct way to show the last queries executed on MySQL?
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 100.0%
Edit the MySQL config file (mysql.con) and add the following line log = /var/log/mysql/mysql.log 0.0%
Execute VIEW .mysql_history 0.0%
Restart MySQL using the following line tail -f /var/log/mysql/mysql.log 0.0%
Which of the following is an alternative to groupwise maximum ranking (ex. ROW_NUMBER() in MS SQL)?
Using subqueries 0.0%
Using variables in a MySQL query 0.0%
Using self-join 100.0%
MySQL also supports ROW_NUMBER() 0.0%
Which of the following is an alternative to Subquery Factoring (ex. the 'WITH' clause in MS SQL Server)?
The 'IN' clause 0.0%
Using temporary tables and inline views 0.0%
The 'INNER JOIN' clause 100.0%
Using subqueries 0.0%
Which of the following is not a MySQL statement?
ENUMERATE 100.0%
EXPLAIN 0.0%
KILL 0.0%
LOAD DATA 0.0%
SET 0.0%
Which of the following is not a Table Storage specifier in MySQL?
InnoDB 0.0%
MYISAM 0.0%
BLACKHOLE 0.0%
STACK 100.0%
Which of the following is the best MySQL data type for currency values?
SMALLINT 0.0%
DECIMAL(19,4) 100.0%
VARCHAR(32) 0.0%
BIGINT 0.0%
Which of the following is the best way to disable caching for a query?
Add the /*!no_query_cache*/ comment to the query. 0.0%
Flush the whole cache with the command: FLUSH QUERY CACHE 0.0%
Reset the query cache with the command: RESET QUERY CACHE 0.0%
Use the SQL_NO_CACHE option in the query. 100.0%
Which of the following is the best way to insert a row, and to update an existing row, using a MySQL query?
Use MERGE statement 0.0%
Use INSERT ... ON DUPLICATE KEY UPDATE statement 100.0%
Use ADD UNIQUE statement 0.0%
Use REPLACE statement 0.0%
Which of the following is the best way to modify a table to allow null values?
ALTER TABLE table_name MODIFY column_name varchar(255) null 77.0%
ALTER TABLE table_name MODIFY column_name VARCHAR(255) 11.0%
ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL 11.0%
ALTER table_name MODIFY column_name varchar(255) null 0.0%
Which of the following is the correct way to determine duplicate values?
SELECT column_duplicated, sum(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated 0.0%
SELECT column_duplicated, COUNT(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated 0.0%
SELECT column_duplicated, sum(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1 0.0%
SELECT column_duplicated, COUNT(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1 100.0%
Which of the following relational database management systems is simple to embed in a larger program?
MySQL 25.0%
SQLite 75.0%
Both 0.0%
None 0.0%
Which of the following results in 0 (false)?
"EXPERTRATING" LIKE "EXP%" 0.0%
BINARY "EXPERTRATING" LIKE "EXP%" 0.0%
BINARY "EXPERTRATING" LIKE "Exp%" 100.0%
All will result in 1 (true) 0.0%
Which of the following statements are true about SQL injection attacks?
Wrapping all variables containing user input by a call to mysql_real_escape_string() makes the code immune to SQL injections. 87.0%
Parametrized queries do not make code less vulnearable to SQL injections. 0.0%
SQL injections are not possible, if only emulated prepared statements are used. 0.0%
Usage of later versions of MySQL, validation, and explicit setting of the charset of user input are valid measures to decrease vulnerability to SQL injections. 12.0%
Which of the following statements are true?
Names of databases, tables and columns can be up to 64 characters in length 52.0%
Alias names can be up to 255 characters in length 47.0%
Names of databases, tables and columns can be up to 256 characters in length 0.0%
Alias names can be up to 64 characters in length 0.0%
Which of the following statements grants permission to Peter with password Software?
GRANT ALL ON testdb.* TO peter PASSWORD 'Software' 0.0%
GRANT ALL ON testdb.* TO peter IDENTIFIED by 'Software' 100.0%
GRANT ALL OF testdb.* TO peter PASSWORD 'Software' 0.0%
GRANT ALL OF testdb.* TO peter IDENTIFIED by 'Software' 0.0%
Which of the following statements is true regarding character sets in MySQL?
The default character set of MySQL is UTF-8. 0.0%
lang.cnf sets the default character set for MySQL databases. 0.0%
SET CHARSET utf8 will set the character set of data to be imported to UTF-8. 20.0%
None of these. 80.0%
Which of the following statements is true regarding multi-table querying in MySQL?
JOIN queries are faster than WHERE queries. 20.0%
WHERE queries are faster than JOIN queries. 0.0%
INNER queries are faster than JOIN queries. 20.0%
WHERE & INNER offer the same performance in terms of speed. 60.0%
Which of the following statements is used to change the structure of a table once it has been created?
CHANGE TABLE 0.0%
MODIFY TABLE 0.0%
ALTER TABLE 100.0%
UPDATE TABLE 0.0%
Which of the following will dump the whole MySQL database to a file?
mysql -e "select * from myTable" -u myuser -pxxxxxxxxx mydatabase > mydumpfile.txt 0.0%
mysql -e "select * from myTable" mydatabase > mydumpfile.txt 0.0%
SELECT * from myTable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
' 0.0%
None of the above. 100.0%
Which of the following will raise MySQL's version of an error?
SIGNAL 100.0%
RAISE 0.0%
ERROR 0.0%
None of these. 0.0%
Which of the following will reset the MySQL password for a particular user?
UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='username'; 27.0%
UPDATE mysql.user SET Password='password' WHERE User='username'; 18.0%
UPDATE mysql.user SET Password=RESET('password') WHERE User='username'; 0.0%
None of the above. 54.0%
Which of the following will restore a MySQL DB from a .dump file?
mysql -u<user> -p < db_backup.dump 0.0%
mysql -u<user> -p<password> < db_backup.dump 85.0%
mysql -u<user> -p <password> < db_backup.dump 0.0%
mysql -u<user> -p<password> > db_backup.dump 14.0%
Which of the following will show when a table in a MySQL database was last updated?
Using the following query: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name' 100.0%
Creating an on-update trigger to write timestamp in a custom table, then querying the custom table 0.0%
Getting the "last modified" timestamp of the corresponding database file in the file system 0.0%
None of these. 0.0%
Which one of the following correctly selects rows from the table myTable that have NULL in column column1?
SELECT * FROM myTable WHERE column1 IS NULL 91.0%
SELECT * FROM myTable WHERE column1 = NULL 0.0%
SELECT * FROM myTable WHERE column1 EQUALS NULL 0.0%
SELECT * FROM myTable WHERE column1 NOT NULL 8.0%
SELECT * FROM myTable WHERE column1 CONTAINS NULL 0.0%
Which one of the following must be specified in every DELETE statement?
Table Name 100.0%
Database name 0.0%
LIMIT clause 0.0%
WHERE clause 0.0%
Which query will return values containing strings "Pizza", "Burger", or "Hotdog" in the database?
SELECT * FROM fiberbox WHERE field REGEXP 'Pizza|Burger|Hotdog'; 46.0%
SELECT * FROM fiberbox WHERE field LIKE '%Pizza%' OR field LIKE '%Burger%' OR field LIKE '%Hotdog%'; 53.0%
SELECT * FROM fiberbox WHERE field = '%Pizza%' OR field = '%Burger%' OR field = '%Hotdog%'; 0.0%
SELECT * FROM fiberbox WHERE field = '?Pizza?' OR field = '?Burger?' OR field = '?Hotdog?'; 0.0%
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 th...
SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '2002-11-11' ORDER BY purchase_date; 0.0%
SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< '2002-11-11' ORDER BY purchase date ASC; 0.0%
SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC; 0.0%
SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '2002-11-11' ORDER BY purchase_date DESC; 100.0%