4.3 Using Quick SQL

• About Quick SQL
  Quick SQL is designed to reduce the time and effort required to create SQL tables, triggers, and index structures.
• About Quick SQL Shorthand Syntax
  Learn about the rules to use Quick SQL.
• Accessing Quick SQL
  Access the Quick SQL page by clicking Utilities and then Quick SQL.

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

   The Quick SQL page appears.

4. In the Quick SQL Shorthand pane, enter SQL shorthand syntax.
   
   Description of the illustration GUID-9F3842BE-F08C-4804-A7F0-92B3D795C01E-default.png
5. Click Generate SQL.

   Displays the generated SQL in Oracle SQL Output pane.

   
   Description of the illustration GUID-470FFC12-CD29-4FEE-B1C9-AAA7449032A9-default.png

   Note:

   You can save the generated script or review and run the script.

6. Click Save SQL Script.

   The Save Script dialog appears.

7. 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"

1. On the Workspace home page, click the SQL Workshop icon.
2. Click SQL Scripts.

   The SQL Script page appears.

3. Locate the script and click Run.
   
   Description of the illustration GUID-F40E9B36-DE69-4D1C-94E6-C0CA4BCE2835-default.png

   The Run Script page appears.

4. Click Run Now.

   The Results page appears.

5. 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.

6. 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"

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;
  

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.

Quick SQL supports the following data types:

• VARCHAR2

• NUMBER

• INTEGER

• DATE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

• CLOB

• BLOB