INF3707 Exam Notes

INF3707 Exam Notes Oct/Nov 2017 MQC 1.1 b.View – virtual table, not table on its own but mirrors table(temp table). Multiple tables – complex, single table – simple. In addition, the function and GROUP BY clause prevent changing the displayed data because each record might represent more than one row in the underlying key-preserved Reasons for view table – security, not direct DB access, efficiency – less direct DB hits table. CH. 13, text book pg 517 e-book pg 539 1.2 c. _ and % allows white characters - _ allows 1 unknown characters, % allows many unknown characters. _ and % must be used with the LIKE keyword. CH. 8, text book pg 287, ebook pg 310 1.3 b. NVL allows you to swop NULL with a value. CH. 10, Text book pg 377, ebook pg 400 1.4 a. statement uses the = sign. 1.5 d. CH.7, text book pg 230, ebook pg 253 1.6 a. CH.7, text book pg 231, ebook pg 254 1.7 c. simple index hence no extra keywords CH.6, text book pg 206, ebook pg 229 1.8 d. CH. 10, text book pg 383, ebook pg 406 1.9 b. CH. 8, text book pg 279, ebook pg 302 1.10 c. CH. 8, text book pg 279, ebook pg 302 JOINS 2.1 Difference between inner & outer join: Both inner & outer are used to combine rows for different tables into a single result. The difference is that inner join does not allow/retrieve non-matching rows (matched by PK & FK) where outer join does. The outer join will display ALL records from the table selected (by LEFT or RIGHT keyword) as well as the matching records from the other table that is in the query. The inner join will only display the records that are matching between the two tables. 2.2 The purpose of the qualifier is to avoid possible ambiguity, ie if column ISBN is in both tables then when you are defining tables make an alias/qualifier, IE Books b and Orders o then when querying the column it would be b.ISBN and o.ISBN so when running the query it knows which table to look at. It is required when you are retrieving information from tables that have common columns. 2.3 The join USING approach joins tables based on the specified common columns, which must have the same name. The join ON approach joins tables based on a stated condition; it must be used if the common columns have different names. 2.4 Table structure Fig. 9.5 text book pg 302, ebook pg 325