Are there sample tables on sqlfiddle

Asked 20/1, 2016 at 13:22 Answered 11/1, 2021 at 15:51

Are there any default tables on SqlFiddle that I can query from?

I want to try a basic analytical query on a simple table but I don't want to set up the schema and seed data etc.

normally I would do something like select * from all_objects

Detect answered 20/1, 2016 at 13:22 Comment(1)

Did you find an asnwer? Both of those posted look good to me. If you accept an answer it will help other how view this question in future. – Reversal 3/3, 2017 at 8:39

Auto Shop Database:
SQL fiddle
Library Database:
SQL fiddle
Countries Table:
SQL fiddle

Drover answered 20/8, 2016 at 5:43 Comment(0)

You can use the "View Sample Fiddle" in the SQLFiddle app.

Corpus answered 25/2, 2016 at 3:29 Comment(0)

The following content is from "Auto Shop Database" from Stack Overflow Documentation (archived here); copyright 2017 by FlyingPiMonster, Prateek, forsvarir, Tot Zam, Florin Ghita, Abhilash R Vankayala, WesleyJohnson, Matt, Mureinik, Magisch, Bostjan, Mzzzzzz, Franck Dernoncourt, enrico.bacis, JavaHopper, rdans, bignose, and CL.; licensed under CC BY-SA 3.0. An archive of the full Stack Overflow Documentation content can be found at archive.org, in which this example is indexed by its topic ID: 280, as example: 1014.

Auto Shop Database

In the following example - Database for an auto shop business, we have a list of departments, employees, customers and customer cars. We are using foreign keys to create relationships between the various tables.

Live example: SQL fiddle

Relationships between tables

Each Department may have 0 or more Employees
Each Employee may have 0 or 1 Manager
Each Customer may have 0 or more Cars

Departments

| Id | Name  |
|:---|:------|
| 1  | HR    |
| 2  | Sales |
| 3  | Tech  |

SQL statements to create the table:

CREATE TABLE Departments (
    Id INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(25) NOT NULL,
    PRIMARY KEY(Id)
);

INSERT INTO Departments
    ([Id], [Name])
VALUES
    (1, 'HR'),
    (2, 'Sales'),
    (3, 'Tech')
;

Employees

| Id | FName     | LName    | PhoneNumber | ManagerId | DepartmentId | Salary | HireDate   |
|:---|:----------|:---------|:------------|:----------|:-------------|:-------|:-----------|
| 1  | James     | Smith    | 1234567890  | NULL      | 1            | 1000   | 01-01-2002 |
| 2  | John      | Johnson  | 2468101214  | 1         | 1            | 400    | 23-03-2005 |
| 3  | Michael   | Williams | 1357911131  | 1         | 2            | 600    | 12-05-2009 |
| 4  | Johnathon | Smith    | 1212121212  | 2         | 1            | 500    | 24-07-2016 |

SQL statements to create the table:

CREATE TABLE Employees (
    Id INT NOT NULL AUTO_INCREMENT,
    FName VARCHAR(35) NOT NULL,
    LName VARCHAR(35) NOT NULL,
    PhoneNumber VARCHAR(11),
    ManagerId INT,
    DepartmentId INT NOT NULL,
    Salary INT NOT NULL,
    HireDate DATETIME NOT NULL,
    PRIMARY KEY(Id),
    FOREIGN KEY (ManagerId) REFERENCES Employees(Id),
    FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);

INSERT INTO Employees
    ([Id], [FName], [LName], [PhoneNumber], [ManagerId], [DepartmentId], [Salary], [HireDate])
VALUES
    (1, 'James', 'Smith', 1234567890, NULL, 1, 1000, '01-01-2002'),
    (2, 'John', 'Johnson', 2468101214, '1', 1, 400, '23-03-2005'),
    (3, 'Michael', 'Williams', 1357911131, '1', 2, 600, '12-05-2009'),
    (4, 'Johnathon', 'Smith', 1212121212, '2', 1, 500, '24-07-2016')
;

Customers

| Id | FName   | LName  | Email                     | PhoneNumber | PreferredContact |
|:---|:--------|:-------|:--------------------------|:------------|:-----------------|
| 1  | William | Jones  | [email protected] | 3347927472  | PHONE            |
| 2  | David   | Miller | [email protected]       | 2137921892  | EMAIL            |
| 3  | Richard | Davis  | [email protected]   | NULL        | EMAIL            |

SQL statements to create the table:

CREATE TABLE Customers (
    Id INT NOT NULL AUTO_INCREMENT,
    FName VARCHAR(35) NOT NULL,
    LName VARCHAR(35) NOT NULL,
    Email varchar(100) NOT NULL,
    PhoneNumber VARCHAR(11),
    PreferredContact VARCHAR(5) NOT NULL,
    PRIMARY KEY(Id)
);

