Sign In
Get Started

Executing Your First SQL Query

by | SQL

Introduction to Databases and SQL

What is a Database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. Databases are managed by a Database Management System (DBMS).

Key Concepts

  • Tables: A table is a collection of related data held in a structured format within a database. It consists of columns and rows.
  • Rows: Each row in a table represents a single, implicitly structured data item.
  • Columns: Columns represent the attributes of the data stored in a table.

What is SQL?

SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating databases. It is used to perform tasks such as querying data, updating records, and administrating a database system.

Sales Now On Advertisement

Basic SQL Syntax

-- This is a comment
SELECT column1, column2
FROM table_name
WHERE condition;

Setting Up Your First Database

Step 1: Create a Database

-- Create a new database named 'my_first_database'
CREATE DATABASE my_first_database;

Step 2: Create a Table

-- Use the newly created database
USE my_first_database;

-- Create a table named 'students'
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    enrollment_date DATE
);

Step 3: Insert Data into the Table

-- Insert some sample data into the 'students' table
INSERT INTO students (first_name, last_name, age, enrollment_date)
VALUES
    ('Alice', 'Johnson', 21, '2023-01-15'),
    ('Bob', 'Smith', 22, '2023-01-16'),
    ('Charlie', 'Brown', 20, '2023-01-17');

Step 4: Query the Table

-- Select all columns from the 'students' table
SELECT * FROM students;

-- Select specific columns from the 'students' table
SELECT first_name, last_name FROM students;

-- Select students where age is greater than 20
SELECT * FROM students WHERE age > 20;

Step 5: Update Data in the Table

-- Update age of student with id 1 to 22
UPDATE students
SET age = 22
WHERE id = 1;

Step 6: Delete Data from the Table

-- Delete student with id 2
DELETE FROM students
WHERE id = 2;

Conclusion

This guide provides a foundational understanding of databases and how to use SQL to manage them. By following these steps, you should be able to create your own database, insert data, and perform simple queries confidently.

Setting Up Your SQL Environment

1. Install SQL Database Server

Select a SQL database server software like MySQL, PostgreSQL, or SQLite. Below are setup steps for MySQL and PostgreSQL:

MySQL

  1. Download MySQL Installer

    • Go to the MySQL downloads page
    • Choose the version compatible with your operating system (Windows, macOS, Linux)

  2. Run MySQL Installer

    • Follow the on-screen instructions to install MySQL Server
    • Configure the SQL server settings
      • Create a root password
      • Set the server’s default port (default is 3306)

PostgreSQL

  1. Download PostgreSQL Installer

  2. Run PostgreSQL Installer

    • Follow the on-screen instructions to install PostgreSQL
    • Configure the SQL server settings
      • Create a superuser password (usually for user postgres)
      • Set the server’s default port (default is 5432)

2. Install SQL Client/Workbench

Install SQL client tools such as MySQL Workbench or pgAdmin to interact with the database.

MySQL Workbench

  1. Download MySQL Workbench

  2. Install MySQL Workbench

    • Follow the on-screen instructions to complete the installation

pgAdmin (for PostgreSQL)

  1. Download pgAdmin

  2. Install pgAdmin

    • Follow the on-screen instructions to complete the installation

3. Connect to SQL Server

MySQL

  1. Open MySQL Workbench

    • Launch MySQL Workbench

  2. Create a New Connection

    • Click + icon next to MySQL Connections
    • Enter connection settings
      • Connection name: local instance
      • Hostname: localhost
      • Port: 3306
      • Username: root
      • Password: (enter the root password set during installation)
    • Click Test Connection to verify connection
    • Click OK

  3. Connect to Server

    • Double-click the connection name to connect to the server

PostgreSQL

  1. Open pgAdmin

    • Launch pgAdmin

  2. Create a New Server Connection

    • Right-click Servers in the Browser, select Create > Server…
    • Enter connection settings
      • General tab:
        • Name: local instance
      • Connection tab:
        • Host name/address: localhost
        • Port: 5432
        • Username: postgres
        • Password: (enter the superuser password set during installation)
    • Click Save

  3. Connect to Server

    • Click on the server name under Servers

4. Create a New Database

MySQL

  1. Create Database
    • Open SQL editor within MySQL Workbench
    • Enter SQL command:
      CREATE DATABASE my_first_db;

    • Execute the Command (click on the lightning bolt icon)

