Blog

Basics of SQL Syntax

In this tutorial, we’ll cover the basics of SQL (Structured Query Language) syntax. SQL is a standard language for interacting with databases, used for managing and manipulating data from webpages. We’ll go through essential SQL commands, syntax rules, and provide sample codes with explanations for each scenario.

1. Creating a Database:

First, let’s create a simple database to work with. We’ll call it SampleDB.

CREATE DATABASE SampleDB;

• CREATE DATABASE is the command to create a new database.
• SampleDB is the name of our new database.

2. Using a Database:

Now, we’ll select our newly created database to start working within it.

USE SampleDB;

• USE is used to select the database you want to work with.
• SampleDB is the database we created.

3. Creating a Table:

Tables are where we store our data. Let’s create a simple table called Employees.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Salary DECIMAL(10, 2)
);

• CREATE TABLE is the command to create a new table.
• Employees is the name of our table.
• (EmployeeID INT PRIMARY KEY, Name VARCHAR(50), Age INT, Salary DECIMAL(10, 2)) defines the table’s columns:
• EmployeeID is an integer and the primary key.
• Name is a variable-length string (up to 50 characters).
• Age is an integer.
• Salary is a decimal number with precision 10 and scale 2.

4. Inserting Data:

Let’s add some sample data into our Employees table.

INSERT INTO Employees (EmployeeID, Name, Age, Salary)
VALUES
    (1, 'John Doe', 30, 50000.00),
    (2, 'Jane Smith', 28, 45000.50),
    (3, 'Michael Johnson', 35, 60000.75);

• INSERT INTO is used to add new records to a table.
• Employees (EmployeeID, Name, Age, Salary) specifies the columns we’re inserting data into.
• VALUES is followed by the actual data to be inserted into each row.

5. Selecting Data:

To retrieve data from the Employees table, we use the SELECT statement.

SELECT * FROM Employees;

• SELECT * retrieves all columns.
• FROM Employees specifies the table from which to retrieve the data.

6. Filtering Data:

We can filter data using the WHERE clause.

SELECT * FROM Employees WHERE Age > 30;

• This query selects all columns from Employees where the Age is greater than 30.

7. Updating Data:

To update existing data, we use the UPDATE statement.

UPDATE Employees SET Salary = 52000.00 WHERE EmployeeID = 2;

• This query updates the Salary of the employee with EmployeeID 2 to 52000.00.

8. Deleting Data:

Deleting data is done with the DELETE statement.

DELETE FROM Employees WHERE EmployeeID = 3;

• This query deletes the employee with EmployeeID 3 from the Employees table.

9. Combining Conditions:

You can combine conditions using AND and OR.

SELECT * FROM Employees WHERE Age > 25 AND Salary < 55000.00;

• This selects employees with Age greater than 25 and Salary less than 55000.00.

10. Ordering Data:

We can order the results using ORDER BY.

SELECT * FROM Employees ORDER BY Salary DESC;

• This sorts the result set in descending order of Salary.

11. Limiting Results:

To limit the number of results returned, we use LIMIT.

SELECT * FROM Employees LIMIT 2;

• This query returns the first 2 rows from the Employees table.

12. Grouping Data:

We can use GROUP BY for grouping data.

SELECT Age, COUNT(*) FROM Employees GROUP BY Age;

• This query counts the number of employees for each Age value.

Conclusion

This tutorial covers the basics of SQL syntax, from creating databases and tables to manipulating data with INSERT, SELECT, UPDATE, and DELETE statements. Understanding these fundamental SQL commands is crucial for working effectively with databases. Practice these examples to get comfortable with SQL syntax, and explore more advanced topics as you continue learning.