IT211 Structured Query Language (Ms Access)

Assessment

-------------

Assignment : 30%

Project: 70% (NO Turnitin requirement)

This unit does NOT require Turnitin.

You will need to decompress the Students.zip file before use.

<Access isn't really considered a full-fledged database but it can be used to learn Microsoft based SQL statement>

It is always better to create your own database. If you need some idea, the following video clip might help.

If you are using decimal, you may want to view this clip:

This is the playlist for Ms Access. It covers the primary key and foreign key.

https://www.youtube.com/playlist?list=PLrHVSJmDPvlr71crD2-ncO5GSn0wPRZ5d

I am usually more concern about the data than the formats.

However, you you really want to fix the trailing zeros after the decimal, here's the solution.

Chapter 3 SQL Select statement

------------------------------------

Projection is the selection of column to retrieve.

Chapter 8 Joining Tables

---------------------------

Most SQL supports 4 types of joins:

• Equijoin
• Non-Equijoin
• Self join
• Outer Join

Project Documentation

Database Description

------------------------

Description of the database used in the project.

ER diagram to describe the tables relationship.

Table Description

-------------------

SQL for creating table:

CREATE TABLE Employee

(

Employee_IC_No VARCHAR(9) NOT NULL,

Employee_Name VARCHAR(30),

Date_of_Birth VARCHAR(12),

Department_Name VARCHAR(20) NOT NULL,

Designation VARCHAR(15),

Date_of_Joining VARCHAR(30),

Salary Number);

Give a description for every table in your database.

Your description must include the followings:

-Purpose of table

-Column’s purpose

-Column’s Datatype and width of the column

-Column’s attribute (eg. primary key, foreign key etc)

SQL for inserting record into a table:

INSERT INTO EMPLOYEE Values ('A1230001Z ', ' John', '12-11-1990', 'Sales', 'Assistant','02-January-2015', 30000.00);

Marking Template

--------------------

You may find the marking template useful:

Project Submission requirement:

• IT211 Documentation
• Database (containing tables and SQL queries)
• PPT slides (about 10 - 12 slides)