1 / 10

Midterm Prep: OQL

This preparation guide covers the mindset required for object-oriented querying in OQL, as well as concepts in data warehousing and data mining. It also includes topics on object-relational databases, relational SQL, and relational algebra.

gsaladin
Download Presentation

Midterm Prep: OQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related