Query the Songbook database
Choose one of the available queries.
| AVAILABLE QUERIES | |
|---|---|
| File name | Description or SQL code |
| 01-event.sql | List all the events |
| 02-event_song.sql | List every song included for every event |
| 03-frequency.sql | Lists songs in order of how often they have been recorded as having been sung, most frequent first. |
| 04-weighted_frequency.sql | Lists songs in order of how often they have been recorded as having been sung, but orders by "singer events", not just a simple count, so that events with more people count for more than those with just a few. |
| 10-not_sung_in_3_months.sql | List songs that have not been sung in the last three months, provided that they have been sung at some time with a familiarity score of "fully confident". (That is, songs we know but have not sung recently.) |
| 11-not_sung_in_6_months.sql | List songs that have not been sung in the last six months, provided that they have been sung at some time with a familiarity score of "fully confident". (That is, songs we know but have not sung recently.) |
| 12-not_sung_in_12_months.sql | List songs that have not been sung in the last year, provided that they have been sung at some time with a familiarity score of "fully confident". (That is, songs we know but have not sung recently.) |
| 13-learning.sql | List songs that have been sung in the last six months, but whose familiarity has never been recorded as "fully confident". |
| 19-never_sung.sql | List songs that have not been recorded as having been sung. |
| 20_concerns.sql | List all songs that have concerns listed against them. |
| 21_notes.sql | List all songs that have comments listed against them. |
| 22-notes_and_concerns.sql | List all songs that have either notes or concerns listed against them. |
| 32-sung_in_12_months.sql | List songs that have been sung in the last year |
| 99 disabled checktunes.sql | SELECT 'TUNE DUPLICATION CHECK: songs ', t0.song_number, t0.song_name, 'and', t1.song_number, t1.song_name, 'share a common tune name, but do not both have tune notes entered.' FROM s_songbook t0, s_songbook t1 WHERE t0.tune_name = t1.tune_name and (t0.tune_notes='' or t1.tune_notes='') and t0.song_number < t1.song_number; SELECT 'TUNE DUPLICATION CHECK: songs ', t0.song_number, t0.song_name, 'and', t1.song_number, t1.song_name, 'share a common tune name, but have different tune notes entered.' FROM s_songbook t0, s_songbook t1 WHERE t0.tune_name = t1.tune_name and (t0.tune_notes<> t1.tune_notes) and not (t0.tune_notes='' or t1.tune_notes='') and t0.song_number < t1.song_number; |
| 99 disabled columns.sql | show tables; show columns from s_songbook; show columns from s_familiarity_sources; |
| songbook.sql | MySQL dump 10.11 Host: localhost Database: roster ------------------------------------------------------ Server version 5.0.67 Table structure for table `s_songbook` Dumping data for table `s_songbook` Table structure for table `s_event` Dumping data for table `s_event` Table structure for table `s_event_song` Dumping data for table `s_event_song` Temporary table structure for view `s_songleader` Table structure for table `s_books` Dumping data for table `s_books` Table structure for table `s_event_class` Dumping data for table `s_event_class` Table structure for table `s_event_weight` Dumping data for table `s_event_weight` Table structure for table `s_familiarity` Dumping data for table `s_familiarity` Table structure for table `s_tunes` Dumping data for table `s_tunes` Table structure for table `s_upto` Dumping data for table `s_upto` Final view structure for view `s_songleader` Dump completed on 2009-08-23 8:25:23 |
13 June 2010, 15:44
