Using Quick SQL

4.3 Using Quick SQL

Rapidly design and prototype data models using a markdown-like shorthand syntax that expands to standards-based Oracle SQL.

Getting Started with Quick SQL
Learn about common Quick SQL use cases, the basics of Quick SQL shorthand syntax, and how to access the Quick SQL page.
Using Quick SQL Help
Review Quick SQL Help to learn about Quick SQL shorthand syntax.
Generating Quick SQL with SQL Shorthand Syntax
Quickly develop a script for simple tables and views by entering SQL shorthand syntax in the the left pane and clicking Generate SQL.
Saving Scripts
Learn about saving or downloading generated script.
Creating an Application from a Generated SQL Script
Create an application from a saved script.
Using Quick SQL Samples
Learn about about SQL shorthand syntax by viewing Quick SQL samples.
Configuring Settings
Configure settings inline to explicitly set SQL syntax generation options, or click Settings at the top of the right pane to declaratively set the generation options.
About Database Terminology
Learn about common database terminology used in Quick SQL.
Recommendations When Using Quick SQL
Learn about recommended approaches to creating effective data models.
About Supported Data Types
Learn about supported data types.

Parent topic: Using Oracle Application Express Utilities

4.3.1 Getting Started with Quick SQL

Learn about common Quick SQL use cases, the basics of Quick SQL shorthand syntax, and how to access the Quick SQL page.

Parent topic: Using Quick SQL

4.3.1.1 About Quick SQL

Quick SQL reduces the time and effort required to create SQL tables, triggers, and index structures. It is not designed to be a replacement for data modeling. Quick SQL is simply a quick way to develop a script for simple tables and views. Once the SQL is generated it can be tweaked and expanded upon. Quick SQL provides a quick way to generate the SQL required to create a relational data model from an indented text document.

Use Cases:

Quickly create robust data models
Easily generate random data
Learn SQL create table, select, insert, index, trigger, PL/SQL package, and view syntax using provided examples

Parent topic: Getting Started with Quick SQL

4.3.1.2 About Quick SQL Shorthand Syntax

When using using Quick SQL shorthand syntax, follow these rules:

Parent table names - Enter parent table names without any indention.
Table column names - Enter table columns with a uniform indention of two or more spaces.
Parent/child relationships - Create parent child relationships by indenting child tables under parent tables. Indent the child tables to the same level as the columns in the parent table.

Note:

As a best practice, do not specify primary keys, the system will generate them for you.

Tip:

To learn more about Quick SQL shorthand syntax, click Help at the top of the Quick SQL Shorthand pane.

About Settings

Click the Settings button to view many generation options. You can automatically add Who Columns which indicate who created or last updated a given row and when. You can add a row sequence column that automatically increments by one on each row update. A row sequence can be useful to simplify lost update detection. You can also generate a PL/SQL API which provides a package per table and provides procedures to query, insert, update, and delete rows. Some applications benefit by having each change captured in a history table, logging of old and new values, which is also an option.

4.3.1.3 Accessing Quick SQL

To access the Quick SQL page:

On the Workspace home page, click the SQL Workshop icon.
Click Utilities.
Click Quick SQL.

The Quick SQL page appears.

Note:

You can also access Quick SQL on the SQL Scripts page by clickig the Quick SQL button. See "Using SQL Scripts."

Parent topic: Getting Started with Quick SQL

4.3.2 Using Quick SQL Help

Review Quick SQL Help to learn about Quick SQL shorthand syntax.

Accessing Quick SQL Help

Parent topic: Using Quick SQL

4.3.2.1 Accessing Quick SQL Help

To access the Quick SQL Help:

Navigate to the Quick SQL page:
1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Quick SQL.
Click Help at the top of the Quick SQL Shorthand pane.

Description of the illustration quick_sql_help.png

The Quick sQL A Help window appears.
Click one of the following tabs to learn more about Quick SQL shorthand syntax:
- About - Overview of Quick SQL together with tips on getting started and steps to create the database objects.
- Data Types - List of data types that can applied to each column.
- Table Directives - Commands used to modify tables.
- Column Directives - Commands used to extend column definitions.
- Views - Syntax for defining a view.
- Settings - Inline settings to explicitly set SQL syntax generation options.
- Samples - Samples of data models that can be used to populate the Quick SQL Shorthand.

Parent topic: Using Quick SQL Help

4.3.3 Generating Quick SQL with SQL Shorthand Syntax

Quickly develop a script for simple tables and views by entering SQL shorthand syntax in the the left pane and clicking Generate SQL.

Tip:

Viewing samples is a great way to learn about Quick SQL shorthand syntax. To view samples, click Help and then Samples. Select a sample by clicking Load Model.

To generate the Quick SQL with SQL shorthand syntax:

Navigate to the Quick SQL page:
1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Quick SQL.
In the Quick SQL Shorthand pane (the left pane), enter SQL shorthand syntax.

Description of the illustration shorthand_syntax.png
Click Generate SQL.

The generated SQL appears in Oracle SQL Output pane.

Description of the illustration quick_sql_with_sql_shorthand.png

Note:

You can save the generated script or review and run the script.
Click Save SQL Script.

The Save Script dialog appears.
In the Save Script dialog:
1. In Script Name, enter a name.
2. Click Save Script.
  The script is saved under SQL Scripts.
Note:

To create an application from script, go to SQL Scripts page and run the saved script. You can also review and run the script by clicking Review and Run button.

See Also:

"Using SQL Scripts"

Parent topic: Using Quick SQL

4.3.4 Saving Scripts

Learn about saving or downloading generated script.

Parent topic: Using Quick SQL

4.3.4.1 Saving a SQL Script

Learn how to save a generated script.

To save a SQL script:

On the Quick SQL page, enter Quick SQL shorthand syntax in the left pane and click Generate SQL.
The generated SQL appears on the Oracle SQL output pane.
Click Save SQL Script.
In the Save Script dialog:
1. In Script Name - Enter a name.
2. Click Save Script.
  
  The saved script is saved under SQL Scripts.
Note:

To create an application from script, go to SQL Scripts page and run the saved script. You can also review and run the script by clicking Review and Run button.

See Also:
"About SQL Scripts"

Parent topic: Saving Scripts

4.3.4.2 Downloading SQL Scripts

To download Quick SQL shorthand and the generated SQL script locally:

On the Quick SQL page, enter Quick SQL shorthand syntax in the left pane and click Generate SQL.
The generated SQL appears on the Oracle SQL Output pane.
Scroll through generated SQL in the SQL Output pane.
Description of the illustration quick_sql_home.png

At the bottom, the line starting with -- Generated by Quick SQL is the SQL shorthand syntax. If you specified any settings they display after line starting with # settings =. The following is the Quick SQL syntax and Settings in the previous example:
```
-- Generated by Quick SQL Wednesday May 01, 2019  10:04:45
 
/*
departments /insert 4
   name /nn
   location
   country
   employees /insert 14
      name /nn vc50
      email /lower
      cost center num
      date hired
      job vc255

view emp_v departments employees

# settings = { language: "EN", APEX: true }
*/
```
Click Download to save both the Quick SQL shorthand and the generated SQL locally.

Parent topic: Saving Scripts

4.3.5 Creating an Application from a Generated SQL Script

Create an application from a saved script.

Saving a script, saves it to SQL Scripts. After you run the saved script, you You can run the Create Application Wizard from SQL Scripts page.

To create an application from saved script:

On the Workspace home page, click the SQL Workshop icon.
Click SQL Scripts.
The SQL Script page appears.
Run the saved script:
1. Locate the script and click Run.
  
  Description of the illustration run_script.png
2. On Run Script, click Run Now.
  
  The Results page appears.
To create an application, click Create App from Script.

The Create Application Wizard appears.

Note:

Creating a database application is a multiple step process. Once you specify the application name and appearance, you add pages, select features, and configure application settings.
In the Create Application Wizard:
1. Enter the application name, select an appearance, add pages, select features, and configure application settings.
  
  Tip:
  
  To learn more, see online Help.
2. Click Create Application.
See Also:

"Understanding Page Types, Features, and Settings"

Parent topic: Using Quick SQL

4.3.6 Using Quick SQL Samples

Learn about about SQL shorthand syntax by viewing Quick SQL samples.

Parent topic: Using Quick SQL

4.3.6.1 About Quick SQL Samples

Quick SQL Help includes the four samples. By selecting a sample and clicking Load Model, you can view both the SQL shorthand syntax and the generated SQL.

Available Quick SQL samples include:

