Lesson 5

For a complete listing of DistributedNetworks courses, please visit the next page which contains a link to additional websites.
DistributedNetworks offers courses on a variety of topics, including network security and firewalls, ebusiness architecture, computer science, programming, and design patterns.
If you are going to work with a relational database, whether you are

1. writing applications,
2. performing administrative tasks, or
3. generating reports,

you will need to know how to interact with the data in your database. Even if you are using a tool that generates SQL for you, such as a reporting tool, there may be times when you need to bypass the automatic generation feature and write your own SQL statements. Learning SQL has the added benefit of forcing you to confront and understand the data structures used to store information about your organization. As you become comfortable with the tables in your database, you may find yourself proposing modifications or additions to your database schema.

• Advanced SQL Module Review
  The SQL language is broken into several categories. Statements used to create database objects (tables, indexes, constraints) and these are collectively known as SQL schema statements. The statements used to create, manipulate, and retrieve the data stored in a database are known as the SQL data statements. If you are an administrator, you will be using both SQL schema and SQL data statements. If you are a programmer or report writer, you may only need to use (or be allowed to use) SQL data statements. While this course demonstrates many of the SQL schema statements, the main focus of this book is on programming features. With only a handful of commands, the SQL data statements look deceptively simple. If you are going to work with SQL, it behooves you to understand fully the capabilities of the language and how different features can be combined to produce powerful results. While the examples in this module run on MySQL, Oracle Database, and SQL Server, I had to pick one of those products to host my sample database and to format the result sets returned by the example queries. Of the three, I chose MySQL because it is freely obtainable, easy to install, and simple to administer.

SQL schema statements are a fundamental part of the SQL language, used to define and manage the structural components of a database. These statements allow database administrators and developers to create, modify, and remove database objects such as tables, indexes, views, sequences, and constraints. In the context of "SQL:2023", the latest revision of the SQL standard, there are enhancements and new features aimed at improving schema management, supporting new data types, and providing more robust tools for database design.
Key SQL Schema Statements:

1. CREATE: Used to create new database objects.
   • CREATE TABLE: Defines a new table along with its columns, data types, and constraints.
   • CREATE INDEX: Creates an index on one or more columns of a table to improve query performance.
   • CREATE VIEW: Establishes a virtual table based on the result set of a SELECT query.
   • CREATE SCHEMA: Introduces a new schema to group database objects logically.
2. ALTER: Modifies existing database objects.
   • ALTER TABLE: Adds, modifies, or drops columns and constraints in an existing table.
   • ALTER INDEX: Changes properties of an existing index.
3. DROP: Removes existing database objects.
   • DROP TABLE: Deletes a table and all of its data from the database.
   • DROP INDEX: Eliminates an index from the database.
4. TRUNCATE: Removes all records from a table efficiently without deleting the table structure.
5. COMMENT: Adds descriptive comments to database objects for better documentation and readability.

Enhancements in SQL:2023 Schema Statements:

• Extended Data Type Support: SQL:2023 introduces improved support for JSON, XML, and other semi-structured data types, allowing for more flexible schema definitions.
• Generated Always Columns: Enhancements in defining columns that automatically compute their values based on expressions or functions.
• Temporal Tables: Improved support for system-versioned and application-versioned temporal tables to track historical data changes.
• Enhanced Constraint Capabilities: New options for defining constraints that enforce data integrity more precisely.

SQL Data Statements SQL data statements are used to manipulate the data stored within the database. These statements enable users to insert new data, update existing data, delete unwanted data, and retrieve data through queries. SQL:2023 brings advancements to data statements to handle modern data processing requirements, improve performance, and offer more powerful querying capabilities.
Key SQL Data Statements:

1. SELECT: Retrieves data from one or more tables.
   • Supports clauses like "WHERE", "GROUP BY", "HAVING", "ORDER BY".
   • Includes advanced features like window functions, common table expressions (CTEs), and recursive queries.
2. INSERT: Adds new rows to a table.
   • INSERT INTO ... VALUES: Inserts specific values into a table.
   • INSERT INTO ... SELECT: Inserts data resulting from a SELECT query.
