2. Create two tables called YourName_STORES and SALES. Use the data dictionary b
ID: 3677106 • Letter: 2
Question
2.Create two tables called YourName_STORES and SALES. Use the data dictionary below to create these tables.
TABLE
attribute
data type
Null?
Key
YourName_STORES
StoreCode
char(5)
Not Null
PK
Name
varchar(40)
Not Null
Address
varchar(40)
Not Null
City
varchar(20)
Not Null
State
char(2)
Not Null
Zip
char(5)
Not Null
SALES
OrderNumber
varchar(20)
Not Null
PK
StoreCode
char(5)
Not Null
FK (ref Table: YourName_STORES)
OrderDate
date
Not Null
Quantity
int
Not Null
Terms
varchar(12)
Not Null
TitleID
int
Not Null
Submit the SQL statements used to create these tables.
3.Add a record to the YourName_STORES table with the following information:
StoreCode = IT330, Name = Test_YourName, Address = 1234 Somewhere Street, City = Here, State = MA, Zip = 00333.
Submit the SQL statement used to accomplish this task.
Write an SQL statement to validate the record added in the previous step. Submit the SQL statement used to accomplish this task.
4.Add a record to the SALES table with the following information:
OrderNumber = TESTORDER, StoreCode = IT330, OrderDate = 01/01/2014, Quantity = 10, Terms = NET 30, TitleID = 1234567
What is the SQL statement you used to do this task?
Write a SQL statement to validate the record added in the previous step. Submit the SQL statement used to accomplish this task.
TABLE
attribute
data type
Null?
Key
YourName_STORES
StoreCode
char(5)
Not Null
PK
Name
varchar(40)
Not Null
Address
varchar(40)
Not Null
City
varchar(20)
Not Null
State
char(2)
Not Null
Zip
char(5)
Not Null
SALES
OrderNumber
varchar(20)
Not Null
PK
StoreCode
char(5)
Not Null
FK (ref Table: YourName_STORES)
OrderDate
date
Not Null
Quantity
int
Not Null
Terms
varchar(12)
Not Null
TitleID
int
Not Null
Explanation / Answer
2.
The queries to create the tables with the specified attributes are as follows:
create table YourName_STORES(StoreCode char(5),Name varchar(40),Address varchar(40),City varchar(20), State char(2),Zip char(5), PRIMARY KEY(StoreCode));
create table SALES(OrderNumber varchar(20),StoreCode char(5), OrderDate date,Quantity int, Terms varchar(12), TitleID int, PRIMARY KEY(OrderNumber),FOREIGN KEY(StoreCode) REFERENCES YourName_STORES(StoreCode));
3.
The query to add a record to the YourName_STORES table is,
INSERT INTO YourName_STORES VALUES("IT330","Test_YourName","1234 Somewhere Street","Here","MA",00333);
To validate the record, the following query can be used.
select * from YourName_STORES where StoreCode="IT330";
4.
The query to add a record to the SALES table is,
INSERT INTO SALES VALUES("TESTORDER","IT330",01/01/2014,10,"NET 30",1234567);
To validate the record, the following query can be used.
select OrderNumber from SALES where OrderNumber="TESTORDER";
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.