slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Structured Query Language (SQL) PowerPoint Presentation
Download Presentation
Structured Query Language (SQL)

Loading in 2 Seconds...

play fullscreen
1 / 46

Structured Query Language (SQL) - PowerPoint PPT Presentation


  • 192 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Structured Query Language (SQL)' - emilia


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql statements versus sql plus commands
SQL Statements Versus SQL* Plus Commands

SQL*Plus: DOS, Windows, and browser iSQL*Plus

dos sql plus editing commands1
DOS SQL*Plus Editing Commands

Default: DEFINE _EDITOR = "Notepad"

database
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';

database objects
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…)
database table
Database Table
  • A database object
  • Stores data for the database
  • Consists of columns and rows
  • Created and modified through Data Definition Language (DDL) commands
table
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)

create table command
CREATE TABLE Command
  • Column definition list must be enclosed in parentheses
  • Datatype must be specified for each column
  • Maximum of 1,000 columns
describe command
DESCRIBE Command

Displays structure of specified table

creating tables
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;

tables in the oracle database
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).

slide14
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 ' '.
slide15
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>

table creation through subqueries
Table Creation Through Subqueries
  • Can use subquery to retrieve data from existing table
  • Requires use of AS keyword
  • New column names can be assigned
creating a table by using a subquery
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;
modifying existing tables
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
alter table
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;

modification guidelines
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
alter table drop column command
ALTER TABLE…DROP COLUMN Command
  • Can only reference one column per execution
  • Deletion is permanent
  • Cannot delete last remaining column in a table
slide27
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

alter table set unused command
ALTER TABLE…SET UNUSED Command
  • Once marked for deletion, column cannot be restored
  • Storage space freed at later time
alter table drop unused command
ALTER TABLE…DROP UNUSED Command

Frees up storage space from columns previously marked as unused

more table ddl
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

rename command
RENAME Command

Used to rename a table – old name no longer valid

truncating a table truncate command
Truncating a Table – TRUNCATE Command

Rows are deleted - structure of table remains

drop table command
DROP TABLE Command

Table structure and contents are deleted

drop table w ithout purge option
DROP TABLE Without Purge Option
  • Oracle 10g introduced a recycle bin
  • Dropped tables can be recovered from the recycle bin
flashback command
FLASHBACK Command
  • The FLASHBACK command recovers a table from the recycle bin
purge option available for drop table command
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
data manipulation language dml
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;

assignment 1
Assignment# 1
  • Create the database system.
  • Turn in a spool-file with details by using DESCRIBE and SELECT * on every table.
slide40
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.
slide41

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

slide42

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.

slide43

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.

slide44

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.

slide45

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.