Since the tags aren't as obvious using query, you can always assign the AS statement ie: storyText as story.content
Story 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%' |
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') |
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 |
JSON Samples
Supported 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; |
Tweet Samples
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 |
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') | ||