Structured Query Language (SQL) - Part 1

Structured Query Language (SQL) is the standard language used to communicate with and manage data in relational database management systems (RDBMS). It is a powerful, high-level language that allows users to interact with databases by telling the system what they want to achieve, rather than how to achieve it. This makes SQL a declarative language – you declare your desired result, and the RDBMS figures out the steps to get there.

SQL is used for:
● Creating and modifying database structures: Defining tables, specifying rules (constraints), and setting up relationships.
● Manipulating data: Inserting new records, updating existing ones, deleting records, and retrieving information.
● Controlling access: Granting or revoking permissions to users.

SQL's origins trace back to the early 1970s at IBM.
● It was initially developed by Donald D. Chamberlin and Raymond F. Boyce at IBM's San Jose Research Laboratory.
● Their original language was called SEQUEL (Structured English Query Language), designed for IBM's System R experimental RDBMS.
● The name was later shortened to SQL due to a trademark conflict.
● SQL quickly gained popularity, leading to commercial implementations such as IBM's SQL/DS and DB2.
● Its success spurred other database vendors to adopt similar languages, eventually leading to the need for standardization.

To ensure interoperability and portability across different database systems, SQL has been standardized by major organizations:
● The American National Standards Institute (ANSI) first published an SQL standard in 1986.
● The International Organization for Standardization (ISO) also publishes SQL standards, often in conjunction with ANSI.
● Key standard versions include SQL-92 (a significant milestone), SQL:1999, SQL:2003, SQL:2008, SQL:2011, and SQL:2016.
● Importance of Standards: While different RDBMS (like MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server) have their own variations or "dialects" of SQL, they largely adhere to the core ANSI/ISO SQL standards. This adherence means that a basic SQL query written for one database system will often work with minor or no modifications on another, making SQL a truly universal language for relational databases.

The Data Definition Language (DDL) portion of SQL is used to define, modify, and delete the structure (or schema) of your database. Think of DDL commands as the tools you use to build the "empty boxes" (tables) in your database and set the rules for what kind of data can go into them. DDL statements affect the database schema, not the data itself.

The primary DDL commands we will cover are CREATE TABLE, ALTER TABLE, and DROP TABLE.

The CREATE TABLE statement is used to create a new table in your database. When you create a table, you specify its name, the names of all its columns, the data type for each column, and any constraints (rules) that apply to those columns or the table as a whole.

General Syntax:

CREATE TABLE table_name (
column1_name data_type [column_constraint],
column2_name data_type [column_constraint],
...
[table_constraint]
);

● CREATE TABLE: The keywords that initiate the creation of a new table.
● table_name: The unique name you choose for your new table (e.g., Students, Courses).
● column_name: The unique name for each column within that table (e.g., StudentID, FirstName).
● data_type: Specifies the type of data that can be stored in that column (e.g., INTEGER, VARCHAR(100), DATE). We'll discuss basic data types in Section 4.4.
● [column_constraint]: An optional rule that applies to a single column (e.g., NOT NULL, UNIQUE). These are placed immediately after the column's data type.
● [table_constraint]: An optional rule that applies to the table as a whole, or involves multiple columns (e.g., PRIMARY KEY (col1, col2), FOREIGN KEY). These are typically placed after all column definitions.

Constraints are rules that are enforced on data columns or tables to maintain data integrity and consistency, as discussed in Module 2. They ensure that data conforms to specific business rules and prevent invalid data from being inserted, updated, or deleted.

Constraints can be defined in two ways:
● Inline (Column-level): Defined immediately after the column definition to which they apply.
● Out-of-line (Table-level): Defined separately after all column definitions, typically for constraints that involve multiple columns or for better readability.

Let's detail the most common constraints:
● PRIMARY KEY Constraint:
○ Purpose: Uniquely identifies each row (tuple) in a table. It ensures that no two rows have the same value for the primary key column(s), and that no part of the primary key is NULL. A table can have only one primary key.
... (further explanation of additional constraints follows)

The ALTER TABLE statement is used to modify the structure of an existing table. You can use it to add, drop, or modify columns, or to add and remove constraints.

Common ALTER TABLE operations:
Adding a New Column:

ALTER TABLE table_name
ADD COLUMN new_column_name data_type [constraint];

Dropping an Existing Column:

ALTER TABLE table_name
DROP COLUMN column_name;

... (further operations elaboration)

The DROP TABLE statement is used to completely remove an existing table from the database. This command deletes both the table's structure (schema) and all the data within it.

General Syntax:

DROP TABLE table_name;

Caution: This is an irreversible operation! Once a table is dropped, its data and structure are lost unless you have a backup.