Question | Answer |
What are the benefits the Oracle Database Environment provides for business? | Oracle's database management system gives businesses the ability to coordinate and share computing, application, data, storage, or network resources across dynamic and geographically dispersed organizations and in turn save time, resources and money. |
What services and products does Oracle provide? | Information Technology services and products. |
What is a relational database? | A relational database is a group of data stored in tables that are related by means of a common field. |
What is the difference between application and system software? Provide an example. | Systems software consists of low-level programs designed to interact with hardware. Application Software is the code that allows end users to interact with computers (MS Word, Application Express for developing apps and accessing an Oracle Database.) |
How is data extracted from the database? | Data is extracted from the database by the SELECT statement. |
What is the difference between a projection, a selection, and join statement? | •Use the projection capability in SQL to choose the columns in a table.
•Use the selection capability in SQL to choose the rows in a table.
•Use the join capability in SQL to choose the columns from more than one table. |
How are all rows in a table selected? Provide an example. | SELECT *
FROM employees; |
How are only specific rows in a table selected? Provide an example. | SELECT employee_id, last_name
FROM employees
WHERE salary < 3000; |
Give an example of a keyword. | SELECT, FROM, WHERE... |
Give an example of a clause. | FROM employees; |
Give an example of a statement. | SELECT employee_id, last_name
FROM employees; |
What symbol designates all columns select in a SQL query? | * asterisk |
What keyword suppresses duplicates and why would there be a need to suppress duplicates? | The DISTINCT keyword suppresses data duplication. Example: to get a count of the departments being used. |
How do you tell a row from a column? | •A row is left to right and a column is top to bottom.
•A row holds an individual set of data and a column holds a group of the same data. |
What is a database field? | A database field is where a row and a column meet. |
What is the difference between a primary key and foreign key column? | Primary key is a unique identifier for each row. Foreign key is a column(s) that refers to a primary key column(s) in another table. |
How does the database application know what table to select to retrieve data? | The FROM clause specifies the table. |
What are the five simple guidelines, you use to construct valid SQL statements? | •SQL statements are not case sensitive.
•SQL statements can be on one or more lines.
•Keywords cannot be abbreviated or split across lines.
•Clauses are usually placed on separate lines.
•Indents are used to enhance readability. |
What is a column alias? | A column alias is a new heading/title for a column in a SQL statement. |
What are the rules for using a column alias? (part 1) | Part 1:
oRenames a column heading
oIs useful with calculations
oImmediately follows column name
oMay have optional AS keyword between column name and alias
Click on next slide for part 2 |
What are the rules for using a column alias? (part 2) | o Requires double quotation marks if the alias contains spaces, special characters or is case sensitive. |
Why use a column alias? | The selected column name is used as the heading/title for a column in a SQL statement. This heading (column name) may not be descriptive or may be difficult to understand or may be to long, so change the column heading by using a column alias. |
What are the arithmetic operators? Provide an example. | Addition, subtraction, multiplication, division +, -, *, / |
How does the database application choose what arithmetic operations to do first? | Please Excuse My Dear Aunt Sally.
Parenthesis, Exponents, Multiplication and Division, Addition and Subtraction working from left to right. |
What overrides the rules of precedence? | You can override the rules of precedence by using parentheses to specify the order in which operators are executed. |
What results when a null is used in a calculation? | If any value in an arithmetic expression is null, the result is null. |
If a column alias is wanted a certain way, it should be enclosed by? | By default, alias headings appear in uppercase. If the alias contains spaces or special characters, or is case sensitive, enclose the alias in double quotation marks (“ ”). |
How does the concatenation operator function? | The concatenation operator can link columns to other columns, arithmetic expressions, or constant values to create a character expression. Columns on either side of the operator are combined to make a single output column. |
What is a literal value and what are literal values enclosed by? | A literal value is a character, a number, or a date included in the SELECT list.
Literal values are enclosed in single (‘) quotation marks. |
What command displays the structure of a table? | The DESC command displays the structure of a table. |
List and define the four data types? | CHAR – fixed-length character value
NUMBER – number value
VARCHAR2 – variable-length character value
DATE – Date and time value |
When a column is defined as NOT NULL, what does this represent to the SQL programmer? | NOT NULL indicates that a column must contain data. |
Create a query to display the last name, job id, hire date and employee number for each employee. Label the column headings. | SELECT employee_id “Employee Number”, last_name “Last Name”, job_id “Job Title”, hire_date “Hire Date”
FROM employees; |
What clause restricts rows used by a SQL query? | The WHERE clause |
What is a condition? | A proposition on which another proposition depends; is composed of column names, expressions, constants and a comparison operator |
The WHERE clause can compare values. It consists of which three elements? | •column name
•comparison condition
•column name, constant, or list of values |
When does a column alias require double quotation marks? | When a space or a set syntax is needed |
All character strings and date values are enclosed in _______? Give an example. | Single-quotes
‘the cat’ ‘29-SEP-63’ |
The default date format is ________? Give an example. | DD-MON-RR ‘29-SEP- 63’ |
In what clause can an alias not be used? | An alias cannot be used in the WHERE clause. |
Is the BETWEEN condition inclusive or exclusive to its values? | Values are included in the clause calculation |
What is the membership condition? Give an example. | The IN condition; used to test for values in a list.
WHERE department_id IN (5, 10, 20) |
What is the LIKE condition used for? Give an example. | LIKE is used to combine pattern-matching characters
WHERE last_name LIKE ‘Smith’ |
List and describe the two wildcard symbols used with the ESCAPE Option. | percentage sign (%) – display all characters after this symbol
•
underscore(_) – display each individual characters |
Why does the WHERE clause use IS NULL when searching for a null condition? | The IS NULL condition checks for nulls – since a null value means the value is unavailable, unassigned…. so, you cannot test with the = because a null cannot be equal to any value. |
Why does the WHERE clause use IS NOT NULL when searching for a not null condition? | The IS NOT NULL condition checks for not nulls – values that are assigned, are available ignoring the null values. |
List and describe the three logical operators. | AND – returns true if both conditions are true
•
OR – returns true if either condition is true
•
NOT – returns true if the following condition is false |
What logical operator is calculated first? | NOT, then AND, then OR |
What do you do to override the default order of the rules of precedence? | Use parentheses |
What clause is used to sort the rows? | ORDER BY clause |
Are all character searches case sensitive? True/False | TRUE |
What is the default sort order of the rows of data? | ASC – ascending order |
Where in a SELECT statement does the ORDER BY clause go? Give an example. | Last line of a SELECT statement.
SELECT * FROM employees ORDER BY 1; |
If the ORDER BY clause is not used how is the data displayed? | The sort order is undefined. |
What three ways can the sort condition of the ORDER BY clause be written? Give an example of each. | An expression, an alias, column position
SELECT employee_id, last_name, salary ‘SAL’
FROM employees
ORDER BY employee_id, ‘SAL’, 2; |
Create a query to display the last name, salary and commission for all employees who earn a commission. Place the results in descending order by salary. Label the column headings. | SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL; |
Create a query to display the employees’ last name, job id, start date of employees hired between February 11, 1997 and May 5, 1998. Place the results in order by start date. Label the column headings. | SELECT last_name “Last Name”, job_id “Job Title”, hire_date “Start Date”
FROM employees
WHERE hire_date BETWEEN ’11-FEB-97’ AND ’05-MAY-98’
ORDER BY hire_date; |
Create a query to display all the information about employee King. | SELECT *
FROM employees
WHERE last_name LIKE ‘King’; |
Create a query to display the last names of all employees where the third letter of the last name is ‘k’ and who have an ‘a’ in their last name. | SELECT last_name
FROM employees
WHERE last_name LIKE ‘__k%’
AND last_name LIKE ‘%a%’; |
Describe the difference between single-row functions and multi-row functions. | Single row functions operate on single rows only and return one result per row.
•
Multiple row functions can manipulate groups of rows to give one result per group of rows. |
Think of the calculation (sal * 12) to view the annual salary of all employees, what concepts does it reinforce? | Order of precedence / order of operations |
Name all of the character manipulation functions | Case-Manipulation Functions - LOWER, UPPER, INITCAP
•
Character-Manipulation Functions - CONCAT, SUBSTR, LENGTH, INSTR, LPAD,RPAD, TRIM, REPLACE |
Name the “general” single row functions | nvl, nvl2, nullif, coalesce |
What does the Case Conversion “INITCAP” perform? | INITCAP converts alpha character values to uppercase for the first letter of each word, all other letters are lowercase |
What is the purpose of using the CASE conversion UPPER? | To match the query case with the case of the data in the database. |
Suppose you you have a table with only first initial and last name, all in the same field. As a competent data modeler, you know that first names should be in a column by themselves. How could you remove the first initial from each name with one command? | Use the SUBSTR character-manipulation function to extract the first name from the whole name |
How could you get rid of any extra spaces accidentally entered by the data entry clerk when names were entered? | Use CONCAT TO join the names together. |
What keyword would be used to find the longest name in the database? | LENGTH |
Why would we want to know the longest name? | Possible Answer: To determine how much space is needed for a column. |
Describe the difference between ROUND, TRUNC and MOD. | ROUND is used to round the decimal places to an 'n' decimal place.
TRUNC cuts off the values at a specified decimal place, no rounding is done.
MOD is the value of the remainder after division. |
What is the dual table? | The dual table is a "dummy" table of one row with one column used to do SELECT statements that have no underlying tables. |
What is SYSDATE? | SYSDATE is the current date and time of the database server. |
What would
SELECT sysdate
FROM dual;
Return? | Today's date and time from the database server. |
Why is the dual table needed? | Dual is needed to perform SELECT operations not based on existing tables. |
What are three usages of Arithmetic with dates? | date + number
date - number
date - date |