Which CASE expression correctly classifies salaries? (select all that applies)

SELECT name, CASE WHEN salary > 70000 THEN 'High' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'Low' END AS category FROM employees;

SELECT CASE salary > 70000 THEN 'High' END FROM employees;

SELECT salary, CASE salary WHEN >70000 THEN 'High' END FROM employees;

SELECT name, CASE WHEN salary > 70000 THEN 'High' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'Low' END FROM employees;

Which query finds employees who joined earliest in each department? (Select all that applies)

SELECT name, department_id, hire_date FROM ( SELECT name, department_id, hire_date, RANK() OVER(PARTITION BY department_id ORDER BY hire_date ASC) rnk FROM employees ) x WHERE rnk=1;

SELECT name, department_id, hire_date FROM ( SELECT name, department_id, hire_date, DENSE_RANK() OVER(PARTITION BY department_id ORDER BY hire_date ASC) rnk FROM employees ) x WHERE rnk=1;

SELECT name FROM employees GROUP BY department_id HAVING MIN(hire_date);

SELECT name, department_id FROM employees ORDER BY hire_date LIMIT 1;

A company wants to list employees who got a salary increase compared to their previous year’s salary (based on yearly_salary table: emp_id, year, salary). Which query works best?

WITH cte AS ( SELECT emp_id, year, salary, LAG(salary) OVER(PARTITION BY emp_id ORDER BY year) AS prev_salary FROM yearly_salary ) SELECT emp_id, year, salary, prev_salary FROM cte WHERE salary > prev_salary;

SELECT emp_id, year, salary, LAG(salary) OVER(PARTITION BY emp_id ORDER BY year) prev_salary FROM yearly_salary WHERE salary > prev_salary;

SELECT emp_id, salary FROM yearly_salary WHERE salary > (SELECT MAX(salary) FROM yearly_salary);

SELECT emp_id FROM yearly_salary GROUP BY emp_id HAVING salary > MAX(salary);

We want to find customers who placed orders in consecutive months (orders table: order_id, customer_id, order_date).

WITH cte AS ( SELECT customer_id, order_date, MONTH(order_date) AS curr_month, LAG(MONTH(order_date)) OVER(PARTITION BY customer_id ORDER BY order_date) AS prev_month FROM orders ) SELECT customer_id, order_date FROM cte WHERE curr_month - prev_month = 1;

SELECT * FROM ( SELECT customer_id, order_date, MONTH(order_date) AS curr_month, LAG(MONTH(order_date)) OVER(PARTITION BY customer_id ORDER BY order_date) AS prev_month FROM orders ) t WHERE curr_month - prev_month = 1;

SELECT customer_id, order_date, LAG(MONTH(order_date)) OVER(PARTITION BY customer_id ORDER BY order_date) prev_month FROM orders WHERE MONTH(order_date) - prev_month = 1;

We want to prepare a report showing employee email, but if email is NULL, show `'Not Provided'`. Which query works? (Select all that applies according to MySQL)

SELECT COALESCE(email, 'Not Provided') FROM employees;

SELECT CASE WHEN email IS NULL THEN 'Not Provided' ELSE email END FROM employees;

SELECT ISNULL(email, 'Not Provided') FROM employees;

SELECT NVL(email, 'Not Provided') FROM employees;

Which query returns employees hired in 2024? (Select all that applies)

SELECT * FROM employees WHERE YEAR(hire_date)=2024;

SELECT * FROM employees WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM employees WHERE hire_date LIKE '2024%';

Which of the following is TRUE about COALESCE?

Works only with numeric columns

Which query fetches the average salary of employees?

SELECT AVG(salary) FROM employees;

SELECT MEAN(salary) FROM employees;

SELECT SUM(salary)/COUNT(salary) FROM employees;

Company wants to give bonus to the top employee by sales in each region . (sales: emp_id, region, amount).

SELECT emp_id, region, amount FROM ( SELECT emp_id, region, amount, RANK() OVER(PARTITION BY region ORDER BY amount DESC) rnk FROM sales ) t WHERE rnk = 1;

WITH ranked_sales AS ( SELECT emp_id, region, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk FROM sales ) SELECT emp_id, region, amount FROM ranked_sales WHERE rnk = 1;

SELECT emp_id, region, MAX(amount) FROM sales GROUP BY emp_id, region;

WITH ranked_sales AS ( SELECT emp_id, region, amount, RANK() OVER (ORDER BY amount DESC) AS rnk FROM sales ) SELECT emp_id, region, amount FROM ranked_sales WHERE rnk = 1;

SELECT emp_id, region FROM sales WHERE amount IN (SELECT MAX(amount) FROM sales);

Find all orders where shipping took more than 7 days . (orders: order_id, order_date, ship_date).

SELECT order_id FROM orders WHERE DATEDIFF(ship_date, order_date) > 7;

SELECT order_id FROM orders WHERE ship_date - order_date > 7;

SELECT order_id FROM orders WHERE TIMESTAMPDIFF(DAY, order_date, ship_date) > 7;

Find customers who placed orders but did not make a payment. (customers, orders, payments).

SELECT c.customer_id, c.name FROM customers c JOIN orders o ON c.customer_id=o.customer_id LEFT JOIN payments p ON o.order_id=p.order_id WHERE p.order_id IS NULL;

SELECT customer_id FROM orders WHERE order_id = (SELECT order_id FROM payments);

SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM payments);

Classify employees into 'Low' (<40000), 'Medium' (40000–70000), 'High' (>70000).

SELECT name, CASE WHEN salary < 40000 THEN 'Low' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'High' END AS category FROM employees;

SELECT name, CASE WHEN salary < 40000 THEN 'Low' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'High' END FROM employees;

SELECT name, CASE WHEN salary > 40000 THEN 'Low' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'High' END AS category FROM employees;

Which query lists employees ranked by sales but handles ties properly? (Select all that applies)

SELECT emp_id, sales, RANK() OVER(ORDER BY sales DESC) rnk FROM sales;

SELECT emp_id, sales, DENSE_RANK() OVER(ORDER BY sales DESC) rnk FROM sales;

SELECT emp_id, sales, ROW_NUMBER() OVER(ORDER BY sales DESC) rnk FROM sales;

SELECT emp_id FROM sales ORDER BY sales DESC;

Which query deletes all employees in department HR?

DELETE FROM employees WHERE department='HR';

REMOVE FROM employees WHERE department='HR';

DROP employees WHERE department='HR';

TRUNCATE employees WHERE department='HR';

Which of the following queries returns cumulative salary by department?

SELECT department_id, salary, SUM(salary) OVER(PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum_salary FROM employees;

SELECT department_id, salary, SUM(salary) OVER(ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum_salary FROM employees;

SELECT department_id, salary, SUM(salary) OVER(PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW) cum_salary FROM employees;

Overall SQL Exam

Professional Development

•

30 Qs

Similar activities

UEENEEE103A Chapter 3 revision

Professional Development

•

28 Qs

Quant_Permutation_Quiz

University - Professional Development

•

35 Qs

GROAP 14 - 2022

Professional Development

•

35 Qs

Salesforce 101 Assessment

Professional Development

•

30 Qs

JB Standards

Professional Development

•

26 Qs

Aplikasi android

Professional Development

•

27 Qs

DLC Questionnaire

Professional Development

•

35 Qs

Purchase Contracts

Professional Development

•

27 Qs

Overall SQL Exam

Passage

•

Professional Development

•

Professional Development

•

Practice Problem

•

Hard

Data_Analyst_Harish undefined

Used 5+ times

FREE Resource

30 questions

Show all answers

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

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%');

MULTIPLE SELECT QUESTION

45 sec • 1 pt

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

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

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

MULTIPLE CHOICE QUESTION

30 sec • 2 pts

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;

MULTIPLE CHOICE QUESTION

30 sec • 2 pts

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

MULTIPLE SELECT QUESTION

45 sec • 2 pts

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;

MULTIPLE CHOICE QUESTION

30 sec • 2 pts

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;

Create a free account and access millions of resources

Create resources

Host any resource

Get auto-graded reports

Continue with Google

Continue with Email

Continue with Classlink

Continue with Clever

or continue with

Microsoft

Apple

Others

Already have an account?

Similar Resources on Wayground

30 questions

Session 1LNMIIT

Quiz

•

Professional Development

30 questions

LV03 mock exam

Quiz

•

Professional Development

26 questions

e-COM 2024/s0s5 MID TERM

Quiz

•

Professional Development

31 questions

Cold Water Systems | 6035-02 | Picture Quiz

Quiz

•

Professional Development

30 questions

MSG PST Week 4 Refresher

Quiz

•

Professional Development

28 questions

Ducati Quiz

Quiz

•

Professional Development

30 questions

Comptia Sec+ 421-450

Quiz

•

Professional Development

28 questions

Quiz on Applications and Apps

Quiz

•

Professional Development

Popular Resources on Wayground

5 questions

This is not a...winter edition (Drawing game)

Quiz

•

1st - 5th Grade

25 questions

Multiplication Facts

Quiz

•

5th Grade

10 questions

Identify Iconic Christmas Movie Scenes

Interactive video

•

6th - 10th Grade

20 questions

Christmas Trivia

Quiz

•

6th - 8th Grade

18 questions

Kids Christmas Trivia

Quiz

•

KG - 5th Grade

11 questions

How well do you know your Christmas Characters?

Lesson

•

3rd Grade

14 questions

Christmas Trivia

Quiz

•

5th Grade

20 questions

How the Grinch Stole Christmas

Quiz

•

5th Grade

Discover more resources for Professional Development

15 questions

Fun Holiday Trivia

Quiz

•

Professional Development

20 questions

Christmas Trivia

Quiz

•

Professional Development

21 questions

Name that Holiday Movie

Quiz

•

Professional Development

Overall SQL Exam

30 questions

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

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

Which query finds the 2nd highest salary?

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

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

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

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

Which operator is used to check multiple values in MySQL?

What will this query return?
SELECT COUNT(DISTINCT department_id) FROM employees;

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

Create a free account and access millions of resources

Similar Resources on Wayground

Popular Resources on Wayground

Discover more resources for Professional Development

Overall SQL Exam

30 questions

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

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

Which query finds the 2nd highest salary?

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

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

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

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

Which operator is used to check multiple values in MySQL?

What will this query return?SELECT COUNT(DISTINCT department_id) FROM employees;

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

Create a free account and access millions of resources

Similar Resources on Wayground

Popular Resources on Wayground

Discover more resources for Professional Development

What will this query return?
SELECT COUNT(DISTINCT department_id) FROM employees;