Getting Started with SQL: Creating a Database, Inserting Data, and Performing Queries

Introduction

Structured Query Language (SQL) is the standard language used to communicate with databases. Whether you’re managing user data, product inventory, or any other structured information, SQL is essential for creating, modifying, and querying your database. In this blog, we’ll walk through the basics: creating a database, adding a table, inserting records, and performing basic queries.

Prerequisites

• A basic understanding of SQL syntax.
• A MySQL, PostgreSQL, or any other SQL database management system installed on your system. You can use free options like MySQL Workbench or phpMyAdmin if you prefer a graphical interface.

Step 1: Create a Database

To begin, we need to create a database. Open your SQL client and enter the following command to create a database named company_db.

CREATE DATABASE company_db;

To verify the database creation, use:

SHOW DATABASES;

Now switch to the newly created database:

USE company_db;

Step 2: Create a Table

In this example, we’ll create a table called employees. This table will hold employee records including their ID, name, position, department, and salary.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2)
);

Here’s what each part of this command does:

• employee_id: An integer that will automatically increment as new records are added (serving as the unique identifier for each employee).
• name: A string field that will store the employee’s name.
• position: A string field to store the job title.
• department: A string field to represent which department the employee works in.
• salary: A decimal field to store the employee’s salary.

To verify the creation of the table, use the command:

SHOW TABLES;

Step 3: Insert Data into the Table

Now that we have the table ready, let’s insert at least five records into it.

INSERT INTO employees (name, position, department, salary)
VALUES
('Alice Johnson', 'Software Engineer', 'Development', 85000.00),
('Bob Smith', 'Product Manager', 'Management', 95000.00),
('Charlie Evans', 'HR Manager', 'Human Resources', 65000.00),
('Diana Roberts', 'Data Scientist', 'Analytics', 90000.00),
('Ethan White', 'QA Engineer', 'Testing', 72000.00);

The INSERT INTO command adds new records into the employees table. You can check the inserted data with the following query:

SELECT * FROM employees;

This will return all the records in the employees table.

Step 4: Perform Basic SQL Queries

Now that we have some data in our table, let’s perform basic SQL queries to retrieve and update the data.

Retrieve Data with SELECT

1. View All Employees:

SELECT * FROM employees;

This query retrieves all columns (*) from the employees table.

2. View Specific Columns:

SELECT name, position FROM employees;

This will display only the name and position columns of all employees.

3. Filter Records with WHERE Clause:

SELECT * FROM employees WHERE department = 'Development';

This will return all employees in the Development department.

4. Sort Data:

SELECT * FROM employees ORDER BY salary DESC;

This query will list employees sorted by their salary in descending order.

Update Data with UPDATE

Let’s update the salary of an employee using the UPDATE statement:

UPDATE employees
SET salary = 88000.00
WHERE name = 'Alice Johnson';

This query updates Alice Johnson’s salary to 88,000. You can verify the change by running:

SELECT * FROM employees WHERE name = 'Alice Johnson';

Delete Data with DELETE

To delete a record from the table, use the DELETE statement. For example, to remove the employee named "Charlie Evans":

DELETE FROM employees WHERE name = 'Charlie Evans';

Run the query SELECT * FROM employees; to confirm that the record has been deleted.

Step 5: More Advanced Queries (Bonus)

Once you’re comfortable with the basics, here are a couple of additional queries you can try.

1. Aggregate Functions:

SELECT AVG(salary) AS average_salary FROM employees;

This will return the average salary of all employees.

2. Group By Department:

SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

This query counts how many employees work in each department.

Conclusion

In this blog, we’ve walked through creating a database, adding a table, inserting data, and performing basic SQL queries. These foundational skills are essential for managing databases and retrieving information effectively.

Understanding SQL opens up opportunities to work with databases in a variety of applications, whether it’s web development, data analytics, or software engineering.