-
Giving two tables:EmpCustEmpName, CustID, EmpIDApril, 1, AApril, 3, AApril, 2, ABruce, 4, BBruce, 5, BCarson, 2, CCarson, 1, CCarson, 3, CDavid, 2, DCustReviewCustID, ReviewDate, EmpID1, 01-05-06, A1, 12-13-05, C2, 07-04-04, A2, 05-21-03, C2, 04-02-03, D3, 10-10-02, A3, 02-01-06, C4, 01-01-06, B5, 12-12-04, BWrite a SQL statement that return each EmpName and LATEST ReviewDate. Result should look like this:EmpName, ReviewDateApril, 01-05-06Bruce, 01-01-06...
vishalb
- Apr 27th, 2008
Tested!SELECT E.EmpName, Max(C.Reviewdate) from EmpCust E Join CustReview C on e.Empid=c.empid group by c.empid, E.EmpName
ratna_ponnapalli
- Apr 11th, 2006
Select a.empname,max(b.reviewdate) from empcust a,custreview b where a.custid=b.custid group by a.empname;
-
Using the following Customer, Country and Orders tables: Customer Table c_id c_name c_country_id 1 John 1 Country Table country_id Name 1 US 2 UK Orders Table o_id c_id Qty o_deliver_country_id 1 1 10 1 2 1 20 2
I need the following output in SQLc_id c_name c_country_lives qty o_deliver_country 1 john US 10 US 2 john US 20 UK
Imran_Javed
- May 9th, 2008
SELECT c.c_id, c.c_name , ord.name AS "c_country_lives", ord.qty, ord.name AS "o_deliver_country"FROM customer_c c, (SELECT * &nbs...
vishalb
- Apr 25th, 2008
SELECT X.o_id, X.c_name, X.[Name], X.qty , MT.[name] FROM(SELECT OT.o_id, CT.c_name, CUT.[Name], OT.qty , OT.o_deliver_country_id --CUT.o_deliver_countryFROM Orders OT Join Customer CT on ...
-
I have a customer table custid,Trade,Amount three fields are there custid dont have unique value, now trade B & S values. I want to Show sum of amount of custid, for both the trade in one line Like cust id,Btrade,Bamount,Strade,Samount1 B 12000 S 5000 This One i tried but problem in thisselect a.Custid,a.TRADEID,sum(a.AMOUNT),b.TRADEID,sum(b.AMOUNT) from custmer a,custmer bwhere a.Custid = b.Custid and A.TRADEID = 'B' and b.TRADEID = 'S' group by a.Custid,a.TRADEID,b.Custid,b.TRADEID
Imran_Javed
- May 9th, 2008
SELECT MIN(b.CUSTID) "cust id", MIN(b.TRADE) "Btrade", SUM(b.AMOUNT) "Bamount", MIN(s.trade) "Strade", MIN(s.amount) "Samount"FROM cust_t b, (SELECT MIN(CUSTID),MIN(TRADE) trade,SUM(AMOUNT) amount FROM cust_t WHERE trade ='s') s WHERE b.trade = 'b';
jamesravid
- Dec 10th, 2006
Hi Try this query,Select custid, 'B' as BTrade, sum(decode(tradeid,'B',amount,0) Bamount 'S' as Strade, sum(decode(tradeid,'S',amount,0) Samountfrom customergroup by custidRegards,James.
-
If i have 6 values in one coloumn that is 10,20 30,40 50,60. in this 10,30,50 are max(val),and other are min values. now i want some of max values is 90. write a query for that?
Imran_Javed
- May 12th, 2008
SELECT SUM(col_name)FROM ( SELECT ROWNUM rn, col_name FROM tab_name)WHERE MOD(rn,2)!=0;
Rohan Deshpande
- Jan 30th, 2007
Select sum(max(sal)) from table_name;
-
Get fieldnames from Empty Table
How to get field names from empty table by using SELECT statement;
-
How to obtain employees who earn sal greater than avg sal in the given deptment in classic emp, dept tables
Imran_Javed
- May 9th, 2008
SELECT e.EMPNO, e.ENAME, e.deptno, e. SAL, e1.avg_salFROM emp e, (SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno ) e1WHERE e.deptno =e1.deptno AND e.sal >e1.avg_sal;
Yoga
- Sep 26th, 2007
Select ename, deptno, sal
from emp where sal > (select avg (sal) from emp) group by ename, deptno, sal -
How we can get the salary whose is earning more than one person and also i want his name also to display...Ex:-If there is 5 members are there who is earning more than the JonesOutput I want is:Jones 2500 hari 2600Jones 2500 king 5000Jones 2500 rakesh 6000Jones 2500 rajesh 6500Jones 2500 ramesh 7000
Imran_Javed
- May 9th, 2008
SELECT e.ename || ' '|| e.sal || ' ' || e1.ename || ' '|| e1.salFROM EMP E , emp e1WHERE e.sal < e1.sal
venkat2182
- Aug 9th, 2007
Hi....I hope u know about "self-join". The following query will give the above result.SELECT e1.name,e1.salary,e2.name,e2.salary FROM emp_test e1,emp_test e2WHERE e1.name='Jones' FRO...
-
What should be the return type for a cursor variable.Can we use a scalar data type as return type?
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
krishnaindia2007
- May 16th, 2008
No we can not use a scalar data type as return type.The return value of a strongly typed REF CURSOR must be a record which can be defined using the %ROWTYPE and %TYPE attributes or as a record structure.
-
Establishing many to many relationship in SQL
How will you establish many to many relationship in SQL?Ramco Systems dt. 02 Feb' 2008
krishnaindia2007
- May 16th, 2008
Cartesian product is an example for many to many reolationship in SQL.For exampleselect a.ename, a.job, a.sal, b.dname from emp a, dept bresults in many to many relationship.
rakeshn
- Feb 20th, 2008
EmployeeDepartmentlets say one employee can work in multiple department.It needs many to many relationshipCreate another table EmpDeptMap.it will have foreign key with employee and Deptartment too.In this table (empid+deptid) is the primary key
-
What is a deadlock?
There is a table with Pluks('plk') and Zinks ('zin'). Each Pluk onlyresides on a single Zink and each Zink may contain multiple Pluks. Howdo you find how many Pluks are on each and every Zink? Please provide SQL.What is a deadlock?
krishnaindia2007
- May 16th, 2008
Dead lock is a unique situation in multiuser system that causes two or more users to wait indefinately for a locked resource. First user needs a resource locked by the second user and second user ...
g_sidhu
- Feb 6th, 2008
A deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embraced) and cannot continue processing. Oracle automatically dete...
-
How to find errors of PROCEDURE at run time( not complie time) ?
satyam_Ora
- May 16th, 2008
Hi,what nachs has written is correct.Show error is use to find the compilation error not run time error.For runtime error of procedure we need to take help of exception Handling and that error may be ...
nachs
- Aug 22nd, 2006
Use the exception handlers in run timeuse sqlerrm, sqlcode functionsshow erroris used to find errors during compile time
-
How to get fifth maximum salary from the table with out using TOP keywork(without any keywords) in sql server 2000Can you please guide me about this?
monika1985
- May 28th, 2008
Select sal from emp ewhere 4=(select count(distinct(sal) from emp where sal>e.sal)
Imran_Javed
- May 9th, 2008
SELECT * FROM (SELECT ename ,sal, dense_rank() over (ORDER BY sal DESC) max_sal FROM emp)WHERE max_sal= 5;
-
Export data from SQL to Excel
How to export data from a SQL table to MS Excel?
sameers007
- Apr 6th, 2008
Spooling means just copying what ever we type in SQL prompt rit, I want to know whether any query exists for that
-
How can you see table in reverse order of columns using SELECT statement.
saibabu
- Jan 12th, 2007
hi select ..........col3,col2,col1 from tablename;just specify the columns in the reverse order i n the select statement.
-
How to insert of only system time(hh:mi:ss) into table columnnot in the format of 'dd/mm/yyy hh:mi:ss' only system time not date.
Waseem Mehmood
- Nov 20th, 2007
CREATE TABLE table_name(col1 VARCHAR2(20));INSERT INTO table_nameVALUES(TO_CHAR(SYSDATE, 'HH:MI:SS'))While creating table you must keep in mind the datatype must be Varchar2 for required column
-
Security for SQL Queries?
Can you provide any security for SQL queries? If Yes, How?
-
Can we use aggregate function in another aggregate functionsuppose we got count of a column by using the group by clause so we get different counts in the same statement how to get the maximum of count
gkbiswal
- Sep 29th, 2007
We can use aggregate function inside another aggregate function.Example:SQL> select max(Count(*)) from emp group by deptno;MAX(COUNT(*))------------- 6DB Version: Oracle9i Enterprise Edition Release 9.0.1.1.1
-
Lock Row of a Table using SQL
How to lock a particular row of a table using SQL?
krishnaindia2007
- Jun 20th, 2008
INSERT , UPDATE, DELETE operations implicity lock rows of a table that satisfies the condition.Explicitly you may lock a table using for update of as followsSELECT * FROM EMPWHERE EMPNO = 7369FOR UPDATE OF SAL NOWAITIt will lock single row of emp table having employee number 7369.
-
Find all tables in a database
How to find all the tables existing in a database?
krishnaindia2007
- Jun 20th, 2008
To view tables in the current schemaSELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'To view all the tables that you have access SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT...
Imran_Javed
- May 8th, 2008
SELECT TABLE_NAME FROM All_TABLESOrder by TABLE_NAME;The above query will display all the table names in the database.SELECT TABLE_NAME FROM USER_TABLESOrder by TABLE_NAME;The above query will display all the table names in a particular schema.
-
Alias in Joins
Why do we use aliases during joining two tables?
krishnaindia2007
- Jun 20th, 2008
Alias name it temporary name given to a tablen or columns of a table.It has its existence until the satement is executed. For example in the following example instead of typing tablenames ...
Oracle SQL Interview Questions
Ans