ONLINE COMPUTER EDUCATION

 Introduction: It is popular programming language which is used on the server to create the web applications. It was created by Guido Van Rossum and was released in the year 1991. It is used for web development i.e. server side. It is used for software development. It is used for mathematics and system scripting. What is the use? It can be used on the server for creating the web applications. Frameworks like Django and flask are used for web development. It can be used with the software for creating workflows. It can connect to the database systems for reading and modifying the files. It is used for handling the big and complex data and perform the complex mathematics operations. It is used for rapid prototyping of the production software development as ready. It is used in desktop applications with GUI frameworks like Tkinter and PyQt. It is used in data science for doing data analysis and libraries like pandas, NumPy and Matplotlib are used. It is used in machine learning and AI ...

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

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