linkedin-skill-assessments-quizzes

MySQL

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

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

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

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

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

Q6. How can you create a stored procedure in MySQL?

1 CREATE PROCEDURE P () AS
2 BEGIN
3 END;
1 CREATE PROCEDURE P ()
2 BEGIN
3 END
1 CREATE PROCP
2 BEGIN
3 END;
1 CREATE PROC P AS O
2 BEGIN
3 END;

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

Q8. Management has requested that you build an employee database. You start with the employee table. What is the correct syntax?

    CREATE TABLE employee (
    employeeID char(10),
    firstName varchar(50),
    lastName varchar(50),
    phone varchar(20),
    address varchar(50),
    PRIMARY KEY ON employeeID
    );
    CREATE TABLE employee (
    employeeID char(10),
    firstName varchar(50),
    lastName varchar(50),
    phone varchar(20),
    address varchar(50),
    PRIMARY KEY employeeID
    );
    CREATE TABLE IF EXISTS employee (
    employeeID char(10),
    firstName varchar(50),
    lastName varchar(50),
    phone varchar(20),
    address varchar(50),
    PRIMARY KEY (employeeID)
    );
    CREATE TABLE IF NOT EXISTS employee (
    employeeID char(10),
    firstName varchar(50),
    lastName varchar(50),
    phone varchar(20),
    address varchar(50),
    PRIMARY KEY (employeeID)
    );

Q9. You are working with the tables as shown in this diagram. You need to generate the list of customers who purchased certain car models. Which SQL clause do you use?

mysql Q09

Q10. Which query would NOT be used to administer a MySQL server?

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

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

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

Q14. MySQL uses security based on _ for all connections, queries, and other operations that users can attempt to perform. or How are permissions implemented in MySQL?

Reference

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

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

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

Q18. How can you remove a record using MySQL?

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

Q20. What does the following SQL statement return?

SELECT * FROM Employees WHERE EmployeeName LIKE 'a%'

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

Q22. How does MySQL differ from SQL?

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

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

Reference

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

Q26. What is the default port for MySQL Server?

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

Q28. What is the difference between DROP and TRUNCATE?

Q29. How do you select every row in a given table named “inventory”?

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

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

Reference

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

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

Sample

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

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

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

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

Q38. What cannot have a trigger associated with it?

Reference

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

Q40. Which statement is true for the diagram below

mysql Q41

Q41. Which statement can you use to load data from a file into the table?

Q43. Which is the correct syntax of an extended insert statement?

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

Note that the question is about getting the data and not about the duplicating operation itself. And actually there is no need to run SHOW CREATE TABLE at all. To duplicate the table structure you can CREATE TABLE new_table LIKE original_table;.

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

Q46. You are managing a database with a table called customers. You created a temporary table also called customers with which you are working for the duration of your session. You need to re-create the temporary table with different specifications. Which command do you need to run first?

  1. reference
  2. reference

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

This is exactly what WITH clause is designed for

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

Reference

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

Note: perror prints a description for a system error code or for a storage engine (table handler) error code - link

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

Note: DESCRIBE tablename is a shortcut for this command

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

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

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

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

Q55. In recent versions of MySQL (8.0+), what’s the correct syntax to declare a CTE (Common Table Expression)?

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

Note: “to make the system faster” can also be correct. For example we can calculate some heavy query in advance and store its result in some column (use it as a cache). So if “system” means “application which uses mysql” then it’s correct too.

Q57. The code snippet below is used to read data from an XML file into a table. Which XML structure is _not_ supported by the statement?

LOAD XML LOCAL INFILE 'cars.xml'
INTO TABLE cars
ROWS IDENTIFIED BY `<car>`;
<car>
   <field name="make"> Lexus </field>
   <field name="model"> IS300 </field>
   <field name="make"> 2016 </field>
</car>
<car name="make"> Dodge </car>
<car name="model"> Ram </car>
<car name="year"> 2000 </car>
<car make="Ford" model="Mustang" year="2002"/>
<car year="2010">
    <make>Mercedes</make> <model> C-Class</model>
