Please Explain Code Answers Line Line Comments Questions Answers Trouble Understanding Eve Q43829252
Can you please explain the code answers to me line by line withcomments? I have the questions and answers but I am having troubleunderstanding everything that is going on. Thank you. This is inOrcale SQL, Chinook Database.
QUESTIONS:
ANSWERS:
hello yes sir, everything after question 3.1 is where it getsconfusing to me.1.0 Setting up Chinook In this section you will begin the process of working with the Chinook database Task – Set up the Chinook DB by executing the script found here 2.0 SQL Queries In this section you will be performing various queries against the Oracle Chinook database. 2.1 SELECT Task – Select all records from the Employee table. Task-Select all records from the Employee table where last name is King. Task-Select all records from the Employee table where first name is Andrew and REPORTSTO is NULL. 2.2 ORDER BY Task – Select all albums in album table and sort result set in descending order Task – Select first name from Customer and sort result set in ascending order 2.3 INSERT INTO Task – Insert two new records into Genre table Task – Insert two new records into Employee table Task – Insert two new records into Customer table 2.4 UPDATE Task – Update Aaron Mitchell in Customer table to Robert Walter Task – Update name of artist “Creedence Clearwater Revival” to “CCR” 2.5 LIKE Task – Select all invoices with a billing address like “T” 2.6 BETWEEN Task – Select all invoices that have a total between 15 and 50 Task – Select all employees hired between 1st of June 2003 and 1st of March 2004 2.7 DELETE Task – Delete a record in Customer table where the name is Robert Walter (There may be constraints that rely on this, find out how to resolve them). 3.0 SQL Functions In this section you will be using the Oracle system functions, as well as your own functions, to perform various actions against the database 3.1 System Defined Functions Task – Create a function that returns the current time. Task- create a function that returns the length of a mediatype from the mediatype table 3.2 System Defined Aggregate Functions Task-Create a function that returns the average total of all invoices Task – Create a function that returns the most expensive track 3.3 User Defined Scalar Functions Task – Create a function that returns the average price of invoice-line items in the invoice-line table 3.4 User Defined Table Valued Functions Task – Create a function that returns all employees who are born after 1968. 4.0 Stored Procedures In this section you will be creating and executing stored procedures. You will be creating various types of stored procedures that take input and output parameters. 4.1 Basic Stored Procedure Task – Create a stored procedure that selects the first and last names of all the employees. 4.2 Stored Procedure Input Parameters Task – Create a stored procedure that updates the personal information of an employee. Task – Create a stored procedure that returns the managers of an employee 4.3 Stored Procedure Output Parameters Task – Create a stored procedure that returns the name and company of a customer. 5.0 Transactions In this section you will be working with transactions. Transactions are usually nested within a stored procedure. You will also be working with handling errors in your SQL. Task – Create a transaction that given a invoiceld will delete that invoice (There may be constraints that rely on this, find out how to resolve them) Task – Create a transaction nested within a stored procedure that inserts a new record in the Customer table 6.0 Triggers In this section you will create various kinds of triggers that work when certain DML statements are executed on a table. 6.1 AFTER/FOR Task – Create an after insert trigger on the employee table fired after a new record is inserted into the table. Task – Create an after update trigger on the album table that fires after a row is updated in the table Task – Create an after delete trigger on the customer table that fires after a row is deleted from the table. 6.2 BEFORE Task – Create a before trigger that restricts the deletion of any invoice that is priced over 50 dollars. 7.0 JOINS In this section you will be working with combining various tables through the use of joins. You will work with outer, inner, right, left, cross, and self joins. 7.1 INNER Task – Create an inner join that joins customers and orders and specifies the name of the customer and the invoiceld. 7.2 OUTER Task – Create an outer join that joins the customer and invoice table, specifying the Customerld, firstname, last name, invoiceld, and total. 7.3 RIGHT Task – Create a right join that joins album and artist specifying artist name and title. 7.4 CROSS Task – Create a cross join that joins album and artist and sorts by artist name in ascending order. 7.5 SELF Task – Perform a self-join on the employee table, joining on the reports to column. 8.0 Indexes In this section you will be creating Indexes on various tables. Indexes can speed up performance of reading data. 8.1 Indexes Task – Create an index on of table of your choice conn chinook/p4ssword 1*2.1 SELECT */ SELECT * FROM EMPLOYEE; SELECT * FROM EMPLOYEE WHERE LASTNAME=’King’; SELECT * FROM EMPLOYEE WHERE FIRSTNAME=’Andrew’ AND REPORTSTO IS NULL; 9 10 11 7*2.2 ORDER BY*/ SELECT * FROM ALBUM ORDER BY TITLE DESC; SELECT FIRSTNAME FROM CUSTOMER ORDER BY CITY ASC; 12 13 14 1*2.3 INSERT INTO*/ INSERT INTO GENRE (GENREID, NAME) VALUES (26, ‘Electroswing’); INSERT INTO GENRE VALUES (27, ‘House’); 16 17 18 INSERT INTO EMPLOYEE (EMPLOYEEID, LASTNAME, FIRSTNAME, TITLE, EMAIL) VALUES (9, ‘Duck’, ‘Daffy’, ‘Mascot’, ‘daffy@chinookcorp.com’); INSERT INTO EMPLOYEE (EMPLOYEEID, LASTNAME, FIRSTNAME, EMAIL) VALUES (10, “Cena’, ‘John’, ‘john@chinookcorp.com’); 19 21 INSERT ALL INTO CUSTOMER (CUSTOMERID, FIRSTNAME, LASTNAME, EMAIL, SUPPORTREPID) VALUES (60, ‘Ronald’, ‘McDonald’, ‘ronald@mcdonald’, 4) INTO CUSTOMER (CUSTOMERID, FIRSTNAME, LASTNAME, EMAIL) VALUES (61, ‘Bob’, ‘Doe’, ‘bobdoe@ordinaryman.com’) SELECT * FROM DUAL; 1*2.4 UPDATE*/ UPDATE CUSTOMER SET FIRSTNAME=’Robert’, LASTNAME=’Walter’ WHERE FIRSTNAME=’Aaron’ AND LASTNAME=’Mitchell’; 30 33 34 UPDATE ARTIST SET NAME=’CCR’ WHERE NAME=’Creedence Clearwater Revival’; /*2.5 LIKE/ SELECT * FROM INVOICE WHERE BILLINGADDRESS LIKE ‘T’; 38 41 1*2.6 BETWEEN */ SELECT * FROM INVOICE WHERE TOTAL > 15 AND TOTAL < 50; SELECT * FROM EMPLOYEE WHERE HIREDATE > ’01-JUN-03′ AND HIREDATE < ’01-MAR-04′; 46 7*2.7 DELETE */ DELETE FROM INVOICELINE WHERE INVOICEID IN (SELECT INVOICEID FROM INVOICE WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM CUSTOMER WHERE FIRSTNAME=’Robert’ AND LASTNAME=’Walter’)); DELETE FROM INVOICE WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM CUSTOMER WHERE FIRSTNAME=’Robert’ AND LASTNAME=’Walter’); DELETE FROM CUSTOMER WHERE FIRSTNAME=’Robert’ AND LASTNAME=’Walter’; /*3.1 SQL Functions/ CREATE OR REPLACE FUNCTION GET_CURRENT_TIME RETURN TIMESTAMP IS CUR_DAY TIMESTAMP; BEGIN SELECT LOCALTIMESTAMP INTO CUR_DAY FROM DUAL; RETURN CUR_DAY; END GET_CURRENT_TIME; / 65 69 67 DECLARE CUR TIMESTAMP; BEGIN CUR := GET_CURRENT_TIME; DBMS_OUTPUT.PUT_LINE(‘Current time is ‘ || CUR); END; 73 / 75 73.1b/ CREATE OR REPLACE FUNCTION FETCH_MEDIA_NAME (REQ_ID IN NUMBER) RETURN VARCHAR2 IS MEDIA_NAME MEDIATYPE.NAME%TYPE; BEGIM 70 71 72 CUR := GET_CURRENT_TIME; DBMS_OUTPUT.PUT_LINE(‘Current time is ‘ || CUR); END; / 73 75 /3.16*/ CREATE OR REPLACE FUNCTION FETCH_MEDIA_NAME(REQ_ID IN NUMBER) RETURN VARCHAR2 IS MEDIA_NAME MEDIATYPE.NAME%TYPE; BEGIN SELECT NAME INTO MEDIA_NAME FROM MEDIATYPE WHERE MEDIATYPEID = REQ_ID; RETURN MEDIA_NAME; END FETCH_MEDIA_NAME; / 84 86 CREATE OR REPLACE FUNCTION MEDIATYPE_LENGTH(REQ_ID IN NUMBER) RETURN NUMBER AS LEN NUMBER; BEGIN SELECT LENGTH(FETCH_MEDIA_NAME (REQ_ID)) INTO LEN FROM DUAL; RETURN LEN; END MEDIATYPE_LENGTH; 100 /*3.2 System Defined Aggregate Functions/ CREATE OR REPLACE FUNCTION AVERAGE_TOTAL_OF_INVOICES RETURN NUMBER AS AVERAGE NUMBER; BEGIN SELECT AVG(TOTAL) INTO AVERAGE FROM INVOICE; RETURN AVERAGE; END AVERAGE_TOTAL_OF_INVOICES; / 106 107 108 109 110 111 112 113 114 DECLARE RES NUMBER; BEGIN RES := AVERAGE_TOTAL_OF_INVOICES; DBMS_OUTPUT.PUT_LINE (RES); END; 1 115 116 117 118 /*3.2b*/ CREATE OR REPLACE FUNCTION MOST_EXPENSIVE RETURN VARCHAR2 AS NAMES VARCHAR2(200); BEGIN SELECT NAME INTO NAMES FROM TRACK WHERE rownum = 1 AND UNITPRICE IN (SELECT MAX(UNITPRICE) FROM TRACK); RETURN NAMES; END MOST_EXPENSIVE; / 126 127 128 129 130 131 132 139 /*3.3 User Defined Functions/ CREATE OR REPLACE FUNCTION AVERAGE_INVOICELINE_ITEM_PRICE RETURN NUMBER AS AVERAGE NUMBER; TOTAL NUMBER := 0; CUR NUMBER; CNT NUMBER := 0; CURSOR S IS SELECT UNITPRICE FROM INVOICELINE; BEGIN OPEN S; LOOP FETCH S INTO CUR; TOTAL := TOTAL + CUR; CNT := CNT = 1; EXIT WHEN SNOTFOUND; END LOOP; CLOSE S; AVERAGE := TOTAL / CNT; RETURN AVERAGE; END AVERAGE INVOICELINE UTEM PRICE 141 147 148 144 EXIT WHEN S%NOT FOUND; END LOOP; CLOSE S; AVERAGE := TOTAL / CNT; RETURN AVERAGE; END AVERAGE_INVOICELINE_ITEM_PRICE; 148 149 150 / 151 152 153 154 DECLARE RES NUMBER; BEGIN RES := AVERAGE_INVOICELINE_ITEM_PRICE(); DBMS_OUTPUT.PUT_LINE (“AVERAGE PRICE OF INVOICELINE ITEMS IS: ‘ || RES); END; 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 1*3.4 User Defined Table Valued Functions/ CREATE OR REPLACE TYPE EMPLOYEE_OBJ IS OBJECT (EID NUMBER, FNAME VARCHAR2(20), LNAME VARCHAR2(20)); / CREATE OR REPLACE TYPE EMPLOYEE_SET AS TABLE OF EMPLOYEE_OBJ; 176 178 179 CREATE OR REPLACE FUNCTION EMPLOYEE_BORN_AFTER_1968 RETURN EMPLOYEE_SET IS ESET EMPLOYEE_SET := EMPLOYEE_SET (null); CURSOR S IS SELECT EMPLOYEEID, FIRSTNAME, LASTNAME FROM EMPLOYEE E WHERE EXTRACT (YEAR FROM E.BIRTHDATE) > 1968; EOBJ EMPLOYEE_OBJ := EMPLOYEE_OBJ (null, null, null); CNT NUMBER := 0; BEGIN OPEN S; LOOP FETCH S INTO EOBJ.EID, EOBJ.FNAME, EOBJ.LNAME; CNT := CNT + 1; ESET.EXTEND; ESET (CNT) := EMPLOYEE_OBJ (EOBJ.EID, EOBJ.FNAME, EOBJ. LNAME); EXIT WHEN S%NOTFOUND; END LOOP; CLOSE S; RETURN ESET; END EMPLOYEE_BORN_AFTER_1968; / 180 189 190 191 192 193 194 DECLARE EE EMPLOYEE_SET = EMPLOYEE_SET (null); BEGIN EE := EMPLOYEE_BORN_AFTER_1968; 195 196 199 FOR I IN 1 .. EE.COUNT LOOP DBMS_OUTPUT.PUT_LINE(‘Employee’ || EE(I). FNAME || : EE(I).LNAME || ‘ with ID:’ || EE(I).EID); END LOOP; END; / 202 203 204 205 206 208 209 210 211 /*4.1 Basic Stored Procedure / CREATE OR REPLACE PROCEDURE FIRST_AND_LAST IS CURSOR S IS SELECT FIRSTNAME, LASTNAME FROM EMPLOYEE; F_NAME EMPLOYEE.FIRSTNAME%TYPE; L_NAME EMPLOYEE. LASTNAME%TYPE; BEGIN OPEN S; LOOP FETCH S INTO F_NAME, L_NAME; DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE NAME: ‘ || F_NAME || EXIT WHEN SẢNOTFOUND; 213 ‘ || L_NAME); ENDA / BEGIN FIRST_AND_LAST; END; / 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 /*4.2 Stored Procedure Input Parameters*/ CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE (EID IN EMPLOYEE. EMPLOYEEID%TYPE, UE IN OUT EMPLOYEEXROWTYPE) IS ERES EMPLOYEEXROWTYPE; BEGIN SELECT * INTO ERES FROM EMPLOYEE WHERE EMPLOYEE. EMPLOYEEID = EID; 240 IF UE.LASTNAME IS NULL THEN UE. LASTNAME := ERES. LASTNAME; END IF; 242 243 244 245 246 247 IF UE.FIRSTNAME IS NULL THEN UE.FIRSTNAME := ERES. FIRSTNAME; END IF; 248 250 251 254 255 256 UPDATE EMPLOYEE E SET E. LASTNAME = UE. LASTNAME, E. FIRSTNAME = UE. FIRSTNAME, E.TITLE = UE.TITLE, E.BIRTHDATE = UE.BIRTHDATE, E.HIREDATE = UE. HIREDATE, E. ADDRESS = UE. ADDRESS, E.CITY = UE.CITY, E.STATE = UE.STATE, E. COUNTRY = UE.COUNTRY, E.POSTALCODE = UE. POSTAL CODE, E.PHONE = UE. PHONE, E. FAX = UE. FAX, E.EMAIL = UE. EMAIL WHERE E. EMPLOYEEID = EID; 259 264 265 DBMS_OUTPUT.PUT_LINE(‘Updated’ || EID); END UPDATE_EMPLOYEE; / 266 267 268 269 270 271 DECLARE CUR_EMP EMPLOYEEXROWTYPE; BEGIN SELECT * INTO CUR_EMP FROM EMPLOYEE WHERE EMPLOYEE. EMPLOYEEID = 4; 274 277 287 288 289 276 CUR_EMP. LASTNAME := ‘Parker’; UPDATE_EMPLOYEE (4, CUR_EMP); 278 END; 279 / 280 281 /*4.26*/ 282 CREATE OR REPLACE PROCEDURE MANAGERS_OF_EMPLOYEES 283 IS 284 CURSOR S IS 285 SELECT E1.FIRSTNAME, E2. LASTNAME 286 FROM EMPLOYEE E1 INNER JOIN EMPLOYEE E2 ON E1. EMPLOYEEID = E2. REPORTSTO; FNAME EMPLOYEE.FIRSTNAME%TYPE; LNAME EMPLOYEE. LASTNAME%TYPE; 290 BEGIN 291 OPEN S; LOOP 293 FETCH S INTO FNAME, LNAME; 294 DBMS_OUTPUT.PUT_LINE (“MANAGER NAME: ‘ || FNAME | 295 EXIT WHEN S%NOTFOUND; 296 END LOOP; 297 CLOSE S; 298 END MANAGERS_OF_EMPLOYEES; 2991 300 301 BEGIN 292 ‘ || LNAME); MANAGERS_OF_EMPLOYEES; END; / 302 303 304 305 306 307 308 1*4.3 Stored Procedure Output Parameters*/ CREATE OR REPLACE PROCEDURE COMPANY_NAME 309 310 CURSOR S IS SELECT FIRSTNAME, LASTNAME, COMPANY FROM CUSTOMER; FNAME CUSTOMER.FIRSTNAME%TYPE; LNAME CUSTOMER. LASTNAME%TYPE; CMPY CUSTOMER.COMPANY TYPE; BEGIN OPEN S; 317 318 LOOP 324 329 338 FETCH S INTO FNAME, LNAME, CMPY; EXIT WHEN SNOTFOUND; DBMS_OUTPUT.PUT_LINE (FNAME || ‘, ‘ || LNAME || ‘at’ || CMPY); END LOOP; CLOSE S; 325 END COMPANY_NAME; 326 / 327 328 BEGIN COMPANY_NAME; 330 END; 3311 332 333 334 /*5.0 Transactions 335 CREATE OR REPLACE PROCEDURE DELETE_INVOICE 336 (REQ_ID IN INVOICE.INVOICEID%TYPE) 337 BEGIN 339 DELETE FROM INVOICELINE WHERE INVOICEID = REQ_ID; 340 DELETE FROM INVOICE WHERE INVOICEID = REQ_ID; 341 COMMIT; 342 END DELETE_INVOICE; 343 / 344 345 BEGIN DELETE_INVOICE (10); 347 END; 348 / 349 350785.0b*/ 351 CREATE OR REPLACE PROCEDURE INSERT_CUSTOMER (NEW_CUSTOMER IN CUSTOMER%ROWTYPE) 353 IS 354 BEGIN 355 INSERT INTO CUSTOMER VALUES NEW_CUSTOMER; 356 COMMIT; 357 END INSERT_CUSTOMER; 358 / 359 360 DECLARE 361 ADD_ONE CUSTOMER ROWTYPE; 362 CURSOR S IS SELECT * FROM CUSTOMER ORDER BY CUSTOMERID DESC; BEGIN 365 OPEN S; LOOP FETCH S INTO ADD_ONE; 363 364 EXIT; END LOOP; CLOSE S; ADD_ONE.CUSTOMERID := ADD_ONE.CUSTOMERID + 1; INSERT_CUSTOMER (ADD_ONE); DBMS_OUTPUT.PUT_LINE(‘ADDED CUSTOMER WITH ID OF: ‘ || ADD_ONE.CUSTOMERID); END; / 375 376 377 378 379 380 381 /*6.1 Triggers/ CREATE OR REPLACE TRIGGER TR_EMPLOYEE_INSERT AFTER INSERT 380 381 382 383 384 385 386 387 388 CREATE OR REPLACE TRIGGER TR_EMPLOYEE_INSERT AFTER INSERT ON EMPLOYEE FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(‘After Insert’); END TR_EMPLOYEE_INSERT; / 390 391 DECLARE EMP EMPLOYEE%ROWTYPE; BEGIN SELECT * INTO EMP FROM (SELECT * FROM EMPLOYEE ORDER BY EMPLOYEEID DESC) WHERE rownum = 1; 392 393 394 EMP. EMPLOYEEID := EMP. EMPLOYEEID + 1; 399 495 398 INSERT INTO EMPLOYEE VALUES emp; END; 400 / 401 402 4031*6.16*/ 404 CREATE OR REPLACE TRIGGER TR_ALBUM_UPDATE AFTER UPDATE ON ALBUM 496 FOR EACH ROW 407 BEGIN 408 DBMS_OUTPUT.PUT_LINE(‘After Update’); 499 END TR_ALBUM_UPDATE; 410 / 411 412 BEGIN 413 UPDATE ALBUM 414 SET ALBUM. TITLE = ‘Balls to the wall Redux’ 415 WHERE ALBUM.ALBUMID = 2; 416 END; 417 / 418 419 420 /*6.1c*/ 421 CREATE OR REPLACE TRIGGER TR_CUSTOMER_DELETE AFTER DELETE ON CUSTOMER 423 FOR EACH ROW 424 BEGIN 425 DBMS_OUTPUT.PUT_LINE( ‘After Delete’); 426 END TR_CUSTOMER_DELETE; 427 / 428 429 DECLARE 430 ADD_ONE CUSTOMERXROWTYPE; 431 CURSOR S IS SELECT * FROM CUSTOMER ORDER BY CUSTOMERID DESC; 432 BEGIN OPEN S; FETCH S INTO ADD_ONE; CLOSE S; LON ADD_ONE.CUSTOMERID := ADD_ONE.CUSTOMERID + 1; INSERT_CUSTOMER (ADD_ONE); 440 441 442 DELETE FROM CUSTOMER WHERE CUSTOMER.CUSTOMERID = ADD_ONE.CUSTOMERID; END; / 443 444 445 1*7.1 Inner/ SELECT C.FIRSTNAME, C. LASTNAME, I. INVOICEID FROM CUSTOMER C INNER JOIN INVOICE I ON C.CUSTOMERID = I.CUSTOMERID; 447 448 449 450 451 452 453 454 455 456 457 458 1*7.2 Outer/ SELECT C.CUSTOMERID, C.FIRSTNAME, C. LASTNAME, I.INVOICEID, I. TOTAL FROM CUSTOMER C LEFT OUTER JOIN INVOICE I ON C.CUSTOMERID = I.CUSTOMERID; /*7.3 Right*/ 456 457 458 459 /*7.3 Right*/ SELECT ARTIST.NAME, ALBUM.TITLE FROM ALBUM RIGHT OUTER JOIN ARTIST ON ALBUM.ARTISTID = ARTIST.ARTISTID; 460 461 462 463 464 465 466 /*7.4 Cross*/ SELECT * FROM ALBUM CROSS JOIN ARTIST ORDER BY ARTIST.NAME ASC; 468 469 470 471 472 473 474 475 476 /*7.5 Self*/ SELECT * FROM EMPLOYEE E1 INNER JOIN EMPLOYEE E2 ON E1. REPORTSTO = E2. REPORTSTO; 477 478 479 480 481 482 483 484 485 486 487 488 489 492 491 492 493 494 495 496 /*7.6 Complicated Join assignments/ SELECT * FROM ALBUM AL INNER JOIN ARTIST AR ON AL.ARTISTID = AR.ARTISTID INNER JOIN TRACK T ON T.ALBUMID = AL.ALBUMID INNER JOIN PLAYLISTTRACK PT ON PT. TRACKID = T. TRACKID INNER JOIN PLAYLIST PL ON PL.PLAYLISTID = PT.PLAYLISTID INNER JOIN MEDIATYPE MT ON MT.MEDIATYPEID = T.MEDIATYPEID INNER JOIN GENRE G ON G. GENREID = T. GENREID INNER JOIN INVOICELINE IL ON IL.TRACKID = T. TRACKID INNER JOIN INVOICE I ON I. INVOICEID = IL.INVOICEID INNER JOIN CUSTOMER C ON C.CUSTOMERID = I.CUSTOMERID INNER JOIN EMPLOYEE E ON C.SUPPORTREPID = E.EMPLOYEEID; 497 498 499 500 501 exit; Show transcribed image text 1.0 Setting up Chinook In this section you will begin the process of working with the Chinook database Task – Set up the Chinook DB by executing the script found here 2.0 SQL Queries In this section you will be performing various queries against the Oracle Chinook database. 2.1 SELECT Task – Select all records from the Employee table. Task-Select all records from the Employee table where last name is King. Task-Select all records from the Employee table where first name is Andrew and REPORTSTO is NULL. 2.2 ORDER BY Task – Select all albums in album table and sort result set in descending order Task – Select first name from Customer and sort result set in ascending order 2.3 INSERT INTO Task – Insert two new records into Genre table Task – Insert two new records into Employee table Task – Insert two new records into Customer table 2.4 UPDATE Task – Update Aaron Mitchell in Customer table to Robert Walter Task – Update name of artist “Creedence Clearwater Revival” to “CCR” 2.5 LIKE Task – Select all invoices with a billing address like “T” 2.6 BETWEEN Task – Select all invoices that have a total between 15 and 50 Task – Select all employees hired between 1st of June 2003 and 1st of March 2004 2.7 DELETE Task – Delete a record in Customer table where the name is Robert Walter (There may be constraints that rely on this, find out how to resolve them).
3.0 SQL Functions In this section you will be using the Oracle system functions, as well as your own functions, to perform various actions against the database 3.1 System Defined Functions Task – Create a function that returns the current time. Task- create a function that returns the length of a mediatype from the mediatype table 3.2 System Defined Aggregate Functions Task-Create a function that returns the average total of all invoices Task – Create a function that returns the most expensive track 3.3 User Defined Scalar Functions Task – Create a function that returns the average price of invoice-line items in the invoice-line table 3.4 User Defined Table Valued Functions Task – Create a function that returns all employees who are born after 1968. 4.0 Stored Procedures In this section you will be creating and executing stored procedures. You will be creating various types of stored procedures that take input and output parameters. 4.1 Basic Stored Procedure Task – Create a stored procedure that selects the first and last names of all the employees. 4.2 Stored Procedure Input Parameters Task – Create a stored procedure that updates the personal information of an employee. Task – Create a stored procedure that returns the managers of an employee 4.3 Stored Procedure Output Parameters Task – Create a stored procedure that returns the name and company of a customer. 5.0 Transactions In this section you will be working with transactions. Transactions are usually nested within a stored procedure. You will also be working with handling errors in your SQL. Task – Create a transaction that given a invoiceld will delete that invoice (There may be constraints that rely on this, find out how to resolve them) Task – Create a transaction nested within a stored procedure that inserts a new record in the Customer table
6.0 Triggers In this section you will create various kinds of triggers that work when certain DML statements are executed on a table. 6.1 AFTER/FOR Task – Create an after insert trigger on the employee table fired after a new record is inserted into the table. Task – Create an after update trigger on the album table that fires after a row is updated in the table Task – Create an after delete trigger on the customer table that fires after a row is deleted from the table. 6.2 BEFORE Task – Create a before trigger that restricts the deletion of any invoice that is priced over 50 dollars. 7.0 JOINS In this section you will be working with combining various tables through the use of joins. You will work with outer, inner, right, left, cross, and self joins. 7.1 INNER Task – Create an inner join that joins customers and orders and specifies the name of the customer and the invoiceld. 7.2 OUTER Task – Create an outer join that joins the customer and invoice table, specifying the Customerld, firstname, last name, invoiceld, and total. 7.3 RIGHT Task – Create a right join that joins album and artist specifying artist name and title. 7.4 CROSS Task – Create a cross join that joins album and artist and sorts by artist name in ascending order. 7.5 SELF Task – Perform a self-join on the employee table, joining on the reports to column. 8.0 Indexes In this section you will be creating Indexes on various tables. Indexes can speed up performance of reading data. 8.1 Indexes Task – Create an index on of table of your choice
conn chinook/p4ssword 1*2.1 SELECT */ SELECT * FROM EMPLOYEE; SELECT * FROM EMPLOYEE WHERE LASTNAME=’King’; SELECT * FROM EMPLOYEE WHERE FIRSTNAME=’Andrew’ AND REPORTSTO IS NULL; 9 10 11 7*2.2 ORDER BY*/ SELECT * FROM ALBUM ORDER BY TITLE DESC; SELECT FIRSTNAME FROM CUSTOMER ORDER BY CITY ASC; 12 13 14 1*2.3 INSERT INTO*/ INSERT INTO GENRE (GENREID, NAME) VALUES (26, ‘Electroswing’); INSERT INTO GENRE VALUES (27, ‘House’); 16 17 18 INSERT INTO EMPLOYEE (EMPLOYEEID, LASTNAME, FIRSTNAME, TITLE, EMAIL) VALUES (9, ‘Duck’, ‘Daffy’, ‘Mascot’, ‘daffy@chinookcorp.com’); INSERT INTO EMPLOYEE (EMPLOYEEID, LASTNAME, FIRSTNAME, EMAIL) VALUES (10, “Cena’, ‘John’, ‘john@chinookcorp.com’); 19 21 INSERT ALL INTO CUSTOMER (CUSTOMERID, FIRSTNAME, LASTNAME, EMAIL, SUPPORTREPID) VALUES (60, ‘Ronald’, ‘McDonald’, ‘ronald@mcdonald’, 4) INTO CUSTOMER (CUSTOMERID, FIRSTNAME, LASTNAME, EMAIL) VALUES (61, ‘Bob’, ‘Doe’, ‘bobdoe@ordinaryman.com’) SELECT * FROM DUAL; 1*2.4 UPDATE*/ UPDATE CUSTOMER SET FIRSTNAME=’Robert’, LASTNAME=’Walter’ WHERE FIRSTNAME=’Aaron’ AND LASTNAME=’Mitchell’; 30 33 34 UPDATE ARTIST SET NAME=’CCR’ WHERE NAME=’Creedence Clearwater Revival’; /*2.5 LIKE/ SELECT * FROM INVOICE WHERE BILLINGADDRESS LIKE ‘T’; 38 41 1*2.6 BETWEEN */ SELECT * FROM INVOICE WHERE TOTAL > 15 AND TOTAL ’01-JUN-03′ AND HIREDATE 1968; EOBJ EMPLOYEE_OBJ := EMPLOYEE_OBJ (null, null, null); CNT NUMBER := 0; BEGIN OPEN S; LOOP FETCH S INTO EOBJ.EID, EOBJ.FNAME, EOBJ.LNAME; CNT := CNT + 1; ESET.EXTEND; ESET (CNT) := EMPLOYEE_OBJ (EOBJ.EID, EOBJ.FNAME, EOBJ. LNAME); EXIT WHEN S%NOTFOUND; END LOOP; CLOSE S; RETURN ESET; END EMPLOYEE_BORN_AFTER_1968; / 180 189 190 191 192 193 194 DECLARE EE EMPLOYEE_SET = EMPLOYEE_SET (null); BEGIN EE := EMPLOYEE_BORN_AFTER_1968; 195 196 199 FOR I IN 1 .. EE.COUNT LOOP DBMS_OUTPUT.PUT_LINE(‘Employee’ || EE(I). FNAME || : EE(I).LNAME || ‘ with ID:’ || EE(I).EID); END LOOP; END; / 202 203 204 205 206 208 209 210 211 /*4.1 Basic Stored Procedure / CREATE OR REPLACE PROCEDURE FIRST_AND_LAST IS CURSOR S IS SELECT FIRSTNAME, LASTNAME FROM EMPLOYEE; F_NAME EMPLOYEE.FIRSTNAME%TYPE; L_NAME EMPLOYEE. LASTNAME%TYPE; BEGIN OPEN S; LOOP FETCH S INTO F_NAME, L_NAME; DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE NAME: ‘ || F_NAME || EXIT WHEN SẢNOTFOUND; 213 ‘ || L_NAME); ENDA
/ BEGIN FIRST_AND_LAST; END; / 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 /*4.2 Stored Procedure Input Parameters*/ CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE (EID IN EMPLOYEE. EMPLOYEEID%TYPE, UE IN OUT EMPLOYEEXROWTYPE) IS ERES EMPLOYEEXROWTYPE; BEGIN SELECT * INTO ERES FROM EMPLOYEE WHERE EMPLOYEE. EMPLOYEEID = EID; 240 IF UE.LASTNAME IS NULL THEN UE. LASTNAME := ERES. LASTNAME; END IF; 242 243 244 245 246 247 IF UE.FIRSTNAME IS NULL THEN UE.FIRSTNAME := ERES. FIRSTNAME; END IF; 248 250 251 254 255 256 UPDATE EMPLOYEE E SET E. LASTNAME = UE. LASTNAME, E. FIRSTNAME = UE. FIRSTNAME, E.TITLE = UE.TITLE, E.BIRTHDATE = UE.BIRTHDATE, E.HIREDATE = UE. HIREDATE, E. ADDRESS = UE. ADDRESS, E.CITY = UE.CITY, E.STATE = UE.STATE, E. COUNTRY = UE.COUNTRY, E.POSTALCODE = UE. POSTAL CODE, E.PHONE = UE. PHONE, E. FAX = UE. FAX, E.EMAIL = UE. EMAIL WHERE E. EMPLOYEEID = EID; 259 264 265 DBMS_OUTPUT.PUT_LINE(‘Updated’ || EID); END UPDATE_EMPLOYEE; / 266 267 268 269 270 271 DECLARE CUR_EMP EMPLOYEEXROWTYPE; BEGIN SELECT * INTO CUR_EMP FROM EMPLOYEE WHERE EMPLOYEE. EMPLOYEEID = 4; 274 277 287 288 289 276 CUR_EMP. LASTNAME := ‘Parker’; UPDATE_EMPLOYEE (4, CUR_EMP); 278 END; 279 / 280 281 /*4.26*/ 282 CREATE OR REPLACE PROCEDURE MANAGERS_OF_EMPLOYEES 283 IS 284 CURSOR S IS 285 SELECT E1.FIRSTNAME, E2. LASTNAME 286 FROM EMPLOYEE E1 INNER JOIN EMPLOYEE E2 ON E1. EMPLOYEEID = E2. REPORTSTO; FNAME EMPLOYEE.FIRSTNAME%TYPE; LNAME EMPLOYEE. LASTNAME%TYPE; 290 BEGIN 291 OPEN S; LOOP 293 FETCH S INTO FNAME, LNAME; 294 DBMS_OUTPUT.PUT_LINE (“MANAGER NAME: ‘ || FNAME | 295 EXIT WHEN S%NOTFOUND; 296 END LOOP; 297 CLOSE S; 298 END MANAGERS_OF_EMPLOYEES; 2991 300 301 BEGIN 292 ‘ || LNAME);
MANAGERS_OF_EMPLOYEES; END; / 302 303 304 305 306 307 308 1*4.3 Stored Procedure Output Parameters*/ CREATE OR REPLACE PROCEDURE COMPANY_NAME 309 310 CURSOR S IS SELECT FIRSTNAME, LASTNAME, COMPANY FROM CUSTOMER; FNAME CUSTOMER.FIRSTNAME%TYPE; LNAME CUSTOMER. LASTNAME%TYPE; CMPY CUSTOMER.COMPANY TYPE; BEGIN OPEN S; 317 318 LOOP 324 329 338 FETCH S INTO FNAME, LNAME, CMPY; EXIT WHEN SNOTFOUND; DBMS_OUTPUT.PUT_LINE (FNAME || ‘, ‘ || LNAME || ‘at’ || CMPY); END LOOP; CLOSE S; 325 END COMPANY_NAME; 326 / 327 328 BEGIN COMPANY_NAME; 330 END; 3311 332 333 334 /*5.0 Transactions 335 CREATE OR REPLACE PROCEDURE DELETE_INVOICE 336 (REQ_ID IN INVOICE.INVOICEID%TYPE) 337 BEGIN 339 DELETE FROM INVOICELINE WHERE INVOICEID = REQ_ID; 340 DELETE FROM INVOICE WHERE INVOICEID = REQ_ID; 341 COMMIT; 342 END DELETE_INVOICE; 343 / 344 345 BEGIN DELETE_INVOICE (10); 347 END; 348 / 349 350785.0b*/ 351 CREATE OR REPLACE PROCEDURE INSERT_CUSTOMER (NEW_CUSTOMER IN CUSTOMER%ROWTYPE) 353 IS 354 BEGIN 355 INSERT INTO CUSTOMER VALUES NEW_CUSTOMER; 356 COMMIT; 357 END INSERT_CUSTOMER; 358 / 359 360 DECLARE 361 ADD_ONE CUSTOMER ROWTYPE; 362 CURSOR S IS SELECT * FROM CUSTOMER ORDER BY CUSTOMERID DESC; BEGIN 365 OPEN S; LOOP FETCH S INTO ADD_ONE; 363 364 EXIT; END LOOP; CLOSE S; ADD_ONE.CUSTOMERID := ADD_ONE.CUSTOMERID + 1; INSERT_CUSTOMER (ADD_ONE); DBMS_OUTPUT.PUT_LINE(‘ADDED CUSTOMER WITH ID OF: ‘ || ADD_ONE.CUSTOMERID); END; / 375 376 377 378 379 380 381 /*6.1 Triggers/ CREATE OR REPLACE TRIGGER TR_EMPLOYEE_INSERT AFTER INSERT
380 381 382 383 384 385 386 387 388 CREATE OR REPLACE TRIGGER TR_EMPLOYEE_INSERT AFTER INSERT ON EMPLOYEE FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(‘After Insert’); END TR_EMPLOYEE_INSERT; / 390 391 DECLARE EMP EMPLOYEE%ROWTYPE; BEGIN SELECT * INTO EMP FROM (SELECT * FROM EMPLOYEE ORDER BY EMPLOYEEID DESC) WHERE rownum = 1; 392 393 394 EMP. EMPLOYEEID := EMP. EMPLOYEEID + 1; 399 495 398 INSERT INTO EMPLOYEE VALUES emp; END; 400 / 401 402 4031*6.16*/ 404 CREATE OR REPLACE TRIGGER TR_ALBUM_UPDATE AFTER UPDATE ON ALBUM 496 FOR EACH ROW 407 BEGIN 408 DBMS_OUTPUT.PUT_LINE(‘After Update’); 499 END TR_ALBUM_UPDATE; 410 / 411 412 BEGIN 413 UPDATE ALBUM 414 SET ALBUM. TITLE = ‘Balls to the wall Redux’ 415 WHERE ALBUM.ALBUMID = 2; 416 END; 417 / 418 419 420 /*6.1c*/ 421 CREATE OR REPLACE TRIGGER TR_CUSTOMER_DELETE AFTER DELETE ON CUSTOMER 423 FOR EACH ROW 424 BEGIN 425 DBMS_OUTPUT.PUT_LINE( ‘After Delete’); 426 END TR_CUSTOMER_DELETE; 427 / 428 429 DECLARE 430 ADD_ONE CUSTOMERXROWTYPE; 431 CURSOR S IS SELECT * FROM CUSTOMER ORDER BY CUSTOMERID DESC; 432 BEGIN OPEN S; FETCH S INTO ADD_ONE; CLOSE S; LON ADD_ONE.CUSTOMERID := ADD_ONE.CUSTOMERID + 1; INSERT_CUSTOMER (ADD_ONE); 440 441 442 DELETE FROM CUSTOMER WHERE CUSTOMER.CUSTOMERID = ADD_ONE.CUSTOMERID; END; / 443 444 445 1*7.1 Inner/ SELECT C.FIRSTNAME, C. LASTNAME, I. INVOICEID FROM CUSTOMER C INNER JOIN INVOICE I ON C.CUSTOMERID = I.CUSTOMERID; 447 448 449 450 451 452 453 454 455 456 457 458 1*7.2 Outer/ SELECT C.CUSTOMERID, C.FIRSTNAME, C. LASTNAME, I.INVOICEID, I. TOTAL FROM CUSTOMER C LEFT OUTER JOIN INVOICE I ON C.CUSTOMERID = I.CUSTOMERID; /*7.3 Right*/
456 457 458 459 /*7.3 Right*/ SELECT ARTIST.NAME, ALBUM.TITLE FROM ALBUM RIGHT OUTER JOIN ARTIST ON ALBUM.ARTISTID = ARTIST.ARTISTID; 460 461 462 463 464 465 466 /*7.4 Cross*/ SELECT * FROM ALBUM CROSS JOIN ARTIST ORDER BY ARTIST.NAME ASC; 468 469 470 471 472 473 474 475 476 /*7.5 Self*/ SELECT * FROM EMPLOYEE E1 INNER JOIN EMPLOYEE E2 ON E1. REPORTSTO = E2. REPORTSTO; 477 478 479 480 481 482 483 484 485 486 487 488 489 492 491 492 493 494 495 496 /*7.6 Complicated Join assignments/ SELECT * FROM ALBUM AL INNER JOIN ARTIST AR ON AL.ARTISTID = AR.ARTISTID INNER JOIN TRACK T ON T.ALBUMID = AL.ALBUMID INNER JOIN PLAYLISTTRACK PT ON PT. TRACKID = T. TRACKID INNER JOIN PLAYLIST PL ON PL.PLAYLISTID = PT.PLAYLISTID INNER JOIN MEDIATYPE MT ON MT.MEDIATYPEID = T.MEDIATYPEID INNER JOIN GENRE G ON G. GENREID = T. GENREID INNER JOIN INVOICELINE IL ON IL.TRACKID = T. TRACKID INNER JOIN INVOICE I ON I. INVOICEID = IL.INVOICEID INNER JOIN CUSTOMER C ON C.CUSTOMERID = I.CUSTOMERID INNER JOIN EMPLOYEE E ON C.SUPPORTREPID = E.EMPLOYEEID; 497 498 499 500 501 exit;
Expert Answer
Answer to Can you please explain the code answers to me line by line with comments? I have the questions and answers but I am havi…
OR