Prefix
PREFIX qb: <http://purl.org/linked-data/cube#>
PREFIX : <http://example.com/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
Q1.1
SELECT (SUM(?extendedprice * ?discount) AS ?sum_revenue)
WHERE {
?li :orderdate ?orderdate ;
:extendedprice ?extendedprice ;
:discount ?discount ;
:quantity ?quantity .
?orderdate :year ?year .
FILTER(?year = 1993)
FILTER((?discount >= 1) && (?discount <= 3) && (?quantity < 25))
}
Q1.2
SELECT ((SUM(?dct) * SUM(?extPrice)) as ?sum_revenue)
WHERE {
?o a qb:Observation .
?o :quantity ?quantity .
?o :discount ?dct .
?o :extendedprice ?extPrice .
?o :orderdate ?date .
?date :yeamonthnum ?yearMonth .
FILTER (?quantity >= 26 && ?quantity <= 35 && ?dct >= 4 && ?dct <= 6 && ?yearMonth=199401 )
}
Q1.3
SELECT ((SUM(?discount) * SUM(?extPrice)) as ?sum_revenue) WHERE {
?o a qb:Observation .
?o :quantity ?quantity .
?o :discount ?discount .
?o :extendedprice ?extPrice .
?o :orderdate ?date .
?date :year ?y .
?date :weeknuminyear ?weekNumber .
FILTER ((?quantity >= 26) && (?quantity <= 35) && (?discount >= 5) && (?discount <= 7) && (?y = 1994) && (?weekNumber=6))
}
Q2.1
SELECT ?monthNumber ?brand (SUM(?revenue) as ?ag1) WHERE {
?o a qb:Observation .
?o :revenue ?revenue .
?o :orderdate ?date .
?date :monthnuminyear ?monthNumber .
?o :partkey ?part .
?part :brand ?brand .
?part :category ?category .
?o :suppkey ?supplier .
?supplier :region ?supplierRegion .
FILTER (((REGEX (?supplierRegion, "AMERICA" , "i")))&&((REGEX (?category,"MFGR#12" , "i"))))
} GROUP BY ?monthNumber ?brand
Q2.2
SELECT ?monthNumber ?brand (SUM(?revenue) as ?ag1)
WHERE {
?o a qb:Observation .
?o :revenue ?revenue .
?o :orderdate ?date .
?date :monthnuminyear ?monthNumber .
?o :partkey ?part .
?part :brand ?brand .
?o :suppkey ?supplier .
?supplier :region ?supplierRegion .
FILTER (((REGEX (?supplierRegion,"ASIA" , "i")))&&(( str(?brand)>="MFGR#2221"))&&(( str(?brand)<="MFGR#2228")))
} GROUP BY ?monthNumber ?brand
Q2.3
SELECT ?monthNumber ?brand (SUM(?revenue) as ?ag1) WHERE {
?o a qb:Observation .
?o :revenue ?revenue .
?o :orderdate ?date .
?date :monthnuminyear ?monthNumber .
?o :partkey ?part .
?part :brand ?brand .
?o :suppkey ?supplier .
?supplier :region ?supplierRegion .
FILTER (((REGEX (?supplierRegion,"EUROPE" , "i")))&&((REGEX (?brand,"MFGR#2239" , "i"))))
} GROUP BY ?monthNumber ?brand
Q3.1
SELECT ?monthNumber ?supplierNation ?supplierRegion (SUM(?revenue) as ?ag1)
WHERE {
?o a qb:Observation .
?o :revenue ?revenue .
?o :orderdate ?date .
?date :monthnuminyear ?monthNumber .
?date :year ?year .
?o :custkey ?customer .
?customer :region ?customerRegion .
?o :suppkey ?supplier .
?supplier :nation ?supplierNation .
?supplier :region ?supplierRegion .
FILTER ((REGEX (?customerRegion,"ASIA" , "i"))&&(REGEX (?supplierRegion,"ASIA" , "i"))&&(?year >= 1992)&&(?year <= 1997))
} GROUP BY ?monthNumber ?supplierNation ?supplierRegion
Q3.2
SELECT ?monthNumber ?customerCity ?supplierCity (SUM(?rev) as ?ag1)
WHERE {
?o a qb:Observation .
?o :revenue ?rev .
?o :orderdate ?date .
?date :monthnuminyear ?monthNumber .
?date :year ?yearNumber .
?o :custkey ?customer .
?customer :city ?customerCity .
?customer :nation ?customerNation .
?o :suppkey ?supplier .
?supplier :city ?supplierCity .
?supplier :nation ?supplierNation .
FILTER ((REGEX (?customerNation,"UNITED STATES" , "i"))&&(REGEX (?supplierNation,"UNITED STATES" , "i"))&&(?yearNumber >= 1992)&&(?yearNumber <= 1997))
} GROUP BY ?monthNumber ?customerCity ?supplierCity
Q3.3
SELECT ?monthNumber ?customerCity ?supplierCity (SUM(?rev) as ?ag1)
WHERE {
?o a qb:Observation .
?o :revenue ?rev .
?o :orderdate ?date .
?date :monthnuminyear ?monthNumber .
?date :year ?yearNumber .
?o :custkey ?customer .
?customer :city ?customerCity .
?o :suppkey ?supplier .
?supplier :city ?supplierCity .
FILTER (((REGEX (?customerCity,"UNITED KI1" , "i") || REGEX (?customerCity,"UNITED KI5" , "i")))&&((REGEX (?supplierCity,"UNITED KI1" , "i") || REGEX (?supplierCity,"UNITED KI5" , "i")))&& (?yearNumber >= 1992)&&(?yearNumber <= 1997))
} GROUP BY ?monthNumber ?customerCity ?supplierCity
Q3.4
SELECT ?day ?customerCity ?supplierCity (SUM(?rev) as ?ag1)
WHERE {
?o a qb:Observation .
?o :revenue ?rev .
?o :orderdate ?date .
?date :daynummonth ?day .
?o :custkey ?customer .
?customer :city ?customerCity .
?o :suppkey ?supplier.
?supplier :city ?supplierCity .
?date :yeamonthnum ?monthYear .
FILTER (((REGEX (?supplierCity,"UNITED KI1" , "i") || REGEX (?supplierCity,"UNITED KI5" , "i")))&&((REGEX (?customerCity,"UNITED KI1" , "i") || REGEX (?customerCity,"UNITED KI5" , "i")))&&(?monthYear = 199712))
} GROUP BY ?day ?customerCity ?supplierCity
Q4.1
SELECT ?monthNumber ?customerCountry (SUM(?rev - ?cost) as ?ag1)
WHERE {
?o a qb:Observation .
?o :revenue ?rev .
?o :supplycost ?cost .
?o :orderdate ?date .
?date :monthnuminyear ?monthNumber .
?o :custkey ?customer .
?customer :nation ?customerCountry .
?customer :region ?customerRegion .
?o :partkey ?part .
?part :brand ?partBrand .
?part :mfgr ?manufacturer .
?o :suppkey ?supplier .
?supplier :region ?supplierRegion .
FILTER ((REGEX (?supplierRegion,"AMERICA" , "i"))&&(REGEX (?customerRegion,"AMERICA" , "i"))&&((REGEX (?manufacturer,"MFGR#1" , "i") || REGEX (?manufacturer,"MFGR#2" , "i"))))
} GROUP BY ?monthNumber ?customerCountry
Q4.2
SELECT ?yearNumber ?category ?nation (SUM(?rev - ?cost) as ?ag1)
WHERE {
?o a qb:Observation .
?o :revenue ?rev .
?o :supplycost ?cost .
?o :orderdate ?date .
?date :year ?yearNumber .
?o :custkey ?customer .
?customer :region ?customerRegion .
?o :partkey ?part .
?part :category ?category .
?part :mfgr ?manufacturer .
?o :suppkey ?supplier .
?supplier :nation ?nation .
?supplier :region ?supplierRegion .
FILTER ((REGEX (?supplierRegion,"AMERICA" , "i"))&&(REGEX (?customerRegion,"AMERICA" , "i"))&&((REGEX (?manufacturer,"MFGR#1" , "i") || REGEX (?manufacturer,"MFGR#2" , "i")))&&(?yearNumber >= 1997)&&(?yearNumber <= 1998))
} GROUP BY ?yearNumber ?category ?nation
Q4.3
SELECT ?monthNumber ?brand ?supplierCity (SUM(?rev-?cost) as ?ag1)
WHERE {
?o a qb:Observation .
?o :revenue ?rev .
?o :supplycost ?cost .
?o :orderdate ?date .
?date :year ?yearNumber.
?date :monthnuminyear ?monthNumber .
?o :custkey ?customer .
?customer :region ?customerRegion .
?o :partkey ?part .
?part :brand ?brand .
?part :category ?category .
?o :suppkey ?supplier .
?supplier :city ?supplierCity .
?supplier :nation ?supplierNation .
FILTER ((REGEX (?supplierNation,"UNITED STATES" , "i"))&&(REGEX (?customerRegion,"AMERICA" , "i"))&&(REGEX (?category,"MFGR#14" , "i"))&&(?yearNumber>= 1997)&&(?yearNumber<= 1998))
} GROUP BY ?monthNumber ?supplierCity ?brand