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 | LName |
+-------+---------+
| Bobby | Khan |
| Clara | Johnson |
+-------+---------+
2 rows in set (0.00 sec)
(3) Provide the names and phones of all secondary caretakers of a
swimmer in level 2.
+--------+-------+--------------+
| FName | LName | Phone
|
+--------+-------+--------------+
| Jim | Khan | 832-116-2994 |
| Azalea | Khan | 832-116-2992 |
+--------+-------+--------------+
2 rows in set (0.00 sec)
(4) Provide the name of caretakers who are the primary (main) caretakers of at least two swimmers.
+--------+-------+
| FName | LName |
+--------+-------+
| Azalea | Khan |
+--------+-------+
1 row in set (0.00 sec)
(5) List the names of all caretakers who have volunteered for the
task 'Recording' but not the task 'Officiating'. (It does not
matter whether the caretaker rescinded, carried out the task, or
not.)
+--------+-------+
| FName | LName |
+--------+-------+
| Joseph | Khan |
| Jim | Khan |
+--------+-------+
2 rows in set (0.00 sec)
(6) Show the number of tasks volunteered by caretakers in
descendant order with the names of the caretakers in the following
format. This counts all commitments, rescinded, carried out, or
not.
+-------------------+-----------------------------+
| caretaker |
number of tasks volunteered |
+-------------------+-----------------------------+
| Azalea Khan
|
4 |
| Elizabeth Johnson
|
3 |
| Katie Johnson
|
3 |
| Joseph Khan
|
2 |
| Jim Khan
|
1 |
+-------------------+-----------------------------+
5 rows in set (0.00 sec)
(7) For every swimmer, provide her name, her primary caretaker
name, current level, signed up meet names, dates and events in the
following format.
+----------------+-------------------+--------+-------------+------------+----------------+
| swimmer |
caretaker | level |
meet | meet date |
event |
+----------------+-------------------+--------+-------------+------------+----------------+
| Billy Khan | Azalea
Khan | Blue | UHCL
Open | 2016-03-03 | 100M Freestyle |
| Billy Khan | Azalea
Khan | Blue | Shell
Trial | 2016-08-04 | 50M Butterfly |
| Billy Khan | Azalea
Khan | Blue | UHCL
Open | 2016-03-03 | 50M Butterfly |
| Billy Khan | Azalea
Khan | Blue | Shell
Trial | 2016-08-04 | 100M Freestyle |
| Bobby Khan | Azalea
Khan | Yellow | Shell Trial |
2016-08-04 | 200M Freestyle |
| Bobby Khan | Azalea
Khan | Yellow | UHCL
Open | 2016-03-03 | 200M Freestyle |
| Bobby Khan | Azalea
Khan | Yellow | Shell Trial |
2016-08-04 | 100M Butterfly |
| Bobby Khan | Azalea
Khan | Yellow | UHCL
Open | 2016-03-03 | 100M Butterfly |
| Clara Johnson | Katie Johnson | Yellow |
Shell Trial | 2016-08-04 | 50M Butterfly |
| Clara Johnson | Katie Johnson | Yellow |
UHCL Open | 2016-03-03 | 100M Butterfly |
| Clara Johnson | Katie Johnson | Yellow |
Shell Trial | 2016-08-04 | 100M Butterfly |
| Clara Johnson | Katie Johnson | Yellow |
Shell Trial | 2016-08-04 | 200M Freestyle |
| Nina Khan | Joseph
Khan | Blue | Shell
Trial | 2016-08-04 | 50M Butterfly |
| Nina Khan | Joseph
Khan | Blue | UHCL
Open | 2016-03-03 | 50M Butterfly |
| Nina Khan | Joseph
Khan | Blue | Shell
Trial | 2016-08-04 | 100M Freestyle |
| Nina Khan | Joseph
Khan | Blue | UHCL
Open | 2016-03-03 | 100M Freestyle |
| Philip Johnson | Elizabeth Johnson | Blue | UHCL
Open | 2016-03-03 | 100M Freestyle |
| Philip Johnson | Elizabeth Johnson | Blue | Shell
Trial | 2016-08-04 | 50M Butterfly |
| Philip Johnson | Elizabeth Johnson | Blue | UHCL
Open | 2016-03-03 | 50M Butterfly |
| Philip Johnson | Elizabeth Johnson | Blue | Shell
Trial | 2016-08-04 | 100M Freestyle |
+----------------+-------------------+--------+-------------+------------+----------------+
20 rows in set (0.00 sec)
-- Bare Bone DDL to create the CLYSTMS DB of
-- HW #5 solution of CSCI 4333.1 Fall 2016
-- You may use the following DELETE TABLE
-- to ensure starting with a clean slate.
-- Note the DELETE TABLE is usually in the
-- reverse order of CREATE TABLE to ensure
-- no referential integrity violations.
DROP TABLE IF EXISTS Commitment;
DROP TABLE IF EXISTS V_Task;
DROP TABLE IF EXISTS V_TaskList;
DROP TABLE IF EXISTS Participation;
DROP TABLE IF EXISTS Event;
DROP TABLE IF EXISTS Meet;
DROP TABLE IF EXISTS LevelHistory;
DROP TABLE IF EXISTS OtherCaretaker;
DROP TABLE IF EXISTS Swimmer;
DROP TABLE IF EXISTS Venue;
DROP TABLE IF EXISTS Level;
DROP TABLE IF EXISTS Caretaker;
DROP TABLE IF EXISTS Coach;
-- Create tables
DROP TABLE IF EXISTS Coach;
CREATE TABLE Coach(
CoachId INT UNSIGNED
AUTO_INCREMENT,
LName VARCHAR(30) NOT
NULL,
FName VARCHAR(30) NOT
NULL,
Phone VARCHAR(12) NOT
NULL,
EMail VARCHAR(60) NOT
NULL,
CONSTRAINT Coach_pk PRIMARY KEY(CoachId)
);
DROP TABLE IF EXISTS Caretaker;
CREATE TABLE Caretaker(
CT_Id INT UNSIGNED
AUTO_INCREMENT,
LName VARCHAR(30) NOT
NULL,
FName VARCHAR(30) NOT
NULL,
Phone VARCHAR(12) NOT
NULL,
EMail VARCHAR(60) NOT
NULL,
CONSTRAINT Caretaker_pk PRIMARY KEY(CT_Id)
);
DROP TABLE IF EXISTS Level;
CREATE TABLE Level(
LevelId INT
UNSIGNED,
-- ok to use smaller INT such as TINYINT
Level VARCHAR(30) NOT
NULL,
Description VARCHAR(250),
CONSTRAINT level_pk PRIMARY KEY(LevelId),
CONSTRAINT level_ck_1 UNIQUE(Level)
);
DROP TABLE IF EXISTS Venue;
CREATE TABLE Venue(
VenueId INT UNSIGNED
AUTO_INCREMENT,
-- ok to use smaller INT such as SMALLINT
Name VARCHAR(100) NOT
NULL,
Address VARCHAR(100)
NOT NULL,
City VARCHAR(50) NOT
NULL,
State VARCHAR(15) NOT
NULL,
ZipCode VARCHAR(10) NOT
NULL,
Phone VARCHAR(12) NOT
NULL,
CONSTRAINT venue_pk PRIMARY KEY(VenueId),
CONSTRAINT venue_ck_1 UNIQUE(Name)
);
DROP TABLE IF EXISTS Swimmer;
CREATE TABLE Swimmer(
SwimmerId INT UNSIGNED
AUTO_INCREMENT,
LName
VARCHAR(30) NOT NULL,
FName
VARCHAR(30) NOT NULL,
Phone
VARCHAR(12) NOT NULL,
EMail
VARCHAR(60) NOT NULL,
JoinTime DATE NOT
NULL,
CurrentLevelId INT UNSIGNED NOT NULL,
Main_CT_Id INT UNSIGNED
NOT NULL,
Main_CT_Since DATE NOT NULL,
CONSTRAINT swimmer_pk PRIMARY KEY(SwimmerId),
CONSTRAINT swimmer_level_fk FOREIGN
KEY(CurrentLevelId)
REFERENCES Level(LevelId),
CONSTRAINT swimmer_caretaker_fk FOREIGN
KEY(Main_CT_Id)
REFERENCES Caretaker(CT_Id)
);
DROP TABLE IF EXISTS OtherCaretaker;
CREATE TABLE OtherCaretaker(
OC_Id INT UNSIGNED
AUTO_INCREMENT,
SwimmerId INT UNSIGNED NOT NULL,
CT_Id INT UNSIGNED NOT
NULL,
Since DATE NOT
NULL,
CONSTRAINT othercaretaker_pk PRIMARY KEY(OC_Id),
CONSTRAINT othercaretaker_swimmer_fk FOREIGN
KEY(SwimmerId)
REFERENCES
Swimmer(SwimmerId),
CONSTRAINT othercaretaker_caretaker_fk FOREIGN
KEY(CT_Id)
REFERENCES Caretaker(CT_Id)
);
DROP TABLE IF EXISTS LevelHistory;
CREATE TABLE LevelHistory(
LH_Id INT UNSIGNED
AUTO_INCREMENT,
SwimmerId INT UNSIGNED NOT NULL,
LevelId INT UNSIGNED
NOT NULL,
StartDate DATE NOT NULL,
Comment
VARCHAR(250),
CONSTRAINT levelhistory_pk PRIMARY KEY(LH_Id),
CONSTRAINT levelhistory_ck_1 UNIQUE(SwimmerId,
LevelId),
CONSTRAINT levelhistory_swimmer_fk FOREIGN
KEY(SwimmerId)
REFERENCES
Swimmer(SwimmerId),
CONSTRAINT levelhistory_level_fk FOREIGN
KEY(LevelId)
REFERENCES Level(LevelId)
);
DROP TABLE IF EXISTS Meet;
CREATE TABLE Meet(
MeetId INT UNSIGNED
AUTO_INCREMENT,
Title VARCHAR(100) NOT
NULL,
Date DATE NOT
NULL,
StartTime TIME NOT NULL,
EndTime TIME NOT
NULL,
VenueId INT UNSIGNED
NOT NULL,
CoachId INT UNSIGNED
NOT NULL,
CONSTRAINT meet_pk PRIMARY KEY(MeetId),
CONSTRAINT meet_venue_fk FOREIGN KEY(VenueId)
REFERENCES Venue(VenueId),
CONSTRAINT meet_coach_fk FOREIGN KEY(CoachId)
REFERENCES Coach(CoachId)
);
DROP TABLE IF EXISTS Event;
CREATE TABLE Event(
EventId INT UNSIGNED
AUTO_INCREMENT,
Title VARCHAR(100) NOT
NULL,
StartTime TIME NOT NULL,
EndTime TIME NOT
NULL,
MeetId INT UNSIGNED NOT
NULL,
LevelId INT UNSIGNED
NOT NULL,
CONSTRAINT event_pk PRIMARY KEY(EventId),
CONSTRAINT event_meet_fk FOREIGN KEY(MeetId)
REFERENCES Meet(MeetId),
CONSTRAINT event_level_fk FOREIGN KEY(LevelId)
REFERENCES Level(LevelId)
);
DROP TABLE IF EXISTS Participation;
CREATE TABLE Participation(
ParticipationId INT UNSIGNED
AUTO_INCREMENT,
SwimmerId INT UNSIGNED
NOT NULL,
EventId
INT UNSIGNED NOT NULL,
Committed
BOOLEAN,
CommitTime
DATETIME,
Participated BOOLEAN,
Result
VARCHAR(100),
Comment
VARCHAR(100),
CommentCoachId INT UNSIGNED,
CONSTRAINT participation_pk PRIMARY
KEY(ParticipationId),
CONSTRAINT participation_ck_1 UNIQUE(SwimmerId,
EventId),
CONSTRAINT participation_swimmer_fk FOREIGN
KEY(SwimmerId)
REFERENCES
Swimmer(SwimmerId),
CONSTRAINT participation_event_fk FOREIGN
KEY(EventId)
REFERENCES Event(EventId),
CONSTRAINT participation_coach_fk FOREIGN
KEY(CommentCoachId)
REFERENCES Coach(CoachId)
);
DROP TABLE IF EXISTS V_TaskList;
CREATE TABLE V_TaskList(
VTL_Id INT UNSIGNED
AUTO_INCREMENT,
MeetId INT UNSIGNED NOT
NULL,
Required BOOLEAN NOT NULL,
Description VARCHAR(250) NOT NULL,
Penalty
VARCHAR(100),
PenaltyAmt DECIMAL(6,2),
CONSTRAINT v_tasklist_pk PRIMARY KEY(VTL_Id),
CONSTRAINT v_tasklist_meet_fk FOREIGN
KEY(MeetId)
REFERENCES Meet(MeetId)
);
DROP TABLE IF EXISTS V_Task;
CREATE TABLE V_Task(
VT_Id INT UNSIGNED
AUTO_INCREMENT,
VTL_Id INT UNSIGNED NOT
NULL,
Name VARCHAR(100) NOT
NULL,
Comment
VARCHAR(250),
Num_V SMALLINT UNSIGNED
DEFAULT 1,
CONSTRAINT v_task_pk PRIMARY KEY(VT_Id),
CONSTRAINT v_task_v_tasklist_fk FOREIGN
KEY(VTL_Id)
REFERENCES V_Tasklist(VTL_Id)
);
DROP TABLE IF EXISTS Commitment;
CREATE TABLE Commitment(
CommitmentId INT UNSIGNED
AUTO_INCREMENT,
CT_Id
INT UNSIGNED NOT NULL,
VT_Id
INT UNSIGNED NOT NULL,
CommitTime DATETIME NOT
NULL,
Rescinded
BOOLEAN,
RescindTime
DATETIME,
CarriedOut
BOOLEAN,
Comment
VARCHAR(100),
CommentCoachId INT UNSIGNED,
CONSTRAINT commitment_pk PRIMARY
KEY(CommitmentId),
CONSTRAINT commitment_ck_1 UNIQUE(CT_Id, VT_Id),
CONSTRAINT commitment_caretaker_fk FOREIGN
KEY(CT_Id)
REFERENCES Caretaker(CT_Id),
CONSTRAINT commitment_v_task_fk FOREIGN
KEY(VT_Id)
REFERENCES V_Task(VT_Id),
CONSTRAINT commitment_coach_fk FOREIGN
KEY(CommentCoachId)
REFERENCES Coach(CoachId)
);
-- Bare Bone DML to populate the CLYSTMS DB of
-- HW #5 solution of CSCI 4333.1 Fall 2016
insert into Coach(FName, LName, Phone, EMail)
values('Joe', 'Smith', '713-222-9413', 'joesmile_1061');
insert into Coach(FName, LName, Phone, EMail)
values('Jane', 'Smith', '713-222-9414', 'janesmile_1061');
insert into Coach(FName, LName, Phone, EMail)
values('Paul', 'Lam', '713-486-2011', 'paulkkk');
insert into Coach(FName, LName, Phone, EMail)
values('Paulina', 'Hall', '832-486-1997', 'paulinathenice');
insert into Coach(FName, LName, Phone, EMail)
values('Katrina', 'Bajaj', '832-117-2435', 'KatrinaBajaj');
insert into Caretaker(FName, LName, Phone, EMail)
values('Azalea', 'Khan', '832-116-2992', 'theAKhan');
insert into Caretaker(FName, LName, Phone, EMail)
values('Joseph', 'Khan', '832-116-2993', 'theJKhan');
insert into Caretaker(FName, LName, Phone, EMail)
values('Jim', 'Khan', '832-116-2994', 'theJKhan2');
insert into Caretaker(FName, LName, Phone, EMail)
values('Katie', 'Johnson', '713-014-0090', 'KatieJohnson1010');
insert into Caretaker(FName, LName, Phone, EMail)
values('Elizabeth', 'Johnson', '713-014-2090', 'EJohnson5111');
insert into Level (LevelId, Level, Description)
values(1, 'Green', 'First Level');
insert into Level (LevelId, Level, Description)
values(2, 'Blue', 'Second level');
insert into Level (LevelId, Level, Description)
values(3, 'Yellow', 'Third level');
insert into Level (LevelId, Level, Description)
values(4, 'Pink', 'Fourth level');
insert into Level (LevelId, Level, Description)
values(5, 'Orange', 'Fifth level');
insert into Level (LevelId, Level, Description)
values(6, 'Lime', 'Six level');
insert into Level (LevelId, Level, Description)
values(7, 'Purple', 'Seventh level');
insert into Level (LevelId, Level, Description)
values(8, 'Red', 'Eigth level');
insert into Level (LevelId, Level, Description)
values(9, 'Brown', 'Ninth level');
insert into Level (LevelId, Level, Description)
values(10, 'Black', 'Tenth level');
insert into Venue(Name, Address, City, State, ZipCode, Phone)
values('UHCL', '2700 Bay Area Boulevard', 'Houston','Texas',
'77058', '281-283-3700');
insert into Venue(Name, Address, City, State, ZipCode, Phone)
values('CLHS', '3300 Bay Area Boulevard', 'Houston', 'Texas',
'77059', '713-126-4544');
insert into Swimmer(FName, LName, Phone, EMail, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)
values('Bobby', 'Khan', '832-116-2992', 'theBKhan1',
'2014-2-12', 3, 1, '2014-2-12');
insert into Swimmer(FName, LName, Phone, EMail, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)
values('Billy', 'Khan', '832-116-2992', 'theBKhan2',
'2015-12-12', 2, 1, '2015-12-12');
insert into Swimmer(FName, LName, Phone, EMail, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)
values('Nina', 'Khan', '832-116-2992', 'theNinaKhan',
'2016-5-12', 2, 2, '2016-5-12');
insert into Swimmer(FName, LName, Phone, EMail, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)
values('Clara', 'Johnson', '713-222-1010', 'ClaraJohnson_11',
'2013-5-12', 3, 4, '2015-5-12');
insert into Swimmer(FName, LName, Phone, EMail, JoinTime, CurrentLevelId, Main_CT_Id, Main_CT_Since)
values('Philip', 'Johnson', '713-222-1010', 'PhilipJohnson_108',
'2015-5-15', 2, 5, '2015-5-15');
insert into OtherCareTaker(SwimmerId, CT_Id, Since)
values(1,2,'2014-2-12');
insert into OtherCareTaker(SwimmerId, CT_Id, Since)
values(1,3,'2014-2-12');
insert into OtherCareTaker(SwimmerId, CT_Id, Since)
values(2,3,'2016-1-3');
insert into OtherCareTaker(SwimmerId, CT_Id, Since)
values(3,1,'2016-5-12');
insert into OtherCareTaker(SwimmerId, CT_Id, Since)
values(3,3,'2016-5-12');
insert into OtherCareTaker(SwimmerId, CT_Id, Since)
values(4,5,'2016-1-12');
insert into LevelHistory(SwimmerId, LevelId, StartDate, Comment)
values(1,1,'2014-2-12','Good spirit');
insert into LevelHistory(SwimmerId, LevelId, StartDate)
values(1,2,'2014-7-15');
insert into LevelHistory(SwimmerId, LevelId, StartDate, Comment)
values(1,3,'2016-1-19','Fast time');
insert into LevelHistory(SwimmerId, LevelId, StartDate)
values(2,1,'2015-12-12');
insert into LevelHistory(SwimmerId, LevelId, StartDate)
values(2,2,'2016-4-15');
insert into LevelHistory(SwimmerId, LevelId, StartDate)
values(3,1,'2016-5-12');
insert into LevelHistory(SwimmerId, LevelId, StartDate, Comment)
values(3,2,'2016-7-12', 'Fast advance');
insert into LevelHistory(SwimmerId, LevelId, StartDate, Comment)
values(4,1,'2013-5-12','Freestyle best');
insert into LevelHistory(SwimmerId, LevelId, StartDate)
values(4,2,'2014-6-1');
insert into LevelHistory(SwimmerId, LevelId, StartDate)
values(4,3,'2015-10-2');
insert into LevelHistory(SwimmerId, LevelId, StartDate)
values(5,1,'2015-5-15');
insert into LevelHistory(SwimmerId, LevelId, StartDate)
values(5,2,'2016-3-15');
insert into Meet(Title, Date, StartTime, EndTime, VenueId, CoachId)
values('UHCL Open', '2016-3-3', '09:00:00', '16:00:00', 1, 1);
insert into Meet(Title, Date, StartTime, EndTime, VenueId, CoachId)
values('Shell Trial', '2016-8-4', '08:00:00', '11:00:00', 2, 1);
insert into Event(Title, StartTime, EndTime, MeetId, LevelId)
values('50M Butterfly', '09:10:00', '09:30:00', 1, 2);
insert into Event(Title, StartTime, EndTime, MeetId, LevelId)
values('100M Freestyle', '09:40:00', '09:50:00', 1, 2);
insert into Event(Title, StartTime, EndTime, MeetId, LevelId)
values('100M Butterfly', '10:10:00', '10:30:00', 1, 3);
insert into Event(Title, StartTime, EndTime, MeetId, LevelId)
values('200M Freestyle', '10:40:00', '10:50:00', 1, 3);
insert into Event(Title, StartTime, EndTime, MeetId, LevelId)
values('50M Butterfly', '09:10:00', '09:30:00', 2, 2);
insert into Event(Title, StartTime, EndTime, MeetId, LevelId)
values('100M Freestyle', '09:40:00', '09:50:00', 2, 2);
insert into Event(Title, StartTime, EndTime, MeetId, LevelId)
values('100M Butterfly', '10:10:00', '10:30:00', 2, 3);
insert into Event(Title, StartTime, EndTime, MeetId, LevelId)
values('200M Freestyle', '10:40:00', '10:50:00', 2, 3);
insert into Participation(SwimmerId, EventId)
values(2,1);
insert into Participation(SwimmerId, EventId, Committed)
values(3,1,1);
insert into Participation(SwimmerId, EventId, Committed, CommitTime,
Participated, Result, Comment, CommentCoachId)
values(5,1,1,'2016-2-20 10:00:00', 1, 'Winner', 'Good!', 2);
insert into Participation(SwimmerId, EventId)
values(3,2);
insert into Participation(SwimmerId, EventId, Committed)
values(2,2,1);
insert into Participation(SwimmerId, EventId, Committed, CommitTime,
Participated, Result, Comment, CommentCoachId)
values(5,2,1,'2016-2-20 10:00:00', 1, 'Winner', 'Second winner', 2);
insert into Participation(SwimmerId, EventId)
values(1,3);
insert into Participation(SwimmerId, EventId, Committed)
values(4,3,1);
insert into Participation(SwimmerId, EventId)
values(1,4);
insert into Participation(SwimmerId, EventId, Committed, CommitTime,
Participated, Result)
values(4,5,1,'2016-1-15 12:00:00', 1, 'Runner up');
insert into Participation(SwimmerId, EventId)
values(2,5);
insert into Participation(SwimmerId, EventId, Committed)
values(3,5,1);
insert into Participation(SwimmerId, EventId, Committed, CommitTime,
Participated, Result, Comment, CommentCoachId)
values(5,5,1,'2016-3-20 10:00:00', 1, '1:12:20', 'Good!', 1);
insert into Participation(SwimmerId, EventId, Committed, CommitTime,
Participated, Result, Comment, CommentCoachId)
values(2,6,1,'2016-3-9 10:00:00', 1, 'Good', 'Need to focus', 3);
insert into Participation(SwimmerId, EventId)
values(3,6);
insert into Participation(SwimmerId, EventId, Committed)
values(5,6,1);
insert into Participation(SwimmerId, EventId)
values(1,7);
insert into Participation(SwimmerId, EventId, Committed)
values(4,7,1);
insert into Participation(SwimmerId, EventId, Committed, CommitTime,
Participated, Result)
values(1,8,1,'2016-1-21 12:00:00', 1, 'winner');
insert into Participation(SwimmerId, EventId)
values(4,8);
insert into V_TaskList(MeetId, Required, Description)
values(1,0,'UHCL meet volunteer tasks');
insert into V_TaskList(MeetId, Required, Description, Penalty, PenaltyAmt)
values(2,1,'Shell Trial meet volunteer tasks','3 credits', 0);
insert into V_Task(VTL_Id, Name, Comment, Num_V)
values(1,'Officiating','Must be trained',2);
insert into V_Task(VTL_Id, Name, Num_V)
values(1,'Recording',3);
insert into V_Task(VTL_Id, Name)
values(1,'Diecting traffic');
insert into V_Task(VTL_Id, Name, Comment, Num_V)
values(2,'Officiating','Must be trained',3);
insert into V_Task(VTL_Id, Name, Num_V)
values(2,'Recording',2);
insert into V_Task(VTL_Id, Name)
values(2,'Diecting traffic');
insert into Commitment(CT_Id, VT_Id, CommitTime)
values(1,1,'2015-10-10 11:11:12');
insert into Commitment(CT_Id, VT_Id, CommitTime)
values(4,1,'2015-10-11 12:11:20');
insert into Commitment(CT_Id, VT_Id, CommitTime)
values(2,2,'2016-1-10 11:05:12');
insert into Commitment(CT_Id, VT_Id, CommitTime)
values(3,2,'2016-1-11 12:12:20');
insert into Commitment(CT_Id, VT_Id, CommitTime)
values(5,2,'2016-1-17 09:19:25');
insert into Commitment(CT_Id, VT_Id, CommitTime)
values(1,3,'2015-10-10 11:11:12');
insert into Commitment(CT_Id, VT_Id, CommitTime,CarriedOut,
Comment, CommentCoachId)
values(1,4,'2016-05-11 11:47:22',1,'Very dependable',1);
insert into Commitment(CT_Id, VT_Id, CommitTime,CarriedOut,
Comment, CommentCoachId)
values(4,4,'2016-05-12 06:37:12',0,'Sick and called',1);
insert into Commitment(CT_Id, VT_Id, CommitTime,CarriedOut,
Comment, CommentCoachId)
values(5,4,'2016-05-12 06:37:12',1,'Brought fruit',2);
insert into Commitment(CT_Id, VT_Id, CommitTime,Rescinded)
values(2,5,'2016-03-11 11:33:12',1);
insert into Commitment(CT_Id, VT_Id, CommitTime,Rescinded)
values(4,5,'2016-03-17 12:35:14',0);
insert into Commitment(CT_Id, VT_Id, CommitTime)
values(1,5,'2016-05-11 11:47:22');
insert into Commitment(CT_Id, VT_Id, CommitTime)
values(5,6,'2016-06-1109:15:22');
1. Required query:
select fname, lname, email from Swimmer where currentlevelid = 3;
Sample output:
2. Required query:
select swimmer.fname, Swimmer.lname from Swimmer join Event on event.LevelId = Swimmer.CurrentLevelId where Event.title = "100M Butterfly" And Event.MeetId = 1 order by Swimmer.fname;
Sample output:
3. Required query:
SELECT caretaker.FName, Caretaker.LName, Caretaker.phone FROM Caretaker join OtherCaretaker on OtherCaretaker.CT_Id = Caretaker.CT_Id join Swimmer on swimmer.SwimmerId = OtherCaretaker.SwimmerId group by swimmer.SwimmerId HAVING Swimmer.CurrentLevelId = 2;
Sample output:
4. Required query:
SELECT Caretaker.FName, Caretaker.LName from Caretaker join Swimmer on Swimmer.Main_CT_Id = Caretaker.ct_id GROUP by Caretaker.FName, Caretaker.LName HAVING count(Swimmer.SwimmerId) >= 2;
Sample output:
Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and...
Create three or more MySQL Data Control language (DCL) Statements using the Youth League Database. 1. A Create new user statement for the database 2. A statement that grants privileges to the new user 3. A statement that revokes privileges 1. A SQL Text File containing the SQL commands to create the database, create the table, and insert the sample test data into the table. 2. A SQL Text File containing the DCL SQL Statements. eted hemas Untitled Limit to...
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...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...
Using SQL and the following info Question 3: Find the people who do not have Viper Certification but are still assigned to Viper class ship Find the fname, lname, and ship_instance_id for all people who do not have Viper certification but are assigned to at least one instance of a Viper class ship (this includes all variants of Viper class ships). Return a row for every ship/person combination. Order your results by fname in ascending order. Use the BSG database...
Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it to create tables and populate with the supplied data. If there are any errors in the script fix them. It is your job to find the errors and correct them. This is important. You will need correct data for future exercises in this module. Then answer all questions. Write the answer below each question then take screen shot of the result. This script builds...
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'); /*...
SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...
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...
Use only a single SQL statement for each of the following questions 1 Give a listing of all the ssns,first names and the class descriptions of all the classes the students are taking. If there are no class _descriptions display 'No description is available yet'. (USE NVL) 2 Give a listing of only the lname and the class_code for students who are taking 'Introduction to C programming'. (Inner join) 3 Give a lising of all the class_descriptions and the number...
Using Oracle database Need help getting the JAVA code for the queries and the rest of the instructions. Need the table provided converted and fulfil the requirements. . For this project - you will be writing a program (you may choose whatever programming language you want) to read in your database tables from Lab 5, and then print out the contents of each of the tables. Additionally, you must print at least one query each with the following clauses: *JOIN (any...