Showing Table Information

set head off echo off pages 999 long 90000

select dbms_metadata.get_ddl(‘TABLE’,’PROGLANG_TBL’,’AI_DEMO’) from dual;

Modifying Tables

After a table has been created, you can still modify its structure using the ALTER TABLE command.

What we mean by modify is that you can change field types, sizes, even add or delete columns.

desc proglang_tbl;

ALTER TABLE proglang_tbl modify author varchar(35);

desc proglang_tbl;

Creating New Tables from Existing Tables

INSERT INTO proglang_tbluk (id, language, author, year, standard) VALUES

(1, ‘Prolog’, ‘Colmerauer’, ‘1972’, ‘ISO’);

INSERT INTO proglang_tbluk (id, language, author, year) VALUES

(2, ‘Perl’, ‘Wall’, ‘1987’);

INSERT INTO proglang_tbluk (id, year, standard, language, author) VALUES

(3, ‘1964’, ‘ANSI’, ‘APL’, ‘Iverson’);

CREATE TABLE proglang_tbl AS SELECT * FROM proglang_tbluk;

SELECT * FROM proglang_tbl;

Dropping Tables

DROP TABLE proglang_tbl;

DROP TABLE proglang_tblcopy;

DROP TABLE proglang_tbltmp;

Differences Between a Primary Key and a Unique Key

Two constraints discussed above are similar in their purpose.

However, there are a couple of differences between them.

  1. A primary key field cannot take on a NULL value, whereas a field with a unique Constraint can. However, there can be only one such record since each value must be unique due to the very definition of the constraint.
  2. We are allowed to define only one primary key constraint but we can apply the unique constraint to as many fields as we like.

Unique Key Constraint

A unique key like a primary key is also used to make each record in a table unique.

This would ensure none of the records would duplicate information about the same programming language.

CREATE TABLE proglang_tbluk (

id NUMBER NOT NULL PRIMARY KEY,

language VARCHAR(20) NOT NULL UNIQUE,

author VARCHAR(25) NOT NULL,

year INTEGER NOT NULL,

standard VARCHAR(10) NULL);

Primary Key Constraint

A primary key is used to make each record unique and not null.

CREATE TABLE proglang_tbltmp (

id INTEGER NOT NULL PRIMARY KEY,

language VARCHAR(20) NOT NULL,

author VARCHAR(25) NOT NULL,

year INTEGER NOT NULL,

standard VARCHAR(10) NULL);

Selective Field Inserts

INSERT INTO proglang_tblcopy (id, language, author, year, standard) VALUES

(1, ‘Prolog’, ‘Colmerauer’, ‘1972’, ‘ISO’);

INSERT INTO proglang_tblcopy (id, language, author, year) VALUES

(2, ‘Perl’, ‘Wall’, ‘1987’);

INSERT INTO proglang_tblcopy (id, year, standard, language, author) VALUES

(3, ‘1964’, ‘ANSI’, ‘APL’, ‘Iverson’);

select * from proglang_tblcopy;

Constraints

Constraint is a rule that is applied while doing SQL operations. They are useful to make the data in the database more meaningful and structured.

Example constraints:  NULL, NOT NULL, Primary key, foreign key, unique.

CREATE TABLE proglang_tblcopy (

id INTEGER NOT NULL,

language VARCHAR(20) NOT NULL,

author VARCHAR(25) NOT NULL,

year INTEGER NOT NULL,

standard VARCHAR(10) NULL);

Writing the first Query

select * from proglang_tbl;

Inserting Data into a Table

INSERT INTO proglang_tbl VALUES (1, ‘Fortran’, ‘Backus’, 1955);

INSERT INTO proglang_tbl VALUES (2, ‘Lisp’, ‘McCarthy’, 1958);

INSERT INTO proglang_tbl VALUES (3, ‘Cobol’, ‘Hopper’, 1959);

Table Creation

CREATE TABLE AI_DEMO.proglang_tbl (

id number,

language VARCHAR(20),

author VARCHAR(25),

year number);

desc AI_DEMO.proglang_tbl

Create a user/schema

What is the difference between user and schema?

User is a end user.
We do not give space to store objects.
It has not a lot of privileges (select only).
Password expires periodically.

Schema is an application owner.
Has space to store objects like table, index etc.
Has Privileges. Password will never expire.

CREATE USER AI_DEMO

IDENTIFIED BY “ajhDRks#83654”

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE SERVICE_ACCOUNT

ACCOUNT UNLOCK;

— 1 Role for AI_DEMO

GRANT RESOURCE TO AI_DEMO;

ALTER USER AI_DEMO DEFAULT ROLE ALL;

— 3 System Privileges for AI_DEMO

GRANT ALTER SESSION TO AI_DEMO;

GRANT CREATE SESSION TO AI_DEMO;

GRANT UNLIMITED TABLESPACE TO AI_DEMO;

— 1 Tablespace Quota for AI_DEMO

ALTER USER AI_DEMO QUOTA UNLIMITED ON USERS;

Creating the (Oracle) database:

CREATE DATABASE orcl
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 (‘redo01a.log’,’redo01b.log’) SIZE 100M BLOCKSIZE 512,
GROUP 2 (‘redo02a.log’,’redo02b.log’) SIZE 100M BLOCKSIZE 512,
GROUP 3 (‘redo03a.log’,’redo03b.log’) SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1  MAXLOGFILES 16  MAXLOGMEMBERS 3
MAXDATAFILES 1024 CHAR SET AL32UTF8 NATIONAL CHAR SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE ‘system01.dbf’
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE ‘sysaux01.dbf’
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users DATAFILE ‘users01.dbf’
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE ‘temp01.dbf’
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS1 DATAFILE ‘undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
USER_DATA TABLESPACE usertbsDATAFILE ‘usertbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

What is a table?

A table in a relational database is nothing but a collection of data where the column describe the type of data and the row contains the actual data. In the database terminology, we call, column as field and row as record.

SQL Commands Classification

  1. DDL (Data Definition Language): CREATE TABLE, ALTER TABLE, DROP TABLE.
    These commands for creating or modifying the database structure.
  2. DML (Data Manipulation Language): INSERT, UPDATE, DELETE
    These commands for manipulating data in the database.
  3. DQL (Data Query Language: SELECT
    Used for querying or selecting a subset / all of data in the database.
  4. DCL (Data Control Language): GRANT, REVOKE
    Used for controlling access to data in the database, commonly used for granting or revoking user privileges.
  5. TCC (Transaction Control Commands: COMMIT, ROLLBACK
    Used for managing groups of statements as a unit of work.

What is a relational database?

A relation is a mathematical term is roughly equivalent to a table itself.

So a relational database mean, the particular system arranges data in a tabular

fashion.

What is a database?

  • A database is a collection of organized data. It does not have to be a digital format.
    Example: Telephone directory
  • Software which is used to manage a digital database is called Database Management System (DBMS).
  • The most prevalent database organizational model is the Relational Model.
  1. Developed by Dr. E F Codd in his research paper, “A Relational Model of Data for Large Shared Data Banks”.
  2. In this model, data to be stored is organized as rows inside a table with the column headings specifying the corresponding type of data stored.
  3. This is like a spreadsheet where the first row is column headings and the rows store the actual data.

What is SQL?

SQL stands for Structured Query Language and it is de-facto standard for interacting

with relational databases.


 

policy

  • Do not visit this site on your work computer system.
  • Treat one another with respect.
  • Do use your cell phone to access this site.
  • Reach out for help from fellow students when you become stuck on a problem.
  • More to come…