1.Create a view that includes all of the fields from tblCompanies and tblContact
ID: 3719427 • Letter: 1
Question
1.Create a view that includes all of the fields from tblCompanies and tblContacts.
2.Using the view created in #1, provide the top 5 rows.
3.Go back and make 2 comments in #1.
4.Using the view, create a new table that has all the information for North Dakota
**Using the ECCS database structure, write the SQL code to provide the result set requested.
tblCompanies
CoID
AutoNumber
CoName
Short Text
CoAdd
Short Text
City
Short Text
St
Short Text
Zip
Short Text
CoPhone
Short Text
CoTollFree
Short Text
CoFax
Short Text
CoEmail
Hyperlink
CoComment
Short Text
EDate
Date/Time
EBy
Short Text
MDate
Date/Time
MBy
Short Text
tblContacts
ContactID
AutoNumber
CoID
Number
FName
Short Text
LName
Short Text
SSN
Short Text
Title
Short Text
MContact
Yes/No
DPhone
Short Text
DCell
Short Text
DEmail
Hyperlink
CEBy
Short Text
CEDate
Date/Time
CMBy
Short Text
CMDate
Date/Time
tblEntries
EID
AutoNumber
ContactID
Number
Drug
Date/Time
OSHA
Date/Time
MSHA
Date/Time
ReInstate
Date/Time
EEBy
Short Text
EEDate
Date/Time
EMBy
Short Text
EMDate
Date/Time
tblStats
SID
AutoNumber
CompID
Number
SYear
Short Text
Injuries
Number
DaysLost
Number
Deaths
Number
OSHAIncidents
Number
ESBy
Short Text
ESDate
Date/Time
ESMBy
Short Text
ESMDate
Date/Time
tblCompanies
CoID
AutoNumber
CoName
Short Text
CoAdd
Short Text
City
Short Text
St
Short Text
Zip
Short Text
CoPhone
Short Text
CoTollFree
Short Text
CoFax
Short Text
CoEmail
Hyperlink
CoComment
Short Text
EDate
Date/Time
EBy
Short Text
MDate
Date/Time
MBy
Short Text
Explanation / Answer
ANSWER:-
1.
the view can be created with the command
CREATE VIEW testView
AS
SELECT * FROM tblCompanies a, tblContacts b where a.CoID=b.CoID ;
2. to select top records from the view, we can use the limit keyword
select * from testView limit 5;
3.
we can add comments on the view like this
CREATE VIEW testView
AS -- this is a comment
-- this is a second comments
SELECT * FROM tblCompanies a, tblContacts b where a.CoID=b.CoID ;
4. you can create a new table with the view with the following query
SELECT * INTO newTable FROM testView
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.