Upwork Databases

MySQL Test 2016

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

Answers:

  1. Indexes can have NULL values
  2. BLOB and TEXT columns can be indexed
  3. Indexes per table cannot be more than 16
  4. Columns per index cannot be more than 16

2. 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

Answers:

  1. 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
  2. select * from messages where name =Desc Result: 1 A A_data_1 2 A A_data_2 3 A A_data_3
  3. select * from messages group by name Result: 1 A A_data_1 4 B B_data_1 6 C C_data_1
  4. Answer A and B

3. How can an user quickly rename a MySQL database for InnoDB?

Answers:

  1. He cannot rename any MySQL database
  2. By using: RENAME DATABASE db_old_name TO db_new_name
  3. By using: RENAME DATABASE TO db_new_name
  4. By creating the new empty database, then rename each table using: RENAME TABLE db_old_name.table_name TO db_new_name.table_name

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

Answers:

  1. No
  2. Yes

5. 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?

Answers:

  1. select authorname from authors where authorid in (select authorid from books where popularityrating<5)
  2. select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
  3. select authorname from authors where authorid in (select bookid from books where popularityrating<5)
  4. select authorname from authors where authorid in (select authorid from books where popularityrating in (0,5))

6. The Flush statement cannot be used for:

Answers:

  1. Closing any open tables in the table cache
  2. Closing open connections
  3. Flushing the log file
  4. Flushing the host cache

7. Consider the query:

SELECT name

FROM Students

WHERE name LIKE ‘_a%’;
Which names will be displayed?

Answers:

  1. Names starting with “a”
  2. Names containing “a” as the second lette
  3. Names starting with “a” or “A”
  4. Names containing “a” as any letter except the first

8. Which of the following is the best MySQL data type for currency values?

Answers:

  1. SMALLINT
  2. DECIMAL(19,4)
  3. VARCHAR(32)
  4. BIGINT

9. What are MySQL Spatial Data Types in the following list?

Answers:

  1. GEOMETRY
  2. CIRCLE
  3. SQUARE
  4. POINT
  5. POLYGON

10. 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?

Answers:

  1. The two statements produce identical results
  2. The second statement returns an error
  3. There is no need to specify DESC because the results are sorted in descending order by default
  4. None of the above statments is correct

11. Which of the following will raise MySQL’s version of an error?

Answers:

  1. SIGNAL
  2. RAISE
  3. ERROR
  4. None of these.

12. Which query will return values containing strings “Pizza”, “Burger”, or “Hotdog” in the database?

Answers:

  1. SELECT * FROM fiberbox WHERE field REGEXP ‘Pizza|Burger|Hotdog’;
  2. SELECT * FROM fiberbox WHERE field LIKE ‘%Pizza%’ OR field LIKE ‘%Burger%’ OR field LIKE ‘%Hotdog%’;
  3. SELECT * FROM fiberbox WHERE field = ‘%Pizza%’ OR field = ‘%Burger%’ OR field = ‘%Hotdog%’;
  4. SELECT * FROM fiberbox WHERE field = ‘?Pizza?’ OR field = ‘?Burger?’ OR field = ‘?Hotdog?’;

13. Which datatype is used to store binary data in MySQL?

Answers:

  1. BLOB
  2. BIGINT
  3. INT
  4. Both BLOB and BIGINT

14. Which of the following will reset the MySQL password for a particular user?

Answers:

  1. UPDATE mysql.user SET Password=PASSWORD(‘password’) WHERE User=’username’;
  2. UPDATE mysql.user SET Password=’password’ WHERE User=’username’;
  3. UPDATE mysql.user SET Password=RESET(‘password’) WHERE User=’username’;
  4. None of the above.

15. Which of the following is the best way to modify a table to allow null values?

Answers:

  1. ALTER TABLE table_name MODIFY column_name varchar(255) null
  2. ALTER TABLE table_name MODIFY column_name VARCHAR(255)
  3. ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL
  4. ALTER table_name MODIFY column_name varchar(255) null

16. Which of the following will dump the whole MySQL database to a file?

Answers:

  1. mysql -e “select * from myTable” -u myuser -pxxxxxxxxx mydatabase > mydumpfile.txt
  2. mysql -e “select * from myTable” mydatabase > mydumpfile.txt
  3. SELECT * from myTable FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘n’
  4. None of the above.

