Question #1: When you are grouping what does the where "do and" what does the having do?

Question #2: I have a table with idno, name, dept, jobcode, and salary. Write the code to group by jobcode eliminating any one with a salary greater than a certain amount. Display the salary average for each group but eliminate a specific jobcode from the results. The output should include jobcode and average salary for each group.

Question #3: You have a table with idno, name, dept, jobcode, salary. Write the SQL to show the jobcode and the minimum salary grouped by jobcode , but you only want to see the groups that have a minimum salary less the the average salary divided by 2.

Question #4: With subqueries, what code is needed to allow the inner select to return multiple rows/records? Explain.

Question #5: Explain when a cursor is needed and the function it serves? Give a detailed description of what the cursor does, how you determine what goes in the cursor, how the information gets into the cursor, how you step through the information in the cursor, and how you know that you have completed processing all of the records in the cursor.

Question #6: Explain the difference and similarities between a function, a procedure and a trigger. When is each one used?

Question #7: What is a trigger associated with, when does it "fire" and why would the developer choose to use one?

Question #8: Explain the concept of a primary and foreign key and how they can be used in a relational database.

Question #9: Assume you are populating the following set of tables the first time. The tables are being created with information from your paper files so you have data to enter into all four tables. Underline and bold means a primary key. First determine what would be setup as foreign keys and then explain the order that you would use in populating the tables.

Library:

Customer (acctno, name, address)

Checkout Header (acctno, date)

Checkout Detail (acctno, date, ISBN #, due date)

Books(ISBN#, title, primary author, date published...)

Question #10: Show me the create to create the Checkout Detail table with the primary key. Note the primary key is being created at the same time that you create the table. Show me the create to create the Checkout Header without a primary key and then the statement to modify the table by making a primary key.

Question #11: Show me the code to create the foreign keys that link these tables.

Question #12: You want to "write a "PL/SQL program that has a cursor that brings in all students with a CI major and a gpa of 3.2 or higher and numcredits > 45 and who have submitted their intent to graduate form.. Then you want to process the records in the cursor to determine the level of honors. Lets say 3.2 to 3.49 is Cum Laude, Magna Cum Laude is 3.5 to 3.79 and Summa Cum Laude is 3.8 or higher. You want to update the table (gradstatus) with that information.

Student (studentid, studentname, majorcode, optioncode, numcredits, formsub, GPA, gradstatus)

Question #13: Run the program you wrote in question #12

Question #14: You want to "write a" PL/SQL program that uses two cursors. The first cursor should hold the records from the major table. The second cursor should hold the records from the student table. The program should process the students one majorcode at a time. It should count the number of students in the major, and the numcredits earned by students in that major. That information should be written to a separate table.

Input tables:

Major (majorcode, majorname, advisor)

Student(studentid, studentname, majorcode, optioncode, numcredits, formsub, GPA, gradstatus)

Output table:

Statistics (majorcode, numstudents, numcredits)

Question #15: Run the program you wrote in question #14.

pur-new-sol

Related Questions