Midterm Prep: OQL

1. Midterm Prep: OQL • The mindset you should be in is object oriented. • A number of you based on emails/queries I have been answering are still in the relational mindset • E.g. there is no real concept of a cross product in OQL (we deal with collections or bags) • You want to leverage the strengths of OQL – use the functions (appropriately).

2. Lets look at an example select distinctstruct(star1: s1, star2: s2) from s1 in Stars, s2 in Stars where s1.addr = s2.addr and s1.name < s2.name; • Note here s1 and s2 are collections – we are going to compare collections. • Do not think of this as a cross product/join – the underlying OO system. • I want you to in the exam when asked OQL queries to think object oriented. • Use functions!

3. Simple Query with Rename • Give the names of people who are older than 26 years old: • SELECT SName: p.name FROM p in People WHERE p.age > 26

4. Watch out for Path expressions • Find the names of the students of all tutors: • SELECT t.students.name FROM t in Tutors • Illegal use of . Above • Correct solution • SELECT s.nameFROM  t in Tutors, s in t.students • Alternate notation • SELECT s.nameFROM Tutors t, t.students s

5. Express the query in EnglishSubquery in FROM clause • What is this query trying to do? SELECT r.nameFROM ( SELECT t FROM Tutors t WHERE t.salary > 300 ) r, r.students sWHERE st IN s AND st.fee > 30 In some implementations the IN is implicit: SELECT t.nameFROM ( SELECT t FROM Tutors t WHERE t.salary > 300 ) r, r.students sWHERE s.fee > 30 • Ans: Find the names of all tutors who have a salary greater than 300 and who have at least one student paying more than 30.

6. What do these do? • SELECT t.nameFROM t in TAs WHERE t.salary = max ( select ta.salary from ta in TAs ) • Give the names of TAs with the highest salary • SELECT sname, avgFee: AVG(SELECT p.s.fee FROM partition p)FROM t in Tutors, t.students s GROUP BY sname: s.name • Give the names of the students and the average fee they pay their Tutors

7. Using subqueries in the Where clause • Give the names of people who are not Tas • Assume PEOPLE class and TA class • You can solve this without assuming any connections between them. • SELECT p.name FROM p in People WHERE not ( p.name in SELECT t.name FROM t in TAs ) • Views can be used to simplify these things – you are free to use them unless the question explicitly asks you not too.

8. Rest of the exam • Data Warehousing/Data Mining • Understand the defintions of support and confidence • Given a transactional dtabase be able to determine these values for given rules • Understand at a basic level what pivoting, selecting, drill down and roll up imply • Be able to manipulate tables such as the examples in the notes. • Conceptual questions • Why is sparse matrix management important in data warehouses? • What distinguishes them from traditional OLTP?

9. Object Relational • Brush up on simple declarations as discussed in the notes • Make sure you understand the difference between a row_type and an ADT • Be able to manipulate simple queries • Be able to instantiate simple functions • Conceptually these are examples of things you may be asked. • Differences between object relational and OODB and pure relational • What does the ADT bring to the table. • Role of less than and equals functions and why they are important

10. Relational SQL • Be able to solve simple and complicated queries • Pay particular attention to exists, not exists and mathematical logic operations (pg 207) • Pay attention to later stuff (e.g. recursive operations) • Conceptual stuff (these are examples) • What is the difference between, relational, object relational and OQL across different dimensions? • Querying • Performance • DISCLAIMER: All of these are just examples of things that could be asked on the midterm. You are responsible for everything covered in the notes and in the associated reading material.