Difference between CHAR, VARCHAR AND VARCHAR2
Filed under: MySQL Interview Questions, Oracle Interview Questions, SQL SERVER Interview Questions
CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.
SQL> CREATE TABLE char_test (col1 CHAR(10));
Table created.
SQL> INSERT INTO char_test VALUES (‘qwerty’);
1 row created.
SQL> SELECT col1, length(col1), dump(col1) “ASCII Dump” FROM char_test;
COL1 LENGTH(COL1) ASCII Dump
———- ———— ————————————————————
qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32
VARCHAR2
VARCHAR2 is used to store variable length character strings. The string value’s length will be stored on disk with the value itself.
SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));
Table created.
SQL> INSERT INTO varchar2_test VALUES (‘qwerty’);
1 row created.
SQL> SELECT col1, length(col1), dump(col1) “ASCII Dump” FROM varchar2_test;
COL1 LENGTH(COL1) ASCII Dump
———- ———— ————————————————————
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121
VARCHAR
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
What are the difference between DDL, DML and DCL commands?
Filed under: MySQL Interview Questions, Oracle Certification Exams Interview Questions, Oracle Interview Questions, Relational Database Interview Questions
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
* CREATE – to create objects in the database
* ALTER – alters the structure of the database
* DROP – delete objects from the database
* TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
* COMMENT – add comments to the data dictionary
* RENAME – rename an object
DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
* SELECT – retrieve data from the a database
* INSERT – insert data into a table
* UPDATE – updates existing data within a table
* DELETE – deletes all records from a table, the space for the records remain
* MERGE – UPSERT operation (insert or update)
* CALL – call a PL/SQL or Java subprogram
* EXPLAIN PLAN – explain access path to data
* LOCK TABLE – control concurrency
DCL
Data Control Language (DCL) statements. Some examples:
* GRANT – gives user’s access privileges to database
* REVOKE – withdraw access privileges given with the GRANT command
Alter table sql command
Filed under: MySQL Interview Questions, Oracle Interview Questions
ALTER TABLE suppliers
RENAME TO vendors;
ALTER TABLE table_name
MODIFY column_name column_type;
For example:
ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;
ALTER TABLE supplier
MODIFY ( supplier_name varchar2(100) not null,
city varchar2(75) );
ALTER TABLE employees
DROP COLUMN salary;
ALTER TABLE departments
RENAME COLUMN department_name to dept_name;
Explain Index ? How to create index ?
Filed under: MySQL Interview Questions, Oracle Interview Questions
How to create an INDEX
An index is a performance-tuning method of allowing faster retrieval of records.
An index creates an entry for each value that appears in the indexed columns.
By default, Oracle creates B-tree indexes.
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
For example:
CREATE INDEX supplier_idx
ON supplier (supplier_name);
In this example, we’ve created an index on the supplier table called
supplier_idx. It consists of only one field – the supplier_name field.
We could also create an index with more than one field as in the example below:
CREATE INDEX supplier_idx
ON supplier (supplier_name, city);
You can create function-based indexes in oracle
WHAT ARE FUNCTION BASED INDEX AND HOW TO CREATE THEM
The syntax for creating a function-based index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
For example:
CREATE INDEX supplier_idx
ON supplier (UPPER(supplier_name));
Rename an Index
How to rename or alter an INDEX in ORACLE
The syntax for renaming an index is:
ALTER INDEX index_name
RENAME TO new_index_name;
HOW TO DROP AN INDEX
Drop an Index
The syntax for dropping an index is:
DROP INDEX index_name;
For example:
DROP INDEX supplier_idx;