SQL Quiz – 11 – NiC IT Academy

SQL Quiz – 11

By Mr.Chandra

Full Name

Mobile Number

1) What is a database audit in Oracle?

A process of reviewing database activity

A database object used to store data

A function used to perform calculations

2) What is the purpose of the Oracle Data Masking feature?

To mask sensitive data

To anonymize sensitive data

All of the above

3) What is a dimension table in a data warehouse?

A table that contains measures

A table that contains dimensions

A table that contains both measures and dimensions

4) What is a slowly changing dimension (SCD) in a data warehouse?

A dimension that changes slowly over time

A dimension that changes frequently

A dimension that is used to track historical data

5) What is a materialized view refresh method in Oracle?

Incremental refresh

Fast refresh

All of the above

6) What is the purpose of the Oracle Partitioning feature?

To improve query performance

To manage large datasets

All of the above

7) You're working on a project to track employee performance. Which SQL statement would you use to find the average salary of employees in the 'Sales' department?

SELECT AVG(salary) FROM employees WHERE department = 'Sales';

SELECT MAX(salary) FROM employees WHERE department = 'Sales';

SELECT MIN(salary) FROM employees WHERE department = 'Sales';

8) For a project on customer satisfaction, you need to identify customers who have made purchases in the past 30 days. Which clause would you use in your SQL query?

HAVING

WHERE

GROUP BY

9) In a project analyzing sales trends, you want to group customers by their purchase frequency. Which SQL clause would you use?

HAVING

WHERE

GROUP BY

10) You're working on a project to optimize database performance. Which SQL statement would you use to identify tables that have a large number of rows?

SELECT table_name, num_rows FROM user_tables;

SELECT table_name, last_analyzed FROM user_tables;

SELECT table_name, num_data_blocks FROM user_tables;

11) For a project on inventory management, you need to calculate the total quantity of a specific product in stock. Which SQL statement would you use?

SELECT SUM(quantity) FROM inventory WHERE product_id = 'P123';

SELECT AVG(quantity) FROM inventory WHERE product_id = 'P123';

SELECT COUNT(*) FROM inventory WHERE product_id = 'P123';

12) You're working on a project to analyze customer behavior. Which SQL statement would you use to find the first purchase date for each customer?

SELECT customer_id, MIN(purchase_date) FROM orders GROUP BY customer_id;

SELECT customer_id, MAX(purchase_date) FROM orders GROUP BY customer_id;

SELECT customer_id, AVG(purchase_date) FROM orders GROUP BY customer_id;

13) For a project on customer segmentation, you need to identify customers who have made purchases in multiple product categories. Which SQL statement would you use?

SELECT customer_id, COUNT(DISTINCT product_category) FROM orders GROUP BY customer_id HAVING COUNT(DISTINCT product_category) > 1;

SELECT customer_id, SUM(quantity) FROM orders GROUP BY customer_id HAVING SUM(quantity) > 100;

SELECT customer_id, AVG(purchase_date) FROM orders GROUP BY customer_id HAVING AVG(purchase_date) > '2023-01-01';

14) In a project analyzing sales trends, you want to find the top 5 products by sales volume. Which SQL statement would you use?

SELECT product_id, SUM(quantity) FROM orders GROUP BY product_id ORDER BY SUM(quantity) DESC LIMIT 5;

SELECT product_id, MAX(quantity) FROM orders GROUP BY product_id ORDER BY MAX(quantity) DESC LIMIT 5;

SELECT product_id, AVG(quantity) FROM orders GROUP BY product_id ORDER BY AVG(quantity) DESC LIMIT 5;

15) For a project on customer retention, you need to identify customers who have made a purchase in the current month but not in the previous month. Which SQL statement would you use?

SELECT customer_id FROM orders WHERE purchase_date >= TRUNC(SYSDATE, 'MM') AND purchase_date < TRUNC(SYSDATE, 'MM') + 1;

SELECT customer_id FROM orders WHERE purchase_date >= TRUNC(SYSDATE, 'MM') - 1 AND purchase_date < TRUNC(SYSDATE, 'MM');

