CIS 411W Database Systems

Oracle ApEx

"Oracle ApEx is a rapid Web application development tool that lets you share data and create applications." With Oracle ApEx, developers can create desktop, mobile, websheet and packaged applications.

The WHENEVER statement

WHENEVER Statement This statement allows you to do automatic error checking and handling. The syntax is: EXEC SQL WHENEVER <condition> <action>; EXEC SQL WHENEVER SQLERROR GOTO errexit;

Previous Database Systems

We can forget about flat file systems - easy to implement, but hard to maintain. Networked databases could be emulated with sequences that "tie" tables together, creating artificial but valid connections between tables.

The inner query

We can tell who has ordered something by running a SQL query: select customer_id from orders; If we could combine this with the original CUSTOMERS select then we would have our answer.

The Inner Join

We have looked at SELECT statements that return rows from tables based on a join condition hat links tables based on data about both entities existing in both tables: Here the student has a student record and at least one telephone record. STUDENT_LST_NM STUDENT_FRST_NM TELEPHONE_NUMBER -------------------- -------------------- ---------------- Parker Al 5551234 Parker Al 8781234 SELECT student_lst_nm, student_frst_nm, telephone_number FROM student s, telephone t WHERE s.collegeID = t.collegeID / In order for this query to evaluate to TRUE and appear in the result set, the student must have a telephone table record collegeID that matches the student table collegeID.

SQLERRM

sqlerrm - contains that actual error message generated by the SQL statement. There are two components to this message: sqlerrml - Length of the message text stored in sqlerrmc.  sqlerrmc - Up to 70 characters of the message text corresponding to the error code stored in sqlcode. The errorexit subroutine to display the sqlerrm. errexit: printf("Error: %70s", sqlca.sqlerrm.sqlerrmc);

update

update students set stdnt_entry_dt = to_date('2015','YYYY') where stdnt_cd = 'F'; Here the students table is updated, setting the year part of stdnt_entry_dt to '2015' for freshmen students.

2 for 1

update students set stdnt_entry_dt = to_date('AUG-2015','MON-YYYY') where stdnt_cd = 'F'; Here both the month and the year are updated at the same time using the 'MON-YYYY' format mask where 'MON' is the month and 'YYYY' is the year.

implicit conversion

when values do not match the defined parameters of functions, oracle attempts to convert them into the required data types. this is known as implicit conversion. select stdnt_lname ||', '|| stdnt_fname as name, stdnt_entry_dt as "date" from students where stdnt_entry_dt = '26-AUG-13' / In this example the stdnt_entry_dt of data type 'date' is implicitly converted to 'char' and then compared to the character string '26-AUG-13'. The proceeding example will work as long as the date values for stdnt_entry_dt were inserted in the simple form 'DD-MON-YYYY' where 'DD' is the day, 'MON' the first three characters of the month ('AUG','JUL', etc.) and 'YYYY' is the year.

undefine search

will clear out substitution variable being held

The Query

select to_char(end_date,'YYYY') "Quitting Year", JOB_ID,COUNT(*) "Number of Employees" from job_history GROUP BY to_char(end_date,'YYYY'), job_id order by count(*) desc;

Spell out the DATE

select to_char(sysdate,'DY')|| ' ' || to_char(sysdate,'MONTH') || ' '|| to_char(sysdate,'DD')|| ' ' || to_char(sysdate,'YEAR') ||' day '|| to_char(sysdate,'D') || ' of the week.' from dual;

NOT NULL

sid VARCHAR2(3) NOT NULL, When the table is created the constraint NOT NULL can be added to the column_name, datatype description.

UNIQUE

sid VARCHAR2(3) UNIQUE, When creating a table the constraint UNIQUE can be added to force only one and only one value equal to this value in the table.

Sequence

" A sequence is a construct that generates unique numbers. There are many cases where unique numbers are needed. Sequences issue numbers in order, on demand: it is absolutely impossible for the same number to be issued twice."

Synonyms

" A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points."

Internal/External datatypes

Oracle Internal datatype: CUSTOMER_L_NAME VARCHAR2(10) External Datatype: VARCHAR customer_lst_name[FOOD_DESC_LEN];

Data for loading data with runtime data

EMPNO EMP_F_NAME EMP_L_NAME DEPTNO 1,Holmes,Sherlock,1 2,Hammer,Mike,2 3,"Ness, Jr.",Elliot,3

Views

" A view is a stored SELECT statement that can be addressed as though it were a table. It is nothing more than a SELECT statement but, rather than running the statement itself, the user issues a SELECT statement against the view instead. In effect, the user is selecting from the result of another selection. "

Set Operators

" The set operators take as their input the results of two or more SELECT statements and from these generate a single result set. This is known as a compound query. " Oracle provides three set operators: UNION, INTERSECT, and MINUS. Set operators work on result sets. Right before you see the results of the query set operators change what you get in the result set. The statement terminator ';' or '/' goes after the second compound statement to terminate the entire compound statement. select column_name1, column_name2 from table_name1, [table_name2] where conditions SET OPERATOR select column_name1, column_name2 from table_name1, [table_name2] where conditions;

Bitmap Indexes

"A bitmap index stores the rowids associated with each key value as a bitmap. " Storing the values for rowids in a binary format allows for fast access. create bitmap index <identifier> on <table_name>(<column_name>, <column_name>);

Dashboards

"A business intelligence dashboard is a data visualization tool that displays the current status of metrics and key performance indicators (KPIs) for an enterprise. Dashboards consolidate and arrange numbers, metrics and sometimes performance scorecards on a single screen."

Ralph Kimball

"A data warehouse is a copy of transaction data specifically structured for query and analysis." Ralph Kimball has also created dimensional modelling also known as the de-normalized model.

Bill Inmon

"A data warehouse is a subject-oriented, integrated, time-variant, non-volatile collection of data in support of management's decision making process."

Defining a Function

"A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution." Functions take 0 or more parameters and return a value of a certain datatype. The parameters sent to the function can be from different datatypes.

Simple View

"A simple view draws data from one detail table, uses no functions, and does no aggregation." "A complex view can join detail tables, use functions, and perform aggregations."

Synonyms

"A synonym is an alternative name for an object. If synonyms exist for objects, then any SQL statement can address the object either by its actual name, or by its synonym." select * from hr.employees@prod; schema name database name create public synonym emp for hr.employees@prod; then all the user need enter is the following: synonym select * from emp;

CONNECT BY

"CONNECT BY specifies that rows are to be retrieved in a hierarchical order, and defines the relationship to be used to connect table rows into a hierarchy."

Data Modeling

"Data management is the development, execution and supervision of plans, policies, programs and practices that control, protect, deliver and enhance the value of data and information assets." "It turns out that regardless of whether you are designing a relational database or a data warehouse, the entirety of the field of database design can be found in a single statement - 'Put stuff where it belongs.' - Mike Hotek. The primary goal of data modeling is to identify the facts that must be stored in a database. The entity-relationship model is used to interpret, specify and document requirements for database processing systems. The entity-relationship model was introduced by Peter Chen in 1976. Basic building block - the entity. Once the entity has been identified, the attributes are determined. Relevant data is included. Other data is excluded. Inside of the entity/table, attributes should only hold one value.

Using Set Operators

"Developers and even DBAs, occasionally need to compare the contents of two tables to determine whether the tables contain the same data. The need to do this is especially common in TEST environments."

Multiple GROUP BY expressions

"Exercise 6-2: Grouping Data Based on Multiple Columns Analysis of staff turnover is a common reporting requirement. You are required to create a report containing the number of employees who left their jobs, grouped by the year in which they left. The jobs they performed are also required. The results must be sorted in descending order based on the number of employees in each group. The report must list the year, the JOB_ID, and the number of employees who left a particular job in that year."

Group Functions

"Group functions operate on aggregated data and return a single result per group."

INTERSECT

"INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates." select product_id, product_name, product_status from product_information INTERSECT product_id, product_name, product_status From product_information p, orders o where p.product_id = o.product_id and order_total > 499.99;

Indexes

"Indexes are a means of improving access times to rows in tables. If a query requires only one row, then rather than scanning the entire table to find the row, an index can give a pointer to the row's exact location. "

Indexes

"Indexes have two functions: to enforce primary key and unique constraints and to improve performance. An application's indexing strategy is critical for performance. " B*Tree Indexes A B*Tree index (the "B" stands for "balanced") is a tree structure. The root node of the tree points to many nodes at the second level, which can point to many nodes at the third level, and so on. The hierarchical nature of Binary Tree Indexing isn't useful for all situations.

minus

"MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates." The commutative property does not hold for subtraction like it does for addition, since: 5-2 and 2-5 will give different results. So the placement of you table names in your compound query will affect the results. select student_last_name, student_first_name from students MINUS select student_last_name, student_first_name from students s, student_gpa gpa, student_level sl where s.student_id = gpa.student_id and gpa.student_id = sl.student_id and gpa < 3.0 and stdnt_level IN('PBC','GR');

INSERT

"Oracle stores data in the form of rows in tables. Tables are populated with rows (just as a country is populated with people) in several ways, but the most common method is with the INSERT statement. " "There are much faster techniques than INSERT for populating a table with large numbers of rows. These are the SQL*Loader utility, which can upload data from files produced by an external feeder system, and Datapump, which can transfer data in bulk from one Oracle database to another—either via disk files or through a network link." Insert into student: insert into student values('988','Bolt','Usain','M','MKT',4.0, 1600,'21-AUG-1986'); NOTE: if you are entering data into every column in the table, you don't have to specify the column names.

Paginated Reports

"Paginated" reports are more traditional, headers and footers, page numbers, etc.

DELETE

"Previously inserted rows can be removed from a table with the DELETE command. The command will remove one row or a set of rows from the table, depending on a WHERE clause. If there is no WHERE clause, every row in the table will be removed" delete from student where sid = 988; The student is no longer in the school, so we DELETE him. NOTE: if we delete the student record we should also get rid of his course enrollment records. "A deletion is all or nothing. It is not possible to nominate columns. When rows are inserted, you can choose which columns to populate. When rows are updated, you can choose which columns to update. But a deletion applies to the whole row—the only choice is which rows in which table. This makes the DELETE command syntactically simpler than the other DML commands."

Conversion Functions

"SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression or literal." "TO_CHAR, TO_NUMBER and TO_DATE are the three most widely used conversion functions."

Single-row Functions

"Single-Row Functions There are several categories of single-row functions including character, numeric, date, conversion, and general. The focus of this chapter is on the character, numeric, and date single-row functions. These are functions that operate on one row of a dataset at a time. If a query selects 10 rows, the function is executed 10 times, once per row with the values from that row as input to the function." 1 select region_id, region_name, length(region_name) 2* from regions REGION_ID REGION_NAME LENGTH(REGION_NAME) ---------- ------------------------------ ------------------- 1 Europe 6 2 Americas 8 3 Asia 4 4 Middle East and Africa 22

Single-row Functions

"Single-row functions, explored in Chapters 4 and 5, return a single value for each row in a set of results. Group or aggregate functions operate on multiple rows."

Terms

"Sqoop is a command-line interface application for transferring data between relational databases and Hadoop." 1 "Oracle GoldenGate is a comprehensive software package for real-time data integration and replication in heterogeneous IT environments."2

START WITH

"Start With identifies the row(s) to be used as the root(s) of the tree by specifying a condition that they must satisfy." Subqueries are permitted in a START WITH clause. START WITH could start with a column value such as last_name = 'Knox'

Multiple Group functions

"Summary statistics are required by senior management. This includes details like number of employees, total staff salary cost, lowest salary, and highest salary values. Can such a report be drawn using one query?"

Multiple Group functions

"Summary statistics are required by senior management. This includes details like number of employees, total staff salary cost, lowest salary, and highest salary values. Can such a report be drawn using one query?" "Yes. There is no restriction to the number of group functions listed in the SELECT clause. The requested report can be drawn using the following query: SELECT COUNT(*) Num_Employees, SUM(SALARY) Tot_Salary_Cost, MIN(SALARY) Lowest Salary, MAX(SALARY) Maximum Salary FROM EMPLOYEES;"

Constraints

"Tables have rules defined that control the rows that may be inserted. These rules are constraints. A constraint is an implementation of a business rule."

Equijoin

"Tables may be joined in several ways. The most common technique is called an equijoin. A row is associated with one or more rows in another table based on the equality of column values or expressions."

ADD_MONTHS()

"The ADD_MONTHS function returns a date item calculated by adding a specified number of months to a given date value." select add_months(SYSDATE,200) from dual; ADD_MONTH --------- 19-MAY-30

INSTR() Function

