OMIS 452 EXAM 1 Questions with Correct Answers (Grade A+)
Question 1: What is Information?
Answer: Organized Data
Question 2: Name 4 different relational database management systems
Answer: Hana DB2 MySQL Oracle
Question 3: What do the following stand for DBMS SQL
Answer: DBMS is Database management system SQL is structured query language
Question 4: What is the purpose of a query
Answer: To answer a question or request
Question 5: Given the table student(zid,lname,fname,e mail,major) Write a query to display all
columns, all rows of the table student
Answer: Select zid, lname, fname, email, major from student;
Question 6: Given the table student(zid,lname,fname,e mail,major,age) Write a query to display the
columns zid and fname for all students whose age is either 23 or 24
Answer: SELECT zid, fname FROM student WHERE age = 23 or age = 24;
Question 7: Find the names of students who have a last name that starts with a N
Answer: Select lname, fname from student where lname like 'N%';
Question 8: GIVEN the following tables and relationships Underlined attributes are the primary
keys bolded attributes are foreign keys student(zid, fname, lname, zipcode, email_address, adv_id)
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) grades(zid,cl_id, grade) 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) Write a query to display all
Answer: Select classes.cl_id from classes, courses where classes.c_num=courses.c_num and
course_name='Operating Systems 649';
Page 1
, Question 9: Underlined attributes are the primary keys bolded attributes are foreign keys
student(zid, fname, lname, zipcode, email_address, adv_id) 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) grades(zid,cl_id, grade) 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) Write a query to find the names of students along with the city and
state they live in. Display the
Answer: select fname, lname, city, state from student, zip where student.zipcode= zip.zipcode order by
state, city;
Question 10: Underlined attributes are the primary keys bolded attributes are foreign keys
student(zid, fname, lname, zipcode, email_address, adv_id) 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) grades(zid,cl_id, grade) 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) Write a query to find the names and zipcode of students that live in
any of the following zipcodes,
Answer: Select lname, fname, zipcode from student where zipcode='60115' or zipcode='60543' or
zipcode='60454' order by lname;
Question 11: Underlined attributes are the primary keys bolded attributes are foreign keys
customer(cus_id, cus_first, cus_last, street_addr, zipcode, ph_number) employee(eid, fname, lname,
zipcode, street_address, zipcode,home_phone) cars(vin,make,model,year1, color,cost,new_used)
purchases(pur_id,vin,cus_id ,eid,date_purchased, price_paid) try_out(try_id,cus_id,vin,eid,
date_tried,opinion) zip(zipcode, city, state) with the following referential integrity customer(cus_id)
-> purchases(cus_id) customer(cus_id) -> try_out(cus_id) employee(eid) -> purchases(eid)
employee(eid) -> try_out(eid) cars(vin) -> purchases(vin) cars(vin) -> try_out(vin) zip(zipcode) ->
customer(zipcode) zip(zipcode) - >employee(zipcode)
Answer: Select count(*) from customer,purchases where customer.cus_id=purchases.cus_id and
customer.cus_first='Jane' and customer.cus_last='Smith';
Question 12: Underlined attributes are the primary keys bolded attributes are foreign keys
student(zid, fname, lname, zipcode, email_address, adv_id) 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) grades(zid,cl_id, grade) 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) Write a query to display the number of faculty who have taught a
class with a
Page 2
Question 1: What is Information?
Answer: Organized Data
Question 2: Name 4 different relational database management systems
Answer: Hana DB2 MySQL Oracle
Question 3: What do the following stand for DBMS SQL
Answer: DBMS is Database management system SQL is structured query language
Question 4: What is the purpose of a query
Answer: To answer a question or request
Question 5: Given the table student(zid,lname,fname,e mail,major) Write a query to display all
columns, all rows of the table student
Answer: Select zid, lname, fname, email, major from student;
Question 6: Given the table student(zid,lname,fname,e mail,major,age) Write a query to display the
columns zid and fname for all students whose age is either 23 or 24
Answer: SELECT zid, fname FROM student WHERE age = 23 or age = 24;
Question 7: Find the names of students who have a last name that starts with a N
Answer: Select lname, fname from student where lname like 'N%';
Question 8: GIVEN the following tables and relationships Underlined attributes are the primary
keys bolded attributes are foreign keys student(zid, fname, lname, zipcode, email_address, adv_id)
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) grades(zid,cl_id, grade) 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) Write a query to display all
Answer: Select classes.cl_id from classes, courses where classes.c_num=courses.c_num and
course_name='Operating Systems 649';
Page 1
, Question 9: Underlined attributes are the primary keys bolded attributes are foreign keys
student(zid, fname, lname, zipcode, email_address, adv_id) 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) grades(zid,cl_id, grade) 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) Write a query to find the names of students along with the city and
state they live in. Display the
Answer: select fname, lname, city, state from student, zip where student.zipcode= zip.zipcode order by
state, city;
Question 10: Underlined attributes are the primary keys bolded attributes are foreign keys
student(zid, fname, lname, zipcode, email_address, adv_id) 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) grades(zid,cl_id, grade) 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) Write a query to find the names and zipcode of students that live in
any of the following zipcodes,
Answer: Select lname, fname, zipcode from student where zipcode='60115' or zipcode='60543' or
zipcode='60454' order by lname;
Question 11: Underlined attributes are the primary keys bolded attributes are foreign keys
customer(cus_id, cus_first, cus_last, street_addr, zipcode, ph_number) employee(eid, fname, lname,
zipcode, street_address, zipcode,home_phone) cars(vin,make,model,year1, color,cost,new_used)
purchases(pur_id,vin,cus_id ,eid,date_purchased, price_paid) try_out(try_id,cus_id,vin,eid,
date_tried,opinion) zip(zipcode, city, state) with the following referential integrity customer(cus_id)
-> purchases(cus_id) customer(cus_id) -> try_out(cus_id) employee(eid) -> purchases(eid)
employee(eid) -> try_out(eid) cars(vin) -> purchases(vin) cars(vin) -> try_out(vin) zip(zipcode) ->
customer(zipcode) zip(zipcode) - >employee(zipcode)
Answer: Select count(*) from customer,purchases where customer.cus_id=purchases.cus_id and
customer.cus_first='Jane' and customer.cus_last='Smith';
Question 12: Underlined attributes are the primary keys bolded attributes are foreign keys
student(zid, fname, lname, zipcode, email_address, adv_id) 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) grades(zid,cl_id, grade) 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) Write a query to display the number of faculty who have taught a
class with a
Page 2
