MySQL Test Questions Answers Linkedin Assessment

0

MySQL Test Questions Answers Linkedin Assessment



Advanced MySQL Questions given here should be practiced before taking any assessment examinations. This MCQ will help you to crack any MySql tests.

1. In the diagram below, the price field is declared as type DECIMAL. What would be a more efficient declaration for this field?

  • FLOAT
  • DECIMAL(10,2)
  • NUMERIC
  • DOUBLE

2. How would you retrieve data on all the customers where no phone number is stored?

  • SELECT * FROM customers WHERE PhoneNumber = NULL;
  • SELECT * FROM customers WHERE PhoneNumber IS NOT VALID;
  • SELECT * FROM customers WHERE PhoneNumber IS NULL;
  • SELECT * FROM customers WHERE PhoneNumber IS UNKNOWN;

3. Explain the security aspect of stored procedures

  • Stored procedures are not secure, because they can be executed from the command line as the root user
  • Stored procedures are secure, because the owner of the stored procedure can decide to whom access is granted
  • Stored procedures are secure, because applications can be given access to stored procedures and not any underlying variables
  • Stored procedures are not secure, because they can execute statements to drop tables or bulk delete data

4. What is the equivalent of the mysqladmin reload command?

  • mysqladmin flush-threads
  • mysqladmin flush-tables
  • mysqladmin flush-privileges
  • mysqladmin flush-all

5. Which statement is true about TIMESTAMP and DATETIME data types?

  • TIMESTAMP values require more bytes for storage than DATETIME values.
  • TIMESTAMP is stored without timezone, and DATETIME is stored in UTC values.
  • TIMESTAMP and DATETIME are both stored without time zone.
  • TIMESTAMP is stored in UTC values, and DATETIME is stored in without time zone.

6. Which command will return a list of triggers in the current database?

  • DISPLAY TRIGGERS;
  • SHOW TRIGGERS;
  • SELECT ALL TRIGGERS;
  • SELECT * FROM information_schema.triggers;

7. What is the maximum number of columns that can be used by a single table index?

  • 2
  • 4
  • 8
  • 16

8. Which is a valid constructor for a class named User?

  • public construct User() {}
  • public User() {}
  • public instance User() {}
  • public init User() {}

9. What is the advantage of using a temporary table instead of a heap table?

  • The temporary table will be dropped when the database is restarted.
  • Temporary tables can be shared among clients, which makes them more usable in group development environments.
  • The temporary table will be dropped as soon as your session disconnects.
  • Creating a temporary table does not require any special privileges.

10. Which choice is not a valid model for a stored procedure parameter?

  • INOUT
  • IN
  • OUT
  • IN OUT

11. One form of backup, replication, enables you to maintain identical data on multiple servers, as a ______ configuration.

  • remote-local
  • parent-child
  • master-slave
  • logical-physical

12. You need to export the data in the customers table into a CSV file, with columns headers in the first row. Which clause do you add to your MySQL command?

  • JOIN
  • WITH HEADERS
  • UNION
  • WITH COLUMNS

13. After installing MySQL, it may be necessary to initialize the _ which may be done automatically with some MySQL installation methods.

  • storage engine
  • user accounts
  • grant tables
  • data directory

14. MySQL option files provide a way to specify commonly used options so that they need not be entered on the command line each time you run a program. What is another name for the option files?

  • variable settings
  • configuration files
  • help files
  • default settings

15. In an efficiently designed relational database, what does every table have?

  • set of triggers
  • sequential id field
  • minimum of three columns
  • primary key

16. How do you select every row in a given table named "inventory"?

  • SELECT all FROM inventory;
  • FROM inventory SELECT all;
  • FROM inventory SELECT *;
  • SELECT * FROM inventory;

17. What is the difference between DROP and TRUNCATE?

  • They both refer to the same operation of deleting the table completely.
  • They both refer to the same operation of clearing the table, but keeping its definition intact.
  • TRUNCATE deletes table completely, removing its definition as well. DROP clears the table but does not delete the definition.

DROP deletes table completely, removing its definition as well. TRUNCATE clears the table but does not delete the definition.

18. How can you filter duplicate data while retrieving records from a table?

  • DISTINCT
  • WHERE
  • LIMIT
  • AS

19. What is the default port for MySQL Server?

  • 25
  • 990
  • 0
  • 3306

20. You are working with very large tables in your database. Which SQL clause do you use to prevent exceedingly large query results?

  • UNIQUE
  • LIMIT
  • DISTINCT
  • CONSTRAINT

