Booking.com None Interview Question
NonesCountry: Netherlands
Interview Type: Phone Interview
2 should be like :
SELECT c.countryId, c.countryname FROM country c LEFT OUTER JOIN city i ON c.countryid = i.countryid GROUP BY c.countryId, c.countryname HAVING COUNT(c.countryId) < 3
As in questiion it is saying country having 0 city should also be in solution set.
1.
Select CountryId, Name from Country where CountryId not In (select CountryId from city)
2.
Select CountryID, NVL(cnt,0) from Country cntry,(Select CountryId, Count(City) from City group by CountryId having count(City)<3) city Where cntry.CountryId = City.CountryId(+)
1. how do you get the countries that has no cities?
select countryName from country where countryID not in (select country.countryId from country inner join city using(countryID));
2. how do you get the countries that has less than 3 cities and also make sure the countries with no cities also show up.
select country.countryId from country left outer join city using(countryID) group by countryID having count(*) < 3;
1. SELECT CountryID, CountryName FROM dbo.Country WHERE CountryID NOT IN (SELECT CountryID FROM dbo.City)
2. SELECT Co.CountryName, COUNT(CityName) AS NoOfCities FROM Country Co LEFT OUTER JOIN City Ci ON Ci.CountryID = Co.CountryID
GROUP BY Co.CountryName HAVING COUNT(Ci.CountryID) BETWEEN 1 AND 2
CREATE TABLE COUNTRY
(
CID INT PRIMARY KEY,
CNAME VARCHAR(100)
)
CREATE TABLE CITY
(
CID INT FOREIGN KEY REFERENCES COUNTRY(CID),
CINAME VARCHAR(100)
)
INSERT INTO COUNTRY VALUES (1,'INDIA')
INSERT INTO COUNTRY VALUES (2,'USA')
INSERT INTO COUNTRY VALUES (3,'JAMAICA')
INSERT INTO CITY VALUES (1,'MUMBAI')
INSERT INTO CITY VALUES (1,'DELHI')
INSERT INTO CITY VALUES (2,'NYC')
INSERT INTO CITY VALUES (2,'CHICAGO')
SELECT CO.*
FROM COUNTRY CO
WHERE CID NOT IN
(
SELECT CID
FROM CITY
)
SELECT COUNT (CI.CID) AS COUNT , CO.CNAME
FROM CITY CI
INNER JOIN COUNTRY CO
ON CI.CID = CO.CID
GROUP BY CI.CID,CO.CNAME
HAVING COUNT(CI.CID) < 3
CREATE TABLE `country` (
`country_id` int(11) NOT NULL auto_increment,
`country_name` varchar(64) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `city` (
`country_id` int(11) NOT NULL,
`city_name` varchar(64) NOT NULL,
CONSTRAINT `city_country` FOREIGN KEY (`country_id`) REFERENCES `user` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
select co.*
from country co left join city ci
on co.country_id = ci.country_id
where co.country_id is null;
select co.country_id, co.country_name
from country co left join city ci
on co.country_id = ci.country_id
group by country_id
having count(ci.city_name < 3);
CREATE TABLE `country` (
`country_id` int(11) NOT NULL auto_increment,
`country_name` varchar(64) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `city` (
`country_id` int(11) NOT NULL,
`city_name` varchar(64) NOT NULL,
CONSTRAINT `city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
select co.*, ci.*
from country co left join city ci
on co.country_id = ci.country_id
where ci.country_id is null;
select co.*, ci.*, count(ci.city_name)
from country co left join city ci
on co.country_id = ci.country_id
group by co.country_id
having count(ci.city_name) < 3;
Mysql solution in ANSI syntax:
1. how do you get the countries that has no cities?
SELECT country.countryid,
country_name
FROM country
LEFT JOIN city ON city.countryid = country.countryid
WHERE city.city_name IS NULL
OR city_name = ''
2. how do you get the countries that has less than 3 cities and also make sure the countries with no cities also show up.
SELECT country.countryid,
country_name,
count(1) cnt
FROM country
LEFT JOIN city ON city.countryid = country.countryid
GROUP BY country.countryid
HAVING cnt < 3;
1. SELECT c.* FROM country c LEFT OUTER JOIN city i ON c.countryid = i.countryid WHERE i.countryid IS NULL
- Murali Mohan June 16, 20132.SELECT c.countryId, c.countryname FROM country c INNER JOIN city i ON c.countryid = i.countryid GROUP BY c.countryId, c.countryname HAVING COUNT(c.countryId) < 3