Module 8 - Advanced SQL

SQL Overview

• SQL (Structured Query Language)

  • A complete database language used for managing and manipulating databases.

  • Key Functions:

    • Data Definition: Creating and defining tables and views.

    • Data Manipulation: Performing operations like inserting, updating, and deleting data.

    • Common commands: INSERT, UPDATE, DELETE.

SQL Characteristics

• Not a complete programming language on its own; often used with languages like Java, C#, and PHP.

• Embedded SQL: Allows SQL commands to be included within a host programming language.

Data Definition

• Tables, views, and indexes can be defined while the system is operational.

• Key Concepts:

  • Base Table: An autonomous, named table.

  • Creating Tables: Use the CREATE TABLE statement to define new tables.

Constraints in SQL

• Primary Key Constraint: Uniquely identifies each record in a table.

  • Syntax: CONSTRAINT pk_stock PRIMARY KEY(stkcode);

• Foreign Key Constraint: Ensures referential integrity between tables.

  • Syntax: CONSTRAINT fk_stock_nation FOREIGN KEY(natcode) REFERENCES nation(natcode);

• Unique Constraint: Ensures all values in a column are unique.

  • Syntax: CONSTRAINT unq_stock_stkname UNIQUE(stkname);

Check Constraints

• Table Check Constraint: Ensures that conditions are met when adding new rows.

  • Syntax: CREATE TABLE item ( itemcode INTEGER, CONSTRAINT chk_item_itemcode CHECK(itemcode <500));

• Column Check Constraint: Applies constraints to individual columns for data validation.

  • Example: CREATE TABLE item ( itemcode INTEGER, CONSTRAINT chk_item_itemcode CHECK(itemcode <500), itemcolor VARCHAR(10));

Data Types in SQL

• Various data types include:

  • Numeric Types: SMALLINT, INTEGER, DECIMAL, FLOAT.

  • Character Types: CHAR(n), VARCHAR(n).

  • Date and Time Types: DATE, TIME, TIMESTAMP.

  • Large Object Types: BLOB, CLOB.

  • Note: Each data type has specific storage requirements and characteristics.

Formatting Data in SQL

• Number Formatting:

  • Use FORMAT(x,d) to format numbers with specified decimal places.

  • Example: SELECT FORMAT(amount, 2) FROM Payments;

• Date Formatting:

  • Use DATE_FORMAT(date, format) for flexible date reporting.

  • Example: SELECT DATE_FORMAT(orderDate, '%Y-%m-%d') FROM Orders;

Collation Sequence

• Determines how characters are sorted based on language.

  • English: A to Z

  • Special characters in other languages like Norwegian: Æ, Ø, Å

• Specify collation at the database, table, and column levels:

  • Example: CREATE DATABASE ClassicModels COLLATE latin1_general_cs;

Modifying Tables

• ALTER TABLE: Used to add new columns, but cannot change storage formats or delete columns.

• DROP TABLE: Completely removes a table from the database.

Views in SQL

• Creating Views: CREATE VIEW allows for creating a virtual table.

• Dropping Views: Use DROP VIEW to remove a defined view.

Indexes in SQL

• Creating Indexes: CREATE INDEX improves query performance.

• Dropping Indexes: Use DROP INDEX to delete an existing index.

Data Manipulation Statements

• Fundamental operations: INSERT, UPDATE, DELETE, SELECT are used to manage data.

Inserting Data

• Single and Multiple Row Inserts: Syntax for inserting data into tables, including using subqueries.

  • Example: INSERT INTO STOCK (...) SELECT ... FROM download WHERE ...;

Updating Data

• Update operations can target one row, multiple rows, or all rows.

Deleting Data

• Similar to UPDATE, allows for deleting rows based on conditions.

  • Important distinction from DROP TABLE.

Producing a Cartesian Product

• Combining all rows from one table with all possible rows of another.

• Example: SELECT * FROM stock, nation;

Correlated Subqueries

• Inner query evaluated multiple times for each row in the outer query.

• Example: Comparing stock quantities to the average.

Aggregate Functions

• Functions like COUNT, SUM, AVG, MAX, MIN are used to summarize data.

SQL Routines

• Functions, Procedures, and Triggers: Enhance SQL capabilities for business rules and productivity.

SQL Functions

• Created with CREATE FUNCTION, similar to built-in operations.

  • Example: Converting kilometers to miles.

SQL Procedures

• Stored codes that execute SQL commands, created with CREATE PROCEDURE.

• Example procedure for transferring funds between accounts.

Triggers in SQL

• Actions triggered by SQL statements (INSERT, UPDATE, DELETE).

• Automatically log changes or enforce rules on data modification.

Understanding Nulls

• Represents unknown or inapplicable data, leading to potential confusion.

• Recommended to use NOT NULL constraints to prevent ambiguity.

SQL Security

• Protecting data involves using privileges, enforcing access controls.

• Key security commands include GRANT and REVOKE.

User Privileges

• GRANT: Assigns specific permissions to users for operations on database objects.

• REVOKE: Removes permissions previously granted, affecting all cascading users.

SQL Injection Attacks

• Exploits weaknesses to manipulate SQL commands using unauthorized inputs.

• Prevention includes using parameterized queries and stringent input validation.

SQL Catalog

• A relational database containing metadata definitions.

• Interrogated using SQL to extract information about tables, columns, and types.

Future of SQL

• SQL remains highly standard, portable, and crucial in transaction processing systems.

Key Points Summary

• SQL routines (functions, procedures, triggers) improve flexibility.

• Emphasis on security and privilege management.

• The role of embedded SQL in application development.