1 / 18

Software Specification KXA233 Lecture 7A Manipulating and Creating Tables

Software Specification KXA233 Lecture 7A Manipulating and Creating Tables. Paul Crowther School of Computing University of Tasmania. Today. Report Format Commands Manipulating Data Creating Tables. SQL*Plus Format Commands. COLUMN [ column option ] controls column formats

sabine
Download Presentation

Software Specification KXA233 Lecture 7A Manipulating and Creating Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Software SpecificationKXA233Lecture 7AManipulating and Creating Tables Paul Crowther School of Computing University of Tasmania

  2. Today... • Report Format Commands • Manipulating Data • Creating Tables

  3. SQL*Plus Format Commands • COLUMN [column option] • controls column formats • TTITLE [text | OFF | ON] • page header control • BTITLE [text | OFF | ON] • page footer control • BREAK [ON report_element] • suppress duplicate values and section rows of data with line feeds

  4. COL[UMN] [{column | alias} [option]] • CLE[AR] • FOR[MAT] format: changes display of a column using a format mask • HEA[DING] text • JUS[TIFY] {align}: left, right or centre • NOPRI[NT]: hides the column • NUL[L] text : specifies text for NULL values • TRU[NCATED]: truncates string at end of display • WRA[PPED]: wraps string to next line

  5. Examples: • Create column headings: COL ename HEA ‘Employee | Name’ FOR A15 COL sal JUS LEFT FORMAT $99,990.00 COL mgr FOR 999999999 NULL ‘ No manager’ • Display the current setting for the ename column COL ename • Clear settings for ename column COL ename CLEAR

  6. Column Formats • A A4 Fred • 9 999 123 • 0 0999 0123 • $ $9999 $123 • . 999.99 123.00 • , 9,999 1,230 • NOTE formating commands come before the SELECT statement

  7. DML - Data Manipulation Language • A DML statement is executed when you: • Add new rows to a table • Modify existing rows in a table • remove existing rows from a table • A transaction consists of a collection of DML statements that form a logical unit of work • If one operation fails, the other statements must be undone

  8. Adding rows - INSERT INSERT INTO table [(column [, column…])]VALUES (value [, value…]); • Only one row is inserted at a time • If you do not use the column list, the order must be the default order - safer to use column list SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, ‘DEVELOPMENT, ‘PERTH’); 1 row created • NOTE enclose character and date values within single quotation marks

  9. Inserting Rows with NULL Values • Either omit the column from the column list SQL> INSERT INTO dept (deptno, dname )2 VALUES (60, ‘MIS’); • Or specify the NULL keyword SQL> INSERT INTO dept 2 VALUES (70, ‘FINANCE’, NULL); • NOTE default column order used

  10. DATE functions can be INSERTED SQL> INSERT INTO emp (empno, ename, job, mgr, 2 hiredate, sal, comm, deptno)3 VALUES (7169, ‘GREEN’, ‘SALESMAN,4 7782, SYSDATE, 2000, NULL,5 10); • Confirm insertions by : SQL> SELECT *2 FROM emp3 WHERE empno = 7169;

  11. You can also use substitution variables SQL> INSERT INTO dept (deptno, dname, loc)2 VALUES (&department_id,3 &department_name,4 &department_location); Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for department_location: HOBART • ACCEPT can be used to create customised prompts ACCEPT department_id PROMPT ‘Please enter the - department number’

  12. Use UPDATE to Change Data UPDATE tableSET column = value [, column = value, …][WHERE condition] • Can update more than one row at a time SQL> UPDATE emp2 SET deptno = 20 3 WHEWRE empno = 7782; 1 row updated • All rows updated if the WHERE clause omitted SQL> UPDATE emp2 SET deptno = 20; 14 rows updated

  13. Removing Rows - DELETE DELETE [FROM] table[WHERE condition] • Specific rows deleted specified by WHERE SQL> DELETE FROM dept2 WHERE dname = ‘MIS’; 1 row deleted • ALL rows deleted if WHERE omitted SQL> DELETE FROM dept; 14 rows deleted • Dangerous!

  14. Transactions • Consist of: • DML statements that change data • DDL statement • DCL statement • Begin with first executable SQL • End when: • COMMIT or ROLLBACK • DDL or DCL (auto COMMIT) • User exits (auto COMMIT) • System crashes (auto ROLLBACK)

  15. COMMIT and ROLLBACK • COMMIT • Changes made permanent • Previous state permanently lost • SQL> COMMIT; Commit complete • ROLLBACK • Data changes undone • Previous state of data restored • SQL>ROLLBACK;Rollback complete

  16. Creating Tables - CREATE CREATE TABLE [schema.] table (column datatype [DEFAULT expr] [, …]) SQL> CREATE TABLE newdept2 (deptno NUMBER(3), 3 dname VARCHAR2 (14),4 loc VARCHAR2(12),5 mgr NUMBER(6));

  17. How to recreate the basic tables: • In the read directory there is a script called tables • Copy this (you can also look at it) • SQL > RUN tables • This will recreate all your files

  18. Next week... • More on updates • More on creating tables • Schemas and subschemas

More Related