September 30, 2011 at 2:46 am
I have two tables like below, and
1. i would like to select all records and where customers have more than 1 Address return the Address with the Address Type = ‘Postal’.
2. what indexes should i create on the 2 tables and why
3. SQL statement to return all the customers with duplicate business names.
Can anyone please help with the above questions.
CREATE TABLE `bulksms`.`tblContact` (
`ipkContact` INT NOT NULL ,
`ifkContactType` varchar(10) ,
`FirstName` VARCHAR( 50 ) NOT NULL ,
`Surname` VARCHAR( 50 ) NOT NULL ,
`BusinessName` VARCHAR( 75 ) NOT NULL ,
`Telephone1` VARCHAR( 12 ) NOT NULL ,
`Telephone2` VARCHAR( 12 ) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE `bulksms`.`tblAddress` (
`ipkAddress` INT NOT NULL ,
`ifkContact` INT NOT NULL ,
`ifkAddressType` varchar(10) not Null,
`Line1` VARCHAR( 50 ) NOT NULL ,
`Line2` VARCHAR( 50 ) NOT NULL ,
`Line3` VARCHAR( 50 ) NOT NULL ,
`PostCode` INT NOT NULL
) ENGINE = InnoDB;
September 30, 2011 at 2:51 am
Please forgive me if I'm wrong but... is this homework?
-- Gianluca Sartori
September 30, 2011 at 2:52 am
omlac (9/30/2011)
I have two tables like below, and1. i would like to select all records and where customers have more than 1 Address return the Address with the Address Type = ‘Postal’.
2. what indexes should i create on the 2 tables and why
3. SQL statement to return all the customers with duplicate business names.
It's nice of you to let us see a sample of the kind of questions posed by teachers or interviewers, but I suspect that they are designed to test the question taker's ability rather than ours.
Have a go at answering the questions yourself, if you're still struggling then come back and post what you've got. I'm sure someone will be happy to point you in the right direction.
September 30, 2011 at 3:00 am
Its not homework nor is it an interview. am just trying to make my tables more effiecient and struggling with the first question, i know i can use a case, but not sure how to implement that.
September 30, 2011 at 3:25 am
1. i would like to select all records and where customers have more than 1 Address return the Address with the Address Type = ‘Postal’.
SELECT A.*
FROM bulksms.tblContact AS A
CROSS APPLY (
SELECT TOP(1) *
FROM bulksms.tblAddress AS B
WHERE A.ipkContact = B.ifkContact
ORDER BY CASE ifkAddressType WHEN 'Postal' THEN 0 ELSE 1 END
)
2. what indexes should i create on the 2 tables and why
It depends on too many factors, I can't answer this. I would need to see typical queries and usage patterns.
3. SQL statement to return all the customers with duplicate business names.
SELECT *
FROM bulksms.tblContact
WHERE BusinessName IN (
SELECT BusinessName
FROM bulksms.tblContact
GROUP BY BusinessName
HAVING COUNT(*) > 1
)
However, I suspect it won't run in MySQL. I think you should ask this on the MySQL forums in the first place.
Hope this helps
Gianluca
-- Gianluca Sartori
September 30, 2011 at 5:20 am
Thanks so much Hall of Fame for your help.
Is it neccessary to add an index on a key column, or foreign key column. Are there any performance related issues.
September 30, 2011 at 7:41 am
For basic indexing, have a look at this series (3 parts): http://www.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply