Question

Question 2: SQL-5 marks Using the GAMEMEMBERS relational schema below a) List the order in which the tables were created? b) Write the CREATE TABLE statement for MEMBER table. Use appropriate data types and size. HINT please look at the sample data in part c LEVEL GAME Levelp Level GAMEName GAMEMEMBER GAMEMemberiD MemberiD GAMEID Leyelp Date MEMBER MemberiD FName LName Address Suburb State PostCode Phone RegionlD REGION RegionName RegionID c) Write a SQL statement that will insert the following data into the GAMESMEMBER table | GAMEİD GAME MemberID | MemberID LevellD Date 20 Aug 12 21 Aug 12 21 Aug 12 22 Aug 12 0101 0102 0103 0104 s001 so01 s002 $003 M898 M898 M222

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server 2014.Database and Tables are created using SQL Server Management Studio (SSMS).

Question a: Below is the list in the order table needs to be created.

1. GAME
2. LEVEL
3. REGION
4. MEMBER
5. GAMEMEMBER

Question b:

1.Table Name :GAME

--Table Name:GAME
create table GAME(
gameID varchar(4) primary key,
GameName varchar(10)
);

2.Table Name :LEVEL

--Table Name:Level
create table level(
LevelID char(1) primary key,
Level varchar(10));

3.Table Name :REGION

create table region (
RegionID int primary key,
RegionName varchar(20)
);

4.Table Name :MEMBER

create table member(
memberID varchar(4) primary key,
FName varchar(20) ,
LName varchar(20) ,
Address varchar(30) ,
suburb varchar(20) ,
State varchar(20),
PostalCode varchar(20),
RegionID int foreign key references region(RegionID)
);

5.Table Name :GAMEMEMBER

--Table Name :GameMember
create table GameMember(
GamesMemberID int primary key,
MemberID varchar(4) ,
GameID varchar(4),
LevelID char(1),
Date date,
foreign key (GameID) references Game(GameID),
foreign key (MemberID) references Member (MemberID),
foreign key (LevelID) references Level (LevelID)
);
Question 3 :

--insert record into game table
insert into game values('S001',null);
insert into game values('S002',null);
insert into game values('S003',null);

--insert record into level table
insert into level values('A',null);
insert into level values('B',null);
insert into level values('C',null);

--Insert records into members table
insert into member (memberID) values ('M898');
insert into member (memberID) values ('M888');
insert into member (memberID) values ('M222');

--Insert records into Gamemember table
insert into Gamemember values (0101,'M898','S001','A','2016/08/20');
insert into Gamemember values (0102,'M888','S001','C','2012/08/21');
insert into Gamemember values (0103,'M898','S002','B','2012/08/21');
insert into Gamemember values (0104,'M222','S003','C','2012/08/22');

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.