Retreiving Data Using SELECT

Question 0

What are pseudo columns?

Answer 0

Pseudo columns are values defined automatically for every table by oracle. They do not appear in DESC output since they are not saved in the table. You can select them just as you would any other column. They generally return a different value for each row.

2 examples of pseudo columns:

ROWNUM - system assigned number for row, to number each row of output from a SELECT statement, however note that ROWNUM is assigned before ORDER BY clause is processed.

ROWID - system assigned physical address for a given row. This can change for example if you export a table and then import it.
ROWID can be useful when attempting to delete duplicate rows.

Question 1

Describe SELECT statement

Answer 1

SELECT ship_id, ship_name, capacity FROM SHIPS ORDER BY ship_name;

The select list is a list of expressions (column names, arithmetic operators, liberals and functions). This is the data you want to retrieve and is displayed in the order the items are in your select list

The FROM keyword is followed by one or more tables

The ORDER BY clause is followed by an expression identifying how you want the rows to be sorted

Without a WHERE clause all table rows are displayed

SELECT keyword must be followed by at least one expression and FROM keyword followed by at least one table name.
SELECT 1 FROM SHIPS; or SELECT ship_name from SHIPS;

Question 2

Describe how DISTINCT or UNIQUE is used in a SELECT statement

Answer 2

DISTINCT or UNIQUE identify a unique set of values from a table.

SELECT DISTINCT last_name from employees;

SELECT DISTINCT last_name, first_name from employees; - gives unique combinations of last and first names

Question 3

Describe the use of expressions in SELECT statements

Answer 3

Expressions provide a way to transform data.

Literal values that can be used in expressions:
Number 49.12
Character / string ‘Hayden, Day’ ‘Isn’‘t it nice’
Date ‘10-MAY-09’

Interval YEAR TO MONTHS ‘INTERVAL ‘24-3’ YEAR(2) TO MONTH’ is 24 years and 3 months where 2 is the precision for the year component
Interval DAY TO SECONDS
Interval YEAR, MONTH, DAY, MINUTE, SECOND

You can combine column names and expressions:

SELECT employee_id, salary, salary * 1.05 FROM pay_history WHERE end_data IS NULL ORDER BY employee_id;

Expressions can be used in:

SELECT statements column list and it’s WHERE, HAVING and ORDER BY clauses
Hierarchical queries with CONNECT BY, START WITH and ORDER BY clauses
INSERT statements VALUES clause
UPDATE statements SET clause and WHERE clause
DELETE statements WHERE clause

Question 4

Describe operator precedence

Answer 4

Precedence: ( ), (*,/), (+,-)

(11-4+((2+3)*.7)/4) = 7.875

SELECT salary * (base_pay * (1.03 * years_of_service)) /12 FROM emp_compensation;

Question 5

Describe functions

Answer 5

A function is represented by a name or abbreviation
Receive one or more bits of incoming data
Perform some task on data
Produce a single answer representing the result of a function
Parameters may include literal values or table column names

Question 6

List SELECT statement clauses

Answer 6

Select list - which columns to retrieve and display
FROM clause - which tables to get data from
WHERE condition - restrict rows
Hierarchical_query_clause - output in hierarchical order
ORDER BY order_by_clause - sort rows
GROUP BY group_by_clause - collect rows in groups
HAVING condition - performs WHERE operation for groups defined
by GROUP BY
Sub query - SELECT statement can have embedded SELECT statement in WhERE clause and elsewhere

Question 7

Describe capabilities of SELECT

Answer 7

Projection - ability to choose a subset of columns
Selection - ability to choose a subset of rows
Joining - ability to specify that there is common information shared by two tables and ability to link data from such tables together according to common shared data or relation

SELECT PORTS.port_name, SHIPS.ship_name FROM PORTS JOIN SHIPS ON (PORTS.port_id = SHIPS.home_port_id);

Question 8

Describe SCHEMA objects

Answer 8

Schema objects are owned by a user and exist in a collection within a user account

Tables - all data in a database is stored in tables. System defined tables called data dictionaries store information about created tables ( names, columns, data types). This is called meta data or data about data.

Constraints - (not null, unique, primary key, foreign key, check)
A constraint is a rule on a table that puts restrictions on the sort of data that is added to the table. A constraint is not a database object but is listed in data dictionary and can be named.

Views - a view acts like a table, you can describe it, select from it and insert, update, delete, but it is not a table and stores no data. It is effectively just a select statement on which the column structure and data types depend.

Indexes - creating an index object signals that one or more columns will be frequently used to lookup data. Oracle builds a separate object that has a unique list of the data currently in the column, sorts it and stores internal addressing information that ties the index back to the source table rows.

Any future queries on the table that reference indexed data will cause:

1. An analysis to be performed to determine if the query will benefit from using an index
2. If yes then search index for desired data and obtain direct locations of rows
3. The more data that is stored in a table the more beneficial the use of an index.

Sequences: a sequence is a counter that issues numbers in a series always keeping track of what the next number should be. Supports row inserts by providing appropriate values for PRIMARY KEY. A sequence is not tied to any one particular table.

Synonyms: an object that associates an alias with an existing object e.g. ECP for EMPLOYEE_ COMPENSATION_PLANS. A private synonym is owned by a user account, a public synonym is owned by the user PUBLIC and is available to all users.