Answers to Chapter 7

Reviewing It

1: What is the purpose of SQL functions?
A1: SQL functions perform a variety of tasks such as date compares, date formatting, a host of character functions, as well as several numerical functions
2: What is the DUAL table useful for?
A2: The DUAL table contains one column and one row and is useful when wanting to run a function once
3: Explain SQL concatenation.
A3: Concatenation is when two or more columns are merged together, many times merged with some text items, to form a single column of outout
4: The DECODE statement is what?
A4: The DECODE function is the IF-THEN-ELSE logic to SQL
5: When might you use the HAVING clause?
A5: to limit the rows returned from a GROUP BY clause
6: Briefly describe what a JOIN is.
A6: A join is when two or more tables are required to be used in the same SQL statement
7: What is 'qualification' and why is it important to a self-join?
A7: A self-join is where you are joining the same table to itself and you will need to qualify the columns in the where clause so Oracle knows which table a column belongs to
8: Name the four types of sub-queries.
A8: those that return a single row; those that return multiple rows; those that return multiple columns in a single row; and those that return multiple columns in multiple rows
9: What is an IN-LINE view?
A9: Oracle9i supports a subquery in the FROM clause. This type of SQL statement is also known as an 'INLINE View.'

Checking It

1: SQL functions falls into

  1. 1 Category

  2. 2 Categories

  3. 3 Categories

  4. Does not have categories

A1: 2: single-row functions and multiple-row functions
2: A single-row function can only return one row.

True/False

A2: True
3: In a DATE mask, the RR means

  1. Current century if the year tested is between 00 and 49 and the current year is between 50 and 99

  2. Current century if the year tested is between 00 and 49 and the current year is between 00 and 49

  3. Next century if the year tested is 00 through 49 and the current year is 50 through 99

  4. Current century if the year tested is 50 and 99 and the current year is between 00 and 49

A3: b
4: When might a group function return more than one row?

  1. When used with a WHERE clause

  2. When used with an ORDER BY clause

  3. When used with a GROUP BY clause

  4. Cannot have more than one row returned

A4: c
5: Cartesian Joins have a valid use.

True/False

A5: False, usually occurs when the WHERE clause is accidentally left off of a join condition
6: Oracle9i supports a subquery in the FROM clause.

True/False

A6: True
7: SQL*Plus can

  1. Take input from the command line

  2. Format column output

  3. Sum the contents of a column

  4. All of the above

A7: d
8: SQL*Plus can be used to create GUI reports.

True/False

A8: False, Character mode only.

Applying It

Independent Exercise 1:

  • SELECT the departments with a count of the rows for each department.

Independent Exercise 2:

  • Build a Master/Detail report using DEPT and EMP.

  • Show just the Department Name on the master record.

  • Show the employees name and salary in the Detail.

  • Create a Cross-matrix report using EMP, showing the salaries summed by JOB and by DEPTNO.

Independent Exercise 3:

  • UPDATE the EMP table, give everyone a 10% increase in salary.

  • Show the values of ENAME and SAL prior to the UPDATE.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.224.54.255