CS307-Assignment 3 Complex Queries v3 Solved

As a student at the SUSTech, we often use the Shenzhen Metro and buses. In this assignment, we use the data of Shenzhen subway and bus for exercise.

Problem 1                                                                        
Which stations in Line 1 are not in Line 2, please output the id of those stations in ascending order

ᬌ᧗ڊӞݩᕚӤጱߺԶᒊӧࣁԫݩᕚӤ,᧗܋ଧᬌڊ᫣ᒊidҘ

Sample Output:

 

 

 

Problem 2                                                                        
**The stations on Line 1 distributed in which district? How many stations are there in each district? Please output the district names, the number of stations, and the ranking.

Order of result set is ignored by Testing script

**ӞݩᕚӤጱ᫣ᒊ᮷ߺࣁ૲ړԶ܄ҘᬯԶ܄ྯӻํग़੝ӻંԭ1ݩᕚጱ᫣ᒊҘ᧗ᬌڊᬯԶݷ܄҅᫣ᒊහ ഭ݊զ҅ᰁݷ̶ ၥᦶ෸஺ኼഭଧᳯ᷌

Sample Output:

 

Problem 3                                                                        
Please output how many subway lines pass through each district and the ranking of the number of subway lines in each district.

Order of result set is ignored by Testing script. and the null district should not take into consideration.

ᬌ᧗ڊྯӻ܄ํग़੝ࣈ᱈ᕚ᪠ᕪᬦ҅զ݊ྯӻ܄ࣈ᱈ᕚ᪠හᰁጱഭݷ̶̶

ၥᦶ෸஺ኼഭଧᳯ᷌, ྦྷਁӾପഝහ܄ҁdistrict҂ԅnullᕑӧᅩᒊጱفᘍᡤ 

Sample Output:

 

Problem 4                                                                        
Please output the subway stations with more than or equal to 10 bus stops around the subway station in each line, sorted by 1. ascending order of line id, 2. ascending order according to the count of bus stops, 3. descending order of station id. Your result set only return 10 rows from the 16th row  (You can use limit 10 offset 15)

ᬌ᧗ڊྯ๵ᕚ᪠Ӿࣈ᱈ᒊޮᬟلԻᒊग़ԭ౲ᒵԭ10ጱࣈ᱈᫣ᒊ҅ଧഭׁഝ 1. line id ጱ܋ଧ҅2. لԻ᫣ ጱහᒊ܋ଧ҅ 3. station id ጱᴳଧ̶ᬬࢧᕮຎᵞ՗ᒫ16ত୏ᤈכኸ10ᤈ

Sample Output:

 

Problem 5                                                                        
 Once upon a time, a new intern comes to Shenzhen metro department to design new stations. As a biology under-graduate, the intern is asked to simply give names to stations instead of designing routes. But the master of department is a strange guy who dislikes the station starts with the same character. For example, if some station starts with 'Ⴎ' such like 'Ⴎԯ' and another station named 'Ⴎय़', then the frequency of this character is 2 (in fact considering all stations, 'Ⴎ' presents 7 times).

 However, human beings' ability has the limits and thus the intern cannot find new names with never-used start character. So the master relaxed the limitation and if the name doesn't start with the character that shows up the most times, then the name is valid. However, the limitation asks for 'the most frequency for each district'. So, although the character 'ਪ' is not valid in Bao'an district, it's valid in Nanshan district.

 Even worse, some district has the same frequency for some characters. For example, 'Ⴎ', '܏', and 'ḕ' are all presents 4 time in Futian district, and all of them are not valid.

 The intern has this task daily and nightly in his thought. Finally, on a stellar scintillation night, he had a dream about a short paragraph of SQL code that can list districts, all the most frequency words for each district, and their frequencies. On the next day, as his best friend, you are asked to write this code for him.

 Task: find the most frequency starting characters of stations for each district. For example, in Nanshan district, you should find all the stations in Nanshan district. And count the first character's present time, and get the highest frequency characters' district, character (chr), and present time (cnt).

 Hint 1: characters with the same pinyin but not with the same form doesn't same.

 Hint 2: the null district should not take into consideration.

Order of result set is ignored by Testing script

 ຤ॠ҅ੜA᩸ᒊ᱈ࣈෛԅᨱᨮ҅ԟਫᳪ᮱᱈ࣈࣉႮࣁݷ̶ᆐᘒ҅୮ݷ໏ݶڹਁጱࣈ᱈ᒊਫࣁॡग़ԧ҅ ತᘒکਠقဌአᬦጱݷਁ݈உࢯᵙ҅ੜAݝమ᭿ع୏१ᒫӞӻਁڊሿஉग़ེጱ̶ֺਁݷই҅Ⴎԯ޾Ⴎय़ ᮷զ“Ⴎ”୏१҅ԭฎ҅“Ⴎ”ᚆӧ֢ԅෛݷਁጱ୏१̶

 ֕Ոᔄጱᚆێฎํຄᴴጱ҅ԭฎੜAᓒ಑ݝ݄ധڊሿ๋ग़ེጱᮎӻ୏१ਁ҅ࣁ୮ڹఘ٭ӥ҅ݝํ“Ⴎ”ӧ ᒧݳᥝ࿢̶֕ฎ҅՜ᬮ಑ᓒ੒ྯӻ܄ತکᧆڊ܄ሿ๋ग़ጱ୏१ਁֺ҅ইਪਞ܄ԅ“ਪ”҅ܖઊ܄ԅ“Ⴎ”̶ᥝ ጱํ҅఺ဳݢ܄ᚆ۱ތग़ӻݶ໏ེහጱਁֺ҅ইᐰኦ܄ጱ“Ⴎ”̵“܏”̵“ḕ”࣐֢ԅࣈ᱈ᒊ୏१ਁڊሿԧࢥ ӧฎ᮷ਁԶᬯᘒེ҅ݳဩጱ̶

 ੜAತ᯾໼ॠํԭᕣమज़௏෭کԧӞྦྷSQL᧍ݙ҅๶ଆ՜ತکྯӻڊٖٖ܄ሿ๋ग़ེጱ୏१ਁ̶ᘒ֦ฎ ੜAಓ೘՜ฎԭ҅݋๏ጱঅ๋ڊٟڊٟ֦ᬯྦྷSQL᧍ݙ̶

 ձۓғԅྯӻ܄ತک᫣ᒊݷਁӾڊሿེහ๋ग़ጱ୏१ਁҁݢᚆํग़ӻ҂̶ֺই҅੒ܖઊ܄ᵱᥝತکಅ ํܖԭ֖ઊ܄ጱࣈ᱈ᒊ҅ଚᕹᦇݷਁӾᒫӞӻਁڊሿጱེහ҅ଚਖ਼๋ग़ེහ੒ଫጱ܄ҁdistrict҂̵ਁ ҁchr҂̵ེහҁcntᬌ҂ڊ̶

 ғӞᐏ൉ݶ໏೪ᶪ֕ਁ୵ӧݶጱਁӧᦊԅฎݶ໏ጱਁ̶

 ྦྷਁӾପഝහғԫᐏ൉܄ҁdistrict҂ԅnullᕑӧᅩᒊጱفᘍᡤ̶

ၥᦶ෸஺ኼഭଧᳯ᷌

Sample Output:

district
chr
cnt
Bao'an
ਪ
4
Futian
ᐰ
4
Futian
܏
4
Futian
ḕ
4
Longgang
य़
2
Longhua
ὄ
2
Longhua
࿆
2
Luohu
ᕁ
2
Nanshan
Ⴎ
5