Given .sql file:

create table Player ( ID integer, Name varchar(64), Birthday date, Address varchar(128), Email varchar(32), PhoneNumber char(10), PlayPos varchar(16) ); alter table Player add primary key(ID); insert into Player values( 101, "pistol pete", '1980/02/01', '1990 S Espina', 'pistolpete@ g mail.com', 1587548415, 'Middle' ); insert into Player values( 102, "Lobo Louie", '1989/08/18', '100 Vista del monte', 'LoboLouie@ g mail.com', 5128479856, 'Middle' ); insert into Player values( 103, "yoyo", '1993/11/06', '2422 S ESpina ST', 'yoyo@ g mail.com', 5199688745, 'Middle' ); insert into Player values( 104, "mayi", '1993/11/06', '9030 Markvile DR', 'mayi@ g mail.com', 5122457854, 'center' ); insert into Player values( 105, "jack", '1970/05/16', '222 l street', 'jack@ g mail.com', 5122458854, 'Middle' ); create table Manager ( ID integer, LoginID varchar(16), Name varchar(64), Password varchar(8), Birthday date, Address varchar(128), Email varchar(32), PhoneNumber char(10) ); alter table Manager add primary key(ID); insert into Manager values( 201, 1234, 'Boss', 128414, '1986/11/08', 'Las Cruces', 'Boss@ g mail.com', 5488759874 ); insert into Manager values( 202, 2345, 'Alice', 825447, '1995/08/10', 'El paso', 'Alice@ g mail.com', 7845964712 ); insert into Manager values( 203, 8659, 'BOB', 201158, '1980/08/01', 'Las Cruces', 'BOB@ g mail.com', 5874125397 ); create table Stats ( PlayerID integer, Year char(4), TotalPoints integer, ASPG integer, Foreign key (PlayerID) references Player(ID) ); insert into Stats values (101, 2010, 50, 200); insert into Stats values (101, 2012, 100, 200); insert into Stats values (101, 2019, 200, 200); insert into Stats values (101, 2014, 100, 200); insert into Stats values (102, 2012, 100, 200); insert into Stats values (102, 2014, 50, 200); insert into Stats values (102, 2013, 200, 200); insert into Stats values (105, 2013, 500, 200); insert into Stats values (103, 2019, 500, 200); insert into Stats values (103, 2012, 500, 200); create table Training ( TrainingName varchar(256), Instruction varchar(256), TimePeriodInHour integer ); alter table Training add primary key(TrainingName); create table AssignTraining ( PlayerID integer, ManagerID integer, TrainingName varchar(256), Foreign key (PlayerID) references Player(ID), Foreign key (ManagerID) references Manager(ID), Foreign key (TrainingName) references Training(TrainingName) ); create table Game ( GameID integer, Date date, Result varchar(16), PlayingVenue varchar(256), OpponentTeam varchar(32) ); alter table Game add primary key(GameID); insert into Game values(20101, '2016/11/05', 'win', 'The Pit', 'NMSU'); insert into Game values(20102, '2018/10/28', 'lose', 'PanAm', 'Aggies'); insert into Game values(20103, '2019/12/03', 'lose', 'PanAm', 'Aggies'); insert into Game values(20104, '2017/05/11', 'win', 'PanAm', 'NMSU'); insert into Game values(20105, '2017/12/28', 'win', 'The pit', 'NMSU'); create table Play ( PlayerID integer, GameID integer, Foreign key (PlayerID) references Player(ID), Foreign key (GameID) references Game(GameID) ); insert into Play values (101, 20102); insert into Play values (102, 20101); insert into Play values (102, 20103); insert into Play values (103, 20101); insert into Play values (103, 20105); create table Doctor ( Email varchar(32), Name varchar(64), PhoneNumber char(10) ); alter table Doctor add primary key(Email); insert into Doctor values('paul@ g mail.com', 'Paul', 8154789874); insert into Doctor values('mike@ g mail.com', 'mike', 8154789874); insert into Doctor values('jack@ g mail.com', 'jack', 8154789874); insert into Doctor values('aron@ g mail.com', 'aron', 8154789874); create table TakeExam ( PlayerID integer, DocEmail varchar(32), TestDate date, TestResult varchar(256), Foreign key (PlayerID) references Player(ID), Foreign key (DocEmail) references Doctor(Email) ); insert into TakeExam values(101, 'paul@ g mail.com', '2018/10/20', 'positive'); insert into TakeExam values(101, 'mike@ g mail.com', '2016/10/10', 'negative'); insert into TakeExam values(102, 'paul@ g mail.com', '2017/12/10', 'positive'); insert into TakeExam values(103, 'aron@ g mail.com', '2016/09/25', 'negative'); create table ManagerCertificate ( ManagerID integer, CertificateId integer, Certificate blob ); alter table ManagerCertificate add FOREIGN KEY (ManagerID) REFERENCES Manager(ID); insert into ManagerCertificate values(201, 1001212, 'CITI'); insert into ManagerCertificate values(202, 1001213, 'CITI'); insert into ManagerCertificate values(203, 1001214, 'CITI'); insert into ManagerCertificate values(202, 1001217, 'new'); insert into ManagerCertificate values(203, 1001216, 'new'); insert into ManagerCertificate values(203, 1001215, 'new1');