INSERT INTO Customers
    ([Id], [FName], [LName], [Email], [PhoneNumber], [PreferredContact])
VALUES
    (1, 'William', 'Jones', '[email protected]', '3347927472', 'PHONE'),
    (2, 'David', 'Miller', '[email protected]', '2137921892', 'EMAIL'),
    (3, 'Richard', 'Davis', '[email protected]', NULL, 'EMAIL')
;

Cars

| Id | CustomerId | EmployeeId | Model        | Status  | Total Cost |
|:---|:-----------|:-----------|:-------------|:--------|:-----------|
| 1  | 1          | 2          | Ford F-150   | READY   | 230        |
| 2  | 1          | 2          | Ford F-150   | READY   | 200        |
| 3  | 2          | 1          | Ford Mustang | WAITING | 100        |
| 4  | 3          | 3          | Toyota Prius | WORKING | 1254       |

SQL statements to create the table:

CREATE TABLE Cars (
    Id INT NOT NULL AUTO_INCREMENT,
    CustomerId INT NOT NULL,
    EmployeeId INT NOT NULL,
    Model varchar(50) NOT NULL,
    Status varchar(25) NOT NULL,
    TotalCost INT NOT NULL,
    PRIMARY KEY(Id),
    FOREIGN KEY (CustomerId) REFERENCES Customers(Id),
    FOREIGN KEY (EmployeeId) REFERENCES Employees(Id)
);

INSERT INTO Cars
    ([Id], [CustomerId], [EmployeeId], [Model], [Status], [TotalCost])
VALUES
    ('1', '1', '2', 'Ford F-150', 'READY', '230'),
    ('2', '1', '2', 'Ford F-150', 'READY', '200'),
    ('3', '2', '1', 'Ford Mustang', 'WAITING', '100'),
    ('4', '3', '3', 'Toyota Prius', 'WORKING', '1254')
;

Legitimatize answered 20/1, 2016 at 13:22 Comment(0)

The following content is from "Library Database" from Stack Overflow Documentation (archived here); copyright 2017 by enrico.bacis, Bostjan, Shiva, WesleyJohnson, and Christian; licensed under CC BY-SA 3.0. An archive of the full Stack Overflow Documentation content can be found at archive.org, in which this example is indexed by its topic ID: 280, as example: 1014.

Library Database

In this example database for a library, we have Authors, Books and BooksAuthors tables.

Live example: SQL fiddle

Authors and Books are known as base tables, since they contain column definition and data for the actual entities in the relational model. BooksAuthors is known as the relationship table, since this table defines the relationship between the Books and Authors table.

Relationships between tables

Each author can have 1 or more books
Each book can have 1 or more authors

Authors

(view table)

| Id | Name                 | Country |
|:---|:---------------------|:--------|
| 1  | J.D. Salinger        | USA     |
| 2  | F. Scott. Fitzgerald | USA     |
| 3  | Jane Austen          | UK      |
| 4  | Scott Hanselman      | USA     |
| 5  | Jason N. Gaylord     | USA     |
| 6  | Pranav Rastogi       | India   |
| 7  | Todd Miranda         | USA     |
| 8  | Christian Wenz       | USA     |

SQL to create the table:

CREATE TABLE Authors (
    Id INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(70) NOT NULL,
    Country VARCHAR(100) NOT NULL,
    PRIMARY KEY(Id)
);

INSERT INTO Authors
    (Name, Country)
VALUES
    ('J.D. Salinger', 'USA'),
    ('F. Scott. Fitzgerald', 'USA'),
    ('Jane Austen', 'UK'),
    ('Scott Hanselman', 'USA'),
    ('Jason N. Gaylord', 'USA'),
    ('Pranav Rastogi', 'India'),
    ('Todd Miranda', 'USA'),
    ('Christian Wenz', 'USA')
;

Books

(view table)

| Id | Title | |:---|:--------------------------------------| | 1 | The Catcher in the Rye | | 2 | Nine Stories | | 3 | Franny and Zooey | | 4 | The Great Gatsby | | 5 | Tender id the Night | | 6 | Pride and Prejudice | | 7 | Professional ASP.NET 4.5 in C# and VB |

SQL to create the table:

CREATE TABLE Books (
    Id INT NOT NULL AUTO_INCREMENT,
    Title VARCHAR(50) NOT NULL,
    PRIMARY KEY(Id)
);

INSERT INTO Books
    (Id, Title)
VALUES
    (1, 'The Catcher in the Rye'),
    (2, 'Nine Stories'),
    (3, 'Franny and Zooey'),
    (4, 'The Great Gatsby'),
    (5, 'Tender id the Night'),
    (6, 'Pride and Prejudice'),
    (7, 'Professional ASP.NET 4.5 in C# and VB')
