Category Archives: Database Query – SQL

Learn Data Modeling Part 2

Three basic Styles of data model:

Conceptual data models.  These models, sometimes called domain models, are typically used to explore domain concepts with project stakeholders.  On Agile teams high-level conceptual models are often created as part of your initial requirements envisioning efforts as they are used to explore the high-level static business structures and concepts.  On traditional teams conceptual data models are often created as the precursor to LDMs or as alternatives to LDMs.

Logical data models (LDMs).  LDMs are used to explore the domain concepts, and their relationships, of your problem domain.  This could be done for the scope of a single project or for your entire enterprise.  LDMs depict the logical entity types, typically referred to simply as entity types, the data attributes describing those entities, and the relationships between the entities. LDMs are rarely used on Agile projects although often are on traditional projects (where they rarely seem to add much value in practice).

Physical data models (PDMs).  PDMs are used to design the internal schema of a database, depicting the data tables, the data columns of those tables, and the relationships between the tables. PDMs often prove to be useful on both Agile and traditional projects and as a result the focus of this article is on physical modeling.

Although LDMs and PDMs sound very similar, and they in fact are, the level of detail that they model can be significantly different.  This is because the goals for each diagram is different – you can use an LDM to explore domain concepts with your stakeholders and the PDM to define your database design.

Figure 1. A simple logical data model.

Figure 2. A simple physical data model.

For example, figure above examine the relationship between Customer and Address there really should be two names “Each CUSTOMER may be located in one or more ADDRESSES” and “Each ADDRESS may be the site of one or more CUSTOMERS”.

What About Conceptual Models?

Halpin (2001) points out that many data professionals prefer to create an Object-Role Model (ORM), an example is depicted in Figure 3, instead of an LDM for a conceptual model.  The advantage is that the notation is very simple, something your project stakeholders can quickly grasp, although the disadvantage is that the models become large very quickly.  ORMs enable you to first explore actual data examples instead of simply jumping to a potentially incorrect abstraction – for example Figure 3 examines the relationship between customers and addresses in detail.

Figure 3. A simple Object-Role Model.

Common Data Modeling Notations

Figure 4 below presents a summary of the syntax of four common data modeling notations: Information Engineering (IE), Barker, IDEF1X, and the Unified Modeling Language (UML).

source: datamodeling

Advertisements

Learn Data Modeling

Data modeling is the formalization and documentation of existing processes and events that occur during application software design and development. Data modeling techniques and tools capture and translate complex system designs into easily understood representations of the data flows and processes, creating a blueprint for construction and/or re-engineering. Data modeling in software engineering is the process of creating a data model for an information system by applying formal data modeling techniques.

A data model can be thought of as a diagram or flowchart that illustrates the relationships between data. Well-documented models allow stake-holders to identify errors and make changes before any programming code has been written.

Data modelers often use multiple models to view the same data and ensure that all processes, entities, relationships and data flows have been identified. There are several different approaches to data modeling, including:

  • Conceptual Data Modeling – identifies the highest-level relationships between different entities.
  • Enterprise Data Modeling – similar to conceptual data modeling, but addresses the unique requirements of a specific business.
  • Logical Data Modeling – illustrates the specific entities, attributes and relationships involved in a business function. Serves as the basis for the creation of the physical data model.
  • Physical Data Modeling – represents an application and database-specific implementation of a logical data model.

Data modeling is a process used to define and analyze data requirements needed to support the business processes within the scope of corresponding information systems in organizations. Therefore, the process of data modeling involves professional data modelers working closely with business stakeholders, as well as potential users of the information system. There are three different types of data models produced while progressing from requirements to the actual database to be used for the information system. The data requirements are initially recorded as a conceptual data model which is essentially a set of technology independent specifications about the data and is used to discuss initial requirements with the business stakeholders. The conceptual model is then translated into a logical data model, which documents structures of the data that can be implemented in databases. Implementation of one conceptual data model may require multiple logical data models. The last step in data modeling is transforming the logical data model to a physical data model that organizes the data into tables, and accounts for access, performance and storage details.

How data models deliver benefit

The Benefits of Data Modeling

Abstraction

  • The act of abstraction expresses a concept in its minimum, most universal set of properties. A well abstracted data model will be economical and flexible to maintain and enhance since it will utilize few symbols to represent a large body of design. If we can make a general design statement which is true for a broad class of situations, then we do not need to recode that point for each instance. We save repetitive labor; minimize multiple opportunities for human error; and enable broad scale, uniform change of behavior by making central changes to the abstract definition.
  • In data modeling, strong methodologies and tools provide several powerful techniques which support abstraction. For example, a symbolic relationship between entities need not specify details of foreign keys since they are merely a function of their relationship. Entity sub-types enable the model to reflect real world hierarchies with minimum notation. Automatic resolution of many-to-many relationships into the appropriate tables allows the modeler to focus on business meaning and solutions rather than technical implementation.

Transparency

  • Transparency is the property of being intuitively clear and understandable from any point of view. A good data model enables its designer to perceive truthfulness of design by presenting an understandable picture of inherently complex ideas. The data model can reveal inaccurate grouping of information (normalization of data items), incorrect relationships between objects (entities), and contrived attempts to force data into preconceived processing arrangements.
  • It is not sufficient for a data model to exists merely as a single global diagram with all content smashed into little boxes. To provide transparency a data model needs to enable examination in several dimensions and views: diagrams by functional area and by related data structures; lists of data structures by type and groupings; context-bound explosions of details within abstract symbols; data based queries into the data describing the model.

Effectiveness

  • An effective data model does the right job – the one for which it was commissioned – and does the job right – accurately, reliably, and economically. It is tuned to enable acceptable performance at an affordable operating cost.
  • To generate an effective data model the tools and techniques must not only capture a sound conceptual design but also translate into a workable physical database schema. At that level a number of implementation issues (e.g., reducing insert and update times; minimizing joins on retrieval without limiting access; simplifying access with views; enforcing referential integrity) which are implicit or ignored at the conceptual level must be addressed.
  • An effective data model is durable; that is it ensures that a system built on its foundation will meet unanticipated processing requirements for years to come. A durable data model is sufficiently complete that the system does not need constant reconstruction to accommodate new business requirements and processes.
  • Furthermore, as additional data structures are defined over time, an effective data model is easily maintained and adapted because it reflects permanent truths about the underlying subjects rather than temporary techniques for dealing with those subjects.

Data Modeling Process

The process of designing a database involves producing the previously described three types of schemas – conceptual, logical, and physical. The database design documented in these schemas are converted through a Data Definition Language, which can then be used to generate a database. A fully attributed data model contains detailed attributes (descriptions) for every entity within it. The term “database design” can describe many different parts of the design of an overall database system. Principally, and most correctly, it can be thought of as the logical design of the base data structures used to store the data. In the relational model these are the tables and views. In an object database the entities and relationships map directly to object classes and named relationships. However, the term “database design” could also be used to apply to the overall process of designing, not just the base data structures, but also the forms and queries used as part of the overall database application within the Database Management System or DBMS.

In the process, system interfaces account for 25% to 70% of the development and support costs of current systems. The primary reason for this cost is that these systems do not share a common data model. If data models are developed on a system by system basis, then not only is the same analysis repeated in overlapping areas, but further analysis must be performed to create the interfaces between them. Most systems within an organization contain the same basic data, redeveloped for a specific purpose. Therefore, an efficiently designed basic data model can minimize rework with minimal modifications for the purposes of different systems within the organization.

Data modeling in the context of Business Process Integration

Types of Data Models

A database model is a theory or specification describing how a database is structured and used. Several such models have been suggested. Common models include:

  • Flat model: This may not strictly qualify as a data model. The flat (or table) model consists of a single, two-dimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another.
  • Hierarchical model: In this model data is organized into a tree-like structure, implying a single upward link in each record to describe the nesting, and a sort field to keep the records in a particular order in each same-level list.
  • Network model: This model organizes data using two fundamental constructs, called records and sets. Records contain fields, and sets define one-to-many relationships between records: one owner, many members.
  • Relational model: is a database model based on first-order predicate logic. Its core idea is to describe a database as a collection of predicates over a finite set of predicate variables, describing constraints on the possible values and combinations of values.
  • Object-relational model: Similar to a relational database model, but objects, classes and inheritance are directly supported in database schemas and in the query language.
  • Star schema is the simplest style of data warehouse schema. The star schema consists of a few “fact tables” (possibly only one, justifying the name) referencing any number of “dimension tables”. The star schema is considered an important special case of the snowflake schema.

source: wikipedia

Comparison of data modeling tools

In general:

