SQL in Python

1. 🗄️ Database and SQL

1.1 What is a Database?
1. A database is an organized collection of data. It helps us store, manage, search, update, and delete data easily. Example: A shop may store: - Products - Customers - Employees - Sales records Instead of storing all this information in many text files or Excel files, we can store it in a database.
1.2 What is a Relational Database?
1. A relational database stores data in tables. A table contains rows and columns. In simple words: - Database ➜ Collection of tables - Table ➜ Collection of related data - Row ➜ One record - Column ➜ One field or attribute Example: Database: Retail Store Database Tables: - sales - employees - products - customers
1.3 Database Analogy
1. Imagine an Excel workbook. An Excel workbook can contain many sheets. Each sheet contains rows and columns. Database works in a similar way. Analogy: - Database ➜ Excel Workbook - Table ➜ Excel Sheet - Row ➜ One record - Column ➜ One field

1.4 Why Use Database Instead of Files?
1. Databases are useful because they provide: ✅ Better data organization ✅ Faster searching and filtering ✅ Better data integrity ✅ Better security ✅ Easier update and delete operations ✅ Ability to handle large data more efficiently Example: If we store employee information in a text file, searching and updating records can become difficult. But in a database, we can write a simple SQL query to find or update records.
1.5 What is RDBMS?
1. RDBMS stands for Relational Database Management System. An RDBMS is software used to manage relational databases. Common RDBMS examples: - SQLite - MySQL - PostgreSQL - Oracle - SQL Server
1.6 What is SQL?
1. SQL stands for Structured Query Language. SQL is used to communicate with databases. We use SQL to: - Create tables - Insert data - Read data - Update data - Delete data Example: SELECT * FROM employees; This query gets all records from the employees table.
1.7 What is DBeaver?
1. DBeaver is a free database tool. It helps us connect to databases and write SQL queries. We can use DBeaver to work with databases like: - SQLite - MySQL - PostgreSQL - Oracle - SQL Server Click here to download DBeaver

2. 🧱 Creating SQLite Database

2.1 What is SQLite?
1. SQLite is a lightweight, file-based database. It stores the whole database inside a single file. SQLite is easy to set up and good for learning SQL. In this note, we will use SQLite. Example database file: database.db 📌 SQLite is good for learning, small projects, desktop apps, and local data storage.
2.2 Creating SQLite Database File
1. Steps: 1. Open your project folder. 2. Create a file named database.db. 3. This file will store our SQLite database. Example: database.db 📌 If the database file does not exist, SQLite can create it automatically when we connect to it.
2.3 Connecting SQLite Database in DBeaver
1. Steps: 1. Open DBeaver. 2. Click the New Database Connection icon. Shortcut: Ctrl + Shift + N 3. Select SQLite. 4. Click Next. 5. Click Open. 6. Select database.db file. 7. Click Test Connection. 8. If this is the first time, DBeaver may download the required driver. 9. After successful connection, click Finish. 10. Right-click the database name. 11. Go to SQL Editor. 12. Click New SQL Script. 13. Write and run SQL queries in the SQL editor.

3. 🧾 Sample Tables

3.1 Sales Table

3.2 Employees Table

4. 🏗️ Creating Tables

4.1 What is CREATE TABLE?
1. CREATE TABLE is used to create a new table in a database. Syntax: CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype );
4.2 Common SQLite Data Types
1. Common SQLite data types: INTEGER: Used for whole numbers. REAL: Used for decimal numbers. TEXT: Used for text/string values. BLOB: Used for binary data like files or images. NULL: Used when value is missing or unknown.
4.3 Creating employees Table
1. Example: CREATE TABLE employees (id INTEGER,name TEXT,salary REAL,department TEXT );
4.4 Creating sales Table
1. Example: CREATE TABLE sales (id INTEGER,txn_date TEXT,customer_name TEXT,product_name TEXT,quantity INTEGER,price REAL );

5. ➕ Inserting Data

5.1 What is INSERT INTO?
1. INSERT INTO is used to add new records into a table. Syntax: INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
5.2 Insert One Record
1. Example: INSERT INTO employees (name, salary, department) VALUES ("Alice", 70000, "IT");
5.3 Insert Multiple Records
1. Example: INSERT INTO employees (name, salary, department) VALUES("Diana", 65000, "Finance"),("Charlie", 60000, "Sales"),("Bob", 55000, "Marketing");
5.4 Insert Sales Records
1. Example: INSERT INTO sales (txn_date, customer_name, product_name, quantity, price) VALUES("2023-01-01", "Alice", "Laptop", 1, 999.99),("2023-01-02", "Bob", "Smartphone", 2, 499.99),("2023-01-03", "Charlie", "Headphones", 3, 199.99);
5.5 Validate Inserted Data
1. Example: SELECT * FROM employees;Example: SELECT * FROM sales;

6. 📖 Retrieving Data with SELECT

6.1 What is SELECT?
1. SELECT is used to retrieve data from a table. Syntax: SELECT column1, column2 FROM table_name;
6.2 Select All Columns
1. Example: SELECT * FROM employees; Here: * means all columns.
6.3 Select Specific Columns
1. Example: SELECT name, salary FROM employees;
6.4 LIMIT
1. LIMIT is used to show only a fixed number of records. Example: SELECT * FROM employees LIMIT 10;
6.5 ORDER BY
1. ORDER BY is used to sort records. Example: SELECT name, salary FROM employees ORDER BY salary DESC; Here: DESC means descending order. ASC means ascending order.
6.6 ORDER BY with LIMIT
1. Example: SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5; This shows top 5 employees by salary.

