استعلامات SQL الأساسية

Access SQL: Basic Concepts and Terminology

• SQL (Structured Query Language) is a computer language similar to English used to retrieve data from databases.
• Understanding SQL helps create better queries and troubleshoot retrieval issues.
• Essential SQL statements include: SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, and UNION.

What is SQL?

• SQL is a computer language used to work with sets of facts and relationships between them.
• Relational database programs like Microsoft Access utilize SQL to manage data.
• SQL is relatively easy to read and understand even for beginners.
• SQL is an international standard recognized by hardware standards like ISO and ANSI.

Using SQL for Data Retrieval

• SQL is used to describe sets of data to answer specific questions, requiring correct syntax.
• Syntax is a set of rules for combining language elements (e.g., based on English syntax in SQL).
• Example: Retrieving last names where the first name is "Mary".

  SELECT Last_Name
  FROM Contacts
  WHERE First_Name = 'Mary';
  

• Note: SQL is also used for creating and modifying database objects (like tables). Data Definition Language (DDL) is not covered in this section.

SQL Statements and Their Functions

• SQL statements are similar to parts of speech.
• A table outlining common SQL statements and their actions is provided (SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING).
• SELECT: Retrieves specific fields.
• FROM: Specifies the table(s) containing the fields.
• WHERE: Filters records based on specific criteria.
• ORDER BY: Sorts the results.
• GROUP BY: Organizes results into summary groups (typically with aggregate functions).
• HAVING: Filters the results from GROUP BY.

SQL Terms

• SQL uses terms comparable to parts of speech.
• The table illustrates common SQL terms (identifier, operator, adverb, fixed, expression).
• Identifier: Used to name database objects (e.g., table, field names).
• Operator: Represents actions or modifies actions.
• Fixed: Unchangeable values like numbers or NULL.
• Expression: A combination of identifiers, operators, constants, and functions evaluating to a single value.

Basic SQL Statements: SELECT, FROM, WHERE

• Basic SQL statement structure:

  SELECT field_1
  FROM table_1
  WHERE criterion_1;
  

• Access ignores line breaks in SQL statements.
• Semicolons (;) end SELECT statements.

Example in Access

• An example SQL statement in Access displays how to get email and company, filtering by city "Seattle".

  SELECT Email address, Company
  FROM Contacts
  WHERE City = 'Seattle';
  

SQL SELECT Phrase

• The SELECT statement specifies which fields to retrieve.
• Identifiers of fields should be enclosed in square brackets if they contain spaces.

FROM Phrase

• The FROM clause specifies the table where the data is retrieved from.

WHERE Phrase

• The WHERE clause adds filters to the query.

Sort Results: ORDER BY

• The ORDER BY clause is used to sort the query results.

Summary Data Usage: GROUP BY and HAVING

• Use aggregate functions (e.g., COUNT, AVG) for summaries in SELECT statements with GROUP BY.
• GROUP BY groups data based on a field value.

Determining Unused Fields in Aggregate Functions: GROUP BY Statement

• A GROUP BY clause is typically used with aggregate functions.
• If all fields are involved in aggregation, a GROUP BY clause is not needed.
• The GROUP BY clause appears after the WHERE or FROM clause.

Restrict Aggregate Values Using Group Criteria: HAVING Statement

• Use HAVING to filter aggregated data based on criteria not used in the aggregation itself.
• HAVING statements follow the WHERE or FROM clause.

Combine Query Results: UNION

• The UNION operator combines results from multiple SELECT statements.
• The SELECT statements must have the same number and type fields.

Example Combining Tables (Products and Services):

• SQL example combining Products and Services tables using UNION ALL.