Database Terminology Used in Quick SQL

5.4.10 Database Terminology Used in Quick SQL

Learn about common database terms used in Quick SQL.

Quick SQL includes references to the following database terms:

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