Basic SQL Commands

Objective: Learn the fundamental SQL commands.

In this lesson, we will cover the essential SQL commands that form the foundation of working with relational databases. You will learn how to create databases and tables, insert data into tables, select data from tables, update existing data, and delete data from tables. By understanding these basic commands, you’ll be equipped to perform fundamental database operations efficiently.

1. Creating Databases and Tables

Creating a database and tables is the first step in managing data using SQL. A database acts as a container for tables, and tables hold the actual data in a structured format. Each table consists of rows and columns, where each row is a record and each column is a field in the record.

Creating a Database:

• Syntax:

   CREATE DATABASE database_name;

• Explanation: The CREATE DATABASE command is used to create a new database. You need to specify a name for the database, which will be used to identify it in the system.
• Example:

   CREATE DATABASE company_db;

• Explanation: This command creates a new database named “company_db”. This database will serve as a container for tables related to the company’s data.
  
  

Creating a Table:

• Syntax:

CREATE TABLE table_name (

       column1 datatype constraint,

       column2 datatype constraint,

       ...

   );

• Explanation: The CREATE TABLE command is used to create a new table within a database. You need to define the table’s name and the columns it will contain, along with their data types and any constraints (such as primary keys or unique constraints).

• Example:

CREATE TABLE employees (

       id SERIAL PRIMARY KEY,

       name VARCHAR(100),

       position VARCHAR(100),

       salary NUMERIC

   );

• Explanation:  This command creates a table named “employees” with four columns: “id”, “name”, “position”, and “salary”. The “id” column is of type SERIAL and serves as the primary key, meaning each value in this column must be unique and not null. The “name” and ”position” columns are of type VARCHAR(100) meaning they can store strings up to 100 characters long. The “salary” column is of type NUMERIC, used for storing numerical values.
  
  
  

2. Inserting Data into Tables

Inserting data into tables is essential to populate your database with information. This operation allows you to add new records to the table.

Inserting Data:

• Syntax:

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

• Explanation: The INSERT INTO command is used to add new records to a table. You specify the table name, the columns you want to insert values into, and the corresponding values.
• Example:

INSERT INTO employees (name, position, salary) 
VALUES ('John Doe', 'Manager', 60000);

• Explanation: This command inserts a new record into the employees table. It adds the values John Doe for the name column, Manager for the position column, and 60000 for the salary column.
  
  
  

3. Selecting Data from Tables

Selecting data allows you to query and retrieve specific information from your tables. This operation is used to extract useful data based on certain criteria.

Selecting Data:

• Syntax:

SELECT column1, column2, …
FROM table_name;

• Explanation: The SELECT command is used to query data from a database. You specify the columns you want to retrieve and the table from which to retrieve them.

• Example:

SELECT * FROM employees;

• Explanation: This command retrieves all columns and rows from the “employees” table. The “*” symbol is a wildcard that means “all columns.”
  
  
  

4. Updating Data in Tables

Updating data is used to modify existing records in your tables. This operation is useful when you need to change data that has already been stored.

Updating Data:

• Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

• Explanation: The UPDATE command is used to modify existing records in a table. You specify the table name, the columns to update with their new values, and a condition to identify which records should be updated.

• Example:

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

• Explanation: This command updates the “salary” of the record in the “employees” table where the “name” is “John Doe” to “65000”. The WHERE clause ensures that only the record for John Doe is updated.
  
  
  

5. Deleting Data from Tables

Deleting data is used to remove records from your tables. This operation is essential for maintaining and cleaning up your database.

Deleting Data:

• Syntax:

DELETE FROM table_name 
WHERE condition;

• Explanation: The DELETE command is used to remove records from a table. You specify the table name and a condition to identify which records should be deleted.

• Example:

DELETE FROM employees 
WHERE name = 'John Doe';

• Explanation: This command deletes the record from the “employees” table where the “name” is “John Doe”. The WHERE clause ensures that only the record for John Doe is deleted.
  
  

Conclusion

By the end of this lesson, you should be familiar with the basic SQL commands used to create databases and tables, insert data, select data, update data, and delete data. These commands form the foundation of SQL operations and are essential for managing any relational database.

In the next lesson, we will set up PostgreSQL, a popular relational database system, and start writing our first SQL queries. You will learn how to install PostgreSQL, create databases and tables, and perform basic data operations using SQL. This hands-on approach will help you apply the concepts learned in this lesson and build a solid foundation in SQL and database management.