Basic SQL

2. Creating and Connecting a Database

2.1 🐘 PostgreSQL Database
1. We will use PostgreSQL as our database. PostgreSQL is useful for learning SQL because it is powerful, popular, and open-source.
2.2 🔌 Connecting PostgreSQL in DBeaver
1. Steps: 1. Open DBeaver 2. Click the New Database Connection icon. Shortcut: Ctrl + Shift + N 3. Select PostgreSQL 4. Click Next 5. Fill in connection details: Host, Port, Database, Username, Password 6. Click Test Connection 7. If the connection is successful, click Finish 8. Right-click the database name 9. Go to SQL Editor → New SQL Script 10. Write SQL queries in the SQL editor 📌 Note: DBeaver may download the PostgreSQL driver when you test the connection for the first time.
2.3 Creating a Database
1. CREATE DATABASE company_db; 📌 A database is the container where we create tables and store data.

3. SQL Command Categories

3.1 📚 Types of SQL Commands
1. SQL commands are grouped based on what they do. DDL commands are used to change table structure, such as creating tables, deleting tables, adding/removing fields, or renaming fields. DML commands are used to change data, such as adding new records, removing records, or changing values in records. DQL commands are used to read data from tables based on a search condition. DCL commands are used to control access and permissions.

4. Creating Tables

4.1 🧱 What is a Table?
1. A table is an entity that stores related data in rows and columns.

4.2 Syntax
1. CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype );

4.4 Example: Create Employees Table
1. CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(255),salary FLOAT,joined_date DATE,is_active BOOLEAN ); 📌 CREATE TABLE creates the structure of a table. 📌 PRIMARY KEY is a constraint — explained in next section.

5. SQL Constraints

5.1 🔒 What are Constraints?
1. Constraints are rules applied to table columns. They help keep data: ✅ Correct ✅ Consistent ✅ Safe ✅ Meaningful If data breaks a constraint, PostgreSQL rejects it.

5.3 Inline Constraint Example
1. CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(255) NOT NULL,salary FLOAT CHECK (salary > 0),created_at TIMESTAMP DEFAULT NOW() ); 📌 NOW() is a built-in function that returns the current date and time.
5.4 Named Constraint Example
1. CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(255) NOT NULL,salary FLOAT,created_at TIMESTAMP DEFAULT NOW(),CONSTRAINT chk_salary CHECK (salary > 0) );
5.5 Add Constraint to Existing Table
1. ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0); 📌 Constraints protect the table from bad data.

6. DROP, TRUNCATE, and ALTER

6.1 🗑️ DROP
1. DROP deletes a database or table completely. DROP TABLE employees; Safer version: DROP TABLE IF EXISTS employees; Drop a database: DROP DATABASE IF EXISTS company_db; ⚠️ Warning: DROP removes the structure and data.
6.2 🧹 TRUNCATE
1. TRUNCATE removes all rows from a table but keeps the table structure. TRUNCATE TABLE employees; 📌 Simple difference: DROP TABLE — Deletes the table itself TRUNCATE TABLE — Deletes all rows but keeps the table

6.3 🛠️ ALTER TABLE
1. ALTER TABLE changes the structure of an existing table.

7. Inserting Data

7.1 ➕ INSERT INTO
1. INSERT INTO adds data into a table. We specify the table name, columns, and values to insert. It is okay not to provide values for all columns if they have default values or allow NULL. The sequence of columns and values must match.
7.2 Syntax
1. INSERT INTO table_name (column1, column2) VALUES(value1, value2),(value3, value4); 📌 value1 and value3 are inserted into column1 📌 value2 and value4 are inserted into column2
7.3 Example
1. INSERT INTO employees (name, salary) VALUES('Alice', 70000),('Charlie', 60000); ⚠️ PostgreSQL note: Text values should use single quotes, like 'Alice', not double quotes.
7.4 Validate Inserted Data
1. SELECT * FROM employees; 📌 Simple idea: Insert first, then verify using SELECT.

8. Updating and Deleting Data

