Structured Query Language (SQL)

Chapter 10: Structured Query Language (SQL)

This chapter provides a comprehensive introduction to Structured Query Language (SQL), the standard language
for managing and manipulating relational databases. Students will learn the fundamental concepts of relational
databases, understand the different categories of SQL commands (DDL, DML, DQL), and gain practical skills in
creating database structures, inserting and updating data, and writing complex queries to retrieve information from
single or multiple tables.

10.1 Introduction to Databases and SQL

10.1.1 Database Concepts

A database is an organized collection of structured information, or data, that is typically stored electronically
within a computer system. The system that manages a database is known as a Database Management System
(DBMS). At a fundamental level, it is important to distinguish between key concepts. Data refers to raw,
unorganized facts, while information is data that has been processed to be meaningful. The database itself is the
structured collection of this data, and the DBMS is the software that interacts with users, applications, and the
database itself to capture and analyze data. Using a DBMS offers several significant advantages, including
ensuring data integrity, which means the data is accurate and consistent. It also helps in reducing data redundancy
by eliminating duplicate data, enhances data security by controlling access, and facilitates the easy retrieval of data
through query languages.

10.1.2 Relational Data Model

The relational model is a prominent approach for database management, based on the principles of first-order
predicate logic. The core concept of this model is to represent a database as a collection of relations, which are
more commonly known as tables. In this structure, data is meticulously organized into tables consisting of rows and
columns. Each row, formally called a tuple, represents a single record or entity. Each column, formally called an
attribute, represents a specific property of that entity. Several key terminologies are central to understanding the
relational model. A Relation is the table itself. A Tuple is a single row in the table. An Attribute is a single column.
[Link] me/edzaaic hat [Link] [Link]
 Structured Query Language (SQL)

The Domain of an attribute is the set of all possible values that the attribute can hold. The Degree of a relation
refers to the number of attributes (columns) it has, while its Cardinality refers to the number of tuples (rows) it
contains.

10.1.3 Keys in a Relational Database

Keys are a fundamental component of the relational model, serving as special attributes used to uniquely identify
records within a table and to create logical links between different tables. A Primary Key is a constraint that
uniquely identifies each record, or tuple, in a table. A primary key must contain unique values for each row and
cannot contain null values. A Foreign Key is an attribute or a set of attributes in one table that refers to the
primary key of another table. It acts as a cross-reference, establishing a formal link and enforcing referential
integrity between the two tables. A Candidate Key is an attribute or a set of attributes that could potentially serve
as a primary key because it can uniquely identify a tuple. From the set of available candidate keys, one is chosen to
be the official Primary Key for the table.

10.2 SQL Fundamentals and Data Definition Language (DDL)

10.2.1 Introduction to SQL

Structured Query Language, or SQL, is the universally recognized standard language for interacting with and
managing relational databases. It is a declarative language, which means that the user specifies what data they
want to retrieve or what task they want to perform, without having to detail the exact procedural steps of how to
accomplish it. SQL commands are organized into several distinct categories based on their function. These
categories include Data Definition Language (DDL) for defining the database schema, Data Manipulation Language
(DML) for managing the data itself, Data Query Language (DQL) for retrieving data, Data Control Language
(DCL) for managing access rights, and Transaction Control Language (TCL) for managing transactions.

10.2.2 Data Types in SQL

When defining the structure of a table in a database, it is mandatory to assign a specific data type to each column.
The data type determines the kind of data that the column can store, such as numbers, text, or dates. The selection
of an appropriate data type is crucial for maintaining data integrity and optimizing storage space. Common SQL
data types are grouped into several categories. Numeric types include `INT` for whole numbers and `DECIMAL`
for numbers with fractional parts. Character string types include `CHAR` for fixed-length strings,
`VARCHAR(n)` for variable-length strings up to a specified maximum length `n`, and `TEXT` for long-form text.
Date and time types include `DATE` for storing calendar dates, `TIME` for storing time of day, and
`TIMESTAMP` for storing both date and time information together.

[Link] me/edzaaic hat [Link] [Link]

 Structured Query Language (SQL)

10.2.3 DDL Commands: CREATE, ALTER, DROP

Data Definition Language (DDL) consists of SQL commands used to create, modify, and delete the structure of
database objects, such as tables. The `CREATE TABLE` command is used to build a new table in the database.
When using this command, you must define each of its columns, specifying a name, a data type, and any applicable
constraints for each. Common constraints include `PRIMARY KEY` to designate a column as the unique identifier
and `NOT NULL` to ensure a column cannot have empty values. The `ALTER TABLE` command is used to modify
the structure of an existing table. This can involve actions such as adding a new column, deleting an existing column,
or changing the data type or constraints of a column. The `DROP TABLE` command is used to permanently
remove an entire table, including its structure, data, and associated indexes, from the database.

10.3 Data Manipulation and Querying

10.3.1 DML Commands: INSERT, UPDATE, DELETE

Data Manipulation Language (DML) commands are used to manage the data contained within the database schema
objects. The `INSERT INTO` statement is used to add one or more new rows of data into a specified table. The
`UPDATE` statement is used to modify existing records that are already present in a table. It is critically
important to use a `WHERE` clause with the `UPDATE` statement to specify exactly which record or records
should be changed; without it, the command would modify all records in the table. Similarly, the `DELETE`
statement is used to remove existing records from a table. The `WHERE` clause is equally essential here to
identify the specific records to be removed, as omitting it would result in the deletion of every record in the table.

