SQL Tutorial Deutsch | Komplettkurs für Anfänger

Introduction

This tutorial is designed to help beginners learn SQL through a comprehensive course that covers essential concepts and practical applications. By the end of this guide, you will understand SQL fundamentals, including how to create databases, use various SQL commands, and perform data manipulation tasks. This resource is particularly useful for those interested in data science and data analysis.

Step 1: Understanding SQL

• SQL stands for Structured Query Language, and it is used to communicate with relational databases.
• It allows you to perform tasks such as querying data, updating records, and managing database structures.
• Familiarize yourself with basic SQL terminology, such as tables, records, and fields.

Step 2: Installing MySQL

• Download MySQL from the official website: MySQL Download.
• Follow the installation instructions specific to your operating system.

Installation for Mac

1. Open the downloaded MySQL package.
2. Follow the on-screen instructions to complete the installation.
3. Configure the MySQL server as prompted.

Installation for Windows

1. Run the MySQL Installer.
2. Choose the setup type (Developer Default is recommended).
3. Follow the installation wizard to complete the setup.

Step 3: Creating a Database

• Open your MySQL command-line client or use MySQL Workbench.
• Create a new database with the following command:

  CREATE DATABASE your_database_name;
  

• Switch to the newly created database:

  USE your_database_name;
  

Step 4: Using the SELECT Command

• The SELECT command retrieves data from a database.
• Basic syntax:

  SELECT column1, column2 FROM table_name;
  

• To select all columns, use:

  SELECT * FROM table_name;
  

Step 5: Applying Conditions with WHERE

• Use the WHERE clause to filter results.
• Example:

  SELECT * FROM table_name WHERE condition;
  

• Combine multiple conditions using AND/OR.

Step 6: Utilizing the IN Operator

• The IN operator allows you to specify multiple values in a WHERE clause.
• Example:

  SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);
  

Step 7: Using the BETWEEN Operator

• The BETWEEN operator filters records within a range.
• Example:

  SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
  

Step 8: Implementing the LIKE Operator

• The LIKE operator is used for pattern matching.
• Example:

  SELECT * FROM table_name WHERE column_name LIKE 'pattern%';
  

Step 9: Aggregating Data

• Use aggregate functions like COUNT, MIN, MAX, AVG, and SUM.
• Example:

  SELECT COUNT(*) FROM table_name;
  

Step 10: Grouping Data with GROUP BY

• The GROUP BY clause groups rows sharing a property so that aggregate functions can be applied.
• Example:

  SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
  

Step 11: Understanding Joins

• Joins are used to combine rows from two or more tables based on a related column.
• INNER JOIN: Returns records with matching values in both tables.

  SELECT columns FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;
  

• LEFT JOIN: Returns all records from the left table and matched records from the right table.
• RIGHT JOIN: Returns all records from the right table and matched records from the left table.

Step 12: Modifying Data

• To add data to a table, use INSERT:

  INSERT INTO table_name (column1, column2) VALUES (value1, value2);
  

• To update existing records, use UPDATE:

  UPDATE table_name SET column1 = value1 WHERE condition;
  

• To delete records, use DELETE:

  DELETE FROM table_name WHERE condition;
  

Step 13: Creating an ER-Diagram

• An Entity-Relationship Diagram (ER-Diagram) visually represents the relationships between entities in a database.
• Use diagramming tools to create an ER-Diagram based on your database structure.

Conclusion

Congratulations on taking the first steps in learning SQL! You have covered essential topics such as database creation, data retrieval, filtering, and manipulation. Continue practicing these commands and concepts to solidify your understanding. As you progress, consider exploring more advanced SQL topics or engaging with data science projects for practical experience.