17. Which of the following statements is true regarding character sets in MySQL?

Answers:

  1. The default character set of MySQL is UTF-8.
  2. lang.cnf sets the default character set for MySQL databases.
  3. SET CHARSET utf8 will set the character set of data to be imported to UTF-8.
  4. None of these.

18. Which of the following is an alternative to groupwise maximum ranking (ex. ROW_NUMBER() in MS SQL)?

Answers:

  1. Using subqueries
  2. Using variables in a MySQL query
  3. Using self-join
  4. MySQL also supports ROW_NUMBER()

19. 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?

Answers:

  1. select subject,count(*) as Books from books,subjects where books.popularityrating > 7
  2. select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject
  3. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject
  4. select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating > 7 group by subjects.subject

20. Which of the following statements are true about SQL injection attacks?

Answers:

  1. Wrapping all variables containing user input by a call to mysql_real_escape_string() makes the code immune to SQL injections.
  2. Parametrized queries do not make code less vulnearable to SQL injections.
  3. SQL injections are not possible, if only emulated prepared statements are used.
  4. 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.

21. Which of the following is an alternative to Subquery Factoring (ex. the ‘WITH’ clause in MS SQL Server)?

Answers:

  1. The ‘IN’ clause
  2. Using temporary tables and inline views
  3. The ‘INNER JOIN’ clause
  4. Using subqueries

22. 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?

Answers:

  1. select item, brand, price from items where max(price) order by item
  2. select * from items where price = max group by item
  3. select item, brand, max(price) from items group by item
  4. select * from items where price > 200 order by item

23. Which of the following will restore a MySQL DB from a .dump file?

Answers:

  1. mysql -u<user> -p < db_backup.dump
  2. mysql -u<user> -p<password> < db_backup.dump
  3. mysql -u<user> -p <password> < db_backup.dump
  4. mysql -u<user> -p<password> > db_backup.dump

24. Which of the following will show when a table in a MySQL database was last updated?

Answers:

  1. Using the following query: SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = ‘database_name’ AND TABLE_NAME = ‘table_name’
  2. Creating an on-update trigger to write timestamp in a custom table, then querying the custom table
  3. Getting the “last modified” timestamp of the corresponding database file in the file system
  4. None of these.

25. Which of the following results in 0 (false)?

Answers:

  1. “EXPERTRATING” LIKE “EXP%”
  2. “EXPERTRATING” LIKE “Exp%”
  3. BINARY “EXPERTRATING” LIKE “EXP%”
  4. BINARY “EXPERTRATING” LIKE “Exp%”
  5. All will result in 1 (true)

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

Answers:

  1. MySQL
  2. SQLite
  3. Both
  4. None

27. What is true about the ENUM data type?

Answers:

  1. An enum value may be a user variable
  2. An enum may contain number enclosed in quotes
  3. An enum cannot contain an empty string
  4. An enum value may be NULL
  5. None of the above is true

28. What will happen if two tables in a database are named rating and RATING?

Answers:

  1. This is not possible as table names are case in-sensitive (rating and RATING are treated as same name)
  2. This is possible as table names are case sensitive (rating and RATING are treated as different names)
  3. This is possible on UNIX/LINUX and not on Windows platform
  4. This is possible on Windows and not on UNIX/LINUX platforms
  5. This depends on lower_case_table_names system variable

29. How can a InnoDB database be backed up without locking the tables?

Answers:

  1. mysqldump –single-transaction db_name
  2. mysqldump –force db_name
  3. mysqldump –quick db_name
  4. mysqldump –no-tablespaces db_name

30. What does the term “overhead” mean in MySQL?

Answers:

  1. Temporary diskspace that the database uses to run some of the queries
  2. The size of a table
  3. A tablespace name
  4. None of the above

31. 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?

Answers:

  1. DELETE FIRST 4 FROM table1 WHERE column1=2
  2. DELETE 4 FROM table1 WHERE column1=2
  3. DELETE WHERE column1=2 LIMIT 4
  4. DELETE FROM table1 WHERE column1=2 LIMIT 3
  5. DELETE FROM table1 WHERE column1=2 LEAVING 1

32. 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?