"The INSTR Function (In-string) The INSTR function locates the position of a search string within a given string. It returns the numeric position at which the nth occurrence of the search string begins, relative to a specified start position. If the search string is not present the INSTR function returns zero."

MONTHS_BETWEEN()

"The MONTHS_BETWEEN function returns a numeric value representing the number of months between two date values. " select months_between('19-SEP-2012',SYSDATE) from dual; MONTHS_BETWEEN('19-SEP-2012',SYSDATE) ------------------------------------- -12

The NVL() Function

"The NVL function evaluates whether a column or expression of any data type is null or not. If the term is null, an alternative not null value is returned; otherwise, the initial term is returned. The NVL function takes two mandatory parameters. Its syntax is NVL(original, ifnull)." select nvl(1234) from dual; Won't work; missing parameter. select nvl(dummy,1234) from dual; 1* select NVL(dummy,1234) from dual NVL( ---- X 1 select empno, empfname,NVL(department_id, '999') 2* from employees EMPNO EMPFNAME NVL(DEPARTMENT_ID,'999') ---------- -------------------- ------------------------ 1 Alice 10 3 Andrew 20 4 Clare 30 5 Todd 40 6 Nancy 50 7 Brier 60 8 Sarah 70 9 Sophie 80 10 DeJuan 90 11 Pall 100 12 Luna 100 EMPNO EMPFNAME NVL(DEPARTMENT_ID,'999') ---------- -------------------- ------------------------ 13 andrew 999 12 rows selected.

TRUNC() function

"The Numeric TRUNC Function (Truncate) The TRUNC function performs a truncation operation on a numeric value based on the decimal precision specified. A numeric truncation is different from rounding because the resulting value drops the numbers at the decimal precision specified and does not attempt to round up or down if the decimal precision is positive."

Replace()

"The REPLACE(string, search item, replacement item) function locates the search item in a given string and replaces it with the replacement item, returning a string with replaced values: replace('#PASSWORD#','WORD','PORT') = #PASSPORT#"

UPDATE

"The UPDATE command is used to change rows that already exist—rows that have been created by an INSERT command, or possibly by a tool such as Datapump." "One UPDATE statement can change rows in only one table, but it can change any number of rows in that table." update student set major = 'ACC' where sid = 988; Here just one row is updated. Change the student's major to 'ACC' - accounting. update student set major = 'ACC', dob = '21-AUG-84', sat = 1580 where sid = 988; Here multiple columns (major, dob, sat) are being updated in the same SQL update statement.

the where clause

"The WHERE clause specifies one or more conditions that Oracle RDBMS server evaluates to restrict the rows returned by a query." The conditions that restrict the data returned by a result set can operate on columns or they can evaluate data based on expressions.

substitution variables

"The key to understanding substitution variables is to regard them as placeholders." "Any text, sub-clause or clause element, or even the entire SQL query is a candidate for substitution." "First, it checks whether the variable is defined in the user session." If the variable is not defined already then the SQL server will prompt the user for a value. Select * from jobs where job_id = &JOBID; Here the SQL query uses a substitution variable rather than a set value. The SQL server will prompt the user to enter a value for JOBID is it does find a value already defined for this session. If the variable accepts a VARCHAR2 datatype, then you will have to include the single quotes. If the query expects a number you don't have to enclose that in single quotes. You can also use the wildcard characters '%' and '_' just like you would in a regular SQL query. Note also that you don't have to use the statement terminator (';' or '/') when entering a value for a substitution variable. A work-around for users having to add their own single-quotes in variable substitution is to enclosed the variable name in single-quotes in the SQL query. select * from jobs where job_id = '&JOBID'; SQL> r 1* select * from jobs where job_id = '&JOBID' Enter value for jobid: SA_MAN old 1: select * from jobs where job_id = '&JOBID' new 1: select * from jobs where job_id = 'SA_MAN' JOB_ID JOB_TITLE MIN_SALARY ---------- -------------------- ---------- SA_MAN Sales Manager 100000

Relational Database Objects

"The objects of greatest interest to a SQL programmer are those that contain, or give access to, data. These are:" Tables Views Synonyms Indexes Sequences

Chapter 7

"The three pillars of relational theory are selection, projection, and joining. This chapter focuses on the practical implementation of joining. " We have spent the first six chapters of the book looking at selecting from only one table at a time.

Websheets

"Websheets are a quick and easy way to get content on the web. Whether that content is text, images, reports or charts, it all can be integrated into a websheet application."

Create Table

"When creating a table, there are certain rules that must be followed regarding the table's structure: its columns may only be of certain data types. There are also rules that can be defined for the individual rows; these are known as constraints."

Summary

"When values do not match the defined parameters of functions, Oracle attempts to convert them into the required data types. This is known as implicit conversion." "Explicit conversion occurs when a function like TO_CHAR is invoked to change the data type of a value." "Character items are explicitly transformed into date values using the TO_DATE conversion function." "Character items are changed into number values using the TO_NUMBER conversion function."

Scenario and Solution

"You would like to retrieve the earliest date from a column that stores DATE information. Can a group function be utilized to retrieve this value?"

Declaring Host Variables in C/C++

#define UNAME_LEN 20 #define MAJOR_LEN 10 VARCHAR stdnt_lst_nm[UNAME_LEN]; VARCHAR stdnt_frst_nm[UNAME_LEN]; VARCHAR stdnt_mjr[MAJOR_LEN]; Note: these are "transitional" host variables defined as VARCHAR rather than the usual VARCHAR2 that modern Oracle SQL uses. While a column in the database is VARCHAR2 the host variable is VARCHAR. The C/C++ program will then have to use different string functions to deal with the host variable so that it can be used by the application program. You do not need the EXEC SQL command when declaring C/C++ host variables because right now you are not accessing the database. So... VARCHAR2 VARCHAR string acceptable to C/C++

WHERE VS HAVING

'The WHERE clause restricts rows in a dataset before grouping, while the HAVING clause restricts them after grouping. '

Data file for .CSV position

1 Holmes Sherlock 1 2 Hammer Mike 2 3 Ness Elliot 3

Getting Earliest MIN() Date

1* select MIN(dt_rcvd) from product_information MIN(DT_RC --------- 17-SEP-12

MAX()

1* select TRUNC(MAX(list_price),2) from product_information TRUNC(MAX(LIST_PRICE),2) ------------------------ 1500 What is the price of the most expensive item in the inventory? All other list prices do not appear in the result set.

SUM()

1* select TRUNC(SUM(list_price),2) from product_information TRUNC(SUM(LIST_PRICE),2) ------------------------ 5528.96 What is the total value of the inventory?

DATE Conversion

1* select to_char(sysdate,'YEAR') from dual TO_CHAR(SYSDATE,'YEAR') ------------------------------------------ TWENTY THIRTEEN What year is it spelled out.

Loading data with commas

1,Holmes,Sherlock,1 2,Hammer,Mike,2 3,"Ness, Jr.",Elliot,3 Indata3.csv

Traditional DBMS Architecture

1. Hardware costs - traditional DBs architecture uses high-end servers. 2. Software costs - the cost of Oracle and Microsoft licensing, among others, becomes a factor. 3. High risk of failure - having the application and database run on a few servers could make it vulnerable to failure.

inequality operators

< Less than > Greater than <= Less than or equal to >= Greater than or equal to <> Not equal to != Not equal to

CHECK CONSTRAINT

<column_name> datatype(size) CONSTRAINT ck_active_ind CHECK(some_condition), Checks to see if appropriate data is being loaded. The 'some_condition' part can contain a SQL query that controls what can and can't be loaded into the column.

PRIMARY KEY

<column_name> datatype(size) CONSTRAINT pk_column_name PRIMARY KEY, The 'pk_column_name' in this example is an identifier. By convention primary key identifiers start with 'pk_' and include the column name. This makes it easier to troubleshoot if someone tries to enter bad data that violates the Primary Key.

DML Trigger

A DML trigger is either simple or compound. "A simple DML trigger fires at exactly one of these timing points: Before the triggering statement runs (The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger.) After the triggering statement runs (The trigger is called an AFTER statement trigger or statement-level AFTER trigger.) Before each row that the triggering statement affects (The trigger is called a BEFORE each row trigger or row-level BEFORE trigger.) After each row that the triggering statement affects (The trigger is called an AFTER each row trigger or row-level AFTER trigger.)" "A compound DML trigger created on a table can fire at one, some, or all of the preceding timing points. Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data." "Triggers are not reliable security mechanisms, because they are programmatic and easy to disable. "

data warehouse

A Data Warehouse is a way to maintain and access data in a way that is separate from the production database.

Datatypes

A column defined as NUMBER cannot hold characters and letters. A DATE datatype can't hold random numbers or characters. If you encounter data that has to be transformed or converted, then you have to use a conversion function.

Data mart

A data mart is a simple form of a data warehouse that is focused on a single subject(or functional area) such as Sales, Finance or Marketing. A data warehouse deals with multiple subject areas, while a data mart specializes in one subject.

Data Warehouse

A data warehouse is a central repository with historical data which can be queried for multiple business needs. Forecasts, summaries, reports, etc. Like Tedd Codd is associated with the relational database model, Ralph Kimball and Bill Inmon are associated with data warehousing. What is data warehousing used for? To gather analytical operations. Avoid slowdowns in the transactional management system. Minimize the increased CPU costs. Consolidation of information resources. Improved query performance. Separate research and decision support functions from the operational systems. Foundation for data mining, data visualization, advanced reporting and OLAP (online analytical processing) tools. Why use relational database management systems for data warehousing? Adoption and implementation. A sizable Return on Investment. The technology is mature. With open-source tools the service guarantee, technical support is not available. Data warehouses are relational databases that are designed for querying and data analysis rather than processing transactions. Transactions are just daily business activities. OLTP Online transaction processing OLAP Online Analytical Processing. OLTP is used to run a business. Typically the data in the data warehouse is not updated or deleted. Production Databases Data Warehouse Create, read,insert,update,delete Read

Factless Fact Table

A fact table that does not hold a measurement. In the previous example the measure was how many units were sold in one sales transaction.

Summary

A simple view has one detail (or base) table and uses neither functions nor aggregation. A complex view can be based on any SELECT statement, no matter how complicated. Views are schema objects. To use a view in another schema, the view name must be qualified with the schema name. A view can be queried exactly as though it were a table. Views can be joined to other views or to tables, they can be aggregated, and in some cases they can accept DML statements. Views exist only as data dictionary constructs. Whenever you query a view, the underlying SELECT statement must be run.

The self JOIN

A table can be joined to itself. Say you want both telephone numbers for a student in one result set row SELECT student_lst_nm, student_frst_nm, t.telephone_number, t2.telephone_number FROM student s, telephone t, telephone t2 WHERE s.collegeID = t.collegeID AND t.collegeID = t2.collegeID AND rownum = (SELECT MIN(rownum) FROM student WHERE collegeID = s.collegeID) / STUDENT_LST_NM STUDENT_FRST_NM TELEPHONE_NUMBER TELEPHONE_NUMBER -------------------- -------------------- ---------------- ---------------- Parker Al 8781234 5551234

Define

A variable can be defined for a SQL user session with the Define command. SQL> DEFINE PRODNAME = HD% Here the variable named PRODNAME will have the value HD% until the session ends or the variable is UNDEFINED

VIEW

A view is the result set of a SQL query that has been given a name and can be SELECTED from as if it were a table.

AVG()

AVG(expr) Returns the average or mean of a group of data. The options are: ALL expr and DISTINCT expr 1* select AVG(list_price) from product_information AVG(LIST_PRICE) --------------- 614.328889

GROUP Functions

AVG: returns the variance or variability of an expression. COUNT: returns the number of rows returned by a query. FIRST: returns the first value from an ordered sequence. LAST: returns the last value from an ordered sequence. MAX: returns the maximum value of a column. MIN: returns the minimum value of a column. STDDEV: returns the standard deviation of a set of numbers. SUM: returns the sum or total the values of a column. VARIANCE: returns the variance or variability of an expression.

tables

After a RDBMS table is created it doesn't hold any data. This we be for just a short time or the table may never hold any data. One of the strengths of SQL is that empty tables pose no problem to the developer.

ETL

ETL is a process that pulls the data from the source systems and places it into a data warehouse. Transforming the data may involve things such as: Cleaning the data, mapping NULL values to zeroes, filtering the data, applying business rules to the data.

The Communication Area.

After each SQL statement is executed in an embedded SQL program, the DBMS provides feedback on whether or not the SQL statement worked properly. This information is returned to the application program from the DBMS through a collection of variables to an area of primary memory that is shared by the application program. This area is called the SQL communication area (SQLCA). Within the SQLCA the variable SQLCODE declared as an integer datatype, stores a value indicating the success or failure of the SQL statement just executed. SQLCODE = 0 - means that the SQL statement executed successfully.

ApEx Web

ApEx can also interact with web programming languages such as Javascript. Content can also be displayed using CSS and HTML.

ApEx Items

ApEx items available to applications developers include: Checkboxes, Color pickers, Radio groups, Text fields, etc.

Retrieving Multiple Rows.

As we saw from the previous example, it is easy to select a row from an RDBMS using embedded SQL, but how do developers go about selecting multiple rows from a database? This can be done using a cursor. Cursors are usually used in PL/SQL, but since embedded SQL utilizes a high-level language, they can be used in SQL as well.

data types

BLOBs Binary Large Objects are used for storing binary data that can hold pictures and music clips.

TO_NUMBER()

Basic Syntax: TO_NUMBER(char1, [format mask], [nls_parameters])=num 1 SQL> select to_number('2,000.73','9,999.99') from dual; TO_NUMBER('2,000.73','9,999.99') -------------------------------- 2000.73

Syntax for Declaring a Cursor

Basic syntax for declaring a cursor in COBOL: EXEC SQL DECLARE cursor_name CURSOR FOR SELECT statement END-EXEC. Basic syntax for declaring a cursor in C/C++: EXEC SQL DECLARE cursor_name CURSOR FOR SELECT statement;

Basic COBOL and C/C++ embedded SQL syntax.

Basic syntax for embedded SQL. For COBOL programs, the basic syntax is this: EXEC SQL SQL statement END-EXEC. For C/C++ programs the basic syntax is this: EXEC SQL SQL statement ;

Sequences

Basic syntax: CREATE SEQUENCE [schema.]sequencename [INCREMENT BY number] [START WITH number] [MAXVALUE number | NOMAXVALUE] [MINVALUE number | NOMINVALUE] You can start a sequence with any number, and you can add to a sequence any number to get the next sequence value.

DROP synonym

Basic syntax: DROP [PUBLIC] SYNONYM synonym ;

The forward slash

Because PL/SQL is actual computer code that will be compiled PL/SQL code is usually followed by a '/' in addition to the regular ';' terminating character.

Big Data

Big data is a popular term that is used to describe the exponential growth and availability of data. More data may lead to more accurate analysis. Google, Twitter, Facebook, etc. are creating petabytes of data per day. CERN Hadron collider project produces 15 PB of data per day. "Big Data are a collection of data sets so large and complex that it becomes difficult to process using on-hand database management tools or traditional data processing applications." -Tom White, Definitive Guide

Bind variables

Bind variables take values from one table, and then treat them as variables in PL/SQL scripts. You don't have to create the variables in your PL/SQL program: the data type is derived from the column name you are referencing. Bind variables are preceded by ':new.' to tell the PL/SQL compiler that this is a bind variable.

Oracle utilities

Both Sqlldr and SQL*Plus are utility programs from Oracle that accessed with the same username/password

Foreign Key

CONSTRAINT fk_course_fid FOREIGN KEY (fid) REFERENCES faculty(fid) The Foreign Key references a column in another table so you have to supply a foreign table name and foreign column name. A Foreign Key references a value in another table. That is, the value in that other table has to exist before you can insert it into the new table. CONSTRAINT fk_course_fid FOREIGN KEY (fid) REFERENCES faculty(fid)) Here a faculty ID (fid) must exist before we add a record to the COURSE table.

