SQL Language Notes

Chapter 1: Introduction to SQL Language

  • Overview of SQL:

    • SQL (Structured Query Language) is the language used to store, manipulate, and retrieve data from a relational database.
    • Data is organized in tabular form (rows and columns), with relations defined via foreign keys.

  • Using Sample Files:

    • Important to utilize provided sample files which contain database creation and manipulation statements.
    • Sample files include:
      • Database Creation Statement: Sets up the schema.
      • Create Table Statements: Define the structure of the tables.
      • Insert Statements: Used to populate tables with data.
      • Reporting Queries: Helps in data retrieval for project requirements.

  • Writing SQL Statements:

    • Best Practices:
      • Emphasize correctness, readability, and optimization.
      • Avoid premature optimization; focus first on obtaining correct data.
    • Understanding the Database:
      • Use SQL Server to inspect database structure (e.g., SELECT TOP 10 * FROM table_name; to preview data).

Chapter 2: Database and Data

  • SQL Statement Structure:
    • Common pattern: SELECT (fields) FROM (table) WHERE (conditions) ORDER BY (fields) GROUP BY (fields) HAVING (conditions)
    • Only SELECT and FROM are required components; others like WHERE or ORDER BY are optional.
    • Use of WHERE clause is critical to limit returned data and maintain performance.
Example of Writing SELECT Statements:
  • Basic Example:
    ```sql
    SELECT customerID, lastName, firstName FROM Customers 
   - Specify fields in a comma-separated list after `SELECT`, followed by `FROM` and the table name.

### Use of Wildcards in WHERE Clause:  
- Example with `LIKE` operator:  
   - `SELECT * FROM students WHERE name LIKE 'j%';` (Finds names that start with 'j').  
   - Use `%` for wildcard matching.

## Chapter 3: Ordering Data  
- **Ordering Results**:  
   - `ORDER BY` clause allows sorting results, using `ASC` for ascending and `DESC` for descending orders.
- **Aggregating Data**:  
   - Use of aggregate functions like `SUM()`, `AVG()`, `MAX()`, etc., to perform calculations across data.
- **Data Manipulation Language (DML)**:  
   - Includes commands: SELECT, INSERT, UPDATE, DELETE.
   - Properly format statements for readability; each statement on a new line for clarity.

## Chapter 4: Deleting Data  
- **Delete Command**:  
   - Deletes rows using the syntax:

sql
DELETE FROM table_name WHERE condition; 

   - Always utilize a `WHERE` clause to avoid deleting all entries from the table.

## Chapter 5: SQL Server and Data  
- **Data Definition Language (DDL)**:  
   - Commands for creating, modifying, and deleting database objects (tables, schemas).  
- **Creating Tables**:  
   - Example syntax for creating table:

sql
CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50), Age INT);
```

  • Data types differ between SQL implementations (e.g., SQL Server vs MySQL).

Chapter 6: Individual Project Guidelines

  • Project Creation Requirements:
    • Create DDL, DML, and reporting scripts as outlined in the project document.
    • Include a minimum of 20 records in specified tables and implement at least three of your custom DML scripts (insert, update, delete).
    • Total six queries in the report file, including aggregates like total revenue.

Chapter 7: Conclusion

  • Final Guidance:
    • Ensure all project files are well-structured and follow provided guidelines.
    • Reach out for assistance if issues arise, and utilize resources for SQL server learning.
    • Stay informed on differences between SQL implementations to avoid future errors while coding.