If you take to back dropped table, you use flashback statement. The recyclebin must be on to use flashback. Example You use this query to see recyclebin status show parameter recyc; recyclebin –> on After drop table employees; flashback table employees to before drop; Recyclebin Table If you delete a table , This table insert…
Tag: SQL
Data Dictionary in Oracle
The data dictionary is structured in tables and views, but it is also an important tool for all users, from end users to application designers and database administrators. You use SQL statements to access the data dictionary. Because the data dictionary is read-only, you can issue only queries against its tables and views. The data…
ALTER TABLE Statement in Oracle
If you want to change table, you can use alter table. You can add or drop constraint, add or drop column, modify column name or type, modify table name with alter table statement. Add, Drop and Modify Column ALTER TABLE employees ADD (birth_date varchar2(20)); ALTER TABLE employees MODIFY (birth_date date); ALTER TABLE employees MODIFY (birth_date…
Delete row from table which have constraint column
You can use two method for this processing. You can either do disable constraint or use on delete cascade or on delete set null in table. Method 1 Table Name : employees Constraints : primary_key_employee_id , foreign_key_department_id If you want to delete row from employees, you must disable foreign key constraint. ALTER TABLE employees DISABLE…
LOWER,UPPER,INITCAP Function in Oracle
Lower, upper and initcap are case-conversion functions. If you want to change string, you can use this functions. Lower : Converts alpha character values to lowercase. Upper : Converts alpha character values to uppercase. Inıtcap : Converts alpha character values to uppercase for the first letter of each word, all other letters in lowercase. Example…
Nested Subquery in Oracle
Example select department_id, (select first_name from employees where employee_id=10) as “First Name”, (select last_name from employees where employee_id=20) as “Last Name”, (select department_name from departments where department_id = 20) as “Department” from employees where department_id = 20;
Date Functions in Oracle
Oracle default date format DD-MON-RR (14-MAY-11) for English Language Database. DD/MM/YYYY(14/05/2011) for Turkish. You can only subtraction between two dates. The result is day count. ROUND and TRUNC for Date ROUND with ‘MONTH’ => If the day is 16 or more than 16, the date rolls next month and the day makes 1. ROUND with…
Sysdate in Oracle
Sysdate very important date expression in Oracle. It is very useful date format. Sysdate gives current date. Example select sysdate from dual; Sysdate Functions Sysdate + 1 =>1 day add Sysdate – 1 =>1 day decrease Sysdate + 1/24 =>1 hour add Sysdate + 1/1440 =>1 minute add Current Date : 15-JUN-10 select sysdate+2 from…
External value in SQL
If you want to use external value in SQL, you must use && or & expressions. You can use external value in column name, table name or variable. Example select * from employees where employee_id = &employeeid; If you use to double expression (&&), this value makes a cache. You can use this cached value…