The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Basic SQL Concepts

Next ►
►

In this section, we will cover the fundamental concepts of SQL (Structured Query Language) that are essential for working with PostgreSQL. Understanding these basics will provide a strong foundation for more advanced topics.

What is SQL?

SQL is a standard language for managing and manipulating databases. It allows you to:

  • Create and modify database structures (tables, indexes, etc.).
  • Insert, update, delete, and query data.
  • Control access to the data.

Key SQL Concepts

  1. Data Types

Data types define the kind of data that can be stored in a column. Common data types include:

  • Integer: Whole numbers.
  • Decimal: Numbers with fractional parts.
  • Text: Strings of characters.
  • Date/Time: Dates and times.

  1. Tables

Tables are the primary structure in a database where data is stored. Each table consists of rows and columns.

  • Columns: Define the data type and name of the data.
  • Rows: Contain the actual data entries.

  1. Primary Key

A primary key is a unique identifier for each row in a table. It ensures that each record can be uniquely identified.

  1. Foreign Key

A foreign key is a column or a set of columns in one table that uniquely identifies a row of another table. It is used to establish a relationship between the two tables.

  1. SQL Statements

SQL statements are used to perform tasks such as updating data in a database or retrieving data from a database. Common SQL statements include:

  • SELECT: Retrieves data from a database.
  • INSERT: Adds new data to a database.
  • UPDATE: Modifies existing data.
  • DELETE: Removes data from a database.

Practical Examples

Creating a Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

Explanation:

  • CREATE TABLE employees: Creates a new table named employees.
  • id SERIAL PRIMARY KEY: Creates an id column that auto-increments and serves as the primary key.
  • name VARCHAR(100): Creates a name column that can store up to 100 characters.
  • position VARCHAR(50): Creates a position column that can store up to 50 characters.
  • salary DECIMAL(10, 2): Creates a salary column that can store decimal values with up to 10 digits, 2 of which can be after the decimal point.
  • hire_date DATE: Creates a hire_date column that stores date values.

Inserting Data

INSERT INTO employees (name, position, salary, hire_date)
VALUES ('John Doe', 'Software Engineer', 75000.00, '2023-01-15');

Explanation:

  • INSERT INTO employees (name, position, salary, hire_date): Specifies the table and columns to insert data into.
  • VALUES ('John Doe', 'Software Engineer', 75000.00, '2023-01-15'): Provides the values to be inserted into the specified columns.

Querying Data

SELECT * FROM employees;

Explanation:

  • SELECT * FROM employees: Retrieves all columns (*) from the employees table.

Updating Data

UPDATE employees
SET salary = 80000.00
WHERE name = 'John Doe';

Explanation:

  • UPDATE employees: Specifies the table to update.
  • SET salary = 80000.00: Sets the salary column to a new value.
  • WHERE name = 'John Doe': Specifies the condition to identify which rows to update.

Deleting Data

DELETE FROM employees
WHERE name = 'John Doe';

Explanation:

  • DELETE FROM employees: Specifies the table to delete data from.
  • WHERE name = 'John Doe': Specifies the condition to identify which rows to delete.

Exercises

Exercise 1: Create a Table

Create a table named departments with the following columns:

  • id (primary key, auto-increment)
  • name (string, up to 50 characters)
  • location (string, up to 100 characters)

Solution:

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location VARCHAR(100)
);

Exercise 2: Insert Data

Insert the following data into the departments table:

  • name: 'Human Resources', location: 'Building A'
  • name: 'Engineering', location: 'Building B'

Solution:

INSERT INTO departments (name, location)
VALUES ('Human Resources', 'Building A'),
       ('Engineering', 'Building B');

Exercise 3: Query Data

Retrieve all data from the departments table.

Solution:

SELECT * FROM departments;

Exercise 4: Update Data

Update the location of the 'Engineering' department to 'Building C'.

Solution:

UPDATE departments
SET location = 'Building C'
WHERE name = 'Engineering';

Exercise 5: Delete Data

Delete the 'Human Resources' department from the departments table.

Solution:

DELETE FROM departments
WHERE name = 'Human Resources';

Summary

In this section, we covered the basic SQL concepts essential for working with PostgreSQL, including data types, tables, primary and foreign keys, and common SQL statements. We also provided practical examples and exercises to reinforce these concepts. Understanding these basics will prepare you for more advanced SQL operations and database management tasks.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved