Lesley Ruzvidzo

• Nov 3rd, 2016

Theta join combines tubles from different tables(relation) provided they satisfy the theta condition.
Given R1(A1,A2,A3,...,An) and R2(B1,B2,B3,...,Bn) such that they have no common attributes i.e
R1 intersection R2 (R1 n R2) = Theta.
Theta can use all kinds of comparisons like =,>,< ...e.t.c.

sivaprasad

• Apr 10th, 2015

Theta join is nothing but not equi join, i.e.,it is a principle to join two tables with out any common column of both tables.

Answer Question  Select Best Answer

I have a scenario like Have 7 tables. All these 7 tables have same metadata structure and also all have 2 columns. In that, One column is same in all 7 tables (Joining column, But not a primary column) and another column has different values.

Below have a example for 2 tables. How can we achieve for 7 tables?

Tab1
====
loc_name mem_name
HYD ...

Tmas

• Nov 3rd, 2016

Use outer join

Code
 

Pavan

• Oct 1st, 2016

Hi use sub-query with left join

Answer Question  Select Best Answer

Compose an SQL statement to show a list of two least expensive vendors (suppliers) for each raw material. In the result table, show Material ID, Material Description, Vendor ID, Vendor Name, and Supplier’s unit price. Sort the result table by Material_ID and unit price in ascending order. Note: if a raw material has only one supplier(vendor), that vendor should also be in the result (output) table...

shirley

• Apr 26th, 2017

"sql SELECT Rank() OVER (PARTITION BY MaterialID ORDER BY UnitPrice ASC) AS [Ranking], MaterialID, Description, VendorID, VendorName, UnitPrice INTO tempdb..ProductByVendor FROM ...

Answer Question  Select Best Answer

I have a source table with one column of 15 characters and I want output like divide the 15 chars column into multiple of 3 columns like every column 5 chars

source empnoorderprdct target empno order prdct

Divya

• Jun 5th, 2017

You can use Substring function to split the column values to 3 columns

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Jul 12th, 2016

Please use MID() function to extract characters from a field.

Code
 

Answer Question  Select Best Answer

How can i retrieve uncommon fields from two different tables along with the data??? say i have table A and table B and Tbl A has x,y,z fields and Tbl B has x,a,b fields and i have to retrieve y,z,a,b fields from two tables along with the data.

Divya

• Jun 5th, 2017

You can try below example query using the 2columns in this query : Select ContactName,FirstName from Customers C, Employees E where C.ContactName not in( E.FirstName)

Ade

• Jun 27th, 2016

Code
 

Answer Question  Select Best Answer

There is three tables Customer, order and order details.
Customer and order linked with cust_id, order and order_details connected with order_id.
How a column(total_order) in customer to be updated from order_details.

When we give SELECT * FROM EMP; How does oracle respond?

Abdul Rahman

• Jul 18th, 2017

All rows from the EMP table are returned (output).

Sandip Muke

• Jul 29th, 2016

First it will do the syntax/ semantic check and if it is correct then an only that statement forward to the Oracle RDBMS server. After that Oracle will check whether that user has privilege or not. Th...

Answer Question  Select Best Answer

Co-related sub query is one in which inner query is evaluated only once and from that result outer query is evaluated.Nested query is one in which Inner query is evaluated for multiple times for gatting one row of that outer query.ex. Query used with IN() clause is Co-related query. Query used with = operator is Nested query

Abdul Rahman

• Jul 18th, 2017

A nested sub query is one sub query inside another sub query. A correlated sub query is a sub query that references a column from a table which appears in the parent statement.

qptopm

 Profile Answers by qptopm Questions by qptopm

• Oct 13th, 2016

Co-Related Vs Nested-SubQueries. Technical difference between Normal Sub-query and Co-related sub-query are: 1. Looping: Co-related sub-query loop under main-query; whereas nested not; therefore...

Answer Question  Select Best Answer

Not known

Abdul Rahman

• Jul 18th, 2017

Sql Server is the Database offering/ product of Microsoft. Examples of other database products are Oracle, MySql, etc. SQL is the query language that is used to handle/ manipulate data in the database...

amit

• Jun 12th, 2017

SQL is a command language to perform operations on database. SQL server in the underlying software that will accept the SQL commands given to it to perform the operations in data handled/ stored by server.
e.g. .net is language and Visual studio is software to run it.

Answer Question  Select Best Answer

How to delete reverse duplicate in oracle.
e.g i/p
col1 col2
hyd blr
mum del
del blr
blr hyd
blr del

i want in o/p
col1 col2
hyd blr
mum del
del blr

Priyanka Jaiiswal

• Aug 17th, 2017

SELECT a.col1 ,a.col2 from t1 a
where a.col1 < = a.col2

manish

• Feb 7th, 2017

SELECT a.mem_name,a.loc_name
FROM TAB4 a
WHERE a.loc_name = a.mem_name;

Answer Question  Select Best Answer

EID ENAME SAL SALMONTH
101 xyz 100 Jan 2015
101 xyz 200 Feb 2015
102 Abc 300 Feb 2015

I want output like this
Eid Ename hike
101 xyz 100
102 Abc 0

Akash

• Aug 15th, 2017

Can you elaborate what you are solving its not about duplicates

dan

• Jul 31st, 2015

The question is poorly stated, perhaps partially stated as well. But it is good that basic sample data is given. Does the table have data only for Jan 2015 and Feb 2015.? What if the salary changes we...

Answer Question  Select Best Answer

