Student: Name and Example Output

Homework Chapter 2 Part 2 Name ____________________
ZID# _________________
GIVEN the following tables and relationships

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 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

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

SELECT s.zid, count (g.grade)
FROM student s, grades g
WHERE s.zid=g.zid;

3) Find the names of faculty that do not live in one of the following 3 zipcodes. 60543, 60234, 60012. use not in ***

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

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

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

SELECT fname, lname
FROM student
WHERE zipcode in (‘60543’, ‘60234’,’60012’);