0

I have given following tasks in technical round.

  1. Write the SQL DDL for creating a new table called Job that relates a person to a company in a Many-to-Many relationship.
  2. Provided an SQL parameter called@searchValue, create an SQL query that will return all people who worked for a company where the name or description contains the search term.

enter image description here

I have studied https://social.technet.microsoft.com/wiki/contents/articles/19854.tutorial-many-to-many-dimension-example-for-sql-server-analysis-services.aspx and checked similar question like How to make SQL many-to-many same-type relationship table but still can not figure it out.

I tried solving questions as below:

1) I have created Job table but wondering that should it contains job_id as primary key or should I make primary key after combining person_id and company_id?

create table Job
(
  person_id int,
  company_id int,
  CONSTRAINT person_cat_pk PRIMARY KEY (person_id, company_id),
  CONSTRAINT FK_ person
      FOREIGN KEY (person_id) REFERENCES person (person_id),
  CONSTRAINT FK_company
      FOREIGN KEY (company_id) REFERENCES category (company_id)
);

2) I have used two INNER JOIN, is it correct in all circumstances? Can we solve it in a simple way? It helps me to understand for future queries. 

SELECT a.firstName, a.lastName, a.dateOfBirth 
FROM Person a INNER JOIN Job b ON a.id = b.person_id 
INNER JOIN Company c ON b.company_id = c.id 
WHERE ((c.name LIKE '%' + @searchValue + '%') 
OR (c.description  LIKE '%' + @searchValue + '%'))
Jayveer Parmar
  • 500
  • 7
  • 25
  • 1
    What database is this for? – tadman Sep 05 '17 at 15:51
  • It was not mentioned in technical round. We can solve with any SQL. – Jayveer Parmar Sep 05 '17 at 15:51
  • + don't work in mysql for string concat .. so ... with not solved with any sql – ScaisEdge Sep 05 '17 at 15:52
  • 1
    That is not an answer. Again: **What database is this for**? Every single one has their own often very peculiar interpretation of the SQL "Standard". – tadman Sep 05 '17 at 15:52
  • Okay. I want to solve with MS SQL Server. – Jayveer Parmar Sep 05 '17 at 15:53
  • @tadman Why does it matter? – BlueMonkMN Sep 05 '17 at 15:53
  • @BlueMonkMN They often have bitterly differing opinions on how to concatenate strings. – tadman Sep 05 '17 at 15:54
  • what @tadman are telling is real not only for string concat but for most function too – ScaisEdge Sep 05 '17 at 15:55
  • @scaisEdge Precisely. As far as standards go, SQL is one of the least standardized. – tadman Sep 05 '17 at 15:59
  • 1
    `FOREIGN KEY (company_id) REFERENCES category (company_id)` should that read `REFERENCES company`? Other than that, both answers look ok at first glance. Assuming a person cannot, in this constrained example, be employed by the same company twice (e.g. during different time periods, with start/end dates) then the composite key you've got is the best way to do it. For the second one, it looks right but you can always create some test cases to be sure. – ADyson Sep 05 '17 at 16:11
  • @ADyson I made a mistake there, it is actually like this: `FOREIGN KEY (company_id) REFERENCES company (company_id)`. Yes, it references company. – Jayveer Parmar Sep 05 '17 at 16:14
  • i would also add a job_id as a primary key in job table. Query looks ok – Kashif Qureshi Sep 05 '17 at 16:38

0 Answers0