Databases

This section covers the fundamentals of databases and how to interact with them using Structured Query Language (SQL). We will focus on basic SQL queries, including SELECT, WHERE, AND, OR, and ORDER BY.

What is a Database?

A database is an organized collection of data, typically stored and accessed electronically. Databases are used to store information in a structured way, allowing for efficient retrieval and manipulation.

Relational Databases

Most databases used in computer science are relational databases. This means data is organized into tables with rows and columns. Each table represents a specific entity (e.g., students, products, orders).

Tables, Rows, and Columns

A table consists of rows and columns:

• Rows (Records): Represent individual instances of the entity.
• Columns (Fields): Represent attributes of the entity.

Student ID	Name	Grade
1	Alice	A
2	Bob	B
3	Charlie	C

SQL Basics

SQL (Structured Query Language) is the standard language for interacting with relational databases. We'll look at the most common commands.

SELECT Statement

The SELECT statement is used to retrieve data from a database. The basic syntax is:

SELECT column1, column2, ... FROM table_name;

This will retrieve all columns and all rows from the specified table.

WHERE Clause

The WHERE clause is used to filter the data retrieved by the SELECT statement. It specifies a condition that must be met for a row to be included in the result.

Syntax: WHERE condition

Example: SELECT * FROM students WHERE Grade = 'A'; (Retrieves all columns and rows where the Grade is 'A')

Comparison Operators

Common comparison operators used in the WHERE clause include:

• = (equal to)
• != or <> (not equal to)
• > (greater than)
• < (less than)
• >= (greater than or equal to)
• <= (less than or equal to)

Logical Operators

The AND and OR operators are used to combine multiple conditions in the WHERE clause.

• AND: Both conditions must be true.
• OR: At least one condition must be true.

Example: SELECT * FROM students WHERE Grade = 'A' AND Score > 80; (Retrieves students with Grade 'A' and Score greater than 80)

Example: SELECT * FROM products WHERE Category = 'Electronics' OR Category = 'Clothing'; (Retrieves products in either the 'Electronics' or 'Clothing' category)

ORDER BY Clause

The ORDER BY clause is used to sort the results of a SELECT statement. It specifies the column(s) to sort by and the sorting order (ascending or descending).

Syntax: ORDER BY column_name [ASC | DESC]

ASC (ascending) is the default sorting order.

DESC (descending) sorts the data in reverse order.

Example: SELECT * FROM students ORDER BY Grade DESC; (Retrieves students sorted by Grade in descending order)

Example Queries

Query 1: Select all students with a grade of 'B'

SELECT * FROM students WHERE Grade = 'B';

Query 2: Select the names and grades of students with a score greater than 70

SELECT Name, Grade FROM students WHERE Score > 70;

Query 3: Select all products from the 'Electronics' category, sorted by price in descending order

SELECT * FROM products WHERE Category = 'Electronics' ORDER BY Price DESC;

Query 4: Select all students who have a grade of 'A' and a score greater than 80

SELECT * FROM students WHERE Grade = 'A' AND Score > 80;

Query 5: Select all products that are either in the 'Electronics' or 'Clothing' category, sorted alphabetically by name

SELECT * FROM products WHERE Category IN ('Electronics', 'Clothing') ORDER BY Name ASC;

Further Learning

This is a basic introduction to SQL. Further topics include:

• JOINs (combining data from multiple tables)
• INSERT, UPDATE, and DELETE statements (modifying data)
• Database design and normalization