create database if not exists count;

use count;
drop table if exists users0;
drop table if exists users1;
drop table if exists urls2;
drop table if exists user2;
drop table if exists urls3;
drop table if exists user3;
drop table if exists company3;
drop table if exists url_rel4;
drop table if exists urls4;
drop table if exists user4;
drop table if exists company4;


create table users0 (
       name varchar(20) NOT NULL,
       company varchar(20) not null,
       company_address varchar(20) not null,
       url1 varchar(20) not null,
       url2 varchar(20) not null
);

insert into users0 (name, company, company_address, url1, url2) values ('Joe', 'ABC', '1 work Lane', 'abc.com', 'xyz.com');
insert into users0 (name, company, company_address, url1, url2) values ('Jill', 'XYZ', 'i Job Street', 'abc.com', 'xyz.com');
/*
 select * from users0;
*/


create table users1 (
       name varchar(20) NOT NULL,
       company varchar(20) not null,
       company_address varchar(20) not null,
       url varchar(20) not null
);
insert into users1 (name, company, company_address, url) values ('Joe', 'ABC', '1 work Lane', 'abc.com');
insert into users1 (name, company, company_address, url) values ('Jill', 'XYZ', 'i Job Street', 'xyz.com');
insert into users1 (name, company, company_address, url) values ('Joe', 'ABC', '1 work Lane', 'xyz.com');
insert into users1 (name, company, company_address, url) values ('Jill', 'XYZ', 'i Job Street', 'abc.com');
/**
select * from users1;
**/

create table users2 (
    userid int AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL,
    company varchar(20) not null,
    company_address varchar(20) not null
);
insert into users2 (name, company, company_address) values ('Joe', 'ABC', '1 work Lane');
insert into users2 (name, company, company_address) values ('Jill', 'XYZ', 'i Job Street');

create table urls2 (
    urlid int AUTO_INCREMENT PRIMARY KEY,
    relUserId int,
    url varchar(20),
    FOREIGN KEY(RelUserId) REFERENCES users2(userid)
);
insert into urls2 (reluserid, url) values (1, 'abc.com');
insert into urls2 (reluserid, url) values (1, 'xyz.com');
insert into urls2 (reluserid, url) values (2, 'abc.com');
insert into urls2 (reluserid, url) values (2, 'xyz.com');
/**
select * from users2 join urls2 on urls2.reluserid=users2.userid;
**/

create table company3 ( 
    companyID int AUTO_INCREMENT PRIMARY KEY,
    company varchar(20),
    company_address varchar(20)
);
insert into company3 (company, company_address) values ('ABC', '1 Work Lane');
insert into company3 (company, company_address) values ('XYZ', '1 Job Street');
create table user3 (
    userid int AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL,
    RelCompanyId int,
    FOREIGN KEY(RelCompanyId) REFERENCES company3(companyID)
);
insert into user3 (name, RelCompanyId) values ('Joe', 1);
insert into user3 (name, RelCompanyId) values ('Jill', 2);
create table urls3 (
    urlid int AUTO_INCREMENT PRIMARY KEY,
    relUserId int,
    url varchar(20),
    FOREIGN KEY(RelUserId) REFERENCES users2(userid)
);
insert into urls3 (reluserid, url) values (1, 'abc.com');
insert into urls3 (reluserid, url) values (1, 'xyz.com');
insert into urls3 (reluserid, url) values (2, 'abc.com');
insert into urls3 (reluserid, url) values (2, 'xyz.com');
/**
select * from user3 join urls3 on urls3.reluserid=user3.userid join company3 on user3.relcompanyid=company3.companyid;
**/

create table company4 ( 
    companyID int AUTO_INCREMENT PRIMARY KEY,
    company varchar(20),
    company_address varchar(20)
);
insert into company4 (company, company_address) values ('ABC', '1 Work Lane');
insert into company4 (company, company_address) values ('XYZ', '1 Job Street');
create table user4 (
    userid int AUTO_INCREMENT PRIMARY KEY,
    name varchar(20) NOT NULL,
    RelCompanyId int,
    FOREIGN KEY(RelCompanyId) REFERENCES company4(companyID)
);
insert into user4 (name, RelCompanyId) values ('Joe', 1);
insert into user4 (name, RelCompanyId) values ('Jill', 2);
create table urls4 (
    urlid int AUTO_INCREMENT PRIMARY KEY,
    url varchar(20)
);
insert into urls4 (url) values ('abc.com');
insert into urls4 (url) values ('xyz.com');
create table url_rel4 ( 
    relationID int AUTO_INCREMENT PRIMARY KEY,
    RelURLid int,
    RelCompanyID int,
    FOREIGN KEY(RelURLid) REFERENCES urls4(urlid),
    FOREIGN KEY(RelCompanyId) REFERENCES company4(companyID)
);

/** 
This formulation of insert into url_rel4 works but is suboptimal because it requires knowing the
IDs that were set by auto increment a few lines ago
**/
insert into url_rel4 (RelURLid, relcompanyid) values (1, 1);
insert into url_rel4 (RelURLid, relcompanyid) values (1, 2);
insert into url_rel4 (RelURLid, relcompanyid) values (2, 1);
insert into url_rel4 (RelURLid, relcompanyid) values (2, 2);

/**
This formulation is better, but it has the cross-product problem.  Namely that creates the full
cross product of the company4 aqnd urls4 table and then immediately throws most of it away.
When tables are small, this is OK.  But when tables get large it is very wasteful.
**/
insert into url_rel4(relurlid, relcompanyid) select urls4.urlid, company4.companyid  from urls4, company4 where urls4.url='abc.com' and  company4.company='ABC';
insert into url_rel4(relurlid, relcompanyid) select urls4.urlid, company4.companyid  from urls4, company4 where urls4.url='abc.com' and  company4.company='XYZ';
insert into url_rel4(relurlid, relcompanyid) select urls4.urlid, company4.companyid  from urls4, company4 where urls4.url='xyz.com' and  company4.company='ABC';
insert into url_rel4(relurlid, relcompanyid) select urls4.urlid, company4.companyid  from urls4, company4 where urls4.url='xyz.com' and  company4.company='XYZ';



/**
The best insert formulation.
**/
insert into url_rel4(relurlid, relcompanyid) select urls4.urlid, company4.companyid  from urls4 join company4 on company='ABC' where urls4.url='abc.com';
insert into url_rel4(relurlid, relcompanyid) select urls4.urlid, company4.companyid  from urls4 join company4 on company='ABC' where urls4.url='xyz.com';
insert into url_rel4(relurlid, relcompanyid) select urls4.urlid, company4.companyid  from urls4 join company4 on company='XYZ' where urls4.url='abc.com';
insert into url_rel4(relurlid, relcompanyid) select urls4.urlid, company4.companyid  from urls4 join company4 on company='XYZ' where urls4.url='xyz.com';

/**
A similar formulation should be used for inserts into the User4 table.
**/
insert into user4 (name, RelCompanyId) select 'Joe',company4.companyid  from company4 where company4.company='ABC';

/**
Queries
**/
/** List all of XYZ’s urls **/
select urls4.url from urls4 
    join company4 on company4.company='xyz'  
    join url_rel4 on url_rel4.relcompanyid=company4.companyid
    where urls4.urlid=url_rel4.relurlid;

/** list the names of all employees of ABC **/
select user4.name from user4 
    join company4 on company4.company='abc' and user4.relcompanyid=company4.companyid;

/** List all employees of the company that Joe works for **/
with jc(cid) as (select user4.relcompanyid from user4 where user4.name='joe')     
    select unique user4.userid,user4.name from user4
        join jc on user4.relcompanyid=jc.cid;

/** NOTE use of "self" join **/
select unique u4.userid,u4.name from user4 as u4
        join user4 as uj4 on uj4.name='joe'
    where uj4.relcompanyid=u4.relcompanyid;

/** How many companies are in the DB? **/
select max(companyid) from company4;
select count(companyid) from company4;