1. What is SQL?
1. SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving
data from the Database.
2. It is database understandable language.
3. It act as interface between user and database.
4. It is common for any RDBMS.
5. It is permission oriented language.
6. It is not a oracle tool. Sql * plus is an oracle tool.
7. Sql * plus is a collection of all sql commands and plus commands.
2. Oracle Datatypes ?
a. Datatype is nothing but a type of Data.
b. Each column value in a SQL statement has a datatype, which is associated with a specific storage format.
c. When you create a table, you must specify a datatype for each of its columns.
d. It is used for to allocate memory for given data.
Datatype Catagerious
1. Character Datatype
(i) Char (ii) Varchar (iii) Varchar2
2. Numeric Datatype
3. DATE Datatype
4. LOB (Large Object)
i. Character Datatype
Used to store alpha numeric values.* It is used when Fixed Length string is required.
* When you create a table with a Char column, you must specify a string length between 1 and 2000 bytes.
* The default is 1 byte.
* If a user Enters a value larger than the specified length, then the Oracle Database returns an error.
* Drawback is Wastage of memory.
ii. Varchar Datatype
* The VARCHAR data type is actually a subtype of VARCHAR2.* It is same as varchar2 but the maximum size is 2000 bytes.
iii. Varchar2 Datatype
* Used to store alpha numeric values.* Varchar2 saves disks space when compared to char datatypes.
* When you create a table with a Varchar2 column, you can specify a maximum string length between 1 and 4000 bytes.
* If a user is not utilizing the space of the data type as declared while creating, depending on this data it will
occupy that space in disk.
* No default size.
2. Numeric Datatypes
Numbers( ) Used to store both integer and floating point number.Max upto 38 digits.
Number(p) – integer
Number(p,s) – float
Example:
a number(5);
b number(5,3);
3. DATE Datatype
* It is used to store date & time.* Oracle Database uses its own internal format to store dates.
* It allocates 7 fixed bytes of memory.
* The default date format is dd-mon-yy/yyyy
* The default time format is 0(zero) hours eg: 12:00:00 am
* Oracle Database can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 9999 AD.
4. Large Object (LOB)
Used to store large objects and binary objects.Maximum size is 4gb till oracle 9i from 10g max. size is 128 terabytes.
Used to store images,videos etc.
3. SQL Statements ?
1. DDL (Data Definition Language) - Create,alter,drop,rename,turncat
2. DML (Data Manipulation Language)- Insert,update,delete
3. DRL (Data Retrieval Language) - Select
4. DCL (Data Control Language) - Grant,Revoke
5. TCL (Transition Control language) - Commit,rollback,savepoing
4. Types of Constraints ?
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. COMPOSITE PRIMARY KEY
5. DEFAULT
6. CHECK
7. FOREIGN KEY
1. NOT NULL:
Is to Stop the NULL value in a Specific column, Indirectly declaring a column as Mandatory.
2. UNIQUE:
Is to restrict a duplicate value in the column or a duplicate row in the table. But It allows NULL values
3. PRIMARY KEY:
Is the combination of both NOT NULL and UNIQUE.#Only ONE primary key per table allowed.
4. COMPOSITE PRIMARY KEY:
A primary key with more then one Column.
5. DEFAULT:
Default implies that if we do not specify the values in the insert statement, then the value specified
in the default clause will be inserted by default.
6. CHECK:
Is to defined USER defined Validation.
7. FOREINGN KEY (or) REFERENCE INTEGRITY CONSTRAINTS:
Is to define Relationship between the columns of different table. The table which has foreign key is called the CHILD TABLE
and the table that contains Primary key is called the parent column.
5. Views ?
* A view is a logical object of database.
* A view contains no data of its own, but is like a window through which data from tables can be viewed or changed.
* A view can be create on all columns of a table or selected columns from a table.
* A view is to hide the original names of the table.
* Data security.
Types of Views
Simple view :When a View is created on a single table is called a Simple View.
Complex view :
When a View is created on more then one table is called a Complex View.
How to Create and a View
Create ViewCREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
DROP A VIEW
Drop view view_name;
6. Indexes ?
* The INDEX is used to create and retrieve data from the database very quickly.
* An index provides direct and fast access to rows in a table.
* When index is created, it is assigned a ROWID for each row before it sorts out the data.
* They can be created or dropped at any time, and have no effect on the base tables.
* When you drop a table, the corresponding indexes are also dropped.
* If you do not have an index on the column, then a full table scan occurs.
* The users cannot see the indexes, they are just used to speed up searches/queries.
A single-column index is one that is created based on only one table column.
CREATE INDEX index_name ON table_name (column_name);
Composite Indexes:
A composite index is an index on two or more columns of a table.
CREATE INDEX index_name on table_name (column1, column2);
Rename an Index:
ALTER INDEX index_name RENAME TO new_index_name;
Types of Indexes
1. Unique Indexes:A unique index does not allow any duplicate values to be inserted into the table.
2. NonUnique Indexes:
A Nonunique indexes permit duplicates values in the indexed columns.
3. B-tree indexes(balanced tree):
Should be used when a column has large number of duplicate values.
4. Bitmap Indexes:
Should be used when a column has few duplicate values.
7. Sequence ?
* Sequence are database objects from which multiple users can generate numbers in Sequence order.
* The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically.
* A sequence is referenced in SQL statements with the NEXTVAL and CURRVAL. - NEXTVAL generates the new value of the sequence
while CURRVAL displays the current value of the sequence.
* To create a sequence in your schema, you must have the CREATE SEQUENCE system privilege.
* To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE privilege.
* CREATE SEQUENCE [SEQUENCE NAME] [INCREMENT BY n]|[START WITH n]|[{MAXVALUE n | NOMAXVALUE}]|[{MINVALUE n | NOMINVALUE}]|
[{CYCLE | NOCYCLE}]|[{CACHE n | NOCACHE}]|[{ORDER n | NOORDER}];
* INCREMENT BY
It Increment with the given value.
* START WITH
Specify the first sequence number to be generated.
* MAXVALUE
Specify the maximum value the sequence can generate.
* CYCLE | NOCYCLE – This clause adds cyclic behavior to a sequence. The sequence recycles the values once the MAXVALUE is reached.
NOCYCLE is the default behavior.
* CACHE
Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can
have 28 or fewer digits.
* NOCACHE
Specify NOCACHE to indicate that values of the sequence are not preallocated. The database caches 20 sequence numbers by default
* ORDER
Specify Order to guarantee that sequence numbers are generated in order of request.
* NOORDER
Specify Noorder if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
8. Synonyms ?
A synonym is an alternative name (or alias) for an object (like an table or view) in the database.Reasons to use synonyms are security. (for example, to hide the owner of an object)
TYPES OF SYNONYMS
1. PUBLIC
A public synonym is owned by the special user group named PUBLIC and is accessible to every user in a database.
2. PRIVATE
A private synonym is contained in the schema of a specific user and available only to the user and the user's grantees.
EX:CREATE PUBLIC SYNONYM public_emp FOR scott.emp;
CREATE SYNONYM EMPLOYEE FOR EMP;
GRANT SELECT ON EMPLOYEE TO U1;
INSERT INTO emp (empno, ename, job) VALUES (emp_sequence.NEXTVAL, 'SMITH', 'CLERK');
INSERT INTO items(Orderno, Partno, Quantity) VALUES (Order_seq.CURRVAL, 20321, 3);
No comments:
Post a Comment