CareerCup

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

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

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

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

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

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

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

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

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

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

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)

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

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

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;

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)

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

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

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

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

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;

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;