MySQL Test – Upwork SEO Test Answer, WordPress | Punjab State Lottery lohri Bumper 2019 Result Updates

MySQL Test 2017

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

Answers:

Indexes can have NULL values
BLOB and TEXT columns can be indexed
Indexes per table cannot be more than 16
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:

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

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

Answers:

He cannot rename any MySQL database
By using: RENAME DATABASE db_old_name TO db_new_name
By using: RENAME DATABASE TO db_new_name
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:

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:

select authorname from authors where authorid in (select authorid from books where popularityrating<5)
select authorname from authors where authorid in (select authorid from books where popularityrating<=5)
select authorname from authors where authorid in (select bookid from books where popularityrating<5)
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:

Closing any open tables in the table cache
Closing open connections
Flushing the log file
Flushing the host cache

7. Consider the query:

SELECT name

FROM Students

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

Answers:

Names starting with “a”
Names containing “a” as the second lette
Names starting with “a” or “A”
Names containing “a” as any letter except the first

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

Answers:

SMALLINT
DECIMAL(19,4)
VARCHAR(32)
BIGINT

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

Answers:

GEOMETRY
CIRCLE
SQUARE
POINT
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:

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

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

Answers:

SIGNAL
RAISE
ERROR
None of these.

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

Answers:

SELECT * FROM fiberbox WHERE field REGEXP ‘Pizza|Burger|Hotdog’;
SELECT * FROM fiberbox WHERE field LIKE ‘%Pizza%’ OR field LIKE ‘%Burger%’ OR field LIKE ‘%Hotdog%’;
SELECT * FROM fiberbox WHERE field = ‘%Pizza%’ OR field = ‘%Burger%’ OR field = ‘%Hotdog%’;
SELECT * FROM fiberbox WHERE field = ‘?Pizza?’ OR field = ‘?Burger?’ OR field = ‘?Hotdog?’;

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

Answers:

BLOB
BIGINT
INT
Both BLOB and BIGINT

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

Answers:

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

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

Answers:

ALTER TABLE table_name MODIFY column_name varchar(255) null
ALTER TABLE table_name MODIFY column_name VARCHAR(255)
ALTER TABLE table_name CHANGE column_name column_name type DEFAULT NULL
ALTER table_name MODIFY column_name varchar(255) null

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

Answers:

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

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

Answers:

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

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

Answers:

Using subqueries
Using variables in a MySQL query
Using self-join
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:

select subject,count(*) as Books from books,subjects where books.popularityrating > 7
select subject,count(*) as Books from books,subjects where books.authorid=subjects.authorid and books.popularityrating > 7 group by subjects.subject
select subject,count(*) as Books from books,subjects where books.subjectid=subjects.subjectid and books.popularityrating = 7 group by subjects.subject
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:

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

The ‘IN’ clause
Using temporary tables and inline views
The ‘INNER JOIN’ clause
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:

select item, brand, price from items where max(price) order by item
select * from items where price = max group by item
select item, brand, max(price) from items group by item
select * from items where price > 200 order by item

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

Answers:

mysql -u<user> -p < db_backup.dump
mysql -u<user> -p<password> < db_backup.dump
mysql -u<user> -p <password> < db_backup.dump
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:

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.

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

Answers:

“EXPERTRATING” LIKE “EXP%”
“EXPERTRATING” LIKE “Exp%”
BINARY “EXPERTRATING” LIKE “EXP%”
BINARY “EXPERTRATING” LIKE “Exp%”
All will result in 1 (true)

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

Answers:

MySQL
SQLite
Both
None

27. What is true about the ENUM data type?

Answers:

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

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

Answers:

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

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

Answers:

mysqldump –single-transaction db_name
mysqldump –force db_name
mysqldump –quick db_name
mysqldump –no-tablespaces db_name

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

Answers:

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

31. Consider the following select statement and its output:

SELECT * FROM table1 ORDER BY column1;
Column1

——–

3
Given the above output, which one of the following commands deletes 3 of the 5 rows where column1 equals 2?

Answers:

DELETE FIRST 4 FROM table1 WHERE column1=2
DELETE 4 FROM table1 WHERE column1=2
DELETE WHERE column1=2 LIMIT 4
DELETE FROM table1 WHERE column1=2 LIMIT 3
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:

Two tables are created
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
Those queries are invalid
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:

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

34. Which of the following statements are true?

Answers:

Names of databases, tables and columns can be up to 64 characters in length
Alias names can be up to 255 characters in length
Names of databases, tables and columns can be up to 256 characters in length
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:

CHANGE TABLE
MODIFY TABLE
ALTER TABLE
UPDATE TABLE

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

Answers:

The function returns no value
The function always returns the same value for the same input
The function returns the input value
None of the above

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

Answers:

GRANT ALL ON testdb.* TO peter PASSWORD ‘Software’
GRANT ALL ON testdb.* TO peter IDENTIFIED by ‘Software’
GRANT ALL OF testdb.* TO peter PASSWORD ‘Software’
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:

EMPNO, unique value of ENAME and then SALARY are displayed
EMPNO, unique value ENAME and unique value of SALARY are displayed
DISTINCT is not a valid keyword in SQL
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:

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

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

Answers:

No limit
OS specific
65,534
2’147’483’648
128

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

Answers:

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

Answers:

DD-MM-YYYY
YYYY-DD-MM
YYYY-MM-DD
MM-DD-YY
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:

True
False

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

Answers:

ndb_config
cluster_config
ndb –config
configNd
None of the above

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

Answers:

Table Name
Database name
LIMIT clause
WHERE clause

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

Answers:

BIGINT
LARGEINT
SMALLINT
DOUBLE
DECIMAL

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

Answers:

JOIN queries are faster than WHERE queries.
WHERE queries are faster than JOIN queries.
INNER queries are faster than JOIN queries.
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:

Nothing
The id column cannot be auto incremented because it has not been defined as a primary key
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:

ALTER TABLE table1 ADD column2a INT AFTER column2
MODIFY TABLE table1 ADD column2a AFTER column2
INSERT INTO table1 column2a AS INT AFTER column2
ALTER TABLE table1 INSERT column2a INT AFTER column2
CHANGE TABLE table1 INSERT column2a BEFORE column3
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:

SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
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:

UPDATE
CREATE ROUTINE
SELECT
CREATE FUNCTION
No specific privilege

52. What is wrong with the following query:

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

Answers:

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

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

Answers:

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
Edit the MySQL config file (mysql.con) and add the following line log = /var/log/mysql/mysql.log
Execute VIEW .mysql_history
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:

Select * from Products order by CurrentStock,ProductGroup
Select * from Products order by CurrentStock DESC,ProductGroup
Select * from Products order by ProductGroup,CurrentStock
Select * from Products order by ProductGroup,CurrentStock DESC
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:

SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
SELECT * FROM Persons SORT REVERSE ‘FirstName’
SELECT * FROM Persons ORDER BY -‘FirstName’
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:

SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ‘2002-11-11’ ORDER BY purchase_date;
SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date< ‘2002-11-11’ ORDER BY purchase date ASC;
SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
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:

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

Select course from students where count(course) > 5;
Select course from students where count(*) > 5 group by course;
Select course from students group by course;
Select course from students group by course having count(*) > 5;
Select course from students group by course where count(*) > 5;
Select course from students where count(group(course)) > 5;
Select count(course) > 5 from students;
None of the above

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

Answers:

TINYINT
MEDIUMINT
SMALLINT
INT
BIGINT

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

Answers:

