Overall SQL Exam

Which query fetches all customers whose name starts with “A”?

• SELECT * FROM customers WHERE name = 'A%';

• SELECT * FROM customers WHERE name LIKE 'A%';

• SELECT * FROM customers WHERE name STARTS WITH 'A';

• SELECT * FROM customers WHERE name IN ('A%');

Which of the following is TRUE about PRIMARY KEY? (Select all that applies)

• Allows NULL values

• Must be unique

• A table can have multiple PRIMARY KEYS

• A values must be entered in a PRIMARY KEY Column

Which query finds the 2nd highest salary?

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

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

• SELECT salary FROM employees ORDER BY salary DESC LIMIT 2,1;

• Both a and b

Which query finds each employee’s salary compared to previous employee in order of hire_date?

• SELECT name, salary,

  LAG(salary) OVER(ORDER BY hire_date) prev_salary

  FROM employees;

• SELECT name, salary,

  LEAD(salary) OVER(ORDER BY hire_date) prev_salary

  FROM employees;

• SELECT name, salary FROM employees ORDER BY hire_date;

• SELECT name, salary, salary-1 FROM employees;

Which of the following queries creates a VIEW of active customers?

• CREATE VIEW active_customers AS

  SELECT * FROM customers WHERE status='active';

• CREATE OR REPLACE VIEW active_customers AS

  SELECT * FROM customers WHERE status='active';

• CREATE TABLE active_customers AS

  SELECT * FROM customers WHERE status='active';

• Both a and b

Which query finds employees who do not have a manager assigned? (Select all that applies)

• SELECT * FROM employees WHERE manager_id IS NULL;

• SELECT * FROM employees WHERE manager_id = NULL;

• SELECT * FROM employees WHERE manager_id NOT IN (SELECT id FROM employees);

• SELECT * FROM employees WHERE manager_id = 0;

Which query checks employees working in more than one department? (emp_dept table)

• SELECT emp_id FROM emp_dept

  GROUP BY emp_id

  HAVING COUNT(DISTINCT dept_id) > 1;

• SELECT emp_id FROM emp_dept WHERE dept_id > 1;

• SELECT DISTINCT emp_id FROM emp_dept;

• SELECT emp_id FROM emp_dept GROUP BY dept_id;

Which operator is used to check multiple values in MySQL?

• BETWEEN

• LIKE

• IN

• EXISTS

What will this query return?

SELECT COUNT(DISTINCT department_id) FROM employees;

• Total number of employees

• Number of unique departments

• Distinct salaries

• Distinct employees

Which of the following are valid constraints in MySQL? (Select all that applies)

• UNIQUE

• FOREIGN KEY

• NOT NULL

• ORDER BY