21. If you need to order a table of movies by name, which query will work?

  • SELECT * FROM movies GROUP BY name
  • SELECT * FROM movies ORDER BY name
  • SELECT * FROM movies ORDER TABLE by name
  • SELECT * FROM movies FILTER BY name

22. You are importing data as JSON into a new table. You run CREATE TABLE json_data ( city JSON ); and insert rows into this table. What is the correct syntax to see the list of cities?

  • SELECT city FROM json_data;
  • SELECT city->>'$.name' city FROM json_data;
  • SELECT city.name city FROM json_data;
  • SELECT city->'$.name' city FROM json_data;

23. You need to restore a MySQL database from a backup file. Which command-line tool do you use for the actual data import, after re-creating the database?

  • mysqld
  • mysql
  • mysqladmin
  • mysqldump

24. Which type of backup includes all the changes made to the data since the last full backup was performed?

  • snapshot
  • logical
  • differential
  • incremental

25. When working with MySQL cursor, what must you also declare?

  • DEFAULT value
  • RETURN variable
  • SQLEXCEPTION routine
  • NOT FOUND handler

26. In data migration, there is often a need to delete duplicate rows as part of data cleanup. Which statement works best?

  • DELETE DUPS
  • DELETE DISTINCT
  • DELETE JOIN
  • DELETE WITH

27. Which statement about the TRUNCATE TABLE statement is true?

  • It will stop and issue an error when it encounters a row that is referenced by a row in a child table.
  • It always first drops, then re-creates a new table.
  • It deletes rows one by one on tables with foreign key constraints.
  • It does not invoke the DELETE triggers associated with the table.

28. Inside a transaction, several operations need to be performed. What would you do if an exception happens during that transaction?

  • UNDO
  • UNCOMMIT
  • ROLLBACK
  • REVERSE

29. What is one reason to introduce data redundancy into a normalized database design?

  • to reduce corruption in data
  • to reduce storage space
  • to make the system faster
  • to prevent data anomalies

30. In which table does MySQL store passwords for user accounts?

  • mysql.accounts;
  • mysql.passwords;
  • mysql.admin;
  • mysql.user;

31. This diagram shows what type of relationship between customers and cars?

  • one-to-many
  • parent-child
  • many-to-many
  • many-to-one

32. A stored routine is a set of SQL statements stored on the server and takes form as either a procedure or a function. Which statement cannot be used inside stored routines?

  • SELECT
  • USE
  • SET
  • DECLARE

33. When a new student is added to a new database, you want new records to be created in the related tables such as Exam, Score and Attendance. How would you accomplish this?

  • trigger
  • regular expression
  • view
  • index

34. Which choice is NOT a statement you would use to filter data?

  • GROUP BY
  • WHERE
  • LIMIT
  • LIKE

35. Why would you use a common table expression (CTE)?

  • To define queries for later reuse for the duration of the current session
  • To create temporary tables that can be used to pre-select often-used result sets.
  • To calculate a new single value from a result set and return it to the query parser.
  • To break down complex queries and allow reuse within a query.

36. Which option modifier tells a program not to exit with an error if it does not recognize the option, but instead to issue a warning?

  • --verbose
  • --skip
  • --skip-error
  • --loose

37. What does this SQL statement return?

SELECT name FROM students WHERE name REGEXP '^to';

  • all names starting with "to," such as Tommy or Tony
  • all names with "to," such as Roberto and Tommy
  • all names without "to," such as Samantha or Kathryn
  • all names ending with "to," such as Roberto

38. You are loading data into a table. Which command can you use to make sure that all data is inserted and duplicated rows are discarded?

  • INSERT IGNORE
  • INSERT UNIQUE
  • INSERT INTO
  • INSERT DISTINCT

39. The left and right joins are also known as ________

  • Inner Join
  • Natural Join
  • Outer Join
  • Cartesian Join

40. What is the valid way to create a database view in MySQL?

  • CREATE VIEW v1 SELECT * FROM t1 WHERE col1 > 10;
  • CREATE VIEW v1 AS BEGIN SELECT * FROM t1 END;
  • CREATE VIEW v1 BEGIN SELECT * FROM t1 END;
  • CREATE VIEW v1 AS SELECT * FROM t1;

41. Which choice is not one of the table maintenance statements?

  • CHECK TABLE;
  • ANALYZE TABLE;
  • CREATE TABLE;
  • OPTIMIZE TABLE;

