Since the tags aren't as obvious using query, you can always assign the AS statement ie: storyText as story.content Story Stories Samples
|
|
|
|---|
| Brexit Stories | All Stories that include mention of Brexit | SELECT storyText, header FROM story WHERE storyText like '%brexit%' | | Hilary Clinton Stories | All Stories that include mention of Clinton | SELECT storyText, header FROM story WHERE storyText like '%clinton%' | | AP Stories | All AP Topics | SELECT S.storyText AS story, S.header AS headline, T.name as topic
FROM story S
JOIN story_topic T ON S.topicID=T.ID
WHERE T.name IN ('AP Business', 'AP Entertainment', 'AP Politics', 'AP Top Headlines')
AND S.output=1
ORDER BY topic | | Stories - First Sentence Only | Stories and include only the first sentence. | SELECT
IF(LENGTH(SUBSTR(S.storyText, 1, POSITION('.' IN S.storyText))) > 0,
SUBSTR(S.storyText, 1, POSITION('.' IN S.storyText)),
S.storyText) AS header, T.broadcastname
FROM story S
JOIN story_topic T ON S.topicID=T.ID
WHERE T.name=@Topic | | Stories - Single Paragraph Only | First paragraph only of each story | SELECT
IF(LENGTH(SUBSTR(S.storyText, 1, POSITION('\n' IN S.storyText))) > 0,
SUBSTR(S.storyText, 1, POSITION('\n' IN S.storyText)),
S.storyText) AS header, T.broadcastName
FROM story S
JOIN story_topic T ON S.topicID=T.ID
WHERE T.name=@Topic | | Stories from a specific topic | Stories from a specific topic using parameters | SELECT S.storyText, S.header, T.broadcastName
FROM story S, story_topic T
WHERE S.topicid=T.ID AND T.name LIKE @Topic AND S.output=1
/*Example BBC% */ ORDER BY T.broadcastName, S.priority DESC |
Sports Samples
|
|
|
|---|
| Final MLB Scores | All MLB Scores that are final | SELECT L.name as league, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore as vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore as hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND S.gamestate = 'Final' AND L.name = 'MLB' | | Final Scores | All scores that have gone final | SELECT L.name, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND S.gamestate = 'Final' | | New York Games | Games that include NYC teams | SELECT L.name, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND (V.location = 'New York' OR H.location = 'New York') | | Scores - NHL Top 10 Players | Scores - NHL Top 10 Players w params | SELECT P.firstname, P.lastname, T.code, T.location, T.name,
'Top 10 Assists' as topic, MAX(case when TAG.key = @stat then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as stat,
/*A example for stat*/ MAX(case when TAG.key = 'headshot' then TAG.`value` end) as headshot
FROM
teamplayer_tag TAG
JOIN team_player P on TAG.teamplayerID=P.ID
JOIN team T on P.teamID=T.ID
JOIN league L on L.ID=T.leagueID
WHERE L.code='NHL'
GROUP BY TAG.teamplayerID
ORDER BY stat DESC, P.lastname, P.firstname
LIMIT 10 | | Scores - NBA Standings Divisions | Scores - NBA Standings w Params | SELECT
MAX(case when TAG.key = 'divisionRank' then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as ranks,
T.code, T.location, T.name,
MAX(case when TAG.key = 'division' then TAG.`value` end) as division,
MAX(case when TAG.key = 'GP' then TAG.`value` end) as GP,
MAX(case when TAG.key = 'W' then TAG.`value` end) as W,
MAX(case when TAG.key = 'L' then TAG.`value` end) as L,
MAX(case when TAG.key = 'PCT' then TAG.`value` end) as PCT,
MAX(case when TAG.key = 'divisionGB' then TAG.`value` end) as GB,
MAX(case when TAG.key = 'home' then TAG.`value` end) as home,
MAX(case when TAG.key = 'road' then TAG.`value` end) as away,
MAX(case when TAG.key = 'last10' then TAG.`value` end) as L10,
MAX(case when TAG.key = 'streak' then TAG.`value` end) as STRK
FROM team_tag TAG
JOIN team T ON TAG.teamID=T.ID
JOIN league L ON T.leagueID=L.ID
WHERE L.code='NBA'
GROUP BY TAG.teamID
HAVING division=@division
ORDER BY ranks |
|
|
|
Custom Samples
|
|
|
|---|
| Golf Query | Golf Query using parameters | SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
SELECT P.*,
'PGA' as 'league' FROM
/*PGA as example*/
(SELECT Y FROM
(SELECT JSON_EXTRACT(X.prop, '$.runners') AS Y FROM
(SELECT MAX(case when TAG.key='name' then TAG.value end) AS name, MAX(case when TAG.key='Winner' then TAG.value end) AS prop FROM custom_tag TAG JOIN custom C ON TAG.customID=C.ID JOIN custom_topic T ON C.topicID=T.ID WHERE T.name=@topic GROUP BY TAG.customID HAVING name=@Event) AS X) AS Z) AS PROP, /*Custom ID example: The Ryder Cup 2023 and Topic example : Golf Sportsbook*/ JSON_TABLE(
CAST(PROP.Y AS JSON),
'$[*]' COLUMNS(
name VARCHAR(100) PATH '$.name',
odds VARCHAR(100) PATH '$.odds',
active BOOL PATH '$.active'
)
) AS P; | | Go Transit Buses | Go Transit Buses | SELECT 'Go Transit Buses' as service,
MAX(case when TAG.key = 'zone' then TAG.`value` end) as zone,
MAX(case when TAG.key = 'stop' then TAG.`value` end) as stop,
MAX(case when TAG.key = 'routeID' then TAG.`value` end) as routeID,
MAX(case when TAG.key = 'route' then TAG.`value` end) as route,
MAX(case when TAG.key = 'routeColour' then TAG.`value` end) as routeColour,
MAX(case when TAG.key = 'routeTextColour' then TAG.`value` end) as routeTextColour,
MAX(case when TAG.key = 'routeType' then TAG.`value` end) as routeType,
MAX(case when TAG.key = 'direction1' then TAG.`value` end) as destination1,
MAX(case when TAG.key = 'departureTime1' then TAG.`value` end) as departureTime1,
MAX(case when TAG.key = 'direction2' then TAG.`value` end) as destination2,
MAX(case when TAG.key = 'departureTime2' then TAG.`value` end) as departureTime2,
MAX(case when TAG.key = 'direction3' then TAG.`value` end) as destination3,
MAX(case when TAG.key = 'departureTime3' then TAG.`value` end) as departureTime3
FROM
custom_tag TAG
JOIN custom C on TAG.customID=C.ID
JOIN custom_topic T on C.topicID=T.ID
WHERE T.name='Scotia Bank Arena Public Transit'
GROUP BY TAG.customID
HAVING routeType='bus' AND zone='Toronto_GO' AND destination1 NOT LIKE '%union station%'
ORDER BY route | | US Elections Trend Reports | Custom - US Elections Trend Reports w params | SELECT 218 AS majority, partyChip, total,
CASE
WHEN X.p='DEM' THEN 'Democratic'
WHEN X.p<>'DEM' THEN 'Republican'
END AS party
FROM
(SELECT
MAX(case when TAG.key = 'party' then TAG.`value` end) as p,
MAX(case when TAG.key = 'party' then CONCAT(TAG.`value`, '.png') end) as partyChip,
MAX(case when TAG.key = 'Winning Trend' then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as total
FROM custom_tag TAG
JOIN custom C on TAG.customID=C.ID
JOIN custom_topic T on C.topicID=T.ID
WHERE T.name=@race /*House Trend Report Example*/ GROUP BY TAG.customID
LIMIT 2) X
ORDER BY total DESC |
|
|
|
Elections Samples
|
|
|
|---|
| Elections - California Proposition 68 | Elections - California Proposition 68 | SELECT CANDIDATE.lastname AS choice, CHOICE.votes
FROM elc_contest CONTEST
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
JOIN elc_office OFFICE ON CONTEST.officeID=OFFICE.ID
JOIN elc_choice CHOICE ON CHOICE.contestID=CONTEST.ID
JOIN elc_candidate CANDIDATE ON choice.candidateID=CANDIDATE.ID
WHERE OFFICE.name = 'Proposition 68-Fund Outdoor Access' AND EVENT.name='California State Primary 2018'
ORDER BY CHOICE.votes DESC | | Elections - Party Totals | Elections - Party Totals params | SELECT * FROM
((SELECT nameShort AS partyCode, name AS partyName, chicklet, COUNT(*) as total
FROM (
SELECT * FROM
(SELECT PARTY.name, PARTY.nameShort, CHOICE.votes, CHOICE.contestID, PARTY.color AS chicklet
FROM elc_candidate CANDIDATE
JOIN elc_party PARTY ON CANDIDATE.partyID=PARTY.ID
JOIN elc_choice CHOICE ON CANDIDATE.ID=CHOICE.candidateID
WHERE CHOICE.contestID IN
(SELECT CONTEST.ID FROM elc_contest CONTEST
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
WHERE EVENT.name=@Event)
ORDER BY contestID, votes DESC) x
GROUP BY contestID) y
GROUP BY nameShort)
UNION
(SELECT distinct P.nameShort AS partyCode, P.name AS partyName, P.color AS chicklet, 0 AS total
FROM elc_party P
JOIN elc_candidate CANDIDATE ON P.ID=CANDIDATE.partyID
JOIN elc_choice CHOICE ON CANDIDATE.ID=CHOICE.candidateID
JOIN elc_contest CONTEST ON CHOICE.contestID=CONTEST.ID
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
WHERE EVENT.name=@Event /*Alberta 2019*/ AND P.nameShort IN ('UCP', 'NDP', 'AP', 'LIB', 'GRN', 'WRP'))) z
GROUP BY partyCode
ORDER BY total DESC, partyCode | | Elections - Electoral College Total | Elections - Electoral College Total | SELECT CHOICE.votes, p.name
FROM elc_contest CONTEST
JOIN elc_choice CHOICE ON CHOICE.contestID=CONTEST.ID
JOIN elc_event E ON E.ID=CONTEST.eventID
JOIN elc_office O ON CONTEST.officeID=O.ID
JOIN elc_candidate CANDIDATE ON CHOICE.candidateID=CANDIDATE.ID
JOIN elc_party P ON CANDIDATE.partyID=P.ID
WHERE E.name='US and Statewide Popular and Electoral Votes 2016'
AND O.name='Electoral College'
AND P.nameShort IN ('REP', 'DEM') |
Finance Samples
|
|
|
|---|
| Highest Volume Stocks | The 5 highest volume stocks | SELECT name, symbol, volume FROM stock ORDER BY volume DESC limit 5 | | Top Gaining Stocks | The 5 top gaining stocks by percentage | SELECT name, symbol, volume, priceChange/price as percentChange, price, priceChange FROM stock ORDER BY priceChange/price DESC limit 5 | | Top Losing Stocks | The 5 top losing stocks by percentage | SELECT name, symbol, volume, priceChange/price as percentChange, price, priceChange FROM stock ORDER BY priceChange/price ASC limit 5 | | Market Volume Leaders | Market Volume Leaders using parameters | SELECT symbol, name, volume
FROM stock S
JOIN stock_topic T ON S.topicID=T.ID
WHERE T.market=@market /*Dow Jones 30 Example*/ ORDER by S.volume DESC
LIMIT 10 |
JSON SamplesSupported in mySQL version 8.x
|
|
|
|---|
| JSON extract from a JSON dynamic tag | pull a json feed: @prop
| SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
SET @PROP := 'o_Grid_Position_of_Winner';
SET @LEAGUE := 'F1';
SELECT P.* FROM
(SELECT Y FROM
(SELECT JSON_EXTRACT(X.json, '$.runners') AS Y FROM
(SELECT TAG.`value` AS json FROM league_tag TAG JOIN league L ON TAG.leagueID=L.ID WHERE `key`= @PROP AND L.code=@LEAGUE) AS X) AS Z) AS PROP,
JSON_TABLE(
CAST(PROP.Y AS JSON),
'$[*]' COLUMNS(
name VARCHAR(100) PATH '$.name',
odds VARCHAR(100) PATH '$.odds',
active BOOL PATH '$.active'
)
) AS P; |
|
|
|
|
|
|
|---|
| Tweets with Trump |
| SELECT `tweet`, `fromAccount`, `fromName`, `avatarUrl` FROM tweet WHERE `tweet` like '%trump%' |
|
|
|
|
|
|
Weather Samples
|
|
|
|---|
| Hottest Cities | The 5 hottest cities | SELECT C.cityName as name, D.temp as temp, CONCAT('file:///D:/',W.`filename`) as media FROM weather W, weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id AND w.`ID` = D.`weatherId` ORDER BY D.temp DESC LIMIT 5 | | The Hottest City | The hottest city | SELECT C.cityName, D.temp, D.shortText FROM weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id ORDER BY D.temp DESC LIMIT 1 | | The Coldest City | The coldest city | SELECT C.cityName, D.temp, D.shortText FROM weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id ORDER BY D.temp ASC LIMIT 1 | | 7 Day Forecast | 7 Day Forecast using parameters | SELECT C.cityName,
MAX(CASE WHEN W.detailType = 'current' THEN 'Current' END) AS day0,
MAX(CASE WHEN W.detailType = 'current' THEN W.temp END) AS temp0,
MAX(CASE WHEN W.detailType = 'current' THEN X.type END) AS condition0,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'current' THEN X.filename END)) AS icon0,
MAX(CASE WHEN W.detailType = 'day1' THEN W.dayOfWeek END) AS day1,
MAX(CASE WHEN W.detailType = 'day1' THEN W.temp END) AS temp1,
MAX(CASE WHEN W.detailType = 'day1' THEN W.temp2 END) AS low1,
MAX(CASE WHEN W.detailType = 'day1' THEN W.pop END) AS pop1,
MAX(CASE WHEN W.detailType = 'day1' THEN X.type END) AS condition1,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day1' THEN X.filename END)) AS icon1,
MAX(CASE WHEN W.detailType = 'day2' THEN W.dayOfWeek END) AS day2,
MAX(CASE WHEN W.detailType = 'day2' THEN W.temp END) AS temp2,
MAX(CASE WHEN W.detailType = 'day2' THEN W.temp2 END) AS low2,
MAX(CASE WHEN W.detailType = 'day2' THEN W.pop END) AS pop2,
MAX(CASE WHEN W.detailType = 'day2' THEN X.type END) AS condition2,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day2' THEN X.filename END)) AS icon2,
MAX(CASE WHEN W.detailType = 'day3' THEN W.dayOfWeek END) AS day3,
MAX(CASE WHEN W.detailType = 'day3' THEN W.temp END) AS temp3,
MAX(CASE WHEN W.detailType = 'day3' THEN W.temp2 END) AS low3,
MAX(CASE WHEN W.detailType = 'day3' THEN W.pop END) AS pop3,
MAX(CASE WHEN W.detailType = 'day3' THEN X.type END) AS condition3,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day3' THEN X.filename END)) AS icon3,
MAX(CASE WHEN W.detailType = 'day4' THEN W.dayOfWeek END) AS day4,
MAX(CASE WHEN W.detailType = 'day4' THEN W.temp END) AS temp4,
MAX(CASE WHEN W.detailType = 'day4' THEN W.temp2 END) AS low4,
MAX(CASE WHEN W.detailType = 'day4' THEN W.pop END) AS pop4,
MAX(CASE WHEN W.detailType = 'day4' THEN X.type END) AS condition4,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day4' THEN X.filename END)) AS icon4,
MAX(CASE WHEN W.detailType = 'day5' THEN W.dayOfWeek END) AS day5,
MAX(CASE WHEN W.detailType = 'day5' THEN W.temp END) AS temp5,
MAX(CASE WHEN W.detailType = 'day5' THEN W.temp2 END) AS low5,
MAX(CASE WHEN W.detailType = 'day5' THEN W.pop END) AS pop5,
MAX(CASE WHEN W.detailType = 'day5' THEN X.type END) AS condition5,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day5' THEN X.filename END)) AS icon5,
MAX(CASE WHEN W.detailType = 'day6' THEN W.dayOfWeek END) AS day6,
MAX(CASE WHEN W.detailType = 'day6' THEN W.temp END) AS temp6,
MAX(CASE WHEN W.detailType = 'day6' THEN W.temp2 END) AS low6,
MAX(CASE WHEN W.detailType = 'day6' THEN W.pop END) AS pop6,
MAX(CASE WHEN W.detailType = 'day6' THEN X.type END) AS condition6,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day6' THEN X.filename END)) AS icon6
FROM weather_city_details W
JOIN weather_city C ON C.id=W.cityID
JOIN weather X ON X.ID=W.weatherID
WHERE W.detailType IN ('current', 'day1', 'day2', 'day3', 'day4', 'day5', 'day6') AND
C.cityName=@City
GROUP BY W.cityID |
Branding Schedule Samples
|
|
|
|---|
| Prime Time Schedule Lineup | Prime Time Schedule | SELECT P.name, P.hashtag, S.start FROM channel C, program P, program_schedule S
WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name='BL Demo Channel'
AND Date(S.start)=Date(now()) AND Time(S.start)>Time('19:00:00') AND Time(S.start)<Time('23:00:00') |
|
| SELECT P.name, P.hashtag, S.start FROM channel C, program P, program_schedule S
WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name='BL Demo Channel'
AND Date(S.start)=Date(now()) AND Time(S.start)>Time('19:00:00') | Branding - Next on Channel | Next on Channel using parameters | SELECT P.name, S.start FROM channel C, program P, program_schedule S
WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name=@Channel /*Demo Central XPR*/ AND S.start > now()
ORDER BY S.start LIMIT 1 |
|