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