SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();

SELECT order_id, order_date,       YEAR(order_date)    AS yr,       MONTH(order_date)   AS mon,       MONTHNAME(order_date) AS month_name,       DAY(order_date)     AS dy,       DAYNAME(order_date) AS weekday,       QUARTER(order_date) AS qtrFROM ordersLIMIT 4;

SELECT order_id, order_date,       DATE_ADD(order_date, INTERVAL 5 DAY)                    AS expected_delivery,       order_date + INTERVAL 5 DAY + INTERVAL 1 MONTH           AS return_deadline,       DATE_SUB(order_date, INTERVAL 1 WEEK)                    AS week_beforeFROM ordersWHERE order_id IN (1001, 1002, 1004);

SELECT first_name, hire_date,       DATEDIFF('2025-12-31', hire_date)                AS days_employed,       TIMESTAMPDIFF(MONTH, hire_date, '2025-12-31')     AS months_employed,       TIMESTAMPDIFF(YEAR, hire_date, '2025-12-31')      AS full_yearsFROM employeesORDER BY hire_dateLIMIT 5;

SELECT order_date,       DATE_FORMAT(order_date, '%d/%m/%Y')     AS indian_style,       DATE_FORMAT(order_date, '%d %b %Y')     AS short_text,       DATE_FORMAT(order_date, '%W, %M %e')    AS long_text,       DATE_FORMAT(order_date, '%Y-%m')        AS month_labelFROM ordersWHERE order_id IN (1001, 1006);

SELECT STR_TO_DATE('31/01/2026', '%d/%m/%Y') AS parsed_date;

SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,       COUNT(DISTINCT o.order_id)          AS orders,       SUM(oi.quantity * p.price)          AS revenueFROM orders oJOIN order_items oi ON oi.order_id = o.order_idJOIN products p     ON p.product_id = oi.product_idWHERE o.status <> 'Cancelled'GROUP BY monthORDER BY month;

SELECT order_id, order_dateFROM ordersWHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';

SELECT LAST_DAY('2024-02-10')                      AS last_day_feb_2024,       DAYOFWEEK('2025-03-08')                     AS day_of_week,       WEEK('2025-03-08')                          AS week_no,       DATE('2025-03-08 17:45:00')                 AS date_part,       MAKEDATE(2026, 32)                          AS day_32_of_2026;

SELECT first_name, commission,       COALESCE(commission, 0)          AS commission_or_zero,       salary + COALESCE(commission, 0) AS total_payFROM employeesWHERE dept_id IN (2, 3);

SELECT customer_name, email, city,       COALESCE(email, CONCAT('no email - ', city)) AS contactFROM customersWHERE customer_id IN (3, 4, 5);

SELECT first_name,       IFNULL(dept_id, 'none')    AS dept,       IFNULL(manager_id, 'top')  AS reports_toFROM employeesWHERE emp_id IN (100, 101, 102);

SELECT product_name, price, stock,       price / NULLIF(stock, 0) AS price_per_unit_in_stockFROM productsWHERE category = 'Electronics';

CREATE TABLE survey (respondent INT, score INT);INSERT INTO survey VALUES (1, 8), (2, 0), (3, 6), (4, 0);SELECT AVG(score) AS avg_with_zeros, AVG(NULLIF(score, 0)) AS avg_answered_onlyFROM survey;

SELECT first_name, IF(commission IS NULL, 'Salary only', 'Salary + commission') AS pay_typeFROM employeesWHERE dept_id = 2 OR emp_id = 101;

SELECT group_column, AGGREGATE(column)FROM table_name[WHERE row_condition]GROUP BY group_column[HAVING group_condition][ORDER BY ...];

SELECT dept_id,       COUNT(*)    AS staff,       SUM(salary) AS payrollFROM employeesGROUP BY dept_idORDER BY dept_id;

SELECT d.dept_name,       COUNT(*)           AS staff,       ROUND(AVG(e.salary)) AS avg_salaryFROM employees eJOIN departments d ON d.dept_id = e.dept_idGROUP BY d.dept_nameORDER BY staff DESC;

SELECT dept_id, city, COUNT(*) AS staffFROM employeesWHERE dept_id IS NOT NULLGROUP BY dept_id, cityORDER BY dept_id, city;

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,       COUNT(*) AS ordersFROM ordersGROUP BY monthORDER BY month;

SELECT p.category,       SUM(oi.quantity)           AS units,       SUM(oi.quantity * p.price) AS revenueFROM order_items oiJOIN products p ON p.product_id = oi.product_idGROUP BY p.categoryORDER BY revenue DESC;

SELECT dept_id, first_name, COUNT(*)FROM employeesGROUP BY dept_id;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'company.employees.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT dept_id, COUNT(*) AS staff, GROUP_CONCAT(first_name ORDER BY first_name) AS namesFROM employeesGROUP BY dept_id;

SELECT dept_id, COUNT(*) AS staffFROM employeesWHERE salary >= 50000GROUP BY dept_idHAVING COUNT(*) >= 2;

SELECT category, COUNT(*) AS products, SUM(stock) AS stockFROM productsGROUP BY category WITH ROLLUP;

SELECT group_column, AGGREGATE(column)FROM table_nameWHERE row_conditionGROUP BY group_columnHAVING group_condition;

SELECT customer_id, COUNT(*) AS ordersFROM ordersGROUP BY customer_idHAVING COUNT(*) > 1ORDER BY customer_id;

SELECT o.customer_id,       SUM(oi.quantity * p.price) AS spentFROM orders oJOIN order_items oi ON oi.order_id = o.order_idJOIN products p     ON p.product_id = oi.product_idWHERE o.status <> 'Cancelled'GROUP BY o.customer_idHAVING spent > 5000ORDER BY spent DESC;

SELECT dept_id, COUNT(*)FROM employeesWHERE COUNT(*) > 2GROUP BY dept_id;

ERROR 1111 (HY000): Invalid use of group function

SELECT dept_id, COUNT(*) AS well_paidFROM employeesWHERE salary >= 50000GROUP BY dept_idHAVING COUNT(*) >= 2ORDER BY dept_id;

SELECT salary, COUNT(*) AS people, GROUP_CONCAT(first_name) AS namesFROM employeesGROUP BY salaryHAVING COUNT(*) > 1;

SELECT category, ROUND(AVG(price)) AS avg_price, COUNT(*) AS productsFROM productsGROUP BY categoryHAVING AVG(price) > 3000;

INSERT INTO products (product_id, product_name, category, price, stock)VALUES (2, 'Mechanical Keyboard', 'Electronics', 3299, 60),       (10, 'Webcam HD', 'Electronics', 2499, 40) AS newON DUPLICATE KEY UPDATE  price = new.price,  stock = new.stock; SELECT product_id, product_name, price, stock FROM products WHERE product_id IN (2, 10);

CREATE TABLE page_views (page VARCHAR(40) PRIMARY KEY, views INT NOT NULL);INSERT INTO page_views VALUES ('sql-tutorial', 1) ON DUPLICATE KEY UPDATE views = views + 1;INSERT INTO page_views VALUES ('sql-tutorial', 1) ON DUPLICATE KEY UPDATE views = views + 1;INSERT INTO page_views VALUES ('sql-joins', 1)    ON DUPLICATE KEY UPDATE views = views + 1;SELECT * FROM page_views;

INSERT IGNORE INTO departments (dept_id, dept_name, location)VALUES (1, 'Engineering', 'Chennai'),       (6, 'Legal', 'Delhi');SHOW WARNINGS;SELECT * FROM departments;

REPLACE INTO customers (customer_id, customer_name, city)VALUES (6, 'Manoj Pillai', 'Kochi'); SELECT * FROM customers WHERE customer_id = 6;

REPLACE INTO customers (customer_id, customer_name, city) VALUES (1, 'Ananya Sharma', 'Pune');

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

CASE  WHEN condition1 THEN result1  WHEN condition2 THEN result2  ...  ELSE default_resultEND

SELECT first_name, salary,       CASE         WHEN salary >= 100000 THEN 'High'         WHEN salary >= 60000  THEN 'Medium'         ELSE 'Entry'       END AS salary_bandFROM employeesORDER BY salary DESC;

SELECT order_id, status,       CASE status         WHEN 'Delivered' THEN 'Closed'         WHEN 'Cancelled' THEN 'Closed'         ELSE 'Open'       END AS ticket_stateFROM orders;

SELECT first_name,       CASE         WHEN commission IS NULL THEN 'No commission'         WHEN commission >= 10000 THEN 'Top seller'         ELSE 'Seller'       END AS sales_roleFROM employeesWHERE dept_id IN (2, 3);

SELECT product_name, stock, priceFROM productsORDER BY CASE WHEN stock = 0 THEN 1 ELSE 0 END, price;

SELECT customer_id,       COUNT(*) AS orders,       SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,       SUM(CASE WHEN status <> 'Delivered' THEN 1 ELSE 0 END) AS not_deliveredFROM ordersGROUP BY customer_idORDER BY customer_id;

SELECT d.dept_name,       SUM(CASE WHEN e.city = 'Bengaluru' THEN 1 ELSE 0 END) AS bengaluru,       SUM(CASE WHEN e.city = 'Pune'      THEN 1 ELSE 0 END) AS pune,       SUM(CASE WHEN e.city NOT IN ('Bengaluru', 'Pune') THEN 1 ELSE 0 END) AS otherFROM departments dJOIN employees e ON e.dept_id = d.dept_idGROUP BY d.dept_nameORDER BY d.dept_name;

SELECT product_name, IF(stock > 0, 'In stock', 'Sold out') AS availabilityFROM products;

SELECT first_name, commissionFROM employeesWHERE commission IS NULL;

SELECT first_name, commissionFROM employeesWHERE commission IS NOT NULL;

SELECT first_nameFROM employeesWHERE commission = NULL;

SELECT NULL = NULL   AS equals,       NULL <> 1     AS not_equal,       NULL IS NULL  AS is_null,       NULL <=> NULL AS null_safe_equals;

SELECT first_name, salary, commission,       salary + commission AS total_payFROM employeesWHERE dept_id IN (1, 2)ORDER BY emp_id;

SELECT first_name, salary + COALESCE(commission, 0) AS total_payFROM employeesWHERE dept_id IN (1, 2)ORDER BY emp_id;