個人輔導網站：tutoryou輔導網站
在做作業之前，給同學提一些建議。
- 每一道題最重要的是所定義的qn命名的表或者函數，是以，在函數前面或者view前面，可以盡情定義view，友善在後面使用。（
函數内部定義view時，這個view難以使用函數參數
- 在plpgsql的函數内部，想使用參數，盡量用$1或者$2，筆者在使用函數參數的名字的時候，總是出現bug。
- 使用create or replace建立view，如果兩次定義的類名字或者類型不一樣，則無法類型轉換，是以也許把create or replace view換成drop view再create view更好。
- psql的log可能會很大，如果出現
Disk quota exceeded linux
配置環境
有兩個資料庫,mymy1和mymy2，這兩個資料庫分别使用。隻有Q5不一樣。需要定義不同的Q5。
cd /localstorage/YOUID
ls
code .
cp /home/cs3311/web/22T1/assignments/ass1/ass1.sql ass1.sql
dropdb mymy
source ./env
dropdb mymy
createdb mymy
bzcat /home/cs3311/web/22T1/assignments/ass1/mymy1.dump.bz2 | psql mymy
cp /web/cs3311/22T1/assignments/ass1/check1.sql . 了解資料和限制schema
很多資訊可以通過通讀一遍spec獲得，不會也可以問老師。這裡對一些老師忽略的東西做一些補充。
寫query
select * from dbpop();
tab_name | n_records
---------------------------+-----------
acad_object_groups | 17101
academic_standing | 8
affiliations | 12385
books | 0
buildings | 115
class_enrolment_waitlist | 0
class_enrolments | 0
class_teachers | 0
class_types | 16
classes | 163332
countries | 237
course_books | 0
course_enrolment_waitlist | 0
course_enrolments | 525688
course_quotas | 0
course_staff | 44083
courses | 73220
degree_types | 13
degrees_awarded | 0
external_subjects | 0
facilities | 26
orgunit_groups | 537
orgunit_types | 10
orgunits | 648
people | 55767
program_degrees | 1193
program_enrolments | 193456
program_group_members | 2360
program_rules | 1422
programs | 1324
public_holidays | 0
q10a_expected | 1
q10b_expected | 4
q10c_expected | 11
q1_expected | 16
q2_expected | 1
q3_expected | 1
q4_expected | 9
q5a_expected | 1
q6a_expected | 1
q6b_expected | 1
q6c_expected | 1
q7a_expected | 18
q7b_expected | 28
q7c_expected | 18
q8a_expected | 0
q8b_expected | 12
q8c_expected | 5
q9a_expected | 1
q9b_expected | 12
q9c_expected | 27
room_facilities | 1461
room_types | 8
rooms | 1100
rules | 9183
staff | 24405
staff_role_classes | 9
staff_role_types | 7
staff_roles | 884
stream_enrolments | 205068
stream_group_members | 3597
stream_rules | 1516
stream_types | 6
streams | 2077
student_groups | 0
students | 31361
subject_group_members | 32597
subject_prereqs | 6245
subjects | 17779
terms | 64
variations | 0
(72 rows) program_enrolments
mymy=# select * from program_enrolments limit 5;
id | student | term | program | wam | standing | advisor | notes
--------+---------+------+---------+-----+----------+---------+-------
462583 | 1010093 | 126 | 687 | | | |
464457 | 1010093 | 127 | 687 | | | |
469823 | 1010093 | 134 | 1157 | | | |
471345 | 1010093 | 137 | 1157 | | | |
473239 | 1010093 | 138 | 1157 | | | |
(5 rows) people
| unswid | password | family | given | title | sortname | name | street | city | state | postcode | country | homephone | mobphone | email | homepage | gender | birthday | origin
----------+---------+----------+----------+--------------+-------+---------------------+-----------------+--------+------+-------+----------+---------+-----------+----------+------------------------+----------+--------+----------+--------
10000019 | 8758024 | abc123 | Schubert | Emery | AProf | Schubert Emery | Emery Schubert | | | | | | | | [email protected] | | | |
10000021 | 9808692 | abc123 | Moore | Ann Louise | Ms | Moore Ann Louise | Ann Moore | | | | | | | | [email protected] | | | |
10000025 | 9833783 | abc123 | Chang | Zhen-Tian | Dr | Chang Zhen-Tian | Zhen-Tian Chang | | | | | | | | [email protected] | | | |
10000026 | 7610575 | abc123 | Carrick | John Wesley | Mr | Carrick John Wesley | John Carrick | | | | | | | | [email protected] | | | |
10000035 | 9837669 | abc123 | Mort | Pamela Ellen | Ms | Mort Pamela Ellen | Pamela Mort | | | | | | | | [email protected] | | | |
(5 rows) 寫測試
psql mymy -f /web/cs3311/22T1/assignments/ass1/check1.sql
mymy=# select * from dbpop();檢視所有表格
select check_q1();
select * from q2_expected;
select * from check_all(); Q1
- 先檢視expect(), 明白正确的輸出是什麼樣的。
- 看涉及到的相關的表格
- 分步驟創造view，篩選。