Term
|
Definition
select name, population from city
where (CountryCode='USA') and (Population > 10000)
order by Population limit 5; |
|
|
Term
|
Definition
select distinct CountryCode from City limit 5;
--Removes duplicates |
|
|
Term
|
Definition
select Name from City
where Name like 'Ka%' limit 5; |
|
|
Term
| SQL functions (upper, lower, length) |
|
Definition
select upper(name), lower(CountryCode),
Population/1000 as pop1000, length(name)
from City limit 5; |
|
|
Term
|
Definition
| select name, CountryCode, name || CountryCode from City limit 4; |
|
|
Term
|
Definition
| select substr(name, 1, 3), current_date as Today, current_time as WhenIdidit from City limit 4; |
|
|
Term
|
Definition
select Name, CountryCode
from City
where CountryCode in ('USA', 'FRA', 'GER')
order by Name
limit 10,5; -- offset, number of rows |
|
|
Term
|
Definition
select Name, CountryCode
from City
where CountryCode in
(select code form Country
where HeadOfState like '%Elisabeth%')
order by name limit 5; |
|
|
Term
| Give the name and number of speaker of countries whos official language is french |
|
Definition
select C.name, C.population * L.Percentage/100 AS Speakers from Country C, Country Language L
where C.code = L.CountryCode
and lower(L.Lang)='french'
order by Speakers desc
limit 5; |
|
|
Term
|
Definition
| Inner join of A and B gives the result of A intersect B |
|
|
Term
|
Definition
| An outer join of A and B gives the results of A union B |
|
|
Term
|
Definition
Used to provide statistical summaries from the database
min(expr)
max(expr)
sum(expr)
avg(expr)
count(expr) - number of non null values
count(*) - number of rows in table |
|
|
Term
|
Definition
select name, population from city
where population = (select max(population) form city); |
|
|
Term
|
Definition
| Allows us to divide a table into logical groups and calculate statistcis for the group |
|
|
Term
| Count the number of cities in each country |
|
Definition
select D.Name, count(C.Name)
from City C, country D
where C.CountryCode = D.code
group by D.name
limit 5; |
|
|