PostgreSQL

  1. Create Database
    • Open SQL editor within pgAdmin
    • Enter SQL command:
      CREATE DATABASE my_first_db;

    • Execute the Command (click on the Execute/Run button, typically a play icon)

5. Create a New Table

MySQL

  1. Switch to Database

    • Enter SQL command:
      USE my_first_db;

  2. Create Table

    • Enter SQL command:
      CREATE TABLE users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          username VARCHAR(50) NOT NULL,
          email VARCHAR(50) NOT NULL
      );

    • Execute the Command

PostgreSQL

  1. Switch to Database

    • Connect to the newly created database:
      • Either change the connection to my_first_db in pgAdmin
      • Or use SQL command:
        c my_first_db;

  2. Create Table

    • Enter SQL command:
      CREATE TABLE users (
          id SERIAL PRIMARY KEY,
          username VARCHAR(50) NOT NULL,
          email VARCHAR(50) NOT NULL
      );

    • Execute the Command

6. Insert Sample Data

MySQL

  1. Insert Data
    • Enter SQL command:
      INSERT INTO users (username, email)
      VALUES
      ('john_doe', 'john@example.com'),
      ('jane_doe', 'jane@example.com');

    • Execute the Command

PostgreSQL

  1. Insert Data
    • Enter SQL command:
      INSERT INTO users (username, email)
      VALUES
      ('john_doe', 'john@example.com'),
      ('jane_doe', 'jane@example.com');

    • Execute the Command

7. Verify Data Insertion

MySQL

  1. Select Data
    • Enter SQL command:
      SELECT * FROM users;

    • Execute the Command

PostgreSQL

  1. Select Data
    • Enter SQL command:
      SELECT * FROM users;

    • Execute the Command

This guide should enable you to set up your SQL environment, connect to the database server, create a new database and table, and perform simple queries to insert and verify data.

Understanding SQL Syntax and Basic Queries

Basic SQL Syntax

SQL (Structured Query Language) is used to communicate with databases. The standard SQL commands, such as SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP, can be used to accomplish almost everything that one needs to do with a database.

Basic SELECT Query

The SELECT statement is used to select data from a database. The data returned is stored in a result table, sometimes called the result-set.

SELECT column1, column2, ...
FROM table_name;

Example of Basic SELECT Query

Assume we have a table named Employees with the following columns: EmployeeID, FirstName, LastName, Age, and Department.

Power BI Tools Advertisement

To retrieve all columns and rows from the Employees table:

SELECT * FROM Employees;

To retrieve specific columns (for example, FirstName and LastName):

SELECT FirstName, LastName FROM Employees;

Filtering Data with WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example of WHERE Clause

To get all employees in the ‘IT’ department:

SELECT * FROM Employees
WHERE Department = 'IT';

To retrieve employees who are older than 30:

SELECT * FROM Employees
WHERE Age > 30;

Sorting Data with ORDER BY Clause

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The default sort order is ascending.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC];

Example of ORDER BY Clause

To get all employees sorted by LastName in ascending order:

SELECT * FROM Employees
ORDER BY LastName ASC;

To get all employees sorted by Age in descending order:

SELECT * FROM Employees
ORDER BY Age DESC;

Limiting Data with LIMIT Clause

The LIMIT clause is used to specify the number of records to return.

SELECT column1, column2, ...
FROM table_name
LIMIT number;

Example of LIMIT Clause

To get the first 5 records from the Employees table:

SELECT * FROM Employees
LIMIT 5;

Combining Conditions with AND, OR

The AND and OR operators are used to filter records based on more than one condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition1 [AND|OR] condition2;

Example of Combining Conditions

To find employees in the ‘HR’ department who are older than 40:

SELECT * FROM Employees
WHERE Department = 'HR' AND Age > 40;

To find employees who are in the ‘Sales’ department or older than 50:

SELECT * FROM Employees
WHERE Department = 'Sales' OR Age > 50;

Summary

This cheat sheet outlines the basic SQL syntax for performing common tasks: selecting data, filtering with conditions, sorting, limiting the result set, and combining multiple conditions. By understanding and executing these basic queries, beginners can start to interact with databases effectively.

