Amazon.com

  www.amazon.com
  www.amazon.com

Interview Question

Business Analyst Interview Seattle, WA

If you have two SQL database tables that are not joined

  together, how would you create another table to join them.
Tags:
sql database tables
Answer

Interview Answer

2 Answers

1

Pretty simple question

Lets say we have two tables Person and Other school - each person is student in the school, since you need to join those two tables, you need common column to share and hence forth you find the key to join.

Inner Join - join table to bring records common in both the tables
Outer Join - Bring results besides the ones that are common or rather bring distingushed results (uncommon) in two tables

I'll write here inner join using MS- SQL

Question has three part - create another table , join the reults and then populate the reults

-- Create the resulting table
Create Table Result
{
string name;
}

-- Join to table and insert into result table

- Assuing two table person and school has common column name

INSERT INTO RESULTS --whatever be the join result will populate the result table
select name
from Person
Inner join School -- used inner join here
ON person.Name= School.Name -- used the key to join

-- now lets pull up the results by querying resut table

Select Name from Result -- Questions resolved :-)

Harshit Pandey on May 23, 2012
1

create new table mean that -> the tables are not connected, they don't have a common column but they complete each other. they have many to many relationship mean that new table will have 2 columns (this is one option :-) ), and the both are they primary keys of both tables, so they will be presenting the both tables, and they will be primary keys and foreign key for the connecting_tables at the same time. so table 1 and table 2 are called the look up tables and the connection_table called the bridge table.

so the sql code will look something like that :

create table connecting_tables(
id_toPresent_1st_table int(12) NOT NULL, // the variable type and length should be the identical to the variable type and length of the primary key in the 1st table

id_toPresent_2st_table int(12) NOT NULL, // the variable type and length should be the identical to the variable type and length of the primary key in the 2st table

//both are primary keys.
PRIMARY KEY(id_toPresent_1st_table, id_toPresent_2st_table),

//both are foreign keys.
CONSTRAINT fk_table_1 FOREIGN KEY (id_toPresent_1st_table_Id) REFERENCES table_1(table_1_Id),
CONSTRAINT fk_table_2 FOREIGN KEY (id_toPresent_2st_table_Id) REFERENCES table_2(table_2_Id)
);

kimo on Jun 16, 2012

Add Answers or Comments

To comment on this Question, Sign In with Facebook or Sign Up