PRIMARY KEY

CONSTRAINT pk_student_sid PRIMARY KEY(sid), A primary key constraint can be added to a table when the table is being created. Here a name (pk_student_sid) is created. The naming is up to you but here I used the convention of prefixing primary key names with 'pk_'. A Primary Key can also be declared like a NOT NULL or UNIQUE column, right after the column_name datatype. In this case the primary key takes the name of the column it is associated with. A primary key enforces NOT NULL and UNIQUE attributes. A Primary Key is also an INDEX.

WHENEVER

CONTINUE - Program will try to continue to run with the next statement if possible DO - Program transfers control to an error handling function GOTO <label> - Program branches to a labeled statement STOP - Program exits with an exit() call, and uncommitted work is rolled back

COUNT(*)

COUNT(*) Returns all the rows in a given table including duplicates. COUNT(DISTINCT expr) Returns all of the unique instances of expr

CREATE TABLE

CREATE TABLE emp ( empno NUMBER (5), emp_f_name VARCHAR2(10), emp_l_name VARCHAR2(10), deptno NUMBER (5), hire_date DATE, CONSTRAINT pk_empno PRIMARY KEY (empno) );

CREATE TABLE basic syntax

CREATE TABLE table_name(column_name datatype(size)); Columns can have varying datatypes: VARCHAR2, NUMBER, DATE, BLOB

Declare the Cursor.

EXEC SQL DECLARE crsr_student CURSOR FOR SELECT stdnt_lname, stdnt_fname, stdnt_mjr, stdnt_gpa FROM student, majors, student_gpa WHERE student.stdnt_id = majors.stdnt_id AND majors.stdnt_id = student_gpa.stdnt_id AND major = :stdnt_mjr;

Declaring Host Variables in a COBOL Program

EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 STDNT_LST_NM PIC X(10). 01 STDNT_GPA PIC 9V99. 01 STDNT_MJR PIC XXX. 01 STDNT_ID PIC XXX. EXEC SQL END DECLARE SECTION END-EXEC.

Subqueries

Chapter 8: Using Subqueries to Solve Problems. "The previous six chapters have dealt with the SELECT statement in considerable detail, but in every case the SELECT statement has been a single, self-contained command. This chapter is the first of two that show how two or more SELECT commands can be combined into one statement. " "A subquery is a SELECT statement whose output is used as input to another SELECT statement." "A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery can return a set of rows or just one row to its parent query." "The places in a query where a subquery may be used are as follows: In the SELECT list used for column projection In the FROM clause In the WHERE clause In the HAVING clause" select sysdate Today, (select count(*) from employees) "Employee Count", (select count(*) from departments) "Department Count" from dual; TODAY Employee Count Department Count --------- -------------- ---------------- 14-OCT-13 6 7 Basic Syntax SELECT column_name_1, column_name_2... FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE search_condition); The proceeding SQL query contains two SELECT-FROM-WHERE blocks. The portion in parentheses is called the subquery or "inner" query. The subquery is evaluated first, then the outer query is evaluated based on the result of the subquery. The IN operator is used to link the outer query to the subquery when the subquery returns a set of values. Other comparison operators, such as <, >, =, etc., can be used to link an outer query to a subquery when the subquery returns a single value. The subquery must have only a single column or expression in the SELECT clause. You are not limited to one subquery. You can have many subqueries in a given SQL statement. You are asked to create a SQL query for the Dean's office to report on the top students as ranked by GPA. select student_id, stdnt_lname, stdnt_gpa from students s, student_gpa gpa where s.stdnt_id = gpa.student_id and stdnt_gpa = 4.0; This is OK, but here we are just looking for one expression (4.0). What if the top student has a GPA lower than 4.0? In that case there will be no results returned, no top student. You could use the MAX() function to get the top GPA: select student_id, stdnt_lname, stdnt_gpa from students s, student_gpa gpa where s.stdnt_id = gpa.student_id and stdnt_gpa = max(stdnt_gpa); ERROR at line 4: ORA-00934: group function is not allowed here SQL won't allow this comparison of a group value (MAX(stdnt_gpa)) with the single-rows returned in the SELECT clause. We will need a subquery to get the MAX(stdnt_gpa) and then compare to each individual student GPA to come up with who has the highest GPA. select student_id, stdnt_lname, stdnt_gpa from students s, student_gpa gpa where s.stdnt_id = gpa.student_id and stdnt_gpa = (select max(stdnt_gpa) from student_gpa); This works: STUDENT_I STDNT_LNAME STDNT_GPA --------- -------------------- ---------- 1 Codd 4 Notice that there could be more than one student returned. Also notice that you still have to join the table STUDENTS to table STUDENT_GPA in order to see the student's GPA in the result set.

Operating on Character Data

Character data defined as VARCHAR2 can hold data such as letters, numbers and other characters. Manipulating this data in SQL is often similar to other languages such as C/C++ and Java, and is many times easier to do since you don't have to worry about data structures.

Coalesce() Function

Coalesce: look through a group of values until you come up with one that is not NULL and return that value.

column aliases

Column aliases can transform a column name into something more meaningful for the person looking at the result set of a query. Column aliases can also be used in conjunction with the FORMAT command so that the output of a SQL query can be made more readable.

Column substitution

Column names can also be substituted as variables. select product_name,list_price from product_information order by &COLUMNNAME;

Username/password header file

Contents of the username/password header file: #define SQL_USERNAME "username" #define SQL_PASSWORD "password"

CURRENT_DATE

Current_date is similar to the sysdate function, but returns the current date-time within the session's time zone. Normally the session date/time and the system/date time will be the same, but if you are starting a remote session they might differ.

date-based conditions

DATE columns are used to store date and time information. Dates could be stored as numbers: 09102013 or characters but the DATE datatype allows you to perform operations on dates. select employee_id from job_history where start_date = end_date; Look for employees who were fired the same day they were hired. This is an Equality Operator the two sides of the equation must match for the row to be returned in the result set.

DCLGEN (DEClaration GENerator).

DCLGEN is a structure-generating utility that maps the columns of a database table into a structure that you can include in a variable declaration. DCLGEN is invoked like this: dclgen language dbname tablename filename structurename dclgen c bscacad employees employees.dcl employees_struct EXEC SQL DECLARE employees TABLE (emp_number integer, name c20); struct employees_struct_ { long emp_number; char name[21]; } employees_struct;

Data Models

Data Models are typically converted into relational databases and process models become computer programs. The logistics network is mirrored by a telecommunications network that handles the electronic flow of data that supports the actual physical aspects of the business.

COMMIT

Data that has been added to a table is not saved until a COMMIT occurs. The COMMIT is usually done by the user or the process loading the data but it can also be done by the SQL system as an implicit COMMIT. The ApEx system does implicit commits, saving your work at intervals.

Data Warehousing

Data warehousing is not a specific tool or set of tools, but rather a concept.

Datatype Equivalencing

Datatype Equivalencing Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores column values in database tables. External datatypes specify the formats used to store values in input and output host variables.. At precompile time, a default Oracle external datatype is assigned to each host variable. Datatype equivalencing allows you to override this default equivalencing and lets you control the way Oracle interprets the input data and formats the output data

dates

Dates and time values are extremely important in data processing, and are found in every area of information management. Dates and times are critical in manufacturing, financial services, engineering and transportation. Dealing with dates and time values consumes a great deal of a SQL developer's time. update students set stdnt_entry_dt = to_date('26-AUG-2013 12:00:00', 'DD-MON-YYYY HH12:MI:SS') / In this example every row in the students table is updated (usually a mistake) to add the time component '12:00:00' to the column stndt_entry_dt. If we run the first select again: select stdnt_lname ||', '|| stdnt_fname as name, stdnt_entry_dt as "date" from students where stdnt_entry_dt = '26-AUG-13' / no rows selected

Double ampersand substitution

Double ampersand substitution. If the same variable name will be used multiple times in a SQL script using double ampersand substitution will take the value entered and use it throughout the script rather than prompting the user multiple times. select product_id from product_information where product_name LIKE '&&PRODNAME' AND product_status = 'IN'; SELECT product_name, list_price FROM product_information WHERE product_name LIKE '&&PRODNAME'; SQL> sta lab4d.sql old 3: where product_name LIKE '&&PRODNAME' new 3: where product_name LIKE 'HD%' PRODUCT_ID ---------- 9 10 11 12 old 3: WHERE product_name LIKE '&&PRODNAME' new 3: WHERE product_name LIKE 'HD%' PRODUCT_NAME LIST_PRICE --------------- ---------- HD drv1 601.99 HD drv2 599.99 HD drv3 461.99 HD drv4 461.99

Data file with header record

EMPNO EMP_F_NAME EMP_L_NAME DEPTNO 1,Holmes,Sherlock,1 2,Hammer,Mike,2 3,"Ness, Jr.",Elliot,

Cartesian Join

Each and every row from one table is joined with each and every row from another table. Usually this is done by error. Sometimes, however there may actually be a use of Cartesian Joins. select aMessage || product_id, product_name from product_information, message where product_name LIKE '%HD%' Two table are referenced, and they are "joined" but only in the loosest sense of the word.

Summary

Embedded SQL is a way to use the power of SQL in your programs to access data from a database. The programmer will have to define how the program will log in to the database. Variables used by the (C, C++, COBOL) program will need datatypes that coincide with Oracle datatypes.

Embedded SQL

