MYSQL quiz4

Retrieve the number of customers in each city who have made more than 5 orders.

• SELECT city, COUNT(customer_id) AS num_customers FROM orders GROUP BY city HAVING COUNT(customer_id) > 5;

  
  

• SELECT city, COUNT(customer_id) AS num_customers FROM orders GROUP BY city WHERE COUNT(customer_id) > 5;

  
  

• SELECT city, COUNT(customer_id) AS num_customers FROM orders HAVING COUNT(customer_id) > 5 GROUP BY city;

  
  

•  SELECT city, COUNT(customer_id) AS num_customers FROM orders WHERE COUNT(customer_id) > 5 GROUP BY city;

  
  

Retrieve the average salary of employees in each department that have more than 10 employees.

• SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department WHERE COUNT(*) > 10;

  
  

• SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING COUNT(*) > 10;

  
  

• SELECT department, AVG(salary) AS avg_salary FROM employees HAVING COUNT(*) > 10 GROUP BY department;

  
  

• SELECT department, AVG(salary) AS avg_salary FROM employees WHERE COUNT(*) > 10 GROUP BY department;

  
  

Retrieve the maximum order amount for each customer who has placed more than 3 orders.

•  SELECT customer_id, MAX(order_amount) AS max_amount FROM orders GROUP BY customer_id WHERE COUNT(*) > 3;

  
  

• SELECT customer_id, MAX(order_amount) AS max_amount FROM orders HAVING COUNT(*) > 3 GROUP BY customer_id;

  
  

• SELECT customer_id, MAX(order_amount) AS max_amount FROM orders GROUP BY customer_id HAVING COUNT(*) > 3;

  
  

•  SELECT customer_id, MAX(order_amount) AS max_amount FROM orders WHERE COUNT(*) > 3 GROUP BY customer_id;

  
  

Retrieve the total quantity of products sold for each category where the average price is greater than $50.

• SELECT category, SUM(quantity) AS total_quantity FROM products WHERE AVG(price) > 50 GROUP BY category;

  
  

• SELECT category, SUM(quantity) AS total_quantity FROM products HAVING AVG(price) > 50 GROUP BY category;

  
  

• SELECT category, SUM(quantity) AS total_quantity FROM products GROUP BY category WHERE AVG(price) > 50;

  
  

• SELECT category, SUM(quantity) AS total_quantity FROM products GROUP BY category HAVING AVG(price) > 50;

  
  

Retrieve the minimum age of employees in each department who have more than 5 years of experience.

• SELECT department, MIN(age) AS min_age FROM employees GROUP BY department HAVING COUNT(*) > 5 AND experience > 5;

  
  

•  SELECT department, MIN(age) AS min_age FROM employees GROUP BY department WHERE COUNT(*) > 5 AND experience > 5;

  
  

• SELECT department, MIN(age) AS min_age FROM employees HAVING COUNT(*) > 5 AND experience > 5 GROUP BY department;

  
  

• SELECT department, MIN(age) AS min_age FROM employees WHERE COUNT(*) > 5

Retrieve the number of customers in each city who have made more than 5 orders.

• SELECT city, COUNTCOUNT(customer_id) > 5;

  
  

• SELECT city, COUNT(customer_id) AS num_customers FROM orders GROUP BY city HAVING COUNT(customer_id) > 5;

  
  

• SELECT city, COUNT(customer_id) AS num_customers FROM orders HAVING COUNT(customer_id) > 5 GROUP BY city;

  
  

•  SELECT city, COUNT(customer_id) AS num_customers FROM orders WHERE COUNT(customer_id) > 5 GROUP BY city;

  
  

What is the main purpose of a database?

• To organize and store data

  
  

• To analyze trends and derive insights

  
  

• To encrypt sensitive information

  
  

• To manage concurrent access and updates

  
  

Which type of database is the most widely used?

• Graph database

  
  

• Document-oriented database

  
  

• Relational database

  
  

• Key-value store

  
  

What is the role of a database management system (DBMS)?

• Data analysis and reporting

  
  

• Data encryption and security

  
  

• Data retrieval and management

  
  

• Data scalability and growth

  
  

What advantage does a database provide in terms of data integrity?

• Establishing relationships between tables

  
  

• Supporting transactions

  
  

• Handling large data volumes

  
  

• Ensuring data quality and reliability