Essential SQL Commands to Know for Intro to Database Systems

SQL commands are essential for managing and manipulating data in databases. They allow you to retrieve, insert, update, and delete records, as well as define table structures and relationships, making data management efficient and organized.

1. SELECT

   • Retrieves data from one or more tables in a database.
   • Can specify particular columns or use * to select all columns.
   • Supports filtering, sorting, and grouping of results.

2. INSERT

   • Adds new records to a table.
   • Requires specifying the target table and the values for each column.
   • Can insert multiple rows in a single command.

3. UPDATE

   • Modifies existing records in a table.
   • Requires specifying the target table and the new values for columns.
   • Must include a WHERE clause to avoid updating all records unintentionally.

4. DELETE

   • Removes records from a table.
   • Requires specifying the target table and conditions for which records to delete.
   • Must include a WHERE clause to prevent deleting all records.

5. CREATE TABLE

   • Defines a new table and its structure in the database.
   • Requires specifying the table name and the data types for each column.
   • Can include constraints like PRIMARY KEY and FOREIGN KEY.

6. ALTER TABLE

   • Modifies the structure of an existing table.
   • Can add, modify, or drop columns and constraints.
   • Allows for changing data types and renaming tables.

7. DROP TABLE

   • Permanently removes a table and its data from the database.
   • Cannot be undone; all data in the table is lost.
   • Requires careful consideration before execution.

8. CREATE INDEX

   • Improves the speed of data retrieval operations on a table.
   • Creates a pointer to the data in a table based on specified columns.
   • Can be unique or non-unique, affecting how duplicates are handled.

9. WHERE

   • Filters records based on specified conditions.
   • Can be used with SELECT, UPDATE, and DELETE commands.
   • Supports various operators like =, <, >, AND, OR, and NOT.

10. JOIN

    • Combines rows from two or more tables based on related columns.
    • Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
    • Essential for querying related data across multiple tables.

11. GROUP BY

    • Groups rows that have the same values in specified columns.
    • Often used with aggregate functions like COUNT, SUM, AVG.
    • Helps in summarizing data for reporting purposes.

12. ORDER BY

    • Sorts the result set of a query by one or more columns.
    • Can specify ascending (ASC) or descending (DESC) order.
    • Enhances readability and organization of query results.

13. HAVING

    • Filters groups created by GROUP BY based on specified conditions.
    • Used with aggregate functions to restrict results.
    • Allows for more complex queries involving grouped data.

14. UNION

    • Combines the result sets of two or more SELECT statements.
    • Removes duplicate records by default; use UNION ALL to include duplicates.
    • Requires that all SELECT statements have the same number of columns with compatible data types.

15. CREATE VIEW

    • Defines a virtual table based on the result of a SELECT query.
    • Simplifies complex queries by encapsulating them in a view.
    • Can be used to restrict access to specific data in a table.