4. Queries c. From the Customer table, show Customer Number, Customer Name, Stre

Question

4. Queries

c. From the Customer table, show Customer Number, Customer Name, Street, and City. Select all records where the name starts with a “T” and ends with an “e.” Save as Query 3.

d. From the Sales Reps table, show Last Name and First Name. Create a field called Commission. Calculate this amount by multiplying the Base Pay YTD by the Commission Rate. Change the data type to Currency. Save as Query 4.

e.Join the tables. From the Sales Rep table, show Sales Rep Number, First Name, and Last Name. From the Customer table, show Customer Number, Customer Name, and Balance. Sort the records in ascending order by Last Name. All sales reps should appear, even if they have no customers. Save as Query 5.

*working in access

Customer Customer Number Customer Name Street City State Postal Code Balance Amount Paid Sales Rep Number AM23 Amy's Store 223 Johnson Oxford TN 37021 $195.00 $1,695.00 44 BF34 Barbara's Fashions 1939 Jackson Lowton TN 37084 $150.00 $0.00 51 CM09 Casual by Marie 3140 Halsted Ashton VA 20123 $295.00 $1,080.00 51 CY12 Curlin Yoga Studio 1632 Clark Georgetown NC 28794 $145.00 $710.00 49 DG14 Della's Designs 312 Gilham Granger NC 27036 $340.00 $850.00 44 EC07 Environmentally Casual 1805 Broadway Pineville VA 22503 $0.00 $1,700.00 44 FN19 Fitness Counts 675 Main Oxford TN 37021 $345.00 $1,950.00 51 JN34 Just Natural 2200 Lawrence Ashton VA 20123 $360.00 $700.00 49 LB20 Le Beauty 13 Devon Lowton TN 37084 $200.00 $1,250.00 49 NC25 Nancy's Place 1027 Wells Walburg NC 28819 $240.00 $550.00 44 RD03 Rose's Day Spa 787 Monroe Pineville VA 22503 $0.00 $975.00 51 Total:regular salary+overtime salary Blondie's on Main 3294 Main Oxford TN 37021 $555.00 $1,350.00 49 TS34 Tom's Sporting Goods 578 Central Walburg NC 28819 $1,235.00 $0.00 49 TT21 Tan and Tone 1939 Congress Ashton VA 20123 $160.00 $725.00 44 TW56 The Workout Place 34 Gilham Granger NC 27036 $680.00 $125.00 51

Explanation / Answer

Query3: From the Customer table, show Customer Number, Customer Name, Street, and City. Select all records where the name starts with a “T” and ends with an “e.” Save as Query 3.

select Customer Number,Customer Name,Street,City from Customer where Customer Name Like 'T%e';

Query 4. From the Sales Reps table, show Last Name and First Name. Create a field called Commission. Calculate this amount by multiplying the Base Pay YTD by the Commission Rate. Change the data type to Currency. Save as Query 4.

select Las Name,First Name ,(Base Pay YTD*Comm Rate) as Commission from Sales Reps

Query 5.Join the tables. From the Sales Rep table, show Sales Rep Number, First Name, and Last Name. From the Customer table, show Customer Number, Customer Name, and Balance. Sort the records in ascending order by Last Name. All sales reps should appear, even if they have no customers. Save as Query 5.

select s.Sales Rep Number,s.Last Name,s.First Name ,c.Customer Number, c.Customer Name, c.Balance

from Sales Reps,Customer order by(s.Last Name).

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

---

---

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