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

Alistair Begg On Ananias

Background

It is quite easy to be be lured into sleep by Alistair Begg’s smooth accent and delivery.

 

Alistair Begg

Do you know how many will be converted if you take them under Christ?

 

Scripture

Acts 22:6-21

Link

  1. 6 “About noon as I came near Damascus, suddenly a bright light from heaven flashed around me. 7 I fell to the ground and heard a voice say to me, ‘Saul! Saul! Why do you persecute me?’
  2. 8 “‘Who are you, Lord?’ I asked.
  3. “ ‘I am Jesus of Nazareth, whom you are persecuting,’ he replied. 9 My companions saw the light, but they did not understand the voice of him who was speaking to me.
  4. 10 “‘What shall I do, Lord?’ I asked.
  5. ” ‘Get up,’ the Lord said, ‘and go into Damascus. There you will be told all that you have been assigned to do.’ 11 My companions led me by the hand into Damascus, because the brilliance of the light had blinded me.
  6. 12 “A man named Ananias came to see me. He was a devout observer of the law and highly respected by all the Jews living there. 13 He stood beside me and said, ‘Brother Saul, receive your sight!’ And at that very moment I was able to see him.
  7. 14 “Then he said: ‘The God of our ancestors has chosen you to know his will and to see the Righteous One and to hear words from his mouth. 15 You will be his witness to all people of what you have seen and heard. 16 And now what are you waiting for? Get up, be baptized and wash your sins away, calling on his name.
  8. 17 “When I returned to Jerusalem and was praying at the temple, I fell into a trance 18 and saw the Lord speaking to me. ‘Quick!’ he said. ‘Leave Jerusalem immediately, because the people here will not accept your testimony about me.’
  9. 19 “‘Lord,’ I replied, ‘these people know that I went from one synagogue to another to imprison and beat those who believe in you. 20 And when the blood of your martyr Stephen was shed, I stood there giving my approval and guarding the clothes of those who were killing him.’
  10. 21 “Then the Lord said to me, ‘Go; I will send you far away to the Gentiles.’ ”

 

Video

  1. Alistair Begg
    • Alistair Begg–Philemon, Part 1
      • Videos
        • Part 1
          • Video #1
            Channel :- Grace Bible Church
            Published on :- 2017-Jun-21st
            Link

Philemon

 

Videos

  1. Victory Church
    • Profile
      • Philemon is the story of a runaway slave named Onesimus. Stealing from his master and running away to Rome, Onesimus comes in contact with the apostle Paul. Paul then asks Onesimus to return to his master, even though he was a run away, even tho he stole. Onesimus owed a great debt. A debt he could not pay. Though his journey, we are shown an incredible picture of purpose, grace and redemption.
    • Videos
      • Video #1
        Channel :- Victory Church
        Published On :- 2015-July-7th
        Link
  2. Alistair Begg
    • Alistair Begg–Philemon, Part 1
      • Videos
        • Part 1
          • Video #1
            Channel :- Grace Bible Church
            Published on :- 2017-Jun-21st
            Link
  3. David Leone’, ‎Omega of Apostasy
    • Study in the book of Philemon (The Gospel Theater)
      • Profile
        • The book of Philemon has great insights into the great cosmic conflict and the stage in which the gospel is set.
      • Videos
        • Video #1
          Channel :- David Leone’
          Streamed live on Sep 19, 2018
          Link

 

In Depth

Alistair Begg

Alistair Begg–Philemon, Part 1

  1. Do you know how many will be converted if you take them under Christ
    • Barnabas
      • Luke 9

 

Transact SQL :- List Default Constraints

Background

List SQL Server Default Constraints.

Metadata

If one decides to use Sequences, one has to be able to document where it is being used.

Identity Columns

Identify Columns has self documenting tooling.

Inclusive :-

  1. sys.columns
    • is_identity
  2. sys.identity_columns
  3. OBJECTPROPERTY
    • TableHasIdentity
  4.  COLUMNPROPERTY
    • IsIdentity

Sequence

There is really nothing like a Sequence Column.

They are defined outside of a table definition.

It is not intrinsic so to speak.

It is a service that can be used as a communal toolset.

Outline

To detect use we can ask :-

  1. sys.columns
  2. sys.default_constraints
  3. sys.sql_expression_dependencies

