Oracle Database/SQL
< Oracle Database(Redirected from Oracle Programming/Using Single-Row Functions to Customize Output)Contents
Retrieving Data Using the SQL SELECT StatementEdit
List the capabilities of SQL SELECT statementsEdit
Selection, projection, join
Execute a basic SELECT statementEdit
- Select All Columns:
Select * from table_name;
- Select Specific Columns:
Select column1, column2 from tables_name;
- Use Column Heading Defaults
- Use Arithmetic Operators:
Select 12 salary+100 from emp --sell value is 2. Result: 12 * cell's value + 100 --i.e. 12 * 2 + 100= 124
- Understand Operator Precedence
- Learn the DESCRIBE command to display the table structure
Type- DESCRIBE table_name; *NOTE: Your Oracle user and/or schema must have permissions/privaliages or be within the schema to describe the table. You can use the data_dictionary views to get the table info.
Restricting and Sorting DataEdit
Limit the rows that are retrieved by a queryEdit
- Write queries that contain a WHERE clause to limit the output retrieved
- List the comparison operators and logical operators that are used in a WHERE clause
- Describe the rules of precedence for comparison and logical operators
- Use character string literals in the WHERE clause
Sort the rows that are retrieved by a queryEdit
- Write queries that contain an ORDER BY clause sort the output of a SELECT statement
- Sort output in descending and ascending order
Use ampersand substitution to restrict and sort output at runtimeEdit
the ampersand operator is used to take the input at runtime( ex:-&employeename) and if ampersand is used twice i.e && then it will take the input of single ampersand operator and is used to provide data to the query at runtime.
Using Single-Row Functions to Customize OutputEdit
Describe various types of functions available in SQLEdit
- Describe the differences between single row and multiple row functions
Use character, number, and date functions in SELECT statementsEdit
- Manipulate strings with character function in the SELECT and WHERE clauses
- Manipulate numbers with the ROUND, TRUNC and MOD functions
- Perform arithmetic with date data
- Manipulate dates with the date functions
Using Conversion Functions and Conditional ExpressionsEdit
Describe various types of conversion functions that are available in SQLEdit
Implicit data type conversion
Implicit conversion occurs when Oracle attempts to convert the values, that do not match the defined parameters of functions, into the required data types.
Explicit data type conversion Explicit conversion occurs when a function like TO_CHAR is invoked to change the data type of a value.
Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functionsEdit
- Nest multiple functions
- Apply the NVL, NULLIF, and COALESCE functions to data
Apply conditional expressions in a SELECT statementEdit
- Use conditional IF THEN ELSE logic in a SELECT statement
Reporting Aggregated Data Using the Group FunctionsEdit
Identify the available Group FunctionsEdit
Describe the use of group functionsEdit
Group data by using the GROUP BY clauseEdit
Include or exclude grouped rows by using the HAVING clauseEdit
Displaying Data from Multiple TablesEdit
Write SELECT statements to access data from more than one table using equijoins and nonequijoinsEdit
Join a table to itself by using a self-joinEdit
View data that generally does not meet a join condition by using outer joinsEdit
- Join a table by using a self join
Generate a Cartesian product of all rows from two or more tablesEdit
Using Subqueries to Solve QueriesEdit
Define subqueriesEdit
Describe the types of problems that the subqueries can solveEdit
List the types of subqueriesEdit
Write single-row and multiple-row subqueriesEdit
Using the Set OperatorsEdit
Describe set operatorsEdit
Use a set operator to combine multiple queries into a single queryEdit
Control the order of rows returnedEdit
Manipulating DataEdit
Describe each data manipulation language (DML) statementEdit
Insert rows into a tableEdit
|
A Wikibookian suggests that this book or chapter be merged with Oracle Database/Tables. Please discuss whether or not this merger should happen on the discussion page. |
Inserting data in database is done through "insert" command in oracle.
Syntax:
INSERT INTO [table name][column1,column2,.....] values(value1,value2,....);
Example:
insert into employee values(1,'Rahul','Manager');
By the above query the employee table gets populated by empid:-1 , empname:-'Rahul' and empdesignation:-'Manager'.
Delete rows from a tableEdit
DELETE client1 WHERE ID = 2;
Update rows in a tableEdit
To update rows in a table, write:
update [table name] set [column name] = [your value];
It will update all the rows present in the table by the given value in the selected field.
We can also add queries to this command to make a real use for example,
update [table name] set [column name] = [value] where [column name]>=[value];
You can add your query after the where clause according to your need.
Example:
UPDATE client1 SET address = 'the middle of nowhere' WHERE id = 1;
Using a set operator to combine multiple queries into a single queryEdit
|
This section is a stub. You can help Wikibooks by expanding it. |
Controlling the order of rows returnedEdit
Defining subqueriesEdit
|
This section is a stub. You can help Wikibooks by expanding it. |
Describing the types of problems that the subqueries can solveEdit
Listing the types of subqueriesEdit
Writing single-row and multiple-row subqueriesEdit
Controlling transactionsEdit
- Save and discard changes with the COMMIT and ROLLBACK statements
- Explain read consistency
Using DDL Statements to Create and Manage TablesEdit
Categorize the main database objectsEdit
Review the table structureEdit
List the data types that are available for columnsEdit
Create a simple tableEdit
"Create table" command is used to create table in database.
Syntax:
create table employee(empid number,empname varchar2(20),empdesignation(varchar2(20)));
The above Query will create a table named employee with which contain columns empid
, empname
, empdesignation
followed by their datatypes.
Describe how schema objects workEdit
|
This section is a stub. You can help Wikibooks by expanding it. |
Creating Other Schema ObjectsEdit
Create simple and complex viewsEdit
Retrieve data from viewsEdit
Create, maintain, and use sequencesEdit
Create and maintain indexesEdit
Create private and public synonymsEdit
Controlling User AccessEdit
Differentiate system privileges from object privilegesEdit
Grant privileges on tablesEdit
View privileges in the data dictionaryEdit
Grant rolesEdit
Distinguish between privileges and rolesEdit
Managing Objects with Data Dictionary ViewsEdit
Explain the data dictionaryEdit
Find table informationEdit
Report on column informationEdit
View constraint informationEdit
Find view informationEdit
Verify sequence informationEdit
Understand synonymsEdit
Add commentsEdit
Manipulating Large Data SetsEdit
Manipulate data using sub-queriesEdit
Describe the features of multi-table insertsEdit
Use the different types of multi-table insertsEdit
Merge rows in a tableEdit
Track the changes to data over a period of timeEdit
Managing Data in Different Time ZonesEdit
Use data types similar to DATE that store fractional seconds and track time zonesEdit
Use data types that store the difference between two date-time valuesEdit
Practice using the multiple data-time functions for globalize applicationsEdit
Retrieving Data Using Sub-queriesEdit
Write a multiple-column sub-queryEdit
Use scalar sub-queries in SQLEdit
SELECT * FROM TAB
Edit
Edit
Use the EXISTS and NOT EXISTS operatorsEdit
Use the WITH clauseEdit
Write a multiple-column sub-queryEdit
Use scalar sub-queries in SQLEdit
Edit
Edit
Use the EXISTS and NOT EXISTS operatorsEdit
Use the WITH clauseEdit
Hierarchical QueryEdit
Hierarchical Query allows you the transverse through a self-reference table and display the Hierarchical structure. eg. the employee table contain the manager id the employee.
list out the whole hierarchical structure of the employees
SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", salary, department_id
FROM hr.employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id is null
ORDER SIBLINGS BY last_name;
list out all the employees under manager 'Kochhar'
SELECT LPAD(' ', 4*(level-1))||last_name "Last Name",
salary,
department_id,
CONNECT_BY_ISLEAF
FROM hr.employees
CONNECT BY PRIOR employee_id = manager_id
START WITH last_name = 'Kochhar'
ORDER SIBLINGS BY last_name;
list out all the manager that 'Lorentz' report to
SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", salary, department_id,
SYS_CONNECT_BY_PATH(last_name, '/') "Path", CONNECT_BY_ISLEAF
FROM hr.employees
CONNECT BY employee_id = PRIOR manager_id
START WITH last_name = 'Lorentz'
ORDER SIBLINGS BY last_name;
- pseudocolumn LEVEL -> root = 1, next level=2,3,4,5...etc
- SYS_CONNECT_BY_PATH(col, '/') shows the full path, 2nd parameter is seperator (9i)
- CONNECT_BY_ROOT(col) return the value of the root node in the current hierarchy (10g)
- pseudocolumn CONNECT_BY_ISLEAF return 1 if the return value is at the last node on the Hierarchy (ie. leaf) (10g)
- order SIBLINGS by re-order the sequence of the output and preserve the hierarchical relationship (10g)
- connect by NOCYCLE prior child = parent
- NOCYCLE means stop tranverse the hierarchy at the level when the child reference back to the root. (10g)
- pseudocolumn CONNECT_BY_ISCYCLE evaluate to "1" if the current row references a parent. (10g)
Regular Expression SupportEdit
List the benefits of using regular expressionsEdit
Use regular expressions to search for, match, and replace stringsEdit
Class | Expression | Description |
---|---|---|
Anchoring Character | ^ | Start of a line |
-$ | End of a line | |
Quantifier Character | * | Match 0 or more times |
+ | Match 1 or more times | |
? | Match 0 or 1 time | |
{m} | Match exactly m times | |
{m,} | Match at least m times | |
{m, n} | Match at least m times but no more than n times | |
\n | Cause the previous expression to be repeated n times | |
Alternative and Grouping | Separates alternates, often used with grouping operator () | |
( ) | Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section) | |
[char] | Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters | |
Posix Character | [:alnum:] | Alphanumeric characters |
[:alpha:] | Alphabetic characters | |
[:blank:] | Blank Space Characters | |
[:cntrl:] | Control characters (nonprinting) | |
[:digit:] | Numeric digits | |
[:graph:] | Any [:punct:], [:upper:], [:lower:], and [:digit:] chars | |
[:lower:] | Lowercase alphabetic characters | |
[:print:] | Printable characters | |
[:punct:] | Punctuation characters | |
[:space:] | Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed | |
[:upper:] | Uppercase alphabetic characters | |
[:xdigit:] | Hexidecimal characters | |
Equivalence class | = = | An equivalence classes embedded in brackets that matches a base letter and all of its accented versions. eg, equivalence class '[=a=]' matches ä and â. |
Match Option | c | Case sensitive matching |
i | Case insensitive matching | |
m | Treat source string as multi-line activating Anchor chars | |
n | Allow the period (.) to match any newline character |