Oracle Database/SELECT Statement

Listing the capabilities of SQL SELECT statements A SELECT statement retrieves data from database. With a SELECT statement, you can use the following capabilities: Projection: select the columns in a table that are returned by a query. Selection: select the rows in a table that are returned by a query using certain criteria to restrict the result. Joining: bring together data that is stored in different tables by specifying the link between them. Executing a basic SELECT statement SELECT *|{[DISTINCT] column|expression [[AS] alias],...} FROM table; SQL statements are not case-sensitive. SQL statements can be entered on one or more lines. Keywords like SELECT, FROM cannot be abbreviated or split across lines. In SQL Developer, SQL statements can optionally be terminated by a semicolon (;). Semicolons are required when you execute multiple SQL statements. In SQL*Plus, you are required to end each SQL statement with a semicolon (;). Select All Columns SELECT * FROM hr.employees; Select Specific Columns SELECT employee_id, last_name, hire_date FROM hr.employees; Exclude duplicate rows SELECT DISTINCT last_name FROM hr.employees; Use Arithmetic Operators The operator precedence is the same as normal mathematics, (ie. / * + -) Arithmetic expressions containing a null value evaluate to null SELECT last_name, salary, (salary+100-20)*105/100 FROM hr.employees; Use Column Heading Defaults SQL Developer: Default heading display: Uppercase Default heading alignment: Left-aligned SQL*Plus: Default heading display: Uppercase Character and Date column headings: Left-aligned Number column headings: Right-aligned Use Column Alias Renames a column heading AS keyword between the column name and alias is optional Requires double quotation marks if it contains spaces, special characters, or case-sensitive SELECT last_name AS name, commission_pct comm, salary*12 "Annual Salary" FROM hr.employees; Literal Character Strings Date and character literal values must be enclosed within single quotation marks Each character string is output once for each row returned SELECT last_name || ' annually earns ' || salary*12 FROM hr.employees; Escape the single quote character use two single quotes SELECT last_name || '''s employee no is ' || employee_id FROM hr.employees; Escape the single quote character use alternative quote (q) operator SELECT last_name || q'<'s employee no is >' || employee_id FROM hr.employees; Learn the DESCRIBE command to display the table structure DESC[RIBE] table Describing various types of conversion functions that are available in SQL 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. Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions Nest multiple functions Apply the NVL, NULLIF, and COALESCE functions to data Applying conditional expressions in a SELECT statement Use conditional IF THEN ELSE logic in a SELECT statement Describing various types of functions available in SQL Describe the differences between single row and multiple row functions Single row functions return one result per row. Single row functions: Manipulate data items Accept arguments and return one value Act on each row that is returned Return one result per row May modify the data type Can be nested Accept arguments that can be a column or an expression Character functions Case manipulation functions LOWER UPPER INITCAP Using character, number, and date functions in SELECT statements 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 Identifying the available group functions Describing the use of group functions Grouping data by using the GROUP BY clause Including or excluding grouped rows by using the HAVING clause Writing SELECT statements to access data from more than one table using equijoins and nonequijoins Joining a table to itself by using a self-join Viewing data that generally does not meet a join condition by using outer joins Generating a Cartesian product of all rows from two or more tables