Name and Example Output
Chapter 2 Part 2 Kroenke Questions
grades(zid,cl_id, grade) faculty(fid, fname, lname, zipcode, email_address) zip(zipcode, city, state) courses(c_num, course_name, course_description, credit_hrs) classes(cl_id, c_num, fid) student(zid, fname, lname, zipcode, email_address, adv_id)
with the following referential integrity courses(c_num) -> classes(c_num) student(zid) -> grades(zid) classes(cl_id) -> grades(cl_id) faculty(fid) -> classes(fid) faculty(fid) -> student(adv_id) zip(zipcode) -> student(zipcode) zip(zipcode) -> faculty(zipcode)
Use the above information to create queries for the following questions
1) Display the the name of each student and what zipcode they live in. sort them by last and then first name in ascending order
Example output data
John James lives in 60543
Matt James lives in 60122
April Harmon lives in 60115
Ans: SELECT fname, lname +’ lives in ‘ + , Zipcode FROM student ORDERBY lname, fname;
2) Write a query to display the Student id and the number of grades they have received ***
example output
z12345 7 z14325 12 z11674 2
Ans: SELECT s.Zid, count(g.grades) FROM student s, grade g WHEREns.zid=g.zid FROM grades
3) Find the names of faculty that do not live in one of the following 3 zipcodes. 60543, 60234, 60012. use not in ***
Ans: SELECT fname,lname FROM faculty WHERE Zipcode not in (’60543’, ’60234, ‘60012’);
4) Find the names of courses that have the word Database in the course Description
Ans: SELECT course_name FROM courses WHERE course_description LIKE “* Database * “
5) Find the names of students who have a last name that starts with a N
Ans: SELECT fname,lname FROM student WHERE lname LIKE “N*”
6) Find the names of students that live in one of the following 4 zipcodes. 60543, 60115, 60234, 60012. use in
Ans: SELECT fname,lname FROM
grades(zid,cl_id, grade) faculty(fid, fname, lname, zipcode, email_address) zip(zipcode, city, state) courses(c_num, course_name, course_description, credit_hrs) classes(cl_id, c_num, fid) student(zid, fname, lname, zipcode, email_address, adv_id)
with the following referential integrity courses(c_num) -> classes(c_num) student(zid) -> grades(zid) classes(cl_id) -> grades(cl_id) faculty(fid) -> classes(fid) faculty(fid) -> student(adv_id) zip(zipcode) -> student(zipcode) zip(zipcode) -> faculty(zipcode)
Use the above information to create queries for the following questions
1) Display the the name of each student and what zipcode they live in. sort them by last and then first name in ascending order
Example output data
John James lives in 60543
Matt James lives in 60122
April Harmon lives in 60115
Ans: SELECT fname, lname +’ lives in ‘ + , Zipcode FROM student ORDERBY lname, fname;
2) Write a query to display the Student id and the number of grades they have received ***
example output
z12345 7 z14325 12 z11674 2
Ans: SELECT s.Zid, count(g.grades) FROM student s, grade g WHEREns.zid=g.zid FROM grades
3) Find the names of faculty that do not live in one of the following 3 zipcodes. 60543, 60234, 60012. use not in ***
Ans: SELECT fname,lname FROM faculty WHERE Zipcode not in (’60543’, ’60234, ‘60012’);
4) Find the names of courses that have the word Database in the course Description
Ans: SELECT course_name FROM courses WHERE course_description LIKE “* Database * “
5) Find the names of students who have a last name that starts with a N
Ans: SELECT fname,lname FROM student WHERE lname LIKE “N*”
6) Find the names of students that live in one of the following 4 zipcodes. 60543, 60115, 60234, 60012. use in
Ans: SELECT fname,lname FROM