Answers:

  1. Two tables are created
  2. 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
  3. Those queries are invalid
  4. If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted

33. What is NDB?

Answers:

  1. An in-memory storage engine offering high-availability and data-persistence features
  2. A filesystem
  3. An SQL superset
  4. MySQL scripting language
  5. None of the above

34. Which of the following statements are true?

Answers:

  1. Names of databases, tables and columns can be up to 64 characters in length
  2. Alias names can be up to 255 characters in length
  3. Names of databases, tables and columns can be up to 256 characters in length
  4. Alias names can be up to 64 characters in length

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

Answers:

  1. CHANGE TABLE
  2. MODIFY TABLE
  3. ALTER TABLE
  4. UPDATE TABLE

36. What does DETERMINISTIC mean in the creation of a function?

Answers:

  1. The function returns no value
  2. The function always returns the same value for the same input
  3. The function returns the input value
  4. None of the above

37. Which of the following statements grants permission to Peter with password Software?

Answers:

  1. GRANT ALL ON testdb.* TO peter PASSWORD ‘Software’
  2. GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
  3. GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
  4. GRANT ALL OF testdb.* TO peter IDENTIFIED by ‘Software’

38. What will happen if you query the emp table as shown below:

select empno, DISTINCT ename, Salary from emp;

Answers:

  1. EMPNO, unique value of ENAME and then SALARY are displayed
  2. EMPNO, unique value ENAME and unique value of SALARY are displayed
  3. DISTINCT is not a valid keyword in SQL
  4. No values will be displayed because the statement will return an error

39. Which of the following is the best way to disable caching for a query?

Answers:

  1. Add the /*!no_query_cache*/ comment to the query.
  2. Flush the whole cache with the command: FLUSH QUERY CACHE
  3. Reset the query cache with the command: RESET QUERY CACHE
  4. Use the SQL_NO_CACHE option in the query.

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

Answers:

  1. No limit
  2. OS specific
  3. 65,534
  4. 2’147’483’648
  5. 128

41. Can you run multiple MySQL servers on a single machine?

Answers:

  1. No
  2. Yes

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

Answers:

  1. DD-MM-YYYY
  2. YYYY-DD-MM
  3. YYYY-MM-DD
  4. MM-DD-YY
  5. MMDDYYYY

43. 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.

Answers:

  1. True
  2. False

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

Answers:

  1. ndb_config
  2. cluster_config
  3. ndb –config
  4. configNd
  5. None of the above

45. Which one of the following must be specified in every DELETE statement?

Answers:

  1. Table Name
  2. Database name
  3. LIMIT clause
  4. WHERE clause

46. Which of the following are not Numeric column types?

Answers:

  1. BIGINT
  2. LARGEINT
  3. SMALLINT
  4. DOUBLE
  5. DECIMAL

47. Which of the following statements is true regarding multi-table querying in MySQL?

Answers:

  1. JOIN queries are faster than WHERE queries.
  2. WHERE queries are faster than JOIN queries.
  3. INNER queries are faster than JOIN queries.
  4. WHERE & INNER offer the same performance in terms of speed.

48. What is wrong with the following statement?

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

Answers:

  1. Nothing
  2. The id column cannot be auto incremented because it has not been defined as a primary key
  3. It is not spelled correctly. It should be: CREATE TABLE foo (id int AUTO_INCREMENT, name int);

49. 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?

Answers:

  1. ALTER TABLE table1 ADD column2a INT AFTER column2
  2. MODIFY TABLE table1 ADD column2a AFTER column2
  3. INSERT INTO table1 column2a AS INT AFTER column2
  4. ALTER TABLE table1 INSERT column2a INT AFTER column2
  5. CHANGE TABLE table1 INSERT column2a BEFORE column3
  6. Columns are always added after the last column

50. 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?

Answers:

  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
  2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
  3. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
  4. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
  5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

51. What privilege do you need to create a function?

Answers:

  1. UPDATE
  2. CREATE ROUTINE
  3. SELECT
  4. CREATE FUNCTION
  5. No specific privilege

52. What is wrong with the following query:

select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)

Answers:

  1. In the sub query, ‘*’ should be used instead of ‘OrderID’
  2. The sub query can return more than one row, so, ‘=’ should be replaced with ‘in’
  3. The sub query should not be in parenthesis
  4. None of the above

