Can someone please help me with these questions.
Court (schema)
CourtID CourtName CourtStartDate CourtType
int string date string
Criminal
Civil
Vehicle
CourtMembers
CourtMembersID CourtID ProsecutorID CourtMemberStatus CourtMemberSignDate
int int int string date
Responded
Converted
Cancelled
Attended
Prosecutors
ProsecutorID ProsecutorName AccountID
int String int
- In the CourtMembers table, there should only be one unique CourtID / ProsecutorID combination. As a QA check, write a SQL statement that returns any CourtID / ProsecutorID combination that occurs more than once in the table.
- If our organizational logic dictates that in cases of duplication, we only want to keep the first CourtID / ProsecutorID combination based on the CourtMembersSignUpDate, write a SQL statement that returns the entire CourtMembers table, but only keeps the first CourtID / ProsecutorID combination. Be mindful of potential “ties” in the CourtMembersSignUpDate
- Write a SQL statement that returns all Court Names that have no ‘Attended’ court members, excluding Courts that have yet to start. The result should be only a list of Court IDs.
- Write a SQL statement that returns each court type and the three prosecutor names that have the most attendances for each court type.