Basic SQL

Database and SQL

• Relational Database: Organized collection of tables.
• Table: Collection of related data organized in rows(record) and columns(field).
• Analogy: Table => Excel Sheet, Database => Excel Workbook.
• Better performance, data integrity and security as compared to storing in files.
• RDBMS: Software that manages relational databases.
• Common RDBMS: MySQL, PostgreSQL, SQLite, Oracle, SQL Server (MS-SQL).
• SQL => Structured Query Language
• SQL: Code for searching, adding, modifying and removing data stored in table.
• DBeaver: Tool to Query various databases. Download from here & install.
• Postgres: Popular open-source RDBMS. Download from here & install.

Creating Database

• We will be using PostgreSQL database.
• PostgreSQL is a powerful popular open-source RDBMS.
Connecting Database in DBeaver
1. Click on socket like icon just below File. (Shortcut: ctrl + shift + N).
2. Click PostgreSQL and click Next.
3. Fill in the connection details (Host, Port, Database, Username, Password).
4. Click Test Connection. Note: For First Time Driver will be downloaded.
5. Once connection is successful, click Finish to connect to database.
6. Right click ondatabase name => SQL Editor => New SQL Script
7. This opens SQL editor where we can write SQL queries.
8. Create database as CREATE DATABASE db_name; -- Validate with GUI

SQL Commands

Creating Tables

Syntax:
1. CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );
• Common DataTypes: INT, SERIAL, FLOAT, DATE, VARCHAR, TEXT
Example:
1. CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, salary FLOAT, created_at TIMESTAMP, is_vip BOOLEAN );

Sample Table for Mart

Constraints

• Rule that helps to maintain data integrity and consistency in table.
• If data violates constraint, it will be rejected and error will be thrown.

Creating table with Constraints

• When creating a table, constraints can be defined inline or as separate statements.
Inline Constraints Example:
1. CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, salary FLOAT CHECK (salary > 0), created_at TIMESTAMP DEFAULT NOW() );
Separate Constraints Example:
1. CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, salary FLOAT, created_at TIMESTAMP DEFAULT NOW(), CONSTRAINT chk_sal CHECK (salary > 0));
Add contraint to old table
1. ALTER TABLE employees ADD CONSTRAINT pk_employees PRIMARY KEY (id);

Drop

• Used to delete database, table or column.
Syntax:
1. DROP DATABASE database_name;
2. DROP TABLE table_name;
Example:
1. DROP TABLE IF EXISTS employees;
2. DROP DATABASE IF EXISTS company_db;
3. TRUNCATE TABLE employees; -- DROP table & re-run DDL.

Alter Table

• Used to modify structure of existing table.

Inserting Data

• Data is added in table with INSERT INTO statement.
• Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value_1_1, value_1_2, ...), (value_2_1, value_2_2, ...), ... (value_n_1, value_n_2, ...);
Example:
1. INSERT INTO employees (name, salary) VALUES ("Alice", 70000), ("Charlie", 60000);
• Validate: SELECT * FROM employees;

Updating and Deleting Data

UPDATE
1. Used to modify existing records in a table.
2. Syntax: UPDATE table_name SET col_1 = val_1, col_2 = val_2, ... , column_n = val_n WHERE condition;
3. Example: UPDATE employees SET salary = 75000 WHERE name = "Alice";
DELETE
1. Used to remove records from a table.
2. Syntax: DELETE FROM table_name WHERE condition;
3. Example: DELETE FROM employees WHERE name = "Charlie";

Retrieving Data with SELECT

• Data is retrieved from table with SELECT statement.
• Syntax: SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column ASC|DESC LIMIT number;
Example:
1. SELECT * FROM employees;
2. SELECT name, salary FROM employees;
3. SELECT * FROM employees LIMIT 10;
4. SELECT DISTINCT department FROM employees;
5. SELECT name, salary AS sl FROM employees ORDER BY 2 DESC, 1 LIMIT 5;

Filter Conditions with WHERE Clause

• WHERE clause is used to filter records based on specified conditions.
• Multiple conditions can be combined with AND / OR.
• Operators: =, !=, <, >, <=, >=, LIKE, IN, BETWEEN.
Example:
1. SELECT * FROM employees WHERE salary > 60000;
2. SELECT * FROM employees WHERE department != "HR";
3. SELECT * FROM employees WHERE salary >= 50000 AND salary < 80000;
4. SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;
5. SELECT * FROM employees WHERE name = "Alice" OR name = "Bob";
6. SELECT * FROM employees WHERE name IN ("Alice", "Bob");
7. SELECT * FROM employees WHERE name LIKE "A%";
8. SELECT * FROM employees WHERE name LIKE "%e";
9. SELECT * FROM employees WHERE department IS NULL;
• NOTE: Functions like LOWER, UPPER, LENGTH, ROUND, REPLACE, ABS, TRIM, CONCAT, IFNULL, CASE .. END etc. are available in SQL.

Read SQL table in Python

• We use psycopg2 to connect & query PostgreSQL database in Python.
• Installed as pip install psycopg2-binary.
Example:
1. import psycopg2 db_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()

Write SQL table from Python

Example:
1. import psycopg2 db_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()