SELECT * EXCEPT (column_name) FROM table_name;
How can you select the top 10% of rows from a table?
SELECT * FROM table_name ORDER BY column_name DESC LIMIT (SELECT COUNT(*) FROM table_name) * 0.1;
How can you find the second highest salary in a table?
SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees);
How can you find the difference between the highest and lowest salary in a table?
SELECT MAX(salary) - MIN(salary) FROM employees;
How can you select the most common value in a column?
SELECT column_name, COUNT(*) as frequency FROM table_name GROUP BY column_name ORDER BY frequency DESC LIMIT 1;
How can you select the nth highest salary in a table?
SELECT salary FROM (SELECT DISTINCT salary FROM employees ORDER BY salary DESC) WHERE ROWNUM = n;
Please note that the above solutions may not work in all SQL implementations, as the syntax can vary between different database management systems
How can you select all the duplicate rows from a table?
SELECT column1, column2, ..., column_n FROM table_name GROUP BY column1, column2, ..., column_n HAVING COUNT(*) > 1;
How can you find the number of unique values in a column?
SELECT COUNT(DISTINCT column_name) FROM table_name;
How can you select the most recent record from a table for each unique value in a specific column?
SELECT a.* FROM table_name a INNER JOIN (SELECT column_name, MAX(date_column) as max_date FROM table_name GROUP BY column_name) b ON a.column_name = b.column_name AND a.date_column = b.max_date;
How can you find the total sum of a column for each unique value in another column?
SELECT column1, SUM(column2) as total_sum FROM table_name GROUP BY column1;
How can you select the average salary for each job title in a table?
SELECT job_title, AVG(salary) as avg_salary FROM employees GROUP BY job_title;
How can you select the top 3 most populous cities from a table?
SELECT city, population FROM cities ORDER BY population DESC LIMIT 3
How can you select all the customers who have placed an order in the last 30 days?
SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE customers.customer_id = orders.customer_id AND order_date >= DATEADD(day,-30,GETDATE()));
How can you select all the employees who have no manager?
SELECT * FROM employees WHERE manager_id IS NULL;
How can you select the count of all the employees grouped by department?
SELECT department, COUNT(*) FROM employees GROUP BY department;
How can you select the most recent order for each customer?
SELECT customer_id, MAX(order_date) as last_order_date FROM orders GROUP BY customer_id;
How can you select all the products that have been ordered at least 5 times?
SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM orders GROUP BY product_id HAVING COUNT(*) >= 5);
How can you select the average salary for each department and job title?
SELECT department, job_title, AVG(salary) as avg_salary FROM employees GROUP BY department, job_title;
How can you select all the products that are out of stock?
SELECT product_name FROM products WHERE stock_quantity = 0;
How can you select all the products that have a price that is greater than the average price of all products?
SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products);
How can you select all the customers who have placed an order with a total price greater than $100?
SELECT customers.* FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN order_items ON orders.order_id = order_items.order_id GROUP BY customers.customer_id HAVING SUM(order_items.price*order_items.quantity) > 100;
How can you select the most recent order for each product?
SELECT product_id, MAX(order_date) as last_order_date FROM orders JOIN order_items ON orders.order_id = order_items.order_id GROUP BY product_id;
How can you select the top 3 selling products?
SELECT product_id, SUM(quantity) as total_quantity FROM order_items GROUP BY product_id ORDER BY total_quantity DESC LIMIT 3;
How can you select all the customers who have placed an order in the last 30 days and have spent more than $200 in total?
SELECT customers.* FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN order_items ON orders.order_id = order_items.order_id WHERE orders.order_date >= DATEADD(day,-30,GETDATE()) GROUP BY customers.customer_id HAVING SUM(order_items.price*order_items.quantity) > 200;
How can you select all the products that are out of stock and have not been ordered in the last 90 days?
SELECT product_name FROM products WHERE stock_quantity = 0 AND product_id NOT IN (SELECT product_id FROM orders JOIN order_items ON orders.order_id = order_items.order_id WHERE order_date >= DATEADD(day,-90,GETDATE()))
How can you select the average salary for each department, job title and the gender of the employees?
SELECT department, job_title, gender, AVG(salary) as avg_salary FROM employees GROUP BY department, job_title, gender;
How can you select the most recent order for each customer, including the total price of the order?
SELECT customers.customer_id, MAX(orders.order_date) as last_order_date, SUM(order_items.price*order_items.quantity) as total_price FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN order_items ON orders.order_id = order_items.order_id GROUP BY customers.customer_id
0 Comments