Rapidly design and prototype data models using a markdown-like shorthand syntax that expands to standards-based Oracle SQL.
Parent topic: Using Oracle Application Express Utilities
Parent topic: Using Quick SQL
Quick SQL is designed to reduce the time and effort required to create SQL tables, triggers, and index structures.
Quick SQL is not designed to be a replacement for data modeling. It 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
Learn about the rules to use Quick SQL.
Follow these rules to use Quick SQL syntax:
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.
About Settings
Click the Settings button to view many generation options. You can automatically add Who Columns, for example, 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.
See Also:
Parent topic: Getting Started with Quick SQL
Access the Quick SQL page by clicking Utilities and then Quick SQL.
To access the Quick SQL page:
Parent topic: Getting Started with Quick SQL
Parent topic: Using Quick SQL
To access the Quick SQL Help page:
Parent topic: Using Quick SQL Help
Quick SQL Help has the following:
About - Overview of Quick SQL together with tips on getting started and steps to create the DB 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
To generate the Quick SQL with SQL Shorthand:
Parent topic: Using Quick SQL
Parent topic: Using Quick SQL
You can create and save a SQL script.
To save a SQL script:
Parent topic: Saving Scripts
Download the script to your local filesystem. If you want to change the SQL shorthand, you can readily paste it back into Quick SQL.
To download SQL Shorthand:
Parent topic: Saving Scripts
When you save a script it saves it to SQL Scripts and you can directly run the Create Application Wizard from SQL Scripts page.
To create an application from script:
Parent topic: Using Quick SQL
Parent topic: Using Quick SQL
Quick SQL includes samples to assist you in learning to use SQL Shorthand syntax.
You have several options when generating Quick SQL. Enables you to use a shorthand format and generate SQL.
Available samples are:
Departments and Employees - Create a DEPARTMENTS and EMPLOYEES table with a SQL join VIEW.
Employee Skills - Create a DEPARTMENTS, EMPLOYEES, and SKILLS table using indentation to identify parent and child tables.
Product Sales - Create a star schema with PRODUCTS, CUSTOMERS, CHANNELS, PROMOTIONS, and SALES tables.
Project Management - Create a PROJECT_MANAGEMENT and four detail tables.
Parent topic: Using Quick SQL Samples
Use SQL Shorthand syntax to generate SQL.
To access the Quick SQL page:
Parent topic: Using Quick SQL Samples
Parent topic: Using Quick SQL
Learn how to set SQL syntax generation options.
You can use inline settings to explicitly set SQL syntax generation options by either entering settings directly into the Quick SQL Shorthand pane, or by clicking the Settings button at the top of the Oracle SQL Output pane.
Parent topic: Configuring Settings
You can configure settings declaratively to explicitly set SQL syntax generation options.
To configure settings declaratively:
Parent topic: Configuring Settings
You can configure settings inline to explicitly set SQL syntax generation options.
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"
Note:
The settings must start on a new line and begin with # settings =
to enter multiple settings, or #
to enter a single setting per line. 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
.
Tip:
You can find the setting inline, by clicking Help, Settings.
Parent topic: Configuring Settings
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
There are many valid approaches to creating an effective data model. Oracle recommends the following method:
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
Quick SQL supports the following data types:
VARCHAR2
NUMBER
INTEGER
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
CLOB
BLOB
Parent topic: Using Quick SQL