Embedded SQL offers the programmer the ability to write programs to have the logic and processing ability of a regular programming language with the query ability of SQL. Most embedded SQL uses COBOL or the C/C++ programming languages. Programs are written in C and the SQL commands are added along with the Oracle SQL logon commands. Embedded SQL programs then are given the extension .pc for pre-compiler. Compiling embedded SQL programs is a two-step process. proc <file_name>.pc This command comments out the embedded SQL parts so that the program can go through the regular compiling process. cc <file_name>.c compiling proceeds as usual to make and executable program. Embedded SQL communicates with the host language through global variables that a re approximately the same data type as the host language variables. VARCHAR CHAR in C VARCHAR STRING in C VARCHAR PIC(X) in COBOL To capture SQL error messages, you have to include two C header files: EXEC SQL INCLUDE SQLCA.H; EXEC SQL INCLUDE SQLDA.H; The username/password combination can be stored in a header file at the start of the program. Declare username/password variables: #define UNAME_LEN 8 #define PWD_LEN 10 VARCHAR username[UNAME_LEN]; VARCHAR password[PWD_LEN]; Connect to Oracle SQL in a C program: EXEC SQL WHENEVER SQLERROR GOTO errexit; EXEC SQL CONNECT :username IDENTIFIED BY :password; Basic format of an Embedded SQL command: EXEC SQL SELECT customer_lst_name INTO :customer_lst_name FROM customer WHERE customer_id = :customer_id; EXEC SQL Goes before the SQL command telling the program send this query to SQL, using these host variables. All EXEC SQL commands end with a ';' All host variables in embedded SQL are prefaced by ':' to distinguish them from SQL column names. The equivalencing can be done on a variable-by-variable basis using the VAR statement. The syntax is: EXEC SQL VAR <host_var> IS <type_name> [ (<length>) ]; //global declaration VARCHAR customer_lst_name[FOOD_DESC_LEN]; EXEC SQL SELECT customer_lst_name INTO :customer_lst_name FROM customer WHERE customer_id = :customer_id; customer_lst_name.arr[LNAME_LEN] = '\0'; strncpy(c[count].customer_lst_name, customer_lst_name.arr,18);

Some Sample output from the program.

Enter Buffalo State Student ID: 1 LAST NAME : Garrity FIRST NAME : Andrew MAJOR : CIS GPA : 3.44 [garritat@bscacad2 ~]$

Explicit Data Conversion

Explicit Data Type Conversion "Oracle offers many functions to convert items from one data type to another, known as explicit data type conversion functions. These return a value guaranteed to be the type required and offer a safe and reliable method of converting data items."

Fact and Dimension Tables

Fact table - a transaction. Here it is sales transactions

Customers

Find the customers who have no orders. Step one find all of the customers: select customer_id, cust_first_name, cust_last_name from customers; CUSTOMER_ID CUST_FIRST_NAME CUST_LAST_NAME ----------- --------------- -------------------- 1 Sally Edwards 2 Bo Hitchcock 3 Charlotte Buckley 4 Bob Sharif 5 Steve Prefontaine 6 Gustav Steenburgen 7 Asafa Powell 8 Tirunesh Dibaba CUSTOMER_ID CUST_FIRST_NAME CUST_LAST_NAME ----------- --------------- -------------------- 1 Sally Edwards 2 Bo Hitchcock 3 Charlotte Buckley 4 Bob Sharif 5 Steve Prefontaine 6 Gustav Steenburgen 7 Asafa Powell 8 Tirunesh Dibaba This isn't the solution because it gives the same information, as the first select from CUSTOMERS. That is, it returns all of the rows from the customers table.

The Outer Join

First attempt: Use an outer join to find all of the people not in the ORDERS table: select customers.customer_id, cust_first_name, cust_last_name from customers, orders where customers.customer_id = orders.customer_id (+);

External Tables

First: create a directory object so that the SQL statement can find the file. Create or replace directory <directory_name> as '<actual_driectory_name>' Note: the directory must exist in the Oracle directory structure instead of the local LINUX directory. Step Two: create or load a flat file of data, in the directory you have defined. This will usually be a comma or other character, delimited file: 1,Holmes,Sherlock,1 2,Hammer,Mike,2 Step Three: create a table that will "hold" the data in the file.

NUMBER

For example: largeNumber NUMBER(20,2); This example could hold a number such as 123456789.90 and so on. Oracle does not use CURRENCY or MONEY datatypes, but relies on NUMBER instead.

Summing up.

Functions accept zero or more input parameters but always return one result of a predetermined data type. Single-row functions execute once for each row selected, while multiple-row functions execute once for the entire set of rows queried. Character functions are either case-conversion or character-manipulation functions.

SQL functions

Functions can also be combined with mathematical expressions. select empno, CONCAT('Pay per check: ',TRUNC(empsalary/26,2)) from employees;

functions

Functions can also be used with operators: select lower('Today''s Date: ' || sysdate) "The Date" from dual Note the doubling of the single quotes in "Today's Date".

ETL

HDFS - the Hadoop Distributed File System. A lot of the ETL can be done with PL/SQL, the procedural language with embedded SQL used by Oracle relational database management systems.

Hadoop

Hadoop is an open-source software platform that lets developers create and run applications that process vast amounts of data. It includes: Hadoop runs on the Linux operating system and is a big data solution but not the only big data technology available. HDFS - Hadoop distributed file system, where all the data files consisting of structured and unstructured data reside.

Clustering

Hadoop uses a cluster of servers . Commodity hardware. Commonly available, relatively inexpensive hardware. Free open -source software. Reduce single point of failure

Sqlldr basic syntax

LOAD DATA INFILE 'indata.csv' into table emp fields terminated by "," (empno, emp_f_name, emp_l_name, deptno)

Control file with position

LOAD DATA INFILE 'indata2.csv' into table emp ( empno position (01:05) integer external, emp_f_name position (06:15) char, emp_l_name position (16:25) char, deptno position (26:30) integer external )

Oracle functions

Oracle SQL 11g and 12c provides a great deal of SQL functions that help developers. Oracle also has PL/SQL - a programming language extension that uses SQL to create functions, procedures and packages.

Embedded SQL

Here is an embedded SQL program written in C that utilizes a cursor to retrieve records from the database. #include <stdio.h> #include <string.h> #include "/home/staff/garritat/atgorasql.h" #define UNAME_LEN 8 #define PWD_LEN 11 #define NAME_LEN 20 #define MJR_LEN 10 #define GPA_LEN 4 VARCHAR username[UNAME_LEN]; VARCHAR password[PWD_LEN]; VARCHAR stdnt_lname[NAME_LEN]; VARCHAR stdnt_fname[NAME_LEN]; VARCHAR stdnt_mjr[MJR_LEN]; VARCHAR stdnt_gpa[GPA_LEN]; EXEC SQL INCLUDE SQLCA.H; EXEC SQL INCLUDE SQLDA.H; extern SQLDA *sqlald(); EXEC SQL DECLARE crsr_student CURSOR FOR SELECT stdnt_lname, stdnt_fname, stdnt_mjr, stdnt_gpa FROM student, majors, student_gpa WHERE student.stdnt_id = majors.stdnt_id AND student.stdnt_id = student_gpa.stdnt_id AND stdnt_mjr = :stdnt_mjr; int main() { //local declarations int stdnt_id, count, length; stdnt_id = count = 0;//length = 0; char v_stdnt_mjr[4]; //local statements printf("Enter Buffalo State Student MJR: "); scanf("%s", &v_stdnt_mjr); length = strlen(stdnt_mjr.arr); strncpy((char *) stdnt_mjr.arr, v_stdnt_mjr, 3); stdnt_mjr.len = strlen((char *) stdnt_mjr.arr); strncpy((char *) username.arr, SQL_USERNAME, 8); username.len = strlen((char *) username.arr); strncpy((char *) password.arr, SQL_PASSWORD, PWD_LEN); password.len = strlen((char *) password.arr); EXEC SQL WHENEVER SQLERROR GOTO errexit; EXEC SQL CONNECT :username IDENTIFIED BY :password; EXEC SQL WHENEVER NOT FOUND DO break; EXEC SQL OPEN crsr_student; for(;;) { EXEC SQL FETCH crsr_student INTO :stdnt_lname, :stdnt_fname, :stdnt_mjr, :stdnt_gpa; stdnt_lname.arr[strlen(stdnt_lname.arr)] = '\0'; stdnt_fname.arr[strlen(stdnt_fname.arr)] = '\0'; stdnt_mjr.arr[strlen(stdnt_mjr.arr)] = '\0'; stdnt_gpa.arr[strlen(stdnt_gpa.arr)] = '\0'; printf("LAST NAME : %s\n",stdnt_lname.arr); printf("FIRST NAME : %s\n",stdnt_fname.arr); printf("MAJOR : %s\n",stdnt_mjr.arr); printf("GPA : %s\n",stdnt_gpa.arr); length = 0; strncpy(stdnt_lname.arr," ",20); strncpy(stdnt_fname.arr," ",20); strncpy(stdnt_mjr.arr," ",4); ++count; } return 0; errexit: printf("Error: %70s", sqlca.sqlerrm.sqlerrmc); }

NULLS

Here the two strings from the separate SQL statements are returned, because SQL can handle a character to NULL comparison.

Summary

Hierarchical queries were added to relational databases to gain market share and persuade companies to go with an RDBMS system. Hierarchical queries use the Key Words CONECT BY and START WITH to traverse an organizational structure. LEVEL is a pseudo-column that returns the level of each row in a result set as it is defined by CONNECT BY.

Embedded SQL Host Variables

Host variable - a host variable is a data item declared in the host language using the data types of that particular language, for use with SQL statements that are embedded within the application program. Colons are used to distinguish the host variables from the SQL column names. It is possible for the host variable to have the same name as the column being selected from. EXEC SQL SELECT customer_lst_name INTO :customer_lst_name FROM customer WHERE customer_id = :customer_id;

Substitution Variables

How do you link one SQL query to another in a compound SQL statement. So far we have used non-equi joins to "sort" the result set based on the Set Operator being used. Another way is to use a substitution variable, which we have already looked at. If you use the double '&&' then the user won't be prompted to enter the value of the variable a second time.

CASE()

I said earlier in the semester that SQL lacked the selection structures (if,if/else) of other programming languages. That's not quite true. There have been some additions to add this type of functionality to SQL. One of them is the CASE switch. Basically you set up a column value or expression to switch on and then you tell the SELECT statement what to do if you come across that value. select case(department_id) when 10 then empfname|| ' is from management.' when 20 then empfname|| ' is from accounting.' else 'no match' end from employees; CASE(DEPARTMENT_ID)WHEN10THENEMPFNAME||' ---------------------------------------- Alice is from management. Andrew is from accounting. no match no match no match no match no match no match no match no match no match CASE(DEPARTMENT_ID)WHEN10THENEMPFNAME||' ---------------------------------------- no match 12 rows selected.

summary

INSERT enters rows into a table. UPDATE adjusts the values in existing rows. DELETE removes rows. MERGE can combine the functions of INSERT, UPDATE, and DELETE. Even though TRUNCATE is not DML, it does remove all rows in a table.

Updating using to_date

If there are many different date and time elements (day, month, year, hours, minutes, seconds) in a DATE data type, how do you modify a part of an existing date? The to_date() function can be used to update dates and parts of dates, not just select dates from tables.

GROUP

If we group the PRODUCT_INFORMATION table by product_status there would be three groups 'orderable','obsolete','under development' as well as 'IN', '#in#", etc.

ETL Tools

Oracle Data integrator IBM InfoSphere DataStage SAP Business Objects Microsoft SQL Server Integration Services Talend Informatica

Hierarchical Queries

Immediately we see that there is an order or "pecking order". You can move from top to bottom - from chairman of the department to adjunct professor. Or you can go from bottom to top, in a clearly defined way. Hierarchical queries do not work with groups that have the same "level", such as the Justice League Ultimately you will come to the top level of the hierarchical system. The top person doesn't have a boss so if there is a column such as manager_id in this table the value for the boss of all would be NULL. A hierarchical query is like falling out of a tree and hitting every branch on the way down. You have to start somewhere - the top of the tree in this case. And you have to "traverse" the tree by hitting the branches. But what if you have an ownership group such as the original owners of the Buffalo Sabres: Seymour Knox and Northrup Knox and Robert Swados? You also have a situation where the original General Manager and the Head coach were the same person - Punch Imlach. select <column_name>, <column_name> from <table_name> start with <some_condition> connect by prior <some_condition> select first_name, last_name, job from sabres_org start with manager_id IS NULL connect by prior emp_id = manager_id; This SQL query is based on the idea that everyone in the Sabres Organization has a unique employee ID. Not everyone has a manager ID, but the only people that don't are the owners. So the table is traversed by the manager ID being compared to the employee ID of the person that manages them. FIRST_NAME LAST_NAME JOB ---------- -------------------- -------------------- Seymour Knox Owner Punch Imlach GM Northrup Knox Owner Punch Imlach Head Coach Rene Robert Player Martin Rick Player Robert Swados Owner 7 rows selected.