SELECT customer_id FROM orders WHERE purchase_date >= TRUNC(SYSDATE, 'MM') AND purchase_date = TRUNC(SYSDATE, 'MM') - 1 AND purchase_date < TRUNC(SYSDATE, 'MM'));

16) You're working on a project to analyze employee turnover. Which SQL statement would you use to find the average tenure of employees who have left the company?

SELECT AVG(TO_DATE(end_date) - TO_DATE(start_date)) FROM employees WHERE end_date IS NOT NULL;

SELECT AVG(TO_DATE(end_date) - TO_DATE(start_date)) FROM employees WHERE start_date IS NOT NULL;

SELECT AVG(TO_DATE(start_date) - TO_DATE(end_date)) FROM employees WHERE end_date IS NOT NULL;

17) You're working on a project to analyze financial data. Which SQL statement would you use to find the total revenue generated by each product category in the past quarter?

SELECT product_category, SUM(revenue) FROM sales_data WHERE purchase_date >= TRUNC(SYSDATE, 'Q') - 90 AND purchase_date < TRUNC(SYSDATE, 'Q') GROUP BY product_category;

SELECT product_category, SUM(revenue) FROM sales_data WHERE purchase_date >= TRUNC(SYSDATE, 'YY') - 1 AND purchase_date < TRUNC(SYSDATE, 'YY') GROUP BY product_category;

SELECT product_category, SUM(revenue) FROM sales_data WHERE purchase_date >= TRUNC(SYSDATE, 'MM') - 3 AND purchase_date < TRUNC(SYSDATE, 'MM') GROUP BY product_category;

18) For a project on customer profitability, you need to calculate the customer lifetime value (CLTV) based on their purchase history and average order value. Which SQL statement would you use?

SELECT customer_id, SUM(revenue) / AVG(order_value) AS CLTV FROM sales_data GROUP BY customer_id;

SELECT customer_id, SUM(revenue) * AVG(order_value) AS CLTV FROM sales_data GROUP BY customer_id;

SELECT customer_id, AVG(revenue) / SUM(order_value) AS CLTV FROM sales_data GROUP BY customer_id;

19) You're working on a project to optimize inventory management. Which SQL statement would you use to find the average lead time for product replenishment?

SELECT product_id, AVG(TO_DATE(arrival_date) - TO_DATE(order_date)) AS avg_lead_time FROM purchase_orders GROUP BY product_id;

SELECT product_id, MAX(TO_DATE(arrival_date) - TO_DATE(order_date)) AS avg_lead_time FROM purchase_orders GROUP BY product_id;

SELECT product_id, MIN(TO_DATE(arrival_date) - TO_DATE(order_date)) AS avg_lead_time FROM purchase_orders GROUP BY product_id;

20) For a project on supplier performance, you need to identify suppliers who have frequently missed delivery deadlines. Which SQL statement would you use?

SELECT supplier_id, COUNT(*) FROM purchase_orders WHERE arrival_date > expected_delivery_date GROUP BY supplier_id HAVING COUNT(*) > 1;

SELECT supplier_id, AVG(TO_DATE(arrival_date) - TO_DATE(expected_delivery_date)) FROM purchase_orders GROUP BY supplier_id HAVING AVG(TO_DATE(arrival_date) - TO_DATE(expected_delivery_date)) > 0;

SELECT supplier_id, MAX(TO_DATE(arrival_date) - TO_DATE(expected_delivery_date)) FROM purchase_orders GROUP BY supplier_id HAVING MAX(TO_DATE(arrival_date) - TO_DATE(expected_delivery_date)) > 0;

SQL Quiz – 11

By Mr.Chandra

For enrolment support contact us at:

+91 99529 74013

+91 70100 80468

Contact Us

NiC IT Academy

An Online Software Training Institute

G1, Venkatesan st, Chrompet, Chennai - Tamil Nadu 600044 (IN)

Support Email

support@nicitacademy.com

Useful Links

©2024 NiC IT Academy - All Right Reserved.

Designed By - Innovtechsol

Login with your email & password

Sign up with your email & password