Learn Basic SQL in 3.5 hrs | Complete SQL Beginner Course

Introduction

This tutorial provides a comprehensive guide to learning basic SQL concepts, perfect for beginners looking to enhance their database skills. You'll learn how to install PostgreSQL, create and manipulate databases and tables, and understand key SQL commands. By the end of this tutorial, you'll be equipped with the foundational knowledge needed to tackle SQL-related tasks and interviews.

Step 1: Install PostgreSQL and PgAdmin

To start your SQL journey, you'll need to install PostgreSQL, a powerful open-source database system, along with PgAdmin, a management tool for PostgreSQL.

1. Download PostgreSQL from the official website.
2. Follow the installation instructions for your operating system (Windows, macOS, or Linux).
3. Install PgAdmin during the PostgreSQL setup process or download it separately from the PgAdmin website.
4. Launch PgAdmin and connect to your PostgreSQL server by entering your credentials.

Step 2: Create a New Database

Once PostgreSQL and PgAdmin are set up, you can create a new database.

1. Open PgAdmin and navigate to the Servers section.
2. Right-click on the Databases node and select Create > Database.
3. Enter a name for your database and click Save.

Step 3: Understand Database and Tables

Before working with SQL commands, it's essential to understand what databases and tables are.

• Database: A structured collection of data that is stored and accessed electronically. It can hold multiple tables.
• Table: A set of data organized in rows and columns, much like a spreadsheet. Each table contains records (rows) and fields (columns).

Step 4: Create a Simple Table

Now, let's create a basic table to store data.

1. In PgAdmin, navigate to your new database.
2. Right-click on Tables and select Create > Table.
3. Define the table structure by adding columns:
   • Name the column (e.g., id, name, age).
   • Choose a data type for each column (e.g., INTEGER, VARCHAR, DATE).
4. Click Save.

Step 5: Load Data into the Table

You can load data into your created table using SQL commands.

1. Open the Query Tool in PgAdmin.

2. Use the following SQL command to insert data:

   INSERT INTO your_table_name (id, name, age)
   VALUES (1, 'John Doe', 30),
          (2, 'Jane Smith', 25);
   

3. Execute the command to load the data.

Step 6: Read Data from the Table

To view the data, you can run a simple SELECT statement.

1. Open the Query Tool.

2. Enter the following SQL command:

   SELECT * FROM your_table_name;
   

3. Execute the command to display all records in the table.

Step 7: Modify Data in the Table

To update existing records, use the UPDATE command.

1. Open the Query Tool.

2. Enter the following SQL command:

   UPDATE your_table_name
   SET age = 31
   WHERE name = 'John Doe';
   

3. Execute the command to apply the changes.

Step 8: Remove Data from the Table

To delete records, utilize the DELETE command.

1. Open the Query Tool.

2. Enter the following SQL command:

   DELETE FROM your_table_name
   WHERE name = 'Jane Smith';
   

3. Execute the command to remove the specified record.

Step 9: Explore SQL Commands

Familiarize yourself with essential SQL commands:

• SELECT: Retrieve data from a database.
• INSERT: Add new records to a table.
• UPDATE: Modify existing records.
• DELETE: Remove records from a table.
• CREATE TABLE: Define a new table structure.
• DROP TABLE: Remove a table from the database.

Step 10: Understand Constraints

Learn about database constraints to maintain data integrity.

• Primary Key: Uniquely identifies each record in a table.
• Foreign Key: Creates a relationship between two tables.
• Identity Constraint: Automatically generates unique values for a column.

Conclusion

In this tutorial, you've learned the fundamental concepts of SQL, from installation to database management. Practice creating and manipulating tables, and explore SQL commands further to solidify your understanding. For more advanced topics, consider diving into JOIN operations, GROUP BY clauses, and real-world case studies to apply what you've learned. Happy querying!