CODEWITHSUNDEEP
sqldatabasepostgresql
user10662775
user10662775

Reputation:

Query about companies and regions

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!

Upvotes: 0

Views: 232

Answers (1)

Marcell Toth
Marcell Toth

Reputation: 3688

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):

Results

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)

enter image description here

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.

enter image description here

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.

Upvotes: 1

Related Questions