Tool Creator Target Business Size License Supported Database Platforms Supported OSs Standalone or bundled into a larger toolkit Launch Date
CA ERwin Data Modeler CA Technologies SMBs and enterprises Commercial (Free, limited, community edition available) Access, IBM DB2, Informix, Ingres, MySQL, Oracle, Progress, MS SQL Server, Sybase, Teradata All versions of Windows Standalone 1992?
Database Visual Architect Visual Paradigm SMBs and enterprises Commercial IBM DB2, Firebird, Progress OpenEdge, Cache, MS SQL Server, MySQL, HSQLDB, SQLite, Oracle, PostgreSQL, Sybase Windows, Linux, MAC OS Standalone (or as part of an UML modeling environment) Unknown
dbConstructor DBDeveloper Solutions SMBs Commercial Oracle, MS SQL Server, MySQL, Access All versions of Windows Standalone Unknown
DbSchema Wise Coders Solutions SMBs Commercial MySQL, PostgreSQL, MS SQL Server, Derby, Firebird, Oracle, IBM DB2, Informix, HSql, Pointbase, Mimer, Pervasive, Sybase All versions of Windows Standalone 2005
DbWrench Database Design Nizana Systems SMBs Commercial MS SQL Server, Oracle, PostgreSQL, MySQL Windows, Linux, Mac Standalone 2005
DeZign for Databases Datanamic SMBs and enterprises Commercial MS SQL Server, MySQL, Oracle, IBM DB2, Firebird, InterBase, Informix, MS Access, SQLite, PostgreSQL, Sybase, Paradox All versions of Windows Standalone 1999
Enterprise Architect Sparx Systems SMBs and enterprises Commercial IBM DB2, Firebird, InterBase, Informix, Ingres, Access, MS SQL Server, MySQL, SQLite, Oracle, PostgreSQL, Sybase Windows, Linux, Mac Data modeling is supported as part of a complete modeling platform. 2000
ER/Studio Embarcadero SMBs and enterprises Commercial Access, IBM DB2, Informix, MySQL, Oracle, PostgreSQL, MS SQL Server All versions of Windows Standalone 1998
IBM InfoSphere Data Architect IBM Enterprises Commercial IBM DB2, Informix, Oracle, Sybase, MS SQL Server, MySQL, Teradata Linux, Windows Standalone Unknown
MicroOLAP Databaser Designer MicroOLAP SMBs Commercial MySQL, PostgreSQL All versions of Windows Standalone 2003
MySQL Workbench MySQL (An Oracle Company) SMBs – personal Commercial MySQL Linux, Windows, MAC OS Standalone 2006
Online Data Modeler JenaSoft SMBs – personal Commercial MS SQL Server, MySQL, PostgreSQL, Oracle, SQLite, MS Access Online Standalone 2011
Open ModelSphere Grandite SMBs – personal Commercial and free edition available MS SQL Server, MySQL Online Part of modeling environment with UML support 2010
Oracle SQL Developer Data Modeler Oracle Enterprises Free Oracle, MS SQL Server, IBM DB2 Cross-platform Standalone Unknown
PowerDesigner Sybase SMBs and enterprises Commercial MS SQL Server, Oracle, PostgreSQL, MySQL, IBM DB2, Informix All versions of Windows Standalone 1996
SQL Power Architect SQLPower Software SMBs Commercial and free community edition PostgreSQL, Oracle, MS SQL Server Windows Standalone 2004
Toad Data Modeler Quest Software SMBs and enterprises Commercial (Free, limited, community edition available) Access, IBM DB2, Informix, MySQL, Oracle, PostgreSQL, MS SQL Server All versions of Windows Standalone 2005 (before this date known as CaseStudio)
XCase Resolution Software Ltd SMBs and enterprises Commercial MS SQL Server, Oracle, PostgreSQL, MySQL, IBM DB2 All versions of Windows Standalone 1996
Tool Creator Target Business Size License Supported Database Platforms Supported OSs Standalone or bundled into a larger toolkit Launch Date

Features

Tool Supported data models (conceptual, logical, physical) Supported notations Forward engineering Reverse engineering Model/database comparison and synchronization Teamwork/repository
CA ERwin Data Modeler Conceptual, logical, physical IDEF1X, IE (Crows feet), and more Yes Yes Update database and/or update model Workgroup edition provides collaboration
Database Visual Architect Physical Crows feet Yes Yes Update database and/or update model Additional product Teamwork Server for collaboration.
dbConstructor Physical Crows feet Yes Yes Update database No
DbSchema Physical Crows feet Yes Yes Compare to database and generate alter database script (update existing model not supported). No
DbWrench Database Design Physical Crows feet Yes Yes Update database and/or update model No
DeZign for Databases Logical, physical IDEF1X, IE (Crows feet) Yes Yes Update database and/or update model Not directly. Merge functionality available.
Enterprise Architect Conceptual, Logical & Physical + MDA transform of Logical to Physical IDEF1X, UML DDL, Information Engineering & ERD Yes Yes Update database and/or update model Multi-user collaboration using File or DBMS Repositoty (or transfer via XMI, CVS/TFS or Difference Merge)
ER/Studio Conceptual, logical, physical IDEF1X, IE (Crows feet) Yes Yes Update database and/or update model Additional ER/Studio Repository for collaboration
IBM InfoSphere Data Architect Conceptual, logical, physical IDEF1X, IE (Crows feet), and more Yes Yes Update database and/or update model Yes
MicroOLAP Databaser Designer Physical Crows feet Yes Yes Update database with alter scripts No
MySQL Workbench Physical IE (Crows feet) Yes Yes Update database and/or update model No
Navicat Conceptual, physical IE (Crows feet) Yes Yes Update database and/or update model No
Online Data Modeler Logical, physical IDEF1X (incomplete) Yes Yes Update database (update model unknown) Unknown
Open ModelSphere Logical, physical IDEF1X, IE (Crows feet), and more Yes Yes Update database and/or update model No
Oracle SQL Developer Data Modeler Logical, physical IDEF1X, IE (Crows feet), and more Yes Yes Update database and/or update model Unknown
PowerDesigner Conceptual, logical, physical IDEF1X, IE (Crows feet), and more Yes Yes Update database and/or update model Yes
SQL Power Architect Physical IDEF1X, IE (Crows feet), and more Yes Yes Compare models and generate alter script No
Toad Data Modeler Logical, physical IDEF1X, IE (Crows feet), and more Yes Yes Update database and/or update model Yes
XCase Physical IDEF1X, IE (Crows feet), and more Yes Yes Update database and/or update model Not directly. Merge functionality available.
Tool Supported data models (conceptual, logical, physical) Supported notations Forward engineering Reverse engineering Model/database comparison and synchronization Teamwork/repository

source: wikipedia

Comparison of Database tools

In General:

