Booking.com None Interview Question for Nones


Country: Netherlands
Interview Type: Phone Interview




Comment hidden because of low score. Click to expand.
9
of 11 vote

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

- Murali Mohan June 16, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

Good solution.

- Subhajit June 16, 2013 | Flag
Comment hidden because of low score. Click to expand.
3
of 3 votes

inner join has to be changed to left join .... thanks for the solution

- sunny smart June 16, 2013 | Flag
Comment hidden because of low score. Click to expand.
4
of 4 votes

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.

- Avenger June 16, 2013 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

@rupesh, avenger,
Thanks for the correction. Sorry, I had misread the question.

- Murali Mohan June 16, 2013 | Flag
Comment hidden because of low score. Click to expand.
1
of 1 vote

{SELECT c.* FROM country c, city i WHERE c.countryid=i.countryid AND i.countryid=NULL}

Will that work?

- Coder March 30, 2014 | Flag
Comment hidden because of low score. Click to expand.
6
of 6 vote

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(+)

- Raj July 07, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

neat and clean... short and simple solution..... good job..
but inner queries are generally cost effective..

- PKT January 20, 2014 | Flag
Comment hidden because of low score. Click to expand.
1
of 1 vote

QUERY 2:
select COUNT(c.cid) as count , c.cid from city c
join country co
on co.cid = c.cid
group by c.cid
having COUNT(c.cid) <3

- Pratik Shah June 16, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

1. select co.countryName from Country co where not exists (select 'X' from city ci where co.countryId=ci.countyId); ---- CountryNames whose does not have any cities

2. select co.countyName from Country co where 3>(select count(*) from city ci where ci.countryId=co.countryId);

Not tested

- rajeevkrishnanv June 16, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

query1: select cid from country where cid not in
(
select c.cid from country c
right join city cc
on cc.cid = c.cid
);

- Pratik Shah June 16, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select co_name from country left join cd on seq_no(Pk) = se(Fk) where se IS NULL;

- Sumit Kesarwani...... June 17, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select co_name from country left join cd on seq_no = se group by co_name HAVING count(se) < 3;

- Sumit Kesarwani...... June 17, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Select cname from country where cid in ( select cid from city where cityname is NULL )

- smita July 04, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

1. select countryname from country where countryid not in(select distinct countryid from city);
2. select countryname from country where countryid not in(select countryid from city group by countryid having count(*)>=3);;

- manvendrasingh828 July 10, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select country.countryname, countryId
from country
where country.countryname not in(select country.countryname
from country, city
where city.countryid = country.countryid
group by city.countryid
having count(city)>2)

- vartika September 02, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT NAME FROM country WHERE id NOT IN (SELECT c1.id FROM country c1, city c2 WHERE c1.id = c2.Country_id)

SELECT NAME FROM (SELECT c1.name,c1.id, COUNT(c2.name) AS Total FROM country c1 LEFT OUTER JOIN city c2 ON c1.id = c2.Country_id GROUP BY c1.id) AS inner_table WHERE Total < 3

- 2010prashant September 19, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select t3.Country_Nm,COUNT(City_Name)
from
(select Country_Nm,IsNull(City_Name,'N/A')as City_Name from Ods_Country as t1
left outer join Ods_City as t2 on t1.Country_Cd=t2.Country_Code
) as t3
group by t3.Country_Nm
having COUNT(City_Name)<3

- Pakos November 05, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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;

- Azim December 17, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select country_name
from country coun
where 1=1
and 3 >(select count(distinct city_name)
from city ct1
where ct1.country_id = coun.country_id)

- PC December 28, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- akashsingh41 January 30, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select country.country_name from country LEFT JOIN city ON country.countryid =city.countryid where city.city_name IS NULL;


select country_name from country x where ((select count(*) from city where city.countryid = x.countryid) > 0);

- Samurai February 25, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

Query 1:
Select country_name from country A
where not exists (Select 1 from City B
where A. Country id = B. Country id)

- Anonymous March 01, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

2. select co.cid,co.name from [dbo].[country] as co left join [dbo].[city] as ci on co.cid=ci.[cid] group by co.[cid],co.name having count(*)<3

- Anonymous June 11, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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

- moham316 April 04, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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);

- rohithv April 21, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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;

- rohithv April 21, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

For Question No. 2, We can write query like this as well

SELECT C.name,tt.total
FROM country_new C
LEFT JOIN (SELECT count(country_id) as total,country_id FROM city_new GROUP BY country_id) as tt ON C.id=tt.country_id
WHERE tt.total < 3 OR tt.total IS NULL;

- Anonymous August 18, 2016 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

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;

- Kamal Nayan December 15, 2016 | Flag Reply


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More