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
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;
MySQL Practice Student StudentID FirstName LastName Street City State Zipcode 10001 Bruce Wayne 123 Elm Street...
Founded in 2006 by Blake Mycoskie, TOMS Shoes was an American footwear company based in Santa Monica, California. Although TOMS Shoes was a for-profit business, its mission was more like that of a not-for-profit organization. The firm’s reason for existence was to donate to children in need one new pair of shoes for every pair of shoes sold. Blake Mycoskie referred to it as the company’s “One for One” business model. While vacationing in Argentina during 2006, Mycoskie befriended children...