ONLINE COMPUTER EDUCATION

 Q1) Write an query to print the salaries of employees above 3000 as high salary or low salary. Ans select ename,sal,case when sal>3000 THEN 'High Salary' ELSE 'Low Salary' END AS salary_category from emp; Q2) Write a query to retreive the last five records from the employee table based on the empno. Ans    select * from emp order by empno desc               fetch first 5 rows only; select * from ( select * from emp order by empno desc ) where rownum<=5; Note: In oracle, limit clause is not used. You can use FETCH first n rows or ROWNUM to achieve the result. Q3) Write a query to fetch employees who earn more than the average salary. Ans select * from emp where sal>(select avg(sal) from emp); Q4) How do you find the second highest salary from the employee table. Ans select max(sal) from emp where sal<(select max(sal) from emp); Q5) How to find all the duplicate records in the employee table taking all the colum...

 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...

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...