6. List the players that have either won more than 2 matches or lost more than 2 matches. List the player number, name, initials, won, and lost. Insert your snip here.

7. Which players are captains of a team and have ever served as a committee members? Display the player number, player name, initials, team number, and committee member position. This will require 3 tables. Insert your snip here.

8. Using a subquery, have any other players been penalized the same amount as player 8? Let MySQL do the work for you. Do not explicitly query for $25. Display the player number, player name, initials, and amount. Insert your snip here.

9. Who has played more than 2 matches? Display the player number as ‘Number’, concatenate the player name (initials, space, name) as ‘Name’, and the number of matches played as ‘Number of Matches’. Insert your snip here.

10. Sum the number of match wins and losses by player number. Display the player number as ‘Number’, concatenate the player name (initials, space, name) as ‘Name’, wins as ‘Total Wins’, and losses and ‘Total Losses’. Insert your snip here.

***********************************************************************************************************************************************

***********************************************************************************************************************************************

***********************************************************************************************************************************************

Create database tennis;

USE tennis;

#--Create table players and fill it--------------------------

Create table players
(
playerno   int       not null    primary key,
name       varchar(15)   not null,
initials   varchar(3),
birth_date   date,
gender       char(1),
joined       int   not null,
street       varchar(15)   not null,
houseno       varchar(4),
zip       char(6),
town       varchar(10)   not null,
phoneno       char(10),
leagueno   char(4)
);

Insert into players values
(2,'Everett','R','1988-01-09','M',2000,'Stoney Road','43','3575NH','Stratford','070-237893','2411'),
(6,'Paramenter','R','1984-06-25','M',2002,'Haseltine Lane','80','1234KK','Stratford','070-476547','8467'),
(7,'Wise','GWS','1983-05-11','M',2006,'Edgecombe Way','39','9758VB','Stratford','070-347689',Null),
(8,'Newcastle','B','1982-07-08','F',2005,'Station Road','4','6584RO','Inglewood','070-458458','2983'),
(27,'Collins','DD','1990-05-10','F',2008,'Long Drive','804','8457DK','Eltham','079-234857','2513'),
(28,'Collins','C','1983-06-22','F',2008,'Old Main 28','10','1294QK','Midhurst','071-659599',Null),
(39,'Bishop','D','1986-10-29','M',2005,'Eaton Square','78','9629CD','Stratford','070-393435',Null),
(44,'Baker','E','1983-09-01','M',2010,'Lewis Street','23','4444LJ','Inglewood','070-368753','1124'),
(57,'Brown','M','1981-08-17','M',2007,'Edgecombe Way','16','4377CB','Stratford','070-473458','6409'),
(83,'Hope','PK','1976-11-11','M',2009,'Magdalene Road','16A','1812UP','Stratford','070-353548','1608'),
(94,'Miller','P','1993-05-14','M',2013,'High Street','33A','5746OP','Douglas','070-867564',Null),
(100,'Parmenter','P','1983-02-28','M',2012,'Haseltine Lane','80','1234KK','Stratford','070-494593','6524'),
(104,'Moorman','D','1990-05-10','F',2014,'Stout Street','65','9437AO','Eltham','079-987571','7060'),
(112,'Bailey','IP','1983-10-01','F',2014,'Vixen Road','8','6392LK','Plymouth','010-548745','1319');

#--Create the table committee_members and fill it--------------------

Create table committee_members
(
playerno   int   not null,
begin_date   date   not null,
end_date   date,
position   varchar(20),
primary key(playerno, begin_date)
);

Insert into committee_members values
(2,'2010-01-01','2012-12-31','Chairman'),
(2,'2014-01-01',Null,'General Member'),
(6,'2010-01-01','2010-12-31','Secretary'),
(6,'2011-01-01','2012-12-31','General Member'),
(6,'2012-01-01','2013-12-31','Treasurer'),
(6,'2013-01-01',Null,'Chairman'),
(8,'2010-01-01','2010-12-31','Treasurer'),
(8,'2011-01-01','2011-12-31','Secretary'),
(8,'2013-01-01','2013-12-31','General Member'),
(8,'2014-01-01',Null,'General Member'),
(27,'2010-01-01','2010-12-31','General Member'),
(27,'2011-01-01','2011-12-31','Treasurer'),
(27,'2013-01-01','2013-12-31','Treasurer'),
(57,'2012-01-01','2012-12-31','Secretary'),
(94,'2014-01-01',Null,'Treasurer'),
(112,'2012-01-01','2012-12-31','General Member'),
(112,'2014-01-01',Null,'Secretary');

#--Create the table matches and Fill it-------------------

