data/sql.txt

# Basic SELECT
show all employees = SELECT * FROM employees;
list all departments = SELECT * FROM departments;
fetch all customers = SELECT * FROM customers;
get all orders = SELECT * FROM orders;
display all products = SELECT * FROM products;

# Filters
show employees in department 10 = SELECT * FROM employees WHERE department_id = 10;
list customers from New York = SELECT * FROM customers WHERE city = 'New York';
get products priced over 100 = SELECT * FROM products WHERE price > 100;
fetch orders with status shipped = SELECT * FROM orders WHERE status = 'shipped';
show active users = SELECT * FROM users WHERE active = 'Y';

# Aggregations
show total sales = SELECT SUM(sales_amount) FROM orders;
total number of employees = SELECT COUNT(*) FROM employees;
average salary = SELECT AVG(salary) FROM employees;
maximum order value = SELECT MAX(order_total) FROM orders;
minimum product price = SELECT MIN(price) FROM products;

# Group By
sales by region = SELECT region, SUM(sales_amount) FROM orders GROUP BY region;
employee count per department = SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
departments with more than 5 employees = SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
products with multiple suppliers = SELECT product_id FROM product_suppliers GROUP BY product_id HAVING COUNT(supplier_id) > 1;

# Order By
top 5 customers by revenue = SELECT customer_id, SUM(sales_amount) FROM orders GROUP BY customer_id ORDER BY SUM(sales_amount) DESC FETCH FIRST 5 ROWS ONLY;
recent orders = SELECT * FROM orders ORDER BY order_date DESC;
highest paid employees = SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;
cheapest products = SELECT * FROM products ORDER BY price ASC FETCH FIRST 10 ROWS ONLY;

# Joins
get employee names with department names = SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;
orders with customer names = SELECT o.order_id, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
products with category names = SELECT p.product_name, c.category_name FROM products p JOIN categories c ON p.category_id = c.category_id;
invoice details with vendor = SELECT i.invoice_id, v.vendor_name FROM invoices i JOIN vendors v ON i.vendor_id = v.vendor_id;

# Date Filters
orders in last 30 days = SELECT * FROM orders WHERE order_date >= SYSDATE - 30;
employees hired this year = SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM SYSDATE);
sales in Q1 = SELECT * FROM sales WHERE TO_CHAR(sale_date, 'Q') = '1';
payments in 2023 = SELECT * FROM payments WHERE TO_CHAR(payment_date, 'YYYY') = '2023';

# Subqueries
employees with above average salary = SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
customers with most orders = SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id ORDER BY COUNT(*) DESC FETCH FIRST 1 ROWS ONLY);
products in top selling categories = SELECT * FROM products WHERE category_id IN (SELECT category_id FROM orders GROUP BY category_id ORDER BY SUM(sales_amount) DESC FETCH FIRST 3 ROWS ONLY);

# Case Expressions
classify employees by salary = SELECT name, CASE WHEN salary > 10000 THEN 'High' WHEN salary > 5000 THEN 'Medium' ELSE 'Low' END AS salary_band FROM employees;
tag orders as urgent or normal = SELECT order_id, CASE WHEN priority = 'High' THEN 'Urgent' ELSE 'Normal' END AS priority_level FROM orders;

# Variants
who are our top buyers = SELECT customer_id, SUM(sales_amount) FROM orders GROUP BY customer_id ORDER BY SUM(sales_amount) DESC FETCH FIRST 5 ROWS ONLY;
total revenue so far = SELECT SUM(sales_amount) FROM orders;
how many departments do we have = SELECT COUNT(*) FROM departments;
list products with price > 100 = SELECT * FROM products WHERE price > 100;


# Category: Sales

total sales = SELECT SUM(sales_amount) FROM orders;
total sales by region = SELECT region, SUM(sales_amount) FROM orders GROUP BY region;
top 5 products by sales = SELECT product_name, SUM(sales_amount) FROM orders GROUP BY product_name ORDER BY SUM(sales_amount) DESC FETCH FIRST 5 ROWS ONLY;
monthly sales trend = SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(sales_amount) FROM orders GROUP BY TO_CHAR(order_date, 'YYYY-MM') ORDER BY month;
total sales this year = SELECT SUM(sales_amount) FROM orders WHERE EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM SYSDATE);

