Sample Database – w3resource

Background

Looking for sample database model for a query that I had in mind.

Found a simple on at w3Resource.

 

w3Resource

The sample is title “SQL Exercises, Practice, Solution – exercises on employee Database”.

And, it is available here.

 

Model

Data

Employee

 

SQL

Data Definition Language ( DDL )

Employee

SQL


if schema_id('w3resource') is null
begin

    exec('create schema [w3resource] authorization [dbo]')

end
go

/*

    w3resource
    SQL employee Database [115 Exercise with Solution]
    https://www.w3resource.com/sql-exercises/employee-database-exercise/index.php

*/

/*

    drop table [w3resource].[employee];

*/
if object_id('[w3resource].[employee]') is null
begin

    create table [w3resource].[employee]
    (
          [id]              int          not null
                            identity(1,1)

        , [empID]           int          not null

        , [empName]         varchar(60)  not null

        , [jobName]         varchar(60)  null

        , [managerID]       int          null

        , [hireDate]        date         null

        , [salary]          decimal(10,2) null

        , [commision]       decimal(7,2) null

        , [departmentID]    int          null

        , constraint [PK_w3resource.employee]
            primary key
            (
                [empID]
            )
        , constraint [FK_w3resource.employee.managerID]
            foreign key
            (
                [managerID]
            )
            references [w3resource].[employee]
            (
                [empID]
            )

   ) 

end   

go



 

Data Manipulation Language ( DML )

Employees

SQL



set nocount on
go

set XACT_ABORT on
go


/*

    w3resource
    SQL employee Database [115 Exercise with Solution]
    https://www.w3resource.com/sql-exercises/employee-database-exercise/index.php

*/

delete 
from    [w3resource].[employee]
;

DBCC CHECKIDENT ('[w3resource].[employee]', RESEED, 0)  
go

insert into [w3resource].[employee]
(
      [empID]   
    , [empName] 
    , [jobName] 
    , [managerID]
    , [hireDate]
    , [salary]
    , [commision]
    , [departmentID]
)     
select   
      63819 as [empID]   
    , 'Kayling' as [empName] 
    , 'President' as [jobName] 
    , null as [managerID]
    , '1991-11-18'
    , 6000
    , null
    , 1001


union all

select   
      66928 as [empID]   
    , 'Blaze' as [empName] 
    , 'Manager' as [jobName] 
    , 63819 as [managerID]
    , '1991-05-01'
    , 2750
    , null
    , 3001

union all

select   
      67832 as [empID]   
    , 'Clare' as [empName] 
    , 'Manager' as [jobName] 
    , 63819 as [managerID]
    , '1991-06-09'
    , 2550
    , null
    , 1001

union all

select   
      65646 as [empID]   
    , 'Jonas' as [empName] 
    , 'Manager' as [jobName] 
    , 63819 as [managerID]
    , '1991-04-02'
    , 2957.00
    , null
    , 2001

union all

select   
      67858 as [empID]   
    , 'Scarlet' as [empName] 
    , 'Analyst' as [jobName] 
    , 65646 as [managerID]
    , '1997-04-19'
    , 3100.00
    , null
    , 2001


union all

--Frank
select   
      69062 as [empID]   
    , 'Frank' as [empName] 
    , 'Analyst' as [jobName] 
    , 65646 as [managerID]
    , '1991-12-03'
    , 3100.00
    , null
    , 2001


union all

-- Sandrine
select   
      63679 as [empID]   
    , 'Sandrine' as [empName] 
    , 'Clerk' as [jobName] 
    , 69062 as [managerID]
    , '1990-12-18'
    , 900
    , null
    , 2001

union all

-- Adelyn
select   
      64989 as [empID]   
    , 'Adelyn' as [empName] 
    , 'Salesman' as [jobName] 
    , 66928 as [managerID]
    , '1991-02-20'
    , 1700
    , 400
    , 3001


union all

-- Wade
select   
      65271 as [empID]   
    , 'Wade' as [empName] 
    , 'Salesman' as [jobName] 
    , 66928 as [managerID]
    , '1991-02-22'
    , 1350.00
    , 600.00
    , 3001

union all
-- Madden
select   
      66564 as [empID]   
    , 'Madden' as [empName] 
    , 'Salesman' as [jobName] 
    , 66928 as [managerID]
    , '1991-09-28'
    , 1350.00
    , 600.00
    , 3001

union all

-- Tucker
select   
      68454 as [empID]   
    , 'Tucker' as [empName] 
    , 'Salesman' as [jobName] 
    , 66928 as [managerID]
    , '1991-09-08'
    , 1600.00
    , 0.00
    , 3001

union all

-- Adnres
select   
      68736 as [empID]   
    , 'Adnres' as [empName] 
    , 'Clerk' as [jobName] 
    , 67858 as [managerID]
    , '1997-05-23'
    , 1200.00
    , null
    , 2001

union all

-- Julius
select   
      69000 as [empID]   
    , 'Julius' as [empName] 
    , 'Clerk' as [jobName] 
    , 66928 as [managerID]
    , '1991-12-03'
    , 1050.00
    , null
    , 3001

union all

-- Marker
select   
      69324 as [empID]   
    , 'Marker' as [empName] 
    , 'Clerk' as [jobName] 
    , 67832 as [managerID]
    , '1992-01-23'
    , 1400.00
    , null
    , 1001

go

 

Source Control

GitHub

DanielAdeniji/w3ResourceDBSample
Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s