Question

Need help converting into sql language. How many players from each town served on the committee...

Need help converting into sql language.

  1. 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.

/* *******************************************************************
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

0 0
Add a comment Improve this question Transcribed image text
Answer #1

SQL Query :

select town as 'Town',count(committee_members.playerno) as 'Committee Service'
from players,committee_members where
players.playerno=committee_members.playerno
group by town;

Explanation :

  • Above sql query will join two tables players,committee_members and
  • will group the records based on playerno and will return count of Committee Service
Add a comment
Know the answer?
Add Answer to:
Need help converting into sql language. How many players from each town served on the committee...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS order_details; DROP...

    DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS order_details; DROP TABLE IF EXISTS items; DROP TABLE IF EXISTS artists; DROP TABLE IF EXISTS employees; -- create tables CREATE TABLE customers (    customer_id INT ,    customer_first_name VARCHAR(20),    customer_last_name VARCHAR(20) NOT NULL,    customer_address VARCHAR(50) NOT NULL,    customer_city VARCHAR(20) NOT NULL,    customer_state CHAR(2) NOT NULL,    customer_zip CHAR(5) NOT NULL,    customer_phone CHAR(10) NOT NULL,    customer_fax CHAR(10),    CONSTRAINT customers_pk...

  • 2. The Vice President of Marketing for your firm wants the firm’s sales representatives to be...

    2. The Vice President of Marketing for your firm wants the firm’s sales representatives to be able to directly view and edit customer details, but only for the state to which a particular sales representative is assigned. You have suggested that this need can be addressed with a view. For example, a view could be created for one particular state, and user account permissions for accessing that view granted only to sales representatives from that state. The VP has asked...

  • MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects...

    MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects 2.List the project with the most hours SCRIPT TO BUILD DATABASE create table department ( dept_ID int not null , dept_name char(50) NOT NULL, manager_ID int not null, manager_start_date date not null, constraint Dept_PK primary key (dept_ID), constraint D_Name_AK unique (dept_name) ); insert into department values(1,'abc',1,'2019-01-08'); insert into department values(2,'abc2',2,'2019-01-08'); insert into department values(3,'abc3',2,'2019-01-08'); insert into department values(4,'abc4',2,'2019-01-08'); /*project table done*/ create table project...

  • I need help with the following SQL query for a company database (script given below). The...

    I need help with the following SQL query for a company database (script given below). The name of the Department. The number of employees working in that department. The number of different projects controlled by this department. The name of the project controlled by this department that has the maximum number of employees of the company working on it. The number of the above project. The cumulative sum of the number of employees of the company working on the projects...

  • Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and...

    Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and phones of all swimmers currently in level (of id) 3. +-------+---------+---------------------------+ | FName | LName   | EMail                     | +-------+---------+---------------------------+ | Bobby | Khan    | theBKhan1 | | Clara | Johnson | ClaraJohnson_11 | +-------+---------+---------------------------+ 2 rows in set (0.00 sec) (2) Provide the names of swimmers who have signed up to participate in the event '100M Butterfly' of Meet id 1. +-------+---------+ | FName...

  • I need help in SQL management studio please. Question: Create the same query as query 5,...

    I need help in SQL management studio please. Question: Create the same query as query 5, but only include artists if they have 'Pop' Genre. Previous question: Create a list of all composers and artists in your database. Include two columns in your record set (the names of the returned columns are in brackets): 'Composer/Artist Name' and 'Type' (which will indicate whether your results are from the composer or artist table). Order by 'Composer/Artist Name'. This is the answer for...

  • Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB...

    Create SQL statements based on the given tables: Code to create the tables: CREATE DATABASE LAB4DB; USE LAB4DB; CREATE TABLE CUSTOMER ( CUS_CODE int primary key, CUS_LNAME varchar(15), CUS_FNAME varchar(15), CUS_INITIAL varchar(1), CUS_AREACODE varchar(3), CUS_PHONE varchar(8), CUS_BALANCE float(8) ); INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0'); INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0'); INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86'); INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75'); INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0'); INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0'); INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19'); INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93'); INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55'); INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0'); /*...

  • You are an analyst fir a mutual fund that wanrs to include a home inprovement store...

    You are an analyst fir a mutual fund that wanrs to include a home inprovement store in its portfolio. Given that Home Depot and Lowes are the dominant olayers in the industry, which firm would you recommend buying? Explain C D F N M N O P Q R 5 T 1 LOWE's HOME DEPOT 2010-01 2001-01 2012-01 2011-01 2014-01 2005-2006-01 2017-01 220 48.835 50,208 50 52 54 56.223 52 2010-2011-01 2012-01 2013-08 2014-01 2015-2016-01 2017-01 3.112 3.560 277 73...

  • * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...

    * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE EMPLOYEE and loads the default data rows */ set echo on; set serveroutput on; select systimestamp from dual; show user; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DROP TABLE LINE CASCADE CONSTRAINTS; DROP TABLE INVOICE CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE VENDOR CASCADE CONSTRAINTS; DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE...

  • please answer these queries. 1. List the artist name of the artists who do not have...

    please answer these queries. 1. List the artist name of the artists who do not have a webaddress and their leadsource is “Directmail” 2. List the names of members in the artist called 'Today'. 3. Report the total runtime in minutes FOR EACH album in the Titles table. 4.List the firstname, lastname of members who are represented by the salesperson “Lisa Williams” 5.List EACH salesperson’s firstname along with the number of Members that EACH SalesPerson represents. DROP TABLES IF EXISTS...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT