9 1 Structured Query Language SQL ATS Application

9. 1 Structured Query Language (SQL) ATS Application Programming: Java Programming © Accenture 2005 All Rights Reserved Course Code #Z 16325

Objectives After completing this section, you should be able to: • Understand Structured Query Language (SQL) and its purpose • Create and execute a basic SELECT statement • Create and execute a basic INSERT statement • Create and execute a basic UPDATE statement • Create and execute a basic DELETE statement • Execute COMMIT and ROLLBACK on transaction © Accenture 2005 All Rights Reserved 2

What is SQL? SQL (Structured Query Language) is a standard interactive programming language for getting information from and updating to a database. Statements take the form of a command language that lets you: • • SELECT data INSERT data UPDATE data DELETE data © Accenture 2005 All Rights Reserved 3

Guidelines in writing SQL Statements • • SQL statements are not case-sensitive SQL statements can be on one or more lines SQL statements are optionally ended with “; ” Keywords cannot be abbreviated or split across lines Clauses are usually placed on separate lines Indents are used to enhance readability Keywords are typically entered in uppercase; all other words such as table name and columns are entered in lower case © Accenture 2005 All Rights Reserved 4

Basic SELECT Statement Use the SELECT statement to retrieve data from one or more tables: SELECT <column(s) > FROM <table> [WHERE <condition>] [ORDER BY <column(s) [ASC|DESC]>] table is the name of the table column is the name of the column in the table to be selected condition identifies the rows to be selected and is composed of column names, expressions, constraints, sub-queries and comparison operators column (order by) is the name of the column(s) used for sorting © Accenture 2005 All Rights Reserved 5

Choosing Columns • To choose all the columns of the table for display, you can use the asterisk (*) SELECT * FROM TABLE_A • To choose specific columns on the table for display, you specify each column separated by a comma (, ) SELECT COLUMN_1, COLUMN_2 FROM TABLE_A • It is best to put each column chosen in a separate line © Accenture 2005 All Rights Reserved 6

Limiting Rows • The method of restriction is the basis of the WHERE clause in SQL • Character strings and dates in the WHERE clause must be enclosed in Single Quotation Marks (‘) • Numeric Values do not need the Single Quotation marks(‘) © Accenture 2005 All Rights Reserved 7

Rows may be limited by: • EQUALS CONDITION • Display rows based on an exact match of values. SELECT last_name, salary FROM employee WHERE salary = 30000 SELECT employee_id, last_name FROM employee WHERE manager_name = ‘RAYMOND’ © Accenture 2005 All Rights Reserved 8

Rows may be limited by: • >, < or <> CONDITION SELECT last_name FROM employee WHERE salary > 30000 SELECT employee_id FROM employee WHERE manager_name <= ‘RAYMOND’ SELECT employee_id FROM employee WHERE status <> ‘ACTIVE’ © Accenture 2005 All Rights Reserved 9

Rows may be limited by: • BETWEEN CONDITION • Display rows based on a range of values SELECT last_name FROM employee WHERE salary BETWEEN 30000 AND 50000 • IN CONDITION • Display rows based on a list of values SELECT employee_id FROM employee WHERE manager_id IN (100, 200, 300) © Accenture 2005 All Rights Reserved 10

Rows may be limited by: • LIKE CONDITION • Perform wildcard searches of valid search string values • Can contain either literal characters or numbers • % denotes zero or many characters • _ denotes one character • Use ESCAPE identifier to search for the actual % and _symbols. Identifies the backslash() as the escape character SELECT last_name FROM employee WHERE last_name LIKE ‘%a’ © Accenture 2005 All Rights Reserved 11

Rows may be limited by: • LOGICAL CONDITION • AND, OR, NOT SELECT last_name, job_id FROM employee WHERE job_id NOT IN ('SSE', 'TL') SELECT last_name, job_id FROM employee WHERE salary NOT between 10000 AND 15000 © Accenture 2005 All Rights Reserved 12

Rows may be limited by: • LOGICAL CONDITION • AND, OR, NOT SELECT last_name, job_id FROM employee WHERE last_name NOT LIKE 'A%' AND last_name NOT LIKE 'B%‘ SELECT last_name, job_id FROM employee WHERE commission_pct IS NOT NULL © Accenture 2005 All Rights Reserved 13

