SQL Examples

List all members of the EmailList

SELECT * FROM EmailList

Changing the Name of a EmailList member

UPDATE EmailList SET Name = 'First Last' WHERE Email = 'name@university.ac.jp'

Adding a program committee member

INSERT INTO CommitteeMember VALUES (5,'First Last','','C','University','','','Japan','loginname','password','','','N')

Listing all committee members interest

SELECT Name, Description FROM CommitteeMember, MemberTopic, ResearchTopicList WHERE MemberTopic.Topic = ResearchTopicList.Topic AND CommitteeMember.MemberId = MemberTopic.MemberId

List paper interest with for all committee members

SELECT * FROM MemberInterest WHERE Level = '1' ORDER BY MemberID

Writing all emails into a file

SELECT Email FROM EmailList INTO OUTFILE '/tmp/CONFERENCEan.list'

Reset the password of a program committee member

UPDATE CommitteeMember SET Password = 'new' WHERE MemberID = '5'

Listing topics associated to the papers

SELECT PaperTopic.PaperID, Authors, Title, Description FROM Paper, PaperTopic, ResearchTopicList WHERE PaperTopic.Topic = ResearchTopicList.Topic AND Paper.PaperID = PaperTopic.PaperID ORDER BY PaperID

List papers which do not have an AcceptCode (undecided)

SELECT PaperID FROM Paper WHERE AcceptCode !='Y' AND AcceptCode !='N'

Show the number of assigned papers per reviewer

SELECT Name,COUNT(Name) AS AssignedReviews FROM AssignedReview, CommitteeMember WHERE AssignedReview.MemberID = CommitteeMember.MemberID GROUP BY Name ORDER BY Name

Show the number of reviews (done) per reviewer

SELECT Name,COUNT(Name) AS AssignedReviews FROM AssignedReview, CommitteeMember, Review WHERE AssignedReview.MemberID = CommitteeMember.MemberID AND Review.MemberID = AssignedReview.MemberID AND Review.PaperID = AssignedReview.PaperID GROUP BY Name ORDER BY Name

Documentation

Trouble Shooting

Duplicated entry "" for key 1

During the submission of one paper something went wrong. The paper was not in the database and could not be uploaded. The sql server reported a duplicated key.

Checking database tables

cd /usr/local/var/HC2000 /usr/local/bin/isamchk PaperBody

Repairing database tables

/etc/rc.d/init.d/mysql.server stop /usr/local/bin/isamchk -r PaperBody /etc/rc.d/init.d/mysql.server start