INF3707 Exam Notes

INF3707 Exam Notes 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 This study source was downloaded by from CourseH on :47:55 GMT -05:00 This study resource was shared via CourseH SELECT isbn, ach, , AS profit FROM customers c JOIN orders o USING(customer#) JOIN orderitems oi USING(order#) JOIN books b USING(isbn) WHERE name = 'JAKE' AND ame = 'LUCAS' ORDER BY date, profit DESC 2.5 SELECT , #, FROM books b LEFT OUTER JOIN orderitems oi ON = LEFT OUTER JOIN orders o ON # = # LEFT OUTER JOIN customers c ON mer# = mer# GROUP FUNCTIONS 3.1 SELECT title, TO_CHAR(quantity*(paideach-cost), ‘$999.99’) AS Profit FROM books JOIN orderitems USING(isbn) WHERE order# = 1002 3.2 SELECT AVG(SUM((retail-cost)*quantity)) FROM orders JOIN orderitems USING(order#) JOIN books USING(isbn) GROUP BY order# REGULAR EXPRESSIONS 4i. REGEXP_REPLACE – will replace a regular expression pattern of characters with another string of characters wherever it occurs in a string ii. REGEXP_INSTR – allows you to search a string for a regular expression pattern and return the position of the pattern iii. REGEXP_SUBSTR – allows you to search a string for a regular expression pattern and return the pattern TABLES 5.1 CREATE TABLE Client_Data( Member_ID NUMBER(10), Last_Name VARCHAR(25) NOT NULL, Address VARCHAR2(100), City VARCHAR2(15), Phone VARCHAR2(15), JoinDate DATE, CONSTRAINT clientdata_member_id_pk PRIMARY KEY(Member_ID)) 5.2 DESC Client_Data 5.3 CREATE SEQUENCE Client_Data_ID_SEQ START WITH 101 INCREMENT BY 1 MAXVALUE 5000 NOCACHE; 5.4 INSERT INTO Client_Data(Member_ID, Last_Name, Address, City, Phone, JoinDate) VALUES(Client_Data_ID_SEQ.NEXTVAL, ‘Brown’, ‘283 King Street’, ‘Pretoria’, ‘’, ’08 June 2016’) This study source was downloaded by from CourseH on :47:55 GMT -05:00 This study resource was shared via CourseH OR INSERT INTO Client_Data VALUES(Client_Data_ID_SEQ.NEXTVAL, ‘Brown’, ‘283 King Street’, ‘Pretoria’, ‘’, ’08 June 2016’) 5.5 ALTER TABLE Client_Data ADD Next_Of_Kin VARCHAR2(50); DESC Client_Data; 5.6 ALTER TABLE Client_Data DROP COLUMN Next_Of_Kin; DESC Client_Data; 5.7 CREATE SYNONYM CLIENTDET FOR Client_Data_ID_SEQ 5.8 CREATE INDEX ClientData_lastname_idx ON Client_Data(last_name); SELECT index_name FROM user_indexes; DROP INDEX ClientData_lastname_idx; 5.9 CREATE USER Scott IDENTIFIED BY Scott_Password1 PASSWORD EXPIRE; GRANT CREATE SESSION TO Scott; GRANT select, insert ON Client_Data TO Scott WITH GRANT OPTION; May/June 2017 MQC 1.1 c - WITH CHECK OPTION constraint: The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don’t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause. For example, if a view consists of books only in the Cooking category, and the user attempts to change the category of a book in the view to Family Life, the change isn’t allowed if WITH CHECK OPTION was included when the view was created. Why? The change would mean that the book is no longer listed in the view, which consists of books only in the Cooking category. If WITH CHECK OPTION is omitted when the view is created, any valid DML operation is allowed, even if the result is that rows being changed are no longer included in the view. However, if you’re creating a view with the sole purpose of displaying data, the WITH READ ONLY option can be used instead to ensure that data can’t be changed. 1.2 b – although it creates both synonyms, because you’re running from your domain it will run see the employee synonym, if another user runs it from a public domain it will run the departments synonym. There is no S1 table so d does not apply, s1 is merely a synonym, not a table on its own. 1.3 b - I think this is a trick question – It’s asking you to change the VALUE, not the PRIMARY KEY. The only criteria when changing the value would be that it cannot be NULL & must be unique. If we were changing the PRIMARY KEY, we would need to copy it to a new column, SELECT the new column we wanted as PRIMARY KEY 1.4 b - It is using the sign in its search criteria therefore not equality search. This study source was downloaded by from CourseH on :47:55 GMT -05:00 This study resource was shared via CourseH 1.5 c - Returns the number of rows containing a value in the identified field. Rows containing NULL values in the field aren’t included in the results. To count rows containing NULL values, use an asterisk (*) rather than a field name. 1.6 d - ’00,999.99’is the format they want 1230 displayed in therefore 01,230.00. The 9 before the decimal represents optional values & the zero represents mandatory values, so if the value is NULL in the position where a 0 is placed, the system will display 0 but it the value is NULL in the position where a 9 is placed, it won’t display anything, ie ’99,999.99’ will display 1,230.00 1.7 c - single-row function is a function that returns one row of results for each record processed 1.8 d - A TIMESTAMP datatype is available for storing time zone information as part of the date value in a column. The DATE datatype doesn’t store time zone information. 1.9 b - The data being displayed is grouped, so adding a single record to the view isn’t possible. 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 table. 1.10 b - NVL allows you to swop NULL with a value 1.11 a - = sign is used therefore equality 1.12 d - CH.7, text book pg 230, ebook pg 253 1.13 a - CH.7, text book pg 231, ebook pg 254 1.14 c - simple index hence no extra keywords. CH.6, text book pg 206, ebook pg 229 1.15 d - same as 1.6 above 1.16 b - CH. 8, text book pg 279, ebook pg 302 1.17 c - CH. 8, text book pg 279, ebook pg 302 1.18 b – CH.2, text book pg 26, ebook pg 49 1.19 c - Commands used to create or modify database tables are called data definition language (DDL) commands. 1.20 d - CREATE TABLE ... AS Creates a table from existing database tables, using the AS clause and subqueries.