Sorting Rows • ORDER BY clause • ASC specifies an ascending order • DESC specifies a descending order SELECT last_name, salary, job_id FROM employee ORDER BY salary DESC, job_id ASC • Display the result in descending order by the attribute salary. If two records have the same attribute value, the salary sorting criteria is in ascending order according to the attribute values of job_id © Accenture 2005 All Rights Reserved 14

Basic INSERT Statement INSERT INTO <table> [ (column [, column…] ) ] VALUES (value [, value…] ) table column value is the name of the table is the name of the column in the table to populate is the corresponding value for the column Note: This statement with the VALUES clause adds only one row at a time to a table. © Accenture 2005 All Rights Reserved 15

Basic INSERT Statement • • Insert a new row containing values for each column List values in the default order of the columns in the table Option: list the columns in the INSERT clause Enclose character and date values within single quotation marks INSERT INTO departments ( department_id, department_name, current_date) VALUES ( 70, ‘Public Relations’, ’ 10 -OCT-04’) © Accenture 2005 All Rights Reserved 16

Inserting Rows from Another Table • Write your INSERT statement with a subquery • Do not use the VALUES clause • Match the number of columns in the INSERT clause to those in the subquery INSERT INTO SELECT FROM WHERE © Accenture 2005 All Rights Reserved sales_reps ( id, name, salary) employee_id, last_name, salary employees job_id LIKE ‘%REP%’ 17

Basic UPDATE Statement UPDATE SET table column = value [, column = value, …] [WHERE condition] table is the name of the table column name of the column in the table to populate value corresponding value for the column condition identifies the rows to be updated and is composed of column names, expressions, constraints, sub-queries, and comparison operators © Accenture 2005 All Rights Reserved 18

Updating Rows in a Table • Specific row or rows are modified if you specify the WHERE clause UPDATE employees SET department_id = 70 WHERE employee_id = 113 • All rows in the table are modified if you omit the WHERE clause © Accenture 2005 All Rights Reserved 19

Updating Rows Based on Another Table • Use subqueries in UPDATE statements to update rows in a table based on values from another table UPDATE SET copy_emp department_id = WHERE job_id © Accenture 2005 All Rights Reserved = (SELECT department_id FROM employees WHERE emp_id =100) (SELECT job_id FROM employees WHERE emp_id = 200) 20

Basic DELETE Statement DELETE [WHERE [FROM] table condition] ; table is the name of the table condition identifies the rows to be updated and is composed of column names, expressions, constraints, sub-queries, and comparison operators © Accenture 2005 All Rights Reserved 21

Deleting Rows in a Table • A specific row or specific rows are deleted if you specify the WHERE clause DELETE FROM employees WHERE employee_id = 113 ; • All rows in the table are deleted if you omit the WHERE clause © Accenture 2005 All Rights Reserved 22

Deleting Rows Based on Another Table • Use subqueries in DELETE statements to delete rows in a table based on values from another table DELETE FROM employees WHERE department_id = (SELECT FROM WHERE © Accenture 2005 All Rights Reserved department_id departments dept_type = ‘CST’) 23

What is a Transaction? • A transaction usually means a sequence of information exchange and related work (such as database updating) that is treated as a unit for the purposes of satisfying a request and for ensuring database integrity • For a transaction to be completed and database changes to be made permanent, a transaction has to be completed in its entirety. A program that manages or oversees the sequence of events that are part of a transaction is sometimes called a transaction monitor • Transactions are supported by SQL. When a transaction completes successfully, database changes are said to be committed; when a transaction does not complete, changes are rolled back © Accenture 2005 All Rights Reserved 24

Transaction Control • Transaction Control Statements • COMMIT • ROLLBACK © Accenture 2005 All Rights Reserved 25

COMMIT • Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction • If you do not explicitly commit the transaction and the program terminates abnormally, then the last uncommitted transaction is automatically rolled back • SYNTAX: COMMIT; © Accenture 2005 All Rights Reserved 26

ROLLBACK • Use the ROLLBACK statement to undo work done in the current transaction, or to manually undo the work done by an in-doubt distributed transaction • SYNTAX: ROLLBACK; © Accenture 2005 All Rights Reserved 27

Key Points • SQL is an industry standard language for updating, and getting information from, a database • The basic and most common SQL statements are: SELECT, INSERT, UPDATE, DELETE • Transaction management is implemented in SQL using COMMIT and ROLLBACK © Accenture 2005 All Rights Reserved 28

Comments & Questions © Accenture 2005 All Rights Reserved 29