Code


 if SUSER_ID('rdsa') is null
 begin

    use [master]

 end
 go

 if object_id('[dbo].[sp_helpConstraintDefault]') is null
 begin

    exec('create procedure [dbo].[sp_helpConstraintDefault] as ')

 end
 go

 alter procedure [dbo].[sp_helpConstraintDefault]
 as
 begin

   select 

              [schema]
                = tblSS.[name]

            , [object]
                = tblSO.[name]

            , [column]
                = tblSC.[name]

            , [columnType]
                = tblST.[name]

            , [is_identity]
                = tblSC.is_identity

            , [is_rowguidcol]
                = tblSC.is_rowguidcol

            , [columnDefault]
                = tblSDC.[name]

            , [definition]
                = tblSDC.[definition]

            , [referencedObject]
                = quotename
                    (
                        tblSORefS.[name]
                    )
                    + '.'
                    + quoteName
                        (
                            tblSORef.[name]
                        )

            , [referencedObjectType]
                = tblSORef.[type_desc]

    from   sys.columns tblSC

    inner join sys.types tblST

        on  tblSC.system_type_id = tblST.system_type_id

        and tblSC.user_type_id = tblST.user_type_id

    inner join sys.objects tblSO

        on tblSC.[object_id] = tblSO.[object_id]

    inner join sys.schemas tblSS

        on tblSO.[schema_id] = tblSS.[schema_id]

    inner join sys.default_constraints tblSDC

        on  tblSC.object_id = tblSDC.parent_object_id

        and tblSC.column_id = tblSDC.parent_column_id

    left outer join sys.sql_expression_dependencies AS tblSSED 

        on tblSC.[default_object_id] = tblSSED.referencing_id

    left outer join sys.objects tblSORef

        on tblSSED.referenced_id = tblSORef.[object_id]

    left outer join sys.schemas tblSORefS
        on tblSORef.[schema_id] = tblSORefS.[schema_id]

    order by

              tblSS.[name]

            , tblSO.[name]

            , tblSC.[name]

 end
 go

 if SUSER_ID('rdsa') is null
 begin

    exec sp_MS_marksystemobject '[dbo].[sp_helpConstraintDefault]'

 end
 go

Output

Output – Sequence – #1

Output – Database – Idf – #1

 

Source Control

GitHub

Repository

DanielAdeniji/dbo.sp_helpConstraintDefault
Link

Transact SQL :- Sequence Object

Background

As I read more about Hibernate, reminded once again the usage of Identity Columns to implement Sequences is quite unique to SQL Server and likely Sybase.

And, that Oracle, for one, uses Sequence to generate increasing numbers.

Hibernate

hibernate.IdGeneratorStrategy

Sample Code

Outline

  1. Schema
    • Does Schema exists?
      • schema_id()
    • If Schema does not exist?
      • Create Schema
  2. Sequence
    • Does Sequence exists?
      • Consult sys.sequences
    • If Sequence does not exist
      • Create Using “CREATE SEQUENCE”
  3. Utilize Sequence Generator
    • Add Data
      • Get next available Sequence
        • Syntax
          • NEXT VALUE FOR [sequence object]
        •  Sample
          • NEXT VALUE FOR [lab].[seq]
  4. Review Sequence Objects
    • sys.sequences
      • name
      • is_cached
      • increment
      • current_value
      • is_exhausted
      • maximum_value

Code


use [tempdb]
go

set nocount on
go

set XACT_ABORT on
go

declare @commit bit

declare @object  sysname

declare @schema_id int
declare @schema    sysname
declare @sequence  sysname

set @commit = 0

set @schema = 'lab'
set @sequence = 'seq';