Departments and Employees - Shorthand SQL syntax creates a DEPARTMENTS and EMPLOYEES table with a SQL join VIEW. Random data is also generated using/INSERT syntax. The /NN adds a NOT NULL SQL column constraint. Indented lists are used to define tables, columns and child tables. Primary and foreign key columns are automatically added. You do not need to supply any primary or foreign key columns.
Employee Skills - Creates a DEPARTMENTS, EMPLOYEES, and SKILLS table using indentation to identify parent and child tables. Shorthand syntax VC255 is used to specify a SQL datatype. The /INSERT syntax is used to generate random data.
Product Sales - Short hand syntax that creates a star schema with PRODUCTS, CUSTOMERS, CHANNELS, PROMOTIONS, and SALES tables. The SALES table contains foreign key references to the other tables. A SQL VIEW is also created which joins all five tables. Random sample data is also generated. Numeric column are identified with NUM syntax.
Project Management - Creates a PROJECT_MANAGEMENT and four detail tables. Two views are created using /VIEW syntax. Database CHECK constraints are also added using /CHECK syntax. Also uses #APEX to set settings that control SQL syntax generation.

Parent topic: Using Quick SQL Samples

4.3.6.2 Accessing Quick SQL Samples

To access the Quick SQL samples:

Navigate to the Quick SQL page:
1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Quick SQL.
Click Samples on the Quick SQL Shorthand pane.

The Samples page appears.
Choose a sample and click Load Model.

The Sample loads on Quick SQL Shorthand pane and generated SQL is displayed in Oracle SQL Output pane .

Parent topic: Using Quick SQL Samples

4.3.7 Configuring Settings

Configure settings inline to explicitly set SQL syntax generation options, or click Settings at the top of the right pane to declaratively set the generation options.

Parent topic: Using Quick SQL

4.3.7.1 Configuring Settings Inline

You can configure settings inline to explicitly set SQL syntax generation options. When configuring setting inline:

Settings must start on a new line. To enter multiple settings, begin with # settings =. To enter a single setting per line, use #.
All values are case insensitive.
Brackets, spaces, and commas can be added for clarity but are ignored.
To have all settings generated use # verbose: true

Entering settings directly into the Quick SQL Shorthand pane ensures the same SQL generation options are utilized even if you download the script and later paste it back. For example, enter the following to prefix all table names with TEST and generate for schema OBE:

# settings = { prefix: "test", schema: "OBE" }.
Alternatively, enter each setting on a separate line for the same result:
# prefix: "test" 
# schema: "OBE"

Tip:

To view a listing of settings, by clicking Help and then Settings.

Parent topic: Configuring Settings

4.3.7.2 Configuring Settings Declaratively

To configure settings declaratively:

Navigate to the Quick SQL page:
1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Quick SQL.
Click Settings on the Oracle SQL Output pane.

The Settings page appears. Configure the appropriate attributes.
Under Table:
- Object Prefix - Database object prefix. Prefixes all object names with the provided value .
- Schema - Prefix object names with a schema name. The default is no schema prefix for object names.
- On Delete - Defines how foreign keys behavebehave on delete of the parent row. Options include:
  - Cascade - A foreigh key with "on delete cascade" automatically deletes the child row when the correponding parent is deleted.
  - Restrict - A foreign key with "on delete restrict" prevents deletion of the parent row if a child row exists.
  - Set Null - A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null.
- Compression - Determines if you want your tables to be compressed or not.
  Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. Table compression is completely transparent to applications. It is especially useful in online analytical processing (OLAP) systems, where there are lengthy read-only operations, but can also be used in online transaction processing (OLTP) systems.
- Generate Inserts - Generating inserts can be time consuming and impact usability. Consider turning off the generation of inserts as you refine your data model, then turning on when you are ready to generate data.
Under Column:
- Primary Keys - Defines how table primary keys are to be populated. Options include:
  - Via triggers and GUIDs - Populate the primary key with a numeric random number that is non sequential and non uniform in length. This method is useful in that it can be more secure, it can also allow data to be merged from different databases with out fear of primary key conflict. This primary key population is implemented via a database trigger.
  - Via triggers and Sequence - Increment the primary key using number sequence. This primary key population is implemented via a database trigger.
  - 12c Identity Datatype - Set the primary key by default to an ever increasing number. This does not require any trigger logic to be implemented.
- Date Data Type - Defines what type of date to use to store columns identified as dates. Options include: Date, Timestamp, and Timestamp with local time zone.
- Semantics- Options include:
  - Default - Default to the database setting. If you are unsure what this is, select Default.
  - BYTE - Indicates that the column will have byte length semantics.
  - CHAR - Indicates that the column will have character semantics.