Product Creator Latest stable release Latest testing release License Runs on Windows Runs on Mac OS X Runs on Linux Oracle MySQL PostgreSQL SQL Server ODBC JDBC SQLite Other Programming language
ABIS-Query[1] BOS-Software Sarl[2] 1.4 (2010-09-22) 1.4 (2010-09-22) Proprietary Yes No No Yes Yes Yes Yes Yes SAP, Firebird, Advantage Database, MS Access, MS Excel C#
ACDB[3] Electronic Experience 2.0.1 (2010-04-25) ? Proprietary Yes Yes Yes Yes Yes Java
Adminer Jakub Vrána 3.3.3 (2011-08-12) none distributed Apache license or GPL Yes Yes Yes Yes Yes Yes Yes Yes php
Advanced Query Tool (AQT) Cardett Associates Ltd[4] 8.2.8 (2009-08-31) 8.2.8 (2009-08-31) Proprietary Yes No No Yes Yes Yes Yes Yes C++
Aqua Data Studio[5] AquaFold, Inc. 9.0.0 (2010-10-04) ? Proprietary Yes Yes Yes Yes Yes Yes Yes Yes Yes IBM DB2 iSeries, DB2 LUW, IBM DB2 z/OS, Netezza, Sybase ASE, Sybase Anywhere, Sybase (IQ), Teradata, Aster Data Systems (nCluster), Informix, Apache Derby Java
ARMS[6] NovaQuantum 1.0.2 (2010-03-10) 1.0.2 (2010-03-10) Proprietary Yes Yes Yes Yes JavaScript / PHP
Caché Monitor[7] Andreas Schneider 2009 2009 ? Yes Yes Yes InterSystems Caché Java
Chive (Software) Fusonic 0.3.0 ? GPL Yes Yes Yes Yes php
CompareData Zidsoft 2008 2008 Proprietary Yes No No Yes C++
DaDaBIK Eugenio Tacchini 4.3 (2010-12-08) 4.4 alpha (2010-02-02) GPL Yes Yes Yes Yes Yes Yes Yes No No Yes php
Data Analyst Toolkit[8] LiveMeta 1.1.0.3 (209-09-22) 1.1.0.3 (2009-09-22) Freeware Yes No No Yes Yes Yes Yes Yes DB2 – All versions zOs and UDB, Any DB with an ODBC Driver C#
Data Visualization[9] Adam Witkowski 1.1 (2011-11-27) Not Available Freeware Yes Yes Yes Yes Yes Yes Yes Yes Yes Firebird, IBM DB2, Interbase, Amazon SimpleDB, Sybase, KDB_(database) Java
Database Deployment Manager The Unauthorized Frog project v0.1a (2010-10-14) ? LGPL Yes No Yes Yes Qt/C++
Database Master with LINQ[10] Nucleon Software v3.0 (2010-06-15) ? Proprietary Yes No No Yes Yes Yes Yes Yes No Yes MongoDB, SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, Ingres (database), Firebird, SQLite, EffiProz, XML File, ODBC, OleDb, MS Access Netezza .Net / C#
DatabaseSpy Altova v2010r3 (2009-05-05) ? Proprietary Yes No No Yes Yes Yes Yes IBM DB2, Sybase, MS Access C++
Database Workbench[11] Upscene Productions v4.2.1 (2012-02-09) ? Proprietary Yes No No Yes Yes No Yes Yes No No InterBase, Sybase SQL Anywhere, Firebird, NexusDB Delphi
DataFlyer[12] Alvila Systems 2.0 ? Proprietary Yes No Yes Yes Yes Yes Yes Yes Yes IBM DB2 , Apache Derby , HSQLDB , MS Excel Java
DatAdmin[13] JenaSoft 3.6.3 (2010-05-18) 3.7.3 (2010-05-16) Proprietary Yes No No Yes Yes Yes Yes MS Access C#
DataGlass[14] Impathic 3.0 (2010-12-04) ? Proprietary No iOS (iPhone, iPad, iPod Touch) No Yes Yes Yes Yes Microsoft Access, IBM DB2, Sybase ASE Objective-C
DBeauty[15] DBeauty 1.0 (2011-08-15) 1.0 (2011-08-15) Apache 2.0 Yes Yes Yes Yes Yes Yes Yes Yes JDBC Java
DBEdit Jef Van Den Ouweland 2.4 (2011-03-18) ? GPL Yes Yes Yes Yes Yes Yes Yes No Yes Yes IBM DB2, HSQLDB, Apache Derby, H2 Java
dbForge SQL Complete Devart 2.00 (2011-21-01) 2.00.46 (2011-24-01) Freeware and Proprietary Yes No No No No No Yes C#
dbForge Studio for MySQL Devart 4.50 (2010-06-21) ? Proprietary Yes No No Yes C#
DBMan[16] SQLEXEC LLC 5.0 (2009-10-01) 5.0 (2009-10-01) ? Yes No No Yes Yes Yes Yes Yes DB2, Sybase (ASE/ASA), MS Access, Firebird PowerBuilder/C++
DbNinja[17] Vayer Software Ltd. 1.0.1 (2010-01-01) ? Proprietary Yes Yes Yes Yes ?
DbSchema[18] Wise Coders Solutions[19] 5.1.2 (2008-08-13) ? Proprietary Yes Yes Yes Yes Yes Yes Yes Yes Yes Java
DBTyp.NET[20] BYPsoft 2007 2008 Proprietary Yes No No Yes Yes Yes C#
DbVisualizer[21] DbVis Software 8.0 (2011-06-16) ? Proprietary Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes IBM DB2, Sybase ASE, Informix, Apache Derby, Mimer SQL, HP Neoview, H2 Java
Debugger for MySQL[22] MyDebugger.com 1.2 (2010-06-02) 1.2 (2010-06-02) Proprietary Yes No No Yes Embarcadero Delphi
Devgems Data Modeler Devgems 1.0.0 (2009-04-30) ? Proprietary Yes No No Yes InterBase, Firebird Embarcadero Delphi
Dezign for Database Datanamic 6.3.3 (2011-06-01) ? Proprietary Yes No No Yes Yes Yes Yes InterBase, IBM DB2, Firebird, MS Access, Paradox, dBase, Pervasive, Informix, Clipper, Foxpro, Sybase, ElevateDB, NexusDB, DBISAM, Sqlite Embarcadero Delphi
DreamCoder[23] DreamCoder for MySQL/Oracle/PostgreSQL 5.1.1 (2008-04-04) ? Proprietary Yes No No Yes Yes Yes ?
Embarcadero DBArtisan Embarcadero Technologies 9.0.1 9.1 Proprietary Yes No No Yes Yes Yes Yes Yes DB2, Sybase ASE C++ Java
EMS SQL Management Studio SQLManager.net 1.1.01 (2007-12-13) ? Proprietary Yes Yes Yes Yes Yes Yes Yes DB2, InterBase/Firebird ?
Epictetus Antilogic Software ? 1.0 (2009-06-17) Proprietary Yes Yes Yes Yes Yes Yes Sybase, InterBase/Firebird, H2, HSQLDB Java
ERwin Data Modeler ca.com 7.3 ? Proprietary Yes Yes Yes Yes Yes Yes Yes Yes MS Access, DB2, Foxpro, Informix, Ingres, Sybase, Teradata ?
ESF Database Migration Toolkit EasyFrom Inc 6.0.21 (2009-06-05) ? Proprietary Yes No No Yes Yes Yes Yes Yes IBM DB2, MS Access, MS Excel, Firebird, Interbase, Foxpro, dBase, CSV/Text, any ODBC C++
Happy Fish Automatisering Polderij 4.0.1 (2008-05-24) ? ? Yes No No Yes Borland Delphi
HeidiSQL HeidiSQL 4.0 (2009-04-30) 5.0 Beta (2010-01-30) GPL Yes Yes Yes Yes Embarcadero Delphi
Jailer[24] Jailer 3.5.6 (2011-06-07) 3.5.6 (2011-06-07) Apache 2.0 Yes Yes Yes Yes Yes Yes Yes Yes JDBC Java
Maatkit Baron Schwartz 5247 (2010-06-01) ? GPL Yes Yes Yes Yes Perl
MicroOLAP Database Designer MicrOLAP 1.2.3 (2008-02-01) 1.2.4-beta (2008-05-09) Proprietary Yes No No Yes Yes ?
My Database OnLine[25] DataCast Technologies Inc. 1.4 (2007-06-22) 1.4 (2007-06-22) Proprietary Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Mixed
Microsoft SQL Server Management Studio Microsoft ? ? Proprietary Yes No No Yes .Net
ModelRight ModelRight 3.6 3.7 Proprietary Yes No No Yes Yes Yes Yes SQL Server, Oracle, MySQL, PostgreSQL, DB2, DB2/zOS, MS Access C++
MyOra Jayam Systems LLC 1.0.4 (2010-04-09) ? Proprietary Yes No No Yes Java
MyQuery Builder[26] MyQuery Builder] 2.0.4 (2011-08-04) 2.0.4 (2011-08-04) Proprietary Yes Yes Yes Yes PHP
MySql Lite Administrator Vladimir Carrer ? 0.8 (2008-06-11) GPL Yes Yes Yes Yes php
MySQL Workbench Oracle Corporation 5.2.35 (2011-09-23)  ? GPL Yes Yes Yes Yes C++/C# Objective-C
Navicat Navicat 9.0.10 (2010-06-23) 9.0.10 (2010-06-23) Proprietary Yes Yes Yes Yes Yes Yes Yes Yes Yes Borland Delphi Objective-C
Nob Hill Database Compare[27] Nob Hill Software 1.3.01 ? Proprietary Yes No No Yes Yes .net
Online Data Modeler[28] JenaSoft 1.0.0 1.0.0 Proprietary Yes Yes Yes Yes Yes Yes Yes Yes EffiProz JavaScript, ASP.NET
Oracle Enterprise Manager Oracle Corp. 10gR5(2009-03-03) ? Proprietary Yes No Yes Yes Yes Yes DB2, Sybase, TimesTen Java
Oracle SQL Developer Oracle Corp. 2.1.1 (1 March 2010) 3.0 early adopter Proprietary Yes Yes Yes Yes Yes No Yes Yes Yes Microsoft Access, Sybase, DB2, Teradata Java
OraDeveloper Studio Devart 2.55 (2007-11-28) ? Proprietary Yes No No Yes C#
Orbada Andrzej Kałuża 1.0.4.109 (2011-02-08) 1.0.4.109 (2011-02-08) GPL Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Firebird, HSQL, Interbase, Derby all with JDBC driver Java
pgAdmin III pgAdmin Development Team 1.14.0 (2011-09-12) ? Artistic License Yes Yes Yes Yes C++
phpliteadmin Dane Iracleous 1.6 (2011-3-15) ? GPL No No No No No No No No No Yes php
phpMSAdmin Adam Voigt 0.18 (2006-07-23) ? GPL Yes Yes Yes Yes php
phpMyAdmin phpMyAdmin Development Team 3.4.2 (2011-06-07) ? GPL Yes Yes Yes Yes Drizzle, MariaDB php
phpPgAdmin The phpPgAdmin Project 4.2.3 (2010-03-29) ? GPL Yes Yes Yes Yes php
PL/SQL Developer[29] Allround Automations 8.0.0 (2009-08-24) 8.0.0 (2009-08-24) Proprietary Yes No No Yes Embarcadero Delphi
QuantumDB[30] Julen Parra 3.3.3 (2009-08-27) ? GPL Yes Yes Yes Yes Yes Java
QweryBuilder[31] Werysoft 7.1.0 (2011-11-12) 7.1.0 (2011-11-12) Proprietary Yes No No Yes Yes Yes Sybase, SQL Anywhere ?
RazorSQL[32] Richardson Software, LLC[33] 5.1.2 (2010-04-14) ? Proprietary Yes Yes Yes Yes Java
SchemaBank Ezenso Limited ? ? SaaS Yes Yes Yes Yes Yes ?
Silver Sash Administrator[34] silversash.com 1.0.1 ? Proprietary Yes Yes Yes Yes Java
SQL Buddy[35] ? 1.3.3 (2011-01-18) ? MIT Yes Yes Yes No Yes Yes php
SQL Developer[36] Jan Borchers 2.3.0 ? Proprietary Yes Yes Yes Yes Java
SQL Edge[37] Bay Breeze Software, Inc. 3.3 ? Proprietary Yes Yes Yes Yes Yes Yes Yes Yes DB2, Sybase, MaxDB Java
SQL Maestro SQL Maestro Group various various EULA Yes No No Yes Yes Yes Yes Yes Yes DB2, Firebird, MS Access Embarcadero Delphi
SQL Navigator Quest Software 6.1.1(2009-02-17) 6.2 Proprietary Yes No No Yes Embarcadero Delphi
SQL Server Comparison Expert[38] DatabaseComparer Ltd. 2.41 (2010-01-23) 2.41 (2010-01-23) Proprietary Yes No No Yes C++
SQLDetective Conquest Software Solutions 4.3.1 (2009-04-07) 4.3.1 (2009-04-07) Proprietary Yes No Yes Yes Embarcadero Delphi
SQLPro SQL Client Vive 1.4 (2008-02-04) ? Proprietary Yes No No Yes Yes Yes Yes Yes C++
SQLWave nerocode 5.7.2 Not Available Proprietary Yes No No Yes ?
SQLyog Webyog Softworks Pvt. Ltd. 9.2 ? GPLv2 Yes Yes Yes Yes C++
SQuirreL SQL ? 3.1.2 (2010-12-31) ? GPL Yes Yes Yes Yes Yes Yes Yes Yes Yes Access,Axion Java RDBMS, Apache Derby, Daffodil DB, Fujitsu Siemens SESAM/SQL, Firebird, FrontBase, HSQLDB, Hypersonic SQL, H2 (DBMS), IBM DB2, Informix, Ingres, OpenIngres, InstantDB, InterBase, Mckoi SQL Database, , Microsoft SQL Server, Mimer SQL, Netezza, Pointbase, SAPDB, Sybase, Sunopsis XML Driver, Teradata Warehouse, ThinkSQL RDBMS, Vertica Analytic Database, and others with JDBC drivers. Java
Toad Quest Software Various Betas Proprietary Yes No No Yes Yes Yes Yes DB2, Sybase Embarcadero Delphi, C#.NET
Toad Data Modeler Quest Software 3.3.8 (2009-03-05) Betas[39] Freeware and Proprietary Yes No No Yes Yes Yes Yes DB2, MS Access, Sybase Embarcadero Delphi
TOra Community 2.1.3 (2010-09-19)  ? GPL Yes Yes Yes Yes Yes Yes Teradata C++/Qt
WWW SQL Designer Ondřej Žára 2.3.2 (2009-01-07) ? GPL Yes Yes Yes Yes Yes Yes Yes JavaScript

Features:

Legend

  •     Create/alter table:
  1.         Yes – can create table, alter its definition and data, and add new rows
  2.         Some – can only create/alter table definition, not data
  •     Browse table:
  1.         Yes – can browse table definition and data
  2.         Some – can only browse table definition
  •     Multi-server support:
  1.         Yes – can manage from the same window/session multiple servers
  2.         Some – can manage from a different window/session multiple servers
