Command_II.sql

/*
==================================================
   BASIC SELECT QUERIES
   ================================================== */

-- Select specific column
SELECT name
FROM customers;

-- Select all columns
SELECT *
FROM customers;

-- Select unique values
SELECT DISTINCT name
FROM customers;

-- Copy data into a new table
SELECT *
INTO customers
FROM customers_backup;

-- Select top N rows
SELECT TOP 50 *
FROM customers;

-- Select top percentage
SELECT TOP 50 PERCENT *
FROM customers;

/* ==================================================
   ALIASES
   ================================================== */

-- Rename column using alias
SELECT name AS first_name
FROM customers;

/* ==================================================
   FILTERING DATA
   ================================================== */

-- WHERE clause
SELECT name
FROM customers
WHERE name = 'Bob';

-- AND condition
SELECT name
FROM customers
WHERE name = 'Bob'
  AND age = 55;

-- OR condition
SELECT name
FROM customers
WHERE name = 'Bob'
   OR age = 55;

-- BETWEEN range
SELECT name
FROM customers
WHERE age BETWEEN 45 AND 55;

-- LIKE pattern matching
SELECT name
FROM customers
WHERE name LIKE '%Bob%';

-- IN multiple values
SELECT name
FROM customers
WHERE name IN ('Bob', 'Fred', 'Harry');

-- NULL checks
SELECT name
FROM customers
WHERE name IS NULL;

SELECT name
FROM customers
WHERE name IS NOT NULL;

/* ==================================================
   CREATE STATEMENTS
   ================================================== */

-- Create database
CREATE DATABASE dataquestDB;

-- Create table
CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(255),
    age INT
);

-- Create index
CREATE INDEX idx_name
ON customers (name);

-- Create view
CREATE VIEW bob_customers AS
SELECT name, age
FROM customers
WHERE name = 'Bob';

/* ==================================================
   DROP STATEMENTS (USE WITH CAUTION)
   ================================================== */

-- Drop database
DROP DATABASE dataquestDB;

-- Drop table
DROP TABLE customers;

-- Drop index
DROP INDEX idx_name;

/* ==================================================
   UPDATE & DELETE
   ================================================== */

-- Update records
UPDATE customers
SET age = 56
WHERE name = 'Bob';

-- Delete specific records
DELETE FROM customers
WHERE name = 'Bob';

/* ==================================================
   ALTER TABLE
   ================================================== */

-- Add column
ALTER TABLE customers
ADD surname VARCHAR(255);

-- Remove column
ALTER TABLE customers
DROP COLUMN surname;

/* ==================================================
   AGGREGATE FUNCTIONS
   ================================================== */

-- Count rows
SELECT COUNT(*)
FROM customers;

-- Sum values
SELECT SUM(age)
FROM customers;

-- Average
SELECT AVG(age)
FROM customers;

-- Minimum
SELECT MIN(age)
FROM customers;

-- Maximum
SELECT MAX(age)
FROM customers;

/* ==================================================
   GROUPING & FILTERING AGGREGATES
   ================================================== */

-- Group by
SELECT name, AVG(age)
FROM customers
GROUP BY name;

-- Having clause
SELECT COUNT(customer_id), name
FROM customers
GROUP BY name
HAVING COUNT(customer_id) > 2;

/* ==================================================
   SORTING & PAGINATION
   ================================================== */

-- Order by ascending
SELECT name
FROM customers
ORDER BY age;

-- Order by descending
SELECT name
FROM customers
ORDER BY age DESC;

-- Offset rows
SELECT name
FROM customers
ORDER BY age
OFFSET 10 ROWS;

-- Fetch rows
SELECT name
FROM customers
ORDER BY age
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

/* ==================================================
   JOINS
   ================================================== */

-- Inner join
SELECT name
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

-- Left join
SELECT name
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

-- Right join
SELECT name
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

-- Full outer join
SELECT name
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

/* ==================================================
   SUBQUERIES
   ================================================== */

-- Exists
SELECT name
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
);

/* ==================================================
   PERMISSIONS
   ================================================== */

-- Grant permissions
GRANT SELECT, UPDATE
ON customers
TO usr_bob;

-- Revoke permissions
REVOKE SELECT, UPDATE
ON customers
FROM usr_bob;

/* ==================================================
   TRANSACTIONS
   ================================================== */

-- Savepoint
SAVEPOINT before_delete;

-- Commit transaction
COMMIT;

-- Rollback
ROLLBACK TO before_delete;

/* ==================================================
   TRUNCATE
   ================================================== */

-- Remove all records, keep table
TRUNCATE TABLE customers;

/* ==================================================
   UNION
   ================================================== */

-- Union (removes duplicates)
SELECT name FROM customers
UNION
SELECT name FROM orders;

-- Union all (keeps duplicates)
SELECT name FROM customers
UNION ALL
SELECT name FROM orders;