Practice SQL


1. Of the vendors with a 'Y' order, how many are there in each areacode?

2. What is the frequency distribution of the first letter of the vendor names?
i.e. how many names start with each used letter.

3a.Which records of the japan table have a smallnum that is larger than its bignum?
3b.Which records of the japan table have a smallnum that is larger than 1/10 of its bignum?
3c.Which records of the japan table have a smallnum that is larger than 1/100 of its bignum?

4. What percentage of Cities have the same name as their District?
hint: subquery in Select clause or use the number of cities in an expression.

b. the number of distinct Districts
c. the number of distinct combinations of city name and District
d. SKIP: the districts that are in more than one country. self-join  HARD
 select distinct c1.district,c1.countrycode,c2.district,c2.countrycode from city as c1, city as c2 
where c1.district=c2.district and c1.countrycode<c2.countrycode and c1.district not like '_' and c2.districtnot like '_';
this is each pair of same district...

select district,group_concat(countrycode) 
 from (select distinct c1.district,c2.countrycode from city as c1, city as c2
        where c1.district=c2.district and c1.countrycode!=c2.countrycode and c1.district not like '_' and c2.district not like '_' order by c1.district) 
  as derivedtable group by district;

5. The count of each species that has a non-null remark. 

6. Events that happened within four months (120 days) of each other.
Hint: self-join.  Datediff() and Abs() functions.

7. The book of the Bible with the most number of chapters.

8. The rectangle with the smallest area.

9. The points that are near the 45 degree diagonal line from (0,0) to (1000,1000).
More specifically, the points whose X and Y coordinates are within 5 of each other.
Hint: Abs() 

10.  List the country names, (ISO) Code and (Internet TLD) Code2 of
the countries whose code2 is neither the first two, nor the last two,
nor the first and last characters of the Code. Hint: left(), right() and
concat() functions.