create table contact (
    id uniqueidentifier primary key,
    first_name nvarchar(50) not NULL,
    last_name nvarchar(50) NOT NULL,
    age int
)
create table deal (
    id uniqueidentifier primary key,
    ref_created_by uniqueidentifier not NULL,
    name nvarchar(200) NOT NULL
)
create table activity (
    id uniqueidentifier primary key,
    ref_created_by uniqueidentifier not NULL,
    ref_for_deal uniqueidentifier not null
)

alter table activity add [subject] nvarchar(200)

insert into contact (id, first_name, last_name)
values
(newid(), 'john', 'smith'),
(newid(), 'tamara', 'jones')

select * from contact
insert into deal (id, name, ref_created_by)
values
(newid(), 'deal 1', '5BA73DFB-8153-4CBE-80EA-7C5CB2236EF4'),
(newid(), 'deal 2', '5BA73DFB-8153-4CBE-80EA-7C5CB2236EF4')

select * from deal
insert into activity (id, [subject], ref_created_by, ref_for_deal)
values
(newid(), 'meeting (7/18/2017)', '5BA73DFB-8153-4CBE-80EA-7C5CB2236EF4', '88DCB7F1-9A64-407D-BCC1-54004B2F595A'),
(newid(), 'phone call (6/16/2016)', 'B75F0C1E-541E-48CC-9026-9861F28EA077','88DCB7F1-9A64-407D-BCC1-54004B2F595A')

select
    a.subject
    , created_by.first_name + ' ' + created_by.last_name  as [created_by]
    , d.name
    , deal_created_by.first_name + ' ' + deal_created_by.last_name  as [deal_created_by]
from activity as a
left join contact as created_by on a.ref_created_by = created_by.id
left join deal as d on d.id = a.ref_for_deal
left join contact as deal_created_by on deal_created_by.id = d.ref_created_by

------ other


--newid()
--getdate() '2010-5-5'

drop table contact
create table company (
    id uniqueidentifier primary key,
    [name] nvarchar(200) NOT NULL,
    [date_founded] datetime
)
drop table company
select * from contact
select contact.id, first_name, last_Name, age, name AS company_name
from contact
inner join company on contact.ref_company = company.id


select * from company

insert into company (id, name, date_founded)
values (newid(), 'Dynamo Software', '1998-10-10')
insert into company (id, name, date_founded)
values (newid(), 'Microsoft', '1985-5-5')


insert into contact (id, first_name, last_name, age, ref_company)
values (newid(), 'John', 'Smith', 20, '5258FD16-1A45-4F0B-8C18-05D21E4D6A3D')

insert into contact (id, first_name, last_name, age, ref_company)
values (newid(), 'Tamara', 'Jones', 22, 'C5B8086E-81D1-4016-9C76-BDE4EF9FD3FC')

insert into contact (id, first_name, last_name, age, ref_company)
values (newid(), 'Scott', 'Gu', 30, NULL)

insert into contact (id, first_name, last_name, age, ref_company)
values (newid(), 'Scott', 'Hans', 30, 'C5B8086E-81D1-4016-9C76-BDE4EF9FD3FF')


DELETE FROM contact WHERE ID IN ('7E391E1D-3B05-4B3C-B8A3-8821B3EB24F7')
DELETE FROM contact WHERE ID = '7E391E1D-3B05-4B3C-B8A3-8821B3EB24F7'
UPDATE Contact SET last_name = 'Cage' WHERE ID = '7E391E1D-3B05-4B3C-B8A3-8821B3EB24F7'