Implicit Data Conversion

Implicit Data Conversion: Oracle is able to convert the data from one datatype to another without the use of conversion functions. There are times, however, when the SQL developer has to explicitly convert the data using system-defined functions. "Oracle allows columns to be defined with ANSI, DB2, and SQL/DS data types. These are converted internally to Oracle data types. This approach allows applications written for other database systems to be migrated to Oracle with ease." In other words, you can take data from other systems and use it in Oracle.

MOD() function

In C++ programming we looked at the modulus operator which returned the remainder of a division. select mod(6,2) from dual; MOD(6,2) ---------- 0 select MOD(empno,4) from employees order by empno; Here the SQL query assigns employees to groups based on the MOD value of their empno divided by 4.

Implicit vs. explicit conversion

In the preceding example we see that the implicit conversion date to character string doesn't work for a date that has a time component. The select seemed to work fine, it just didn't return any results. We will need to do an explicit conversion to get the data that we want.

ANSI-standard Join Syntax

In the traditional join syntax, the join condition is specified in the WHERE clause. With the new syntax the WHERE clause and any AND clauses are for filtering results only (similar to selecting from a single table.) Traditional SELECT student_lst_nm, student_frst_nm, telephone_number FROM student s, telephone t WHERE s.collegeID = t.collegeID /

Compile

Invoking the C Oracle embedded SQL precompiler proc file_name.pc Compiling the embedded C/C++ program after the precompilation: cc file_name.c #include <stdio.h> #include <string.h> #include "/home/staff/garritat/atgorasql.h" #define UNAME_LEN 8 #define PWD_LEN 11 #define NAME_LEN 20 #define MJR_LEN 5 #define GPA_LEN 4 VARCHAR username[UNAME_LEN]; VARCHAR password[PWD_LEN]; VARCHAR stdnt_lname[UNAME_LEN]; VARCHAR stdnt_fname[UNAME_LEN]; VARCHAR stdnt_mjr[MJR_LEN]; VARCHAR stdnt_gpa[GPA_LEN]; EXEC SQL INCLUDE SQLCA.H; EXEC SQL INCLUDE SQLDA.H; extern SQLDA *sqlald(); struct student { char stdnt_lname[20]; char stdnt_fname[20]; char stdnt_mjr[5]; char stdnt_gpa[5]; }; int main() { //local declarations int stdnt_id, count; stdnt_id = count = 0; struct student c[2]; //local statements printf("Enter Buffalo State Student ID: "); scanf("%d", &stdnt_id); strncpy((char *) username.arr, SQL_USERNAME, 8); username.len = strlen((char *) username.arr); strncpy((char *) password.arr, SQL_PASSWORD, PWD_LEN); password.len = strlen((char *) password.arr); EXEC SQL WHENEVER SQLERROR GOTO errexit; EXEC SQL CONNECT :username IDENTIFIED BY :password; EXEC SQL SELECT stdnt_lname, stdnt_fname, stdnt_mjr, stdnt_gpa INTO :stdnt_lname, :stdnt_fname, :stdnt_mjr, :stdnt_gpa FROM student, majors, student_gpa WHERE student.stdnt_id = majors.stdnt_id AND majors.stdnt_id = student_gpa.stdnt_id AND student.stdnt_id = :stdnt_id; stdnt_lname.arr[UNAME_LEN] = '\0'; strncpy(c[count].stdnt_lname,stdnt_lname.arr,18); stdnt_fname.arr[UNAME_LEN] = '\0'; strncpy(c[count].stdnt_fname,stdnt_fname.arr,18); stdnt_mjr.arr[MJR_LEN] = '\0'; strncpy(c[count].stdnt_mjr,stdnt_mjr.arr,18); stdnt_gpa.arr[GPA_LEN] = '\0'; strncpy(c[count].stdnt_gpa,stdnt_gpa.arr,18); printf("LAST NAME : %s\n",c[count].stdnt_lname); printf("FIRST NAME : %s\n",c[count].stdnt_fname); printf("MAJOR : %s\n",c[count].stdnt_mjr); printf("GPA : %s\n",c[count].stdnt_gpa); return 0; errexit: printf("Error: %70s", sqlca.sqlerrm.sqlerrmc); }

Sqlldr command

Invoking the sqlldr utility - sqlldr username@server/password control=loader.ctl If you are not running this in batch mode you can omit the username/password and just type that in at runtime.

set operators

It is useful to see where two versions of the same table on different instances of the same database (TEST vs. PROD for example) differ. Does the TEST database need to be cloned from production because the data is old? "Comparison of tables is also useful for automated testing purposes, when we have to compare actual results with a given set of expected results."

ETL Best Practices

Know the data requirements well before the ETL stage. Map source to data warehouse. Size the data and do volume analysis. Utilize transaction logs and reporting as much as possible. Scheduling Ability to restart the ETL process Documentation

sqlldr

LOAD DATA - command telling the sqqldr utility the type of data load - in this case a basic data load. INFILE - what the name of the .csv file is that contains the data to be loaded. INTO TABLE - what table you are loading or appending data into. FIELDS TERMINATED BY - what character you are using the terminate the each individual field. Other characters can be used, there can be issues when the data contains commas (such as money.) (column_name, column_name...) - what columns you are loading, columns can be eliminated, but you have to keep the order straight. Also you can type the sqlldr command along with the control filename: sqlldr control=control_file.ctl And sqlldr loader will prompt you for your username and password.

LEVEL

Level is a pseudo-column, meaning it doesn't really exist in the table being queried, but it can be used to show the different "levels" in a hierarchical query.

Loading data with commas

Loading data with commas ',' in the data. LOAD DATA INFILE 'indata3.csv' into table emp fields terminated by "," OPTIONALLY ENCLOSED BY '"' (empno, emp_f_name, emp_l_name, deptno)

The reasons for using embedded SQL continued

Many applications are best processed in batches. Data are collected and stored until a large number of transactions occur or a designated time occurs (such as nightly processing of checks at a bank facility.) Typical applications written in high-level languages include payroll systems, cash receipts, disbursements and general ledger. These applications are written in mid-level languages utilizing embedded SQL.

MapReduce

MapReduce - used to process large data sets with a parallel, distributed algorithm on a cluster of servers. First you perform the mapping methods that sort and filter the data. Second you use reduce methods to summarize the data and count values given a set of parameters.

Mobile Apps

Mobile apps/reports are specifically designed for smart phones and tablets. They are "big and bold" aggregating and summarizing data.

CONSTRAINTS

NOT NULL <column_name> datatype(size) NOT NULL, This means that you have to put something in this column. The order of tables with constraints matter if you are dropping tables. If you have a table with a Foreign Key (call it table 'A') that references another table (call it table 'B') you will have to Drop table 'A' before table 'B' because if you don't you will get Foreign Key constraint violation.

Types of Constraints

NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK

INITCAP()

NOTE: in this example the value for empfname stored in the table is not changed, it only appears different in the result set of the SQL query.

Network Databases

Networked databases worked, but you had to know how the tables were connected and you had to strictly maintain the connections between the tables or you couldn't be sure where your data was or if the results of your queries were valid.

New Join Syntax.

New Join Syntax: SELECT student_lst_nm, student_frst_nm, telephone_number FROM student s INNER JOIN telephone t ON s.collegeID = t.collegeID / STUDENT_LST_NM STUDENT_FRST_NM TELEPHONE_NUMBER -------------------- -------------------- ---------------- Parker Al 5551234 Parker Al 8781234

Verify

Normally the variable substitutions appear in the command window as they occur. If you want to stop this from displaying, you can use VERIFY [ON/OFF] SQL> set verify on SQL> select * from jobs where job_id LIKE '&JOBID'; Enter value for jobid: SA_MAN old 1: select * from jobs where job_id LIKE '&JOBID' new 1: select * from jobs where job_id LIKE 'SA_MAN' JOB_ID JOB_TITLE MIN_SALARY ---------- -------------------- ---------- SA_MAN Sales Manager 100000

Date format

Note: the date format 'DD-MON-YYYY' is often referred to as the Oracle default date format. This is usually true but international dates are in different date formats and different date formats can be set during the installation of the Oracle SQL database instance.

number- based conditions

Numeric-Based Conditions SELECT last_name, salary FROM employees WHERE salary = 10000; The comma in 10000 must not be included or you will get the error: ERROR at line 2: ORA-00933: SQL command not properly ended select empfname, empsalary from employees where empsalary = '10000' With the single-quotes is also acceptable because SQL will do the conversion from character data to numeric data.

OLAP

OLAP Online Analytical Processing - the data warehouse helps to optimize the business once it is up and running.

OLTP

OLTP -online transaction processing. These are the applications that process customer transactions and create entries into databases.

OLTPs

OLTP software writes to different databases

Skipping Header records

OPTIONS (SKIP=1) LOAD DATA INFILE 'indata4.csv' into table emp fields terminated by "," OPTIONALLY ENCLOSED BY '"' (empno, emp_f_name, emp_l_name, deptno)

Loading data with runtime data

OPTIONS (SKIP=1) LOAD DATA INFILE 'indata4.csv' into table emp fields terminated by "," OPTIONALLY ENCLOSED BY '"' (empno, emp_f_name, emp_l_name, deptno, hire_date SYSDATE)

BADFILE AND DISCARD files

OPTIONS (SKIP=1) LOAD DATA INFILE 'indata4.csv' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis' APPEND into table emp fields terminated by "," OPTIONALLY ENCLOSED BY '"' (empno, emp_f_name, emp_l_name, deptno, hire_date SYSDATE) Loader6.ctl

View commands

OR REPLACE - if the view already exists it will be dropped and then recreated. A view is the result of a SELECT statement so no data is lost dropping a view.

ODS

Operational Data Store. A database designed to integrate data from multiple sources for additional operations on the data. A temporary location. The ODS is the first stop for the data on its way to the warehouse. The ODS is also a place where you can clean up the data before it gets to the data warehouse. Source systems are available for extraction during a specific time, usually after hours. Extracted data from the same set of source systems are going to be used in multiple places (data warehouse loading, ODS loading, third-party applications, etc.) so the data should be consistent.

Map Charts

Oracle ApEx map charts is a map with a data overlay, and not a web mapping applications such as Google Maps.

to_char, to_date

Oracle SQL uses the explicit conversion functions to_char and to_date to work with date values. To use the to_date() function you will have to add two parameters: the date value and the format mask. In this example the character string '26-AUG'13 12:00:00' is explicitly converted into the DATE data type using the to_date() function along with the parameters: date value('26-AUG-13 12:00:00' and format mask ('DD-MON-YY HH12:MI:SS'). This SELECT statement will work, but can you see the logic error? There are better ways to write this SELECT statement. select stdnt_lname ||', '|| stdnt_fname as name, stdnt_entry_dt as "date" from students where stdnt_entry_dt = to_date('26-AUG-13 12:00:00', 'DD-MON-YY HH12:MI:SS') /

Relational Databases

Originally Oracle and the other few "relational" database products on the market stored and retrieved data from a database in a purely relational way.

PL/SQL

PL/SQL can be used to dynamically select, insert, update and delete data from tables.

PL/SQL

PL/SQL is also a programming language that uses embedded SQL but since PL/SQL is part of the Oracle Application environment you don't have to specify a login to Oracle.

Packaged Applications

Packaged Applications are apps that have been created by Oracle the developers can download and modify for their own use. The modified packaged application can then be deployed to users. "Packaged applications are a suite of business productivity applications, easily installed with only a few clicks. These solutions can be readily used as production applications to improve business processes and are fully supported by Oracle."

Headers and Footers

Page headers and footers display information about your application that you want users to see, such as the title and navigation back to the home page of your application.

Database Systems

Previous versions of Database Systems were flat file, network or hierarchical. To grab more market share for their flagship database products RDBMS companies began to offer the ability to store and retrieve data from a database in a "hierarchical" way.

Functions previously

Previously in CIS 151/251, we worked with functions. There were two general types of functions: User created which you yourself created and then called from your program. System defined where you added a header file to your program and then referenced the function that you wanted.

CONCAT

Previously we have looked at the concatenation operator '||'. There is a SQL function that will do the same thing. Whether you use the operator or the function will depend on what you want accomplished. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The following queries illustrate the usage of this function: select concat(1+2.14,' approximates pi') from dual; select concat('Today is:',SYSDATE) from dual;

projection

Projection is the restricting of column names in a SQL query so that only useful information is returned to you, or the organization that you are working for Queries can offer information quickly or can be made into more elaborate reports that have column aliases, headers, foot notes, title pages, etc.

