SQL Syntax – Tutorial with Examples

SQL, or Structured Query Language, is a programming language used to manage and manipulate relational databases. It is widely used in industries such as finance, healthcare, technology, and more, and is considered an essential skill for anyone working with data. In this guide, we will cover the basic syntax of SQL, including data types, creating a database and tables, and retrieving data using the SELECT statement.

SQL Data Types

SQL supports several data types, including numeric, string, and date/time. When creating a table, it is important to specify the data type for each column to ensure accurate and efficient storage of data. Here are some of the most commonly used data types in SQL:

• INT – A 32-bit integer value
• BIGINT – A 64-bit integer value
• DECIMAL – A decimal number with a fixed precision and scale
• VARCHAR – A variable-length string
• DATE – A date value (YYYY-MM-DD)
• TIMESTAMP – A date and time value (YYYY-MM-DD HH:MM:SS)

Creating a Database and Tables

Before you can start manipulating data, you need to create a database and define the structure of the tables within it. This is done using the CREATE DATABASE and CREATE TABLE statements. Here is an example of how to create a database called “example_db” and a table called “customers”:

CREATE DATABASE example_db;

USE example_db;

CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  date_joined DATE
);

In this example, the CREATE DATABASE statement creates a database called “example_db”, and the USE statement sets it as the current database. The CREATE TABLE statement creates a table called “customers” with five columns: “id”, “first_name”, “last_name”, “email”, and “date_joined”. The “id” column is defined as an integer and set as the primary key, meaning it is a unique identifier for each row in the table.

Retrieving Data with the SELECT Statement

Once you have created a database and populated it with data, you can retrieve it using the SELECT statement. The SELECT statement is used to specify the columns and rows you want to retrieve from a table. Here is a simple example of how to retrieve all data from the “customers” table:

SELECT * FROM customers;

In this example, the asterisk (*) after SELECT is a wildcard operator that retrieves all columns from the “customers” table. You can also specify specific columns you want to retrieve, like this:

SELECT first_name, last_name, email FROM customers;

This will only retrieve the “first_name”, “last_name”, and “email” columns from the “customers” table. The SELECT statement can also be used in combination with other statements, such as WHERE, to filter the data you retrieve. For example:

SELECT * FROM customers
WHERE date_joined BETWEEN '2022-01-01' AND '2022-12-31';

This statement will retrieve all columns from the “customers” table where the “date_joined” column is between January 1st, 2022, and December 31st, 2022. The WHERE clause filters the data based on the specified condition.

Conclusion

SQL is a powerful language for managing and manipulating data. In this guide, we covered the basic syntax of SQL, including data types, creating a database and tables, and retrieving data using the SELECT statement. As you continue to learn and practice SQL, you will discover more advanced features and techniques for working with data. With a solid understanding of the basics, you will be well on your way to becoming a proficient SQL programmer.