Now you can execute these SQL statements directly in your database management system to see the results.

Data Mentor Advertisement

Performing Data Retrieval with SELECT Statements

In this unit, we’ll describe how to execute SQL SELECT statements to retrieve data from a database.

1. Basic SELECT Statement

To retrieve all columns from a table:

SELECT * FROM table_name;

For example, to retrieve all data from a table named employees:

SELECT * FROM employees;

2. Selecting Specific Columns

To retrieve specific columns from a table:

SELECT column1, column2 FROM table_name;

For example, to retrieve only the first_name and last_name from the employees table:

SELECT first_name, last_name FROM employees;

3. Using WHERE Clause

To filter data based on specific conditions:

SELECT column1, column2 FROM table_name WHERE condition;

For example, to retrieve employees with the first name ‘John’:

SELECT first_name, last_name FROM employees WHERE first_name = 'John';

4. Using AND, OR Clauses

To filter data based on multiple conditions:

SELECT column1, column2 FROM table_name WHERE condition1 AND/OR condition2;

For example, to retrieve employees named ‘John’ who work in the ‘Sales’ department:

SELECT first_name, last_name FROM employees WHERE first_name = 'John' AND department = 'Sales';

5. Ordering Results

To sort the results based on a column:

SELECT column1, column2 FROM table_name ORDER BY column ASC/DESC;

For example, to retrieve employees and sort them by last name in ascending order:

SELECT first_name, last_name FROM employees ORDER BY last_name ASC;

6. Limiting Results

To limit the number of results returned:

SELECT column1, column2 FROM table_name LIMIT number;

For example, to retrieve the first 5 employees:

SELECT first_name, last_name FROM employees LIMIT 5;

7. Combining Clauses

To combine different clauses in a single query:

SELECT column1, column2 FROM table_name WHERE condition ORDER BY column ASC/DESC LIMIT number;

For example, to retrieve first 5 employees named ‘John’ and sort them by last name in descending order:

SELECT first_name, last_name FROM employees 
WHERE first_name = 'John' 
ORDER BY last_name DESC 
LIMIT 5;

Apply these commands directly on your database to retrieve data efficiently and confidently.

Filtering and Sorting Data for Precise Results

Filtering Data Using WHERE Clause

To filter data in SQL, you use the WHERE clause. This clause allows you to specify conditions that the rows must meet to be included in the results.

Example

Suppose you have a table named employees with the following columns: id, name, position, and salary. To select employees who are software engineers (position equals ‘Software Engineer’), you can use the following query:

SELECT id, name, position, salary
FROM employees
WHERE position = 'Software Engineer';

You can also use multiple conditions using AND, OR operators:

SELECT id, name, position, salary
FROM employees
WHERE position = 'Software Engineer' AND salary > 60000;

Sorting Data using ORDER BY Clause

To sort data, use the ORDER BY clause. This clause allows you to sort the result set by one or more columns.

EDNA AI Advertisement

Example

To sort the table employees by salary in descending order:

SELECT id, name, position, salary
FROM employees
ORDER BY salary DESC;

If you want to sort by multiple columns, such as position alphabetically and then salary in descending order:

SELECT id, name, position, salary
FROM employees
ORDER BY position ASC, salary DESC;

Combining Filtering and Sorting

You can combine both filtering and sorting in a single query to get more precise results.

Example

To find all software engineers with a salary greater than 60,000, and then sort them by their names in ascending order:

SELECT id, name, position, salary
FROM employees
WHERE position = 'Software Engineer' AND salary > 60000
ORDER BY name ASC;

Example in Action

Scenario

Let’s assume you are given the following table products:

id name category price
1 Laptop Electronics 1200
2 Phone Electronics 800
3 T-Shirt Clothing 20
4 Shoes Footwear 50
5 Headphones Electronics 150

Task

Retrieve all products in the ‘Electronics’ category priced less than 1000, and sort them by price in ascending order.

SQL Query

SELECT id, name, category, price
FROM products
WHERE category = 'Electronics' AND price < 1000
ORDER BY price ASC;

Result

id name category price
5 Headphones Electronics 150
2 Phone Electronics 800

By following this approach, you can effectively filter and sort data for precise results in your SQL queries. Implement these strategies to enhance your SQL querying skills and generate accurate, well-organized datasets.

Related Posts