DML using oracle

More SQL Data Definition INSERT, UPDATE, DELETE • INSERT - add a row to a table • UPDATE - change row(s) in a table • DELETE - remove row(s) from a table • UPDATE and DELETE use ‘WHERE clauses’ to specify which rows to change or remove • BE CAREFUL with these - an incorrect WHERE clause can destroy lots of data

More SQL Data Definition INSERT INSERT INTO <table> (col1, col2, …) VALUES (val1, val2, …) – The number of columns and values must be the same – If you are adding a value to every column, you don’t have to list them – SQL doesn’t require that all rows are different (unless a constraint says so)

More SQL Data Definition INSERT Student ID 1 Name John Year 1 Student ID 1 2 Name John Mary Year 1 Student ID 1 2 Name John Mary Year 1 3 Student ID 1 2 Name John Mary Year 1 3 INSERT INTO Student (ID, Name, Year) VALUES (2, ‘Mary’, 3) INSERT INTO Student (Name, ID) VALUES (‘Mary’, 2) INSERT INTO Student VALUES (2, ‘Mary’, 3)

More SQL Data Definition UPDATE UPDATE <table> SET col1 = val1 [,col2 = val2…] [WHERE <condition>] – All rows where the condition is true have the columns set to the given values – If no condition is given all rows are changed so BE CAREFUL – Values are constants or can be computed from columns

More SQL Data Definition UPDATE Student ID 1 2 3 4 Name John Mark Anne Mary Year 1 3 2 2 Student ID 1 2 3 4 Name John Mark Anne Mary Year 2 4 3 3 UPDATE Student SET Year = Year + 1 UPDATE Student SET Year = 1, Name = ‘Jane’ WHERE ID = 4 Student ID 1 2 3 4 Name John Mark Anne Jane Year 1 3 2 1

More SQL Data Definition DELETE • Removes all rows which satisfy the condition DELETE FROM <table> [WHERE <condition>] – If no condition is given then ALL rows are deleted - BE CAREFUL – Some versions of SQL also have TRUNCATE TABLE <T> which is like DELETE FROM <T>

More SQL Data Definition DELETE Student ID 1 2 3 4 Name John Mark Anne Mary Year 1 3 2 2 Student ID Name Year DELETE FROM Student or TRUNCATE TABLE Student DELETE FROM Student WHERE Year = 2 Student ID 1 2 Name John Mark Year 1 3

More SQL Data Definition Being Careful • When using DELETE and UPDATE – You need to be careful to have the right WHERE clause – You can check it by running a SELECT statement with the same WHERE clause first Before running DELETE FROM Student WHERE Year = 3 run SELECT * FROM Student WHERE Year = 3

More SQL Data Definition SQL SELECT Overview SELECT <column-list> FROM <table-names> [WHERE <condition>] [GROUP BY <column-list>] [HAVING <condition>] [ORDER BY <column-list>] • ([]- optional, | - or)

More SQL Data Definition Simple SELECT SELECT <columns> FROM <table> <columns> can be – A single column – A comma-separated list of columns – * for ‘all columns’ • Given a table Student with columns – stuID – stuName – stuAddress – stuYear

Selecting all columns 11 SELECT * FROM departments;

Selecting specific columns 12 SELECT department_id, location_id FROM departments;

Arithmetic Expressions • Create expressions with number and date data by using arithmetic operators. 13 Operator + - * / Description Add Subtract Multiply Divide

Using arithmetic operators 14 SELECT last_name, salary, salary + 300 FROM employees; …

Operators precedence • Multiplication and division take priority over addition and subtraction. • Operators of the same priority are evaluated from left to right. • Parentheses are used to force prioritized evaluation and to clarify statements. 15 * / + _

Operator precedence 16 SELECT last_name, salary, 12*salary+100 FROM employees; …

Using parenthesis 17 … SELECT last_name, salary, 12*(salary+100) FROM employees;

Defining a NULL value • A null is a value that is unavailable, unassigned, unknown, or inapplicable. • A null is not the same as zero or a blank space. 18 SELECT last_name, job_id, salary, commission_pct FROM employees; … …

NULL values in Arithmetic Expressions • Arithmetic expressions containing a null value evaluate to null. 19 SELECT last_name, 12*salary*commission_pct FROM employees; … …

Defining a column alias • A column alias: – Renames a column heading – Is useful with calculations – Immediately follows the column name - there can also be the optional AS keyword between the column name and alias – Requires double quotation marks if it contains spaces or special characters or is case sensitive 20

Using column aliases 21 SELECT last_name "Name", salary*12 "Annual Salary" FROM employees; SELECT last_name AS name, commission_pct comm FROM employees; … …

Concatenation operator • A concatenation operator: – Concatenates columns or character strings to other columns – Is represented by two vertical bars (||) – Creates a resultant column that is a character expression 22

Using concatenation operator 23 SELECT last_name||job_id AS "Employees" FROM employees; …

Literal character strings • A literal is a character, a number, or a date included in the SELECT list. • Date and character literal values must be enclosed within single quotation marks. • Each character string is output once for each row returned. 24

Using literal character strings 25 … SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

Duplicate rows • The default display of queries is all rows, including duplicate rows. 26 SELECT department_id FROM employees; …

Eliminating duplicate rows • Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause. 27 SELECT DISTINCT department_id FROM employees;

The CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees; Using the CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: … …

DML using oracle

More Related Content

What's hot

Similar to DML using oracle

Recently uploaded

DML using oracle

Editor's Notes