Advanced SQL and RDBMS

Introduction to Advanced SQL

• The importance of SQL in database architecture is less obvious to application users due to relational DBMSs and application generators.
• Many database application users lack direct SQL knowledge.
• SQL-based relational database applications consist of a user interface, database tables, and an SQL-capable Relational Database Management System (RDBMS).
• SQL is used within the RDBMS to create tables, translate user requests, maintain the data dictionary and system catalog, update tables, ensure security, and perform backups/recovery.
• A relational DBMS implements a relational data model, stores data in tables, and represents relationships via common values.

SQL Environment and Commands

• The SQL environment aligns with the SQL:200n standard.
• A catalog contains each database; it describes every database object, regardless of the creator.
• Companies maintain multiple database versions; PROD_C represents the production version which captures real business data.
• A schema is a collection of related objects like tables, views, domains, constraints, character sets, triggers, and roles.
• An information schema within each catalog contains descriptions of all schemas, tables, views, attributes, privileges, constraints and domains.
• The DBMS maintains the catalog based on SQL commands; users can browse catalog contents by using SQL SELECT statements.
• Data Definition Language, Data Manipulation Language and Data Control Language are the SQL commands classifications.
• DDL commands create, alter, and drop tables, views, and indexes.
• DML commands update, insert, modify, and query database data.
• DCL commands allow a DBA to control database access by granting or revoking privileges, and managing transactions.

Defining and Generating SQL Databases

• You may not be allowed to create a database due to system storage allocation.
• Database creation privileges are reserved for the database administrator in some systems.
• Command Structure: CREATE SCHEMA database_name; AUTHORIZATION owner_user id
• Several SQL DDL CREATE commands are included in SQL:200n

DDL CREATE Command Functions:

• CREATE SCHEMA defines the portion of a database owned by a particular user.
• Schemas are dependent on a catalog and contain schema objects.
• Schema objects include base tables and views, domains, constraints, assertions, character sets, and collations
• CREATE TABLE defines a new table (base or derived) and its columns.
• Tables are dependent on a schema
• Derived tables are created by executing a query of tables or views.
• CREATE VIEW defines a logical table from one or more tables or views.
• Views cannot be indexed.
• Updated views transfer changes to the referenced base tables.
• DDL CREATE commands can be reversed by using a DROP command.
• The DROP TABLE tablename command destroys a table and its definition, contents, constraints, views, and indexes.
• ALTER TABLE adds, drops, or changes a column in an existing base table.

Creating Tables and Data Integrity Controls

• Once the data model is designed and normalized, the SQL CREATE TABLE command defines the columns for each table.
• Syntax: CREATE TABLE Customer_T (CustomerlD NUMBER(11,0) NOT NULL, CustomerName VARCHAR2(25) NOT NULL, CustomerAddress VARCHAR 2(30), CustomerCity VARCHAR 2(20), Customer State CHAR (2), CustomerPostal Code VARCHAR 2(9), CONSTRAINT Customer_PK PRIMARY KEY (CustomerID));"
• Syntax establishes foreign keys to establish referential integrity between two tables with a 1:M relationship.
• Referential integrity means that a value in the matching column on the many side must correspond to a value in the primary key, or be NULL.
• The SQL REFERENCES clause prevents a foreign key value from being added if it's not a valid value in the referenced primary key.
• ON UPDATE CASCADE passes changes through to child tables.
• ON UPDATE SET NULL changes the CustomerID value in the Order_T table to NULL if the Customer_T table is updated.
• ON DELETE RESTRICT prevents customer record deletion if there are orders from that customer.
• With DELETE CASCADE, removing a customer removes all associated order records.
• ON DELETE SET NULL sets the order records for that customer to null.
• ON DELETE SET DEFAULT sets the order records for that customer to a default value.
• Update and delete permissions may be revoked if the SQL RDBMS does not provide for primary key referential integrity.

Views

• A view is a virtual table based on a SELECT query.
• The query may extract columns, computed columns, aliases, and aggregate functions from one or more base tables.
• The CREATE VIEW command creates a view.
• Syntax: CREATE VIEW viewname AS SELECT query
• The CREATE VIEW statement is a data definition command that stores the subquery specification in the data dictionary.
• Views can be used wherever a table name is expected in a SQL statement.
• They are dynamically updated (re-created on demand).
• Views supply a certain level of security in the database by restricting the users to specified columns and rows.
• Views may be used as the basis for reports.

