Learn SQL

SQL (Structured Query Language) is a domain-specific language designed for managing and querying relational databases. Created in the 1970s, SQL has become the standard language for database operations and is used by virtually every database system.

SQL allows you to create, read, update, and delete data in databases. It's essential for developers, data analysts, database administrators, and anyone who works with data. SQL is used across industries and is a fundamental skill for backend development, data science, business intelligence, and more.

-- Your first SQL query
SELECT 'Hello, World!' AS greeting;

-- Select from a table
SELECT name, email 
FROM users 
WHERE age > 18;

SQL syntax is clean and readable. Understanding these fundamental rules will help you write SQL code effectively.

if x > 0:
print("Positive")  # IndentationError!
    print("Still positive")

if x > 0:
    print("Positive")  # Correct!
    print("Still positive")

SQL is dynamically typed, meaning you don't need to declare variable types. SQL automatically determines the type based on the value assigned.

Basic Data Types

age = 30
count = -5
big_number = 1000000

price = 19.99
pi = 3.14159
temperature = -5.5

name = "SQL"
message = 'Hello, World!'
multiline = """This is
a multiline
string"""

is_active = True
is_complete = False
has_permission = True

fruits = ["apple", "banana", "orange"]
numbers = [1, 2, 3, 4, 5]
mixed = [1, "hello", True, 3.14]

person = {
    "name": "John",
    "age": 30,
    "city": "NYC"
}

coordinates = (10, 20)
colors = ("red", "green", "blue")
single = (42,)  # Note the comma

result = None
value = None

SQL supports various operators for performing operations on variables and values.

Arithmetic Operators

Comparison Operators

x = 5
y = 10

print(x == y)  # False (equal)
print(x != y)  # True (not equal)
print(x < y)   # True (less than)
print(x > y)   # False (greater than)
print(x <= y)  # True (less than or equal)
print(x >= y)  # False (greater than or equal)

Logical Operators

a = True
b = False

print(a and b)  # False
print(a or b)   # True
print(not a)    # False

Control flow statements allow you to control the execution order of your code. SQL supports if/else, loops, and more.

If/Else Statements

age = 20

if age >= 18:
    print("You are an adult")
elif age >= 13:
    print("You are a teenager")
else:
    print("You are a child")

For Loops

# Iterate over a list
fruits = ["apple", "banana", "orange"]
for fruit in fruits:
    print(fruit)

# Iterate with index
for i, fruit in enumerate(fruits):
    print(f"{i}: {fruit}")

# Range function
for i in range(5):
    print(i)  # 0, 1, 2, 3, 4

While Loops

count = 0
while count < 5:
    print(f"Count: {count}")
    count += 1

# Break and continue
while True:
    user_input = input("Enter 'quit' to exit: ")
    if user_input == 'quit':
        break
    if user_input == 'skip':
        continue
    print(f"You entered: {user_input}")

Functions are reusable blocks of code that perform a specific task. They help organize code and avoid repetition.

Defining Functions

# Simple function
def greet(name):
    return f"Hello, {name}!"

# Function with default parameters
def greet_with_title(name, title="Mr."):
    return f"Hello, {title} {name}!"

# Function with multiple return values
def get_name_and_age():
    return "John", 30

name, age = get_name_and_age()

Lambda Functions

# Lambda (anonymous) function
square = lambda x: x ** 2
print(square(5))  # 25

# Used with map, filter, etc.
numbers = [1, 2, 3, 4, 5]
squared = list(map(lambda x: x ** 2, numbers))
# [1, 4, 9, 16, 25]

Lists and dictionaries are SQL's most commonly used data structures for storing collections of data.

Lists

# Create a list
fruits = ["apple", "banana", "orange"]

# Access elements
print(fruits[0])      # "apple"
print(fruits[-1])     # "orange" (last item)

# Modify list
fruits.append("grape")  # Add item
fruits.remove("banana") # Remove item
fruits.insert(1, "mango")  # Insert at index

# List methods
fruits.sort()         # Sort alphabetically
fruits.reverse()      # Reverse order
length = len(fruits)  # Get length

Dictionaries

# Create a dictionary
person = {
    "name": "John",
    "age": 30,
    "city": "NYC"
}

# Access values
print(person["name"])        # "John"
print(person.get("age"))    # 30
print(person.get("email", "N/A"))  # "N/A" (default)

# Modify dictionary
person["email"] = "john@example.com"  # Add/update
person.pop("age")  # Remove key
person.clear()     # Clear all items

