Busy. Please wait.
or

show password
Forgot Password?

Don't have an account?  Sign up 
or

Username is available taken
show password

why


Make sure to remember your password. If you forget it there is no way for StudyStack to send you a reset link. You would need to create a new account.
We do not share your email address with others. It is only used to allow you to reset your password. For details read our Privacy Policy and Terms of Service.


Already a StudyStack user? Log In

Reset Password
Enter the associated with your account, and we'll email you a link to reset your password.

Remove Ads
Don't know
Know
remaining cards
Save
0:01
To flip the current card, click it or press the Spacebar key.  To move the current card to one of the three colored boxes, click on the box.  You may also press the UP ARROW key to move the card to the "Know" box, the DOWN ARROW key to move the card to the "Don't know" box, or the RIGHT ARROW key to move the card to the Remaining box.  You may also click on the card displayed in any of the three boxes to bring that card back to the center.

Pass complete!

"Know" box contains:
Time elapsed:
Retries:
restart all cards




share
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.

  Normal Size     Small Size show me how

Oracle DB Design

Review for the Final Exam

QuestionAnswer
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
Created by: klaidley