-
How to give select privilege on all the objects owned by an user(say user1) to another user say(user2) using a single sql statement?
yagneswarasankar
- Nov 24th, 2010
SQL> conn sample/sample;Connected.SQL> select count(1) from girija.employees;select count(1) from girija.employees &n...
-
SQL
What are the data types in oracle?
MahanamShakyamuni
- Nov 19th, 2010
In SQL, there are mainly varchar2, char, number, date...then each of them has subtypes like number has integer, real etc..Following website gives all details:For SQL datatypes refer:http://download....
pradeepnagaraja
- Nov 18th, 2010
Different data type used in Oracle are number, char, varchar, varchar2, clob, blob, long.
-
Data Conversion
Have 2 table Emp1 (Column Name: Ename), 2nd table Emp2 (Column Name: First and Column Name: LastTable Name :Emp1ENameHunt, MichaelGraham, SmithNelson, Ryan Table Name: Emp2First LastMichael HuntSmith GrahamRyan NelsonNeed to write a SQL query to convert the data from Emp1 to Emp2 in this way.Can any body help me.Thanks
yagneswarasankar
- Nov 16th, 2010
SQL> select * from name;NAME--------------------Girija,Sankar 1* create table split_name as(select substr(name,1,(instr(name,',') -1)) "First",substr(name,(instSQL> /Tabl...
katgeektalk
- Aug 22nd, 2010
SELECT substr(ename instr(ename, ',')+2) First ,substr(ename, 1, instr(ename, ',')-1) Last FROM emp1;has to be +2 because of the space after comma
-
Enlarge Work Area Size
How to enlarge the size of work area in iSQL*plus?
viji_info
- Oct 22nd, 2010
If you are working isql plus..Go to the preference which lay on the right hand corner, then Edit the column width and height as much you want... Try it.
-
View - Base Tables
Does View occupy memory? If we delete the base table of existing view, What happens?
Read Best Answer
Editorial / Best Answer
Mad Hatter- Member Since Nov-2008 | Nov 4th, 2008
View does not occupy memory, but Oracle keeps view code in its dictionary (you can check in user_views, for example)
If you delete the base table, the view becomes INVALID.
In order to make it valid again, you have to ether build the deleted table again, or create anotehr view with the same name and structure as deleted table, or create synonym with the same name as deleted table, which will point to the table with the same structure.pkshve
- Sep 12th, 2010
Views are database objects - they are just SQL code stored in database which will be executed every single time the view is called. Database will check the existance of columns and tables in the base ...
manimehra905
- May 18th, 2010
View does not occupy memory as it only provides a way for watching multiple views of an object: Table. Moreover, it is a virtual table which does not take space.It provides DATA HIDING.For e.g. i...
-
Can you use a reference cursor as an input parameter in a procedure with out declaring it explicitly?
gwilson5
- Sep 4th, 2010
A cursor variable is a variable that points to or refernces an underlying cursor.? Unlike an explicit cursor, which names the PL/SQL work area for the result set, a cursor variable is a reference to ...
-
Analytical Function
WE using Last_value()analytical function but the result do not match according to me?
Raj_P
- Sep 1st, 2010
I am working on Oracle 9i and as far as I know the function "Last_Value" doesnt work in 9i. We are using the "First_Value" function with order by clause using descending order. Select First_Value(FieldName) Over (Partition by FieldName Order by TrandDate Desc) From .
Avashisth
- Feb 20th, 2010
Analytical function are the builtin function in Oracle, so during execution it will be faster then other simple function. These function are basically used for reporting and most of the time to improv...
-
Column Level Constraint
What are Column Level Constraint and Table Level Constraint? How do they differ from one another?
katgeektalk
- Aug 22nd, 2010
You can create the constarin at either the column or table level. Constrain defined at the column level are included when the column is defined. Table-level constrains are defined at the end of the ta...
hindol_saha
- Jan 20th, 2010
The NOT NULL constraint can be define only at column level,It enforces that a value must be define for this column such tht the column may not be NULL for any row in any circumstance.
-
How can we find the number of day's between any two given date's by using the dual table only?
saishradha
- Aug 13th, 2010
Below is the correct query according to me..
select to_date('19-aug-2010', 'dd-mon-yyyy') - to_date('10-aug-2010', 'dd-mon-yyyy') as numberofdays from dual;
Let me know if it does not work..
Sairam..
vikas_lomate
- Aug 13th, 2010
Use below query to find number of days between two given dates -SQL> select to_date('30-aug-2010','dd-mon-rrrr') - to_date('20-aug-2010','dd-mon-rrrr') num_days from dual;NUM_DAYS
----------
10
SQL>
-
HAVING vs WHERE Clause
Where clause restricts rows, what does having clause restricts ?1. only group results.2. rows results.3. both rows and groups result.
Read Best Answer
Editorial / Best Answer
ajaysinghnegi- Member Since Mar-2009 | Mar 27th, 2009
1) HAVING clause can only be used to filter out the aggegate functions whereas WHERE clause cannot filter out the aggegate functions.
2) HAVING clause can be used with GROUP BY function where as WHERE clause cannot be used with group by function.gubba123
- Aug 8th, 2010
Example: Write a query to display the Managers who are working for more than one Departments:Select emp_id, count(distinct dept_id) From emp Where...
Tartauris
- Feb 16th, 2010
Having is used for agregate results while where is used for unagregated results.
-
-
SQL Queries- Say for Eg.Two Tables - EMP and DeptEMP Tables consist of Empno, Deptno, Mgrid, Empname, LocationDept Table Consist of Deptno, Deptname.1. Write a query to select Department name for which there is more that 20 employees in a specific Department. List the deptname and no of employees.Deptno in Emp table is the primary key for deptno column in Dept Table.2. Write a query to select Manager name for each employee. MGRID column in the emp table is the empid of the Manager. There should be self join query on the emp table.
bhagyashree010
- Jul 20th, 2010
SELECT COUNT(e.ename), d.dname FROM emp e, dept d WHERE d.deptno=e.deptnoGROUP BY d.dname HAVING COUNT(e.ename) > 20It is the same...only diff is...use "dname" in group by clause , not "deptno"Thanks
isha123
- Aug 9th, 2006
Select e.empno "Subordinate",e.ename "Emp Name",e.mgrID "ManagerID",m.ename "Manager Name",m.empno "Employee Number" from emp e JOIN emp m where (e.mgrid=m.empno);
-
When we retrieve data from table in SQL Queries, and want to select second, third,........rows from table, then what is the coding of that program?
sau326
- Jul 19th, 2010
SELECT TOP 2 employeenumber FROM employees WHERE employeenumber NOT IN(SELECT TOP 1 employeenumber FROM employees)Regards,Saurabh
Imran_Javed
- May 9th, 2008
SELECT *FROM (SELECT empno, ename, sal , rank() over(ORDER BY empno) e FROM emp)WHERE e IN (2,3);
-
Write a query to update third column such that
There is a table having the following columns :-student id marks1 marks2 maxmarks1 10 20 202 25 30 303 30 10 304 35 25 355 20 40 40write a query to update column maxmarks such that maxmarks column contains whatever be the greater value among marks1...
indra.prasad
- Jul 12th, 2010
CREATE TABLE aa(name varchar(10), marks1 int, marks2 int, maxmarks int);INSERT INTO aa VALUES ('a',10,20,null);INSERT INTO aa VALUES ('b',20,25,null);UPDATE aa SET maxmarks= CASE WHEN marks1 > marks2 THEN marks1 ELSE marks2 END;
Palash Biswas
- May 20th, 2010
Declare @tbl_Test TABLE([Name] Nvarchar(255),Mark1 int,mark2 int,maxmark int) Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('a',50,20) Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('b',10,...
-
SQL Buffer
Which command displays the SQL command in the SQL buffer, and then executes it?
ajitsankpal
- Jun 29th, 2010
RUN is the command which displays the query in SQL buffer and executes it.
-
-
Change Default Display
How to change the default display? For deptno 10 show "Financial Department", for deptno 20 show "Account Department", for deptno 30 show "Management Information System", for deptno 40 show "Electronic Data Processing"
kiran_marla
- Jun 20th, 2010
Use decoding to display names.select decode(deptno,10,'Financial Department',20,'Account Department',30,'Management Information System',40,'Electronic Data Processing'...
Kushy14
- Jun 19th, 2010
SELECT DECODE (deptno,10,'Financial Department',20,'Account Department',30,'Management Information System',40,'Electronic Data Processing') FROM dept;
-
How do i get the dates of a particular month using the SQL query statement?
rashok1983
- May 24th, 2010
SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1,rownum,TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum from all_objectsWHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/30/2003', 'MM/DD/YYYY')
MANU
- May 18th, 2006
Hi,You can use date functions available to get the dates of a particular monthEx: SELECT col1 FROM TABLE A WHERE TO_CHAR(DATECOLUMN,'MONYY') = 'JUN05'ORSELECT * FROM TABLE A WHERE TO_CHAR(DATECOLUMN,'MON') = 'FEB'Hope this was useful.....
-
Display Time in SQL
How to display current system time in SQL.
g.sreemayee
- May 19th, 2010
select to_char(sysdate,'HH24:MM:SS') from dual;select current_timestamp from dual;These two will give the current time of the system.1. ll give the time only.2. ll give the time with the date.
-
Average Department Salary
How to Get the employee details whose salary is less than the average salary of the department.
dj_dj_dj
- May 20th, 2010
select emp_name, avg(salary)from employees group by emp_namehaving avg(salary) < (Select avg(salary) &...
g.sreemayee
- May 19th, 2010
SELECT * FROM emp e WHERE sal> (SELECT AVG(sal) FROM emp GROUP BY deptno HAVING deptno=e.deptno) ORDER BY deptno;
Oracle SQL Interview Questions
Read Best Answer
Ans