# Category: HR

employee count = SELECT COUNT() FROM employees;
employees per department = SELECT department_id, COUNT() FROM employees GROUP BY department_id;
manager-wise employee count = SELECT manager_id, COUNT(*) FROM employees GROUP BY manager_id;
average salary by job = SELECT job_id, AVG(salary) FROM employees GROUP BY job_id;
employee list by hire date = SELECT employee_id, first_name, hire_date FROM employees ORDER BY hire_date;

# Category: Finance

total expenses = SELECT SUM(expense_amount) FROM expenses;
monthly budget vs expenses = SELECT month, budget_amount, expense_amount FROM finance_summary;
profit by year = SELECT year, revenue - expenses AS profit FROM yearly_finance;
yearly revenue trend = SELECT year, SUM(revenue) FROM yearly_finance GROUP BY year ORDER BY year;
transactions above 10k = SELECT * FROM transactions WHERE amount > 10000;

# Category: Inventory

stock levels by item = SELECT item_id, item_name, quantity_in_stock FROM inventory;
reorder items = SELECT item_id, item_name FROM inventory WHERE quantity_in_stock < reorder_level;
out of stock items = SELECT item_id, item_name FROM inventory WHERE quantity_in_stock = 0;
most stocked items = SELECT item_id, quantity_in_stock FROM inventory ORDER BY quantity_in_stock DESC FETCH FIRST 5 ROWS ONLY;
items with zero usage = SELECT item_id FROM inventory WHERE usage_count = 0;

# Category: Customer

top customers by orders = SELECT customer_id, COUNT() FROM orders GROUP BY customer_id ORDER BY COUNT() DESC FETCH FIRST 5 ROWS ONLY;
customer purchase history = SELECT * FROM orders WHERE customer_id = :customer_id;
customers by city = SELECT city, COUNT() FROM customers GROUP BY city;
customer feedback count = SELECT customer_id, COUNT() FROM feedback GROUP BY customer_id;
customer with highest spend = SELECT customer_id, SUM(sales_amount) FROM orders GROUP BY customer_id ORDER BY SUM(sales_amount) DESC FETCH FIRST 1 ROWS ONLY;

# Category: Vendor

vendors by product count = SELECT vendor_id, COUNT(*) FROM vendor_products GROUP BY vendor_id;
pending vendor payments = SELECT vendor_id, SUM(amount) FROM payments WHERE status = 'PENDING' GROUP BY vendor_id;
latest vendor deliveries = SELECT * FROM vendor_deliveries ORDER BY delivery_date DESC FETCH FIRST 10 ROWS ONLY;
vendor payment history = SELECT * FROM payments WHERE vendor_id = :vendor_id;
monthly vendor expenses = SELECT TO_CHAR(payment_date, 'YYYY-MM') AS month, SUM(amount) FROM payments GROUP BY TO_CHAR(payment_date, 'YYYY-MM');

# Category: General

current system date = SELECT SYSDATE FROM dual;
row count of a table = SELECT COUNT(*) FROM :table_name;
list all tables = SELECT table_name FROM user_tables;
list all columns in a table = SELECT column_name FROM user_tab_columns WHERE table_name = UPPER(:table_name);
list distinct job titles = SELECT DISTINCT job_id FROM employees;

# SQL : Introduction and Features

what is oracle sql, May I know about sql, what actually the SQL is, Is SQL worthy = Oracle SQL is the specific implementation of the SQL (Structured Query Language) standard used for interacting with Oracle Database. It's a language for defining, accessing, and managing data within Oracle's database system. While based on the ANSI/ISO SQL standard, Oracle SQL includes its own extensions and features. Here's a more detailed.\\n\\nExplanation:\\n\\nSQL as a Standard:\\nSQL is a standardized language for interacting with relational databases. It allows users to perform various operations like querying data, inserting, updating, and deleting records, creating database objects, and managing access control.\\n\\nOracle's Implementation:\\nOracle Database uses SQL as its primary language for all data manipulation and management tasks.\\n\\nExtensions and Features:\\nOracle SQL includes extensions to the standard SQL language, providing additional functionality and features specific to Oracle Database.\\n\\nTools and Applications:\\nOracle provides various tools and applications, like SQL Developer and SQL*Plus, that utilize Oracle SQL to interact with the database  SQL FILE.