Create table matches
(
matchno       int   not null   Primary Key,
teamno       int   not null   references teams(teamno),
playerno   int   not null   references players(playerno),
won       int,
lost       int
);

Insert into matches values
(1,1,6,3,1),
(2,1,6,2,3),
(3,1,6,3,0),
(4,1,44,3,2),
(5,1,83,0,3),
(6,1,2,1,3),
(7,1,57,3,0),
(8,1,8,0,3),
(9,2,27,3,2),
(10,2,104,3,2),
(11,2,112,2,3),
(12,2,112,1,3),
(13,2,8,0,3);

#--Create Table Penalties and Fill it-------------------------------------

create table Penalties
(
paymentno   int   not null   Primary Key,
playerno   int   not null   references players(playerno),
payment_Date   date   not null,
amount   decimal(10,2)   not null
);

Insert into Penalties values
(1,6,'2010-12-08',100.00),
(2,44,'2011-05-05',75.00),
(3,27,'2013-09-10',100.00),
(4,104,'2014-07-08',50.00),
(5,44,'2010-12-08',25.00),
(6,8,'2010-12-08' ,25.00),
(7,44,'2012-12-30',30.00),
(8,27,'2014-08-12',75.00);

#--Create Table Teams and Fill it----------------------------------------------

Create table teams
(
teamno   int   Primary Key   Not Null,
playerno   int   Not Null   references players(playerno),
division   varchar(6)
);

Insert into teams values
(1,6,'first'),
(2,27,'second');

/* ********************************************************************************************
End of loading the database
******************************************************************************************** */

1.Which players have lost more matches than the average number of losses?   No duplicates should be listed.   Order by player number.   Insert your screen shot here.

2.How many players from each town served on the committee in any capacity? Display the town as ‘Town’ and the number served as ‘Committee Service’.   Insert your screenshot here.

3.How many members come from each town? Display the town as ‘Town’ and the number of members as ‘Number’. Insert your screenshot here.

4.Who has served on the committee more than once? Display the player number as ‘Number’, concatenate the player initial and name as ‘Name’, town as ‘Town’, and number served as ‘Terms’. Insert your screenshot here.

Below I will include the tennis database

* *******************************************************************

CREATE and OPEN the TENNIS Base

******************************************************************* */

Create database tennis;

USE tennis;

#--Create table players and fill it--------------------------

Create table players

(

playerno              int                         not null                primary key,

name                    varchar(15)         not null,

initials   varchar(3),

birth_date           date,

gender                 char(1),

joined                   int          not null,

street                   varchar(15)         not null,

houseno                             varchar(4),

zip                         char(6),

town                     varchar(10)         not null,

phoneno                             char(10),

leagueno             char(4)

);

Insert into players values

(2,'Everett','R','1988-01-09','M',2000,'Stoney Road','43','3575NH','Stratford','070-237893','2411'),

(6,'Paramenter','R','1984-06-25','M',2002,'Haseltine Lane','80','1234KK','Stratford','070-476547','8467'),

(7,'Wise','GWS','1983-05-11','M',2006,'Edgecombe Way','39','9758VB','Stratford','070-347689',Null),

(8,'Newcastle','B','1982-07-08','F',2005,'Station Road','4','6584RO','Inglewood','070-458458','2983'),

(27,'Collins','DD','1990-05-10','F',2008,'Long Drive','804','8457DK','Eltham','079-234857','2513'),

(28,'Collins','C','1983-06-22','F',2008,'Old Main 28','10','1294QK','Midhurst','071-659599',Null),

(39,'Bishop','D','1986-10-29','M',2005,'Eaton Square','78','9629CD','Stratford','070-393435',Null),

(44,'Baker','E','1983-09-01','M',2010,'Lewis Street','23','4444LJ','Inglewood','070-368753','1124'),

(57,'Brown','M','1981-08-17','M',2007,'Edgecombe Way','16','4377CB','Stratford','070-473458','6409'),

(83,'Hope','PK','1976-11-11','M',2009,'Magdalene Road','16A','1812UP','Stratford','070-353548','1608'),

(94,'Miller','P','1993-05-14','M',2013,'High Street','33A','5746OP','Douglas','070-867564',Null),

(100,'Parmenter','P','1983-02-28','M',2012,'Haseltine Lane','80','1234KK','Stratford','070-494593','6524'),

(104,'Moorman','D','1990-05-10','F',2014,'Stout Street','65','9437AO','Eltham','079-987571','7060'),

(112,'Bailey','IP','1983-10-01','F',2014,'Vixen Road','8','6392LK','Plymouth','010-548745','1319');

#--Create the table committee_members and fill it--------------------

Create table committee_members

(

playerno              int          not null,

begin_date          date       not null,

end_date             date,

position               varchar(20),

primary key(playerno, begin_date)

);