3. UPDATE: Modifies existing data in a table.
   Updates can be conditional based on a "WHERE" clause.
4. DELETE: Removes existing rows from a table.
   Deletes can be conditional based on a "WHERE" clause.
5. MERGE: Performs insert, update, or delete operations within a single statement based on conditions, useful for synchronizing tables.

Enhancements in SQL:2023 Data Statements:

• Expanded MERGE Capabilities: More flexible matching and action clauses to handle complex data manipulation scenarios.
• Improved JSON Functions: Enhanced functions for querying and manipulating JSON data directly within SQL.
• Polymorphic Table Functions: Allows functions to return result sets with varying structures, improving flexibility in data retrieval.
• Enhanced Analytical Functions: Additional window functions and statistical aggregates for advanced data analysis.

Transaction Control and Data Integrity
While not exclusively categorized under schema or data statements, transaction control commands are vital for maintaining data integrity:

• BEGIN TRANSACTION: Starts a new transaction block.
• COMMIT: Saves all changes made in the current transaction.
• ROLLBACK: Undoes all changes made in the current transaction.

Constraints such as "PRIMARY KEY", "FOREIGN KEY", "UNIQUE", "CHECK", and "NOT NULL" defined within schema statements ensure data integrity by enforcing rules at the database level.
Security and Access Control:
SQL:2023 continues to emphasize database security through statements that control user access and privileges:

• GRANT: Assigns specific privileges to users or roles.
• REVOKE: Removes previously granted privileges from users or roles.
• CREATE ROLE and DROP ROLE: Manage roles for grouping privileges.

Conclusion In the context of SQL:2023, both SQL schema statements and SQL data statements have evolved to address the complexities of modern data management. The standard introduces enhancements that support new data types, improve performance, and offer more powerful tools for developers and database administrators. By expanding the capabilities of schema and data statements, SQL:2023 ensures that SQL remains a robust and versatile language for relational database management in an ever-changing technological landscape.
Key Takeaways:

• SQL Schema Statements: Focus on defining and managing the database structure, with enhancements in data types, constraints, and schema flexibility in SQL:2023.
• SQL Data Statements: Deal with data manipulation and retrieval, with improvements in data processing functions, analytical capabilities, and handling of semi-structured data in SQL:2023.
• SQL:2023 Enhancements: Aim to support modern data requirements, including big data, analytics, and advanced data types like JSON and XML.

By understanding and utilizing these statements effectively, users can design robust databases, maintain data integrity, and perform complex data manipulations efficiently in the latest SQL standard.

A database is nothing more than a set of related information. A telephone book, for example, is a database of the names, phone numbers, and addresses of all people living in a particular region. While a telephone book is certainly a ubiquitous and frequently used database, it suffers from the following:

1. Finding a person's telephone number can be time-consuming, especially if the telephone book contains a large number of entries.
2. A telephone book is indexed only by last/first names, so finding the names of the people living at a particular address, while possible in theory, is not a practical use for this database.
3. From the moment the telephone book is printed, the information becomes less and less accurate as people move into or out of a region, change their telephone numbers, or move to another location within the same region.

The same drawbacks attributed to telephone books can also apply to any manual data storage system, such as patient records stored in a filing cabinet. Because of the cumbersome nature of paper databases, some of the first computer applications developed were database systems, which are computerized data storage and retrieval mechanisms.
Because a database system stores data electronically rather than on paper, a database system is able to retrieve data more quickly, index data in multiple ways, and deliver up-to-the-minute information to its user community. Early database systems managed data stored on magnetic tapes.

Because there were generally far more tapes than tape readers, technicians were tasked with loading and unloading tapes as specific data was requested. Because the computers of that era had very little memory, multiple requests for the same data generally required the data to be read from the tape multiple times. While these database systems were a significant improvement over paper databases, they are a far cry from what is possible with today's technology.
Modern database systems can manage terabytes of data spread across many fast-access disk drives, holding tens of gigabytes of that data in high-speed memory.