Lesson 1: Introduction to Databases and SQL

What is a Database?

A database is an organized collection of data that can be easily accessed, managed, and updated. Databases are crucial for applications that require storing large amounts of data in a structured format.

Types of Databases

• Relational Databases: Store data in tables with relationships between them (e.g., MySQL, PostgreSQL).
• NoSQL Databases: Store data in a non-tabular format (e.g., MongoDB, Cassandra).

What is SQL?

SQL (Structured Query Language) is the standard programming language used to manage and manipulate relational databases. It allows users to perform various operations such as querying data, updating records, and managing database structures.

Basic SQL Operations

1. SELECT: Retrieve data from a database.
2. INSERT: Add new data to a database.
3. UPDATE: Modify existing data in a database.
4. DELETE: Remove data from a database.

Example of a Simple SQL Query

Here’s a simple example of how to use SQL to retrieve data from a database:

SELECT * FROM employees;

This command selects all columns from the employees table.

Creating a Database and Table

To illustrate how SQL works, let’s create a simple database and a table.

-- Create a new database
CREATE DATABASE CompanyDB;

-- Use the newly created database
USE CompanyDB;

-- Create a table named 'employees'
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

This code snippet creates a database named CompanyDB and a table named employees with four fields.

Inserting Data into the Table

Once the table is created, you can insert data into it:

-- Insert data into the employees table
INSERT INTO employees (name, position, salary) VALUES
('Alice', 'Developer', 60000),
('Bob', 'Manager', 80000);

This command adds two entries to the employees table.

Querying Data

You can retrieve data from the employees table as follows:

-- Query to retrieve all employees
SELECT * FROM employees;

Best Practices

Always back up your database before performing operations that modify data.

Use meaningful names for your tables and columns to improve readability.

Common Mistakes

• Forgetting to use ; at the end of SQL commands can lead to errors.
• Using reserved keywords as table or column names without proper escaping.

Summary of Key Concepts

• A database is an organized collection of data.
• SQL is the language used to manage relational databases.
• Basic SQL operations include SELECT, INSERT, UPDATE, and DELETE.
• Proper naming conventions and backups are essential for database management.

• Exercise 1: Create a new database named TestDB and a table named products with fields: id, product_name, price.
• Exercise 2: Insert at least three products into the products table with different names and prices.
• Exercise 3: Write a SQL query to retrieve all products from the products table.

• Databases organize data for easy access and management.
• SQL is essential for interacting with relational databases.
• Familiarize yourself with basic SQL commands: SELECT, INSERT, UPDATE, DELETE.
• Always follow best practices for database management.