Booking.com None Interview Question
NonesCountry: Netherlands
Interview Type: Phone Interview
1. SELECT c.* FROM country c LEFT OUTER JOIN city i ON c.countryid = i.countryid WHERE i.countryid IS NULL
2.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
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?
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.
- Kamal Nayan December 15, 2016