SELECT column_duplicated, sum(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
SELECT column_duplicated, COUNT(*) amount FROM table_name WHERE amount > 1 GROUP BY column_duplicated
SELECT column_duplicated, sum(*) amount FROM table_name GROUP BY column_duplicated HAVING amount > 1
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:

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

Answers:

SHOW TABLES
DESCRIBE TABLES
SHOW ALL TABLES
LIST TABLES

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

Answers:

ENUMERATE
EXPLAIN
KILL
LOAD DATA
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:

SELECT ID FROM ( SELECT ID AS SECOND_ID, name FROM ( SELECT * FROM employee ) );
SELECT ID FROM ( SELECT ID, name AS NAME FROM ( SELECT * FROM employee ) );
SELECT ID FROM ( SELECT ID, name FROM ( SELECT * FROM employee ) AS T ) AS T;
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:

InnoDB
MYISAM
BLACKHOLE
STACK

66. The REPLACE statement is:

Answers:

Same as the INSERT statement
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
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:

NULL
An error will be generated
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:

Use MERGE statement
Use INSERT … ON DUPLICATE KEY UPDATE statement
Use ADD UNIQUE statement
Use REPLACE statement

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

Answers:

UPDATE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
UPDATE Persons SET LastName = ‘Hansen’ INTO LastName = ‘Nilsen’
SAVE Persons SET LastName = ‘Nilsen’ WHERE LastName = ‘Hansen’
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:

SELECT * FROM myTable WHERE column1 IS NULL
SELECT * FROM myTable WHERE column1 = NULL
SELECT * FROM myTable WHERE column1 EQUALS NULL
SELECT * FROM myTable WHERE column1 NOT NULL
SELECT * FROM myTable WHERE column1 CONTAINS NULL

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

Answers:

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

Answers:

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

73.Which of the following statements is true?

Answers:

Replication can be a part of a load balancing strategy.
Replication and clustering are complete synonyms in the context of MySQL server administration.
MySQL supports only master-slave replication.
MySQL does not support master-master replication.

74. Which of the following is not a valid Arithmetic operator?

Answers:

+
–
*
\
%
All are valid

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

Answers:

GEOMETRY
CIRCLE
SQUARE
POINT
POLYGON

76. Which of the following statements relating to Alias names is true?

Answers:

Alias names are case sensitive
Alias names are case sensitive
Alias names are case in-sensitive
Alias names are case sensitive on UNIX and not on Windows
Alias names are case sensitive on Windows and not on UNIX
Alias names case sensitivity depends on lower_case_table_names system setting

77. Examine the description of the STUDENTS table:

Answers:
STD_ID INT
COURSE_ID VARCHAR (10)
START_DATE DATE
END_DATE DATE
The aggregate functions valid on the START_DATE column are:

SUM(start_date)
AVG(start_date)
COUNT(start_date)
AVG(start_date, end_date)
AVG(start_date, end_date)
MIN(start_date)

78. Which of the following is the correct way to change the character set to UTF8?

Answers:
STD_ID INT
COURSE_ID VARCHAR (10)
START_DATE DATE
END_DATE DATE
The aggregate functions valid on the START_DATE column are:

Add the following lines in my.cnf:

default-character-set=utf8

[mysqld]

dd the following lines in my.cnf:
[mysqld]
character_set_server = utf8
Add the following lines in my.cnf:
[mysqld]
skip-character-set-client-handshake
character_set_client=utf8
character_set_server=utf8
AVG(start_date, end_date)
AVG(start_date, end_date)
MIN(start_date)

78. To quote a string within a string, which of the following can you use?

Answers:
STD_ID INT
COURSE_ID VARCHAR (10)
START_DATE DATE
END_DATE DATE
The aggregate functions valid on the START_DATE column are:

«This is the «quoted» message»
«This is the «»quoted»» message»
‘This is the «quoted» message’
«This is the \»quoted\» message»

79.SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000;
Which of the following statements is correct with regard to this code?
Answers:

It returns employees whose salary is 50% more than $23,000
It returns employees who have 50% commission rate or salary greater than $23,000
It returns employees whose salary is 50% less than $23,000
None of the above

80.Select which of the following is the best way to duplicate MySql Database(DB1) in to another database(DB2) without using mysqldump?

Answers:

Create the target database using MySQLAdmin,
Execute the following MySql query CREATE TABLE t2 SELECT * FROM t1;
Execute the following MySql query CREATE TABLE x LIKE other_db.y;
CREATE TABLE DB2 SELECT * FROM DB1;

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

Answers:

SELECT «EXPERTRATING» LIKE «EXP%»
SELECT «EXPERTRATING» LIKE «Exp%»
SELECT BINARY «EXPERTRATING» LIKE «EXP%»
SELECT BINARY «EXPERTRATING» LIKE «Exp%»
All will result in 1 (true)

82.Which query will display data from the Pers table relating to Analyst, Clerk and Salesman who joined between 1/1/2005 and 1/2/2005 ?

Answers:

select * from Pers where joining_date from #1/1/2005# to #1/2/2005#, job=Analyst or clerk or salesman
select * from Pers where joining_date between #1/1/2005# to #1/2/2005#, job=Analyst or job=clerk or job=salesm
select * from Pers where joining_date between #1/1/2005# and #1/2/2005# and
None of the above

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

Answers:

mysqldump –single-transaction db_name
mysqldump –force db_name
mysqldump –quick db_name
mysqldump –no-tablespaces db_name

85. Which of the following is not a valid Bit operator?

Answers:

86. What is the main purpose of InnoDB over MyISAM?

Answers:

InnoDB is thread safe
InnoDB provides a transaction safe environment
InnoDB can handle table with more than 1000 columns
InnoDB provides FULLTEXT indexes
None of the above

87. View the following Create statement:
1. Create table Pers
2. (EmpNo Int not null,
3. EName Char not null,
4. Join_dt Date not null,
5. Pay Int)
Answers:

88.What will happen if some of the columns in a table are of char datatype and others are of varchar datatype?

Answers:

Nothing will happen
MySQL will generate an error
MySQL will convert all varchar datatypes into char
MySQL will convert all char datatypes into varchar

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

Answers:

0000
1900
2000
Ambiguous, cannot be determined

90.Which of the following statements is true is regards to whether the schema integration problem between development, test, and production servers can be solved?

Answers:

True, only can create migration solution in .NET programming language.
True, it can be solve by migration solution. These solutions vary by programming language.
Both A and B
None of the above

91. Which of the following is not a valid Logical operator?

Answers:

92. What is true regarding the TIMESTAMP data type?

Answers:

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value
TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp
When the server runs with the MAXDB SQL mode enabled, TIMESTAMP is identical with DATETIME
A TIMESTAMP column cannot have a default value
None of the above is true

93. Which of the following is not a SQL operator?

Answers:

Between..and..
Like
In
Is null
Not in
All of the above are SQL operators

94. Which of the following is not a SQL operator?

Answers:

Between..and..
Like
In
Is null
Not in
All of the above are SQL operators

95.Which of the following is not a valid Comparison operator?

Answers:

==
<=>
!=
<>
REGEXP
All of the above are SQL operators

96.What will happen if you write the following statement on the MySQL prompt?
SELECT NOW();

Answers:

It will display the current date
It will display the error message as now does not exist.
It will display a syntax error near ‘()’

97.Assuming the column col1 in table tab1 has the following values:
2,3,NULL,2,3,1
What will be the output of the select statement below?
SELECT count(col1) FROM tab1

Answers:

98. 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»?
CREATE TABLE `Persons` (
`LastName` varchar(244) NOT NULL DEFAULT »
) ENGINE=InnoDB;
REPLACE INTO Persons VALUE (‘Hansen’),(‘Pettersen’),(‘Nilsen’),(‘Smith’);

Answers:

SELECT * FROM Persons WHERE LastName > ‘Hansen’, LastName <‘Pettersen’
SELECT LastName > ‘Hansen’ AND LastName < ‘Pettersen’ FROM Persons
SELECT * FROM persons WHERE LastName > ‘Hansen’ AND LastName > ‘Pettersen’
SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’

99.Which of the following statements is correct in regards to the syntax of the code below?
SELECT table1.this, table2.that, table2.somethingelse
FROM table1
INNER JOIN table2 ON table1.foreignkey = table2.primarykey
WHERE (some other conditions)

Answers:

Using the older syntax is more subject to error. If use inner joins without an ON clause, will get a syntax error.
INNER JOIN is ANSI syntax. It is generally considered more readable, especially when joining lots of tables. It can also be easily replaced with an OUTER JOIN whenever a need arises
(INNER JOIN) ON will filter the data before applying WHERE clause. The subsequent join conditions will be executed with filtered data which makes better performance. After that only WHERE condition will apply filter conditions.
All of the Above

100.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;

Answers:

101.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?
Answers:

SELECT MAX(GPA) FROM STUDENT_GRADES WHERE GPA IS NOT NULL
SELECT GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
SELECT MAX(GPA) FROM STUDENT_GRADES GROUP BY SEMESTER_END
SELECT TOP 1 GPA FROM STUDENT_GRADES GROUP BY SEMESTER_END
None of the above

102.Which of the following queries is valid?
Answers:

Select * from students where marks > avg(marks);
Select * from students order by marks where subject = ‘SQL’;
Select * from students having subject =’SQL’;
Select name from students group by subject, name;
Select group(*) from students;
Select name,avg(marks) from students;
None of the above

103.Which of the following are aggregate functions in MySQL?
Answers:

Avg
Select
Order By
Sum
Union
Group by
Having

Tag: MySQL Test

Upwork MySQL Test latest

MySQL Test 2017