SQL Interview Questions. SQL Interview Questions(few questions are repeated with small differences in their answers) What Is SQL? SQL (pronounced as the letters S-Q-L or as sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database. Which command displays the SQL command in the SQL buffer, and then executes it? PL/SQL Tutorial - PL/SQL Stored Procedures. General Syntax to create a procedure is: CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] Declaration section Execution section Exception section IS - marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks.
The syntax within the brackets [ ] indicate they are optional. The below example creates a procedure ‘employer_details’ which gives the details of the employee. 1> CREATE OR REPLACE PROCEDURE employer_details 3> CURSOR emp_cur IS 4> SELECT first_name, last_name, salary FROM emp_tbl; 5> emp_rec emp_cur%rowtype; 7> FOR emp_rec in sales_cur 9> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name 10> || ' ' ||emp_cur.salary); How to execute a Stored Procedure?
There are two ways to execute a procedure. 1) From the SQL prompt. EXECUTE [or EXEC] procedure_name; 2) Within another procedure – simply use the procedure name. procedure_name; NOTE: In the examples given above, we are using backward slash ‘/’ at the end of the program. Difference between Views & Materialized views. Materialized Views: Views vs Materialized Views. Like its predecessor the view, materialized views allow you to store the definition of a query in the database. Unlike views, however, materialized views also store the results of the query in the database. In the following queries note how the rowid's for the table and the view are identical, indicating the view returns the exact same data stored in the table.
The rowids of the materialized view, on the other hand, differ from those of the table. This indicates the materialized view is returning a physically separate copy of the table data. The difference between views and materialized views becomes even more evident than this when table data is updated. Note how, after the update, the view data matches the table data but the materialized view data does not. Now that the materialized view has been refreshed its data matches that of its base table. Cleanup drop materialized view mv ; drop view v ; update t set val = lower(val); commit; SQL Views. Oracle PL/SQL: Create Database Link - A database link is a schema object in one database that enables you to access objects on another dat. CREATE DATABASE LINK. Purpose Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system.
However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Once you have created a database link, you can use it to refer to tables and views on the other database. Prerequisites To create a private database link, you must have the CREATE DATABASE LINK system privilege. Oracle Net must be installed on both the local and remote Oracle databases.
Syntax. PLSQL: Sequences (autonumber) Learn how to create and drop sequences in Oracle with syntax and examples.
Description In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key. Create Sequence You may wish to create a sequence in Oracle to handle an autonumber field. Syntax. PL/SQL FAQ. PL/SQL FAQ - Oracle's Procedural Language extension to SQL: [edit] What is PL/SQL and what is it used for? SQL is a declarative language that allows database programmers to write a SQL declaration and hand it to the database for execution.
As such, SQL cannot be used to execute procedural code with conditional, iterative and sequential statements. To overcome this limitation, PL/SQL was created. PL/SQL is Oracle's Procedural Language extension to SQL. Conditional Control Statements: Iterative Statements: Sequential Control Statements: The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
Example PL/SQL blocks: /* Remember to SET SERVEROUTPUT ON to see the output */ BEGIN DBMS_OUTPUT.PUT_LINE('Hello World'); END; /