codingKrills

Posted on Sep 23 • Edited on Sep 26

MySQL Queries

#database #sql #postgres #tutorial

PostgreSQL Important Queries for 5+ Years Experience

1. Database Basics

\l  -- Show all databases
\c dbname;  -- Connect to a database
\dt;  -- Show all tables in current schema
\d tablename;  -- Show table structure

2. CRUD Operations

INSERT INTO users (name, email, age) VALUES ('Pulkit', 'pulkit@example.com', 28);
SELECT * FROM users;
UPDATE users SET age = 29 WHERE id = 1;
DELETE FROM users WHERE id = 1;

3. Filtering & Sorting

SELECT * FROM orders WHERE status = 'completed' ORDER BY created_at DESC;
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
SELECT * FROM users WHERE id IN (1,2,3);
SELECT * FROM customers WHERE name LIKE 'Pul%';

4. Joins

-- Inner Join
SELECT u.name, o.id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- Left Join
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- Right Join
SELECT u.name, o.id FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

-- Full Join
SELECT u.name, o.id FROM users u FULL JOIN orders o ON u.id = o.user_id;

-- Cross Join
SELECT * FROM users CROSS JOIN roles;

5. Grouping & Aggregations

SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT user_id, COUNT(*) as total_orders FROM orders GROUP BY user_id HAVING COUNT(*) > 5;

6. Subqueries

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 500);
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as total_orders FROM users u;

7. Common Table Expressions (CTEs)

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT * FROM recent_orders WHERE status = 'completed';

WITH RECURSIVE employee_hierarchy AS (
  SELECT id, manager_id, name FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e
  INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

8. Window Functions

SELECT id, user_id, amount, RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank FROM orders;
SELECT user_id, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) as running_total FROM orders;

9. Indexes

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_unique_username ON users(username);
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;
DROP INDEX idx_users_email;

10. Constraints

ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
ALTER TABLE products ADD CONSTRAINT check_price CHECK (price > 0);

11. Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

BEGIN;
DELETE FROM orders WHERE id = 100;
ROLLBACK;

12. Advanced Queries

INSERT INTO users (id, name, email) VALUES (1, 'Pulkit', 'pulkit@example.com')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

SELECT data->>'name' as username FROM users_json WHERE data->>'role' = 'admin';
SELECT * FROM posts WHERE tags @> ARRAY['nestjs'];

13. Performance Tools

EXPLAIN SELECT * FROM orders WHERE user_id = 5;
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5;
SELECT * FROM pg_stat_activity;

14. User & Role Management

CREATE USER devuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE mydb TO devuser;
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM devuser;

15. Backup & Restore

pg_dump -U username dbname > backup.sql
psql -U username dbname < backup.sql

Pro Tip: Focus on CRUD, joins, aggregation, CTEs, window functions, indexing, transactions, and JSON operations for interviews and high-scale applications.

DEV Community