sql

Introduction to SQL

  • SQL (Structured Query Language) is a standardized query language for managing relational databases.

  • Development began in the 1970s at IBM, originally named SEQUEL (Structured English Query Language).

  • SQL became an ANSI standard in 1987 and is widely used in various database management systems (DBMS) such as Oracle and Microsoft SQL Server.

Understanding SQL Basics

  • Tables form the basis for data storage in a relational database.

    • A database typically consists of multiple tables.

    • Tables organize logically related data into rows (records) and columns (fields).

  • Each table must have a unique name (e.g., teachers, students).

SQL Statement Types

  • Data Query Language (DQL): Allows querying the database (e.g., SELECT).

  • Data Definition Language (DDL): Used to define and modify database structures (e.g., CREATE, ALTER).

  • Data Manipulation Language (DML): Used for data insertions, updates, and deletions (e.g., INSERT, UPDATE, DELETE).

The SELECT Statement

  • Used to query data from a table.

  • Syntax: SELECT column_names FROM table_name;

  • Example: SELECT name FROM students;

  • Distinct Option: SELECT DISTINCT column FROM table;

    • Returns unique values in the specified column.

Filtering Results with WHERE

  • To narrow down query results, use the WHERE clause.

    • Syntax: SELECT column_names FROM table_name WHERE condition;

  • Example: SELECT name FROM students WHERE year_of_birth=1990;

Operators in WHERE Clause

  • Common operators include:

    • = (equal)

    • <> (not equal)

    • < (less than)

    • > (greater than)

    • <= (less than or equal to)

    • >= (greater than or equal to)

Ordering Results

  • Use the ORDER BY clause to sort results.

  • Syntax: SELECT column_names FROM table_name ORDER BY column1, column2;

  • Example: SELECT * FROM students ORDER BY name;

Grouping Data

  • The GROUP BY clause groups rows sharing a property so that aggregate functions can be applied.

  • Syntax: SELECT column, aggregate_function(column) FROM table_name GROUP BY column;

  • Example: SELECT class_code, AVG(age) FROM students GROUP BY class_code;

Filtering Groups with HAVING

  • Use the HAVING clause to filter groups based on aggregate values.

  • Syntax: SELECT column FROM table GROUP BY column HAVING condition;

Joining Tables

  • Joins combine rows from two or more tables based on a related column.

  • INNER JOIN: Returns only matching rows from both tables.

  • LEFT JOIN: Returns all rows from the left table and matching rows from the right.

  • RIGHT JOIN: Returns all rows from the right table and matching rows from the left.

Using Aliases

  • An alias provides a temporary name for a table or column in a query.

  • Syntax: SELECT column_name AS alias_name FROM table_name;

Subqueries

  • A subquery (nested query) is a query within another SQL query.

  • Example: SELECT name FROM students WHERE age > (SELECT AVG(age) FROM students);

Insert, Update, and Delete Statements

  • INSERT INTO: To add new records.

    • Syntax: INSERT INTO table_name (columns) VALUES (values);

  • UPDATE: To modify existing records.

    • Syntax: UPDATE table_name SET column_name = new_value WHERE condition;

  • DELETE: To remove records.

    • Syntax: DELETE FROM table_name WHERE condition;

Conclusion

  • Mastery of SQL basics is essential for effective database management and manipulation.