42. How would you list the full set of tables in the currently selected database?

  • SELECT * FROM DATABASE;
  • SHOW TABLES;
  • LIST TABLES;
  • SELECT ALL TABLES;

43. How can you list all columns for a given table?

  • SHOW table COLUMNS;
  • SHOW COLUMNS FROM table;
  • LIST table COLUMNS;
  • SELECT COLUMNS FROM table;

44. What is the MySQL perror command-line utility used for?

  • to display your version of MySQL
  • to display operating system error codes
  • to display default settings that are in error
  • to display storage error codes

45. Which choice is not a processing algorithm for database views?

  • merge
  • updatable
  • temptable
  • undefined

46. You need to run a complex query with recursive subqueries, but without creating a stored procedure or a function. Which command or clause do you use?

  • WITH
  • COLLATE
  • UNION
  • FULL JOIN

47. When you have a subquery inside of the main query, which query is executed first?

  • The subquery is never executed. Only the main query is executed.
  • They are executed at the same time
  • the main query
  • the subquery

48. You need to export the entire database, including the database objects, in addition to the data. Which command-line tool do you use?

  • mysqldump
  • mysqlexport
  • mysqladmin
  • mysqld

49. You must ensure the accuracy and reliability of the data in your database. You assign some constraints to limit the type of data that can go into a table. What type of constraints are you assigning?

  • row level
  • database level
  • function level
  • column level

50. Which option of most MySQL command-line programs can be used to get a description of the program's different options?

  • --options
  • ?
  • --help
  • -h

51. MySQL uses environment variables in some of the programs and command-line operations. Which variable is used by the shell to find MySQL programs?

  • DIR
  • HOME
  • PATH
  • MYSQL_HOME

52. If you were building a table schema to store student grades as a letter (A, B, C, D, or F) which column type would be the best choice?

  • VARCHAR
  • ENUM
  • OTEXT
  • LONGTEXT

53. What is the product of the database designing phase?

  • system definition
  • logical model
  • physical model
  • normalized database

54. MySQL server can operate in different SQL modes, depending on the value of the sql_mode system variable. Which mode changes syntax and behavior to conform more closely to standard SQL?

  • TRADITIONAL
  • ANSI
  • MSSQL
  • STRICT

55. MySQL programs are a set of command-line utilities that are provided with typical MySQL distributions. MySQL is designed to be a database.

  • database and programming
  • user and administrator
  • client and server
  • syntax and objects

56. Which MySQL command shows the structure of a table?

  • INFO table;
  • SHOW table;
  • STRUCTURE table;
  • DESCRIBE table;

57. MySQL uses security based on _ for all connections, queries, and other operations that users can attempt to perform.

  • administrator schema
  • access control lists
  • encrypted algorithms
  • user settings

58. Which MySQL command modifies data records in a table?

  • UPDATE
  • MODIFY
  • CHANGE
  • ALTER

59. What is the best type of query for validating the format of an email address in a MySQL table?

  • a SQL query using partitions
  • a SQL query using a regular expression
  • a SQL query using IS NULL
  • a SQL query using LTRIM Or RTRIM

60. In MySQL, queries are always followed by what character?

  • line break
  • colon
  • semicolon
  • period

61. In SELECT * FROM clients; what does clients represent?

  • a SQL query
  • a SQL statement
  • a database
  • a table

62. How does MySQL differ from SQL?

  • SQL is a standard language for retrieving and manipulating data from structured databases. MySQL is a nonrelational database management system that is used to manage SQL databases.
  • SQL is a standard language for retrieving and manipulating data from structured databases. MySQL is a relational database management system that is used to manage SQL databases.
  • They are not different. MySQL and SQL refer to the same thing.
  • My SQL is a language, and SQL is a software application.

63. A trigger is a database object that is associated with a table, and that activates when a particular event occurs for the table. Which three events are these?

  • CREATE, ALTER, DROP
  • OPEN, FETCH, CLOSE
  • INSERT, UPDATE, DELETE
  • DECLARE, SET, SELECT

64. What is the requirement for using a subquery in the SELECT clause?

  • the subquery must use an aggregate function.
  • the subquery must refer to the same table as the main query.
  • the subquery must return a single value.
  • the subquery must return at least one value.

65. Each time MySQL is upgraded, it is best to execute mysql_upgrade, which looks for incompatibilities with the upgraded MySQL server. What does this command do, upon finding a table with a possible incompatibility?

  • it performs a table check and, if problems are found, attempts a table repair.
  • it stops and notifies the server administrator that the upgrade cannot complete until the incompatibility issue are resolved.
  • it provides a full report of the table specifications and the incompatibilities to the server administrator.
  • it performs a table check and, if problems are found, displays the information for the server administrator to take action.

66. What mysql statement is used to check which accounts have specific privileges?

  • show grants (displays the privileges and roles that are assigned to a MySQL user account or role
  • show privileges (shows the list of system privileges that the MySQL server supports
  • show access
  • show user permissions

67. What table cannot have a trigger associated with it?

  • temporary
  • system
  • large
  • new

68. later versions of mysql support the native json data type for storing json documents. What is a drawback of json columns?

  • inefficient for storing json documents
  • cannot be indexed directly
  • documents cannot be validated when stored in json columns
  • cannot be normalized
  • 69. Which is the correct syntax of an extended insert statement?
  • insert into cars (make, model, year) values ('Ford', 'Mustang', 2002) ('Mercedes', 'C', 2003)
  • insert into cars (make, model, year) values ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)
  • insert into cars (make, model, year) values ('Ford', 'Mustang', 2002) values ('Mercedes', 'C', 2003)
  • insert into cars (make, model, year) extended ('Ford', 'Mustang', 2002), ('Mercedes', 'C', 2003)

70. You need to make an exact copy of a table, with all columns and indexes. How can you get all of the information needed to accomplish this?

  • create table
  • show create table
  • clone table
  • insert into

71. you need to make your mysql system secure against attackers. What are you not supposed to do?

  • Grant PROCESS or SUPER privilege to other users.
  • Run MySQL server as the unix root user.
  • Run MySQL server as a normal user.
  • Use the compressed protocol.

72. You manage a database with a table "customers". You created a temporary table also called "customers" with which you are working for the duration of your session. You need to recreate the temporary table with different specs. Which command do you need to run first?

  • create temporary table customers;
  • drop temporary table customers;
  • drop temp table customers;
  • drop table customers;

73. What function finds the current time or date in MySQL?

  • DATE()
  • GETDATE()
  • CURDATE()
  • CURRENT()

74. What is the correct usage of ENUM in MySQL?

  • Create table size (ENUM ('Small','Medium','Large'));
  • Create table size (name ENUM('Small','Medium','Large'));
  • Create table ENUM (name ('Small','Medium','Large'));
  • Create table size (name: ENUM['Small','Medium','Large']);

75. The mysqldump command cannot generate output in _.

  • CSV
  • JSON
  • XML
  • TXT

76. Management has requested that you build an employee database. You need to include each employee's current position and salary, as well as all prior positions and salaries with the company. You decide to use a one-to-many structure: an employee table with the main information such as name and address, and an employment table with position and salary history. You can use the employeeID field to connect them. What is employment.employeeID an example of?

  • foreign key;
  • primary key;
  • secondary key;
  • alternate key;

77. If you want to use MyISAM instead of InnoDB, which option do you need to specify in the CREATE TABLE statement?

  • ENGINE
  • PARTITION
  • STORAGE
  • TABLESPACE

78. Which query lists the databases on the current server?

  • LIST ALL DATABASES;
  • SHOW DATABASES;
  • LIST DATABASES;
  • SHOW DB;

79. What is the product of the database designing phase?

  • all tables, columns, data types, indexes and their relationships
  • a list of entities, their relationship, constraints, data types, and cardinalities
  • a list of entities, their relationship, and constraints
  • all tables and their names, which are needed to implement the logical model

80. Which choice is not an available string type for a column?

  • ENUM
  • BIT
  • SET
  • CHAR

81. What does the following SQL statement return?

SELECT * FROM Employees WHERE EmployeeName LIKE 'a%'

  • It records in the Employees table where the value in the EmployeeName column has an "a".
  • It records in the Employees table where the value in the EmployeeName column ends with "a".
  • It records in the Employees table where the value in the EmployeeName column doesn't have an "a".
  • It records in the Employees table where the value in the EmployeeName column starts with "a".

82. How are permissions implemented in MySQL?

  • access control lists
  • encrypted algorithms
  • user settings
  • administrator schema

83. How can you remove a record using MySQL?

  • REMOVE
  • REMOVE FROM
  • DELETE
  • DELETE FROM
You may like to learn

Linkedin Python Test  Question Answers 








 



Tags:
Post a Comment (0)
Previous Post Next Post