All the questions and the sample result sets in this assignment are using postgres database, and you can import data from shenzhen_metro.sql

Your result set, especially the data type and the order of each column, must strictly follow the description and the sample result set in each question.

You need submit .sql files for these five questions.

The name of each .sql file should be q1, q2, q3, q4, q5 respectively to represent these five questions.

Do not forget to add ; in the end of each query.

Do not compress them into a folder, please submit them directly.

Please submit those queries into sakai website as soon as possible, so that you can get chance to receive feedback before deadline. After the deadline, we will check the assignment automatically by a script and then given your grade, at that time, any argument about your grade of this assignment will not be accepted.

Description:

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.

, districtnull

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 1610

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 doesnt 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 Baoan district, its 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 characters 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 doesnt same.

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

Order of result set is ignored by Testing script

A A

A

ASQL ASQL

district chrcnt

districtnull

Sample Output: