Consider a table named sales_data with the following schema: CREATE TABLE sales_data ( id INT PRIMARY KEY, amount DECIMAL(10,2), region VARCHAR(50) ); Assume the table contains the following data: id amount region 1 100.00 East 2 200.00 West 3 NULL North 4 150.00 East 5 120.00 NULL 6 180.00 West 7 NULL NULL 8 300.00 South Write a SQL query to calculate the total sales amount for each region, including NULL regions, and display the result in descending order of the total sales amount. If a region is NULL or if there are NULL values in the amount column, treat them as 0 in the calculation. Solution SELECT COALESCE(region, 'Unknown') AS region, COALESCE(SUM(COALESCE(amount, 0)), 0) AS total_sales FROM sales_data GROUP BY region ORDER BY total_sales DESC; Explanation: We use the COALESCE function to handle NULL values. If region or amount is NULL, it's replaced with ...

Consider a table named sales that stores information about sales transactions. The table has the following columns: transaction_id : The unique identifier for each transaction. transaction_date : The date when the transaction occurred. amount : The amount of the transaction. Write a SQL query to calculate the cumulative sum of sales amounts for each day, ordered by transaction date. Sample Table: CREATE TABLE sales ( transaction_id INT, transaction_date DATE, amount DECIMAL(10, 2) ); INSERT INTO sales (transaction_id, transaction_date, amount) VALUES (1, '2024-03-01', 100), (2, '2024-03-01', 150), (3, '2024-03-02', 200), (4, '2024-03-03', 50), (5, '2024-03-03', 300), (6, '2024-03-04', 100); Solution: SELECT transaction_date, amount, SUM(amount) OVER (ORDER BY transaction_date) AS cumulative_sum FROM sales ORDER BY transaction_date;   Share your alternate solution in comment...

Consider two tables: employees and departments . The employees table contains columns emp_id , emp_name , dept_id , salary . The departments table contains columns dept_id and dept_name . Write a SQL query to find the department names along with the average salary of employees in each department. Include all departments, even if there are no employees in them. Sample Data: employees emp_id emp_name dept_id salary 1 Alice 101 50000 2 Bob 102 60000 3 Charlie 101 55000 4 David 103 48000 5 Eve 102 62000   departments dept_id dept_name 101 HR 102 Finance 103 Marketing 104 Operations Solution: SELECT d.dept_name, COALESCE(AVG(e.salary), 0) AS avg_salary FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id GROU...

Consider two tables, employees and departments , with the following structures: employees: | employee_id | employee_name | department_id | |-------------|---------------|---------------| | 1 | Alice | 1 | | 2 | Bob | 2 | | 3 | Charlie | 1 | | 4 | David | 3 | departments: | department_id | department_name | |---------------|-----------------| | 1 | Sales | | 2 | Marketing | | 3 | Finance | Write an SQL query to list all employees along with their department names. If an employee does not belong to any department, the department name should be displayed as "No Department". Solution: SELECT e.employee_name, COALESCE(d.department_name, 'No Department') AS department_name FROM employees e ...

Given a table named employees with the following schema: CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(100), salary DECIMAL(10, 2), hire_date DATE ); Write a SQL query to find the names of employees who have been with the company for more than 5 years and have a salary greater than $50,000.   Sample Data: INSERT INTO employees (id, name, department, salary, hire_date) VALUES (1, 'John Doe', 'Engineering', 60000.00, '2016-03-25'), (2, 'Jane Smith', 'Marketing', 55000.00, '2017-07-10'), (3, 'Alice Johnson', 'Engineering', 70000.00, '2018-01-15'), (4, 'Bob Brown', 'Sales', 48000.00, '2019-05-20'), (5, 'Emily Davis', 'HR', 52000.00, '2015-10-30');      Solution: SELECT name FROM employees WHERE hire_date < DATE_SUB(CURDATE(), INTERVAL 5 YEAR) AND salary > 50000;    ...

Consider a table named Employee with the following schema: CREATE TABLE Employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(100), department VARCHAR(100), salary DECIMAL(10, 2), hire_date DATE ); Write an SQL query to find the employee(s) with the highest salary within each department. Sample Table: emp_id emp_name department salary hire_date 1 Alice Sales 50000 2020-01-01 2 Bob Sales 55000 2019-05-15 3 Charlie HR 60000 2020-03-20 4 David HR 58000 2018-11-10 5 Emma Marketing 52000 2021-02-28 6 Frank Marketing 53000 2019-08-10 Solution: SELECT emp_id, emp_name, department, salary FROM Employee e WHERE (department, salary) IN ( SELECT department, MAX(salary) FROM Employee GROUP BY department );   Explanation: The inner query ( SELECT department, MAX(salary) FROM Employee GROUP BY department ) ...