The flashcards below were created by user
on FreezingBlue Flashcards.
What are the advantages of view?
- Join 2 or more tables and show it as one object
- View data without storing the data into the object
- Restrict the view/visibility of the able
- Restrict access to the table
- Views do not store the data, only view defination occupies space so view does not occupy any physical space in the DB
- Can perform DML on views but the underlying tables are affected
What are disadvantages of view?
- When the underlying table(s) is dropped the view becomes inactive
- As views just represent data in the underlying tables we cannot change the data of a viewing without changing the data of the tables.
Truncate and Delete
- Truncate is faster than Delete. (Why)
- Truncate applies to whole table but Delete can be executed for a filtered set of records
- Data cannot be recoved in Truncate, but we can recover data in Delete. (Why)
- Truncate is DDL and Delete is DML
Truncate and Drop
- Truncate will not delete the structure of the table.
- Drop will delete everything including the structure.
Can you Delete a column in table with data in Oracle?
Yes but not always. (Why)
What is the datatype of null in oracle?
NULL is a marker that represents missing, unknown, or inapplicable data. Null is untyped in SQL, meaning that it is not designated as a NUMBER, CHAR, or any other specific data type. Do not use NULL to represent a value of zero, because they are not equivalent.
So it does not have a datatype as it is untyped?
Maximum level of sub-queries in the WHERE clause of an SQL statement?
Oracle allows up to 255 levels of subqueries in the WHERE clause.
How to kill a session in oracle?
To kill session using SQL command. Just check serial no and sid (system identifier) from v$session view as follows:
select sid,serial# from v$session where machine='GPTWORKGROUP';
alter system kill session '9,171' immediate;
immediate is used for forced kill
Difference between Having Clause and Where Clause?
- Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:
- The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
- The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
- The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
Difference between IN and EXISTS
- IN:Returns true if a specified value matches any value in a subquery or a list
- Exists:Returns true if a subquery contains any rows.