INFO20003: Database Systems
Dr Renata Borovica-Gajic
Lecture 23
Overview, sample exam questions Part I
Week 12
INFO20003 Database Systems © University of Melbourne 1
Overview
INFO20003 Database Systems © University of Melbourne 3
Modelling – learn notations
Relationship cardinalities and constraints
Concept Chen’s not. Crow’s foot not.
INFO20003 Database Systems © University of Melbourne 4
Example
Write SQL statements to create the tables for the data model below. Be sure to specify primary and foreign keys. You do not need to specify whether the fields are NULL/NOT NULL.
INFO20003 Database Systems © University of Melbourne 5
SQL
Given the following schema, write a single SQL statement to correctly answer each of the following questions. DO NOT USE VIEWS to answer questions.
INFO20003 Database Systems © University of Melbourne 6
SQL: Q1
Write a query that returns the first name and last name of employees whose manager was hired prior to 01/01/2002.
INFO20003 Database Systems © University of Melbourne 7
SQL: Q2
Write a query that returns customers IDs whose company name is ‘Google’, and for each customer return the total number of orders and total quantities for all products that were not discontinued (‘1’ means discontinued, ‘0’ not discontinued).
INFO20003 Database Systems © University of Melbourne 8
Next lecture
• SampleexamquestionsPartII
INFO20003 Database Systems © University of Melbourne 13