Tools User Interface Create & Alter wizard Browse Auto Completion Syntax colored Multi server support
Database Table Procedure Trigger Database Table Procedure Trigger
ABIS-Query[1] desktop No No No No Yes Yes Yes No No Yes ?
ACDB[3] desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
Adminer Browser-based Yes Yes Yes Yes Yes Yes Yes Yes No Yes ?
Altova DatabaseSpy desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Aqua Data Studio[5] desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
ARMS[6] Browser-based Yes Yes Yes Yes Yes Yes Yes Yes No No Yes
Caché Monitor[7] desktop No Yes No Yes Yes Yes Yes Yes Yes Yes ?
Chive Browser-based Yes Yes Yes Yes Yes Yes Yes Yes No Yes ?
DaDaBIK Browser-based No Some[40] No No No Some[41] No No No No No
Data Visualization[9] desktop No No No No Yes Yes No No Yes Yes Yes
Database Master with LINQ[10] desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Database Workbench[11] desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
DatAdmin[13] desktop Yes Yes Yes Yes Yes Yes Yes Yes No Yes Yes
DataGlass[14] mobile (iOS) No No No No Yes Yes No No No No Yes
DataFlyer[12] desktop ? ? ? ? Yes Yes ? ? ? ? ?
DBeauty[15] desktop No No No No Yes Yes No No Yes Yes ?
DBEdit desktop No No No No Yes Yes Yes No No Yes No
dbForge Studio for MySQL TDI Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
DBMan[16] desktop Yes Yes Yes Yes Yes Yes Yes Yes No No ?
DbNinja[17] Browser-based Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
DbSchema[18] desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
DbVisualizer[21] desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Debugger for MySQL[22] desktop No No Yes No No No Yes No Yes Yes Some
Devgems Data Modeler desktop Yes Yes Yes Yes Yes Yes Yes Yes No Yes ?
DeZign for Databases desktop Yes Some Yes Yes Yes Some Yes Yes No Yes ?
DreamCoder[23] desktop ? ? ? ? Yes Yes Yes Yes Yes[23] Yes ?
Embarcadero DBArtisan desktop Yes Yes Yes Yes Yes Yes Yes Yes No Yes ?
EMS SQL Management Studio desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Epictetus desktop No Yes No No Yes Yes Yes Yes Yes Yes ?
Happy Fish desktop Some[42] Some ? Yes Some[43] Some ? Yes No Yes ?
Jailer[24] desktop No No No No Yes Yes No No Yes Yes ?
jfacedbc desktop ? ? ? ? ? ? ? ? ? ? ?
MicroOLAP Database Designer desktop Yes Some Yes Yes Yes Some Yes Yes No Yes ?
Microsoft SQL Server Management Studio desktop Yes Yes Yes Yes Yes Yes Yes Yes No Yes Yes
ModelRight desktop Yes Yes Yes Yes Yes Yes Yes Yes No Yes ?
MyQuery Builder[26] Browser-based No No No No Yes Yes No No No No Yes
MySql Lite Administrator Browser-based No No No No Yes Yes No No No No ?
Navicat desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Online Data Modeler[28] Browser-based No Yes Yes Yes Yes No No No No Yes ?
Oracle Enterprise Manager Browser-based Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
Oracle SQL Developer desktop No Yes Yes Yes Yes Yes Yes Yes No Yes Yes
OraDeveloper Studio TDI Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
Orbada desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
pgAdmin III TDI Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
phpliteadmin Browser-based Yes Yes No No Yes Yes No No No No ?
phpMSAdmin Browser-based ? ? ? ? ? ? ? ? ? ? ?
phpMyAdmin Browser-based Yes Yes Yes No Yes Yes Yes Yes No Yes Yes
phpPgAdmin Browser-based ? ? ? ? ? ? ? ? ? ? ?
PL/SQL Developer[29] desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
QuantumDB[30] desktop ? ? ? ? Yes Yes ? ? No Yes ?
QweryBuilder[31] desktop No No No No Yes[44] Yes Yes Yes Yes[45] Yes Yes
RazorSQL[32] desktop ? ? ? ? ? ? ? ? ? ? ?
SchemaBank Browser-based Yes Yes Yes Yes No No No No No No No
Silver Sash Administrator[34] desktop Yes Yes Yes Yes Yes Yes Yes Yes No Yes Yes
SQL Developer[36] desktop Yes Some[46] Yes ? Yes Some[47] Yes ? Yes Yes ?
SQL Edge[37] desktop Yes Some[48] No No Yes Yes Yes[49] Yes[49] No Yes ?
SQL Maestro TDI Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
SQL Navigator desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
SQLDetective desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No
SQLyog desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
SQuirreL SQL desktop ? ? ? ? Yes Yes ? ? Yes Yes Some
SQLPro SQL Client desktop No No No No Yes Yes Yes Yes No Yes ?
SQLWave desktop ? ? ? ? Yes Yes Yes Yes ? Yes Yes
Toad desktop Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes ?
Toad Data Modeler desktop Yes Yes Yes Yes Some Some Some Some No Yes ?
TOra desktop No Yes Yes No Yes Yes Yes Yes Yes Yes Yes
WWW SQL Designer Browser-based No Some No No No No No No No No ?

Features (Continued)

Legend:

  •     User manager:
  1. Yes – user manager with support for database and schema permissions as
    well as for individual object (table, view, functions) permissions
  2.         Some – simple user manager with support for database and schema permissions
  3.         No – no user manager, or read-only user manager
user manager Plugin Compare Import Export Debugger Source control
ABIS-Query[1] No ? No SQL script, Excel SQL script, Excel multi-sheet, XML, XSD No ?
ACDB[3] Yes ? No SQL script SQL script Oracle, MySQL ?
Adminer Yes Yes Yes SQL script, CSV SQL script, CSV No SVN
Altova DatabaseSpy No No Yes CSV, XML XML, XML Structure, CSV, HTML, MS Excel No ?
Aqua Data Studio[5] Yes ? Yes CSV CSV, SQL; diagram as JPEG, PNG Oracle, Sybase, MS SQL Server, IBM DB2 (SVN) Subversion (software), (CVS) Concurrent Versions System
ARMS[6] No ? ? SQL script SQL script No ?
Chive Some ? No SQL script SQL script No ?
DaDaBIK Some[50] No No No CSV Yes No
Data Visualization[9] No No No CSV, XLS(X), ODS, XML PDF, XML, CSV, RTF, MATLAB, TXT, XLS(X), HTML, DOCX No No
Database Master with LINQ[10] Yes ? Yes XML Files, CSV, Bulk SQL Script SQL Script, MS Excel, MS Word, PDF, RTF, XPS, CSV, HTML, XML, OpenOffice, DBF, Pack&Go Export/Import, ER Diagram No ?
Database Workbench[11] Yes No Yes CSV, SQL Script XML, DBF, TXT, CSV, SQL Script, Lotus 123, DIF Yes No
DatAdmin[13] No Yes Yes SQL script, CSV, HTML, text file using regular expressions, generic XML SQL script, CSV, HTML, XML, DokuWIKI, MediaWIKI, generic text file, MS Excel No No
DataGlass[14] No No No No CSV No No
DataFlyer[12] No ? No No CSV , MS Excel No ?
DBEdit No No No No MS Excel, PDF, Text, SQL script No ?
dbForge Studio for MySQL Yes Yes Yes Yes CSV, DBF, HTML, MS Access, MS Excel, ODBC, PDF, RTF, Text, XML[51] Yes ?
<DBMan[16] Yes No Yes SQL Script, TXT, CSV TXT, CSV, SQL, PDF, XLS, XML, HTML Table, WK1, WMF, DIF, DBF, Clipboard No No
DbNinja[17] Yes Yes Yes SQL script, CSV SQL script, CSV No ?
DbSchema[18] No ? Yes Yes SQL script, CSV, HTML, XML,Diagram as image No ?
DbVisualizer[21] No Some No Yes CSV, HTML, Excel, XML, SQL, text,Diagram in PNG, JPEG No ?
Debugger for MySQL[22] No No No No No Yes No
Devgems Data Modeler No No No No SQL; HTML report; diagram as EMF, BMP No ?
DeZign for Databases No No Yes No SQL; diagram as EMF, BMP, GIF, JPEG, PNG No ?
DreamCoder[23] No No Yes[23] Yes[23] Yes[23] No[23] ?
EMS SQL Management Studio Yes ? Yes[52] MS Excel/Access/Word, TXT, CSV, DBF, HTML, XML Datapacket, XML Generic, ODF Spreadsheets, ODF Text Imported formats plus RTF, PDF, LaTeX, DIF file, SYLK ? ?
Epictetus No Yes No No Excel No ?
ESF Database Migration Toolkit Yes No Yes Any database/file systems Any database/file systems No ?
Happy Fish No ? Yes ? SQL; diagram as XML, BMP, JPEG, WMF No ?
Jailer[24] No No No SQL script SQL script (topologically sorted), XML, DBUnit-XML No ?
jfacedbc ? ? ? ? ? No ?
MicroOLAP Database Designer No ? No Some[53] SQL; HTML report; diagram as BMP, EMF, GIF, JPEG, PNG No ?
ModelRight Some Yes Yes Yes – from supported databases using native interfaces, or from any ODBC source SQL; XML; DTD; Diagram as BMP, JPEG No ?
MyQuery Builder[26] No No No MySQL script MySQL script No No
MySql Lite Administrator No No No No No No ?
Navicat Yes No Yes Yes – TXT, CSV, DBF, HTML, MS Excel, MS Access, Paradox file, WK1, WQ1, XML, or from any ODBC source (See link for limitations[54]) Yes – TXT, CSV, HTML, XML, DBF, SQL script, RTF, MS Word, MS Excel, MS Access, MS Windows Clipboard, Paradox file, WK1, WQ1, SLK, DIF, LDIF (See link for limitations[54]) Yes No
Oracle SQL Developer Yes ? ? Yes Yes Yes ?
OraDeveloper Studio Yes No Yes No CSV, DBF, HTML, MS Access, MS Excel, ODBC, PDF, RTF, Text, XML Yes ?
Orbada No Yes Yes SQL script SQL script, CSV, XML, HTML, PDF, Excel, DBF, DataText No No
pgAdmin III Yes Yes No No CSV, text, HTML, XML Yes No
phpMSAdmin ? ? ? ? ? No ?
phpMyAdmin Yes Some Yes Yes – CSV, SQL, XML, Excel, ODS Yes – CSV, LaTeX, Excel, Word, ODS, ODT, XML, SQL, YAML, Texy!, JSON, NHibernate, PHP, PDF, Mediawiki No Git
phpPgAdmin ? ? ? ? ? No ?
PL/SQL Developer[29] Yes Yes Yes Dump, SQL CSV, TSV, XML, SQL, HTML, MS Excel Yes Microsoft Visual SourceSafe, Merant PVCS, MKS Source Integrity
QuantumDB[30] ? ? ? ? CSV, XML No ?
QweryBuilder[31] No No Yes[55] Yes Yes Yes No
RazorSQL[32] ? ? ? ? ? No ?
SchemaBank Some Some Yes SQL script SQL script, HTML report, diagram as PNG No Yes
SQL Developer[36] No Yes Yes clipboard SQL, CSV, XML, HTML; diagram as PNG, SVG No ?
SQL Edge[37] No ? No No data as CSV or Tab-delimited; diagram as GIF, JPEG, PNG or SVG[56] No ?
SQL Maestro Yes[57] ? ? MS Excel, MS Access, DBF, XML, TXT, CSV SQL, MS Excel/Access/Word, RTF, HTML, PDF, TXT, CSV, DIF file, SYLK, LaTeX, XML, DBF; diagram in XML format Yes ?
SQL Navigator Some No Some Yes Yes Yes CVS, ClearCase, PVCS, StarTeam, TFS, VSS
SQLDetective Yes Yes Yes Yes Yes Yes Microsoft Visual SourceSafe, Merant PVCS
SQLPro SQL Client ? ? ? ? ? No ?
SQLWave ? No ? ? ? ? ?
SQLyog Yes ? Yes Yes Yes ? ?
SQL Server Management Studio Yes ? ? ? ? Yes ?
SQuirreL SQL ? Yes ? Yes ? No ?
Toad Some No Yes Yes Yes Yes SVN, CVS, TFS, VSS
Toad Data Modeler No ? Yes Toad for Oracle ERD, ERWin 7.1(XML) via plugin SQL; meta data in XML; report in HTML/RTF/CSV; diagram as BMP, JPEG, PNG No ?
TOra Some No Yes Yes Yes Yes No
WWW SQL Designer No No No No SQL No ?

