SQL Sample Database

SQL Sample Database

Summary: in this tutorial, you will learn about a SQL Sample Database called HR that manages the HR data of the small businesses.

The following database diagram illustrates the HR sample database:

SQL Sample Database Schema

The HR sample database has seven tables:

  1. The employees table stores the data of employees.
  2. The jobs table stores the job data including job title and salary range.
  3. The departments table stores department data.
  4. The dependents table stores the employee’s dependents.
  5. The locations table stores the location of the departments of the company.
  6. The countries table stores the data of countries where the company is doing business.
  7. The regions table stores the data of regions such as Asia, Europe, America, and the Middle East and Africa. The countries are grouped into regions.

The following picture shows the table names and their records.

TableRows
employees40
dependents30
departments11
jobs11
locations7
countries25
regions4

Typically, you need to install a Relational Database Management System (RDBMS) to work with SQL.

If you have worked with an RDBMS such as MySQL, PostgreSQL, Oracle Database, and SQL Server, you can use the following script to create the sample database in one of these databases.

In case you don’t have a database system to practice, you can quickly use our SQL online tool to execute the SQL statements in your web browser.

MySQL

The following SQL script creates the HR sample database in MySQL:

Create HR Sample Database in MySQL

The following script allows you to insert data into the tables in MySQL:

Load HR Data in MySQL

PostgreSQL

The following script creates the HR sample database structure in PostgreSQL.

Create HR Sample Database in PostgreSQL

The following script allows you to insert data into the tables in PostgreSQL:

Load HR Data in PostgreSQL

Microsoft SQL Server

The following script creates the HR sample database structure in Microsoft SQL Server.

Create HR Sample Database in SQL Server

The following script allows you to insert data into the tables:

Load HR Data in SQL Server

Oracle Database (>12c)

The following script creates the HR sample database structure in Oracle Database 12c.

Create HR Sample Database in Oracle

The following script inserts data into the tables in the Oracle database:

Load HR Data in Oracle Database

SQLite

The following script creates the HR sample database structure in SQLite.

Create HR Sample Database in SQLite

The following script inserts data into the tables in the SQLite:

Create HR Sample Database in SQLite

Removing tables

The following is the script that drops all tables in case you want to refresh the sample database.

Drop All Tables

Popular posts from this blog

SQL Syntax

Image
  SQL Syntax Summary : in this tutorial, we will introduce you to the SQL syntax that helps you understand the details of SQL statements. SQL is a declarative language, therefore, its syntax reads like a natural language. An SQL statement begins with a verb that describes the action, for example, SELECT, INSERT, UPDATE or DELETE. Following the verb are the subject and predicate. A predicate specifies conditions that can be evaluated as true, false, or unknown. See the following SQL statement: SELECT first_name FROM employees WHERE YEAR (hire_date) = 2000 ; Code language: SQL (Structured Query Language) ( sql ) As you see, it reads like a normal sentence. Get the first names of employees who were hired in 2000. The  SELECT first_name ,  FROM employees , and WHERE  are clauses in the SQL statement. Some clauses are mandatory e.g., the  SELECT  and  FROM  clauses whereas others are optional such as the  WHERE  clause. Because SQL was designed specifically for the non-tec
Read more

SQL FETCH

Image
Skip to content SQL FETCH Summary : in this tutorial, you will learn how to use the SQL   FETCH   clause to limit the number of rows returned by a query. Introduction to SQL FETCH clause To limit the number of rows returned by a query, you use the  LIMIT  clause. The  LIMIT  clause is widely supported by many database systems such as MySQL, H2, and HSQLDB. However, the  LIMIT  clause is not in SQL standard. SQL:2008 introduced the  OFFSET FETCH  clause which has a similar function to the  LIMIT  clause. The  OFFSET FETCH  clause allows you to skip  N  first rows in a result set before starting to return any rows. The following shows the syntax of the SQL  FETCH  clause: OFFSET offset_rows { ROW | ROWS } FETCH { FIRST | NEXT } [ fetch_rows ] { ROW | ROWS } ONLY Code language: SQL (Structured Query Language) ( sql ) In this syntax: The  ROW  and  ROWS ,  FIRST  and  NEXT  are the synonyms. Therefore, you can use them interchangeably. The  offset_rows  is an integer number which must be
Read more

SQL LIMIT

Image
Skip to SQL LIMIT Summary : in this tutorial, you’ll learn to use the SQL  LIMIT  clause to limit the number of rows returned from a query. Introduction to SQL LIMIT clause To limit the number of rows returned by a  select  statement, you use the  LIMIT  and  OFFSET  clauses. The following shows the syntax of  LIMIT  &  OFFSET  clauses: SELECT column_list FROM table1 ORDER BY column_list LIMIT row_count OFFSET offset ; Code language: SQL (Structured Query Language) ( sql ) In this syntax: The  LIMIT row_count  determines the number of rows ( row_count ) returned by the query. The  OFFSET offset  clause skips the  offset  rows before beginning to return the rows. The  OFFSET  clause is optional. If you omit it, the query will return the row_count rows from the first row returned by the  SELECT  clause. When you use the  LIMIT  clause, it is important to use an  ORDER BY  clause to ensure the order of rows in the result set. Not all database systems support the  LIM
Read more