FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Create `sales` table
CREATE TABLE sales (
    id INT PRIMARY KEY,
    employee_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- Insert data into `employees`
INSERT INTO employees (id, name) VALUES
(1, 'Alex'),
(2, 'Jack'),
(3, 'Emily'),
(4, 'Lee');

-- Insert data into `sales`
INSERT INTO sales (id, employee_id, amount) VALUES
(1, 1, 12000.00),
(2, 1, 5000.00),
(3, 2, 14000.00),
(4, 2, 7000.00),
(5, 3, 8000.00),
(6, 3, 2000.00),
(7, 4, 3000.00),
(8, 4, 4500.00),
(9, 1, 15000.00),
(10, 2, 25000.00);

SELECT e.name, SUM(s.amount) AS total_sales
FROM employees e
JOIN sales s ON e.id = s.employee_id
GROUP BY e.name
HAVING SUM(s.amount) > 10000
ORDER BY total_sales DESC;
---------------------------------
SELECT 
e.name, 
ts.total_sales 
FROM employees e 
JOIN ( SELECT employee_id, 
SUM(amount) AS total_sales 
FROM 
sales 
GROUP BY employee_id 
HAVING SUM(amount) > 10000 ) ts 
ON e.id =ts.employee_id 
ORDER BY ts.total_sales DESC;
-----------------------------------------------
SELECT 
e.name, (SELECT SUM(s.amount) 
FROM sales s 
WHERE s.employee_id = e.id 
GROUP BY s.employee_id HAVING SUM(s.amount) > 10000) AS total_sales 
FROM employees e 
WHERE EXISTS( SELECT 1 FROM 
sales s 
WHERE s.employee_id = e.id 
GROUP BY s.employee_id HAVING SUM(s.amount) > 10000 ) 
ORDER BY total_sales DESC;
----------------------------------------------------
SELECT name ,total_sales from 
(
SELECT DISTINCT e.name, SUM(s.amount) 
OVER (PARTITION BY e.id) AS total_sales 
FROM
employees e 
JOIN 
sales s 
ON e.id = s.employee_id 
) A
WHERE total_sales > 10000 
ORDER BY total_sales DESC;

---------------------------

-- Create a temporary table to hold summed sales

SELECT employee_id, SUM(amount) AS total_sales INTO #EmployeeSales
FROM sales 
GROUP BY employee_id;

-- Query the main data using the temporary table 
SELECT e.name, es.total_sales 
FROM employees e 
JOIN #EmployeeSales es ON e.id = es.employee_id 
WHERE es.total_sales > 10000 
ORDER BY es.total_sales DESC;

-- Optionally, drop the temporary table if it's no longer needed
DROP TABLE #EmployeeSales;
----------------------------
WITH SalesSummary AS (
    SELECT employee_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY employee_id
    HAVING SUM(amount) > 10000
)
SELECT e.name, ss.total_sales
FROM employees e
JOIN SalesSummary ss ON e.id = ss.employee_id
ORDER BY ss.total_sales DESC;

name total_sales
---------------------------------------------------------------------------------------------------- ----------------------------------------
Jack 46000.00
Alex 32000.00
name total_sales
---------------------------------------------------------------------------------------------------- ----------------------------------------
Jack 46000.00
Alex 32000.00
name total_sales
---------------------------------------------------------------------------------------------------- ----------------------------------------
Jack 46000.00
Alex 32000.00
name total_sales
---------------------------------------------------------------------------------------------------- ----------------------------------------
Jack 46000.00
Alex 32000.00
name total_sales
---------------------------------------------------------------------------------------------------- ----------------------------------------
Jack 46000.00
Alex 32000.00
name total_sales
---------------------------------------------------------------------------------------------------- ----------------------------------------
Jack 46000.00
Alex 32000.00