Databases - SQL Queries

This section focuses on understanding and writing SQL scripts to retrieve data from a database. We will cover fundamental SQL commands and how to combine them to perform various queries.

Key SQL Concepts

• Database: An organized collection of data.
• Table: A collection of related data organized in rows and columns.
• Record (Row): A single instance of the data in a table.
• Field (Column): A specific attribute of the data in a table.
• SQL (Structured Query Language): The standard language for interacting with databases.

Fundamental SQL Commands

• SELECT: Used to retrieve data from a table.
• FROM: Specifies the table from which to retrieve data.
• WHERE: Filters the data based on specified conditions.
• ORDER BY: Sorts the retrieved data.
• LIMIT: Restricts the number of rows returned.

Example Database Schema

Let's consider a simple database for students:

Student ID	Name	Subject	Marks
1	Alice	Maths	85
2	Bob	Science	92
3	Charlie	Maths	78
4	David	English	95
5	Eve	Science	88

SQL Queries Examples

1. Selecting all data from the 'Students' table

This query retrieves all columns and all rows from the table.

SELECT *
FROM Students;
    

2. Selecting specific columns

This query retrieves only the 'Name' and 'Marks' columns.

SELECT Name, Marks
FROM Students;
    

3. Selecting data with a condition (WHERE clause)

This query retrieves the names and marks of students who study 'Maths'.

SELECT Name, Marks
FROM Students
WHERE Subject = 'Maths';
    

4. Sorting the results (ORDER BY clause)

This query retrieves all data, sorted by 'Marks' in descending order.

SELECT *
FROM Students
ORDER BY Marks DESC;
    

5. Limiting the number of results (LIMIT clause)

This query retrieves only the first 2 students.

SELECT *
FROM Students
LIMIT 2;
    

6. Combining multiple clauses

This query retrieves the names and marks of students who study 'Science' and have marks greater than 90.

SELECT Name, Marks
FROM Students
WHERE Subject = 'Science' AND Marks > 90;
    

Practice Questions

1. Write an SQL query to select the names of all students.
2. Write an SQL query to select the subject and marks of students who study English.
3. Write an SQL query to select all columns from the table where the marks are less than 80.
4. Write an SQL query to select the names of the top 2 students (highest marks).

These examples provide a foundation for understanding and writing SQL queries. Further topics include aggregate functions (e.g., COUNT, SUM, AVG), grouping data (GROUP BY), and joining tables.