Section 1: SQL Basics

In this section, we'll cover the foundational aspects of SQL, focusing on creating databases and tables, inserting data, and retrieving information using the SELECT statement.

Creating a Database and Tables

Creating a New Database

To begin, let's create a new database. The CREATE DATABASE statement is used for this purpose:

CREATE DATABASE my_database;

This command creates a new database named my_database. Ensure that the database name follows your naming conventions and requirements.

Defining Tables with Appropriate Data Types

Once a database is created, tables can be defined within it. The CREATE TABLE statement is employed for this task:

CREATE TABLE employees (

  employee_id INT PRIMARY KEY,

  first_name VARCHAR(50),

  last_name VARCHAR(50),

  birth_date DATE,

  hire_date DATE

);

In this example, a table named employees is created with columns such as employee_id, first_name, last_name, birth_date, and hire_date. Each column is assigned an appropriate data type (e.g., INT, VARCHAR, DATE), and the employee_id column is designated as the primary key.

Inserting Data

Adding Records to Tables Using the INSERT Statement

Once tables are defined, data can be inserted using the INSERT INTO statement. For instance:

INSERT INTO employees (employee_id, first_name, last_name, birth_date, hire_date)

VALUES (1, 'John', 'Doe', '1990-01-15', '2015-03-20');

This command inserts a record into the employees table with specific values for each column.

Inserting Data into Specific Columns

You can insert data into specific columns by omitting the column names:

INSERT INTO employees

VALUES (2, 'Jane', 'Smith', '1985-07-22', '2018-09-10');

This inserts a record into the employees table without specifying column names. Ensure that the order of values aligns with the table's column order.

Retrieving Data with SELECT

Basic Syntax of the SELECT Statement

The SELECT statement is pivotal for retrieving data from tables. The basic syntax is as follows:

SELECT column1, column2, ...

FROM table_name;

This retrieves specific columns from a table. For instance:

SELECT first_name, last_name

FROM employees;

Retrieving All Columns and Specific Columns

To retrieve all columns, use the asterisk *:

SELECT *

FROM employees;

For specific columns:

SELECT employee_id, first_name, last_name

FROM employees;

This concludes the basics of SQL, covering database and table creation, data insertion, and the SELECT statement. In the following sections, we'll delve into more advanced SQL concepts and techniques.