</car>

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

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

Note: both answers are correct - see TRUNCATE TABLE Statement in MySQL manual

Q60. You are working with the tables as shown in this diagram. You need to get the number of cars sold per the home state of each customer’s residence. How can you accomplish this?

mysql Q61

Explanation: THe difference between 2 and 3 is that LEFT JOIN will return 1 row per customer before grouping. If replaced with RIGHT JOIN it would return the correct info.

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

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

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

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

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

Note: the last option is valid too but the results will be enclosed with quotation marks

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

Q67. You are working with the table in this diagram. You want to use full-text search to find the customers who live on a street or a drive. What is the command to do that?

Table name: customers

ID lastname firstname phone address city state zip
A001 Smith Bob 212-555-1212 1001 1st Street New York NY 10001
A002 Chang John 213-555-5678 888 Rodeo Drive Los Angeles CA 90210
A003 Smith Mary 999-999-9999 123 Main Street Anytown VA 12345
A004 Johnson Jack 312-312-3120 1111 Chicago Avenue Chicago IL 60606
A005 Lopez Linda 737-777-3333 123 Main Street Austin TX 73344
SELECT *
FROM customers
WHERE address MATCH 'Street' OR 'Drive';
SELECT *
FROM customers
WHERE MATCH(address) IN ('street, drive');
SELECT *
FROM customers
WHERE address MATCH 'Street' OR address MATCH 'Drive';
SELECT *
FROM customers
WHERE MATCH(address) AGAINST ('street, drive');

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

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

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

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

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

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

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

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

Q76. Explain the security aspect of stored procedures

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

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

mysql picture

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

Explnation: BIT is not a string type

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

mysql picture

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

Explanation: Both SET and DECLARE are used to create variables. Reference: MySQL STORED PROCEDURE Tutorial With Examples

Q83. In the diagram below, the ID fields are declared as type CHAR instead of INT . Which is NOT one of the possible reasons behind that decision?

mysql picture

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

Explanation: CTEs do not create temporary tables, they only work within a signle query. Reference: 13.2.15 WITH (Common Table Expressions).

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

Reference: 4.2.2.4 Program Option Modifiers

Q86. What does this SQL statement return?

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

Q87. You are working with the tables as shown in the diagram. You need to generate the list of price totals for each make and model of car, with subtotals for each make, and the grand total of all prices. Which SQL clause do you use?

mysql picture

Q88. The left and right joins are also known as _.

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

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

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

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

Q93. The mysqldump command cannot generate output in _.

Q94. You are working with the tables shown below. You need to generate the list of all cars, whether or not they had been sold. Which statement accomplishes that?

mysql picture

SELECT cars.*, purchases.date
FROM cars RIGHT JOIN purchases
ON cars.ID = purchases.carID;
SELECT cars.*, purchases.date
FROM cars INNER JOIN purchases
ON cars.ID = purchases.carID;
SELECT cars.*, purchases.date
FROM cars JOIN purchases
ON cars.ID = purchases.carID;
SELECT cars.*, purchases.date FROM cars LEFT JOIN purchases ON cars.ID = purchases.carID;

Q95. Which code snippet from a stored procedure should be rewritten as a CASE statement?

    IF var1 THEN SET varA = var1;
    ELSEIF var2 THEN SET varA = var2;
    ELSEIF var3 THEN SET varA = var3;
    ELSE SET varA = var4;
    END IF;
    IF var1 = var2 THEN SET varA = var1;
    ELSEIF var2 = var3 THEN SET varA = var2;
    ELSEIF var3 = var4 THEN SET varA = var3;
    ELSE SET varA = var4;
    END IF;
    IF var1 = 1 THEN SET varA = var1;
    ELSEIF var2 = 2 THEN SET varA = var2;
    ELSEIF var3 = 3 THEN SET varA = var3;
    ELSE SET varA = var4;
    END IF;
    IF var1 = 1 THEN SET varA = var1;
    ELSEIF var1 = 2 THEN SET varA = var2;
    ELSEIF var1 = 3 THEN SET varA = var3;
    ELSE SET varA = var4;
    END IF;