Features – visual design and reverse engineering

Legend:

  • Visual schema/E-R design: the ability to draw entity-relationship
    diagrams for the database. If missing, the following two features will
    also be missing
  •     Reverse engineering – the ability to produce an ER diagram from a database, complete with foreign key relationships
  1. Yes – supports incremental reverse engineering, preserving user
    modifications to the diagram and importing only changes from the
    database
  2.         Some – can only reverse engineer the entire
    database at once and drops any user modifications to the diagram (can’t
    “refresh” the diagram to match the database)
  •    
    Forward engineering
    – the ability to update the database schema with
    changes made to its entities and relationships via the ER diagram visual
    designer
  1.         Yes – can update user-selected entities
  2.         Some – can only update the entire database at once
Visual query builder Visual schema/model/E-R diagram design Reverse engineering Forward engineering ER diagram groupboxes
ABIS-Query[1] Yes No Yes Yes No
ACDB[3] No Yes Yes Yes Yes
Adminer Yes Yes Yes No No
Altova DatabaseSpy Yes Yes Yes Yes ?
Aqua Data Studio[5] Yes Yes Some No ?
ARMS[6] Yes No No No No
Chive No No No No No
DaDaBIK Some[58] No No No No
Database Master with LINQ[10] Yes Yes Yes Yes Yes
Database Workbench[11] Yes Yes Yes Yes Yes
DatAdmin[13] Yes Yes Yes Yes No
DataGlass[14] Yes No No No No
DBEdit No No No No No
dbForge Studio for MySQL Yes Yes Yes Yes Yes
DBMan[16] No Yes Yes Yes Yes
DbNinja[17] Yes Yes Yes No No
DbSchema[18] Yes Yes Yes Yes Yes
DbVisualizer[21] Yes Yes Some No No
Devgems Data Modeler No Yes Yes Yes Yes
DeZign for Databases No Yes Yes Yes[59] Yes
DreamCoder[23] Yes ? ? ? ?
EMS SQL Management Studio Yes[60] Yes Yes No No
Happy Fish ? Yes Yes Yes Yes
Jailer[24] Yes Yes Yes No No
jfacedbc ? ? ? ? ?
MicroOLAP Database Designer No Yes Some Yes ?
ModelRight No Yes Yes Yes Yes
MyQuery Builder[26] Yes No No No No
MySql Lite Administrator No No No No No
Navicat Yes Yes Yes Yes Yes
Online Data Modeler[28] No Yes Yes Yes No
Oracle SQL Developer Yes Yes Yes Yes ?
OraDeveloper Studio Yes No No No No
Orbada No No No No No
pgAdmin III Yes No No No No
phpMSAdmin No ? ? ? ?
phpMyAdmin Yes Yes Yes No No
phpPgAdmin No No No No No
QuantumDB[30] No ? ? ? ?
QweryBuilder[31] Yes[61] No Yes No No
RazorSQL[32] Yes[62] No No No No
SchemaBank No Yes Yes Yes Yes
SQL Developer[36] No Yes Yes No[47] No
SQL Edge[37] Some[63] Yes Yes No[48] No
SQL Maestro Yes[64] Yes Yes Yes No
SQL Navigator Yes Yes Yes No ?
SQL Server Management Studio ? Yes Yes ? ?
SQLDetective Yes No No No No
SQLPro SQL Client ? No No No No
SQLWave ? ? ? ? ?
SQLyog Yes[65] Yes[66] Yes Yes ?
SQuirreL SQL Yes Yes Yes[67] ? No
Toad Yes Yes Yes Yes ?
Toad Data Modeler No Yes Yes Yes[68] ?
WWW SQL Designer No Yes No Some No

source: wikipedia

Comparison of relational database management systems

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model.

A short definition of an RDBMS is: a DBMS in which data is stored in tables and the relationships among the data are also stored in tables. The data can be accessed or reassembled in many different ways without having to change the table forms.

Timeline of the development of major RDBMS software

Operating System support

The operating systems that the RDBMSes can run on:

Windows Mac OS X Linux BSD UNIX AmigaOS Symbian z/OS1 iOS Android
4th Dimension Yes Yes No No No No No No No No
ADABAS Yes No Yes No Yes No No Yes No No
Adaptive Server Enterprise Yes No Yes Yes Yes No No No Yes Yes
Advantage Database Server Yes No Yes No No No No No No No
Altibase Yes No Yes No Yes No No No No No
Apache Derby2 Yes Yes Yes Yes Yes No No Yes ? No
CUBRID Yes Partial10 Yes No No No No No No No
Drizzle No Yes Yes Yes Yes No No No No No
DB25 Yes Yes (Express C) Yes No Yes No No Yes No No
Empress Embedded Database Yes Yes Yes Yes Yes No No No No Yes
Firebird Yes Yes Yes Yes Yes No No Maybe No No
HSQLDB2 Yes Yes Yes Yes Yes No No Yes ? ?
H22 Yes Yes Yes Yes Yes No No Yes ? ?
FileMaker Yes Yes No No No No No No Yes No
Informix Dynamic Server Yes Yes Yes Yes Yes No No Yes No No
Ingres Yes Yes Yes Yes Yes No No Partial No No
InterBase Yes Yes Yes No Yes (Solaris) No No No No No
Linter SQL RDBMS6 Yes Yes Yes Yes Yes6 No No No ? Yes
LucidDB Yes Yes Yes No No No No No No No
MariaDB Yes Yes[28] Yes Maybe Yes No No No ? ?
MaxDB Yes No Yes No Yes No No Maybe No No
Microsoft Access (JET) Yes No No No No No No No No No
Microsoft Visual Foxpro Yes No No No No No No No No No
Microsoft SQL Server Yes No No No No No No No No No
Microsoft SQL Server Compact (Embedded Database) Yes No No No No No No No No No
MonetDB/SQL Yes Yes Yes No Yes No No No ? ?
MySQL8 Yes Yes Yes Yes Yes Yes Yes Yes ? No
Omnis Studio Yes Yes Yes No No No No No No No
OpenBase SQL Yes Yes Yes Yes Yes No No No No No
OpenEdge Yes No Yes No Yes No No No No No
OpenLink Virtuoso Yes Yes Yes Yes Yes No No Yes No No
Oracle4 Yes Yes Yes No Yes No No Yes No No
Oracle Rdb3 No No No No No No No No No No
Pervasive PSQL Yes Yes (OEM only) Yes No No No No No No No
Polyhedra7 Yes No Yes No Yes No No No No No
PostgreSQL Yes Yes Yes Yes Yes No No No No Yes
R:Base Yes No No No No No No No No No
RDM Embedded Yes Yes Yes Yes Yes No No No No No
RDM Server Yes Yes Yes Yes Yes No No No No No
ScimoreDB Yes No No No No No No No No No
SmallSQL2 Yes Yes Yes Yes Yes No No Yes No No
SQL Anywhere Yes Yes Yes No Yes No No No No Yes
SQLBase Yes No Yes No No No No No No No
SQLite Yes Yes Yes Yes Yes Yes Yes Maybe Yes Yes
Superbase Yes No No No No Yes No No No No
Teradata Yes No Yes No Yes No No No No No
UniData Yes No Yes No Yes No No No No No
UniVerse Yes No Yes No Yes No No No No No
Xeround Cloud Database Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Windows Mac OS X Linux BSD UNIX AmigaOS Symbian z/OS1 iOS Android

Data Size Limits

