Vfrdtyky

I'm doing some exercises about SQL right now.

I have a database with this schema:

CREATE TABLE regions(

region_id          INTEGER PRIMARY KEY,

region_name        TEXT NOT NULL,

number_of_customers INTEGER NOT NULL);



CREATE TABLE region_borders (

region_id1 INTEGER REFERENCES regions,

region_id2 INTEGER REFERENCES regions,

PRIMARY KEY (region_id1, region_id2));



CREATE TABLE companies (

company_id                  INTEGER PRIMARY KEY,

company_name                TEXT NOT NULL,

headquarters_region     INTEGER REFERENCES regions NOT NULL);



CREATE TABLE works_in(

company  INTEGER REFERENCES companies,

region_id  INTEGER REFERENCES regions,

PRIMARY KEY (company_id, region_id));

I want to translate to SQL this query:

"Retrieve the companies that works in three different regions that two by two share a border. Write for every company the company's name and the names of the 3 regions that share borders, in alphabetical order."

Well I have tried to work with joins and grouping, but I only achieved to get the names of companies that works in three different regions. I really have no idea about how implement this query in SQL or relational algebra.

I am using PostgreSQL.

Thank you for any advice and help!

The final solution I could come up with is below, I try to go though the logical steps that took me to get there.
This is in T-SQL. (SQL Server 2016)

List how many regions a company works in

SELECT company_name, COUNT(works_in.region_id) AS count FROM companies 

    LEFT JOIN works_in ON works_in.company_id = companies.company_id

GROUP BY company_name

The result is like this (test data included below):

See which borders does a certain region have:

SELECT DISTINCT region_id1, region_id2 FROM region_borders 

INNER JOIN works_in w1 ON region_borders.region_id1 = w1.region_id

INNER JOIN works_in w2 ON region_borders.region_id2 = w2.region_id

WHERE (region_id1 = 1 OR region_id2 = 1)

List which adjacent regions a company is working in:

SELECT * from regions where exists (SELECT region_id1, region_id2 FROM region_borders 

INNER JOIN works_in w1 ON region_borders.region_id1 = w1.region_id

INNER JOIN works_in w2 ON region_borders.region_id2 = w2.region_id

WHERE (region_id1 = regions.region_id OR region_id2 = regions.region_id)

AND w1.company_id = 4

AND w2.company_id = 4)

This is for company 4, this will be generalized later.

Putting it all together:

SELECT company_name, COUNT(works_in.region_id) AS count FROM companies 

LEFT JOIN works_in ON works_in.company_id = companies.company_id

WHERE EXISTS (

    SELECT region_id1, region_id2 FROM region_borders 

    INNER JOIN works_in w1 ON region_borders.region_id1 = w1.region_id

    INNER JOIN works_in w2 ON region_borders.region_id2 = w2.region_id

    WHERE (region_id1 = works_in.region_id OR region_id2 = works_in.region_id)

    AND w1.company_id = companies.company_id

    AND w2.company_id = companies.company_id

)

GROUP BY company_name

HAVING COUNT(works_in.region_id) = 3

This lists the companies that work in 3 adjacent regions. I left out selecting the actual results you need, you should be able to build on this and finish the exercise.