There is a table T with two columns C1 and C2.
The data is as below:
C1 C2
1 4
2 5
3 6

display the result as :

1 2 3
4 5 6

Santhosh Kumar Gujja

• Nov 27th, 2017

Sel max(case when rn=1 then c1 end),max(case when rn=2 then c1 end),max(CASE WHEN RN=3 THEN c1 end)
union all
sel max(case when rn=1 then c2 end),max(case when rn=2 then c2 end),max(CASE WHEN RN=3 THEN c2 end)2
(
SEL c1,c2,row_number() over(order by C1) as rn
)O

vikas bose

• Nov 3rd, 2017

In MySQL, we have one function GROUP_CONCAT(field_value, separator). It will give single row.
SELECT GROUP_CONCATE(field_Value, ,) FROM table_name where 1 group by field_name.

Answer Question  Select Best Answer

Source:
col1 col2
1 Will
2 John
3 Josh
4 Devin

Target:
col1 col2
1 Will
2 NULL
3 John
4 NULL
5 Josh
6 NULL
7 Devin
8 NULL

I want the SQL query for the...

SANTHOSH KUMAR GUJJA

• Nov 26th, 2017

SEL CASE WHEN COL1=1 THEN COL1 ELSE COL1+MIN(COL1) OVER(ORDER BY COL1 ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) END AS
COL1,COL2 FROM TABLE
UNION
SEL COL1,NULL AS COL2 FROM TABLE WHERE COL11

Rakesh

• Mar 2nd, 2017

SELECT COL1, CASE
WHEN MOD(COL1,2) = 0 THEN NULL
ELSE
COL2
END CASE
FROM G2

Answer Question  Select Best Answer

How to find the latest salary of each employee in the Employer table?
emp.id sal year month
1001 5000 2015 3
1001 3000 2014 4
1002 4000 2013 3
1003 2000 2013 2

SANTHOSH KUMAR GUJJA

• Nov 26th, 2017

SEL EMP.ID,SAL FROM
EMPLOYER QUALIFY ROW_NUMBER() OVER(PARTITION BY EMP.ID ORDER BY YEAR,MONTH DESC)=1

anagogia

• Sep 29th, 2016

The query has to be a nested query because one employee could have the max (year||month) = 201503 where he had max salary and other could have max(year||month) = 201603 where he had the max salary ......

Answer Question  Select Best Answer

How to retrieve a second highest salary from a table?
Note:Suppose salaries are in duplicate values
eg:
Name Sal
Malli 60000
Pandi 60000
Rudra 45000
Ravi 45000

Pankaj Yadav

• Feb 23rd, 2018

Code
 

Santhosh Kumar Gujja

• Nov 27th, 2017

SEL SAL FROM(
SEL DISTINCT SAL FROM TABLE
) O QUALIFY ROW_NUMBER() OVER(ORDER BY SAL DESC)=2

Answer Question  Select Best Answer

I am new to Oracle. I have created one table in that one column name salary, while creating the table I have given the datatype for that column is varchar2 and also I have given not null constraint. Now I want to change the datatype varchar2 to number, Is it possible to alter the datatype when having constraint in table?

DevaNaidu

 Profile Answers by DevaNaidu Questions by DevaNaidu

• Mar 7th, 2018

If you want change only varchar2 to number you can change by using ALTER, but you also defined NOT NULL constraints..so if you need to change a datatype along with Not null..you must use MODIFY key word

Abhimanyu eati

• Nov 17th, 2017

Yes we can change datatype but make sure about size if you have created varchar(5) then alter to number(5)

Answer Question  Select Best Answer

UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;sal = 11000, comm = 1000

Madhumita

• Apr 26th, 2018

It will give
comm=1200 and sal=1000
I have executed and got the result in MySQL

souravbhattacharya1

 Profile Answers by souravbhattacharya1 Questions by souravbhattacharya1

• Aug 22nd, 2011

This Query will work fine. SQL> select * from emp where sal=10000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GENDER ----- ---------- --------- ----- ----------- -------...

Answer Question  Select Best Answer

Describe use of VIEW in SQL Server?

Krishnaveni

• Aug 10th, 2018

A view is virtual table created using the real table. End users can easily access the data with out altering any table definition by using views. We can create, replace or drop the view depending on ...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Ranjeet

• Feb 27th, 2006

---

Emp_name varchar(10) -  if you enter value less than 10 then remaining space can not be deleted. it used total 10 spaces.

Emp_name varchar2(10) - if you enter value less than 10 then remaining space is automatically deleted

Krishna Kant Kumar

• Mar 12th, 2019

VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring data type. VARCHAR can store up to 2000 bytes of characters while ...

Prabhat Sahu

 Profile Answers by Prabhat Sahu Questions by Prabhat Sahu

• Oct 30th, 2018

CHARACTER Datatype: CHAR, VARCHAR both are of CHARACTER Datatype in oracle. CHAR is fixed length datatype and VARCHAR is Variable length datatype to store character data. i.e. A-Z , a-z , 0-9 and key...

Improve Answer

Pendo

• Jul 17th, 2021

WITH Xlist as (
SELECT salary ,rank() over( order by salary desc ) as rnk
FROM salary)
SELECT salary from Xlist where rnk =2;

Akila

• Oct 11th, 2018

SELECT *
FROM
(SELECT personid ,row_number() over (order by PersonID desc) as row_num FROM
(SELECT DISTINCT personid FROM persons))
WHERE row_num = 2

Answer Question  Select Best Answer