53. Which of the following is a correct way to show the last queries executed on MySQL?

Answers:

  1. 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
  2. Edit the MySQL config file (mysql.con) and add the following line log = /var/log/mysql/mysql.log
  3. Execute VIEW .mysql_history
  4. Restart MySQL using the following line tail -f /var/log/mysql/mysql.log

54. 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.

Answers:

  1. Select * from Products order by CurrentStock,ProductGroup
  2. Select * from Products order by CurrentStock DESC,ProductGroup
  3. Select * from Products order by ProductGroup,CurrentStock
  4. Select * from Products order by ProductGroup,CurrentStock DESC
  5. None of the above

55. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?

Answers:

  1. SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
  2. SELECT * FROM Persons SORT REVERSE ‘FirstName’
  3. SELECT * FROM Persons ORDER BY -‘FirstName’
  4. SELECT * FROM Persons ORDER BY FirstName DESC

56. 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?

Answers:

  1. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ‘2002-11-11’ ORDER BY purchase_date;
  2. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< ‘2002-11-11’ ORDER BY purchase date ASC;
  3. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
  4. SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ‘2002-11-11’ ORDER BY purchase_date DESC;

57. State whether true or false:
Transactions and commit/rollback are supported by MySQL using the MyISAM engine

Answers:

  1. True
  2. False

58. 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?

Answers:

  1. Select course from students where count(course) > 5;
  2. Select course from students where count(*) > 5 group by course;
  3. Select course from students group by course;
  4. Select course from students group by course having count(*) > 5;
  5. Select course from students group by course where count(*) > 5;
  6. Select course from students where count(group(course)) > 5;
  7. Select count(course) > 5 from students;
  8. None of the above

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

Answers:

  1. TINYINT
  2. MEDIUMINT
  3. SMALLINT
  4. INT
  5. BIGINT

60. Which of the following is the correct way to determine duplicate values?

Answers:

  1. SELECT column_duplicated, sum(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
  2. SELECT column_duplicated, COUNT(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
  3. SELECT column_duplicated, sum(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1
  4. SELECT column_duplicated, COUNT(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1

61. Examine the query:-

select (2/2/4) from tab1;

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

Answers:

  1. 4
  2. 2
  3. 1
  4. .5
  5. .25
  6. 8
  7. 24

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

Answers:

  1. SHOW TABLES
  2. DESCRIBE TABLES
  3. SHOW ALL TABLES
  4. LIST TABLES

63. Which of the following is not a MySQL statement?

Answers:

  1. ENUMERATE
  2. EXPLAIN
  3. KILL
  4. LOAD DATA
  5. SET

64. 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?

Answers:

  1. SELECT ID FROM ( SELECT ID AS SECOND_ID, name FROM ( SELECT * FROM employee ) );
  2. SELECT ID FROM ( SELECT ID, name AS NAME FROM ( SELECT * FROM employee ) );
  3. SELECT ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) AS T ) AS T;
  4. SELECT ID AS FIRST_ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) );

65. Which of the following is not a Table Storage specifier in MySQL?

Answers:

  1. InnoDB
  2. MYISAM
  3. BLACKHOLE
  4. STACK

66. The REPLACE statement is:

Answers:

  1. Same as the INSERT statement
  2. 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
  3. There is no such statement as REPLACE

67. If you try to perform an arithmetic operation on a column containing NULL values, the output will be:

Answers:

  1. NULL
  2. An error will be generated
  3. Cannot be determined

68. Which of the following is the best way to insert a row, and to update an existing row, using a MySQL query?

Answers:

  1. Use MERGE statement
  2. Use INSERT … ON DUPLICATE KEY UPDATE statement
  3. Use ADD UNIQUE statement
  4. Use REPLACE statement

69. How will you change “Hansen” into “Nilsen” in the LastName column in the Persons Table?

Answers:

  1. UPDATE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
  2. UPDATE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’
  3. SAVE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
  4. SAVE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’

70. Which one of the following correctly selects rows from the table myTable that have NULL in column column1?

Answers:

  1. SELECT * FROM myTable WHERE column1 IS NULL
  2. SELECT * FROM myTable WHERE column1 = NULL
  3. SELECT * FROM myTable WHERE column1 EQUALS NULL
  4. SELECT * FROM myTable WHERE column1 NOT NULL
  5. SELECT * FROM myTable WHERE column1 CONTAINS NULL

