Use following information to create SQL queries: create table tblMember ( Member

Question

Use following information to create SQL queries:

create table tblMember (

MemberID integer.

FirstName char(20),
LastName char(20),
Address char(100),
Phone char(10),
email char(20),
JoinDate date,
Level integer

primary key (MemberID)
);

create table tblDonation (
DonationID integer,
MemberID integer,
DonationDate date,
DonationAmt currency
);

1)Create and save a query named qryDistantMembers that displays the FirstName, LastName, Address, City, StateProv, PostalCode, and Phone fields for all members not living in the same city where you live in ascending order by LastName. Print the query recordset in landscape orientation after testing and saving the query.

2) Create and save a query named qryMarch17Donations that displays the FirstName, LastName, Phone, and DonationAmt fields for all donations made on 3/17/2016 in ascending order by LastName. Print the query recordset after testing and saving the query.

3) Create and save a query named qryDonationTotalsByMember that displays each member’s first name, last name, and total donation amount. For the total field, use the name TotalDonated and a Caption value of Total Donated.

Explanation / Answer

1.
select FistName,LastName,Phone,Address,City,StateProv,PostalCode,Phone
from tblMember
where City !='yourcity' order by LastName /*You can replace yourcity with the name of your city*/

2.
select tblMember.FirstName, tblMember.LastName, tblMember.Phone, tblDonation.DonationAmt
from tblMember
inner join tblDonation ON tblMember.MemberID=tblDonation.MemberID
where tblDonation.DonationDate='03-17-2016'

3.
select tblMember.FirstName, tblMember.LastName,tblDonation.DonationAmt as TotalDonated
from tblMember
inner join tblDonation ON tblMember.MemberID=tblDonation.MemberID

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.