Name and Example Output

Only available on StudyMode
  • Topic: Name, .jobs, University
  • Pages : 3 (492 words )
  • Download(s) : 271
  • Published : March 1, 2012
Open Document
Text Preview
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
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
tracking img