1.What is Database :
A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content
2.What is a table :
Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees , and a set of columns.
basic syntax to create table :
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
column_n datatype [ NULL | NOT NULL ]
);
Create table Murthy (sno number,name varchar2);
3.What is view :
A view is a named and validated SQL query which is stored in the Oracle data dictionary. Views do not contain any data - it is just a stored query in the database that can be executed when called. One can think of a view as a virtual table or mapping of data from one or more tablesSyntax to create a view :
CREATE VIEW emp_view AS SELECT last_name, salary*12 annual_salary FROM employees WHERE department_id = 20;
4.What is join and types of joins :
- INNER JOIN: Returns all rows when there is at least one match in BOTH tables
- LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
- FULL JOIN: Return all rows when there is a match in ONE of the tables
LEFT JOIN is same as LEFT OUTER JOIN
RIGHT JOIN is same as RIGHT OUTER JOIN
5. Oracle Constraints :
Not null
Unique Constraint
Primary Key
Foreign key
Check constraint
A NOT NULL constraint prohibits a database value from being null.
A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
A foreign key constraint requires values in one table to match values in another table.
A check constraint requires a value in the database to comply with a specified condition.
A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.
6.What is the difference between primary key and unique key
| Unique Key | Primary Key |
|---|---|
| A table can contain more than one unique key | A table can contain only one primary key (can be one/more columns) |
| Unique key allows one null value | Primary key will not allow null values |
7.What are DDL and DML commands .
| DDL (DataDifinition Language) | DML (Data Manipulation Language) |
|---|---|
| Create | Select |
| Alter | Insert |
| Truncate | Update |
| Drop | Deletet |
8. What is the difference between “delete” , “truncate” and “drop” commands
| Delete Command | Truncate Command | Drop Command |
|---|---|---|
| Delete Command Belongs to DML | Truncate Command Belongs to DDL | Drop Command Belongs to DDL |
| Can be Used to delete entire Table data | Can be Used to delete entire Table data | Can be Used to delete entire Table data and also the structure |
| Can be used to delete specific rows using where clause | Can't be used to delete specific rows using where clause | Can't be used to delete specific rows using where clause |
| Can be rolled back | Can't be rolled back | Can't be rolled back |
9. Relation between views and tables
Tables are the actual database entities that hold your rows.
Views are "imaginary" tables that are constructed based on the actual tables.
A table contains data, a view is just a SELECT statement which has been saved in the database
The advantage of a view is that it can join data from several tables thus creating a new view of it. Say you have a database with salaries and you need to do some complex statistical queries on it.
Instead of sending the complex query to the database all the time, you can save the query as a view and then SELECT * FROM view
The advantage of a view is that it can join data from several tables thus creating a new view of it. Say you have a database with salaries and you need to do some complex statistical queries on it.
Instead of sending the complex query to the database all the time, you can save the query as a view and then SELECT * FROM view
Syntax :
CREATE VIEW view_name AS SELECT columns
FROM tables WHERE conditions;
10. What are function,trigger and stored procedures :
A stored procedure is a user defined piece of code written in the
local version of PL/SQL, which may return a value that is invoked by calling it explicitly.
A trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).
A function is a user defined piece of code and it should return a value making it a function.
Syntax :
Procedure :
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Procedure :
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Distinct is used to display unique values
Ex:
Select distinct from
Select distinct ename from emp
12. What is the difference between union and union all
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
