SQL
1. Constraint
Constraint is a rule applied in columns or table. It use to check validity of data, ensure accuracy, and integrity of data. If there is any violation between the constraint and the data action, the action will aborted.
Example: Some fields in data table is not null or has a default value. Table user has column name must be a string not a number, so you constraint for this column with type of name value is a string.
There are four main type of constrains in SQL:
NOT NULL: This constraint ensures that a column cannot have a NULL value.
UNIQUE: This constraint ensures that all values in a column are different.
PRIMARY KEY: A combination of a NOT NULL and UNIQUE constraint. It uniquely identifies each row in a table.
FOREIGN KEY: This constraint ensures that the values in a column are linked to the values in another column.
Why we need to use constraint ?
Constraints can help to ensure the accuracy of your data by preventing invalid values from being entered into your tables
Constraints can help to ensure the reliability of your data by preventing errors from occurring in your database.
Reference:
https://www.mygreatlearning.com/blog/sql-constraints/
2. Primary key and Foreign key
Primary key is a column or field in a table, the value of the primary key is not contains null value. A table can only have one primary key. Primary key can be generated from one or many field of the table.
Foreign key is a column or a combination columns in a table whose values match the values of a Primary key column in another table. Using Foreign key we can link two table together.
A primary index is automatically create for the primary key and ensures that the key is unique.
3. Index
Index is a special lookup tables that the database search engine can use to speed up data retrieval.
Index use tree structure, hash table,... Simple put, index is a pointer to data in a table. Index in a database is similar to an index of table of contents of a book.
For example, if you want to refer all pages in a book that discuss a certain topic, you first refer to the index, which lists all the topics alphabetically along with their page numbers.
4. Unique index with Primary key
Unique index is like a primary key, it not allow duplicate value, it prevent has same value in one column.
Unique index has the same primary key is only one value in table or column.
The different is: Unique index accept null value, primary key has only one but unique index can has multiple.
5. Global Index with Local Index
Global index is a index which has one or many relationship between the partitioned table.
Local index is a index which has one to one relationship between the partitioned table and partitions.
Example: Store A has only Manager A, that is a local index, but the company has Store B and Store C, so the Manager A con manage Store B and Store C, that is a global index.
6. Synonym
Synonym in Oracle is the same alias in SQL, we use to create a alternative name for existing object name. It help us simple to query to the object of database. We can use synonym name to query to object without using original name of object.
Example: in SQL
SELECT name_table AS name_alias
FROM name_table
WHERE [condition]
7. In with Exist
In will return a list with value has match with condition in In.
Exist will return true or false if match with condition in Exist.
Example:
SELECT col_name
FORM table_name
WHERE col_name IN (sub_query)
it will a list if col_name has match with sub_query
SELECT col_name
FORM table_name
WHERE col_name Exist (sub_query)
8. Transaction with session: When a transaction is started ?
9. Autonomous Transaction
Example: Autonomous Transaction like try catch, if transaction in try has error and roll back, the transaction in catch will be work or we can use transaction in catch to in try for save log data.
10. DDL with DML, Truncate with Delete
DDL (Data Definition Language) : DDL use to define database structure, include creating table, deleting table, create index, function, procedure.
- CREATE TABLE : Create new table
- ALTER TABLE: Change structure of table
- DROP TABLE: Delete table out of database.
DML (Data Manipulation Language) : DML use to manipulate data in table, include create, delete, update and query data.
- SELECT: Query to the database
- INSERT: Add data to the table
- DELETE: Delete data out of table
- UPDATE: Update data in table
Truncate is a DDL so the object of truncate is a table, it will delete space saved of data table, so if you want to use the table again you must create a table.
Delete is a DML, so the object of delete is a row data in table, we can use delete to delete one row of table or all raw of table, but it not delete space saved of table, so you can use the table again and no need to create it.
11. View and Materialized View - Fast Refresh - Materialized View Log
View is a SQL command have stored in database with one contact. View can contain all column of table or one raw of the table. One view can create form one or many table it dependent of query SQL to create a View. View is a temporary table.
Materialized View is the same View but it can saved last query to dick ( View is a temporary table, Materialized View is not is save on physical ) and we can refresh Materialized View at the time we want to refresh data have save on disk.
12. Trigger, mutating table error
Trigger is a store procedure with out parameter, Trigger is automatic work when one on three command UPDATE, INSSERT, DELETE have change data of table in database.
If you use trigger, you will know mutating table error . This is a popular error with trigger if we use trigger is not right.
13. Partition
14. Identify column from 12c
The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below. It help us declare a column as identity to have Oracle NoSQL Database automatically assign values to it, where the values are generated from a sequence generator.
GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]
GENERATED ALWAYS
The system always generates a value for the identity column. An exception is raised if the user supplies a value for the identity column.
GENERATED BY DEFAULT
The system generates a value for the identity column only if the user does not supply a value for it. If ON NULL is specified for GENERATED BY DEFAULT, the system will generate a value when the user supplies a NULL value or the value evaluates to a NULL.
15. Global Temporary Table
Used to create a temporary table that lasts until all connections to the working database are closed. Can be used on other users' connections. Local Temporary Table exists only for one session. In case you want to use the temporary table for many different sessions, you need to change it to Global Temporary Table
16. Merge Statement
MERGE is a command used to modify (update/insert/delete) the data of a table based on the results compared with another table. Statements using MERGE are often shorter and more efficient than regular statements for the same condition.
17. Role
Decentralize users in the database systems to security systems.
18. Regular expression
It like Regex in Java or JavaScript, you can use Regular expression like a condition to check or find value you want.
PLSQL
1. Function with Procedure
Function and Procedure are database object that contain set of SQL statements to complete the task.
A store procedure can be reuse many times. So if you have a query SQL and you have use many times, you should write it in store procedure.
The store procedure can return zero, one, or more value, but the function must return a unique value.
Function can have only input parameter, but store procedure can have input and out put parameter.
Function can be called from store procedure, but store can't be called form function.
2. Package: can create package without body? Can procedure not declared in package spec?
Yes, we can create package without body, if you see some package has nothing in side when you import to use it.
example:you can search empty package npm => https://www.npmjs.com/package/empty-npm-package
Yes, procedure can not declared in package spec if you don't want to public. Declare them only in the package body.
3. Definer right, Invoker right
In Definer's Right, procedure executes with the privileges of the owner.(means privileges are bound to access schema in which it created). By Default all Procedure created with Definer's Right.Example:User Sales create a procedure with Definer right to modify the table SALES_ORDER present in SALES Schema, now if user Sales not grant executes privileges for user HR, now user HR can not use access table SALE_ORDER present in SALES Schema.
In Invoker’s Right, Procedure executes with the privileges of the Current User. (means privileges are bound to current user only)
Example
User Sales create a procedure to modify the table SALES_ORDER present in SALES Schema. Then user Sales grant execute privileges to user HR.
If procedure created with Invoker’s Right then user HR execute the procedure which access table SALES_ORDER present in HR Schema(means Current Schema/user).
You can understand with simple way, if definer right you use schema of define user, if invoker right you use schema of yourself.
4. How to implement the conditional control in PLSQL (IF ELSE, CASE WHEN, GOTO...)
if else
IF condition THEN
statements
ELSE
statements_else
End IF;
case when
CASE selector
WHEN selector_value_1 THEN statement_1
WHEN selector_value_2 THEN statement_2
WHEN selector_value_3 THEN statement_3
END CASE;
goto
GOTO some_where
<<some_where>>
5. How to implement the iterative processing in PLSQL (FOR LOOP, WHILE, LOOP, CONTINUE...)
for loop
FOR index IN value_from..value_to LOOP
statements
END LOOP ;
while loop
WHILE condition_loop LOOP
statement
END LOOP;
CONTINUE help us skip the current iteration and continue with the next iteration.
6. Handle exception
Exception is a condition error during program execution, developer can use EXCEPTION block to catch the error .
Example: Store Procedure has a parameter is a number, but user has input a string, so you must catch this error.
7. How to raise your own error message (RAISE_APPLICATION ERROR)
The raise_application_error is actually a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are defined between -20,000 and -20,999.
8. Collection in oracle
A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.
9. Cursor
Cursor is a pointer to point to any SQL code, these codes are usually commands: SELECT, UPDATE, INSERT, DELETE.
10. Switch context
11. BULK COLLECT, FORALL
BULK COLLECT: These are SELECT statements that retrieve multiple rows with a single fetch retrieval, thereby improving the speed of data.
FORALL: These are INSERT, UPDATE, and DELETE operations that use collections to change multiple rows of data very quickly.
12. VPD concept
Virtual Private Database use to limit access to the important data. Allows you to create security policies to control access at the row and column level.
13. Data pump technique
Data pump is a tool to import and export data in Oracle Database. Data Pump has two script expdp and impdp. Expdp will export data in Oracle Database to a dump file and Impdp will use the dump file to import to the Oracle database.
14. Dynamic SQL
Because database applications usually perform a specific activity, you usually know the complete SQL statement when you write and compile the script. When PowerBuilder does not support the statement in embedded SQL (as with a DDL statement) or when the parameters or the format of the statements are unknown at compile time, the application must build the SQL statements at runtime. This is called dynamic SQL. The parameters used in dynamic SQL statements can change each time the program is executed.
15. Multi-version read consistence
In Oracle Database, multiversioning is the ability to simultaneously materialize multiple versions of data. Oracle Database maintains multiversion read consistency. Queries of an Oracle database have the following characteristics: Read-consistent queries.
16. Statement level and Transaction level
Oracle always enforces statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data nor any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.
Oracle also offers the option of enforcing transaction-level read consistency. When a transaction runs in serializable mode, all data accesses reflect the state of the database as of the time the transaction began. This means that the data seen by all queries within the same transaction is consistent with respect to a single point in time, except that queries made by a serializable transaction do see changes made by the transaction itself. Transaction-level read consistency produces repeatable reads and does not expose a query to phantoms.