Insert into committee_members values

(2,'2010-01-01','2012-12-31','Chairman'),

(2,'2014-01-01',Null,'General Member'),

(6,'2010-01-01','2010-12-31','Secretary'),

(6,'2011-01-01','2012-12-31','General Member'),

(6,'2012-01-01','2013-12-31','Treasurer'),

(6,'2013-01-01',Null,'Chairman'),

(8,'2010-01-01','2010-12-31','Treasurer'),

(8,'2011-01-01','2011-12-31','Secretary'),

(8,'2013-01-01','2013-12-31','General Member'),

(8,'2014-01-01',Null,'General Member'),

(27,'2010-01-01','2010-12-31','General Member'),

(27,'2011-01-01','2011-12-31','Treasurer'),

(27,'2013-01-01','2013-12-31','Treasurer'),

(57,'2012-01-01','2012-12-31','Secretary'),

(94,'2014-01-01',Null,'Treasurer'),

(112,'2012-01-01','2012-12-31','General Member'),

(112,'2014-01-01',Null,'Secretary');

#--Create the table matches and Fill it-------------------

Create table matches

(

matchno                             int          not null Primary Key,

teamno                int          not null references teams(teamno),

playerno              int          not null references players(playerno),

won                      int,

lost                       int

);

Insert into matches values

(1,1,6,3,1),

(2,1,6,2,3),

(3,1,6,3,0),

(4,1,44,3,2),

(5,1,83,0,3),

(6,1,2,1,3),

(7,1,57,3,0),

(8,1,8,0,3),

(9,2,27,3,2),

(10,2,104,3,2),

(11,2,112,2,3),

(12,2,112,1,3),

(13,2,8,0,3);

#--Create Table Penalties and Fill it-------------------------------------

create table Penalties

(

paymentno         int          not null Primary Key,

playerno              int          not null references players(playerno),

payment_Date   date       not null,

amount decimal(10,2)     not null

);

Insert into Penalties values

(1,6,'2010-12-08',100.00),

(2,44,'2011-05-05',75.00),

(3,27,'2013-09-10',100.00),

(4,104,'2014-07-08',50.00),

(5,44,'2010-12-08',25.00),

(6,8,'2010-12-08' ,25.00),

(7,44,'2012-12-30',30.00),

(8,27,'2014-08-12',75.00);

#--Create Table Teams and Fill it----------------------------------------------

Create table teams

(

teamno int          Primary Key        Not Null,

playerno              int          Not Null               references players(playerno),

division varchar(6)

);

Insert into teams values

(1,6,'first'),

(2,27,'second');

/* ********************************************************************************************

End of loading the database

******************************************************************************************** */

Case 1

John is the manager of a small computer sales and support chain. He has stores located throughout the state of California and is in strong competition with all of the major computer providers within that state. John’s company is known for providing quick support and friendly service. In the process of selling goods to customers, John’s company will often offer deals that include free service or low-priced service for the products being purchased. John’s competitors offer the same types of deals to their customers, but because of the small mobile size of John’s company, he is better able to provide quick service to his customers. John is the president of his company and has raised funding through issuing stock. He has not used external loan funding much in the past. John has approximately 50 stores located in California and is in the process of obtaining business locations outside of the state. John’s main goal is to be successful in the computer business because of the quick customer service his company provides. He believes his company will be able to charge higher prices because people will be willing to pay the initial higher price on computer components for the added customer service on the back end.

John has managers in all of the different stores who report directly to him. They do not communicate regularly with other store managers on inventory issues or customer service representative availabilities. John has found much success in the past because of the customer service he has been able to provide. In recent years, the competition has become more successful in duplicating his activities or in providing low-maintenance products. John’s company has provided financial statements on a yearly basis, so investors can follow the company’s success. With the growing success of competitors, John has found it more difficult to be successful. During the past year, John’s company recorded significant revenues from sales that will require warranty service over the next few years. However, John’s reported warranty expenses stayed the same. In addition, the reported inventory levels remained approximately the same as in previous years. No additional financing or loans were recorded on the financial statements, even though assets continued to grow. Revenue was the only financial statement amount that changed dramatically.

2. What could look like fraud but be explained by industry trends?

Case 1

John is the manager of a small computer sales and support chain. He has stores located throughout the state of California and is in strong competition with all of the major computer providers within that state. John’s company is known for providing quick support and friendly service. In the process of selling goods to customers, John’s company will often offer deals that include free service or low-priced service for the products being purchased. John’s competitors offer the same types of deals to their customers, but because of the small mobile size of John’s company, he is better able to provide quick service to his customers. John is the president of his company and has raised funding through issuing stock. He has not used external loan funding much in the past. John has approximately 50 stores located in California and is in the process of obtaining business locations outside of the state. John’s main goal is to be successful in the computer business because of the quick customer service his company provides. He believes his company will be able to charge higher prices because people will be willing to pay the initial higher price on computer components for the added customer service on the back end.

