Special DB Exam

Which of the following is the correct MySQL command to create a database?

• CREATE TABLE database_name;

• CREATE DATABASE database_name;

• NEW DATABASE database_name;

• CREATE SCHEMA database_name;

Which of the following queries will return employees who have a salary greater than the average salary of all employees?

• SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

• SELECT * FROM employees WHERE salary = AVG(salary);

• SELECT * FROM employees WHERE salary > (SELECT MAX(salary) FROM employees);

• SELECT * FROM employees WHERE salary > 5000;

Which of the following SQL commands is used to prevent new records from being inserted into a table?

• DISABLE INSERT INTO table_name;

• ALTER TABLE table_name ADD DISABLE_INSERT;

• LOCKS TABLES table_name WRITE;

• ALTER TABLE table_name SET INSERT RESTRICT;

• NO ANSWER

What is the result of the following query?

SELECT employee_id, salary FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 2;

• The query will return the 3rd, 4th, and 5th highest salaries.

• The query will return the top 3 highest salaries.

• The query will return the 2nd, 3rd, and 4th highest salaries.

• The query will return the top 2 highest salaries starting from the 3rd highest.

What does the following SQL statement do?

SELECT COUNT(*) FROM employees WHERE department_id IS NULL;

• Counts the number of employees in the employees table.

• Counts the number of employees with no department.

• Counts the number of NULL values in the department_id column

• Counts the total number of rows in the employees table.

Which of the following queries will return the second highest salary from the employees table?

• SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

• SELECT MIN(salary) FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

• SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

• SELECT salary FROM employees ORDER BY salary LIMIT 1 OFFSET 2;

Which statement will return the count of employees per department, including departments with zero employees?

• SELECT department_id, COUNT(employee_id) FROM employees GROUP BY department_id;

• SELECT d.department_id, COUNT(e.employee_id) FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id;

• SELECT department_id, COUNT(*) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id;

• SELECT department_id, COUNT(*) FROM employees GROUP BY department_id WITH NULLS;

Which query is correct to get the first 10 records from a table employees sorted by salary in descending order?

• SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

• SELECT * FROM employees LIMIT 10 ORDER BY salary DESC;

• SELECT TOP 10 * FROM employees ORDER BY salary DESC;

• SELECT * FROM employees WHERE salary > 1000 LIMIT 10;

How would you modify a column salary in the employees table to allow NULL values?

• ALTER TABLE employees MODIFY salary INT NULL;

• ALTER TABLE employees CHANGE salary salary INT NULL;

• ALTER TABLE employees MODIFY salary SET NULL;

• ALTER TABLE employees ALTER salary SET NULL;

Which of the following queries will return the total salary per department, excluding departments with no employees?

• SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING COUNT(employee_id) > 0;

• SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING salary > 0;

• SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING COUNT(*) > 0;

• SELECT department_id, SUM(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id;