ROWNUM

ROWNUM is a pseudo-column. It doesn't exist in the table, but serves as way to filter results as if you had records returned by a query numbered 1, 2, 3, 4...

RPAD

RPAD() will pad a given character set to a selected column or value at a given place. select rpad(dt_rcvd,18,'-HH:MM:SS') from product_information;

Single Ampersand Substitution

Replacing one or more column names in a SQL query allows the query to be reused, rather than writing very similar queries over and over. "When the SQL statement is executed, the Oracle server processes the statement, notices a substitution variable, and attempts to resolve the variable's value in one of two ways."

Oracle ApEx

Reports draw information from database tables. SQL queries are used to retrieve the data from the tables, and the developer or user can then specify how the data is displayed in the app.

HAVING Clause

SELECT COUNT(*) FROM all_tables GROUP BY owner HAVING COUNT(*) > 1;

Using Table Aliases

SELECT student_lst_nm ||', '||student_frst_nm ||' '|| student_mddl_nm "Student Name", telephone_number FROM student, telephone WHERE student.collegeID = telephone.collegeID / The use of table aliases can help improve the performance of SQL statements. When you select data from two or more tables, you should specify which table each column belongs to. Otherwise, if two tables have the columns with the same name then, then the SQL statement will fail. The database can't resolve which column comes from which table. SELECT student_lst_nm ||', '||student_frst_nm ||' '|| student_mddl_nm "Student Name", telephone_number FROM student, telephone WHERE collegeID = collegeID / ERROR at line 3: ORA-00918: column ambiguously defined SELECT * FROM student s, telephone t WHERE student.collegeID = telephone.collegeID / The table name is used to qualify each column name. This eliminates any ambiguity as to which collegeID column the query is referring to. SELECT * FROM student s, telephone t WHERE s.collegeID = t.collegeID / You can also use programmer-defined "identifiers" instead of the actual table name to qualify the ambiguous column name. The letters "s" and "t" in this example are table aliases. You can specify these aliases after the table names. Table aliases provide a convenient shorthand notation , making your queries more concise and readable. Table aliases can be up to 30 characters. If you do create table aliases inside a SQL query, you will have to use those aliases to qualify column names; you will not be able to switch back to using the table name to qualify the table.

ANSI-join outer selects.

SELECT student_lst_nm, student_frst_nm, telephone_number FROM student s LEFT OUTER JOIN telephone t ON s.collegeID = t.collegeID / STUDENT_LST_NM STUDENT_FRST_NM TELEPHONE_NUMBER -------------------- -------------------- ---------------- Parker Al 5551234 Parker Al 8781234 Thompson John

GROUP BY

SELECT table_name, COUNT(*) FROM all_tables GROUP BY table_name; Give the tables in the database. ALL_TABLES is a meta-table. That is, it describes the rest of the tables in the database. select product_status, MIN(list_price) from product_information GROUP BY product_status; SELECT department_id, COUNT(*) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department_id; Get the number of employees in each department.

SPOOL

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT] Stores query results in a file, or optionally sends the file to a printer. In iSQL*Plus, use the preference settings to direct output to a file. SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT] Stores query results in a file, or optionally sends the file to a printer. In iSQL*Plus, use the preference settings to direct output to a file. CRE[ATE] Creates a new file with the name specified. REP[LACE] Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file. APP[END] Adds the contents of the buffer to the end of the file you specify. OFF Stops spooling. OUT Stops spooling and sends the file to your computer's standard (default) printer.

The reasons for using embedded SQL

SQL can be used as both an interactive query language and as an interface between mid-level languages such as C, C++, C#, Java, PL-1, COBOL and FORTRAN, allowing the developer the advantages of both languages. The advantages of using embedded SQL: On-line processing of transactions involves formatted screens to more easily enter data and data validation routine to ensure the integrity of the data entering the database through the use of error-handling and exceptions. Neither of these functions are possible with interactive SQL so developers will use embedded SQL.

SQL Injection

SQL injection is inserting malicious code SQL into fields that then get executed as legitimate SQL code.

nested functions

SQL system-defined functions can be nested one within the other: select concat('Product:',UPPER(Product_description)) from product_information;

LENGTH()

SQL> r 1 select length('this is a string of characters') 2* from dual LENGTH('THISISASTRINGOFCHARACTERS') ----------------------------------- 30 Here the length of the character string is returned. Often it is necessary to know the length of a string before performing an update or insert on a table. What if the string is too long for the destination column?

INITCAP

SQL> select INITCAP(empfname) from employees 2 where empno = 13; INITCAP(EMPFNAME) -------------------- Andrew

MIN()

SQL> select TRUNC(MIN(list_price),2) from product_information; TRUNC(MIN(LIST_PRICE),2) ------------------------ 103 What is the price of the least expensive item in the inventory?

Sysdate Conversion

SQL> select length(SYSDATE) from dual; LENGTH(SYSDATE) --------------- 9 The date value was held as 'DD-MON-YY' and was converted, minus '-' signs included into a number.

String To Number

SQL> select mod ('11',2) from dual; MOD('11',2) ----------- 1 11 divided by 2 is 5 with 1 remaining. Here the implicit conversion was from a character string '11' to a number 11. select mod('$11',2) from dual * ERROR at line 1: ORA-01722: invalid number This SELECT won't work because the '$' symbol can't be implicitly converted into a number.

Set VERIFY OFF

SQL> set verify off SQL> run 1* select * from jobs where job_id LIKE '&JOBID' Enter value for jobid: SA_MAN JOB_ID JOB_TITLE MIN_SALARY ---------- -------------------- ---------- SA_MAN Sales Manager 100000

SQLCA

SQLCA - The SQL Communications Area. This allows the embedded SQL to communicate error messages, warnings and other program messages to the host program. Sqlcode - 0 - no error. Like the return(0) statement in C/C++ this means successful completion of SQL statement.

SQLCODE

SQLCODE > 0 - means that statement executed normally and some special (non-fatal) condition occurred. SQLCODE < 0 - means that an abnormal condition , such as a system failure or corruption of the database has occurred. In order to use the SQLCA in an embedded SQL program you have to declare it in COBOL: EXEC SQL INCLUDE SQLCA END-EXEC. In C/C++: EXEC SQL INCLUDE SQLCA.H;

The Student Table

STUDENT *collegeID Student Last Name Student First Name Student Middle Name Student Date of Birth Social Security Number

Joining Two Tables

STUDENT *collegeID Student Last Name Student First Name Student Middle Name Student Date of Birth Social Security Number | | to | | STUDENT TELEPHONE CollegeId, Telephone Type Area Code Telephone Number

OLTP vs Data Warehouse

Savings Current Account Loans All Applications/DB combinations The Data Warehouse collects all of this information as the complete customer profile.

selection

Selection is the restriction of rows returned by a SQL query by using different conditions. "Selection is actualized using the WHERE clause of the SELECT statement."

single row functions

Single-row functions (SRF) operate on every row of a table being selected.

functions

Some of the parameters of a function are mandatory and some are optional. What you want to accomplish will often determine what function you use and how you use it. Functions may be stand-alone or nested inside other functions to achieve the desired result. "SQL functions are broadly divided into those that calculate and return a value for every row in a data set and those that return a single aggregated value for all rows." LOWER, UPPER, and INITCAP select product_name, product_status from product_information where LOWER(product_status) = 'in'; You can also force the data to conform to the expected literal value in this case 'IN' select product_name, product_status from product_information where UPPER(product_status) = 'IN';

SQLCODE

Sqlcode: >0 Statement executed but exception detected. This occurs when Oracle cannot find a row that meets your WHERE condition or when a SELECT INTO or FETCH returns no rows. An example would be the ora 1403 "no data found" error. sqlcode <0 - Oracle did not execute the statement because of an error. When such errors occur, the current transaction should, in most cases, be rolled back.

sqlldr

Sqlldr is a bulk loader utility used to load data from external files into Oracle tables. It is similar to the BCP (bulk copy program) used in Microsoft SQL Server. It is also similar to the command line load utility in the DB2 database management system. It is invoked from outside a sqlplus session.

Application Builder

The Application Builder tab allows you to create Database applications and Websheet applications.

Summary

Sqlldr offers the SQL developer and DBA a way to load large amounts of data into table. With sqlldr one table or multiple tables can be loaded at the same time. The sqlldr utility is invoked outside of a SQL *Plus session. Sqlldr offers the SQL developer and DBA a way to load large amounts of data into table. With sqlldr one table or multiple tables can be loaded at the same time. The sqlldr utility is invoked outside of a SQL *Plus session. External tables offer the SQL developer a way to access data contained in file through a table.

EXTERNAL TABLES

