SQL Fundamentals Training Course

Relational Database Management System (RDBMS)

Relational Operators

SQL as a Declarative Language

SQL Syntax

SQL Sublanguages: DQL, DML, DDL, and DCL

Detailed Overview of Data Query Language (DQL)

• SELECT Clause: Used to specify the data to be retrieved from one or more tables.
• Column Aliases: Allows renaming columns in query results for better readability and clarity.
• Table Aliases: Simplifies complex queries by assigning a temporary name to a table.
• Date Types and Date Functions: Enables manipulation and formatting of date data.
• Group Functions: Provides aggregate functions for summarizing data, such as COUNT, SUM, and AVG.
• Table Joins: Combines rows from two or more tables based on a related column.
• JOIN Clause: Specifies the conditions for joining tables.
• UNION Operator: Combines the results of multiple SELECT statements into a single result set.
• Nested Queries: Allows one query to be nested within another, enhancing data retrieval capabilities.
• Correlated Subqueries: A subquery that is executed once for each row processed by the outer query.

Data Manipulation Language (DML) Overview

• Inserting Rows (INSERT): Adds new records to a table.
• Inserting Rows Using Subquery: Inserts data from another query into a table.
• Updating Rows (UPDATE): Modifies existing records in a table.
• Deleting Rows (DELETE): Removes records from a table.

Data Definition Language (DDL) Overview

• Creating, Altering, and Dropping Objects (CREATE, ALTER, DROP): Manages the structure of database objects.
• Creating Tables Using Subquery (CREATE TABLE ... AS SELECT...): Creates a new table based on the results of a query.

Constraints Overview

• NULL and NOT NULL: Specifies whether a column can contain null values.
• CONSTRAINT Clause: Defines rules to ensure data integrity, such as primary keys and foreign keys.
• ENUM Type: Allows defining columns with a fixed set of possible values.
• SET Type: Stores multiple values in a single column.
• PRIMARY KEY: Uniquely identifies each record in a table and ensures no duplicate values.
• UNIQUE: Ensures that all values in a column are distinct.
• FOREIGN KEY: Establishes a link between two tables, ensuring referential integrity.
• DEFAULT Clause: Specifies a default value for a column if no value is provided during insertion.

Transactions Overview

• COMMIT: Permanently saves changes made in a transaction.
• ROLLBACK: Undoes all changes made in the current transaction.
• SAVEPOINT: Marks a point within a transaction to which you can roll back without undoing the entire transaction.
• Implicit and Explicit Rollbacks and Commits: Defines how transactions are managed automatically or manually by the database system for government operations.

SQL Dialects Overview

• MySQL: A widely used open-source relational database management system.
• Microsoft Access and SQL Server: Popular choices for small to medium-sized applications and enterprise-level solutions, respectively.
• Oracle and PostgreSQL: Robust and feature-rich options suitable for large-scale and complex databases for government use.