begin tran

    /*
        Get schema_id for schema @schema
    */
    set @schema_id = schema_id(@schema);

    /*
        If schema_id is null, then does not exist
        create it
    */
    if @schema_id is null
    begin

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

    end

    set @schema_id = schema_id(@schema);

    /*
        If schema does not exist, please create it
    */
    if not exists
    (
        select *
        from   sys.sequences
        where  [name] = @sequence
        and    [schema_id] = @schema_id
    )
    begin

        CREATE SEQUENCE [lab].[seq]
        AS INTEGER
        START WITH 1
        INCREMENT BY 1
        MINVALUE 1
        --MAXVALUE 1000000
        CYCLE
        ; 

    end

    /*
        Please create table lab.vehicle
    */
    if object_id('[lab].[vehicle]') is null
    begin

        create table [lab].[vehicle]
        (
            id bigint not null
                DEFAULT NEXT VALUE FOR [lab].[seq]
        )

    end

    /*
        Please create table lab.bridge
    */
    if object_id('[lab].[bridge]') is null
    begin

        create table [lab].[bridge]
        (
            id bigint not null
                DEFAULT NEXT VALUE FOR [lab].[seq]

            , [name] nvarchar(80) not null

        )

    end

    /*
        Insert data
    */
    insert into [lab].[vehicle] default values;

    insert into [lab].[bridge]([name]) values('Seven Mile Bridge');

    insert into [lab].[vehicle] default values;

    insert into [lab].[bridge]([name]) values('Royal Gorge Bridge and Park');

    insert into [lab].[bridge]([name]) values('New River Gorge Bridge');

    /*
        Review data
    */
    select *

    from   [lab].[vehicle]

    select *

    from   [lab].[bridge]

    /*
        Review Sequence
    */
    select
              [schema] = tblSS.[name]
            , tblSeq.[name]
            , tblSeq.is_cached
            , tblSeq.increment
            , tblSeq.current_value
            , tblSeq.is_exhausted
            , tblSeq.maximum_value

    from   sys.sequences tblSeq

    inner join sys.schemas tblSS

            on tblSeq.[schema_id] = tblSS.schema_id

    where  tblSeq.[name] = @sequence
    ;

while (@@TRANCOUNT>0)
begin

    if (@commit =1)
    begin

        commit tran;

    end
    else
    begin

        rollback tran;
    end
end

Output

sequence.lab.20190512.1134PM

Antonio, Dawn, and AJ Armstrong

 

Videos

  1. A.J. ARMSTRONG: Houston teen accused of killing parents gives his side
    • Profile
      • “It’s…. it’s been really hard,” A.J. said, breaking down in tears. “It’s not even the fact of just dealing with not having my parents anymore. It’s the fact that I’m being accused of something of this magnitude.”
    • Videos
      • Video #1
        Channel :- ABC13 Houston
        Published On :- 2019-April-4th
        Link
    • Participants
      • Maxine Adams
  2. AJ Armstrong Trial: Girlfriend grilled over teen defendant’s lies
    • Participants
      • Kate Ober
        • AJ Junior Girlfriend
    • Videos
      • Video #1
        Channel :- ABC13 Houston
        Published On :- 2019-April-23rd
        Link
  3. AJ Armstrong trial: Defense rests after emotional testimony from defendant’s sister
    • Profile
      • A.J. Armstrong’s bid to clear his name is heading for closing arguments after his legal team rested its case Tuesday.
    • Participants
      • Kayra Armstrong
        • AJ Junior Sister
    • Videos
      • Video #1
        Channel :- ABC13 Houston
        Published On :- 2019-April-23rd
        Link
  4. Mistrial declared in AJ Armstrong case
    • Profile
      • After two days of apparent deadlock, the judge presiding over the trial of A.J. Armstrong announced a mistrial on Friday.
    • Participants
      • Judge Kelli Johnson
    • Videos
      • Video #1
        Channel :- KPRC 2 Click2Houston
        Published On :- 2019-April-26th
        Link

 

 

In Depth

 

  1. ABC 13 Houston
    • Family Shattered
      Link
  2. The Grio
    • Texas teen accused in deaths of NFL player dad & mom was angry about discipline
      Link

Peter Hockley, Four nights with the Devil

Profile

Hear the testimony of Peter Hockley who grew up in a household without religion. Dissatisfied with life and unable to find any lasting happiness in the world around him, his emptiness led to a search for spiritual meaning. At the age of twenty-one, Peter wanders through the religion section of the local bookstore, looking for answers. One book stands out among all others: An author who claims his written messages to the spirit world are answered by an invisible entity that takes control of his mind and body; an entity that calls itself “God”. Reading the entire book in one night, the young man is captivated by every word. Only one question remains: How can he know the truth? Taking a pen and paper, the man writes his own letter and is amazed when “God” responds. 

 

Videos

  1. Four nights with the Devil
    • Videos