SQL Basic Queries

This section covers the fundamental SQL statements used for retrieving data from a database. Understanding these basic queries is crucial for interacting with any relational database system.

SELECT Statement

The SELECT statement is the cornerstone of data retrieval in SQL. It allows you to specify which columns and rows you want to retrieve from one or more tables.

Syntax

SELECT column1, column2, ...
FROM table_name;

Selecting All Columns

To retrieve all columns from a table, you can use the asterisk (*) wildcard.

SELECT *
FROM Customers;

Selecting Specific Columns

You can list the names of the columns you wish to retrieve, separated by commas.

SELECT CustomerID, CompanyName
FROM Customers;

WHERE Clause

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

Syntax

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

Common Operators

The WHERE clause uses various operators to define conditions:

• = : Equal to
• > : Greater than
• < : Less than
• >= : Greater than or equal to
• <= : Less than or equal to
• <> or != : Not equal to
• BETWEEN : Between an inclusive range
• LIKE : Search for a pattern
• IN : Specify multiple possible values

Filtering with a Condition

SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'USA';

Filtering with Multiple Conditions (AND/OR)

You can combine conditions using AND (both must be true) and OR (at least one must be true).

SELECT CustomerName, City
FROM Customers
WHERE Country = 'USA' AND City = 'New York';

SELECT CustomerName, Country
FROM Customers
WHERE Country = 'Germany' OR Country = 'France';

ORDER BY Clause

The ORDER BY clause is used to sort the result-set in ascending or descending order. By default, it sorts in ascending order (ASC).

Syntax

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

Sorting in Ascending Order

SELECT CustomerName, Country
FROM Customers
ORDER BY Country;

Sorting in Descending Order

SELECT CustomerName, Country
FROM Customers
ORDER BY Country DESC;

Sorting by Multiple Columns

SELECT CustomerName, Country, City
FROM Customers
ORDER BY Country ASC, City ASC;

DISTINCT Keyword

The DISTINCT keyword is used to return only unique values. Duplicate values are eliminated.

Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT DISTINCT Country
FROM Customers;

INSERT INTO Statement

The INSERT INTO statement is used to insert new records (rows) into a table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (100, 'New Corp', 'John Doe', '123 Main St', 'Anytown', '12345', 'USA');

UPDATE Statement

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

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE Customers
SET Address = '456 Oak Ave', City = 'Newville'
WHERE CustomerID = 1;

DELETE Statement

The DELETE statement is used to delete existing records from a table.

Syntax

DELETE FROM table_name
WHERE condition;

DELETE FROM Customers
WHERE CustomerID = 50;

Caution: Be very careful when using the DELETE statement. Without a WHERE clause, it will delete all records in the table!

Last updated: 2023-10-27