10.3.2 The SELECT Statement and WHERE Clause

The `SELECT` statement is the fundamental command of the Data Query Language (DQL) and is used to retrieve
data from one or more tables in a database. To retrieve all columns from a table, an asterisk (`*`) is used.
Alternatively, you can specify the names of individual columns to retrieve only the data you need. To filter the
results of a query, the `WHERE` clause is used. This clause extracts only those records that satisfy a specific
condition. The `WHERE` clause employs various operators to define these conditions, including comparison
operators such as = (equal to), `<>` (not equal to), `>` (greater than), and `<` (less than). It also uses logical
operators like `AND`, `OR`, and `NOT` to combine multiple conditions. Furthermore, special operators provide
more advanced filtering capabilities, such as `BETWEEN` for selecting values within a range, `IN` for specifying a
list of possible values, and `LIKE` for performing pattern matching on string data.

[Link] me/edzaaic hat [Link] [Link]

 Structured Query Language (SQL)

10.3.3 Sorting, Grouping, and Aggregate Functions

The results of a query can be further organized and analyzed to improve readability and derive meaningful insights.
The `ORDER BY` clause is used to sort the result-set based on one or more columns in either ascending (`ASC`)
or descending (`DESC`) order. Aggregate functions are used to perform a calculation on a set of values, returning
a single summary value. Common aggregate functions include `COUNT()`, which counts the number of rows;
`SUM()`, which calculates the total sum of a numeric column; `AVG()`, which computes the average value; and
`MIN()` and `MAX()`, which find the minimum and maximum values in a set, respectively. The `GROUP BY`
statement is often used in conjunction with aggregate functions. It groups rows that share the same values in
specified columns into summary rows. For example, it can be used to count the number of students in each class.
Finally, the `HAVING` clause is used to filter these groups based on conditions involving the aggregate functions,
which is something the `WHERE` clause cannot do.

10.4 Joins and Advanced SQL

10.4.1 Concept of Joins

In relational database design, data is commonly distributed across

multiple tables to minimize data redundancy and improve data integrity, a
practice known as normalization. A JOIN clause is an essential SQL
mechanism used to combine rows from two or more tables into a single
result set. This combination is based on a related column that exists
between the tables. Most often, this relationship is defined by a primary
key in one table and a corresponding foreign key in another table. Joins
enable the execution of powerful queries that can retrieve a complete
and unified view of related data that is physically stored in separate
tables.

10.4.2 Types of Joins: INNER and LEFT JOIN

There are several types of joins available in SQL, each serving a different purpose. The most frequently used type
is the `INNER JOIN` (which can also be written simply as `JOIN`). This join returns only the records that have
matching values in the specified columns of both tables being joined. An `OUTER JOIN`, by contrast, can retrieve
records even when there is no matching record in the other table. A `LEFT JOIN` is a type of outer join that
returns all records from the left table (the first table mentioned in the `JOIN` clause) and only the matched
records from the right table. If a record in the left table does not have a corresponding match in the right table, the
result set will contain `NULL` values for all columns from the right table for that record.

[Link] me/edzaaic hat [Link] [Link]

 Structured Query Language (SQL)

Activities

Activity 1: Creating a Student Information Database

In this activity, you will design and implement a simple database to manage student information. The first step is to
create a `Students` table. This table should include columns such as `StudentID`, `FirstName`, `LastName`,
`DateOfBirth`, and `ClassID`. You will write the `CREATE TABLE` SQL statement to define this structure. It is
important to select appropriate data types for each column, for example, `INT` for `StudentID` and `ClassID`,
`VARCHAR` for names, and `DATE` for `DateOfBirth`. You must also apply constraints, designating
`StudentID` as the `PRIMARY KEY` and ensuring that key fields like `FirstName` and `LastName` are set to
`NOT NULL`.

Activity 2: Data Entry and Basic Queries

Using the `Students` table created in the previous activity, you will now populate it with data. Use the `INSERT
INTO` statement to add at least 10 fictional student records to the table. Once the table is populated, you will
practice writing basic `SELECT` queries to retrieve this data. For instance, you will write a query to select all
students, another to select students with a specific last name, and a third to select students born after a certain
date by using the `WHERE` clause. You will also practice data manipulation by using the `UPDATE` statement to
change a student's class and the `DELETE` statement to remove a specific student record.

Activity 3: Querying with Joins and Aggregate Functions

This activity expands the database by adding a new `Classes` table. This table will have columns such as `ClassID`,
`ClassName`, and `TeacherName`. After creating the `Classes` table, you will establish a relationship between
the `Students` and `Classes` tables by using `ClassID` as a foreign key in the `Students` table. The primary
objective is to write more complex queries that combine data from both tables. You will be asked to perform the
following tasks: first, use an `INNER JOIN` to create a list showing each student's full name alongside their
corresponding class name. Second, use the `COUNT()` aggregate function with `GROUP BY` to determine the
number of students enrolled in each class. Third, use a `LEFT JOIN` to list all classes, including those that
currently have no students enrolled.

[Link] me/edzaaic hat [Link] [Link]

Structured Query Language (SQL)

[Link] me/edzaaic hat [Link] [Link]