1 / 46

Structured Query Language (SQL)

Structured Query Language (SQL). SQL Statements Versus SQL* Plus Commands. SQL*Plus: DOS, Windows, and browser iSQL*Plus. DOS SQL*Plus Editing Commands. DOS SQL*Plus Editing Commands. Default: DEFINE _EDITOR = "Notepad". SQL Statements. Database. CREATE DATABASE sample

emilia
Download Presentation

Structured Query Language (SQL)

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. Structured Query Language (SQL)

  2. SQL Statements Versus SQL* Plus Commands SQL*Plus: DOS, Windows, and browser iSQL*Plus

  3. DOS SQL*Plus Editing Commands

  4. DOS SQL*Plus Editing Commands Default: DEFINE _EDITOR = "Notepad"

  5. SQL Statements

  6. Database CREATE DATABASE sample CONTROLFILE REUSE LOGFILE GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K, GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K MAXLOGFILES 5 MAXLOGHISTORY 100 MAXDATAFILES 10 MAXINSTANCES 2 ARCHIVELOG CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 DATAFILE 'disk1:df1.dbf' AUTOEXTEND ON, 'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE temp_ts UNDO TABLESPACE undo_ts SET TIME_ZONE= '+02:00';

  7. Database Objects • Schema: • A schema is owned by a database user and has the same name as that user. • Each user owns a single schema. • A schema is a collection of objects (tables, views, sequences, indexes synonyms…)

  8. Database Table • A database object • Stores data for the database • Consists of columns and rows • Created and modified through Data Definition Language (DDL) commands

  9. Table CREATE [GLOBAL TEMPORARY] TABLE [schema.]tablename (columnname datatype [DEFAULT defaultvalue] [PRIMARY KEY][, …]); -GLOBAL TEMPORARY: Data in a temporary table is visible to the session that inserts the data into the table. -schema: the owner’s name (must be prefixed when table does not belong to the user) -Data Definition Language (DDL) statement (create, modify, or remove) -Naming Conventions: -Must begin with a letter -Can be 1-30 characters long -Must contain A-Z, a-z, 0-9, _, $, and # (discouraged) -Must not duplicate the name of another object owned by the same user -Must not be a reserved word -Naming Guidelines: Descriptive and consistent (ex. Same columnname DEPTNO in both tables EMP and DEPT)

  10. CREATE TABLE Command • Column definition list must be enclosed in parentheses • Datatype must be specified for each column • Maximum of 1,000 columns

  11. DESCRIBE Command Displays structure of specified table

  12. Creating Tables SQL> CREATE TABLE dept • (deptno NUMBER(2) • PRIMARY KEY, • dname VARCHAR2(14), • loc VARCHAR2(13) • ); To confirm table creation: DESCRIBE tablename SQL> DESCRIBE dept To view all data: SELECT * FROM tablename; SQL> SELECT * FROM dept;

  13. Tables in the Oracle Database • User Tables: Created and maintained by the user. • Data Dictionary: • Information about the database. • Created and maintained by the Oracle server. • Owned by the user SYS. Data dictionary table USER_CATALOG specifies an object (TABLE, VIEW, SEQUENCE, or SYNONYM).

  14. SQL> desc USER_TAB_COMMENTS Name Null? Type ----------------------------------------- -------- --------------- TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) COMMENTS VARCHAR2(4000) • SQL> desc USER_COL_COMMENTS Name Null? Type ----------------------------------------- -------- ----------------- TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) COMMENTS VARCHAR2(4000) • COMMENT ON { TABLE [schema .]{table|view|materialized view} |COLUMN [schema .]{table.|view.|materialized view.}column |OPERATOR [schema .]operator |INDEXTYPE [schema .]indextype } IS 'text'; • To drop a comment from the database, set it to the empty string ' '.

  15. SQL> DESC user_segments Name Null? Type ----------------------------------------- -------- --------------- SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER BUFFER_POOL VARCHAR2(7) SQL> SELECT segment_name “Table/Index”, bytes 2 FROM user_segments 3 WHERE segment_name = ‘EMP‘ OR segment_name = ‘PK_EMP’; Table/Index BYTES ------------------------------ ---------- EMP 65536 PK_EMP 65536 SQL>

  16. Datatype

  17. Table Creation Through Subqueries • Can use subquery to retrieve data from existing table • Requires use of AS keyword • New column names can be assigned

  18. CREATE TABLE…AS Command

  19. Creating a Table by Using a Subquery • Create a table and insert rows by combining the CREATE TABLE statement and AS subquery option. CREATE TABLE tablename [(columnname1, columnname2…)] AS subquery [NO LOGGING]; -Match the number of sepecified columns to the number of subquery columns if used. Otherwise, column names of the table are the same as the column names in the subquery. -Do not define the datatypes in the new table. -The only constraint that is inherited is the NOT NULL constraint. SQL> CREATE TABLE dept30 • AS • SELECT empno, ename, hiredate • FROM emp • WHERE deptno = 30;

  20. Modifying Existing Tables • Accomplished through ALTER TABLE command • Use ADD clause to add a column • Use MODIFY clause to change a column • Use DROP COLUMN to drop a column

  21. ALTER TABLE • Use the ALTER TABLE statement to: • Add a new column • Modify an existing column • Define a default value for the new column ALTER TABLE tablename ADD (columnname datatype [DEFAULT defaultvalue] [, columnname datatype]…); ALTER TABLE tablename MODIFY (columnname datatype [DEFAULT defaultvalue] [, columnname datatype]…); ALTER TABLE tablename DROP COLUMN columnname; ALTER TABLE tablename SET UNUSED [COLUMN] columnname; Once marked for deletion, a column cannot be restored. ALTER TABLE tablename DROP UNUSED COLUMN;

  22. ALTER TABLE Command Syntax

  23. ALTER TABLE…ADD Command Example

  24. ALTER TABLE…MODIFY Command Example

  25. Modification Guidelines • Column must be as wide as the data it already contains • If a NUMBER column already contains data, size cannot be decreased • Adding or changing default data does not affect existing data

  26. ALTER TABLE…DROP COLUMN Command • Can only reference one column per execution • Deletion is permanent • Cannot delete last remaining column in a table

  27. This is related to Bug 2144889 ORA-00001: UNIQUE CONSTRAINT (SYS.I_COL_USAGE) VIOLATED DURING DROP COLUMN. This is fixed in 9.2.0.1.0. Take a look at Metalink Note: 2144889.8 Support Description for Bug 2144889. Solution Description ----------------------------------- 1) take a backup of the database 2) connect as sysdba (i.e. sqlplus "/ as sysdba") 3) shutdown immediate 4) startup restrict 5) SELECT object_id FROM dba_objects WHERE object_name = ‘TABLENAME'; 5) delete from col_usage$ where obj# = <object_id_for_tablename>; 6) commit; 7) shutdown abort 8) startup 9) alter table <owner>.<tablename> drop column <columnname>; 10) take another backup of the database Explanation -------------------------- This is <bug:2144889> that is fixed in version 9.2. Please see the bug report for further information on this issue. References ------------------------ <bug:2144889> ORA-1: UNIQUE CONTRAINT (SYS.I_COL_USAGE$) VIOLATED DURING DROP COLUMN

  28. ALTER TABLE…SET UNUSED Command • Once marked for deletion, column cannot be restored • Storage space freed at later time

  29. ALTER TABLE…DROP UNUSED Command Frees up storage space from columns previously marked as unused

  30. More Table DDL DROP TABLE tablename [PURGE]; -All data structure in the table is deleted. -Any views and synonyms will remain but are invalid. -Any pending transactions are committed. -All indexes are dropped. -Cannot ROLLBACK this statement (all DDL statements). -Only the creator or user with the DROP ANY TABLE privilege can remove the table. RENAME oldname TO newname; -To change the name of a table, view, sequence, or synonym. TRUNCATE TABLE tablename [REUSE STORAGE |DROP STORAGE]; -To remove all rows and to release the storage space. DELETE FROM tablename; -To remove all rows without releasing the storage space. COMMENT ON TABLE tablename IS stringvalue; -To add a comment of up to 2,000bytes about a column/table/view/snapshot

  31. RENAME Command Used to rename a table – old name no longer valid

  32. Truncating a Table – TRUNCATE Command Rows are deleted - structure of table remains

  33. DROP TABLE Command Table structure and contents are deleted

  34. DROP TABLE Without Purge Option • Oracle 10g introduced a recycle bin • Dropped tables can be recovered from the recycle bin

  35. FLASHBACK Command • The FLASHBACK command recovers a table from the recycle bin

  36. Use PURGE to Remove a Table from the Recycle Bin

  37. PURGE Option Available for DROP TABLE Command • Using the PURGE option will permanently remove a table from the database • The table will not be copied into the recycle bin

  38. Data Manipulation Language (DML) INSERT INTO tablename [(column [, column…])] VALUES (value [, value…]); UPDATE tablename SET columnname = value [, column = value,…] WHERE condition; DELETE [FROM] tablename WHERE condition;

  39. Assignment# 1 • Create the database system. • Turn in a spool-file with details by using DESCRIBE and SELECT * on every table.

  40. The department store has many sales representatives. • A sales representative may have many customers. • A customer may have many orders • An order may have many parts.

  41. Create tables and insert data so you will have the following:SQL> DESC SALES_REP Name Null? Type ------------------------------- -------- ---- SLSREP_NUMBER VARCHAR2(2) LAST VARCHAR2(10) FIRST VARCHAR2(8) STREET VARCHAR2(15) CITY VARCHAR2(15) STATE VARCHAR2(2) ZIP_CODE VARCHAR2(5) TOTAL_COMMISSION NUMBER(7,2) COMMISSION_RATE NUMBER(3,2)SQL> SELECT * FROM SALES_REP 2 ;SL LAST FIRST STREET CITY ST ZIP_C TOTAL_COMMISSION COMMISSION_RATE -- --------------- --------------- --------------- -- ----- ---------------- --------------- 03 JONES MARY 123 MAIN GRANT MI 49219 2150 .05 06 SMITH WILLIAM 102 RAYMOND ADA MI 49441 4912.5 .07 12 DIAZ MIGUEL 419 HARPER LANSING MI 49224 2150 .05

  42. SQL> DESC CUSTOMER Name Null? Type ------------------------------- -------- ---- CUSTOMER_NUMBER VARCHAR2(3) LAST VARCHAR2(10) FIRST VARCHAR2(8) STREET VARCHAR2(15) CITY VARCHAR2(15) STATE VARCHAR2(2) ZIP_CODE VARCHAR2(5) BALANCE NUMBER(7,2) CREDIT_LIMIT NUMBER(6,2) SLSREP_NUMBER VARCHAR2(2)SQL> SELECT * FROM CUSTOMER;CUS LAST FIRST STREET CITY ST ZIP_C BALANCE CREDIT_LIMIT SL --- ---------- -------- --------------- --------------- -- ----- --------- ------------ -- 124 ADAMS SALLY 481 OAK LANSING MI 49224 818.75 1000 03 256 SAMUELS ANN 215 PETE GRANT MI 49219 21.5 1500 06 311 CHARLES DON 48 COLLEGE IRA MI 49034 825.75 1000 12 315 DANIELS TOM 914 CHERRY KENT MI 48391 770.75 750 06 405 WILLIAMS AL 519 WATSON GRANT MI 49219 402.75 1500 12 412 ADAMS SALLY 16 ELM LANSING MI 49224 1817.5 2000 03 522 NELSON MARY 108 PINE ADA MI 49441 98.75 1500 12 567 DINH TRAN 808 RIDGE HARPER MI 48421 402.4 750 06 587 GALVEZ MARA 512 PINE ADA MI 49441 114.6 1000 06 622 MARTIN DAN 419 CHIP GRANT MI 49219 1045.75 1000 03 10 rows selected.

  43. SQL> DESC ORDERS Name Null? Type ------------------------------- -------- ---- ORDER_NUMBER VARCHAR2(5) ORDER_DATE DATE CUSTOMER_NUMBER VARCHAR2(3)SQL> SELECT * FROM ORDERS;ORDER ORDER_DAT CUS ----- --------- --- 12489 02-SEP-02 124 12491 02-SEP-02 311 12494 04-SEP-02 315 12495 04-SEP-02 256 12498 05-SEP-02 522 12500 05-SEP-02 124 12504 05-SEP-02 522 7 rows selected.

  44. SQL> DESC ORDER_LINE Name Null? Type ------------------------------- -------- ---- ORDER_NUMBER VARCHAR2(5) PART_NUMBER VARCHAR2(4) NUMBER_ORDERED NUMBER(3) QUOTED_PRICE NUMBER(6,2)SQL> SELECT * FROM ORDER_LINE;ORDER PART NUMBER_ORDERED QUOTED_PRICE ----- ---- -------------- ------------ 12489 AX12 11 21.95 12491 BT04 1 149.99 12491 BZ66 1 399.99 12494 CB03 4 279.99 12495 CX11 2 22.95 12498 AZ52 2 12.95 12498 BA74 4 24.95 12500 BT04 1 149.99 12504 CZ81 2 325.99 9 rows selected.

  45. SQL> DESC PART Name Null? Type ------------------------------- -------- ---- PART_NUMBER VARCHAR2(4) PART_DESCRIPTION VARCHAR2(12) UNITS_ON_HAND NUMBER(4) ITEM_CLASS VARCHAR2(2) WAREHOUSE_NUMBER VARCHAR2(1) UNIT_PRICE NUMBER(6,2)SQL> SELECT * FROM PART;PART PART_DESCRIPTION UNITS_ON_HAND IT W UNIT_PRICE ---- -------------------- ------------- -- - ---------- AX12 IRON 104 HW 3 24.95 AZ52 DARTBOARD 20 SG 2 12.95 BA74 BASKETBALL 40 SG 1 29.95 BH22 CORNPOPPER 95 HW 3 24.95 BT04 GAS GRILL 11 AP 2 149.99 BZ66 WASHER 52 AP 3 399.99 CA14 GRIDDLE 78 HW 3 39.99 CB03 BIKE 44 SG 1 299.99 CX11 BLENDER 112 HW 3 22.95 CZ81 TREADMILL 68 SG 2 349.95 10 rows selected.

  46. Add three more records in each table with “matching” values.

More Related