SQL Basics

Introduction to SQL

• SQL stands for Structured Query Language.
• It's the "love language" of databases, essential for IT jobs.
• Not necessary to be an expert unless aiming for specific roles like DBA.
• Covers basics of databases: creation, updates, deletions, and queries (CRUD).

Importance of Learning SQL

• Essential for various IT jobs.
• Familiarity with SQL can enhance job prospects.
• SQL skills can be used in roles such as cloud engineering.

Understanding Databases

• Databases are systems for storing large amounts of data.
• Compared to Excel spreadsheets:
  • Columns (fields) and rows (records).
• Databases are used when data size exceeds what Excel can manage.
• Example: A "Network Chuck Coffee" database might include:
  • Customers
  • Coffee offerings
  • Orders

Database Management Systems (DBMS)

• DBMS options include:
  • Microsoft SQL Server
  • MySQL
  • PostgreSQL
  • Oracle
• All utilize SQL for database management.
• SQL is standardized by ISO, making it applicable across different DBMS.

Types of Databases

1. Relational Databases (RDBMS)
   • Most popular type.
   • Data organized in tables related to each other.
   • Example: Orders table linked to Customers table.
2. Non-Relational Databases
   • No structured relationships.
   • Example: NoSQL databases.

Getting Started with MySQL

• Installation:
  • Use any computer (Linux preferred).
  • Update repositories and install MySQL server using commands:

    sudo apt update
    sudo apt install mysql-server -y
    

• Accessing MySQL:
  • Use command mysql to start interacting with the database.

Creating a Database and Table

• Create a new database:

  CREATE DATABASE nc_coffee;
  

• Use the database:

  USE nc_coffee;
  

• Creating a table:
  • Example for a coffee table:

  CREATE TABLE coffee (
      id INT,
      name VARCHAR(255),
      region VARCHAR(255),
      roast VARCHAR(255)
  );
  

Basic SQL Commands

• Insert data into table:

  INSERT INTO coffee VALUES (1, 'Default Roast', 'Ethiopia', 'Light');
  

• Select data from table:

  SELECT * FROM coffee;
  

• Filtering data with WHERE clause:

  SELECT * FROM Avengers WHERE origin = 'Earth';
  

• Updating records:

  UPDATE Avengers SET last_name = NULL WHERE first_name = 'Groot';
  

• Deleting records:

  DELETE FROM Avengers WHERE first_name = 'Jeff';
  

Advanced Querying Techniques

• Using ORDER BY to sort results:

  SELECT * FROM Avengers ORDER BY age ASC;
  

• Adding a new column:

  ALTER TABLE Avengers ADD COLUMN beard BOOLEAN;
  

• Updating multiple records based on conditions.

Conclusion

• SQL is a powerful tool for interacting with databases.
• Develop a deeper understanding of relationships between tables.
• Explore advanced features such as joins and views for comprehensive data analysis.
• Encourage further learning beyond the basics taught.