(colum name data type, colum data type.....); Ex. Create table emp_mast (emp_code varchar2(4), emp_name varchar2(30), emp_dob date);
View- A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. The reasons why views are created are:
- When data Security is required
- When data redundancy is to be kept to the minimum while maintaning data security.
Syntx - CREATE view AS SELECT
FROM ;
ex. Create view emp_view AS select (emp_name) from emp_mast;
CONSTRAINTS ----- Oracle constraints ar provied flexibility and intergrity of our database. There ar a number of different kinds of constraints in oracle and they are.
- CHEACK
- NOT NULL
- PRIMARY KEY
- UNIQUE
- FOREIGN KEY
PRIMARY KEY-----Primary key is one or more columns in a table used to uniquely indentify eah row in the table.
Syntx: ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
Ex. Alter table emp_mast add constraint pk_emp_code primary key (emp_code);
FORIGN KEY ----- A foreign key means that values in one table must also appear in another table. The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.
Syntx -: ALTER TABLE table_nameadd CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);
Ex. Alter table emp_mast add constraint fk_dept_code foreign key(dept_code) reference dept_mast(dept_code);
here emp_mast is child table and dept_mast is parent table.
day 3 Subject - join
A join is a query that combines rows from two or more tables. We read more types of join and they are .
cartesian join
Outer join
self join
Cartesian join -: A cartesian join is a join of every row of one table to every row of another table.
ex. select emp_code, f_name , dept_name from emp_mast, dept_mast;
Outer join -: An outer join is similar to the equi join, but Oracle will also return non matched rows from the table with the outer join operator (+). Missing values are filled with null values.
ex.
self join -: Self joinA self join is a join of a table to itself.
Day 4 subject:- Function-Group by function
Group functions return a single result based on many rows,. Group data using GROUP BY clause.The different group functions are:
AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
HAVING clause
To limit the returned rows after the grouping, we use the HAVING clause. The HAVING clause is applied after the grouping has taken place.
day 5 subject:- subqueryA subquery is a query within a query. We use subquery with where clWHERE clause, FROM clause, or the SELECT clause.
Day 6 subject:- A character function is a function that takes one or more character values as parameters and returns either a character value or a number value. we read more character function and they are-
CONCAT, INITCAP, LOWER, LPAD, LTRIM, REPLACE, RPAD
RTRIM, SUBSTR, TRANSLATE, TRIM, UPPER.
Day 7 subject : union & union all - we can combine multiple queries using the set operators UNION, UNION ALL.
Day 8 subject : view - we learn creating view and what is use of view.A view is a simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.
Day 9 subject :inline view - today we read inline view. use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.
Day 10 subject : number functio- a number function is accept numeric input and return numeric values.Some number function are:
round, mod, power.
Day 11 subject : date function- a date function is use for find current date, time, last date, Calculates the number of months between two dates.etc. some date function are:
ADD_MONTHS, LAST_DAY,MONTHS_ BETWEEN, NEW_TIMENEXT_DAY, ROUND, SYSDATE, TRUNC .
