- Creating databases and SQL
sentences
- Create database with tables you "used" in
earlier exercise.
- LOGIN field has initials on nickname, which can be
used in search to speed it up. Member type could be like "Full member",
"junior member", "honourary member", etc. Position could be like
"President", "Secretary", "Treasurer", etc.
- Create SQL sentences for tables:
TABLE: MEMBER
Field
Type
- MEMBERID INTEGER NOT
NULL
- LOGIN
VARCHAR(16)
- FIRSTNAMES VARCHAR(32)
- LASTNAME VARCHAR(32)
- TYPEID
INTEGER NOT NULL
- POSITIONID INTEGER
NOT NULL
- PHONE
VARCHAR(16)
- EMAIL
VARCHAR(32)
- MEMBERID is the PRIMARY KEY of table
- LOGIN field values should be only "simple",
unambiguous inputs
TABLE: MEMBERTYPE
Field
Type
- MEMBERTYPEID INTEGER NOT
NULL
- LOGIN
VARCHAR(8)
- NOTES
VARCHAR(32)
- MEMBERTYPEID is the PRIMARY KEY of table
- LOGIN field values should be only "simple",
unambiguous inputs
TABLE: POSITION
Field
Type
- POSITIONID
INTEGER NOT NULL
- LOGIN
VARCHAR(8)
- NOTES
VARCHAR(32)
- POSITIONID is the PRIMARY KEYof table
- values of field LOGIN have to be ‘simple’
- Create SQL sentences needed for INSERT, UPDATE and
DELETE functions.
UPDATE and DELETE sentences uses ID fields to create terms.
- Add 3 rows/records into tables MEMBERTYPE and
POSITION, 10 – 20 rows/records into table MEMBER.
- Create SQL sentence, which gets all the members with
their member positions & types.
- Create SQL sentences, which can be used to count
members by their types and positions.
<Top>