Q96. Why would you use stored functions?

Q97. What steps do you need to take to normalize the table from this diagram?

Table name: superheroes | name | alias | power1 | power2 | power3 | | ---- | ----- | ------ | ------ | ------ | | Superman | Clark Kent | Flight | X-Ray Vision | Super Strength | | Wonder Woman | Diana Prince | Force Fields | Reflexes | Telepathy | | Spider-man | Peter Parker | Walcrawling | Web-making | Enhanced Senses | | Aquaman | Arthur Curry | Underwater Breathing | Enhanced Sight | Stamina | | Hulk | Bruce Banner | Super Strength | Radiation Immunity | Invulnerability |

Q98. A table Item has a Boolean field endOfLife and a field makeYear of type YEAR(4). How can you set the Boolean to true for all Items that have been made before 2019?

Q99. Which choice is an example of an aggregate function?

Reference

Q100. You are working on UPDATE trigger on the employee tablein this diagram. How can you access the new value for the address inside the trigger?

mysql picture

Reference

Q101. You are working with the tables as shown in this diagram. You need to generate the list of customers who purchased certain car models. Which SQL clause do you use?

Q104

Q102. How would you make a case-insensitive query in MySQL?

Q103. “COUNT” keyword belongs to which categories in Mysql?

Q104. What is the meaning of “HAVING” clause in Mysql?

Q105. Which clause is similar to “HAVING” clause in Mysql?

Q106. What will be the output of the following MySQL command?

  SELECT emp_id, fname, lname
  FROM employee
  WHERE title=’HEAD TELLER’ AND start_date&gt;2008-11-23;

Q107. Is there any error in the following MySQL statement?

  SELECT e.emp_id, e.fname,e.lname,d.name
  FROM employee e INNER JOIN department d
  ON e.dept_id=e.dept_id;

Q108. With MySQL, how do you select all the records from a table named “Persons” where the “LastName” is alphabetically between (and including) “Hansen” and “Pettersen”?

Reference

Q109. Consider the set of relations given below and the SQL query that follows

    Students : (Roll number, Name, Date of birth)
    Courses: (Course number, Course name, instructor)
    Grades: (Roll number, Course number, Grade)
    SELECT DISTINCT Name
    FROM Students, Courses, Grades
    WHERE Students.Roll_number = Grades.Roll_number
    AND Courses.Instructor =Sriram
    AND Courses.Course_number = Grades.Course_number
    AND Grades.Grade = A

(Which of the following sets is computed by the above query?)

Q110. You are working with the tables shown below. You need to make sure that any record added to the purchases table consists of a customerlD, which already exists in the customers table, and a carlD, which already exists in the cars table. You decide to use a trigger to do the validation. Which one do you use?

mysql picture

IF EXISTS and CROSS JOIN are not valid for a trigger.

Q111. Current versions of MySQL support the full-text search feature on some storage engines, as an alternative to using the LIKE operator and regular expressions. Which statement would you run to enable a full-text index for the column description in the table Car?

Q112. Which statement would you not use to filter data?

image

Q113. In MySQL, which JOIN type returns all rows from the left table and the matching rows from the right table, and fills in with NULL values if there is no match on the right side?

Q114. What does SQL stand for in MySQL?

Q115. Which MySQL statement is used to select data from a database?

Q116. What is the purpose of the PRIMARY KEY in a MySQL table?

Reference

Q117. Which of the following is a valid SQL query to insert a new row into the users table?

Q118. Which of the following is a valid SQL query to delete the row with the id of 2 from the users table?

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

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

Q121. The left and right joins are also known as _.

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

Q123. In a database with a “Students” table containing information about students, which SQL statement is used to retrieve the names of all students who scored higher than 90 in their exams?

SELECT student_name FROM Students WHERE score > 90;
SELECT name FROM Students WHERE score > 90;
SELECT student_name FROM Students WHERE exam_score > 90;
SELECT name FROM Students WHERE exam_score > 90;

Q124 Which SQL command is used to retrieve data from a database?