how can I write this SQL query using that code?
- CREATE Two SQL CREATE VIEW Statements. One of the CREATE VIEW Statements must use two joined tables.
- Use the two created VIEWS in two different SELECT Statements. One created VIEW per SELECT Statement.
- Use the SHOW FULL TABLES Statement to list the stored VIEWS in the Database
Relational schema
The primary key is bold and the foreign key is italic
Region(regionID, regionName, director)
StateOfRegion(stateCode, stateName, statePopulation,RegionID)
Store(StoreID, phone,manager, address,regionID)
Employee(empID,storeID, empName, hireDate, birthDate, socSecNum)
Supplier(supplierID, SupplierName, contact, email, phone)
StoreSupplier(
storeID, supplierID
, startDate)
- Meta dataThe metdata for each table from the given ER diagram is as below
Table Name
|
Attribute
|
Data Type
|
Constraint
|
Region
|
|
|
|
|
regionID
|
INT
|
PK
|
|
regionName
|
VARCHAR
|
NOT NULL
|
|
director
|
VARCHAR
|
|
|
|
|
|
StateOfRegion
|
|
|
|
|
stateCode
|
CHAR(2)
|
PK
|
|
stateName
|
VARCHAR
|
Not NULL
|
|
statePopulation
|
double
|
|
|
regionID
|
int
|
FK Region(RegionID)
|
|
|
|
|
Store
|
|
|
|
|
storeID
|
int
|
PK
|
|
phone
|
VARCHAR
|
Not Null
|
|
manager
|
int
|
Not Null
|
|
address
|
VARCHAR
|
Not Null
|
|
regionID
|
int
|
FK Region(RegionID)
|
|
|
|
|
Employee
|
|
|
|
|
empID
|
Int
|
PK
|
|
storeID
|
int
|
FK Store(StoreID)
|
|
empName
|
VARCHAR
|
Not Null
|
|
hireDate
|
Date
|
Not Null
|
|
birthDate
|
Date
|
|
|
socSecNum
|
Int
|
|
|
|
|
|
Supplier
|
|
|
|
|
supplierID
|
INT
|
PK
|
|
supplierName
|
VARCHAR
|
Not null
|
|
contact
|
VARCHAR
|
Not null
|
|
email
|
VARCHAR
|
Not null
|
|
phone
|
VARCHAR
|
Not null
|
|
|
|
|
StoreSupplier
|
|
|
|
|
storeID
|
Int
|
PK, FK store(StoreID)
|
|
supplierID
|
int
|
PK, FK Supplier(SupplierID)
|
|
startDate
|
Date
|
Not null
|
DROP DATABASE IF EXISTS RegionStore;
CREATE DATABASE RegionStore;
USE RegionStore;
CREATE TABLE Region
(
regionID VARCHAR(10),
regionName VARCHAR(50),
supervisor VARCHAR(50),
CONSTRAINT PRIMARY KEY (regionID)
);
CREATE TABLE Store
(
storeID VARCHAR (10),
storeAddress VARCHAR (50),
phone VARCHAR (10),
Manager VARCHAR (10),
regionID_FK VARCHAR (10),
CONSTRAINT PRIMARY KEY (storeID),
CONSTRAINT FOREIGN KEY (regionID_FK)
REFERENCES REGION (regionID)
);
CREATE TABLE Employee
(
empNum VARCHAR (10),
empName VARCHAR (50),
hireDate DATE,
birthDate Date,
socSecNum CHAR(9),
storeID_FK VARCHAR(10),
CONSTRAINT PRIMARY KEY (empNum),
CONSTRAINT FOREIGN KEY (storeID_FK)
REFERENCES Store(storeID)
);
CREATE TABLE Supplier
(
supplierID VARCHAR (10),
supplierName VARCHAR (50),
contact VARCHAR (50),
phone CHAR (10),
email VARCHAR (50),
CONSTRAINT PRIMARY KEY (supplierID)
);
CREATE TABLE Supplies
(
supplierID_FK VARCHAR (10),
storeID_FK VARCHAR (10),
startDate DATE,
CONSTRAINT PRIMARY KEY (supplierID_FK,storeID_FK )
);
CREATE TABLE StateOfRegion
(
stateCode CHAR(2),
stateName VARCHAR(50),
regionID_FK VARCHAR(10),
statePopulation INT,
CONSTRAINT PRIMARY KEY (stateCode),
CONSTRAINT FOREIGN KEY (regionID_FK )
REFERENCES REGION(regionID)
);
INSERT INTO Region (regionID,regionName,supervisor)
VALUES ('001','Alabama','Henry');
INSERT INTO Region (regionID,regionName,supervisor)
VALUES ('003','wellington','katty');
INSERT INTO Region (regionID,regionName,supervisor)
VALUES ('004','hamilton','ross');
INSERT INTO Region (regionID,regionName,supervisor)
VALUES ('005','auckland','robert');