Joining and Combining Database Tables

• Joining tables on common attributes is a key aspect of relational databases.
• A join is performed to retrieve data from more than one table at a time.

SQL JOIN Types:

• (INNER) JOIN: Returns records with matching values in both tables.
• LEFT (OUTER) JOIN: Returns all records from the left table and matched records from the right table.
• RIGHT (OUTER) JOIN: Returns all records from the right table and the matched records from the left table.
• FULL (OUTER) JOIN: Returns all records when there is a match in either the left or right table.
• The inner join keyword selects records with matching values in both tables.
• The left join keyword returns all records from the left table and the matched records from the right table; if there is no match, the result from the right side is NULL.
• The right join keyword returns all records from the right table and the matched records from the left table; if there is no match, the result from the left side is NULL.
• The full outer join keyword returns all records when there is a match in either the left or right table.
• a self JOIN is where the table is joined with itself.
• The UNION operator combines the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns with similar data types in the same order.
• UNION only selects distinct values by default; use UNION ALL to allow duplicate values.

Subqueries

• Joins are used to get information from two or more tables.
• Example: SELECT INV_NUMBER, INVOICE.CUS_CODE, CUS_LNAME, CUS_FNAME FROM CUSTOMER, INVOICE WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE;
• Subqueries can process data based on other data.
• Example: To generate a list of vendors who provide products, use SELECT V_CODE, V_NAME FROM VENDOR WHERE V_CODE NOT IN (SELECT V_CODE FROM PRODUCT);
• A subquery is a query (SELECT statement) inside a query, normally expressed inside parentheses
• The first query in the SQL statement is the outer query; the query inside is the inner query.
• The inner query executes first, is used as input for the outer query.
• The entire SQL statement is sometimes called a nested query.
• Subqueries can be noncorrelated (executed once for the entire outer query) or correlated (executed once for each row returned by the outer query).
• Noncorrelated Subqueries do not depend on data from the outer query and are executed once for the entire outer query.
• Correlated Subqueries makes use of data from the outer query, executes once for each row of the outer query, and can use the EXISTS operator.

Stored Procedures

• A stored procedure is a segment of declarative SQL code stored in the database catalog.
• It is invoked by programs, triggers, or other stored procedures.
• Stored procedures calling themselves are recursive.
• A stored procedure or function is stored within the database and compiled when used.
• Stored procedures can receive input parameters and can return results.
• Stored procedures can be called from standard and scripting languages or the SQL command prompt.
• Stored procedures increase application performance, reduce traffic between application and database server, can be resued, and are secure.
• Potential downsides includes high database server load in both memory and processors, only supporting declarative SQL, being difficult to debug, and possibly complex to write and maintain
• Basic Syntax: DELIMITER // CREATE PROCEDURE name_of_proc() BEGIN <SQL Command> DELIMITER ;
• Stored procedures contains variables to store immediate results.
• Syntax: DECLARE variable_name datatype(size) DEFAULT default_value;
• Variables can be declared together if they have the same data type.
• The SET statement can assign other values to a variable.
• Besides SET, the SELECT INTO statement can assign a query result to a variable.
• Syntax: DECLARE total_products INT DEFAULT 0; SELECT COUNT(*) INTO total_products FROM products ;

Stored Procedures Parameters and Scope

• A variable declared inside a stored procedure is out of scope when its END is reached.
• A variable inside a block BEGIN/END is out of scope when its END is reached.
• The variable is only effective in its scope if you can declare two variables with the same name in different scopes
• A variable with the ‘@' prefix is a session variable that exists until the session ends.
• Stored procedures that require parameters are more flexible and useful.

MySQL Parameters:

• IN is the default mode, it receives parameters as input like an attribute.
• The calling program must pass an argument to the stored procedure
• This parameter's value is always protected.
• OUT is used to pass a parameter as output and its value can be changed inside the stored procedure.
• The new value is passed back to the calling program.
• A procedure cannot access the OUT parameter's initial value when it starts.
• INOUT combines IN and OUT parameters.
• The calling program passes the argument; the procedure can modify it and pass the new value back.

Dropping, Functions and the Function Syntax