Under Additional Columns:
- Include - Additional columns to automatically add to your table. Options include: Audit Columns, Row Key, Row Version Number, and Security Group ID.
Under Options:
- Data Language - Controls the language used for generating the data using the /INSERT flag.
- PL/SQL API - Select Yes to generate a PL/SQL APIs with one package per table on all tables. Select No to only generate PL/SQL APIs for tables that include an /api at the end of the name.
- Include Drops - Include drop object commands in the output. When set to Yes, a drop command is generated for each database object created. For example:
```
drop table X including contents;
drop table Y including contents;
```
- History Tables - Select Yes to create a history table to log all updates and deletes to all tables. A PL/SQL after update or delete trigger will be created that inserts into this history table. Select No to create a history table only if one or more tables includes a /history option, for example:
```
employees /history
   name
   email
   phone num
```
- Auto Primary Key - Select Yes to have primary key columns automatically generated for tables. The primary key column name will be ID and it will be of type number.
  
  Tip:
  To see an example, see field-level Help.
- Longer Varchars - Available in Oracle Database 12.1 or later. Longer Varchars allow for columns to be up to 32K bytes. Ensure your database is configured to support longer varchars if you wish to use this feature.
- Editionable - When generating PL/SQL objects including triggers and packages make these editionable.
- APEX Enabled - Controls the syntax generated to support audit columns.
- Tags Framework - Choose Yes or No.
- Compatibility - Select an Oracle Database release. Ensures generated SQL is compatible with the database release selected.
Under Preferences:
- Prefix primary keys with table name - Prefix primary key database table columns with name of table.
- Created Column Name - When generating audit columns, controls the name of this audit column.
- Created By Column Name - When generating audit columns, controls the name of this audit column.
- Updated Column Name - When generating audit columns, controls the name of this audit column.
- Updated By Column Name - When generating audit columns, controls the name of this audit column.
To save your configuration, click Save Changes
To reset to the default settings, click Reset

Parent topic: Configuring Settings

4.3.8 About Database Terminology

Learn about common database terminology used in Quick SQL.

Common database terms referenced in Quick SQL include:

BLOB – A binary large object or CLOB. BLOBs store binary data and do not impose any character set encoding. BLOB size can store 4GB or more depending on system configuration.

Check Constraint – A check constraint is a declarative way to limit the values a given column may contain.

create table projects (
	          id     number primary key, 
	          name   varchar2(50), 
	          status varchar2(30) check (status in ('OPEN','CLOSED'))
	          );

CLOB – A character large object or CLOB. The maximum size of a VARCHAR2 datatype is 32,767 bytes, however a CLOB size can be store up to 4GB of character data.
Column – RDBMS tables store data in tables that are comprised of rows and columns. Each column is named. For example, first_name.
Database – A structured set of data held in a computer, especially one that is accessible in various ways.
Data Model – A Data Model is a method of organizing elements of data and defining how they relate to one another. A model typically organizes data into entities that when they are transformed into a physical database model are converted into tables and columns. This tool is a very rudimentary tool that creates physical data models from indented text. For serious data modeling it is recommended that you use a proper data modeling tool Oracle SQL Data Modeler. It is a free graphical tool that enhances productivity and simplifies data modeling tasks.
Data Type – Each column in a table has a data type. Data types are frequently character (VARCHAR2), number, or dates.
DML – Data Manipulation Language. SQL commands that include the following:
- INSERT - SQL statement used to add new rows to a table.
- UPDATE - SQL statement used to update one or more rows of data within a table.
- DELETE - SQL statement used to remove one or more rows of data from a table.
Foreign Key – A column(s) in a table that references another table. For example, if employees are organized by departments, a department_id in the employees table could be a foreign key to the primary key of the employee table. By defining foreign keys you are declaratively defining a relationship between tables.
Join – A join is a SQL query that combines rows from two or more tables or views. A query is a join query whenever multiple tables appear in the FROM clause. A major benefit of the relational database model is that you can store data that repeats in one location. For example, assume we have two table departments, and employees. Here is a query that selects data from both tables.
```
select d.name, d.location, e.name, e.job
	    from   departments d, employees e
	    where  e.department_id = e.id
```
Note if the department name or location changes it is changed in only one place.
Relational Database – A database model that organizes data in a relational model. The relational model organizes data into one or more tables, also known as relations, of columns and rows. The standard language to access data within a relational database and to define database objects is SQL.
RDBMS – Relational Database Management System.
Object – A database object is a named widget used to store or manage data. For example tables, views, triggers, and synonyms are all considered database objects.
PL/SQL – Oracle procedural extension to the SQL language. PL/SQL stands for Procedural Language SQL. PL/SQL is a server-side, stored procedural language that can be used to perform procedural operations on Oracle database data.

