SQL - how to write a simple query

1. SELECT

• Purpose: Used to specify which columns of data to retrieve from a table.
• Syntax:

  SELECT column_name1, column_name2

  • Use * to select all columns.
  • Example:

    SELECT name, age FROM students;

2. FROM

• Purpose: Specifies the table from which the data will be retrieved.
• Syntax:

  FROM table_name

  • Example:

    SELECT * FROM books;
    

3. WHERE

• Purpose: Filters the results to only include rows that meet certain conditions.
• Syntax:

  WHERE condition

  • Example:

    SELECT name, grade FROM students WHERE grade > 70;

  • Operators for conditions include:
    • =: Equal to
    • >: Greater than
    • <: Less than
    • >=: Greater than or equal to
    • <=: Less than or equal to
    • <>: Not equal to

4. AND/OR

• Purpose: Combines multiple conditions.
• Syntax:

  WHERE condition1 AND condition2
  WHERE condition1 OR condition2
  

  • Example:

    SELECT name FROM students WHERE grade > 70 AND attendance > 90;

5. ORDER BY

• Purpose: Sorts the results by one or more columns.
• Syntax:

  ORDER BY column_name [ASC|DESC]

  • ASC (default): Ascending order
  • DESC: Descending order
  • Example:

    SELECT name, grade FROM students ORDER BY grade DESC;

6. LIMIT (Optional in some SQL dialects)

• Purpose: Restricts the number of rows returned.
• Syntax:

  LIMIT number_of_rows

  • Example:

    SELECT name FROM students LIMIT 5;

General Rules

1. Case Sensitivity: SQL keywords (e.g., SELECT, WHERE) are not case-sensitive.
2. Semicolon (;): End each statement with a semicolon to mark its completion.
3. Quotes: Use single quotes (') for text values. Double quotes are used for column/table names if necessary.
4. Comments:
   • Single-line: -- This is a comment
   • Multi-line: /* This is a multi-line comment */

Example Query

Retrieve the names and grades of students who scored above 70, sorted by grade in descending order:

SELECT name, grade
FROM students
WHERE grade > 70
ORDER BY grade DESC;