# Dictionary methods
keys = person.keys()    # Get all keys
values = person.values()  # Get all values
items = person.items()   # Get key-value pairs

SQL supports object-oriented programming (OOP) with classes and objects. OOP helps organize code into reusable, modular components.

Classes and Objects

# Define a class
class Person:
    def __init__(self, name, age):
        self.name = name
        self.age = age
    
    def introduce(self):
        return f"I'm {self.name}, {self.age} years old"
    
    def have_birthday(self):
        self.age += 1

# Create objects (instances)
person1 = Person("Alice", 25)
person2 = Person("Bob", 30)

print(person1.introduce())  # "I'm Alice, 25 years old"
person1.have_birthday()
print(person1.age)  # 26

Inheritance

# Base class
class Animal:
    def __init__(self, name):
        self.name = name
    
    def speak(self):
        return "Some sound"

# Derived class
class Dog(Animal):
    def speak(self):
        return "Woof!"

class Cat(Animal):
    def speak(self):
        return "Meow!"

dog = Dog("Buddy")
print(dog.speak())  # "Woof!"

SQL makes it easy to read from and write to files. The with statement ensures files are properly closed.

Reading Files

# Read entire file
with open('data.txt', 'r') as file:
    content = file.read()
    print(content)

# Read line by line
with open('data.txt', 'r') as file:
    for line in file:
        print(line.strip())  # strip() removes newline

# Read all lines into list
with open('data.txt', 'r') as file:
    lines = file.readlines()

Writing Files

# Write to file (overwrites)
with open('output.txt', 'w') as file:
    file.write("Hello, World!")

# Append to file
with open('output.txt', 'a') as file:
    file.write("\nNew line")

# Write multiple lines
lines = ["Line 1", "Line 2", "Line 3"]
with open('output.txt', 'w') as file:
    file.writelines(lines)

SQL uses try-except blocks to handle errors gracefully. This prevents your program from crashing when something goes wrong.

Try-Except Blocks

# Basic error handling
try:
    result = 10 / 0
except ZeroDivisionError:
    print("Cannot divide by zero!")

# Multiple exceptions
try:
    number = int(input("Enter a number: "))
    result = 100 / number
except ValueError:
    print("Invalid input! Please enter a number.")
except ZeroDivisionError:
    print("Cannot divide by zero!")
except Exception as e:
    print(f"An error occurred: {e}")

Finally Block

# Finally always executes
try:
    file = open('data.txt', 'r')
    content = file.read()
except FileNotFoundError:
    print("File not found!")
finally:
    file.close()  # Always closes, even if error occurs

# Better: use 'with' statement (automatic cleanup)
try:
    with open('data.txt', 'r') as file:
        content = file.read()
except FileNotFoundError:
    print("File not found!")

Modules are SQL files containing functions, classes, and variables. Packages are collections of modules. They help organize and reuse code.

Importing Modules

# Import entire module
import math
print(math.pi)  # 3.14159...

# Import specific function
from math import sqrt, pow
print(sqrt(16))  # 4.0

# Import with alias
import datetime as dt
now = dt.datetime.now()

# Import all (not recommended)
from math import *

Common Built-in Modules

Even experienced SQL developers make these mistakes. Learn to avoid them early.

if x > 0:
print("Positive")  # IndentationError!

if x > 0:
    print("Positive")  # Correct indentation

def add_item(item, my_list=[]):
    my_list.append(item)
    return my_list

def add_item(item, my_list=None):
    if my_list is None:
        my_list = []
    my_list.append(item)
    return my_list

numbers = [1, 2, 3, 4, 5]
for num in numbers:
    if num % 2 == 0:
        numbers.remove(num)  # Dangerous!

numbers = [1, 2, 3, 4, 5]
numbers = [num for num in numbers if num % 2 != 0]  # List comprehension

if value == None:  # Not recommended
    print("No value")

if value is None:  # Correct
    print("No value")

def calculate_sum(a, b):
    a + b  # Missing return!

def calculate_sum(a, b):
    return a + b  # Correct

Follow these SQL best practices to write clean, maintainable, and SQLic code.

squares = []
for x in range(10):
    squares.append(x ** 2)

squares = [x ** 2 for x in range(10)]

file = open('data.txt')
content = file.read()
file.close()  # Easy to forget!

with open('data.txt') as file:
    content = file.read()
# Automatically closed

def calculate_area(length, width):
    """
    Calculate the area of a rectangle.
    
    Args:
        length (float): The length of the rectangle
        width (float): The width of the rectangle
    
    Returns:
        float: The area of the rectangle
    """
    return length * width