Table: Countries

1
Table Countries

• Field Name
• Name
• Area
• Population
• UnitedNationMembers
• JoinedUN
• SecurityCouncilMember

• Data Type
• Text
• Number
• Number
• Yes/No
• Date
• Yes/No

2
SQL Statements

• SELECT FROM tablename
• SELECT SUM(fieldname) as newfield FROM tablename
• DELETE FROM tablename WHERE ..
• UPDATE tablename SET f1Value1, f2Value2
• INSERT INTO tablename (f1,f2,f3) VALUES
  (v1,v2,v3)

3
Execute SQL Statements

• 1. Select Create Query in Design View
• 2. Click Close (Cancel)
• 3. Click menu View / SQL view
• 4. Type in SQL statement
• 5. Click menu Query / Run

4
STRING NUMBER FIELDS

• Select countries is Malaysia
• Select all fields where countries start with B
• Select field - Name Area where countries start
  with B
• Select all fields where countries start with B
  and area gt 1M
• Select field - Name Area where countries start
  with B

5
DATE FIELD

• Select all fields where JoinedUN gt 1-Jan-1960
• Select all fields where JoinedUN gt 1-Jan-1970
• Select all fields where JoinedUN between
  1-Jan-1970 and less than 1-Jan-1980

6
AND, OR, NOT

• Select all fields where Area gt 1 Mand
  Population gt 1 M
• Select all fields where Area gt 1 Mor
  Population gt 1 M
• Select all fields where JoinedUN is above or
  equal to 1-Jan-1980
• Select all fields where JoinedUN not above or
  equal to1-Jan-1980

7
UPDATE

• Update status x
• Update status asean where ..
• Update status

8
SUM, COUNT, MAX, MIN

• Count number of records
• Count number of records where name B
• Count number of records where areagt1M
• Count number of records and sum area that are
  greater or equal to 1M
• Find highest area
• Find highest area and the country name