Information about data size limits.

 

Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size
4th Dimension limited  ?  ? 65135 200 GB (2 GiB Unicode) 200 GB (2 GiB Unicode) 64 bits  ?  ?  ?
Advantage Database Server Unlimited 16 EiB 65530 B 65135 / (10+ AvgFieldNameLength) 4 GiB  ? 64 bits  ?  ? 128
Apache Derby Unlimited Unlimited Unlimited 1012 (5000 in views) 2 147 483 647 chars 254 (VARCHAR: 32672) 64 bits 0001-01-01 9999-12-31 128
CUBRID 2 EB 2 EB Unlimited 6400 Unlimited 1 GB 64 bits 0001-01-01 9999-12-31 254
Drizzle Unlimited 64 TB 8 kB 1000 4 GB (longtext, longblob) 64 kB (text) 64 bits 0001 9999 64
DB2 512 TiB 512 TB 32 677 B 1012 2 GB 32 KiB) 64 bits 0001 9999 128
Empress Embedded Database Unlimited 263-1 bytes 2 GB 32767 2 GB 2 GB 64 bits 0000-01-01 9999-12-31 32
FileMaker 8TB 8TB 8TB 256,000,000 4GB 10^9 characters 10^9 numbers w/ range 10^-400 to 10^400 0001-01-01 4000-12-31 100
Firebird Unlimited1 ~32 TB 65 536 B Depends on data types used. 2 GB 32 767 B 64 bits 100 32768 31
HSQLDB 64 TB Unlimited8 Unlimited8 Unlimited8 64 TB7 Unlimited8 Unlimited8 0001-01-01 9999-12-31 128
H2 64 TB Unlimited8 Unlimited8 Unlimited8 64 TB7 Unlimited8 64 bits -99999999 99999999 Unlimited8
Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size
Informix Dynamic Server ~128 PB ~128 PB 32 765 bytes (exclusive of large objects) 32765 4 TB 32765 1032 01/01/000110 12/31/9999 128 bytes
Ingres Unlimited Unlimited 256 kB 1024 2 GB 32 000 B 64 bits 0001 9999 32
InterBase Unlimited1 ~32 TB 65 536 B Depends on data types used. 2 GB 32 767 B 64 bits 100 32768 31
Linter SQL RDBMS Unlimited 230 rows 64 kB (w/o BLOBs), 4 GB (BLOB) 250 4 GB 4 kB 64 bits 0001-01-01 2099-12-31 128
Microsoft Access (JET) 2 GB 2 GB 16 MB 255 64 kB (memo field), 1 GB (“OLE Object” field) 255 B (text field) 32 bits 0100 9999 64
Microsoft Visual Foxpro Unlimited 2 GB 65 500 B 255 2 GB 16 MB 32 bits 0001 9999  ?
Microsoft SQL Server 524 258 TB (32 767 files * 16 TB max file size) 524 258 TB Unlimited 30000 2 GB 2 GB6 126 bits2 0001 9999 128
Microsoft SQL Server Compact (Embedded Database) 4 GB 4 GB 8060 bytes 1024 500 MB 4000 126 bits 2 0001 9999 128
MySQL 5 Unlimited MyISAM storage limits: 256 TB; Innodb storage limits: 64 TB 64 kB3 40964 4 GB (longtext, longblob) 64 kB (text) 64 bits 1000 9999 64
OpenLink Virtuoso 32 TB DB size (or 32 TB) 4K 200 2 GB 2 GB 2**31 0 9999 100
Oracle Unlimited (4 GB * block size per tablespace) 4 GB * block size (with BIGFILE tablespace) 8 kB 1000 Unlimited 4000 B 126 bits -4712 9999 30
Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size
Polyhedra Limited by available RAM, address space 232 rows Unlimited 65536 4 GB (subject to RAM) 4 GB (subject to RAM) 32 bits 0001-01-01 8000-12-31 255
PostgreSQL Unlimited 32 TB 1.6 TB 250-1600 depending on type 1 GB (text, bytea) – stored inline or 2 GB (stored in pg_largeobject) 1 GB Unlimited -4713 5874897 63
RDM Embedded Unlimited 248-1 rows 32 KB 1000 4 GB char: 256, varchar: 4 KB 64 bits 0001-01-01 11758978-12-31 31
RDM Server Unlimited 264-1 rows 32 KB 32768 Unlimited 32 KB 64 bits 0001-01-01 11758978-12-31 32
ScimoreDB Unlimited 16 EB 8050 B 255 16 TB 8000 B 64 bits  ?  ?  ?
SQL Anywhere 104 TB (13 files, each file up to 8 TB (32k pages)) Limited by file size Limited by file size 45000 2 GB 2 GB 64 bits 0001-01-01 9999-12-31  ?
SQLite 128 TB (231 pages * 64 kB max page size) Limited by file size Limited by file size 32767 2 GB 2 GB 64 bits No DATE type9 No DATE type9 Unlimited
Teradata Unlimited Unlimited 64 kB wo/lobs (64 GB w/lobs) 2048 2 GB 10 000 64 bits  ? 9999-12-31 Select 80991231 (date); 30
UniVerse Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited Unlimited
Xeround Cloud Database Unlimited Unlimited 32GB, depending on available memory 1000 4GB 64K 64 bits 1000 9999 64
Max DB size Max table size Max row size Max columns per row Max Blob/Clob size Max CHAR size Max NUMBER size Min DATE value Max DATE value Max column name size

Database capabilities

Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and Clobs Common Table Expressions Windowing Functions Parallel Query
4th Dimension Yes Yes Yes Yes Yes No No Yes ? ? ?
ADABAS Yes ? ? ? ? ? ? ? ? ? ?
Adaptive Server Enterprise Yes ? ? Yes Yes Yes Yes Yes ? ? Yes
Advantage Database Server Yes No No Yes Yes Yes Yes Yes ? No ?
Altibase Yes ? ? Yes Yes Yes Yes Yes ? ? ?
Apache Derby Yes Yes Yes Yes Yes ? ? Yes No No ?
CUBRID Yes Yes Yes Yes Yes Yes Yes Yes No No ?
Drizzle Yes No No Yes Yes Yes No Yes No No No[70]
DB2 Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes[71]
Empress Embedded Database Yes Yes Yes Yes Yes Yes Yes Yes ? ? ?
Firebird Yes ? ? Yes Yes Yes Yes Yes Yes Yes ?
HSQLDB Yes Yes Yes Yes Yes Yes Yes[72] Yes Yes No Yes[73]
H2 Yes Yes Yes Yes Yes Yes No Yes experimental[74] No[75] ?
Informix Dynamic Server Yes ? Yes, via MINUS Yes Yes Yes Yes Yes Yes ? Yes[76]
Ingres Yes No No Yes Yes Yes Yes Yes No No ?
InterBase Yes ? ? Yes Yes ? ? Yes ? ? ?
Linter SQL RDBMS Yes Yes Yes Yes Yes Yes Yes Yes No Yes ?
LucidDB Yes Yes Yes Yes Yes Yes Yes No ? ? ?
MaxDB Yes ? ? Yes Yes Yes No Yes ? ? ?
Microsoft Access (JET) Yes No No Yes Yes Yes No Yes No No ?
Microsoft Visual Foxpro Yes ? ? Yes Yes Yes ? Yes ? ? ?
Microsoft SQL Server Yes Yes (2005 and beyond) Yes (2005 and beyond) Yes Yes Yes Yes Yes Yes Yes Yes[77]
Microsoft SQL Server Compact (Embedded Database) Yes No No Yes Yes ? No Yes No No ?
MonetDB/SQL ? ? ? ? ? ? ? ? ? ? ?
MySQL Yes No No Yes Yes Yes No Yes No[78] No No[79]
OpenBase SQL No No No Yes Yes Yes Yes Yes ? ? ?
Oracle Yes Yes Yes, via MINUS Yes Yes Yes Yes Yes Yes 1 Yes Yes[80]
Oracle Rdb Yes ? ? Yes Yes Yes Yes Yes ? ? ?
OpenLink Virtuoso Yes ? ? Yes Yes Yes ? Yes ? ? ?
Polyhedra DBMS Yes Yes Yes Yes No ? ? Yes ? ? ?
PostgreSQL Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No[81]
RDM Embedded No No No Yes Yes No No Yes No No No
RDM Server Yes No No Yes Yes Yes No Yes No No No
ScimoreDB Yes ? ? Yes LEFT only Yes Yes Yes ? ? ?
SmallSQL ? ? ? ? ? ? ? ? ? ? ?
SQL Anywhere Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
SQLite Yes Yes Yes Yes LEFT only Yes ? Yes No No ?
Teradata Yes Yes Yes Yes Yes Yes Yes Yes No Yes Yes
UniVerse Yes Yes Yes Yes Yes Yes Yes No No No ?
Xeround Cloud Database Yes No No Yes Yes Yes No Yes No No No
Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and Clobs Common Table Expressions Windowing Functions Parallel Query

Source: wikipedia

The operating systems that the RDBMSes can run on.

SQL Query Syntax Tutorial for beginner

SQL is a standard language for accessing databases.

A database query is a piece of code (a query) that is sent to a database in order to get information back from the database. It is used as the way of retriving the information from database

A database “query” is basically a “question” that you ask the database. The results of the query is the information that is returned by the database management system. Queries are usually constructed using SQL (structured query language) which resembles a high-level programming language.

The term ‘query’ means to search, to question, or to find. When you query a database, your searching for information in the database. Different query languages exist for different type of databases. MS Access uses SQL, which stands for Structured Query Language. MS Access contains Tables, Forms, and Queries. The Forms are used to enter or display the data, the Tables are where the data is saved, and the queries are used to search for specific data.

The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

CREATE DATABASE database_name

For example:

CREATE DATABASE my_db

Creating Tables

You must create your tables before you can enter data into them. Use the Create Table command.

Syntax:

Create table tablename using filename
(fieldname fieldtype(length),
fieldname fieldtype(length),
fieldname fieldtype(length));

For example:

Create table TrnAPDoc using ‘TAPDoc.dat’
( VendId char(10),
RefNbr char(10),
DocDate date(4),
OrigDocAmt float(8),
CuryDocBal float(8),
OpenDoc logical(2));

Importing Data

You can import data into SQLScope from another data source by using the Insert command. Before completing the following exercises, you must import data into the tables you created. The example given in this section will step you through the process.

Syntax:

Insert into tablename
(fieldname, fieldname, fieldname)
Values
(@fieldname, @fieldname, @fieldname);

For example:

Insert into TrnVendor
(Vendid, Name, Add1, City, State, CurrBal, ExpAcct )
Values
(@Vendid, @Name, @Add1, @City, @State, @CurrBal, @ExpAcct)

Retrieving All Data

Select statements are used to retrieve data from  SQL tables. The Select statement illustrated below retrieves all of the columns and rows  from the named table.

Syntax:

Select *
from tablename;

For example:

Select *
from TrnVendor;

Results:

VendId Name Add1 City State CurrBal ExpAcct
TV001 Wet Off Towels 66 Keep Dry Way Toledo OH 1.31300E+001 7230
TV002 The Games All Here 85 Olympic Drive Warren NY 4.53170E+002 4110