• DROP PROCEDURE <name of proc> deletes a procedure.
• A function can be called from inside a statement, and returns a scalar value.
• A stored function is special as it returns a single value.
• Stored functions encapsulate common formulas or business rules that are reusable among SQL statements or stored programs.
• Stored functions can be used in SQL statements wherever an expression is valid.
• Syntax: DELIMITER $$ CREATE FUNCTION function_name( param1, param2,...) RETURNS datatype [NOT] DETERMINISTIC
• First, specify the name with the CREATE FUNCTION keywords.
• List the parameters inside parentheses after the function name (all parameters are IN by default).
• The RETURNS statement specifies the data type of the return value.
• Determine if the function is deterministic, use the DETERMINISTIC keyword.
• The body of the function goes in the BEGIN END block, inside which needs a RETURN statement.
• Deterministic functions always return the same result for the same inputs, deterministic functions may return different results.

Calling a Stored Function and Deterministic Functions

• You pass parameters by listing them inside the parenthesis with a select statement
• Deterministic functions return the same result any time they are called with a specific set of input values
• ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COALESCE, COS, COT, DATALENGTH, DATEADD, DATEDIFF, DAY, DEGREES, EXP, FLOOR, ISNULL, ISNUMERIC, LOG, LOG10, MONTH, NULLIF, POWER, RADIANS, ROUND, SIGN, SIN, SQUARE, SQRT, TAN, YEAR are deterministic functions
• Nondeterministic functions return different results each time they are called even if their inputs are the same values.
• @@CONNECTIONS, @@CPU_BUSY, @@DBTS, @@IDLE, @@IO_BUSY, @@MAX_CONNECTIONS, @@PACK_RECEIVED, @@PACK_SENT, @@PACKET_ERRORS, @@TIMETICKS, @@TOTAL_ERRORS, @@TOTAL_READ, @@TOTAL_WRITE, CUME_DIST, CURRENT_TIMESTAMP, DENSE_RANK, FIRST_VALUE, GETDATE, GETUTCDATE, GET_TRANSMISSION_STATUS, LAG, LAST_VALUE, LEAD, MIN_ACTIVE_ROWVERSION, NEWID, NEWSEQUENTIALID, NEXT VALUE FOR, NTILE, PARSENAME, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, RAND, RANK, ROW_NUMBER, TEXTPTR are examples of Nondeterministic Functions

SQL Triggers

• SQL statements or a set of SQL statements activate when an event occurs with a database table are SQL Triggers.
• Events include INSERT, UPDATE, and DELETE.
• Triggers are special stored procedures with procedural code.
• Triggers can check integrity, catch business logic errors at the database level.
• Triggers run scheduled tasks allowing tasks before or after database changes.
• It is useful to audit data changes.
• Extended validation is only provided and cannot replace all validations by triggers.
• SQL Triggers execute invisibly from client-application which connects to the database server so it is difficult to figure out what happen underlying database layer.
• SQL Triggers run every updates made to the table therefore it adds workload to the database and cause system runs slower

Types of Triggers:

• Before Update Trigger is invoked before an update is executed.
• After Update Trigger is invoked after an update occurs.
• Before Insert Trigger is invoked before a new insert is executed.
• After Insert Trigger gets invoked after an insert is implemented.
• Before Delete Trigger is invoked before a delete statement is implemented.
• After Delete Trigger is invoked after a delete operation is implemented.

Conditional Statements in MySQL

• The IF statement has three forms: IF - THEN, IF-THEN-ELSE, IF-THEN-ELSEIF-ELSE
• The IF-THEN statement executes SQL statements based on a condition.
• The IF-THEN-ELSE statement executes other statements when the IF branch's condition is FALSE.
• The IF-THEN-ELSEIF-ELSE statement executes statements conditionally based on multiple conditions.

The CASE Statement

• An alternative conditional statement to IF, the CASE statement helps for in stored procedures.
• CASE statements result in code that is efficient and readable.
• CASE statements have two forms which are Simple CASE and searched CASE statements.

Looping in MySQL

• The LOOP statement executes one or more statements repeatedly.
• The WHILE loop executes a block of code repeatedly as long as a condition is true.
• The REPEAT statement executes one or more statements until a search condition is true.
• The LEAVE statement exits a labelled flow control.