Cart

Employees Database Sample

Table of Contents

All our products include SQL scripts and database files for the Employees sample, for all supported database types. They are installed by default in the C:/Program Files (x86)/XtractPro/<app>/samples setup folder, and replicated to your C:/Users/<username>/AppData/Local/Xtractor/samples user directory.

EmployeesQX is a very small database, inspired from a sample used by Oracle for many years. We use it internally for testing, but we also included all setup SQL scripts in our product distributions. The script files can be also downloaded from GitHub.

Employees ER Data Models

Several ER model diagrams are automatically generated once you establish a connection with any database. For EmployeesQX, here is a modified Detailed Model:

EmployeesQX Detailed Model

  • dept – table with departments and one-to-many relationship to employees.
  • emp – database table with employees and reflexive relationship to the manager.
  • proj – database table with employee projects.
  • datatypes – specific database table for each RDBMS, to test supported data types.
  • managers – database view with each employee’s manager’s name.

Employees Queries and Charts

In Data Xtractor, Visual Xtractor or Query Xtractor, expand the Queries – Demo Queries folder, and click on add demo queries. A popup opens up with all sorts of demo queries that can be automatically generated for you. Just get them all.

  • Basic queries are generic, with on-the-fly data generated with UNION operators.
  • Inline Charts are also generic, for charts painted in grid’s cells.
  • Charts are also generic, for real-time analytics with regular charts.

Double-click now on any of these demo queries. They should immediately run and return data. Click on the large Design button to switch to the visual SQL query builder and the generated SQL query. For instance, here is the Trigonometric Functions demo query with the visual SQL query builder and the database-specific generated SQL:

MySQL-Chinook-Query-Design

And here is a generic demo with Spark Charts, as inline charts:

Spark Charts Demo

Click on the large SQL toolbar button to see the database-specific generated SQL query, here for Oracle. All data is generated on-the-fly in a subquery, from SELECT-UNION chains:

WITH fruitsu_ AS (
SELECT	1 AS "id", 'Apples' AS "name", 2.0 AS "unit_price",
  22 AS "quantity", 3 AS "depends_on" FROM	dual
UNION ALL SELECT 2, 'Oranges', 1.9, 5, 8 FROM dual
UNION ALL SELECT 3, 'Plums', 4, 0, NULL FROM dual
UNION ALL SELECT 4, 'Lemons', 0.88, 8, 7 FROM dual
UNION ALL SELECT 5, 'Grapes', 1.22, 0, 1 FROM dual
UNION ALL SELECT 6, 'Melons', 1.67, 14, 10 FROM dual
UNION ALL SELECT 7, 'Apricots', 2.3, 8, 8 FROM dual
UNION ALL SELECT 8, 'Bananas', 3, 14, 1 FROM dual
UNION ALL SELECT 9, 'Cherries', 0.20, 14, 3 FROM dual
UNION ALL SELECT 10, 'Clementines', 2.4, 1, 1 FROM dual
ORDER BY 1)

SELECT fruitsu_."name" AS "name", fruitsu_."quantity" AS "q1",
   fruitsu_."quantity" - 7 AS "q2", fruitsu_."quantity" * 2 AS "q3",
   '' AS "spark line", '' AS "spark area",
   '' AS "spark columns", '' AS "spark win/loss",
   '' AS "spark pie"
FROM fruitsu_

How to Install the Employees Database

For any supported database type, here are short instructions on how you can use a free third-party application, to install and connect to an EmployeeQX sample database from our applications. Each database type has a link to the instructions for a general connection from our apps.

  • SQLite – Employees – SQLite.db is a ready-to-use database file created from the Employees – SQLite.sql script. This sample may be connected by default in a new project.
  • Firebird – Employees – Firebird.fdb is a ready-to-use database file created from the Employees – Firebird.sql script. Use localhost and default port 3050. Connect with admin’s sysdba username, and masterkey password. This sample may be connected by default in a new project.
  • Microsoft SQL Server CE – there is an Employees – Microsoft SQL Server CE.sdf ready-to-use database file, created from the Employees – Microsoft SQL Server CE.sql script. Just open the SDF file directly, with nothing else. No password required. As access to SQL CE is always free, we may have this database sample installed by default when you create a new empty project.
  • Microsoft Access – there is an Employees – Microsoft Access.accdb and an Employees – Microsoft Access.mdb file in the samples directory. Connect from Access or any Xtractor app, with no password. There are no SQL scripts for MS Access.
  • Snowflake – load and run the EmployeesQX – Snowflake.sql script from the Snowflake web UI. This will create an EmployeesQX database with tables and other objects, and populate all the tables. Connect with either a username-password, SSO, OAuth or Key-Pair. Use the EmployeesQX database with the default PUBLIC schema. Can optionally set the role to ACCOUNTADMIN and the warehouse to COMPUTE_WH.
  • MySQL – load and run the Employees – MySQL.sql script from MySQL Workbench or other free database manager. Use localhost and default port 3306 (if MariaDB is not using already the same number). Connect with admin’s root username, and a saved password. Use EmployeesQX for both database and schema.
  • MariaDB – load and run the Employees – MariaDB.sql script from HeidiSQL or other free database manager. Use localhost and default port 3306 (if MySQL is not using already the same number). Connect with admin’s root username, and a saved password. Use EmployeesQX for both database and schema.
  • Amazon Aurora – create an Aurora database in AWS (Amazon Web Services), with remote access. Connect to the database, then load and run the Employees – Amazon Aurora.sql script from MySQL Workbench or other free database manager.
  • PostgreSQL – from pgAdmin or other free database manager, create a new EmployeesQX database connecting with admin’s postgres username. Load and run the Employees – PostgreSQL.sql script. Use localhost and default port 5432 to connect to the database.
  • Amazon Redshift – create a Redshift cluster and database in AWS (Amazon Web Services), with remote access. Connect to the database, then load and run the Employees – Amazon Redshift.sql script from SQL Workbench or other free database manager.
  • Microsoft SQL Server – load and run the Employees – Microsoft SQL Server.sql script from SSMS (SQL Server Management Studio) or other free database manager.
  • Microsoft Azure SQL Database – just like for SQL Server, load and run the Employees – Microsoft SQL Azure.sql script from SSMS (SQL Server Management Studio) or other free database manager.
  • Oracle – load and run the Employees – Oracle.sql script from SQL Developer or other free database manager. Create an EmployeesQX schema, use EmployeesQX for all the installation scripts, and connect with this username.
  • IBM DB2 – load and run the Employees – IBM Db2.sql script from SQL Workbench, DBeaver or other free database manager.
  • Sybase/SAP ASE – load and run the Employees – SAP ASE.sql script from I-SQL, DBeaver or other free database manager.
  • Sybase/SAP SQL Anywhere – open directly the ready-to-use Employees – Sybase SAP SQL Anywhere.db database file for version 16, or the Employees – Sybase SAP SQL Anywhere 17.db for version 17, prepared from the Employees – Sybase SAP SQL Anywhere.sql script. Use the Employees – Sybase SAP SQL Anywhere service and database names. Connect with admin’s DBA username and sys password.