DBMS is defined as the software program that is used to manage all the databases that are stored on the network or system hard disk.
RDBMS solution is required when large amounts of data are to be stored as well as maintained. A relational data model consists of indexes, keys, foreign keys, tables and their relationships with other tables
to find if a word is a palindrome declare i varchar(30) := '&i'; j varchar(30); k varchar(30); begin select i,reverse(i) into j,k from dual; if j=k then dbms_output.put_line('word is a palindrome'); else dbms_output.put_line('word is not a plindrome'); end if; end; /
declare i varchar2(10); begin select decode(mod(&number,2),0,'EVEN','ODD') into i from dual; dbms_output.put_line(i); end;
program to find palindrome
declare j varchar2(30):='&word'; k varchar2(30); begin select reverse(j) into k from dual; if j=k then dbms_output.put_line('The given string is palindrome'); else dbms_output.put_line('The given string is not palindrome'); end if; end;
program to display the names
declare i number:=&i; k employees.first_name%type; j number; cursor c1 is select first_name,length(first_name) from employees; begin open c1; loop fetch c1 into k,j; if j=i then dbms_output.put_line(k); end if; exit when c1%notfound; end loop; close c1; end;
Find Negative value --------------------- declare NegativeError EXCEPTION; i number := &i; j number; begin select sign(i) into j from dual; if j < 0 then RAISE NegativeError; end if; EXCEPTION WHEN NegativeError THEN dbms_output.put_line ('Given Nuber is a negative value'); end; /
Validate Email with @ and . symbol ---------------------------------- declare InvalidEmailError EXCEPTION; email varchar(20) := '&email'; i number; j number; begin select instr(email,'@'), instr(email,'.') into i ,j from dual; if i = 0 or j = 0 then RAISE InvalidEmailError; end if; EXCEPTION WHEN InvalidEmailError THEN dbms_output.put_line ('Email is not valid'); end; /
To display the nth maximum salary ----------------------------------
declare n number := &n; k employee.salary%type; cursor c1 is select salary from employee order by salary desc; begin open c1; for i in 1..n loop fetch c1 into k; exit when C1%notfound; end loop; close c1; dbms_output.put_line (k); end; /
declare i number:='&Employeeid'; neg exception; begin if i<0 then raise neg; end if; insert into A values(i); dbms_output.put_line('ID inserted'); exception when neg then dbms_output.put_line('Invalid negative ID'); end; /
Program to validate emailid
declare i varchar(30):='&mailid'; erro exception; begin if i LIKE '%@%.%' then dbms_output.put_line('valid mail id'); else raise erro; end if; exception when erro then dbms_output.put_line('invalid email id'); end; /
PALINDROME USING PROCEDURE --------------------------
CREATE OR REPLACE PROCEDURE check_Palindrom(name in varchar,o out varchar) IS j varchar(20); BEGIN select reverse(name) into j from dual; if name != j then o:='not palindrome'; else o:='palindrome'; end if; END; /
checking the duplicate user using procedure -------------------------------------------
CREATE OR REPLACE PROCEDURE IsduplicateUser(newusrname in varchar, newpwd in varchar,o out varchar) IS usercnt number; begin select count(*) into usercnt from empuser where upper(username) = upper(newusrname) and upper(password) = upper(newpwd); if usercnt != 0 then o:='duplicate user'; else o:='valid user'; end if; end; /
declare a varchar2(10); i varchar2(10); begin select mod( &a,2) into i from dual; if a=0 then dbms_output.put_line('even'); else dbms_output.put_line('odd'); end if; end; /
SQL Query to find the day of the employee joined in a company algorithm: 1:we can find the day by using data type conversion functions to_char and to_date. in a table,the date are stored in date format.
2.so,we covert the date in a table into char .now,we can find the day of employee joined
QUERY: select first_name,hire_date, to_char(hire_date,'day') hire_day from employees;
Trigger to stop insert from a table ------------------------------------ create or replace trigger my_trig before insert on t1 begin raise_application_error(-20000,'Dont do that'); end;
SQL Query to find the day of the employees joined in a company and to arrange the order of the day starting from sunday to saturday:
select first_name,hire_date, to_char((hire_date),'D DAY') "hire_day" from employees order by "hire_day";
D - Day of the week It also returns a value from 1 to 7 representing the day of the week. For eg:- 1 Sunday 2 Monday 3 Tuesday 4 Wednesday 5 Thursday 6 Friday 7 Saturday
declare type tname is table of number index by binary_integer; i tname; begin i(1):=100; i(2):=200; i(3):=300; i(4):=400; for j in 1..i.count loop dbms_output.put_line(i(j)); end loop; end; /
example for varray
declare type tname is varray(3) of varchar2(30); i tname; begin i:=tname('thina','karan','thinu'); for j in 1..i.count loop dbms_output.put_line(i(j)); end loop; end; /
example for refcursor
create or replace procedure refcur(i in number,o out sys_refcursor) as begin open o for select first_name,salary from employees where department_id=i; end; /
example for pragma autonomous_transaction
declare procedure p1 as pragma autonomous_transaction; begin insert into a values(2); commit; end; begin insert into a values(1); p1; insert into a values(3); rollback; end; /
backup replication using trigger
create or replace trigger trg before insert or update or delete on b for each row begin if inserting then insert into a values(:new.t1); elsif updating then update a set t1=:new.t1 where t1=:old.t1; else delete from a where t1=:old.t1; end if; end; /
example for local procedure
declare procedure p1 as begin insert into a values(2); commit; end; begin insert into a values(1); p1; insert into a values(3); rollback; end; /
example for checking palindrome by using procedure
create or replace procedure pal(n in varchar2,k out varchar2) as i varchar2(30); begin
select reverse(n) into i from dual;
if n=i then k:='palindrOme'; else k:='not palindrome'; end if; end; /
example for creating function
create or replace function fn1(a in number,b in number) return number as c number; procedure p1 as pragma autonomous_transaction; begin insert into a values(1); commit; end; begin p1; c:=a+b; return c; end; /
program to find n maximum salary using function
create or replace function fn3(n in number) return employees.salary%type as i employees.salary%type; cursor c1 is select salary from employees order by salary desc;
begin open c1; for j in 1..n loop fetch c1 into i; if j=n then return i; end if; end loop; end; /
program to create package
create or replace package p1 as procedure pal(n in varchar2,k out varchar2);
function fn1(a in number,b in number) return number;
end p1; /
create or replace package body p1 as procedure pal(n in varchar2,k out varchar2) as i varchar2(30); begin
select reverse(n) into i from dual;
if n=i then k:='palindrOme'; else k:='not palindrome'; end if; end pal;
function fn1(a in number,b in number) return number as c number; procedure p1 as pragma autonomous_transaction; begin insert into a values(1); commit; end; begin p1; c:=a+b; return c; end fn1;
To change the first 'A' in the word 'MALAYALAM' to 'B'(MBLAYALAM):
Algorithm: 1.Split the string 'MALAYALAM' into 'MA' & 'LAYALAM' using substr 2.Now replace the 'A' into 'B' from 'MA' ('MB') using replace 3.Now CONCAT or Concatenate(||) both 'MA' & 'LAYALAM' - 'MBLAYALAM'
Query: select 'malayalam' ,replace((substr ('malayalam',1,2)),'a','b')||(substr ('malayalam',3)) from dual;
Difference between Concat and ||
Concat - Accepts only two arguments || - accepts more than two arguments
Determine the number of managers without listing them label the column number of managers. HINT use the manger_id column to determine the number of managers
create a query that display the employees last_name and indicates the amount of their annual income salaries with asterisks. Each asterisks signifies a thousand. Sort the data in descending order of the salary. Label the column Employee_and_their_salaries
select last_name||''||replace(salary*12,'salary*12','*')as Employee_and_their_salaries from employees order by salary desc;
output is coming like this King288000 But i need output like this King******
i cant replace salary amount as a * symbol can any one help me in this...
How to find duplicates in first name and print that duplicates alone?
select first_name, count(*) duplicates from employees group by first_name HAVING count(*) > 1;
output:
FIRST_NAME DUPLICATES Peter 3 Michael 2 Steven 2 John 3 Julia 2 William 2 Karen 2 Kevin 2 David 3 Jennifer 2 Randall 2 Alexander 2 James 2 13 rows returned in 0.00 seconds
I tried my best but my answer was wrong. So i took this from net and changed it.
unmatched records results must have result as 17 records yar. You did left outer join unmatched records and right-outer join is here but it must give 17 records as output.......
Q:what is STDDEV? Ans: STDDEV returns the sample standard deviation of expr, a set of numbers. You can use it as both an aggregate and analytic function. It differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns null.
example: SELECT STDDEV(salary) "Deviation" FROM employees;
Example2: SELECT last_name, salary, STDDEV(salary) OVER (ORDER BY hire_date) "StdDev" FROM employees WHERE department_id = 30;
Q:what is VARIANCE? Ans: >VARIANCE returns the variance of expr. You can use it as an aggregate or analytic function. > Oracle Database calculates the variance of expr as follows:
>0 if the number of rows in expr = 1
>VAR_SAMP if the number of rows in expr > 1
Example1: SELECT VARIANCE(salary) "Variance" FROM employees;
Example2: SELECT last_name, salary, VARIANCE(salary) OVER (ORDER BY hire_date) "Variance" FROM employees WHERE department_id = 30;
Display the 10th maximum salary ? I Think this is correct
select min(salary) from (select salary from employees group by salary order by salary desc) where rownum<=10; Ans: 10000 (OR) select min(salary) from (select distinct(salary) from employees order by salary desc) where rownum<=10; Ans:10000
Q:to find no.of space in a text Ans: Syntax: SELECT 'text' as text,length('text')-length(replace('text',' ')) as "No of space in text" FROM dual; Example: SELECT 'GT specialises in technical training, particularly in the areas of data communications & networking' as text, length('GT specialises in technical training, particularly in the areas of data communications & networking')- length(replace('GT specialises in technical training, particularly in the areas of data communications & networking',' ')) as "No of space in text" FROM dual;
Difference Between Oracle 10g and 11g ? Oracle 10g vs 11g Oracle databases are object-relational database management systems developed and distributed by Oracle Corporation. The latest version of Oracle databases is Oracle 11g, first released in September 2008; it succeeded Oracle 10g. These versions are a part of the continued evolution of the Oracle databases since the 1980s. Each new version has newer patch sets release regularly, the most significant of which are known as a release. A major objective of each upgraded version is to increase performance and scalability over the older version. Therefore there are many new features in 11g built on top of those already available in 10g. These provide better capabilities to the database administrators (DBAs) to manage their multi-tiered database environments that are getting more complex and larger over the years. Oracle 10g Oracle 10g was the upgraded version from Oracle 9i. It was a very stable version from the out set with many of the bugs in 9i fixed and with host of new features. Primarily it provided grid computing by provision of CPUs and data. To this end, Oracle Enterprise Manager (OEM) gave a powerful grid control mechanism. This version also provided enhancements for advanced extensions such as Oracle RAC (Real Application Clusters), Oracle Data Guard and Oracle Streams. 10g brought about automation of most administration tasks by introducing many self-managing features like automated database diagnostic monitor, automated shared memory tuning, automated storage management, and automated disk based backup and recovery. Oracle 11g Oracle 11g pushed the envelop further, enhancing many of the features found in 10g. It provided new components such as Oracle Application Express, Oracle SQL Developer, Oracle Real Application Testing, Oracle Configuration Manager (OCM), Oracle Warehouse Builder, Oracle Database Vault and Oracle Shadow Copy Service. Therefore 11g provides better performance and its release 2 has been geared for newer operating systems such as Windows 7, Server 2008 and latest versions of Linux, Unix, Solaris, etc. What is the difference between 10g and 11g? Compared with 10g, 11g provides more simplified, improved and automated memory management and better ability to diagnose faults through inbuilt infrastructure to prevent, detect, diagnose, and help resolve critical database errors, as well as, low database performance issues. It provides invisible indexes, virtual columns, table partitioning and the ability to redefine tables which have materialized view logs whilst online. A major difference in the two are the new security features found in 11g such as better password-based authentication with mixed case passwords, encryption on tablespace-level and enhancements for data pump encryption and compression. 11g continued the use of different editions used in 10g which are Enterprise Edition (EE), Standard Edition (SE), Standard Edition One (SE1), Express Edition (EX) and Oracle Database Lite for mobile devices.
optimising SQL statements 1.check the steps how oracle access query check the execution plan of query using(set autot on) 2.rewrite the query i.create index for column which is frequently used to retrieve data ii.table partitioning for the data you know example:you know branches of your institute thus,the CPU usage can be reduced and performance increases
Advantages of pl/sql i.performance fast(since pl/sql is compiled and stored .so,it does only hard parsing) by reducing network traffic(it groups logical related SQL statements)
SQL Query to find unmatched record from both tables algorithm:left outer join union right outer join
select e.first_name, d.DEPARTMENT_NAME from employees e, DEPARTMENTS d where e.DEPARTMENT_ID=d.DEPARTMENT_ID(+) and d.DEPARTMENT_ID is null union select e.first_name, d.DEPARTMENT_NAME from employees e, DEPARTMENTS d where e.DEPARTMENT_ID(+)=d.DEPARTMENT_ID and e.DEPARTMENT_ID is null;
Q:Date calculation to show how many weeks and days an employee has been working here... ANS: select first_name , floor((sysdate-hire_date)/7) as weeks, floor(mod(sysdate-hire_date,7)) as days from employees where department_id = 50;
Q:is it possible to insert the values in dual table? Ans: yes, we can insert the values in dual table by sys user... example: insert into dual values(10); but while selecting all column it will show only 'x'... in dual table the value stored as 'x'
rank it also ranks only for groups so,we have to partition the column into groups for group ranking example:to rank the specific dept sytax: rank() over(partition by (column to be partitioned) order by (column which is used to sort) )
rank() over(partition by department_id order by salary desc)
SQL Query to rank 1.based on overall salary of employees 2.based on the salary of specific departments
select FIRST_NAME, EMPLOYEE_ID,SALARY, DEPARTMENT_ID, rank() over(order by salary desc) salary_rank, rank() over(partition by department_id order by salary desc) dept_wise_rank from employees group by(FIRST_NAME,EMPLOYEE_ID, SALARY, DEPARTMENT_ID) order by 5;
SQL Query to dense_rank(it does not leave a gap) 1.based on overall salary of employees 2.based on the salary of specific departments
select FIRST_NAME, EMPLOYEE_ID,SALARY, DEPARTMENT_ID, dense_rank() over(order by salary desc) salary_rank, dense_rank() over(partition by department_id order by salary desc) dept_wise_rank from employees group by(FIRST_NAME,EMPLOYEE_ID, SALARY, DEPARTMENT_ID) order by 5;
difference between rank and dense_rank Rank() that RANK() leaves gaps while ranking the records whereas DENSE_RANK() doesn't leave any gaps.
The easiest way to understand is by having an example, so consider the following data set: 24000 17000 17000 14000 RANK() would return the following rank: 1 2 2 4 DENSE_RANK() would return the following rank: 1 2 2 3
Q:Whats the difference between MySQL and SQL? Ans: SQL is the language to write queries in. SQL = Structured Query Language, MySQL = Open Source Relational Database System MSSQL = Microsoft SQL Server mysql is an open source sql product for the linux unix platform mssql is a properity sql product by microsoft
can any one help me to find this output of this query select to_char('30-oct-2011','dd/mm/yyyy') from dual; or select to_char(30-oct-2011,'dd/mm/yyyy') from dual
assignment how to arrange the datas based on the joining day like sunday monday ANS: select Employee_id,First_name||last_name as name,Email,Phone_number,to_char(hire_date,'day') as joinigday,hire_date,Job_id,salary,commission_pct,manager_id,department_id from employees order by decode((to_char(hire_date,'dy')),'sun',1,'mon',2,'tue',3,'wed',4,'thu',5,'fri',6,'sat',7,8) asc; if there is easy step kindly post and mention this post name....
NVL(rollnumber, 'Is null') The SQL statement above would return 'Is null' if the roll number field contained a null value. Otherwise, it would return the roll number.
The syntax for the NVL2 function is:
NVL2( string1, value_if_NOT_null, value_if_null )
string1 is the string to test for a null value.
value_if_NOT_null is the value returned if string1 is not null.
value_if_null is the value returned if string1 is null.
The SQL statement above would return 'IS null' if the roll number field contained a null value. Otherwise, it would return the 'Not null'.
There is another technique which is commonly used to control Loop.. End Loop within PL/SQL block is the FOR variable IN construct.
Here FOR automatically creates the memory variable of %rowtype.Each record in the opened cursor becomes a value for the memory variable of the %rowtype.
The FOR ensures that a row from the cursor is loaded in the declared memory variable and the loop executes once.This goes on untill all the rows of the cursor have been loaded into memory variable.After this the loop stops.
syntax
FOR memory variable IN cursorname
BEGIN FOR item IN ( SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ) LOOP DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /
Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record. The sequence of statements inside the loop is executed once for each row that satisfies the query. When you leave the loop, the cursor is closed automatically.
to see the output of above function do following steps in sqlplus cmd 1.@function path......then function created 2.select fn_eid_sal(160) from dual output: FN_EID_SAL(160) ---------------------- first name is Louise salary is 7500
task: create a procedure which returns first name and salary for a given employee id
create or replace procedure pn_eid_sal(eid in number) as c varchar2(60);
begin
select 'first name is '||first_name||chr(10)||'salary is '||salary into c from employees where employee_id=eid; dbms_output.put_line(c); end; /
to see the output of above procedure do following steps in sqlplus cmd 1.@procedure path......then procedure created 2.exec pn_eid_sal(100) output: first name is Steven salary is 24000
ASSIGNMENT: How to get duplicates only? ANS: select salary,count(salary) from employees group by salary having count(salary)>1; ANS: It shows the duplicate salary..
Configuration management (CM) is a field of management that focuses on establishing and maintaining consistency of a system or product's performance and its functional and physical attributes with its requirements, design, and operational information throughout its life. Configuration management was first developed by the United States Air Force for the Department of Defense in the 1950s as a technical management discipline of hardware.
- Regular expression functions REGEXP_LIKE , REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR - Compile time warnings - Conditional compilation - Improvement to native compilation - BINARY_INTEGER made similar to PLS_INTEGER - INDICES OF , VALUES OF in FORALL lets you work on non-consecutive indices - Quoting mechanism . Instead of quoting single quotes twice everytime, give your own delimiter to go on using single quotes. Ex: q'!I'm a string, you're a string.!' - Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN - Implicit conversion between CLOB and NCLOB - Improved Overloading - New datatypes BINARY_FLOAT, BINARY_DOUBLE - Global optimization enabled - PLS_INTEGER range increased to 32bit - DYNAMIC WRAP using DBMS_DDL
Oracle ROWID uniquely identifies where a row resides on disk. The information in a ROWID gives Oracle everything it needs to find your row, the disk number, the cylinder, block and offset into the block. It actually the physical address. ROWID can be used to fetch a row from database. ROWID is permanent.
Whereas, ROWNUM is a "pseudo-column", ROWNUM is used to restrict the number of rows in a query. ROWNUM is temporary.
Dynamic SQL is an enhanced form of Structured Query Language (SQL) that, unlike standard (or static) SQL, facilitates the automatic generation and execution of program statements. This can be helpful when it is necessary to write code that can adjust to varying databases, conditions, or servers. It also makes it easier to automate tasks that are repeated many times. Dynamic SQL statements are stored as strings of characters that are entered when the program runs. They can be entered by the programmer or generated by the program itself, but unlike static SQL statements, they are not embedded in the source program. Also in contrast to static SQL statements, dynamic SQL statements can change from one execution to the next. Dynamic SQL statements can be written by people with comparatively little programming experience, because the program does most of the actual generation of the code. A potential problem is reduced performance (increased processing time) if there is too much dynamic SQL running at any given time.
What is the difference between Cursor and ref_cursor?
At the most "basic level", they are the same. A "normal" plsql cursor is static in defintion. Ref cursors may be dynamically opened or opened based on logic. Declare type rc is ref cursor; cursor c is select * from dual; l_cursor rc; begin if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp'; elsif ( to_char(sysdate,'dd') = 29 ) then open l_cursor for select * from dept; else open l_cursor for select * from dual; end if; open c; end; / Given that block of code -- you see perhaps the most "salient" difference -- no matter how many times you run that block -- cursor C will always be select * from dual. The ref cursor can be anything. Another difference is a ref cursor can be returned to a client. a plsql "cursor cursor" cannot be returned to a client. aA cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function) A ref cursor can be passed from subroutine to subroutine -- a cursor cannot be. Static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to - returning result sets to clients - when there is NO other efficient/effective means of achieving the goal that is, you want to use static SQL (with implicit cursors really) first and use a ref cursor only when you absolutely have to
What is the difference between Functions and procedures?
1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter. Â 2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not. Â 9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used. All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table. A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns. In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION). Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse. A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data. The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete. You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view. A materialized view can be stored in the same database as its base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment
Global Temporary Tables Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables. • Creation of Temporary Global Tables • • Miscellaneous Features
Creation of Global Temporary Tables The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction. CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER ) ON COMMIT DELETE ROWS;
In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session. CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER ) ON COMMIT PRESERVE ROWS;
Miscellaneous Features • If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• There are a number of restrictions related to temporary tables but these are version specific.
A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition.
A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete. You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
A materialized view can be stored in the same database as its base table(s) or in a different database.
Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment
What is the difference between Functions and procedures?
1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter. Â 2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not. Â 9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
To find employees having more than 20 yrs exp: select concat(first_name,last_name),hire_date from employees where months_between(sysdate,hire_date)/12>=20 ;
-- ** LOCAL PROCEDURE AND FUNCTION ** -- ****** PL/SQL BLOCK ****** DECLARE -- LOCAL PROCEDURE DECLARATION PROCEDURE PROC AS BEGIN DBMS_OUTPUT.PUT_LINE('HELLO WORLD'); END PROC;
-- LOCAL FUNCTION DECLARATION FUNCTION FUN RETURN VARCHAR2 AS BEGIN RETURN CONCAT('HELLO','WORLD'); END FUN;
BEGIN PROC(); DBMS_OUTPUT.PUT_LINE(FUN()); END; / SQL> @E:\PLSQL\PL_LOC_PR_FN.SQL HELLO WORLD HELLOWORLD
ASSIGNMENT: How to get a unmatched records in table joins? ANS: select ss.sname, cc.cid from s ss full outer join c cc on ss.cid=cc.cid minus ( select ss.sname, cc.cid from s ss, c cc where ss.cid=cc.cid )
TRUNCATE: 1.A DDL Command, 2.Can't be used with WHERE clause. 3.Can't be Rolled back. 4.Drops the entire table contents when executed but not the table. [Note:DROP commnad deletes the entire contents along with the table] 5.Truncate is faster. 6.Frees-up the Stroage Space 7. Doesn't create a log file 8.Doesn't return the no. of rows deleted when queried. 9.Trigger doesn't get fired incase of Truncate. 10.Data gets romoved directly without copying it into the Rollback tablespace when truncate is executed.
DELETE: 1.DML Command. 2.Can be used with WHERE clause. 3.Can be Rolled back. 4.Deletes table contents row by row when executed. 5.Delete is slow 6.Doesn't frees-up the Stroage Space 7.Creates the log file 8.Returns the no. of rows deleted when queried. 9. Trigger gets fired incase of Delete. 10. Date gets copied into the Rollback tablespace after Delete is executed
Difference between DBMS & RDBMS? DBMS: 1) In dbms no relationship concept. 2) It supports Single User only. 3) It treats Data as Files internally. 4) It supports 3 rules of E.F.CODD out off 12 rules. 5) It requires low Software and Hardware Requirements. 6) FoxPro, IMS are Examples. RDBMS: 1)It is used to establish the relationship concept between two database objects, i.e, tables. 2) It supports multiple users. 3) It treats data as Tables internally. 4) It supports minimum 6 rules of E.F.CODD. 5) It requires High software and hardware requirements. 6) SQL-Server, Oracle are examples
finding max salary for each and every department??
ans: select * from employees e where salary in (select max(salary) from employees ee where nvl(ee.department_id,0) = nvl(e.department_id,0) group by department_id) order by salary desc
CREATE OR REPLACE TRIGGER TRG_T1 BEFORE INSERT OR UPDATE OR DELETE ON T1 for each row BEGIN if inserting then INSERT INTO TEST VALUES(:new.a); elsif updating then update test set b=:new.a where b = :old.a; else delete from test where b = :old.a; end if; END; /
CREATE OR REPLACE TRIGGER TRG_T1 BEFORE INSERT OR UPDATE OR DELETE ON T1 for each row BEGIN if inserting then INSERT INTO TEST VALUES(:new.a); elsif updating then update test set b=:new.a where b = :old.a; else delete from test where b = :old.a; end if; END; /
How to change the word malayalam to mblayalam? Query: select 'malayalam', replace(substr('malayalam',1,2),'a','b')||substr('malayalam',3,7) from dual; Output: malayalam mblayalam
-- ******** BREAK AND COMPUTE ***************** -- CREATE EEMP TABLE AND INSERT THE VALUES OR --USE THE EMPLOYEES TABLE --TRY THIS IN SQLPLUS SQL> SELECT * FROM EEMP ORDER BY JOB_ID; ID NAME SALARY JOB_ID ---- ------------ -------- --------- 13 KUMAR 2200 IT_ADMIN 11 SANKAR 20000 IT_ADMIN 14 RAM 28000 IT_PRG 10 SIVA 10000 IT_PRG 12 SIVASANKAR 30000 IT_PRG
SQL> SET PAGESIZE 500 SQL> BREAK ON JOB_ID SKIP1 SQL> COMPUTE MAX MIN SUM AVG OF SALARY ON JOB_ID SQL> SELECT ID,NAME,SALARY,JOB_ID FROM EEMP ORDER BY JOB_ID;
ID NAME SALARY JOB_ID ---- ----------- ---------- --------- 13 KUMAR 2200 IT_ADMIN 11 SANKAR 20000 -------- ********** 11100 avg 2200 minimum 20000 maximum 22200 sum
ASSIGN:how to delete a duplicate records in a table ANS: Table t1 contains datas like A B C 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 1 2 3 1 2 delete from t1 where rowid not in(select max(rowid) from t1 group by a,b,c) output: A B C 1 1 1 2 1 2 3 1 2
How to get a only duplicate values from a table? Table t1 contains data like A 2 2 2 2 1 1 1 5 select a from t2 where rowid not in(select max(rowid) from t2 group by a) output A 1 1 2 2 2
SELECT SYSTIMESTAMP from dual;
ReplyDeleteThis should work!
Table A Table B
ReplyDelete1 1
2 2
3 4
4 5
O/P should be
A
2
2
Try it!
Which is more faster - IN or EXISTS?
ReplyDeleteUsing NOT EXISTS instead of NOT IN adds a limiting condition to your queries that can reduce the number of full table scans necessary.
ReplyDeleteBecause NOT IN does not use a limiting condition, Oracle will perform a full table scan.
So USING EXISTS is faster.
hi
ReplyDeletehow to do the possibility search without using like operator?
ReplyDeletedifference between dbms and rdbms
ReplyDeleteDBMS is defined as the software program that is used to manage all the databases that are stored on the network or system hard disk.
ReplyDeleteRDBMS solution is required when large amounts of data are to be stored as well as maintained. A relational data model consists of indexes, keys, foreign keys, tables and their relationships with other tables
how will you delete the tenth field froma a table(say employees table)
ReplyDeleteTo select nth largest value let N=10
ReplyDeleteSELECT MIN(SALARY) FROM(SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM<=10
To display the duplicate records
SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(*) > 1
To display Even no of records
SELECT EMPLOYEE_ID, FIRST_NAME FROM EMPLOYEES WHERE MOD(EMPLOYEE_ID,2)=0
To Delete the duplicate records
ReplyDeleteCREATE TABLE EM AS SELECT * FROM EMPLOYEES
DELETE FROM EM WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EM GROUP BY DEPARTMENT_ID HAVING COUNT(*) > 1)
SELECT * FROM EM
what is self join ? explain with an example
ReplyDeleteThis comment has been removed by the author.
ReplyDeletewhat is a cartesian product ?
ReplyDeletehow to enable and disable a primary key constraint?
ReplyDeleteThe following message is displayed when I type commit;
ReplyDelete"Commit statement not applicable. All statements are automatically committed."
How to I commit my changes manually?
@ murali
ReplyDeleteyou might be using isqlplus with autocommit check box selected. deselect the autocommit checkbox to commit manually
to find if a word is a palindrome
ReplyDeletedeclare
i varchar(30) := '&i';
j varchar(30);
k varchar(30);
begin
select i,reverse(i) into j,k from dual;
if j=k then
dbms_output.put_line('word is a palindrome');
else dbms_output.put_line('word is not a plindrome');
end if;
end;
/
program to find odd or even
ReplyDeletedeclare
i varchar2(10);
begin
select decode(mod(&number,2),0,'EVEN','ODD')
into i
from dual;
dbms_output.put_line(i);
end;
program to find palindrome
declare
j varchar2(30):='&word';
k varchar2(30);
begin
select reverse(j)
into k
from dual;
if j=k then
dbms_output.put_line('The given string is palindrome');
else
dbms_output.put_line('The given string is not palindrome');
end if;
end;
program to display the names
declare
i number:=&i;
k employees.first_name%type;
j number;
cursor c1 is
select first_name,length(first_name)
from employees;
begin
open c1;
loop
fetch c1 into k,j;
if j=i then
dbms_output.put_line(k);
end if;
exit when c1%notfound;
end loop;
close c1;
end;
Can we use a set operator for Materialized views?
ReplyDeleteTry it!
Find Negative value
ReplyDelete---------------------
declare
NegativeError EXCEPTION;
i number := &i;
j number;
begin
select sign(i) into j from dual;
if j < 0 then
RAISE NegativeError;
end if;
EXCEPTION
WHEN NegativeError THEN
dbms_output.put_line ('Given Nuber is a negative value');
end;
/
Validate Email with @ and . symbol
----------------------------------
declare
InvalidEmailError EXCEPTION;
email varchar(20) := '&email';
i number;
j number;
begin
select instr(email,'@'), instr(email,'.') into i ,j from dual;
if i = 0 or j = 0 then
RAISE InvalidEmailError;
end if;
EXCEPTION
WHEN InvalidEmailError THEN
dbms_output.put_line ('Email is not valid');
end;
/
To display the nth maximum salary
ReplyDelete----------------------------------
declare
n number := &n;
k employee.salary%type;
cursor c1 is
select salary from employee order by salary desc;
begin
open c1;
for i in 1..n
loop
fetch c1 into k;
exit when C1%notfound;
end loop;
close c1;
dbms_output.put_line (k);
end;
/
program to find negative value
ReplyDeletedeclare
i number:='&Employeeid';
neg exception;
begin
if i<0 then
raise neg;
end if;
insert into A values(i);
dbms_output.put_line('ID inserted');
exception
when neg then
dbms_output.put_line('Invalid negative ID');
end;
/
Program to validate emailid
declare
i varchar(30):='&mailid';
erro exception;
begin
if i LIKE '%@%.%' then
dbms_output.put_line('valid mail id');
else
raise erro;
end if;
exception
when erro then
dbms_output.put_line('invalid email id');
end;
/
For copying all the full table to a new table
ReplyDeletecreate table new_table
as
select * from Existing_table
@saranya
ReplyDeletewhat is a cartesian product ?
Cartesian product is nothing but the matrix product
how to enable and disable a primary key constraint?
Just remove the constraint
@tina
ReplyDeleteIn the program to find negative value
in line no. 2
i number:='&Employeeid';
We should remove the single quotes '&Employeeid'
This comment has been removed by the author.
ReplyDeleteQUERY FOR 2ND MAX SALARY:
ReplyDeleteselect max(salary) "2ndMax Salary" from employees
where salary < (select max(salary)from employees);
select max(salary) "2ndMax Salary" from employees
where salary <> (select max(salary)from employees);
select max(salary) "2ndMax Salary" from employees
where salary != (select max(salary)from employees);
Query to replace the char by char or word at a given postion??
ReplyDeleteQUERY FOR PALINDROME
ReplyDeleteselect first_name,
case
when first_name=reverse(first_name) then 'palindrome'
else 'not palindrome' end result
from
employees;
PALINDROME USING PROCEDURE
ReplyDelete--------------------------
CREATE OR REPLACE PROCEDURE check_Palindrom(name in varchar,o out varchar)
IS
j varchar(20);
BEGIN
select reverse(name) into j from dual;
if name != j then
o:='not palindrome';
else
o:='palindrome';
end if;
END;
/
checking the duplicate user using procedure
ReplyDelete-------------------------------------------
CREATE OR REPLACE PROCEDURE IsduplicateUser(newusrname in varchar, newpwd in varchar,o out varchar)
IS
usercnt number;
begin
select count(*) into usercnt from empuser where upper(username) = upper(newusrname) and upper(password) = upper(newpwd);
if usercnt != 0 then
o:='duplicate user';
else
o:='valid user';
end if;
end;
/
program to find even or odd
ReplyDelete..........
declare
a varchar2(10);
i varchar2(10);
begin
select mod( &a,2)
into i
from dual;
if a=0 then
dbms_output.put_line('even');
else
dbms_output.put_line('odd');
end if;
end;
/
SQL Query to find the day of the employee joined in a company
ReplyDeletealgorithm:
1:we can find the day by using data type conversion functions to_char and to_date.
in a table,the date are stored in date format.
2.so,we covert the date in a table into char .now,we can find the day of employee joined
QUERY:
select first_name,hire_date,
to_char(hire_date,'day') hire_day
from
employees;
Hi Every one! I am Bhanu Priya from SQL batch.
ReplyDeleteTrigger to stop insert from a table
ReplyDelete------------------------------------
create or replace trigger my_trig before insert on t1
begin
raise_application_error(-20000,'Dont do that');
end;
hi
ReplyDeletehai...
ReplyDelete1)select * from employees,departments;
2)select * from employees
union all
select * from departmets;
Both are possible means...
what is the diffence between both of these..?
SQL Query to find the day of the employees joined in a company and to arrange the order of the day starting from sunday to saturday:
ReplyDeleteselect
first_name,hire_date, to_char((hire_date),'D DAY') "hire_day"
from
employees
order by "hire_day";
D - Day of the week
It also returns a value from 1 to 7 representing the day of the week.
For eg:-
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
This comment has been removed by the author.
ReplyDeletereplace with no_staff when no employees are avlbl to the dept_name.
ReplyDeleteTry it!!
FIRST_NAME, DEPARTMENT_NAME
-------------------- ---------------------
Luis Finance
Shelley Accounting
no staff Treasury
no staff Corporate Tax
no staff Control And Credit
no staff Shareholder Services
example for plsql table
ReplyDeletedeclare
type tname is table of number index by binary_integer;
i tname;
begin
i(1):=100;
i(2):=200;
i(3):=300;
i(4):=400;
for j in 1..i.count loop
dbms_output.put_line(i(j));
end loop;
end;
/
example for varray
declare
type tname is varray(3) of varchar2(30);
i tname;
begin
i:=tname('thina','karan','thinu');
for j in 1..i.count loop
dbms_output.put_line(i(j));
end loop;
end;
/
example for refcursor
create or replace procedure refcur(i in number,o out sys_refcursor)
as
begin
open o for
select first_name,salary
from employees where department_id=i;
end;
/
example for pragma autonomous_transaction
declare
procedure p1
as
pragma autonomous_transaction;
begin
insert into a values(2);
commit;
end;
begin
insert into a values(1);
p1;
insert into a values(3);
rollback;
end;
/
backup replication using trigger
create or replace trigger trg
before insert or update or delete on b
for each row
begin
if inserting then
insert into a values(:new.t1);
elsif updating then
update a set t1=:new.t1 where t1=:old.t1;
else
delete from a where t1=:old.t1;
end if;
end;
/
example for local procedure
declare
procedure p1
as
begin
insert into a values(2);
commit;
end;
begin
insert into a values(1);
p1;
insert into a values(3);
rollback;
end;
/
example for checking palindrome by using procedure
create or replace procedure pal(n in varchar2,k out varchar2)
as
i varchar2(30);
begin
select reverse(n)
into i
from dual;
if n=i then
k:='palindrOme';
else
k:='not palindrome';
end if;
end;
/
example for creating function
create or replace function fn1(a in number,b in number)
return number
as
c number;
procedure p1
as
pragma autonomous_transaction;
begin
insert into a values(1);
commit;
end;
begin
p1;
c:=a+b;
return c;
end;
/
program to find n maximum salary using function
create or replace function fn3(n in number)
return employees.salary%type
as
i employees.salary%type;
cursor c1 is
select salary from employees order by salary desc;
begin
open c1;
for j in 1..n loop
fetch c1 into i;
if j=n then
return i;
end if;
end loop;
end;
/
program to create package
create or replace package p1
as
procedure pal(n in varchar2,k out varchar2);
function fn1(a in number,b in number)
return number;
end p1;
/
create or replace package body p1
as
procedure pal(n in varchar2,k out varchar2)
as
i varchar2(30);
begin
select reverse(n)
into i
from dual;
if n=i then
k:='palindrOme';
else
k:='not palindrome';
end if;
end pal;
function fn1(a in number,b in number)
return number
as
c number;
procedure p1
as
pragma autonomous_transaction;
begin
insert into a values(1);
commit;
end;
begin
p1;
c:=a+b;
return c;
end fn1;
end p1;
/
how to find no of a's in malayalam????
ReplyDeleteHow to find no of a's in malayalam?
ReplyDeleteALGORITHM:
1.Length of malayalam - 9
2.Replace 'a' in malayalam - mlylm
3.Length(Replace 'a' in malayalam) - 5
4.(Length of malayalam) - (Length(Replace 'a' in malayalam)) - 4
QUERY:
select 'MALAYALAM',length('MALAYALAM')-length(replace('MALAYALAM','A')) "No of A's:" from dual;
To change the first 'A' in the word 'MALAYALAM' to 'B'(MBLAYALAM):
ReplyDeleteAlgorithm:
1.Split the string 'MALAYALAM' into 'MA' & 'LAYALAM' using substr
2.Now replace the 'A' into 'B' from 'MA' ('MB') using replace
3.Now CONCAT or Concatenate(||) both 'MA' & 'LAYALAM' - 'MBLAYALAM'
Query:
select 'malayalam' ,replace((substr ('malayalam',1,2)),'a','b')||(substr ('malayalam',3)) from dual;
Difference between Concat and ||
Concat - Accepts only two arguments
|| - accepts more than two arguments
Determine the number of managers without listing them label the column number of managers.
ReplyDeleteHINT use the manger_id column to determine the number of managers
Answer is 8
I tried it. But it is coming wrong output for me.
create a query that display the employees last_name and indicates the amount of their annual income salaries with asterisks. Each asterisks signifies a thousand. Sort the data in descending order of the salary. Label the column Employee_and_their_salaries
ReplyDeleteselect last_name||''||replace(salary*12,'salary*12','*')as Employee_and_their_salaries from employees order by salary desc;
output is coming like this King288000
But i need output like this King******
i cant replace salary amount as a * symbol can any one help me in this...
@Ramachan
ReplyDeleteTry this, if the salary is 24000 then it displays 24 asterisks..
SELECT CONCAT(LAST_NAME,RPAD('*',SALARY/1000,'*')) FROM EMPLOYEES
@Ramchan
ReplyDeleteSELECT COUNT(DISTINCT(MANAGER_ID)) AS "NUMBER OF MANAGERS" FROM EMPLOYEES;
but the no of managers is 18 not 8. what result you get?
@Suren
ReplyDeletehow to find no of 'a's in malayalam?
SELECT LENGTH('MALAYALAM')-LENGTH(REPLACE('MALAYALAM','A','')) AS "TOTAL NO OF 'A's IN MALAYALAM" FROM DUAL
@ramcharan
ReplyDeletethe number of managers
select count(distinct(manager_id))"no of managers"
from employees;
Hey Guys! This is one of the consultant for TCS. If interested send your shri.s@twsol.com
ReplyDeletethis program is to execute recompiling stored procedures
ReplyDeleteDECLARE
CURSOR C5 IS
select NAME from user_dependencies where REFERENCED_NAME='T1' AND TYPE = 'PROCEDURE';
I USER_DEPENDENCIES.NAME%TYPE;
BEGIN
OPEN C5;
LOOP
FETCH C5 INTO I;
DBMS_DDL.ALTER_COMPILE('PROCEDURE','HR','I');
EXIT WHEN c5%NOTFOUND;
END LOOP;
END;
/
by
v.karthik,kingsley, JP(jaya prakash),Prem
@naveen
ReplyDeleteThis is for freshers too ??
Can i apply dude??
This comment has been removed by the author.
ReplyDeletehow to display only hours mins and seconds?
ReplyDeleteselect to_char(sysdate,'HH:mi:ss') from dual;
output:
03:10:14
dummy table has value like this
ReplyDeleteselect * from dual;
dual table has value as X
column name as dummy
How to find 10th max salary from employees table
ReplyDeleteselect min(salary) from (select salary from employees order by salary desc) where rownum<=10;
How to display only one pipe symbol between first name and last name?
ReplyDeleteselect first_name||last_name as "First_name|Last_name" from employees;
output: First_name|Last_name
Q1. Display the unmatched records ?
ReplyDeleteans: select
e.first_name ,
d.department_name
from
employees e,
departments d
where
e.department_id=d.department_id(+)
and d.department_id is null;
select * from employees where null=null;
ReplyDeleteselect * from employees where 1=2;
Output: no data found
Q2: To print a number from 1..100 using SQL ?
ReplyDeleteans : select level from dual
connect by level<=100;
Q3: To print time only using SQL stmt ?
ReplyDeleteans: select to_char(sysdate, 'hh:mm:ss')from dual
Q4: Display only duplicate records using SQL stmt ?
ReplyDeleteans: select first_name
from employees
group by first_name
having count(first_name)>1 ;
display the 10th max salary ?
ReplyDeleteans: select min(salary)from
(select salary from employees order by salary desc) where rownum <=10
Q 9: select * from emloyees where 1=2 ;
ReplyDeleteans: no data found
Q 10: Backup : copy the table structure ?
ans : create table bk_employees as select * from employees ;
desc bk_employees
How to find duplicates in first name and print that duplicates alone?
ReplyDeleteselect first_name, count(*) duplicates from employees group by first_name HAVING count(*) > 1;
output:
FIRST_NAME DUPLICATES
Peter 3
Michael 2
Steven 2
John 3
Julia 2
William 2
Karen 2
Kevin 2
David 3
Jennifer 2
Randall 2
Alexander 2
James 2
13 rows returned in 0.00 seconds
I tried my best but my answer was wrong. So i took this from net and changed it.
unmatched records from right outer join is?
ReplyDeleteselect
e.first_name ,
d.department_name
from
employees e,
departments d
where
e.department_id(+)=d.department_id
and e.department_id is null;
output
16 rows
@selvakumar
ReplyDeleteunmatched records results must have result as 17 records yar. You did left outer join unmatched records and right-outer join is here but it must give 17 records as output.......
This comment has been removed by the author.
ReplyDeleteQ:copy only the table structure ?
ReplyDeleteans : create table bk_employees as select * from employees ;
this is wrong query...using this we can copy both data and structure of the table
Q: Is it possible to insert the values in dual table?
ReplyDeleteAns:yes, it is possible only in sys user...
while desc the dual table the output will be =X...
Q:what is STDDEV?
ReplyDeleteAns:
STDDEV returns the sample standard deviation of expr, a set of numbers. You can use it as both an aggregate and analytic function. It differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns null.
example:
SELECT STDDEV(salary) "Deviation"
FROM employees;
Example2:
SELECT last_name, salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30;
Q:what is VARIANCE?
ReplyDeleteAns:
>VARIANCE returns the variance of expr. You can use it as an aggregate or analytic function.
>
Oracle Database calculates the variance of expr as follows:
>0 if the number of rows in expr = 1
>VAR_SAMP if the number of rows in expr > 1
Example1:
SELECT VARIANCE(salary) "Variance"
FROM employees;
Example2:
SELECT last_name, salary, VARIANCE(salary)
OVER (ORDER BY hire_date) "Variance"
FROM employees
WHERE department_id = 30;
may i know whats the function of OVER in SQL?
ReplyDeleteDisplay the 10th maximum salary ?
ReplyDeleteI Think this is correct
select min(salary) from (select salary from employees group by salary order by salary desc) where rownum<=10;
Ans: 10000
(OR)
select min(salary) from (select distinct(salary) from employees order by salary desc) where rownum<=10;
Ans:10000
This comment has been removed by the author.
ReplyDeleteTo display duplicate records?
ReplyDeleteselect column name from table name
Group by column name
Having count(column name) > 1;
Copy only the table structure?
ReplyDeletecreate table emp as select * from employees
where 1=2;
Ans: Table created.
desc emp;
The condition 1=2 will never satisfy. Hence the new table will be created with only the structure.
This comment has been removed by the author.
ReplyDeleteQ:to find no.of space in a text
ReplyDeleteAns:
Syntax:
SELECT 'text' as text,length('text')-length(replace('text',' ')) as "No of space in text"
FROM dual;
Example:
SELECT 'GT specialises in technical training, particularly in the areas of data communications & networking' as text,
length('GT specialises in technical training, particularly in the areas of data communications & networking')-
length(replace('GT specialises in technical training, particularly in the areas of data communications & networking',' ')) as "No of space in text"
FROM dual;
Difference Between Oracle 10g and 11g ?
ReplyDeleteOracle 10g vs 11g
Oracle databases are object-relational database management systems developed and distributed by Oracle Corporation. The latest version of Oracle databases is Oracle 11g, first released in September 2008; it succeeded Oracle 10g. These versions are a part of the continued evolution of the Oracle databases since the 1980s. Each new version has newer patch sets release regularly, the most significant of which are known as a release. A major objective of each upgraded version is to increase performance and scalability over the older version. Therefore there are many new features in 11g built on top of those already available in 10g. These provide better capabilities to the database administrators (DBAs) to manage their multi-tiered database environments that are getting more complex and larger over the years.
Oracle 10g
Oracle 10g was the upgraded version from Oracle 9i. It was a very stable version from the out set with many of the bugs in 9i fixed and with host of new features. Primarily it provided grid computing by provision of CPUs and data. To this end, Oracle Enterprise Manager (OEM) gave a powerful grid control mechanism. This version also provided enhancements for advanced extensions such as Oracle RAC (Real Application Clusters), Oracle Data Guard and Oracle Streams. 10g brought about automation of most administration tasks by introducing many self-managing features like automated database diagnostic monitor, automated shared memory tuning, automated storage management, and automated disk based backup and recovery.
Oracle 11g
Oracle 11g pushed the envelop further, enhancing many of the features found in 10g. It provided new components such as Oracle Application Express, Oracle SQL Developer, Oracle Real Application Testing, Oracle Configuration Manager (OCM), Oracle Warehouse Builder, Oracle Database Vault and Oracle Shadow Copy Service. Therefore 11g provides better performance and its release 2 has been geared for newer operating systems such as Windows 7, Server 2008 and latest versions of Linux, Unix, Solaris, etc.
What is the difference between 10g and 11g?
Compared with 10g, 11g provides more simplified, improved and automated memory management and better ability to diagnose faults through inbuilt infrastructure to prevent, detect, diagnose, and help resolve critical database errors, as well as, low database performance issues. It provides invisible indexes, virtual columns, table partitioning and the ability to redefine tables which have materialized view logs whilst online. A major difference in the two are the new security features found in 11g such as better password-based authentication with mixed case passwords, encryption on tablespace-level and enhancements for data pump encryption and compression.
11g continued the use of different editions used in 10g which are Enterprise Edition (EE), Standard Edition (SE), Standard Edition One (SE1), Express Edition (EX) and Oracle Database Lite for mobile devices.
in sqlplus
ReplyDeleteset heading off
hides the headings in a table
optimising SQL statements
ReplyDelete1.check the steps how oracle access query
check the execution plan of query using(set autot on)
2.rewrite the query
i.create index for column which is frequently used to retrieve data
ii.table partitioning for the data you know
example:you know branches of your institute
thus,the CPU usage can be reduced and performance increases
select * from dict;
ReplyDeletedict->shows all the data dictionary table
totally 626 rows
Advantages of pl/sql
ReplyDeletei.performance fast(since pl/sql is compiled and stored .so,it does only hard parsing) by reducing network traffic(it groups logical related SQL statements)
SQL Query to find unmatched record from both tables
ReplyDeletealgorithm:left outer join union right outer join
select
e.first_name,
d.DEPARTMENT_NAME
from
employees e,
DEPARTMENTS d
where
e.DEPARTMENT_ID=d.DEPARTMENT_ID(+)
and d.DEPARTMENT_ID is null
union
select
e.first_name,
d.DEPARTMENT_NAME
from
employees e,
DEPARTMENTS d
where
e.DEPARTMENT_ID(+)=d.DEPARTMENT_ID
and e.DEPARTMENT_ID is null;
how to get last three characters in a string?
ReplyDeleteAns:
select First_name,substr(First_name,-3,3) from employees;
how to select the names which length should be 6 to 8?
ReplyDeleteselect First_name from employees where length(First_name) between 6 and 8;
how to select 6 character string from a table without using 'substr()'?
ReplyDeleteAns:
select First_name from employees where length(First_name)=6;
Q:any one can explain the concept of %rowtype
ReplyDelete@surendiran
ReplyDeleteanswer:
Rowtype is used to fetch a single full row of the table using variable.
Example: first_name last_name salary department_id
Q: About median function
ReplyDeleteANS:
MEDIAN(expr1) returns the median from a group of numbers.
It can be used for number data type and date date type alone.
select median(salary) from employees;
6200
@suren how can u insert values in dual table
ReplyDeletein sys user?
i can insert through select statement like below query
select 12 c1,'a' c2
from dual;
Q:Date calculation to show how many weeks and days an employee has been working here...
ReplyDeleteANS:
select first_name , floor((sysdate-hire_date)/7) as weeks, floor(mod(sysdate-hire_date,7)) as days
from employees
where department_id = 50;
Q: About floor function..
ReplyDeleteThe following example uses FLOOR()
to display the absolute value of 1.8 and -2.2, respectively:
select floor(1.8) as A, floor(-2.2) as B from dual;
Output:
A B
1 -3
Q:is it possible to insert the values in dual table?
ReplyDeleteAns:
yes, we can insert the values in dual table by sys user...
example:
insert into dual values(10);
but while selecting all column it will show only 'x'... in dual table the value stored as 'x'
Rank
ReplyDeleteit ranks all the rows in column
syntax:
rank() over(order by column )
example:
rank() over(order by salary desc)
rank
ReplyDeleteit also ranks only for groups
so,we have to partition the column into groups
for group ranking
example:to rank the specific dept
sytax:
rank() over(partition by (column to be partitioned) order by (column which is used to sort) )
rank() over(partition by department_id
order by salary desc)
SQL Query to rank
ReplyDelete1.based on overall salary of employees
2.based on the salary of specific departments
select FIRST_NAME,
EMPLOYEE_ID,SALARY,
DEPARTMENT_ID,
rank() over(order by salary desc) salary_rank,
rank() over(partition by department_id order by salary desc) dept_wise_rank
from employees
group by(FIRST_NAME,EMPLOYEE_ID, SALARY, DEPARTMENT_ID)
order by 5;
Dense_rank:
ReplyDelete1.little bit different from rank()
syntax:dense_rank() over(order by column )
syntax:dense_rank() over(partition by (column to be partitioned) order by (column which is used to sort) )
SQL Query to dense_rank(it does not leave a gap)
ReplyDelete1.based on overall salary of employees
2.based on the salary of specific departments
select FIRST_NAME,
EMPLOYEE_ID,SALARY,
DEPARTMENT_ID,
dense_rank() over(order by salary desc) salary_rank,
dense_rank() over(partition by department_id order by salary desc) dept_wise_rank
from employees
group by(FIRST_NAME,EMPLOYEE_ID, SALARY, DEPARTMENT_ID)
order by 5;
difference between rank and dense_rank
ReplyDeleteRank()
that RANK() leaves gaps
while ranking the records whereas
DENSE_RANK() doesn't leave any gaps.
The easiest way to understand is by having an example, so consider the following data set:
24000
17000
17000
14000
RANK() would return the following rank:
1
2
2
4
DENSE_RANK() would return the following rank:
1
2
2
3
when i executed this query in sys user..i got error
ReplyDeleteinsert into dual values(10);
error:ORA-12899: value too large for column "SYS"."DUAL"."DUMMY" (actual: 2, maximum: 1)
why error?
how to select only even number empid records?
ReplyDeleteans:
Select * from employees where mod(employee_id,2)=0;
how to find 2nd occurrence of a character in a string?
ReplyDeleteans:
SELECT INSTR('malayalam','a',1,2) FROM DUAL;
here:
malayalam - Given string
a - searching character
1 - starting position of searching
2 - n th occurrence of the character
OR
select instr('malayalam','a',instr('malayalam','a')+1) from dual;
Selecting records from employees table,whose experience should be greater than 20 years?
ReplyDeleteans:
Select * from EMPLOYEES where MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12 >= 20;
How we get last three character from the string?
ReplyDeleteAnswer:
select first_name, substr(first_name, -3,3) from employees;
How to find the second occurance of e in the column_name?
ReplyDeleteAnswer:
select first_name, instr(first_name,'e',1,2) from employees;
HOW TO FIND THE 20 YEARS EXPERIENCE EMP DETAIL?
ReplyDeleteSELECT * FROM EMPLOYEES WHERE MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12>=20;
How to find the 6 and 8 character of data only ?
ReplyDeleteSELECT FIRST_NAME FROM EMPLOYEES WHERE LENGTH(FIRST_NAME)BETWEEN 6 AND 8;
how to get employees id in even numbers?
ReplyDeleteSelect * from employees where mod(employee_id,2)=0;
how to display the 6to8 chars data in db?
ReplyDeleteselect first_name from employees where length(first_name) between 6 and 8;
how to get a second occurance char in db using instr?
ReplyDeleteselect first_name, instr(first_name,'a',1,2) from employees;
Q:Whats the difference between MySQL and SQL?
ReplyDeleteAns:
SQL is the language to write queries in.
SQL = Structured Query Language,
MySQL = Open Source Relational Database System
MSSQL = Microsoft SQL Server
mysql is an open source sql product for the linux unix platform
mssql is a properity sql product by microsoft
how to insert date n timestamp in a table
ReplyDeleteHow to remove all zeros from salary in employees table?
ReplyDeleteAns:
select salary,replace(salary,0,NULL) from EMPLOYEES;
HOW to eliminate a character from a string?
ReplyDeleteans:
select First_name,replace(First_Name,'a',NULL) from employees;
Finding number of character in a string
ReplyDeleteAns:
select Length('malayalam')-length(replace('malayalam','a',NULL)) from dual;
To display the even numbers of employee_id.
ReplyDeleteAnswer:
select * from employees where mod(employee_id,2)=0;
how to eliminate the 'a' character from 'diana'?
ReplyDeleteAnswer:
select 'diana', replace('diana','a') from dual;
Selecting records from employees table,whose experience should be greater than 20 years?
ReplyDeleteANOTHER WAY TO FIND:
ANSWER:
SELECT * FROM EMPLOYEES WHERE HIRE_DATE<='30-SEP-1991';
How to display the data length is 6 only?
ReplyDeleteselect first_name,length(first_name) from employees where length(first_name)=6;
How to display the first five character but without using sub string?
ReplyDeleteselect first_name,rpad(first_name,5) from employees;
Type: To assign a single column to variable
ReplyDeleteRow type:To assign a entire row(i.e all columns) to variable
kindly send the sql dumps to sathishemailbox@gmail.com
ReplyDeleteDifference between procedures and functions?
ReplyDeleteFunctions
1. It contains a return clause.
2. It can accept arguments and must return a value.
3. It computes a value.
4. It cannot perform a DML operation when it is called in a SQL statement.
5. We can assign a function to a variable.
6. Must return a single value.
Procedures
1. It does not contains a return clause.
2. It can accept arguments and can return a value.
3. It performs an action.
4. It can perform a DML operation.
5. We cannot assign a procedure to a variable.
6. It can return one or more values or no value.
Advantages of Packages.
ReplyDelete1. We can have multiple subprograms of same name which differs in number of arguments or data types in a package.
2. We can declare global variables and we can use in any window.
3. It improves performance i.e. the entire package is loaded into memory when ever we call it will retrieve from memory but not from data base.
4. Modularity - It encapsulates logically related subprograms in to a single named module.
5. It hides information i.e. code is hidden in the package body.
6. Easy to understand the design i.e. It consists of Specification and body separately and we will execute it separately.
What is a Cursor?
ReplyDeleteCursor - It is a SQL private work area. It opens an area of memory and uses it to create sql statements and to store processing information.
There are two types of Cursors.
1. Implicit Cursor
2. Explicit Cursor
Implicit Cursor - It is declared by oracle implicitly for all DML and PL/SQL select statements, including queries that return only one row.
Explicit Cursor - If a query returns no rows, one row or more than one row then programmer has to explicitly declare a cursor called as
explicit cursor and manipulated through specific statements in the block's executable actions.
Steps of cursor
1. Declare
2. Open
3. Fetch
4. Print
5. Close
Types of cursor attribites :-
1. FOUND
2. NOTFOUND
3. ISOPEN
4. ROWCOUNT
find output
ReplyDeleteSELECT TO_DATE('30/09/08', ’dd/mm/yy’)+12 from dual;
can any one help me to find this output of this query
ReplyDeleteselect to_char('30-oct-2011','dd/mm/yyyy') from dual;
or select to_char(30-oct-2011,'dd/mm/yyyy') from dual
assignment how to arrange the datas based on the joining day like sunday monday
ReplyDeleteANS: select Employee_id,First_name||last_name as name,Email,Phone_number,to_char(hire_date,'day') as joinigday,hire_date,Job_id,salary,commission_pct,manager_id,department_id from employees order by decode((to_char(hire_date,'dy')),'sun',1,'mon',2,'tue',3,'wed',4,'thu',5,'fri',6,'sat',7,8) asc;
if there is easy step kindly post and mention this post name....
To find system date
ReplyDeleteselect sysdate from dual;
please send me the thump for sql and plsql
ReplyDeletemy mail id is saravannap@gmail.com
assignment how to arrange the datas based on the joining day like sunday monday?
ReplyDeleteanother way
ANS:
select FIRST_NAME,LAST_NAME,SALARY,to_char(HIRE_DATE,'day') from employees order by TO_CHAR(Hire_date,'D') asc;
can any one explain the functions.
ReplyDeleteNVL(rollnumber,'Is null')
NVL2(rollnumber,'Not null','Is null')
here rollnumber datatype is number
NVL(rollnumber, 'Is null')
ReplyDeleteThe SQL statement above would return 'Is null' if the roll number field contained a null value. Otherwise, it would return the roll number.
The syntax for the NVL2 function is:
NVL2( string1, value_if_NOT_null, value_if_null )
string1 is the string to test for a null value.
value_if_NOT_null is the value returned if string1 is not null.
value_if_null is the value returned if string1 is null.
The SQL statement above would return 'IS null' if the roll number field contained a null value. Otherwise, it would return the 'Not null'.
Cursor Loops.
ReplyDeleteThere is another technique which is commonly used to control Loop.. End Loop within PL/SQL block is the FOR variable IN construct.
Here FOR automatically creates the memory variable of %rowtype.Each record in the opened cursor becomes a value for the memory variable of the %rowtype.
The FOR ensures that a row from the cursor is loaded in the declared memory variable and the loop executes once.This goes on untill all the rows of the cursor have been loaded into memory variable.After this the loop stops.
syntax
FOR memory variable IN cursorname
BEGIN
FOR item IN
( SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%'
AND manager_id > 120 )
LOOP
DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' ||
item.job_id);
END LOOP;
END;
/
Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record. The sequence of statements inside the loop is executed once for each row that satisfies the query. When you leave the loop, the cursor is closed automatically.
how to get second maximum salary in employee table?
ReplyDeleteans
select max(salary) from employees
where salary not in
(select max(salary) from employees);
task: create a function which returns first name and salary for a given employee id
ReplyDeletecreate or replace function fn_eid_sal(eid in number)
return varchar2
as
c varchar2(60);
begin
select 'first name is '||first_name||chr(10)||'salary is '||salary
into c
from employees
where employee_id=eid;
return c;
end;
/
to see the output of above function do following steps
ReplyDeletein sqlplus cmd
1.@function path......then function created
2.select fn_eid_sal(160) from dual
output:
FN_EID_SAL(160)
----------------------
first name is Louise
salary is 7500
task: create a procedure which returns first name and salary for a given employee id
ReplyDeletecreate or replace procedure pn_eid_sal(eid in number)
as
c varchar2(60);
begin
select 'first name is '||first_name||chr(10)||'salary is '||salary
into c
from employees
where employee_id=eid;
dbms_output.put_line(c);
end;
/
to see the output of above procedure do following steps
in sqlplus cmd
1.@procedure path......then procedure created
2.exec pn_eid_sal(100)
output:
first name is Steven
salary is 24000
PL/SQL procedure successfully completed.
small diff b/n function and procedure
ReplyDeletefn: it returns the value,so it prints output without using dbms_output.put_line
pro:whereas we need dbms_output.put_line in sql or declare bind variable in sql plus to print..
so its quite tedious in procedure ..
conclusion:use function if u want to return data
use procedure if u need to print data
ASSIGNMENT: How to get duplicates only?
ReplyDeleteANS:
select salary,count(salary) from employees
group by salary
having count(salary)>1;
ANS:
It shows the duplicate salary..
pls sent SQL THUMS
ReplyDeletethis is my mail id "maheshsaturns@gmail.com"
This comment has been removed by the author.
ReplyDeletehow to Display the 10th maximum salary ?
ReplyDeleteANS:
select min(salary) from (select salary from employees group by salary order by salary desc) where rownum<=10;
SQL PLUS COMMANDS:
ReplyDelete1.!
2.$
3.&(1-n)
4.. (period)
5./
6.ACCEPT
7.APPEND
8.BREAK
9.BTITLE
10.CHANGE
11.CLEAR
12.COLUMN
13.COMPUTE
14.CONNECT
15.COPY
16.DEFINE
17. DISCONNECT
18. EXECUTE
19.EXIT
20.GET
21.HOST
22.INPUT (I)
23.LIST (L)
24.PASSWORD
25.PAUSE
26.PRINT
27.PROMPT
28.REMARK (REM)
29.REPFOOTER
30.REPHEADER
31.RUN
32.SAVE
33.SPOOL
34.START
35.STORE
36.TIMING
37.TTITLE
38.UNDEFINE
39.VARIABLE
40.WHENEVER SQLERROR|
Rational Rose:
ReplyDeleteROSE: Rational Object Oriented Software Engineering
Different diagrams:
Use Case
Collaboration
Sequence
Class
State chart
Activity
Component
Deployment
CONFIGURATION MANAGEMENT TOOL:
ReplyDeleteConfiguration management (CM) is a field of management that focuses on establishing and maintaining consistency of a system or product's performance and its functional and physical attributes with its requirements, design, and operational information throughout its life.
Configuration management was first developed by the United States Air Force for the Department of Defense in the 1950s as a technical management discipline of hardware.
Source controls tools:
1. SVN (Subversion)
2. CVS (Concurrent Version System)
3. VSS (Visual SourceSafe)
How to add primary key for an existing column, which is having some NULL values
ReplyDeleteIs it possible to display columns by using it's position, without using column_name.
ReplyDeleteif it's possible, how???
PRINT A TO Z AND Z TO A
ReplyDeleteDECLARE
J NUMBER:=90;
BEGIN
FOR I IN 65..90 LOOP
DBMS_OUTPUT.PUT_LINE(CHR(I)||' '||CHR(J));
J:=J-1;
END LOOP;
END;
/
ANSWER:
A Z
B Y
C X
D W
E V
F U
G T
H S
I R
J Q
K P
L O
M N
N M
O L
P K
Q J
R I
S H
T G
U F
V E
W D
X C
Y B
Z A
DISPLAY ONLY THE COLUMN NAMES IN A EMPLOYEES TABLE
ReplyDeleteSELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EMPLOYEES';
ANS:
COLUMN_NAME
---------------------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID
11 rows selected.
Tell some new features in PL/SQL in 10g
ReplyDelete- Regular expression functions REGEXP_LIKE , REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR
- Compile time warnings
- Conditional compilation
- Improvement to native compilation
- BINARY_INTEGER made similar to PLS_INTEGER
- INDICES OF , VALUES OF in FORALL lets you work on non-consecutive indices
- Quoting mechanism . Instead of quoting single quotes twice everytime, give your own delimiter to go on using single quotes.
Ex: q'!I'm a string, you're a string.!'
- Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN
- Implicit conversion between CLOB and NCLOB
- Improved Overloading
- New datatypes BINARY_FLOAT, BINARY_DOUBLE
- Global optimization enabled
- PLS_INTEGER range increased to 32bit
- DYNAMIC WRAP using DBMS_DDL
Query for displaying the maximum salary from each department.
ReplyDeleteSELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;
Is there any other way to solve it?
This comment has been removed by the author.
ReplyDeleteData present in dual table.
ReplyDeleteselect * from dual;
just check it out;
(A-B)u(B-A)
ReplyDeleteselect A from t1
minus
select B from t2
union
(select B from t2
minus
select A from t1);
T1 T2
A B
1 1
2 2
3 3
4 5
output:
A
4
5
Difference Between Rowid and rownum?
ReplyDeleteOracle ROWID uniquely identifies where a row resides on disk. The information in a ROWID gives Oracle everything it needs to find your row, the disk number, the cylinder, block and offset into the block. It actually the physical address.
ROWID can be used to fetch a row from database.
ROWID is permanent.
Whereas,
ROWNUM is a "pseudo-column",
ROWNUM is used to restrict the number of rows in a query.
ROWNUM is temporary.
How to display the Third Row of a table?
ReplyDeleteAnswer:
select * from T1
where Rowid =
(select rowid from T1 where rownum <=3
minus
select rowid from T1 where rownum <=2);
This will work! :o)
What is Dynamic SQL?
ReplyDeleteDynamic SQL is an enhanced form of Structured Query Language (SQL) that, unlike standard (or static) SQL, facilitates the automatic generation and execution of program statements. This can be helpful when it is necessary to write code that can adjust to varying databases, conditions, or servers. It also makes it easier to automate tasks that are repeated many times.
Dynamic SQL statements are stored as strings of characters that are entered when the program runs. They can be entered by the programmer or generated by the program itself, but unlike static SQL statements, they are not embedded in the source program. Also in contrast to static SQL statements, dynamic SQL statements can change from one execution to the next.
Dynamic SQL statements can be written by people with comparatively little programming experience, because the program does most of the actual generation of the code. A potential problem is reduced performance (increased processing time) if there is too much dynamic SQL running at any given time.
What is the difference between Cursor and ref_cursor?
ReplyDeleteAt the most "basic level", they are the same. A "normal" plsql cursor is static in defintion. Ref cursors may be dynamically opened or opened based on logic. Declare type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then open l_cursor for select * from dept;
else open l_cursor for select * from dual;
end if; open c;
end;
/
Given that block of code -- you see perhaps the most "salient" difference -- no matter how many times you run that block -- cursor C will always be select * from dual. The ref cursor can be anything. Another difference is a ref cursor can be returned to a client. a plsql "cursor cursor" cannot be returned to a client.
aA cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)
A ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.
Static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to - returning result sets to clients - when there is NO other efficient/effective means of achieving the goal that is, you want to use static SQL (with implicit cursors really) first and use a ref cursor only when you absolutely have to
What is the difference between Functions and procedures?
ReplyDelete1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
Â
2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
Â
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
Difference between views and Materialized veiw?
ReplyDeleteA view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.
You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
A materialized view can be stored in the same database as its base table(s) or in a different database.
Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment
Global Temporary Tables
ReplyDeleteApplications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.
• Creation of Temporary Global Tables
•
• Miscellaneous Features
Creation of Global Temporary Tables
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;
In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
Miscellaneous Features
• If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• There are a number of restrictions related to temporary tables but these are version specific.
Difference between views and Materialized veiw?
ReplyDeleteA view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition.
A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.
You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
A materialized view can be stored in the same database as its base table(s) or in a different database.
Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment
What is the difference between Functions and procedures?
ReplyDelete1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
Â
2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
Â
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
how to print numbers from 1 to 10 using dual?
ReplyDeleteAnswer to print numbers from 1 to 10 using dual:
ReplyDeleteselect level as A from dual
connect by level<=10;
This is working!
TWO WAYS TO EXECUTE FUNCTIONS
ReplyDelete1) SELECT FUNCTION_NAME(ARGUMENTS) FROM DUAL;
2) EXEC DBMS_OUTPUT.PUT_LINE(FUNCTION_NAME(ARGUMENTS));
CREATE OR REPLACE FUNCTION FN_ADD(A NUMBER,B IN NUMBER)RETURN NUMBER
AS
C NUMBER;
BEGIN
C:=A+B;
RETURN C;
END;
/
SQL>@E:\PLSQL\FN_ADD.SQL;
SQL> SELECT FN_ADD(100,100) FROM DUAL;
FN_ADD(100,100)
---------------
200
SQL> EXEC DBMS_OUTPUT.PUT_LINE(FN_ADD(100,100));
200
PL/SQL procedure successfully completed.
--To find no.of A's in MALAYALAM--
ReplyDeleteselect 'MALAYALAM', length('MALAYALAM') - length(replace('MALAYALAM','A',NULL)) "No.Of.A's" from dual;
o/p:
'MALAYALAM' No.Of.A's
MALAYALAM 4
To find employees having more than 20 yrs exp:
ReplyDeleteselect concat(first_name,last_name),hire_date from employees where months_between(sysdate,hire_date)/12>=20 ;
To print employee names having six characters:
ReplyDeleteANS: select first_name from employees where length(first_name)=6;
-- ** LOCAL PROCEDURE AND FUNCTION **
ReplyDelete-- ****** PL/SQL BLOCK ******
DECLARE
-- LOCAL PROCEDURE DECLARATION
PROCEDURE PROC
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END PROC;
-- LOCAL FUNCTION DECLARATION
FUNCTION FUN RETURN VARCHAR2
AS
BEGIN
RETURN CONCAT('HELLO','WORLD');
END FUN;
BEGIN
PROC();
DBMS_OUTPUT.PUT_LINE(FUN());
END;
/
SQL> @E:\PLSQL\PL_LOC_PR_FN.SQL
HELLO WORLD
HELLOWORLD
To display employee's day of joining:
ReplyDeleteANS:
select
concat(first_name,last_name),
to_char(hire_date,'day')
from
employees;
ASSIGNMENT:
ReplyDeleteHow to get a unmatched records in table joins?
ANS:
select
ss.sname,
cc.cid
from
s ss full outer join
c cc
on
ss.cid=cc.cid
minus
(
select
ss.sname,
cc.cid
from
s ss,
c cc
where
ss.cid=cc.cid
)
1. Differences b/w TRUNCATE & DELETE Commands:
ReplyDeleteTRUNCATE:
1.A DDL Command,
2.Can't be used with WHERE clause.
3.Can't be Rolled back.
4.Drops the entire table contents when executed but not the table. [Note:DROP commnad deletes the entire contents along with the table]
5.Truncate is faster.
6.Frees-up the Stroage Space
7. Doesn't create a log file
8.Doesn't return the no. of rows deleted when queried.
9.Trigger doesn't get fired incase of Truncate.
10.Data gets romoved directly without copying it into the Rollback tablespace when truncate is executed.
DELETE:
1.DML Command.
2.Can be used with WHERE clause.
3.Can be Rolled back.
4.Deletes table contents row by row when executed.
5.Delete is slow
6.Doesn't frees-up the Stroage Space
7.Creates the log file
8.Returns the no. of rows deleted when queried.
9. Trigger gets fired incase of Delete.
10. Date gets copied into the Rollback tablespace after Delete is executed
Difference between DBMS & RDBMS?
ReplyDeleteDBMS:
1) In dbms no relationship concept.
2) It supports Single User only.
3) It treats Data as Files internally.
4) It supports 3 rules of E.F.CODD out off 12 rules.
5) It requires low Software and Hardware Requirements.
6) FoxPro, IMS are Examples.
RDBMS:
1)It is used to establish the relationship concept between two database objects, i.e, tables.
2) It supports multiple users.
3) It treats data as Tables internally.
4) It supports minimum 6 rules of E.F.CODD.
5) It requires High software and hardware requirements.
6) SQL-Server, Oracle are examples
with out using full outter join keyword hoew to get a result?
ReplyDeleteANS:
select
ss.sname,
cc.cid
from
s ss,
c cc
where
ss.cid=cc.cid(+)
union
( select
ss.sname,
cc.cid
from
s ss,
c cc
where
ss.cid(+)=cc.cid )
Hi, I missed my class today (SQL morning 8:30 - 10). Can any one list down the topics that are taken today... pls!
ReplyDeleteIn Todays SQL class(11/10/2011)
ReplyDeleteTopics covered are SUBQURIES only
This comment has been removed by the author.
ReplyDeletefetching 2nd max salary????
ReplyDeleteans:
select max(salary) from employees where salary not in (select max(salary) from employees);
or
select salary from (select salary,dense_rank() over(order by salary desc) r from employees) where r = 2;
or
select min(salary) from (
select salary from (select distinct salary from employees order by salary desc) where rownum <=2);
finding max salary for each and every department??
ReplyDeleteans:
select * from employees e where salary in (select max(salary) from employees ee where nvl(ee.department_id,0) = nvl(e.department_id,0) group by department_id) order by salary desc
CREATE OR REPLACE TRIGGER TRG_T1
ReplyDeleteBEFORE INSERT OR UPDATE OR DELETE ON T1
for each row
BEGIN
if inserting then
INSERT INTO TEST VALUES(:new.a);
elsif updating then
update test set b=:new.a where b = :old.a;
else
delete from test where b = :old.a;
end if;
END;
/
CREATE OR REPLACE TRIGGER TRG_T1
ReplyDeleteBEFORE INSERT OR UPDATE OR DELETE ON T1
for each row
BEGIN
if inserting then
INSERT INTO TEST VALUES(:new.a);
elsif updating then
update test set b=:new.a where b = :old.a;
else
delete from test where b = :old.a;
end if;
END;
/
finding duplicate department in employees table?
ReplyDeleteans:
select department_id as d from (select department_id from employees e group by department_id having count(department_id) > 1)
This comment has been removed by the author.
ReplyDeleteselect ss.sname,cc.cis from s ss, c cc where ss.cid(+)=cc.cid(+) work same as full join
ReplyDeletefinding max salary in each and every department
ReplyDeleteselect * from employee where salary in (select sal from (select department_id,max(salary) sal from employee group by department_id));
To replace first 'a' with 'b' in malayalam
ReplyDeleteselect'malayalam',replace(substr('malayalam',1,2),'a','b')|| substr('malayalam',3) from dual;
output:
malayalam mblayalam
To replace first 'a' with 'b' in malayalam
ReplyDeleteselect 'malayalam' ,replace('mal','a','b')||substr('malayalam',4,9) from dual;
find 3rd occurence of 'a' in malayalam
ReplyDeleteselect instr('malayalam','a',1,3) from dual
To find employee id getting 2nd maximum salray
ReplyDeleteselect employee_id,salary from employees where salary in(select max(salary) from employees where salary < (select max(salary) from employees))
to get employee name with 6 characters??
ReplyDeleteselect first_name from employees where length(first_name)=6
How to change the word malayalam to mblayalam?
ReplyDeleteQuery: select 'malayalam',
replace(substr('malayalam',1,2),'a','b')||substr('malayalam',3,7)
from
dual;
Output:
malayalam mblayalam
-- ******** BREAK AND COMPUTE *****************
ReplyDelete-- CREATE EEMP TABLE AND INSERT THE VALUES OR --USE THE EMPLOYEES TABLE
--TRY THIS IN SQLPLUS
SQL> SELECT * FROM EEMP ORDER BY JOB_ID;
ID NAME SALARY JOB_ID
---- ------------ -------- ---------
13 KUMAR 2200 IT_ADMIN
11 SANKAR 20000 IT_ADMIN
14 RAM 28000 IT_PRG
10 SIVA 10000 IT_PRG
12 SIVASANKAR 30000 IT_PRG
SQL> SET PAGESIZE 500
SQL> BREAK ON JOB_ID SKIP1
SQL> COMPUTE MAX MIN SUM AVG OF SALARY ON JOB_ID
SQL> SELECT ID,NAME,SALARY,JOB_ID FROM EEMP ORDER BY JOB_ID;
ID NAME SALARY JOB_ID
---- ----------- ---------- ---------
13 KUMAR 2200 IT_ADMIN
11 SANKAR 20000
-------- **********
11100 avg
2200 minimum
20000 maximum
22200 sum
14 RAM 28000 IT_PRG
10 SIVA 10000
12 SIVASANKAR 30000
--------- ***********
22666.6667 avg
10000 minimum
30000 maximum
68000 sum
SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTES
computes cleared
ASSIGN:how to delete a duplicate records in a table
ReplyDeleteANS:
Table t1 contains datas like
A B C
1 1 1
1 1 1
1 1 1
1 1 1
1 1 1
2 1 2
3 1 2
delete from t1 where rowid not in(select max(rowid) from t1 group by a,b,c)
output:
A B C
1 1 1
2 1 2
3 1 2
How to get a only duplicate values from a table?
ReplyDeleteTable t1 contains data like
A
2
2
2
2
1
1
1
5
select a from t2 where rowid not in(select max(rowid) from t2 group by a)
output
A
1
1
2
2
2