Retrieving a Single Column

You can use SQL to retrieve a single column of data.

Syntax:

Select fieldname
from tablename;

For example:

Retrieve the vendor name from each record:

Select Name
from TrnVendor;

Results:

Name
Wet Off Towels
The Games All Here

Retrieving Multiple Columns

You can use SQL to retrieve multiple columns.

Syntax:

Select fieldname, fieldname, fieldname
from tablename;

For example:

Retrieve the vendor ID, name, and current balance for each record:

Select VendId, Name, CurrBal
from TrnVendor;

Results:

VendId Name CurrBal
TV001 Wet Off Towels 1.31300E+001
TV002 The Games All Here 4.53170E+002

Formatting Numbers

By default, Scalable SQL displays numbers using scientific notation. To change the format, you must add a mask.

Syntax:

Select fieldname<mask>, fieldname<mask>, fieldname<mask>
from  tablename;

Number Masks
9 Display number.
Z Display number, drop leading zeros.
+ Display a plus in front of positive numbers.
Display a negative in front of negative numbers.
Display a negative in front of negative numbers.
Display nothing in front of positive numbers.
( ) Display negative numbers enclosed in parentheses.
. Display decimal point.
$ Display dollar sign.
, Use comma to separate thousands.

For example:

Select VendId, Name, CurrBal [($Z,ZZZ,ZZZ.99)]
from TrnVendor;

Results:

VendId Name CurrBal
TV001 Wet Off Towels $13.13
TV007 Spot Out $1,606.54
TV010 The Soda Factory $0.00
TV011 The Freelance ($109.23)

Ordering Rows

Data is stored in Scalable SQL in no particular sequence. If you want to see your data displayed in sequence, you must add an Order By clause to your Select statement.

Syntax:

Select */fieldname<mask> …
from tablename
order by fieldname;

For example:

Retrieve the city, vendor Id, and name from the TrnVendor table. Order your data by city:

Select City, VendId, Name
from TrnVendor
order by City;

Results:

City VendId Name
Bayshore TV019 Bayshore Consulting
Burlington TV020 BayCon Group
Chicago TV003 BedMakers Linen

Displaying Rows in Descending Order

If you would like to see fields displayed in descending order, follow the field name with “desc” in the Order By clause.

Syntax:

Select */fieldname …
from tablename
order by fieldname <desc> …

For example:

Retrieve the city, vendor ID, and name from the TrnVendor table. Order your data by city in descending order:

Select City, VendId, Name 
from TrnVendor
order by City desc;

Results

City VendId Name
Youngstown TV018 Computer Bytes
Warren TV002 The Games All Here
Toledo TV027 Narrow Nest

Ordering Multiple Columns

When ordering your data, you can have multiple sort levels. For example, you can order your data by city and then by name within the city.

Syntax:

Select fieldname, fieldname, fieldname
from tablename
order by fieldname <desc>, fieldname <desc>, fieldname <desc>

For example:

Retrieve the city, name, and vendor ID from the TrnVendor table. Order your data by city and then by name within city:

Select City, Name, VendId
from TrnVendor
order by City, Name;

Results

City Name VendId
Bayshore Bayshore Consulting TV019
Burlington BayCon Group TV020
Chicago BedMakers Linen TV003
Chicago Distant Horizons TV028
Chicago Make Shift Tilts TV030

Retrieving Specific Rows

So far, you have been retrieving all of the rows in the table. You can, however, specify which rows you wish to retrieve. For example, you could retrieve only those vendors who are in Chicago.

Syntax:

Select */fieldname …
from tablename
where fieldname =/!=/<>/>/>=/</<=/in/not in/between/not between/begins with/contains/not contains/ is null/is not null/like/not/like value
order by fieldname <desc>.

Logical Operators
= Equal to
!= or <> Not equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
in Equal to any item in a list
not in Not equal to any item in a list
between Between two values,
greater than or equal to one and less than or equal to the other
not between Not between two values
begins with Begins with specified value
contains Contains specified value
not contains Does not contain specified value
is null Is blank
is not null Is not blank
like Like a specified pattern.
% means any series of characters.
_ means any single character.
not like Not like a specified pattern.
% means any series of characters.
_ means many single character.

For example:

Retrieve all vendors located in Chicago.

Select City, Name, VendId
from TrnVendor
where City = ‘Chicago’;’

Results:

City Name VendId
Chicago BedMakers Linen TV003
Chicago Music Maker TV016

For example 2:

Retrieve all vendors who are not located in Chicago. Order the results by city.

Select City, Name, VendId
from TrnVendor
where City <> ‘Chicago’
order by City;

Results:

City Name VendId
Bayshore Bayshore Consulting TV019
Burlington BayCon Group TV020
Dallas Cooperative Operatives TV021
Dallas Clampett Oil TV026

For example 3:

Retrieve all vendors in Mercer, New York, or Park Ridge.

Select City, Name, VendId
from TrnVendor
where City in ( ‘Mercer’, ‘New York’, ‘Park Ridge’)
order by City;

Results:

City Name VendId
Mercer Bed Room Furniture, Inc. TV004
Mercer The Freelance TV011
New York Paper People TV012
Park Ridge Mosquito No Bite TV006
Park Ridge No Waste Disposal TV015

For example 4:

Retrieve all vendors whose names begin with “Co.”

Select Name, City, VendId
from TrnVendor
where Name begins with ‘Co’
order by Name;

Results:

Name City VendId
Computer Bytes Youngstown TV018
Cooperative Operatives Dallas TV021

For example 5:

Retrieve all vendors whose city ends with the letters “do.”

Select Name, City, VendId
from TrnVendor
where City like ‘%do’
order by City;

Results:

Name City VendId
The Soda Factory Orlando TV010
Against the Tide Orlando TV025
Wet Off Towels Toledo TV001

For example 6:

Retrieve all vendors with a current balance between 500 and 1000 dollars.

Select VendId, Name, CurrBal [ZZ,ZZZ.99]
from TrnVendor
where CurrBal between 500 and 1000
order by CurrBal;

Results:

VendId Name

CurrBal

TV003 BedMakers Linen

500

TV012 Paper People

617

TV017 Food Four

642.98

Multiple Conditions

You can add multiple criteria to your Where clauses by using “and” or “or.”

Syntax:

Select */fieldname<mask> … 
from tablename … 
where fieldname =/!=/<> … value
and/or
fieldname =/!=/<>… value
and/or
fieldname =/!=/<> … value 
order by fieldname <desc> …

For example:

Retrieve all vendors who are located in Chicago and have a current balance over 1,500 dollars. Order the results by current balance.

Select Name, City, CurrBal [-ZZ,ZZZ.99]
from TrnVendor
where City = ‘Chicago’
and CurrBal > 1500
order by CurrBal;

Results:

Name City CurrBal
Make Shift Tilts Chicago 1,793.23
Music Maker Chicago 4,109.14

For example 2:

Retrieve all vendors whose expense account is 4110 or who have a current balance over 1200. The vendor must be located in Chicago.

Select City, CurrBal [-ZZZ,ZZZ.99], ExpAcct
from Vendor
where City = ‘Chicago’
and (ExpAcct = ‘4110’
or CurrBal > 1200) 
order by City, ExpAcct;

Results:

City CurrBal ExpAcct
Chicago 0.00 4030
Chicago 4,135.24 4110
Chicago 1,309.95 4110
Chicago 100.00 6010
Chicago 500.00 6040
Chicago 47.00 7010
Chicago 106.00 7190

Joining Tables

So far, you have worked exclusively with a single table — the TrnVendor table. You might have noticed that each module within Solomon IV consists of several tables. Within the Accounts Payable module, you will find the following tables: APAdjust, APDoc, APHist, APRefNbr, APSetup, APTran, and Vendor. Each table contains specific information. At times, you might need to join two or more tables to retrieve the information you need. For example, if you look at the TrnAPDoc table, you will see that it contains the vendor ID; however, it does not contain the vendor name. If, when retrieving information from the APDoc table, you want to see the vendor name, you have to join the TrnVendor table to the TrnAPDoc table.

Select */tablename.fieldname<mask> …
from tablename <alias>, tablename <alias>, tablename <alias>
where tablename.fieldname = tablename.fieldname
and
tablename.fieldname = tablename.fieldname
and
tablename.fieldname = tablename.fieldname
order by fieldname <desc>…

For example:

Retrieve the vendor ID, the vendor name, and the original document amount from the TrnAPDoc and TrnVendor tables.

Select TrnAPDoc.VendId, TrnVendor.Name,
TrnAPDoc.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc, TrnVendor
where TrnAPDoc.VendId = TrnVendor.VendId;

Results:

TrnAPDoc.VendId TrnVendor.Name TrnAPDoc.OrigDocAmt
TV020 BayCon Group 542.98
TV019 Bayshore Consulting 237.60
TV018 Computer Bytes 55.50

For example 2:

Retrieve the vendor ID, vendor name, and the original document amount from the TrnAPDoc and TrnVendor tables, using a table alias.

Select a.VendId, b.Name,
a.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc a, TrnVendor b
where a.VendId = b.VendId;

Results:

a.VendId b.Name a.OrigDocAmt
TV020 BayCon Group 542.98
TV019 Bayshore Consulting 237.60
TV018 Computer Bytes 55.50

For example 3:

Retrieve the vendor ID, vendor name, reference number, and original document amount from the APDoc and Vendor tables for Vendor V00104. Order the results by RefNbr.

Select TrnAPDoc.VendId, TrnVendor.Name, TrnAPDoc.RefNbr,
TrnAPDoc.OrigDocAmt [-ZZZZZZ.99]
from TrnAPDoc, TrnVendor
where TrnAPDoc.VendId = TrnVendor.VendId
and
TrnAPdoc.VendId = ‘TV004’
order by TrnAPDoc.RefNbr;

Results:

TrnAPDoc.VendId TrnVendor.Name TrnAPDoc.RefNbr TrnAPDoc.OrigDocAmt
TV004 Bed Room Furniture, Inc. 000222 55.50
TV004 Bed Room Furniture, Inc. 000551 3600.00

Updating Tables