_____________________________________________________________________________________________________________________________________________________

Assume that you are given the following relational schemas for the basketball team.

• Player (ID: integer, Name: varchar (64), Birthday: date, Address: varchar (128), Email: varchar (32), PhoneNumber: char (10), PlayPos: varchar (16))

• Manager (ID: integer, LoginID: varchar (16), Name: varchar (64), Password: varchar (8), Birthday: date, Address: varchar (128), Email: varchar (32), PhoneNumber: char (10))

• ManagerCertificate (ManagerID: integer, CertificateId: integer, Certificate: blob) - Foreign key: ManagerID references Manager (ID)

• Doctor (Email: varchar (32), Name: varchar (64), PhoneNumber: char (10))

• TakeExam (PlayerID: integer, DocEmail: varchar (32), TestDate: date, TestResult: varchar (256))

  • Foreign key: PlayerID references Player (ID)

  • Foreign key: DocEmail references Doctor (Email)

• Stats (PlayerID: integer, Year: char (4), TotalPoints: integer, ASPG: integer) - Foreign key: PlayerID references Player (ID)

• Training (TrainingName: varchar (256), Instruction: varchar (256), TimePeriodInHour: integer)

• AssignTraining (PlayerID: integer, ManagerID: integer, TrainingName: varchar (256))

  • Foreign key: PlayerID references Player (ID)

  • Foreign key: ManagerID references Manager (ID)

  • Foreign key: TrainingName references Training (TrainingName)

• Game (GameID: integer, Date: date, Result: varchar (16), PlayingVenue: varchar (256), OpponentTeam: varchar (32))

• Play (PlayerID: integer, GameID: integer) - Foreign key: PlayerID references Player (ID) Foreignkey:GameIDreferencesGame(GameID)

Write an SQL Query for each of the following.

1. ) Show the names, IDs and emails of all players whose play position is Middle.

2. ) Show the total points that player Lobo Louie has scored each year (assume there is only one Lobo Louie).

3. ) Show the names and phone numbers of every manager who has exactly 2 distinct certificates (certificate names).

4. ) Show the names of every player who has played a game at PanAm and lost (Result = lose), in ascending order of age.

5. ) Show all the information of players who have taken exams.

6. ) Find the games that players named Lobo Louie and yoyo have played in. Show the games date, venue, and result.

7. ) Find the games that players named yoyo have played in from 2016-2017. Show the games date, venue, and result.

8. ) Find the Names and IDs of players who have scored points less than the average player.

9. ) Show all players that have the same play position.

10. ) Find the total number of points the Aggie basketball team scored in 2012.