Query – To access / query data in your relational database you use a SQL SELECT statement. In SQL a SELECT statement can query data from one or more tables or views.

Query of 3 columns from a table called projects

select id, name, status
    from projects
    where status = 'OPEN';

Aggregate Query

select count(*) count_of_projects,
           sum(case when status = 'OPEN' then  1 else 0) open_projects,
           sum(case when status = 'CLOSED' then  1 else 0) closed_projects
    from projects;

Reserved Word – A name that cannot be used as an object identifier. For example SELECT cannot be used as a table name.
Row – RDBMS tables store data in tables that are comprised of rows and columns. Each row is made up of one or more columns.
Sequence – Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables. A sequence can be created with the following Syntax:
```
create sequence project_seq;
```
The sequence values can be referenced using project_seq.currval (current value) or project_seq.nextval (next value). Each time the nextval is access the sequence increments. The default increment is1.
Schemas – A schema is a namespace for creating database objects such as tables. You do not create database tables in databases you create them within schemas within a database. Each database can store any number of schemas. Schemas are also sometimes called users. When you have a new database you first create a schema (or user, they are the same thing) then create your tables within the schema or user. You can create schemas using the SQL create schema or create user command. Schemas are a way of organizing database objects within a database.
SQL – Structure Query Language. The language used to define database tables, and to manipulate the data within the tables.
Structured Data – Relational database data is frequently referred to as structured because data is organized cleanly into rows and columns. Unstructured typically refers to data that has no structure imposed on it.

Table – A database object that stores related data organized into rows and columns.

Creating a table

create table projects (
        id     number primary key, 
        name   varchar2(50), 
        status varchar2(30)
        );

Inserting rows into a table

insert into projects (id, name, status)
      values (1, 'My Project','OPEN');

Querying the data with the table

select id, name, status
      from projects
      where status = 'OPEN';

Trigger – A database trigger is procedural code that is automatically executed in response to certain events on a particular table. For example before or after insert of a row. The following trigger forces a column to lower case.
```
create or replace trigger PROJECTS_BIU
	        before insert or update 
	        on PROJECTS
	        for each row
	    begin
	        :new.OWENER_EMAIL := LOWER(:new.OWENER_EMAIL);
	    end;
	/
```
View – A database view is a virtual table based on the result-set of an SQL statement. A view is also known as a stored query. You can create a view that joins multiple tables. Once the view is created you can query it as if it was a table. Following is an example of the SQL to create a view:
```
	create view department_employees as
	select d.name, d.location, e.name, e.job
	    from   departments d, employees e
	    where  e.department_id = e.id;
```

Parent topic: Using Quick SQL

4.3.9 Recommendations When Using Quick SQL

Learn about recommended approaches to creating effective data models.

Although there are many valid approaches to creating an effective data model, Oracle recommends the following:

Table Naming – Define your tables as plural and columns as singular. Since tables are designed to store many rows it makes sense to have them plural. For example Employees.
Column Naming – Define your column names as singular. For example First Name.
Primary Keys – Every table should have a primary key, primary keys are used to uniquely identify each row and can be used to associate child table data.
Primary Key Column Naming – Every table will have a primary key and it should be called ID and it should be numeric.
Table Constraints – The best way to ensure data integrity is to have the database only allow proper data. For example, if a column is required ensure you have a not null constraint. If a column is unique ensure you have a unique constraint.
Referential Integrity – Most relational database models have parent child, also known as one too many relationships. They frequently also have many to many relationships. Using declarative primary key and foreign key syntax you can have the database enforce these relationships to ensure the integrity of your data. By defining the referential integrity you ensure all child tables do in fact relate to parent tables.

Parent topic: Using Quick SQL

4.3.10 About Supported Data Types

Learn about supported data types.

Quick SQL supports the following data types:

VARCHAR2
NUMBER
INTEGER
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
CLOB
BLOB

Tip:

To learn more about Quick SQL shorthand syntax for supported data types, click Help and select Data Types.

Parent topic: Using Quick SQL