basic sql query = SELECT column1, column2 FROM table_name;
simple sql structure = SELECT * FROM table_name;
i need a simple sql query = SELECT * FROM your_table;
i need simple sql query structure = SELECT column1, column2 FROM table_name WHERE condition;


basic sql query = SELECT column1, column2 FROM table_name;
simple select query = SELECT * FROM employees;
sql insert template = INSERT INTO table_name (column1, column2) VALUES (value1, value2);
sql update statement = UPDATE table_name SET column1 = value1 WHERE condition;
sql delete syntax = DELETE FROM table_name WHERE condition;
create table example = CREATE TABLE table_name (id INT PRIMARY KEY, name VARCHAR(100));
select with where clause = SELECT * FROM orders WHERE order_date > TO_DATE('2024-01-01','YYYY-MM-DD');
sql join example = SELECT a.name, b.salary FROM employees a JOIN salaries b ON a.id = b.emp_id;
inner join structure = SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.ref_id;
left join sample = SELECT * FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;
group by query = SELECT department, COUNT(*) FROM employees GROUP BY department;
order by usage = SELECT * FROM students ORDER BY score DESC;
--sql like example = SELECT * FROM products WHERE name LIKE '%phone%' SQL FILE;

#FINANCE
total revenue by month = SELECT TO_CHAR(invoice_date, 'YYYY-MM') AS month, SUM(amount) FROM invoices GROUP BY TO_CHAR(invoice_date, 'YYYY-MM');
list all overdue payments = SELECT * FROM payments WHERE due_date < SYSDATE AND status != 'Paid';

#HR
employee salary by department = SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
find all employees hired last year = SELECT * FROM employees WHERE hire_date BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE;

#SALES
top 5 selling products = SELECT product_name, SUM(quantity_sold) FROM sales GROUP BY product_name ORDER BY SUM(quantity_sold) DESC FETCH FIRST 5 ROWS ONLY;
sales by region and month = SELECT region, TO_CHAR(sale_date, 'YYYY-MM') AS month, SUM(amount) FROM sales GROUP BY region, TO_CHAR(sale_date, 'YYYY-MM');


### CATEGORY: Sales
Show total sales amount per region = SELECT region, SUM(sales_amount) AS total_sales FROM sales GROUP BY region;
List top 5 selling products by revenue = SELECT product_name, SUM(sales_amount) AS revenue FROM sales GROUP BY product_name ORDER BY revenue DESC FETCH FIRST 5 ROWS ONLY;


### CATEGORY: HR
Find all employees hired in the year 2023 = SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) = 2023;
Count of employees in each department = SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;

### CATEGORY: Finance
Show monthly expenses for the year 2024 = SELECT TO_CHAR(expense_date, 'YYYY-MM') AS month, SUM(amount) AS total_expenses FROM expenses WHERE EXTRACT(YEAR FROM expense_date) = 2024 GROUP BY TO_CHAR(expense_date, 'YYYY-MM') ORDER BY month;

List all vendors with payments above 10,000 = SELECT vendor_name, SUM(payment_amount) AS total_paid FROM payments GROUP BY vendor_name HAVING SUM(payment_amount) > 10000;
### CATEGORY: Inventory
Show out-of-stock items = SELECT item_name FROM inventory WHERE stock_quantity = 0;

List top 3 warehouses by item count = SELECT warehouse_id, COUNT(*) AS item_count FROM inventory GROUP BY warehouse_id ORDER BY item_count DESC FETCH FIRST 3 ROWS ONLY;

top 10 employees = A: SELECT employee_id, employee_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;

If the employees table is dropped what happen with table procedure = procedure becomes invalid.