Question

MySQL Practice

Student

StudentID	FirstName	LastName	Street	City	State	Zipcode
10001	Bruce	Wayne	123 Elm Street	Gotham City	New York	10028
10002	Peter	Parker	456 Corallville	New York	New York	10037
10003	Tony	Stark	777 Faith Ave	Malibu	California	60263

School

SchoolID	SchoolName	City	State	Zipcode
19837	Gotham High School	Gotham City	New York	10028
83649	Midtown High School	New York	New York	10037
73934	MIT	Cambridge	Massechusetts	02139

Attendee

AttendeeID	StudentID
100	10001
200	10002
300	10003

Attend

AttendeeID	SchoolID	Debt	IsTA
100	19837	100000	Yes
200	83649	50000	No
300	73934	120000	No

Chart

AttendeeID	TAID
100	213
200	215
300	788

Given the chart above, write the SELECT MYSQL statement for the following questions.

a. Find the name of attendee whose residence State includes string ‘lif’.

b. Select all the cities from Student table for city that ends with ‘K’.

c. Retrieve all information (Student Name, Street, City, etc.) about attendee whose

second letter of city name does not contain ‘o’.

d. Retrieve the name of attendees who has second highest debt of all attendees

e. Find the attendee with the highest and lowest debt

f. List all the TA (teaching assistant)

g. List all the people reporting to each TA

h. List all the attendees who have accumulated more than 10000 in debt and go to school in California

Answer 1

Student Table:

School Table:

Attendee table:

Attend Table:

Chart Table:

Queries:

a. select s.FirstName,s.LastName from student s,attendee a where s.state like "%lif%" and s.studentid=a.studentid;

b. select city from student where city like "%k";

c. select s.* from student s,attendee a where city not like "_o%" and s.studentID=a.studentID;

d. select s.firstname,s.lastname,t.debt from student s,attendee a,attend t where (select count(distinct t2.attendeeID) from attend t2 where t.debt<t2.debt)=1 and s.studentId=a.studentID and t.attendeeId=a.attendeeID;

e. select s.firstname, s.lastname,t.debt from student s,attendee a,attend t where (t.debt=(select max(t1.debt) from attend t1) or t.debt=(select min(t2.debt) from attend t2)) and s.studentId=a.studentID and t.attendeeId=a.attendeeID;

f.  select s.firstName,s.lastName,t.IsTA from student s, attend t, attendee a where t.IsTA="YES" and t.attendeeID=a.attendeeID and a.studentId=s.studentId;

g. select s.firstName,s.LastName,c.TAID from student s, chart c,attendee a where c.attendeeId=a.attendeeID and a.studentId=s.studentId;

h. select s.FirstName,s.LastName,t.Debt,scl.state from student s,attendee a,attend t, school scl where t.debt>1000 and scl.state="California" and t.attendeeID=a.attendeeID and t.schoolId=scl.schoolID and a.studentID=s.studentID;