Question

You are given a database schema as follows:

Transaction(TID, Staff, Shop) Item(ItemCode, Description, Price) TransItem(TID, ItemCode, Quantity)

Sample data of the tables are shown below:

TID	Staff	Shop
1	Ann	1
2	Ann	1
3	Ben	2

Transaction

ItemCode	Description	Price
A1	Milk	6
B1	Bread	10
B2	Chocolate	20

Item

TID	ItemCode	Quantity
1	A1	1
1	B2	2
2	B2	1
3	A1	4
3	B2	1

TransItem

Assumptions:

1. TID, Shop and Price are integers
2. ItemCode contains a letter followed by number with at most 3 digits
3. Staff and Description are string with at most 255 characters

1. Write down the SQL statements for creating the above tables database.

Note: You can ignore the primary key setting in this question.

b. Write down the SQL statements for inserting the sample data to your tables.

Answer 1

Below are the queries required and the explanations;

For creating any tables we should have a datbase so we create a database using CREATE DATABASE Command.

CREATE DATABASE PurchaseDB;

The CREATE TABLE statement is used to create a new table in a .Database

Below is the syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

1.Creating a Transactions table : to create a table we need to use Create keyword like below

CREATE TABLE Transactions
(
TID int,
Staff varchar(255),
Shop int
);

2. Creating Item table

CREATE TABLE Item
(
ItemCode varchar(50),
Description varchar(255),
Price int
);

3. Creating TransItem Table

CREATE TABLE TransItem
(
TID int,
ItemCode varchar(50),
Quantity int
);

Now we will start inserting data into the tables we cretaed above. To insert data into a table use Insert Command

Insert Data into Transactions table:

insert into Transactions values(1,'Ann',1)
insert into Transactions values(2,'Ann',1)
insert into Transactions values(3,'Ben',2)

Insert Data into Item table:

insert into Item values('A1','Milk',6)
insert into Item values('B1','Bread',10)
insert into Item values('B2','Chocolate',20)

Insert Data into TransItem table

insert into TransItem values(1,'A1',1)
insert into TransItem values(1,'B2',2)
insert into TransItem values(2,'B2',1)
insert into TransItem values(3,'A1',4)
insert into TransItem values(3,'B2',1)