Step 4: ORGANIZATION EXTERNAL (type oracle_loader default directory <your_directory> access parameters (fields terminated by ',') location (<data_file.dat'>) The syntax for the external table "ORGANIZATION EXTERNAL' description looks a lot like the SQL Loader control file. An external table can do everything that SQL Loader can and you can use regular SQL statements to display the data. In older version of the Oracle Database the external table could only be read from; as of Oracle Database 10g, external tables can also be written to.

Subqueries

Subqueries are another way to get information for your SQL query. Subqueries are fully formed queries: select column_name from table_name but they can return only one column_name or expression and do not have a statement terminator ';' or '/'. "A subquery is a query embedded within another SQL statement. This statement can be another query or a DML statement. Subqueries can be nested within each other with no practical limits." Subqueries are contained inside of parentheses: (select column_name from table_name) A SQL query can have many subqueries and subqueries can be nested for many levels. Subqueries are used to find the existence or non-existence of a value in a given table: where NOT EXISTS(select 8 from table1);

Summary

Subqueries are fully formed SQL queries that return a single value that can be used in another SQL query. Subqueries can be used with the HAVING, GROUP BY, IN EXISTS, <, > , = operators. Subqueries are contained inside parentheses to show that they are their own separate query.

Correlation

Subqueries imply a basic "correlation" between two result sets. The first result set is the "inner" query or subquery that will return a value. The result of this "inner" query is then used in the "outer" query as if it were part of the "outer" query. When we talk about "correlation" here, we are not talking about mathematical concepts of "sameness" or even the same datatypes. We are talking about practical results. A relation between two result sets (the query and subquery) that returns meaningful results.

The Outer Join

Suppose you want to see all students from the student table, along with their telephone number. If they currently don't have a telephone table record, you still want to see their student record. In this case you would need an outer join. The outer join uses the outer join operator (+) The outer join operator appears after in inner join column name separated by a space. SELECT student_lst_nm, student_frst_nm, telephone_number FROM student s, telephone t WHERE s.collegeID = t.collegeID (+) / STUDENT_LST_NM STUDENT_FRST_NM TELEPHONE_NUMBER -------------------- -------------------- ---------------- Parker Al 5551234 Parker Al 8781234 Thompson John Suppose you wanted all of the telephone numbers from the telephone table, even the numbers of those people who aren't in the student table (they may be applicants or just haven't been added to the student table yet.) SELECT student_lst_nm, student_frst_nm, telephone_number FROM student s, telephone t WHERE s.collegeID (+) = t.collegeID / STUDENT_LST_NM STUDENT_FRST_NM TELEPHONE_NUMBER -------------------- -------------------- ---------------- Parker Al 5551234 Parker Al 8781234 5551222 In this example, even though we are looking for telephone records that don't have a corresponding student record the outer join condition is on the left after the student inner join condition. This is because the database "adds" or creates a student record to pair with the telephone record. Note: the outer join can appear on only one side of a query. SQL> sta select_student; WHERE s.collegeID (+) = t.collegeID (+) * ERROR at line 3: ORA-01468: a predicate may reference only one outer-joined table

Terms

Surrogate keys - an alternate key with a sequence number that can be used as a primary key in the data warehouse. Fact tables - tables that store measures and surrogate keys. Dimension tables - tables that store attribute level information.

sysdate

Sysdate is a SQL operator that returns the current date, but also has a time component. Unless specified the time segment of sysdate is not shown.

systimestamp

Systimestamp is similar to sysdate but allows much more precision: select systimestamp from dual; 20-SEP-16 05.23.09.844586 PM +00:00

The Result

TO_CHAR(SYSDATE,'DY')||''||TO_CHAR(SYSDATE,'MONTH')||''||TO_CHAR(SYSDATE,'DD') ------------------------------------------------------------------------------ TUE SEPTEMBER 24 TWENTY THIRTEEN day 3 of the week.

Dot Notation

Tables are joined using "dot notation". Similar to other programming languages the object (in this case the table) goes first, then a dot '.' and then the table_name. SELECT column_name, column_name... FROM table_name_1, table_name_2 WHERE table_name_1.column_name = table_name_2.column_name;

Tables

Tables are two-dimensional structures containing rows of data. Rows are made up of one or more columns.

Ted Codd

Ted Codd wouldn't approve of mixing the database paradigms, but the companies were trying to make money and not write academic papers.

primitive data types

That data stored in most tables will take the form of the primitive data types: NUMBER, VARCHAR2 and DATE. NUMBER is formatted with the total number of digits and the decimal place.

Hierarchical Databases

That left hierarchical databases. It is human nature to see structure and order in systems. Familiar shapes in clouds... A genus of species... A command structure.

ANY or ALL

The ANY and All operators can look for lowest or highest values depending on whether a '<' , '>' or '=' sign appears before the ANY or ALL keywords. The ANY and ALL operators are similar to the MAX() and MIN() functions.

the BETWEENoperator

The BETWEEN operator looks for a range of values including the starting and ending values given in the clause. select * from employees where empsalary between 35001 AND 100000; Look for employees making between base salary (as defined as $35,000 and less) and $100,000. I had to write it as 35001 because the BETWEEN returns the lowest value, the highest value and the range in between.

CASE Statement

The CASE statement was added to SQL to create an IF...ELSE... stucture like other programming languages. CASE (some condition) IF value1 THEN display_this... IF value2 THEN display_this... DEFAULT display_this... END case

CHAR

The CHAR datatype does have a fixed number of characters. It can be declared like this: fixedString CHAR(10);

COUNT()

The COUNT(*) function returns the total number of rows in a table. COUNT(column_name) can also be used to count rows based on a column name. select count(department_id) from employees;

CHECK Constraint

The Check constraint forces values enter into a table to conform to a given list of values. CONSTRAINT ck_student_major CHECK (major IN('ACC','FIN','MGT','MKT') Here only the abbreviations for Accounting, Finance, Management and Marketing are accepted, and they have to be uppercase.

Data Warehousing

The Data warehouse is a concept that emerged in the 1980s. Big Data is a technology.

IN operator

The IN Operator is used when you want to use an equality operator on two or more values. select * from employees where department_id IN(70,80,90); This will return only the employees from the departments 70, 80 and 90.

the like operator

The LIKE operator is a wildcard operator that uses the '%' symbol to stand for any possible number of characters. It can be used before and after the literal value being searched for. SQL> r 1* select * from employees where empfname LIKE 'A%' EMPNO EMPFNAME EMPSALARY DEPARTMENT_ID BOSSNO ---------- -------------------- ---------- ------------- ---------- 1 Alice 125000 10 3 Andrew 25000 20 2 The '' symbol is for multiple characters. You can also use the '_' underscore symbol to do wildcard search for only one character.

MINUS Set Operator

The MINUS set operator is like an apple with a bite taken out of it. The body of the apple is the original result set returned but all we get in the final result set is the "bite" or what is left over after the MINUS operations.

the OR clause

The OR clause lets you create another result set with its own clauses written along with you original SQL query. select * from job_history where to_char(start_date,'DD-MON-YYYY') between '10-SEP-2012' and '10-SEP-2013' OR to_char(start_date,'DD-MON-YY') < '10-SEP-80' select * from job_history where to_char(start_date,'DD-MON-YYYY') between '10-SEP-2012' and '10-SEP-2013' OR to_char(start_date,'DD-MON-YY') < '10-SEP-80' and employee_id = '1' Here the and clause that specifies the employeed_id must be 1 is paired with the OR clause and not the WHERE clause.

ORDER BY

The ORDER BY clause, placed at the end of a SQL query provides a way to sort the data returned by a query. select * from employees ORDER by empsalary; ORDER BY can be ascending or descending.

Declare Table Statement in COBOL.

The SQL table or view accessed in a COBOL program can be declared in the WORKING-STORAGE SECTION using the DECLARE TABLE command. This embedded SQL has the following basic syntax: EXEC SQL DECLARE table-name or view-name TABLE (column_name1 datatype [NOT NULL], column_name2 datatype [NOT NULL]]...) END EXEC. The inclusion of the table or view definition helps document the program and enables the precompiler to perform checks to ensure SQL column names used within the embedded SQL program are correct.

TRIM()

The TRIM Function "The TRIM function removes characters from the beginning or end of character literals, columns or expressions to yield one potentially shorter character item. Numeric and date literals are automatically cast as characters when they occur as parameters to the TRIM function. Numeric or date expressions are evaluated first before being converted to strings ready to be trimmed." select product_name, trim(both '#' from product_status) from product_information; Here '#' will be trimmed from both the left and right-hand of whatever is stored in the product_status column. Other TRIM() options: leading trailing any number of characters you want removed select trim(1 from SYSDATE) from dual Here the first number of today's date is removed from the output of the query.

ApEx Parts

The basic components of an ApEx application include a log-in page that asks for your username and password and a default home page. More pages can be added when you create the app or more pages can be added in the Page Designer view.

WHERE

The basic syntax of the SQL query with a WHERE clause SELECT column_name1, column_name2... FROM table_name WHERE some_condition

Star Schema

The center of the star consists of a large fact table and the points of the star are the dimension tables.

Undefine

The undefine command will clear out the contents of a variable that has been defined previously in the session. SQL> UNDEFINE PRODNAME;

Big Data Attributes

Volume - large amounts of unstructured data Variety - data comes from different sources. Velocity - the data is created very quickly. Veracity - do decision makers trust the information that they are receiving?

DML

The component of SQL that modifies or updates data is called DML Data Manipulation Language. DML is a transaction management language - every action performed in a database must be done in relation to other actions and objects. Strictly speaking, there are five DML commands: SELECT INSERT UPDATE DELETE MERGE "In practice, most database professionals never include SELECT as part of DML. It is considered to be a separate language in its own right, which is not unreasonable when you consider that it has taken the eight preceding chapters to describe it. The MERGE command is often dropped as well, not because it isn't clearly a data manipulation command but because it doesn't do anything that cannot be done with other commands."

Sqlldr .csv file

The contents of the .csv file: 1,Holmes,Sherlock,1 2,Hammer,Mike,2 3,Ness,Elliot,3

Why study embedded SQL?

The database industry is moving towards large applications or large application packages being installed over the DBMS, whether it be Oracle, Microsoft SQL SERVER or other DBMSs. These applications use embedded SQL to access, modify and delete the data inside the database. Embedded SQL can also perform such DDL actions as creating tables.

to_char

The easiest way to get the results that we want is to 'drop off' the time component and simplify the select statement. We can do this using the to_char() function. The to_char() function uses two parameters: date value and format mask. In this example stndt_entry_dt of data type DATE is explicitly converted into a string of just the date values and then compared to the string on the right. The time value is in effect left off for now. select stdnt_lname ||', '|| stdnt_fname as name, stdnt_entry_dt as "date" from students where to_char(stdnt_entry_dt,'DD-MON-YY') = '26-AUG-13' /

Sample embedded SQL program.

The following is a simple embedded SQL program written in C that will be demonstrated in class. The program opens an connection to the Oracle RDBMS and then selects student information from three different tables. The information is then displayed.

Results of Subquery

The inner query gets evaluated first and in this case, the intermediate query would look like this: select customers.customer_id, cust_first_name, cust_last_name from customers where customer_id NOT IN(1, 2, 3, 4); results of inner query

INSERT

The insert statement add data to an existing table. The data is appended to the table. Basic syntax INSERT INTO table_name(columns...) VALUES(data...); If you are loading every column in the table you can omit the (columns...) part.

Join

The join brings together two or more tables together so that the result set returned is a mixture of the columns from two or more tables. The join is a virtual join. It does not affect the data in any way.

VARCHAR2

The maximum size for a VARCHAR2 column is 4,000 bytes: The maximum size of a VARCHAR2 column is 4,000 bytes in SQL: create table v_test(astring varchar2(4001)) * ERROR at line 1: ORA-00910: specified length too long for its datatype

Compound Statements

The number of columns in each SELECT statement must match. The datatypes of the columns in each SELECT statement of a compound statement must match.

Content

The regions area of your app content can include: PL/SQL dynamic content, Calendars, Reports, Map charts and URLs.

One-to-Many Relationships

The relationship between the STUDENT and STUDENT_TELEPHONES tables can be described as a "One-to-Many" relationship. There is one student record associated with (potentially) many telephone records. At this point we might be tempted to say "Why have two tables? Just put all of the phone information in the STUDENT table."

Snowflake Schema

The snowflake schema builds on the star schema and has dimension tables that refer to other dimension tables.

SUBSTR()

The substr() sub string function returns a character or characters given the place in the string and how many characters you want returned. select substr(product_name,1,2) from product_information / You give the function the column name, where to start and how many characters to return from that starting point.

to_date()

The to_date function can be used with just parts of a date, not just the whole date as long as you supply the right format mask. In the preceding example all that had to be supplied was the function name and two parameters. The system took care of the rest. update students set stdnt_entry_dt = to_date('AUG','MON') where stdnt_cd = 'F' / Here the month is updated to show that freshmen will be entering the school in august. The stdnt_entry_dt column of the table students has been modified to show that freshmen will be entering the school in August of 2015. SQL> select * from students; STDNT_LNAME STDNT_FNAME STDNT_ID ST STDNT_ENT -------------------- -------------------- --------- -- --------- Codd Edgar 1 F 01-AUG-15 Byrne John 2 SO 01-FEB-15 Ditko Steve 3 F 01-AUG-15 Wrightson Bernie 4 F 01-AUG-15 Kriby Jack 5 F 01-AUG-15 Sambourn Linley 6 SE 01-FEB-15 Farrah Mo 7 F 01-AUG-15 Gebrselassie Haile 8 J 01-FEB-15 8 rows selected.

Date and timestamp

The two primary DATE data types in SQL are: DATE and TIMESTAMP. TIMESTAMP is a new version of DATE that supports date values and much more precise time values than DATE. SQL> desc students; Name Null? Type ----------------------------------------- -------- ---------------------------- STDNT_LNAME VARCHAR2(20) STDNT_FNAME VARCHAR2(20) STDNT_ID VARCHAR2(9) STDNT_CD VARCHAR2(2) STDNT_ENTRY_DT DATE The above table description shows the students table that will be used for the examples in this lecture. It is a simple table with on two data types: VARCHAR2 and DATE.

Oracle user-defined functions

There is an important distinction between the system functions that come with SQL packages and the ones created by third-party SQL developers. User-defined SQL functions are created using PL/SQL or T-SQL (according to which RDBMS you are using) and are "owned" by different schemas and you may have to GRANT permissions to use them.

The college system

This diagram seems to imply that there is a chain of command where the chairperson of the department manages the full professor and the full professor manages the associate professor and so on. But this may not be the case. It may only mean the different salary levels, perks, etc. for the different levels.

A Few Caveats...

This version of the embedded SQL SELECT statement can only be used to retrieve zero or 1 row from the DBMS. If more than one row is returned to SQLCODE will contain a negative value, and the application will cease or not perform as expected. The INTO clause specifies where you want the retrieved data to be stored. The host variables following the INTO clause correspond one-for-one with the column names in the SELECT clause.

timestamp

Timestamp by itself will not work but has to be converted for use: SELECT CAST(SYSDATE AS TIMESTAMP) date_to_ts FROM dual;

COUNT(distinct column_name)

To count how many unique values there are of a column value you can use the DISTINCT keyword. select count(distinct department_id) from employees;

GROUP BY

To create groups of data in you result set you use the GROUP BY keywords. This will group data based on some criteria after the results have been restricted by the WHERE and AND clauses. Make a query that shows be department who makes the most. GROUP BY department. What this will do is find the highest earner by department. ORDER BY department - what this will do is give the department_id in ascending order. select department_id,max(empsalary), count(*) from employees group by department_id order by department_id;

The Select Statement.

To retrieve a single row of data from the Oracle DBMS, a SELECT statement containing the query is placed in the PROCEDURE DIVISION section of you COBOL program. Here is the basic syntax: EXEC SQL SELECT column-name1 [,column-name2]... INTO host-variable1 [, host-variable2]... FROM table-name [WHERE search-condition] END-EXEC. The only real difference between the embedded SQL statement and an interactive SQL statement is the INTO command and the host variable.

Retrieve Multiple Rows

To use a cursor you have to: 1. Declare the cursor and the cursor structure. 2. Open the cursor in your embedded SQL program. 3. Retrieve the records one at a time from the cursor by using a loop. 4. Close the cursor after you are done fetching from it.

triggers

Triggers are stored procedures that automatically do something inside the database when a certain event occurs, such as: a new row is added, deleted or updated in a table. a new user is created in the database. a privilege or role is granted or revoked. Triggers allow you to customize your database and DB events, and do things such as: prevent invalid transactions to log events prevent DML operations on a table after regular business hours send information about database events, user events, and SQL statements to applications Triggers are PL/SQL code blocks that are composed mainly of SQL statements. Triggers have to be compiled like source code, but since they are integrated into the Oracle DB, you don't need any special statements to compile triggers. Regular SQL queries won't give you an error message until you try to run them. A trigger has to be compiled, but it may give you an error message: Warning: Trigger created with compilation errors. In this case, you can use the show errors command to see what the error message is: SQL> show errors Errors for TRIGGER T: LINE/COL ERROR -------- ----------------------------------------------------------------- 10/7 PL/SQL: Statement ignored 10/19 PLS-00302: component 'PT_LINE' must be declared You create a trigger with the CREATE TRIGGER statement. You do not have to have a COMMIT statement when inserting, updating or deleting a row via a trigger. CREATE OR REPLACE TRIGGER trg_table_name BEFORE INSERT OR UPDATE OR DELETE ON table_name BEGIN CASE WHEN INSERTING THEN INSERT INTO table_name values(COLUMN1,COLUMN2,COLUMN3); WHEN UPDATING THEN --code goes here WHEN DELETING THEN --code goes here END CASE; END; Triggers for UPDATE have this syntax: BEFORE UPDATE OF lname, fname ON customers; WHEN UPDATING ('lname')THEN --code goes here

One-to-one Relationship

Two SQL tables are said to have a one-to-one relationship if every row can be matched to one and only one row from another table.

summary

UNION ALL concatenates the results of two queries. UNION sorts the results of two queries and removes duplicates. INTERSECT returns only the rows common to the result of two queries. MINUS returns the rows from the first query that do not exist in the second query.

select

Up until now we have been looking at the SELECT statement to retrieve information from the database, but these SELECT statements do not change the data in any way. The first nine chapters of the book were about the SELECT statement, because creating and modifying reports takes up most of a SQL developer's time. Although some people would say that SELECT is a part of the SQL DML (Data Manipulation Language) other wouldn't because a SELECT statement is a non-destructive read. It doesn't really manipulate or change the data just the way people view the data.

VARCHAR2

VARCHAR2 - stands for Variable Character. This is a datatype that can automatically resize itself so that is uses less memory. The '2' in VARCHAR2 is not significant. It just means that this is the second version of this datatype.

Datatypes

VARCHAR2- hold numbers, letters and characters. Good for things like addresses. NUMBER - holds numbers, no letters. Good for math. DATE - holds date values. Good for start times, end times and date created.

Self-join

Very often data for one person repeats in a table: multiple phone number, Term records, Insurance providers, etc. These are called repeating tables. There are various ways for dealing with repeating tables. The 'where' and 'and' clauses be used to restrict the data being returned. Tables can be joined to themselves to provide a piece of data that may appear several times in one table for one person. Select column1, column2... From table1 JOIN table2 t21 ON condition JOIN table2 t22 ON condition

Temporal data

Very often temporal or time-related data is added along with the date component of date. Time components are: hours, minutes and seconds along with a 12 or 24 (military time) specifier.

Quarters

What is a 'Quarter - Q1, Q2, Q3, Q4' A quarter (Q1, Q2, Q3, Q4) is a three-month period on a financial calendar that acts as a basis for the reporting of earnings and the paying of dividends. A quarter refers to one-fourth of a year and is typically expressed as "Q." SELECT SYSDATE, TRUNC(SYSDATE, 'Q'), ROUND(SYSDATE, 'Q') FROM dual;

Extent

When a table is created an extent - how much space the table has inside of the database is also created.

Joining Multiple Tables

When selecting data from one table only, queries are relatively simple. The WHERE clauses and any subsequent AND clauses are dedicated to the selection criteria. With two or more tables, very often the WHERE clause is used to join the tables and the AND clauses are used to filter out the data returned in the result set. If multiple tables are joined correctly joined then the two tables can then be considered as one large table or collection of related data.

union

When using UNION the datatypes must match but the actual values don't have to match. When using INTERSECT the datatype and values must match to see the data in the result set. "UNION Returns the combined rows from two queries, sorting them and removing duplicates." "UNION ALL Returns the combined rows from two queries without sorting or removing duplicates." The UNION ALL operator merges the result sets of two component queries. The UNION operator merges the result sets of two component queries. BUT also eliminates duplicates.

Cartesian Product

When you don't specify a join condition when joining two tables, the database combines each row from the first table with each row from the select table. SELECT count(*) FROM student s, telephone COUNT(*) ---------- 4 The number of rows returned from a Cartesian join is the product of the number of rows in each table. There are two rows in student and two rows in telephone. 2 *2 = 4

Look for Existing Constraints

select owner, table_name, constraint_name,constraint_type from user_constraints where owner = '<user_name>'; Your SQL username in all capital letters

Dynamic SQL

While embedded SQL is fine for fixed applications, sometimes it is important for a program to dynamically create entire SQL statements. With dynamic SQL, a statement stored in a string variable can be issued. PREPARE turns a character string into a SQL statement, and EXECUTE executes that statement. Consider the following example. char *s = "INSERT INTO emp VALUES(1234, 'Johnston', 3)"; EXEC SQL PREPARE q FROM :s; EXEC SQL EXECUTE q;

SQL Functions

With C/C++ or Java functions/methods you had to know the name of the header file/class and the name of the function/method in order to use it. In SQL there are no header files and the function names and what they are used for are straightforward.

Breadcrumbs

With the breadcrumbs you can: Close pages, Navigate to pages, Access help pages, Edit, copy and create.

Migrating Applications

You can also migrate existing applications in to the ApEx workspace using the "Migrations" command found under the Application Builder tab.

Sqlldr basic syntax

You will need: a .ctl control file, one or more data files, with the .csv (commas separated values), .txt or .dat extension.

database and schemas

a database can have many schemas but a schema can have only one database

data analysis

a set of tools existing and ever-developing, a way to improve the ability to ask questions

Drop a Constraint

alter table <table_name> drop constraint <constraint_name>;

Creating a Foreign Key after the table has been created.

alter table sales add constraint fk_channel_id foreign key (channel_id) references products;

NUMBER

create table n_test(anumber number(39,2)) * ERROR at line 1: ORA-01727: numeric precision specifier is out of range (1 to 38) The maximum precision is 127: create table n_test(anumber number(38,128)) * ERROR at line 1: ORA-01728: numeric scale specifier is out of range (-84 to 127) SQL> r 1* INSERT INTO n_test VALUES(999999999999999999999999999999999999999) INSERT INTO n_test VALUES(999999999999999999999999999999999999999) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column

Getting data from Views

create view dept_emp as select department_name, last_name from departments join employees using (department_id); select * from dept_emp where department_name='Marketing';

Min function

select product_status from product_information group by product_status having min(list_price) > 100;

COALESCE()

select rownum, coalesce(state_province,to_char(postal_code),city), postal_code, city from province; Go from state_province, to postal_code to city looking for some data.

Views

create view view_student_gpa as select stdnt_fname, stdnt_lname, stdnt_gpa from students,student_gpa where students.stdnt_id = student_gpa.student_id; Why use views? "It may be that users should only see certain rows or columns of a table. There are several ways of enforcing this, but a view is often the simplest. Consider the HR.EMPLOYEES table. This includes personal details that should not be visible to staff outside the personnel department. But finance staff will need to be able to see the costing information." Views to Simplify User SQL "It will be much easier for users to query data if the hard work (such as joins or aggregations) is done for them by the code that defines the view. " "It is impossible to prevent users making errors, but well constructed views can prevent some errors arising from a lack of understanding of how data should be interpreted." CREATE VIEW <view_name> AS select column_name FROM table_name; FORCE or NOFORCE - with FORCE the view is created even if one of the tables in the subquery of the SELECT statement is missing. With NOFORCE an error is generating of there is a subquery table missing from the SQL SELECT statement.

INSERT ALL

insert all into stores values (3, 'BOSTON') into stores values (4, 'SCRANTON') SELECT * FROM DUAL;

INSERT specifying column names

insert into student(sid,s_lname,s_fname,gender, major,gpa,sat,dob) values('988','Bolt','Usain','M','MKT',4.0, 1600,'21-AUG-86'); Here the column names are included. These are all the column names.

INSERT with NULLs

insert into student values('988','Bolt','Usain','M','MKT',NULL, 1600,NULL); Here the same technique is used, only NULLs have replaced the GPA and Date of Birth columns.

INSERT with fewer columns

insert into student(sid, s_lname, s_fname, gender, major, sat) values('988','Bolt','Usain','M','MKT',1600); Here the column names 'GPA' and 'DOB' were omitted from the column list and then had to be eliminated from the INSERT value list.

joining

refers to interaction of tables with one another in a query

NEXT_DAY() with SYSDATE

select (next_day(to_date('21-NOV-2016','DD-MON-YYYY'),'thu')) - SYSDATE "Turkey Trot" from dual Turkey Trot ----------- 64.2867013888888888888888888888888888889

NVL2() Function

select NVL2(NULLIF(empfname||'@offspot.com',empemail), 'Email does not match','Email is correct') from employees; Nested conversion functions. Take the output of NULLIF and use it as a parameter in NVL2(). NVL2() returns a result of a string comparison.

data arithmetic

select * from job_history where end_date - start_date < 30 Date arithmetic can be performed on date values. Here the query is looking for employees who lasted less then a month.

BETWEEN operator and dates

select * from job_history where to_char(start_date,'DD-MON-YYYY') between '10-SEP-2012' and '10-SEP-2013' I had to use the to_char() function that has two parameters: the start date and the format the date should be in.

Using NULLS in Set Operators

select 1, 'String' from dual UNION select 2, NULL from dual; 1 'STRIN ---------- ------ 1 String 2

NULLIF() Function

select NULLIF(empfname||'@offspot.com',empemail) from employees; Compare two fields to see if they are equal. Return the first value if they aren't equal.

Add the Subquery

select customers.customer_id, cust_first_name, cust_last_name from customers where customer_id NOT IN(select customer_id from orders);

HAVING

select department_id, count(*) from job_history where department_id in (50,60,80,110) group by department_id having count(*)>1; DEPARTMENT_ID COUNT(*) ------------- ---------- 50 2 Among the product_status groups 'orderable', 'collectible', 'obsolete','under development' where do the high price computer peripherals come from? Assume that any price over $100 is a high price object. Select count(employee_id) as "EMPLOYEE COUNT", job_id as "DESIGNATION" from employees group by job_id having count(employee_id) >= 2;

NATURAL JOIN

select emp_id, emp_lname, emp_fname, emp_salary from employees NATURAL JOIN emp_salary_tbl; If there are two tables: EMPLOYEES and EMP_SALARY_TBL with a column that has the same name (emp_id) they can be "naturally" joined.

number expressions

select empfname, empsalary from employees where empsalary/26 = 1000; The number expression to be evaluated is on the left. select empfname, empsalary from employees where empsalary/26 = empsalary * 1.20 Here there are number expressions on both sides.

DECODE()

select empfname,decode(department_id,10, 'Management',20,'Accounting') from employees where department_id IN(10,20); Look for employees with a department_id of 10 or 20 and then "decode" or change this value into a string.

Nested Conversion Functions

select empno, empfname,NVL(to_char(department_id), 'No Dept') from employees;

character-based conditions

select firstname, lastname from emp where emp_status = 'PT'; The value of emp_status must be equal to the literal value = 'PT' even in case. The data in the table must also match the literal value in the SQL select. If someone enters 'pt' this query will not show that row.

Add ORDER BY LEVEL

select level,first_name, last_name, job from sabres_org start with dept_id = 10 connect by prior emp_id = manager_id order by LEVEL; LEVEL FIRST_NAME LAST_NAME JOB ---------- ---------- -------------------- -------------------- 1 Robert Swados Owner 1 Seymour Knox Owner 1 Northrup Knox Owner 2 Punch Imlach GM 2 Punch Imlach Head Coach 3 Martin Rick Player 3 Rene Robert Player

NEXT_DAY()

select next_day(SYSDATE,'fri') from dual; NEXT_DAY( --------- 20-SEP-13 Given a specific day and a day to find on or after that date, this function will return the DATE value of that day. What is the date of Thanksgiving this year? select next_day('21-NOV-2013','thu') "Turkey Trot" from dual; Turkey Tr --------- 28-NOV-13 How many days until Thanksgiving of any given year? select next_day(SYSDATE, 'thus') from dual; What is the next occurrence of a Thursday in the current month?

Total Non-correlation

select number_of_top_ten_hits from musical_artists where artist_last_name = 'Manilow' and artist_first_name = 'Barry' and number_of_top_ten_hits >= (select item_price from food_items where item_name = 'Bolagna');