John has managers in all of the different stores who report directly to him. They do not communicate regularly with other store managers on inventory issues or customer service representative availabilities. John has found much success in the past because of the customer service he has been able to provide. In recent years, the competition has become more successful in duplicating his activities or in providing low-maintenance products. John’s company has provided financial statements on a yearly basis, so investors can follow the company’s success. With the growing success of competitors, John has found it more difficult to be successful. During the past year, John’s company recorded significant revenues from sales that will require warranty service over the next few years. However, John’s reported warranty expenses stayed the same. In addition, the reported inventory levels remained approximately the same as in previous years. No additional financing or loans were recorded on the financial statements, even though assets continued to grow. Revenue was the only financial statement amount that changed dramatically.

2. What could look like fraud but be explained by industry trends?

An insurance company has three types of annuity products: indexed annuity, fixed annuity, and variable annuity. You are given:

• None of the customers have both fixed annuity and variable annuity.
• 40% of the customers with fixed annuity also have indexed annuity.
• Half of the customers with two annuity products have variable annuity.
• 60% of the customers have indexed annuity.
• The number of customers who have only the variable annuity is the same as the number of customers who have two annuity products.
• All customers have at least one type of annuity.

Determine the proportion of the customers who only have the indexed annuity.

1. 0.35

2. 0.37

3. 0.39

4. 0.41

5. 0.43

In respect of the use of electronic payment systems, consumers have a much higher security risk
than banks. Critically examine the above statement with reference to the issues raised by Schulze
(2004).

RentAPhone is a new service company that provides European mobile phones to American visitors to Europe. The company currently has 90 phones available at Charles de Gaulle Airport in Paris. There are, on average, 25 customers per day requesting a phone. These requests arrive uniformly throughout the 24 hours the store is open. (Note:This means customers arrive at a faster rate than 1 customer per hour.) The corresponding coefficient of variation is 1.

Customers keep their phones on average 84 hours. The standard deviation of this time is 72 hours.

Given that RentAPhone currently does not have a competitor in France providing equally good service, customers are willing to wait for the telephones. Yet, during the waiting period, customers are provided a free calling card. Based on prior experience, RentAPhone found that the company incurred a cost of $1 per hour per waiting customer, independent of day or night.

(a) What is the average number of telephones the company has in its store? (Round your answer to nearest whole number.)

The average number of telephones are?

(b) How long does a customer, on average, have to wait for the phone? (Round your answer to two decimal places.)

The average waiting time is ___ Hours?

(c) What are the total monthly (30 days) expenses for telephone cards? (Round your answer to two decimal places.)

The total monthly expenses are ?

(e) How would waiting time change if the company decides to limit all rentals to exactly 84 hours? Assume that if such a restriction is imposed, the number of customers requesting a phone would be reduced to 15 customers per day. (Round your answer to five decimal places.)

The waiting time is ___ hours?

Santana Rey created Business Solutions on October 1, 2020. The company has been successful, and its list of customers has grown. To accommodate the growth, the accounting system is modified to set up separate accounts for each customer. The following chart of accounts includes the account number used for each account and any balance as of December 31, 2020. Santana Rey decided to add a fourth digit with a decimal point to the 106 account number that had been used for the single Accounts Receivable account. This change allows the company to continue using the existing chart of accounts.

In response to requests from customers, S. Rey will begin selling computer software. The company will extend credit terms of 1/10, n/30, FOB shipping point, to all customers who purchase this merchandise. However, no cash discount is available on consulting fees. Additional accounts (Nos. 119, 413, 414, 415, and 502) are added to its general ledger to accommodate the company’s new merchandising activities. Its transactions for January through March follow.

The following additional facts are available for preparing adjustments on March 31 prior to financial statement preparation.

1. The March 31 amount of computer supplies still available totals $2,095.
2. Prepaid Insurance coverage of $699 expired during this three-month period.
3. Lyn Addie has not been paid for seven days of work at the rate of $155 per day.
4. Prepaid rent of $2,655 expired during this three-month period.
5. Depreciation on the computer equipment for January 1 through March 31 is $1,090.
6. Depreciation on the office equipment for January 1 through March 31 is $210.
7. The March 31 amount of merchandise inventory still available totals $574.

2. Post the journal entries in part 1 to the accounts in the company’s general ledger. Note: Begin with the ledger’s post-closing adjusted balances as of December 31, 2020.