71. Is the FROM clause necessary in every SELECT statement?

Answers:

  1. Yes
  2. No

72. Which command will make a backup on the whole database except the tables sessions and log?

Answers:

  1. mysqldump db_name sessions log > backup.sql
  2. mysqldump db_name | grep -vv -E “sessions|log” > backup.sql
  3. mysqldump db_name –ignore-table db_name.sessions db_name.log > backup.sql
  4. mysqldump db_name –except-table=db_name.sessions –except-table=db_name.log > backup.sql

6,051 total views, 6 views today

  • Mysql Test

    What is the correct
    syntax to create a new user in MySQL?

    1 : CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;

    What is the full
    form of ACL?

    1 Access Control
    List

    What is the correct
    command to check if MySql service is running or not in RedHat?

    1 : service mysqld status

    What is the correct
    syntax to grant all privileges on all databases to a user?

    3: GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;

    What
    are the Privileges required if the read_only system variable is enabled, to
    explicitly start a transaction with START TRANSACTION READ WRITE?

    Super_priv

    Show_db_priv

    Create_tablespace_priv

    Execute_priv

    how do you set execution timeout for SELECT statements in MySQL?

    1: By setting the system variable ‘exec_time_out’ to an integer value

    2: There is a default value set in milliseconds that cannot be changed

    3: By setting the system variable ‘max_execution_time’ to an integer
    value

    4: The SELECT statements cannot have an execution timeout

    Which
    of the following will show when a table in a MySQL database was last updated?

    SELECT
    UPDATE_TIME

    FROM information_schema.tables

    WHERE TABLE_SCHEMA = ‘database_name’

    AND
    TABLE_NAME = ‘table_name’

    1: Creating an on-update trigger to write
    timestamp in a custom table, then querying the custom table

    2: Getting the "last
    modified" timestamp of the corresponding database file in the file
    system

    3: None of these.

    What is the correct syntax to get the
    MySQL Version?

    2: SELECT VERSION();

    What is
    the output when the following query executes?

    CREATE
    TABLE t2 (val INT) PARTITION BY LIST(val)( PARTITION mypart VALUES IN (1,3,5),
    PARTITION MyPart VALUES IN (2,4,6);

    2: Error.

    The maximum
    number of columns per index is

    4: 16

    Which statement is used to disable
    autocommit mode implicitly for a single series of statements?

    2:
    COMMIT

    3:
    ROLLBACK

    MyISAM tables can have three different
    storage formats: static (fixed-length), dynamic or compressed. Which of the
    following statements are true for the static format?

    4: It
    is easy to reconstruct after a crash, because rows are located in fixed
    positions.

    >Which of the following are valid
    MySQL functions? Check all that apply

    FORMAT()

    NOW()

    HOUR()

    DATEDIFFERENCE()

    Which of the following statement is
    valid for % and _ in the LIKE clause in a mysql query?

    % corresponds to 1 or more characters; _
    is exactly one character

    % corresponds to 0 or more characters; _
    is exactly one character

    % corresponds to 1 or more characters; _
    is 0 or more characters

    % is exactly one character; _ is
    corresponds to 0 or more characters

    What is
    the syntax to backup a database from command line?

    1: mysql [options] database_name
    > database_name.sql

    2:
    mysqldump [options] database_name > database_name.sql

    3: mysqlbackup [options] database_name
    > database_name.sql

    4: mysqldump [options] database_name
    >> database_name.sql

    How do you control the max size of a
    HEAP table?

    1:MySQL
    config variable called max_heap_table_size.

    2: MySQL config variable called
    max_heap_size.

    3: MySQL config variable called
    max_heap_table.

    4: None of the above

    How
    many Triggers are possible in MySQL?

    4:
    6

    Which of the following are correct about
    HEAP tables? Check all that apply

    HEAP
    tables are in-memory.

    Text fields are allowed within HEAP
    tables.

    HEAP
    tables are generally used for high-speed temporary storage.

    HEAP tables support AUTO_INCREMENT.

    What is the correct syntax to view the
    global event scheduler’s status in MySQL process list?

    1: SHOW
    PROCESSLISTG

    Which of the following is NOT a valid
    string type available for column?

    VAR

    With which of the following you can not
    associate a Trigger?

    1: Permanent tabe

    2:
    Temporary Table

    3: View

    InnoDB prevents which of the following
    operations when innodb_force_recovery is greater than 0?

    Note: There may be more than one right
    answer.

    INSERT, UPDATE, or DELETE

    What are the properties of transactions?

    4:
    Atomicity, Consistency, Durability, Isolation

    What is the default port for MySQL
    Server?

    How can you see all
    index in table ?

    How can you change “Martinez”
    into “Smith” in the “LastName” column in the Customers
    table?

    UPDATE Customers SET
    LastName=’Martinez’ INTO LastName=’Smith’

    MODIFY Customers SET
    LastName=’Smith’ WHERE LastName=’Martinez’

    UPDATE Customers SET
    LastName=’Smith’ WHERE LastName=’Martinez’

    MODIFY Customers SET
    LastName=’Martinez’ INTO LastName=’Smith’

    How can
    you see all indexes defined for a table?

    SHOW INDEX ;

    SHOW INDEX FROM ;

    SHOW *INDEX FROM ;

    None of the above.

    How would you return a random nickname from a very large “users”
    table?

    Like
    I normally do: SELECT nickname FROM users ORDER BY RAND() LIMIT 1;

    I would first run a
    query to get a total number of rows: SELECT COUNT(*) FROM users;. After that I
    would select a random number with the scripting language (e.g. mt_rand in case
    of PHP) and then perform a query like this: SELECT nickname FROM users LIMIT
    $random_number, 1;

    I would first run a
    query to get all the nicknames: SELECT nickname FROM USERS;. After that I would
    select a random nickname with the scripting language (e.g. mt_rand in case of
    PHP).

    What is the correct syntax to restore a database from command line?

    mysqlrestore -u
    username -p database_name < database_name.sql

    mysql -u username
    -p database_name database_name.sql

    mysql -u username
    -p database_name << database_name.sql

    mysql
    -u username -p database_name < database_name.sql

    Which
    of the following Storage Engines in MySQL supports Foreign Keys?

    MyISAM

    Memory

    InnoDB

    Archive

    The correct syntax to call a stored routine country_hos with a
    parameter of type VARCHAR is

    CALL country_hos('Europe');

    USE
    country_hos('Europe');

    SELECT
    country_hos('Europe');

    None of the
    mentioned

    What is the correct syntax to turn on the global event scheduler?

    SET
    GLOBAL event_scheduler = ON;

    SET GLOBAL
    event_scheduler = True;

    SET GLOBAL EVENT
    SCHEDULER = ON;

    SET GLOBAL EVENT
    SCHEDULER = True;

    Which of the following command is used to create a stored routine?

    CREATE ROUTINE

    CREATE PROCEDURE

    CREATE FUNCTION

    None of the
    mentioned

    Which of the following is not a valid comparison operator?

    >=

    <=

    ^=

    What is the correct command to check if MySql service is running or
    not in RedHat?

    service mysqld status

    mysql status

    mysqld status

    None of the
    mentioned

    How many bytes of storage does the BIGINT integer type requires?

    4

    8

    3

    2

    What are the valid modes of parameters in stored routines? Check all
    that apply.

    Note: There may be
    more than one right answer.

    IN

    OUT

    INOUT

    OUTIN

    Consider the
    following schema −

    CUSTOMERS(customer_code,
    first_name, last_name, email, phone_no, date_of_birth);

    Which of the
    following query would display all the customers where the second letter in the
    first name is ‘o’?

    SELECT first_name FROM Customers where first_name
    like ‘_o%’;

    SELECT first_name
    FROM Customers where first_name like ‘%o_’;

    SELECT first_name FROM
    Customers where first_name like ‘%o%’;

    SELECT first_name
    FROM Customers where first_name like ‘_o_’;

    Which of the
    following are valid encryption functions?

    Note: There may be
    more than one right answer.

    MD5

    AES_ENCRYPT

    SHA512

    SHA2

    With SQL, how can you return the number of records in the
    "Customers" table?

    SELECT
    COUNT(*) FROM Customers

    SELECT COLUMNS()
    FROM Customers

    SELECT COLUMNS(*)
    FROM Customers

    SELECT COUNT() FROM
    Customers

    What is the default TCP/IP port on which the MySQL server listens for
    connections?

    80

    8080

    3306

    556

    Which of the following is not a valid MyISAM table format?

    .FYI

    .frm

    .MYD

    .MYI

    How do you set execution timeout for SELECT statements in MySQL?

    By setting the
    system variable 'exec_time_out' to an integer value

    There is a default
    value set in milliseconds that cannot be changed

    By
    setting the system variable 'max_execution_time' to an integer value

    The SELECT
    statements cannot have an execution timeout

    What is the correct syntax to create a view?

    CREATE
    VIEW view_name AS

    SELECT
    column_name(s)

    FROM
    table_name

    WHERE
    condition

    CREATE VIEW
    view_name

    SELECT
    column_name(s)

    FROM table_name

    WHERE condition

    CREATE VIEW
    view_name AS

    USING
    column_name(s)

    FROM table_name

    WHERE condition

    None of the above

    Which of the following can be used to check if a column in a row of a
    table is NULL?

    IS
    NULL

    =

    LIKE

    What is the default maximum number of indexes per MyISAM table?

    There is no limit
    to that

    64

    32

    16

    How can a default value be set for a MySQL Datetime column?

    By using TIMESTAMP
    (default_value TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

    By using DATE
    (default_value DATE DEFAULT CURRENT_DATE)

    By using
    DATEDEFAULT (default_value SET DATEDEFAULT)

    By using DATETIME (default_value DATETIME DEFAULT
    CURRENT_DATETIME)

    Select
    valid operators in MySQL:

    Note: There may be more than one right answer.

    ==

    !=

    ||

    CASE

    ++

    Which of the following is not a valid statement?

    Double
    have accuracy up to eight place whereas float upto 18 places

    Floating point
    numbers are stored in FLOAT whereas Double are stored in DOUBLE.

    Float takes 4 bytes
    whereas DOUBLE takes eight bytes.

    FLOAT is for
    single-precision whereas DOUBLE is for double-precision numbers.

    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

    Use
    INSERT … ON DUPLICATE KEY UPDATE statement

    Use ADD UNIQUE
    statement

    Use REPLACE
    statement

    Select valid date and time functions in MySQL:

    Note: There may be more than one right answer.

    GMT_TIME

    DAYOFWEEK

    STR_TO_MONTH

    UTC_DATE

    YEARWEEK

    Which of the following will show when a table in a MySQL database was
    last updated?

    Note: There may be more than one right answer.

    Using the following
    query:

    SELECT
    UPDATE_TIME

    FROM information_schema.tables

    WHERE TABLE_SCHEMA = ‘database_name’

    AND
    TABLE_NAME = ‘table_name’

    Creating an
    on-update trigger to write timestamp in a custom table, then querying the
    custom table

    Getting the
    “last modified” timestamp of the corresponding database file in the
    file system

    None of these.

    ENUM column can have a maximum of _____ distinct elements.

    65,535

    64

    64,535

    65

    Which of the following is not a
    valid nonstandard string types?

    TINYTEXT

    BLOBTEXT

    MEDIUMTEXT

    LONGTEXT

    How many Auto Increment columns can be present per table in MySQL?

    1

    5

    As
    per the max value set in environment variables

    There is no upper
    limit on the number of Auto Increment colums

    What are the valid types of
    table present in MySQL? Check all that apply.

    Note: There may be more than one right answer.

    MyISAM

    Heap

    Merge

    INNO
    DB

    ISAM

    Which of the statements are
    true for user-defined variables?

    Note: There may be more than one right answer.

    User variable names
    are not case sensitive and have a maximum length of 64 characters.

    User
    variables are written as $var_name, where the variable name var_name consists
    of alphanumeric characters, “.”, “_”, and “@”.

    User-defined
    variables are session-specific. A user variable defined by one client cannot be
    seen or used by other clients.

    Select valid spatial functions:

    Note: There may be
    more than one right answer.

    Point

    MBRMultiPoint

    ST_Distance

    ST_LineString

    Polygon

    How will you get the field
    names and associated type of a MySQL table?

    show schema
    table_name;

    describe
    table_name;

    schema table_name;

    show details
    table_name;

  • Mysql Test

    I have all answers