;

BooksAuthors

(view table)

| BookId | AuthorId | |:-------|:---------| | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | | 6 | 3 | | 7 | 4 | | 7 | 5 | | 7 | 6 | | 7 | 7 | | 7 | 8 |

SQL to create the table:

CREATE TABLE BooksAuthors (
    AuthorId INT NOT NULL,
    BookId  INT NOT NULL,
    FOREIGN KEY (AuthorId) REFERENCES Authors(Id),
    FOREIGN KEY (BookId) REFERENCES Books(Id)
);

INSERT INTO BooksAuthors
    (BookId, AuthorId)
VALUES
    (1, 1),
    (2, 1),
    (3, 1),
    (4, 2),
    (5, 2),
    (6, 3),
    (7, 4),
    (7, 5),
    (7, 6),
    (7, 7),
    (7, 8)
;

Examples

View all authors (view live example):

SELECT * FROM Authors;

View all book titles (view live example):

SELECT * FROM Books;

View all books and their authors (view live example):

SELECT
  ba.AuthorId,
  a.Name AuthorName,
  ba.BookId,
  b.Title BookTitle
FROM BooksAuthors ba
  INNER JOIN Authors a ON a.id = ba.authorid
  INNER JOIN Books b ON b.id = ba.bookid
;

Legitimatize answered 20/1, 2016 at 13:22 Comment(0)

The following content is from "Countries Table" from Stack Overflow Documentation (archived here); copyright 2017 by enrico.bacis, Bostjan, and Shiva; licensed under CC BY-SA 3.0. An archive of the full Stack Overflow Documentation content can be found at archive.org, in which this example is indexed by its topic ID: 280, as example: 9933.

Countries Table

In this example, we have a Countries table. A table for countries has many uses, especially in Financial applications involving currencies and exchange rates.

Live example: SQL fiddle

Some Market data software applications like Bloomberg and Reuters require you to give their API either a 2 or 3 character country code along with the currency code. Hence this example table has both the 2-character ISO code column and the 3 character ISO3 code columns.

Countries

(view table)

| Id | ISO | ISO3 | ISONumeric | CountryName   | Capital    | ContinentCode | CurrencyCode |
|:---|:----|:-----|:-----------|:--------------|:-----------|:--------------|:-------------|
| 1  | AU  | AUS  | 36         | Australia     | Canberra   | OC            | AUD          |
| 2  | DE  | DEU  | 276        | Germany       | Berlin     | EU            | EUR          |
| 2  | IN  | IND  | 356        | India         | New Delhi  | AS            | INR          |
| 3  | LA  | LAO  | 418        | Laos          | Vientiane  | AS            | LAK          |
| 4  | US  | USA  | 840        | United States | Washington | NA            | USD          |
| 5  | ZW  | ZWE  | 716        | Zimbabwe      | Harare     | AF            | ZWL          |

SQL to create the table:

CREATE TABLE Countries (
    Id INT NOT NULL AUTO_INCREMENT,
    ISO VARCHAR(2) NOT NULL,
    ISO3 VARCHAR(3) NOT NULL,
    ISONumeric INT NOT NULL,
    CountryName VARCHAR(64) NOT NULL,
    Capital VARCHAR(64) NOT NULL,
    ContinentCode VARCHAR(2) NOT NULL,
    CurrencyCode VARCHAR(3) NOT NULL,
    PRIMARY KEY(Id)
)
;

INSERT INTO Countries
    (ISO, ISO3, ISONumeric, CountryName, Capital, ContinentCode, CurrencyCode)
VALUES
    ('AU', 'AUS', 36, 'Australia', 'Canberra', 'OC', 'AUD'),
    ('DE', 'DEU', 276, 'Germany', 'Berlin', 'EU', 'EUR'),
    ('IN', 'IND', 356, 'India', 'New Delhi', 'AS', 'INR'),
    ('LA', 'LAO', 418, 'Laos', 'Vientiane', 'AS', 'LAK'),
    ('US', 'USA', 840, 'United States', 'Washington', 'NA', 'USD'),
    ('ZW', 'ZWE', 716, 'Zimbabwe', 'Harare', 'AF', 'ZWL')
;

Legitimatize answered 20/1, 2016 at 13:22 Comment(0)

For MySQL fiddles, follow the links from the answer by Daniel Käfer.

For Microsoft SQL Server versions of the same tables, use these links:

Auto Shop Database:
SQL fiddle
Library Database:
SQL fiddle
Countries Table:
SQL fiddle

Midday answered 11/1, 2021 at 15:51 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Relationships between tables

Departments

Employees

Customers

Cars

Relationships between tables

Authors

Books

BooksAuthors

Examples

Countries

Recommended topics

Hot tags