Can you please explain the code answers to me line by line with comments? I have the questions and answers but I am having trouble understanding everything that is going on. Thank you. This is in Orcale SQL, Chinook Database.

QUESTIONS:

ANSWERS:

hello yes sir, everything after question 3.1 is where it gets confusing 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 '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 SNOTFOUND; 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