8.1 ✏️ UPDATE
1. UPDATE modifies existing records. Syntax UPDATE table_name SET column_name = new_value WHERE condition;Example UPDATE employees SET salary = 75000 WHERE name = 'Alice';UPDATE employees SET salary = 80000, is_active = FALSE WHERE id = 1; ⚠️ Important: Always use WHERE carefully. Without WHERE, all rows may be updated.
8.2 ❌ DELETE
1. DELETE removes records from a table. Syntax DELETE FROM table_name WHERE condition;Example DELETE FROM employees WHERE name = 'Charlie'; ⚠️ Important: Without WHERE, all rows may be deleted.

9. Retrieving Data with SELECT

9.1 🔍 SELECT
1. SELECT is used to retrieve data from a table. We can choose specific columns, filter rows, sort results, and limit the number of rows returned.
9.2 Basic Syntax
1. SELECT column1, column2 FROM table_name WHERE condition ORDER BY column ASC LIMIT number; 📌 WHERE, ORDER BY, and LIMIT are optional depending on what we need.
9.3 Examples
1. Select all columns: SELECT * FROM employees; Select specific columns: SELECT name, salary FROM employees; Limit number of rows: SELECT * FROM employees LIMIT 10; Select unique values: SELECT DISTINCT department FROM employees; Rename output column: SELECT name, salary AS sl FROM employees; Sort results: SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5; 📌 Simple idea: SELECT is used when we want to read data.

10. Filtering Data with WHERE

10.1 🎯 WHERE Clause
1. WHERE filters records based on conditions. It works like filtering in Python or NumPy.

10.3 Examples
1. Salary greater than 60000: SELECT * FROM employees WHERE salary > 60000; Department not equal to HR: SELECT * FROM employees WHERE department != 'HR'; Salary between two values: SELECT * FROM employees WHERE salary >= 50000 AND salary < 80000; Using BETWEEN: SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000; 📌 Note: BETWEEN includes both boundary values. Name is Alice or Bob: SELECT * FROM employees WHERE name = 'Alice' OR name = 'Bob'; Using IN: SELECT * FROM employees WHERE name IN ('Alice', 'Bob'); Names starting with A: SELECT * FROM employees WHERE name LIKE 'A%'; Names ending with e: SELECT * FROM employees WHERE name LIKE '%e'; Department is missing: SELECT * FROM employees WHERE department IS NULL; 📌 WHERE helps us select only the rows we need.

11. Useful SQL Functions

11.1 Common Functions
1. SQL has useful functions to operate on text and numbers. We can convert case, find length, round numbers, replace text, and more. Some popular functions and their descriptions are listed below:

12. Read SQL Table in Python

12.1 🐍 Why Connect SQL with Python?
1. In Data Science, we often read data from a database into Python for analysis. We can use psycopg2 to connect Python with PostgreSQL. Install it using: pip install psycopg2-binary
12.2 Example: Read Data
1. import psycopg2db_config = {"host": "localhost","dbname": "your_db","user": "your_user","password": "your_password" }conn = psycopg2.connect(**db_config) cursor = conn.cursor()cursor.execute("SELECT * FROM employees;") results = cursor.fetchall()print(results)cursor.close() conn.close() 📌 Simple idea: Python sends SQL queries to PostgreSQL and receives the result.

13. Write SQL Table from Python

13.1 Example: Create Table and Insert Data
1. import psycopg2db_config = {"host": "localhost","dbname": "your_db","user": "your_user","password": "your_password" }conn = psycopg2.connect(**db_config) cursor = conn.cursor()# Create table cursor.execute(""" CREATE TABLE IF NOT EXISTS products (name TEXT,price REAL ); """)# Insert data data_to_add = [("Laptop", 999.99),("Smartphone", 499.99),("Headphones", 199.99) ]cursor.executemany("INSERT INTO products (name, price) VALUES (%s, %s);",data_to_add )conn.commit()cursor.close() conn.close() 📌 conn.commit() saves the transaction, similar to the SQL COMMIT command.