Introduction to SQL Concepts

Study Notes

Introduction to SQL

SQL (Structured Query Language) is a domain-specific language used for managing and manipulating databases.
It's used to create, read, update, and delete data in a database relational model.
SQL is standardized, meaning commands and syntax are generally consistent across various database systems.
Common database systems utilizing SQL include MySQL, PostgreSQL, Oracle, and SQL Server.

Core SQL Concepts

Database: A structured collection of data organized into tables.
Table: A collection of related data organized into rows (records) and columns (attributes).
Row (Record): A single entry in a table representing information about a specific entity.
Column (Attribute): A field containing data about a specific property of an entity.
Data Types: Define the kind of data a column can store (e.g., integer, text, date).

Data Definition Language (DDL)

CREATE TABLE: Creates a new table.
- Specifies column names and data types.
- Optionally defines constraints like primary keys and foreign keys.
ALTER TABLE: Modifies an existing table.
- Adds, modifies, or deletes columns.
- Alters constraints.
DROP TABLE: Deletes a table.

Data Manipulation Language (DML)

SELECT: Retrieves data from one or more tables.
- Specifies which columns to retrieve.
- Filters data using WHERE clause.
- Orders the results using ORDER BY.
- Groups data using GROUP BY.
INSERT: Adds new rows to a table.
UPDATE: Modifies existing rows in a table.
DELETE: Removes rows from a table.

Data Querying using SQL

SELECT statements: fundamental for retrieving data from databases.
WHERE Clause: Used to filter records that match specified criteria. (e.g., WHERE age > 30).
JOIN Clause: Combines rows from two or more tables based on a related column. (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN).
Aggregate Functions: Perform calculations on sets of values. (e.g., COUNT, SUM, AVG, MAX, MIN).
GROUP BY Clause: Groups rows that have the same values in specified columns.
ORDER BY Clause: Sorts the result set in ascending or descending order based on one or more columns.
Subqueries: A query embedded within another query.

Constraints and Keys

Primary Key: Uniquely identifies each row in a table.
Foreign Key: Establishes a link between two tables.
Unique Constraint: Ensures that all values in a column are unique.
Not Null Constraint: Ensures that a column does not contain null values.

SQL Data Types

Data types define the type of data that can be stored in a column of a table.
Common data types include INT, VARCHAR, DATE, FLOAT, BOOLEAN.

Transaction Management

Transactions: A sequence of database operations treated as a single logical unit of work.
COMMIT: Saves changes made during a transaction.
ROLLBACK: Reverts changes made during a transaction if something goes wrong.

Advanced SQL Concepts

Views: Virtual tables based on queries.
Stored Procedures: Precompiled SQL code for reusable functionalities.
Indexes: Speed up data retrieval by creating a lookup table.
Triggers: Automatically execute SQL code in response to certain events (like inserting, updating, or deleting data).

SQL Security

User accounts and permissions are essential for access control.

Description

This quiz covers the foundational aspects of Structured Query Language (SQL), including its core concepts and applications in managing databases. Explore key terminology such as databases, tables, rows, and columns, as well as the Data Definition Language (DDL) commands like CREATE TABLE.