Need help converting into sql language.
/*
*******************************************************************
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
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 :
Need help converting into sql language. How many players from each town served on the committee...
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 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 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 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 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, 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 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 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 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 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...