Some T-SQL queries
SELECT t.name as tour_name, COUNT(*)
FROM Upfall u INNER JOIN trip t
on u.id = t.stop
GROUP BY t.name
HAVING COUNT(*) > 6
Aggregate funcions - AVG(), MAX(), MIN(), MEDIAN(), COUNT(), STDEV(), SUM(), VARIANCE()

--Summarizing rows with rollup
SELECT t.name AS tour_name, c.name as county_name COUNT(*) as falls_count
FROM upfall u INNER JOIN trip t
ON U.id = t.stop
INNER JOIN county c ON u.county_id = c.id
GROUP BY t.name, c.name with ROLLUP

SELECT t.name as tour_name,
c.name as county_name
COUNT(*) as falls_count
GROUPING(t.name) as n1 -- test null from cube
GROUPING(t.name) as n2 -- test null from cube
from upfall u INNER JOIN trip t
ON u.id = t.stop
INNER JOIN county c
ON u.county_id = c.id
WHERE t.name = 'Munising'
GROUP BY t.name,c.name WITH CUBE

--RECURSIVE QUERIES
WITH recursiveGov
(level, id, parent_id, name, type) AS
(SELECT 1, parent.id, parent.parent_id, parent.name, parent.type
FROM gov_unit parent
WHERE parent.parent_id IS NULL
UNION ALL
SELECT parent.level + 1, child.id, child.parent_id, child.name, child.type
FROM recursiveGov parent, gov_unit child
WHERE child.parent_id = parent.id)
SELECT level, id, parent_id, name, type
FROM recursiveGov

CREATE TABLE COUNTRY(
ID int identity (1,1),
NAME varchar(15) NOT NULL,
CODE Varchar(2) DEFAULT 'CA'
CONSTRAINT code_not_null NOT NULL
CONSTRAINT code_check
CHECK (country IN ('CA', 'US')),
indexed_name VARCHAR(15),
CONSTRAINT country_pk
PRIMARY KEY(ID)
CONSTRAINT country_fk01
FOREIGN KEY (name,code)
REFERENCES parent_example (name,country),
CONSTRAINT country_u01
UNIQUE(name,country)
CONSTRAINT country_index_upper
CHECK(indexed_name = UPPER(name))
);