So far, you have looked at several different ways to retrieve and review your data. In this section, you will learn how to update your data. In the following two sections, you will learn about deleting and inserting rows. When you update, delete, and insert, you change the data — you should perform these operations very cautiously. Before performing any of these operations on a production database, make sure your data is backed up and use the Start Transaction command. If you use the Start Transaction command, all of your changes are temporary until you commit your work and can be rolled back . If you have issued the Start Transaction command, you can undo your changes simply by typing “rollback work.”

Syntax:

Start transaction;

Update tablename
set fieldname = value
where fieldname = value;

Rollback work;

Commit work;

Explanation:

  • Issue a Start Transaction command before updating your table. This will allow you to roll back the changes, if necessary. If you do not issue a Start Transaction command, you will not be able the roll back your work.
  • If you find that you have updated a row in error, execute the Rollback Work command.
  • When you are satisfied with your changes, issue the Commit Work command.
  • Use a Where clause to specify which rows will be updated. If you do not include a Where clause, all rows will be updated.
  • Remember to end each command with a semicolon.

Example:

  1. Change the name of vendor “TV001” to Genie R. Corp., and then roll back the change. Then change the name of vendor “TV001” to Vanix and commit your work. To start the transaction, type:

Start transaction;

  1. Note the current vendor name, and type:

Select VendId, Name
from TrnVendor 
where VendId = ‘TV001’;

  1. To update the vendor name, type:

Update TrnVendor
Set Name = ‘Genie R Corp.’
Where VendId = ‘TV001’;

  1. To check the vendor name to see that it has changed, type:

Select VendId, Name
from TrnVendor 
where VendId = ‘TV001’;

  1. To roll back the change, type:

Rollback work;

  1. To check the vendor name to see that it has reverted to the original, type:

Select VendId, Name
from TrnVendor 
where VendId = ‘TV001’;

  1. To update the vendor name, type:

Update TrnVendor
Set Name = ‘Vanix’
Where VendId = ‘TV001’;

  1. To check the vendor name to see that it has changed, type:

Select VendId, Name
from TrnVendor 
where VendId = ‘TV001’;

  1. To commit the change, type:

Commit work;

Deleting Rows

You can use Scalable SQL to delete rows of data.

Syntax:

Delete from tablename
where fieldname =/<>/ … value
and/or … 
fieldname =/<>/ … value
and/or
fieldname =/<>/ … value

xplanation:

  • If you do not include a Where clause, all of the rows in the table will be deleted.
  • Every table has a primary key — a field or combination of fields that uniquely identify each row in the table. VendId is the primary key for the vendor table. Each vendor is uniquely identified by the vendor Id. RefNbr is the primary key for APDoc.
  • If you want to delete a single row of data, you can refer to the row in the Where clause by using the primary key.
  • When deleting data, use the Start Transaction command so that any errors can be rolled back.
  • Use the Rollback Work command to undo changes.
  • Use the Commit Work command to finalize changes.

Examples:

Delete vendor TV011 by executing the following commands.

  1. To view the record, type:

Select *
from TrnVendor
where VendId = ‘TV011’;

  1. To start the transaction, type:

Start transaction;

  1. To delete the record, type:

Delete from TrnVendor
where VendId = ‘TV011 ‘;

  1. To check to make sure the records have been deleted, type:

Select *
from TrnVendor
where VendId = ‘TV011’;

  1. To roll back the deletion, type:

Rollback work;

Delete all vendors with a current balance over 2000 dollars.

  1. To view the records, type:

Select Name, CurrBal [-ZZZ,ZZZ.99]
from TrnVendor
where CurrBal > 2000;

  1. To start the transaction, type:

Start transaction;

  1. To delete the records, type:

Delete from TrnVendor
where CurrBal > 2000;

  1. To check to make sure the records have been deleted, type:

Select Name, CurrBal [-ZZZ,ZZZ.99]
from TrnVendor
where CurrBal > 2000;

  1. To roll back the deletion, type:

Rollback work;

Inserting Rows

You can insert new rows into a table by using Scalable SQL.

Syntax:

Insert into tablename ( fieldname, fieldname, fieldname)
values ( value, value, value);

Explanation:

  • When inserting data, use the Start Transaction command so that any errors can be rolled back.
  • You must specify the values to be inserted.
  • When performing an insert, enclose character values in single quotes.
  • Do not enclose numeric values in single quotes.
  • Use the Rollback Work command to undo changes.
  • Use the Commit Work command to finalize changes.

Example:

Insert the following vendor into the TrnVendor table.

Vendor Id: TV055
Name: Party Games
Address: PO Box 136
City: Chicago
State: IL
Current Balance: 2498.62

  1. To start the transaction, type:

Start transaction;

  1. To insert the records, type:

Insert into TrnVendor (VendId, Name, Addr1, City, State, 
CurrBal )
values ( TV055′, ‘Party Games’, ‘PO Box 136’, ‘Chicago’, ‘IL’, 2498.62);

  1. To view the record, type:

Select VendId, Name, Addr1, City, State, CurrBal [-ZZZ,ZZZ.99]
from TrnVendor
where VendId = ‘TV055’;

  1. To roll back your work, type:

Rollback work;

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

For example:

The following SQL creates a PRIMARY KEY on the “P_Id” column when the “Persons” table is created:

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

In MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName).

To create a PRIMARY KEY constraint on the “P_Id” column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

To DROP a PRIMARY KEY Constraint

In SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

In MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let’s illustrate the foreign key with an example. Look at the following two tables:

The “Persons” table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

The “Orders” table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1

Note that the “P_Id” column in the “Orders” table points to the “P_Id” column in the “Persons” table.

The “P_Id” column in the “Persons” table is the PRIMARY KEY in the “Persons” table.

The “P_Id” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

SQL FOREIGN KEY Constraint on CREATE TABLE

In SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

In MySQL:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the “P_Id” column when the “Orders” table is already created, use the following SQL:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

In SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

In MYSQL:

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SELECT DISTINCT column_name(s)
FROM table_name

SELECT DISTINCT Example

The “Persons” table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

Now we want to select only the distinct values from the column named “City” from the table above.

We use the following SELECT statement:

SELECT DISTINCT City FROM Persons

The result-set will look like this:

City
Sandnes
Stavanger

SQL VIEWS

A view is a virtual table.

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Note: A view always shows up-to-date data! The database engine recreates the data, using the view’s SQL statement, every time a user queries a view.

SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has several views installed by default.

The view “Current Product List” lists all active products (products that are not discontinued) from the “Products” table. The view is created with the following SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

We can query the view above as follows:

SELECT * FROM [Current Product List]

Another view in the Northwind sample database selects every product in the “Products” table with a unit price higher than the average unit price:

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

We can query the view above as follows:

SELECT * FROM [Products Above Average Price]

Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called “Product Sales for 1997”:

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

We can query the view above as follows:

SELECT * FROM [Category Sales For 1997]

We can also add a condition to the query. Now we want to see the total sale only for the category “Beverages”:

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName=’Beverages’

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Now we want to add the “Category” column to the “Current Product List” view. We will update the view with the following SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No


SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name

SQL Functions

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

  • AVG() – Returns the average value
  • COUNT() – Returns the number of rows
  • FIRST() – Returns the first value
  • LAST() – Returns the last value
  • MAX() – Returns the largest value
  • MIN() – Returns the smallest value
  • SUM() – Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

  • UCASE() – Converts a field to upper case
  • LCASE() – Converts a field to lower case
  • MID() – Extract characters from a text field
  • LEN() – Returns the length of a text field
  • ROUND() – Rounds a numeric field to the number of decimals specified
  • NOW() – Returns the current system date and time
  • FORMAT() – Formats how a field is to be displayed

Tip: The aggregate functions and the scalar functions will be explained in details in the next chapters.

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name

SQL AVG() Example

We have the following “Orders” table:

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Now we want to find the average value of the “OrderPrice” fields.

We use the following SQL statement:

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

The result-set will look like this:

OrderAverage
950

Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.

We use the following SQL statement:

SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

The result-set will look like this:

Customer
Hansen
Nilsen
Jensen

SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria.


SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name

Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.


SQL COUNT(column_name) Example

We have the following “Orders” table:

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Now we want to count the number of orders from “Customer Nilsen”.

We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer=’Nilsen’

The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:

CustomerNilsen
2

SQL COUNT(*) Example

If we omit the WHERE clause, like this:

SELECT COUNT(*) AS NumberOfOrders FROM Orders

The result-set will look like this:

NumberOfOrders
6

which is the total number of rows in the table.


SQL COUNT(DISTINCT column_name) Example

Now we want to count the number of unique customers in the “Orders” table.

We use the following SQL statement:

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The result-set will look like this:

NumberOfCustomers
3

which is the number of unique customers (Hansen, Nilsen, and Jensen) in the “Orders” table.

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SQL GROUP BY Example

We have the following “Orders” table:

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Now we want to find the total sum (total order) of each customer.

We will have to use the GROUP BY statement to group the customers.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

The result-set will look like this:

Customer SUM(OrderPrice)
Hansen 2000
Nilsen 1700
Jensen 2000

Nice! Isn’t it? 🙂

Let’s see what happens if we omit the GROUP BY statement:

SELECT Customer,SUM(OrderPrice) FROM Orders

The result-set will look like this:

Customer SUM(OrderPrice)
Hansen 5700
Nilsen 5700
Hansen 5700
Hansen 5700
Jensen 5700
Nilsen 5700

The result-set above is not what we wanted.

Explanation of why the above SELECT statement cannot be used: The SELECT statement above has two columns specified (Customer and SUM(OrderPrice). The “SUM(OrderPrice)” returns a single value (that is the total sum of the “OrderPrice” column), while “Customer” returns 6 values (one value for each row in the “Orders” table). This will therefore not give us the correct result. However, you have seen that the GROUP BY statement solves this problem.


GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value


SQL HAVING Example

We have the following “Orders” table:

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Now we want to find if any of the customers have a total order of less than 2000.

We use the following SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

The result-set will look like this:

Customer SUM(OrderPrice)
Nilsen 1700

Now we want to find if the customers “Hansen” or “Jensen” have a total order of more than 1500.

We add an ordinary WHERE clause to the SQL statement:

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer=’Hansen’ OR Customer=’Jensen’
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

The result-set will look like this:

Customer SUM(OrderPrice)
Hansen 2000
Jensen 2000