7. 🔍 Filtering Data with WHERE

7.1 What is WHERE?
1. WHERE is used to filter records based on condition. Syntax: SELECT column1, column2 FROM table_name WHERE condition;
7.2 Common WHERE Operators
1. Common operators: = Equal to != Not equal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to LIKE Pattern matching IN Match any value from a list BETWEEN Match value between two values
7.3 Simple WHERE Examples
1. Example: SELECT * FROM employees WHERE salary > 60000;Example: SELECT * FROM employees WHERE department != "HR";
7.4 AND Condition
1. AND is used when all conditions must be true. Example: SELECT * FROM employees WHERE salary >= 50000 AND salary < 80000;
7.5 BETWEEN Condition
1. BETWEEN is used to filter values between two values. Example: SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;
7.6 OR Condition
1. OR is used when at least one condition should be true. Example: SELECT * FROM employees WHERE name = "Alice" OR name = "Bob";
7.7 IN Condition
1. IN is used to match values from a list. Example: SELECT * FROM employees WHERE name IN ("Alice", "Bob");
7.8 LIKE Condition
1. LIKE is used for pattern matching. Example: SELECT * FROM employees WHERE name LIKE "A%"; This finds names starting with A. Example: SELECT * FROM employees WHERE name LIKE "%e"; This finds names ending with e. Example: SELECT * FROM employees WHERE name LIKE "%li%"; This finds names containing li.

8. ✏️ Updating Data

8.1 What is UPDATE?
1. UPDATE is used to modify existing records in a table. Syntax: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
8.2 Update Example
1. Example: UPDATE employees SET salary = 75000 WHERE name = "Alice";
8.3 Increase Salary by Percentage
1. Example: UPDATE employees SET salary = salary * 1.10; This increases salary of all employees by 10%.
8.4 Important Note
1. 📌 Before running UPDATE, it is a good habit to run SELECT with the same WHERE condition to check which rows will be updated. Without WHERE, all rows may be updated. Example: UPDATE employees SET salary = 75000; This changes salary of every employee.

9. 🗑️ Deleting Data

9.1 What is DELETE?
1. DELETE is used to remove records from a table. Syntax: DELETE FROM table_name WHERE condition;
9.2 Delete Example
1. 📌 Before running DELETE, it is a good habit to run SELECT with the same WHERE condition to check which rows will be deleted. Example: DELETE FROM employees WHERE name = "Charlie";
9.3 Important Note
1. Always use WHERE when deleting specific records. Without WHERE, all rows may be deleted. Example: DELETE FROM employees; This deletes all records from employees table.

10. 🧰 Useful SQL Functions

10.1 Text Functions
1. LOWER(): Converts text to lowercase. Example: SELECT LOWER(name) FROM employees;UPPER(): Converts text to uppercase. Example: SELECT UPPER(name) FROM employees;LENGTH(): Returns length of text. Example: SELECT name, LENGTH(name) FROM employees;TRIM(): Removes extra spaces from beginning and end. Example: SELECT TRIM(name) FROM employees;REPLACE(): Replaces text. Example: SELECT REPLACE(name, "a", "@") FROM employees;
10.2 Number Functions
1. ROUND(): Rounds decimal number. Example: SELECT ROUND(price, 2) FROM sales;ABS(): Returns positive value. Example: SELECT ABS(-10);
10.3 NULL Handling Function
1. IFNULL(): Returns alternative value if value is NULL. Example: SELECT IFNULL(department, "Not Assigned") FROM employees;
10.4 Note about CONCAT
1. Some SQL databases support CONCAT(). SQLite usually uses || for string joining. Example: SELECT name || " - " || department FROM employees;

11. 🐍 Reading SQL Table in Python

11.1 sqlite3 Library
1. Python has a built-in sqlite3 library. It allows us to connect Python with SQLite database. No installation is required.
11.2 Reading Data from SQLite
1. Example: import sqlite3db_path = "database.db" conn = sqlite3.connect(db_path) cursor = conn.cursor() cursor.execute("SELECT * FROM employees;") results = cursor.fetchall() print(results) cursor.close() conn.close()
11.3 Reading Rows as Dictionary-like Objects
1. Example: import sqlite3db_path = "database.db" conn = sqlite3.connect(db_path) conn.row_factory = sqlite3.Row cursor = conn.cursor() cursor.execute("SELECT * FROM employees;") results = cursor.fetchall()for row in results:print(row["name"], row["salary"])cursor.close() conn.close()

12. 🐍 Writing SQL Table from Python

12.1 Creating Table from Python
1. Example: import sqlite3conn = sqlite3.connect("database.db") cursor = conn.cursor()cursor.execute(""" CREATE TABLE products (id INTEGER,name TEXT,price REAL ); """)conn.commit() cursor.close() conn.close()
12.2 Inserting Data from Python
1. Example: import sqlite3conn = sqlite3.connect("database.db") cursor = conn.cursor()data_to_add = [("Laptop", 999.99),("Smartphone", 499.99),("Headphones", 199.99) ]cursor.executemany("INSERT INTO products (name, price) VALUES (?, ?);",data_to_add )conn.commit() cursor.close() conn.close()
12.3 Why Use ? Placeholder?
1. In Python sqlite3, we should use ? placeholders when inserting values. Good: cursor.execute("INSERT INTO products (name, price) VALUES (?, ?);",("Mouse", 25.5) ) Avoid directly joining values into SQL strings. Placeholders help avoid errors and make queries safer.