ONLINE COMPUTER EDUCATION

 How to create table at runtime in oracle SQL plsql? Under this concept, creating the table at runtime or dynamically with the help of EXECUTE IMMEDIATE in the plsql block. EXECUTE IMMEDIATE statement is used to execute the dynamically constructed SQL statements. Dynamic SQL Create table statement i.e. DDL statements cannot be directly executed in the PLSQL block without the use of dynamic SQL. Dynamic SQL is slower than static SQL, so use it when it is necessary. If you want temporary table, you need to include GLOBAL temporary in the CREATE TABLE statement. Static SQL in the PLSQL will not allow you to use create table inside a block, so dynamic SQL is required. Steps: Construct the create table statement:  Build the SQL statement with the string. It includes the table name, column, datatypes and constraints. Use of EXECUTE IMMEDIATE:  EXECUTE  IMMEDIATE statement is used to execute the dynamically constructed SQL statements. Handle exceptions:  It include the...

What is index? It is a separate database object which is associated with the table. It speeds up data retrieval by providing faster access path to the data. It acts like an index in the book. It allows the database to quickly locate and retrieve the fastest access path to the data. It allows the database to quickly locate and retrieve the rows based on the indexed columns. Key features: It is used for faster retrieval data. It allows the database to find the specific row in the database more quickly. It will return the small portion of the table rows. How to create index? What are the types of index? Explain binary tree index? Explain bitmap index? Explain function-based index? Explain reverse key index? When to choose what type of index?   How to know index is being used? How to monitor index usage? What are the benefits and drawbacks of index?

Analytical functions: It is known as window function. It performs calculations on the set of table rows which are related to the current row. Not like an aggregate function, it will return the single result for the group of rows. It will return a value for each row in the result set. It will return the aggregate results and don't group the result set. It will return the group value multiple times with each record. Group of rows is also called as window which is defined through the help of analytical clause. select * from emp; select deptno,count(*) EmployeeCount from emp group by deptno order by 1; We can find out the number of employees count in the each department. select empno,ename,deptno,count(*) over (partition by deptno order by empno) as employee_count from emp; It will count the number of employees in each department. Within each partition, results are sorted as per empno which is specified in the over clause. select empno,ename,deptno,count(*)employee_count from emp group...

 Definition: It is nothing but the conditions or the restrictions are assigned to each and every column of the database in order to maintain the data integrity. It is used to specify the rules for the data in a table. It is used to limit the type of the data that will go into the table. It ensures the accuracy and reliability of the data in the table. It can be applied at the column level or the table level. Column level constraints are applied at the column level. Table level constraints are applied at the whole table. It will not allow us to enter the information for the entire result. How to create constraint in SQL? It can be created when the table is created with the help of create table statement. It can also be created after the table is created with the help of alter statement. Syntax: Create table table_name( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, ... ... ... column n datatype constraint); Types of constraint: 1) NOT NULL: I...

  INTRODUCTION: It is the join strategy where the one table data is loaded into the hash table for an efficient lookup. Other table data is scanned to match with the hash table which is suitable for the large datasets. Database optimizer uses the two of the smaller tables to build the hash table into the memory. Its function is used to map join key values to the hash table locations. Larger table is also scanned for each row. It's fast and efficient for quick matching of rows which is based on the join condition. It is an effective when one or both of the tables are large and cannot fit in the memory. It is also effective when the join condition is an equally predicate i.e. table1.column=table2.column. It's cost is limited to the single read pass which is over the two data sets. It is situated in the PGA and the rows are accessed without latching. It will reduce I/O by preventing the necessity of repeatedly latching and reading blocks in the database buffer cache. If the data s...

 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...