7 SQL Statement Examples for Beginners

Posted on by Ian

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Whether you’re just starting your journey in data management or looking to refresh your skills, understanding the basic SQL statements is crucial.

This article will walk you through seven fundamental SQL statement examples that are pretty much standard across most major Relational Database Management Systems (RDBMSs).

The SELECT Statement: Retrieving Data

The SELECT statement is used to query data from one or more tables. It’s the most common SQL statement, and it’s the foundation for data retrieval.

Example:

SELECT first_name, last_name, email
FROM customers
WHERE country = 'USA';

This query selects three columns from the customers table. Specifically, it selects the first_name, last_name, and email columns. The WHERE clause filters the results to just customers from the USA.

The INSERT Statement: Adding New Records

The SQL INSERT statement is used to add new rows of data into a table.

Example:

INSERT INTO products (product_name, price, category)
VALUES ('Wireless Mouse', 29.99, 'Electronics');

When we use the INSERT statement, we specify the table name, followed by the column names in parentheses. We then follow this up with the VALUES clause, which consists of the VALUES keyword followed by the actual values to insert into the respective columns, also enclosed in parentheses.

So with that in mind, we can see that the above statement inserts a new product into the products table, specifying values for the product_name, price, and category columns.

The UPDATE Statement: Modifying Existing Data

The UPDATE statement is used to modify existing records in a table.

Example:

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales' AND years_of_service >= 5;

This query increases the salary by ten percent for all employees in the Sales department who have been with the company for five or more years.

The DELETE Statement: Removing Records

The DELETE statement is used to remove one or more rows from a table.

Example:

DELETE FROM orders
WHERE order_date < '2023-01-01' AND status = 'Canceled';

This statement deletes all canceled orders placed before January 1, 2023, from the orders table.

Be very careful when deleting data. If we omit the WHERE clause it will delete everything in the table. The thing about SQL is that the less specific we are, the more data that is affected.

The CREATE TABLE Statement: Defining a New Table

The CREATE TABLE statement is used to create a new table in the database. We specify the table name, followed by its definition inside parentheses. The definition consists of column names, their data types, and any constraints to be placed on the column. There are other things we can do too, but let’s not get ahead of ourselves.

Here’s a basic example of creating a table:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    enrollment_date DATE,
    major VARCHAR(100)
);

This statement creates a new table called students with six columns. We can see all the columns listed inside the parentheses along with their data types. The first column has a primary key constraint, which ensures that this column contains a unique identifier for the table. It’s usually a good idea to include a primary key in your tables. A primary key can consist of a single column or multiple tables combined (to form a composite primary key).

The ALTER TABLE Statement: Modifying Table Structure

The ALTER TABLE statement is used to add, modify, or delete columns in an existing table.

Example:

ALTER TABLE products
ADD COLUMN description TEXT;

This statement adds a new column called description to the existing products table. We can also use the ALTER TABLE statement to drop columns or change their definitions.

The JOIN Clause: Combining Data from Multiple Tables

In SQL, JOIN clauses can be used to combine rows from two or more tables based on a related column between them. While joins aren’t actually SQL statements themselves, they do allow us to create more sophisticated queries that pull data from multiple places across our databases.

Example:

SELECT o.order_id, c.customer_name, p.product_name, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';

This query retrieves order information along with customer and product details for all orders placed since January 1, 2024. It demonstrates an INNER JOIN between three tables: orders, customers, and products.

We know it’s an INNER JOIN because, when we omit the INNER part, it automatically becomes an INNER JOIN. There are other join types, such as the LEFT JOIN, the RIGHT JOIN, the FULL OUTER JOIN, the CROSS JOIN, the NATURAL JOIN, and the SELF JOIN.

See my SQL Joins Tutorial for an introduction to the various join types, along with examples of each.

Tips for Writing SQL Statements

  • Although SQL is case-insensitive for keywords in most RDBMSs, many SQL developers always use uppercase for SQL keywords (e.g., SELECT, FROM, WHERE) to improve readability.
  • Use meaningful table and column names to make your queries self-explanatory.
  • Always use WHERE clauses when updating or deleting data to avoid accidentally modifying or removing too many records.
  • Use appropriate data types when creating tables to ensure data integrity and optimize storage.
  • Remember to terminate your SQL statements with a semicolon (;).
  • Use table aliases (like o, c, and p in the JOIN example) to make complex queries more readable and to avoid ambiguity when joining tables with similar column names.
  • Start with simple queries and gradually build complexity as you become more comfortable with SQL syntax.

Conclusion

These seven SQL statement examples cover some of the most common operations you’ll perform when working with relational databases. By mastering these fundamental commands, you’ll be well-equipped to handle a wide range of data management tasks.

I should mention that while the syntax is generally consistent across different RDBMSs, there may be slight variations or additional features specific to each system. Always check the documentation for your particular RDBMS to ensure compatibility and to explore advanced features.

Learn More

Check out my SQL Tutorial for Beginners for a comprehensive introduction to programming in SQL.

Also see my SQL Reference for Beginners that you can use to refresh your SQL knowledge or quickly look up features that you’re not yet familiar with.