-
How can you create a column to be auto generated when creating a table ? is it possoble ?
mv_sivavardhan
- Aug 6th, 2008
There are two ways to auto generate the values of a column in a table1) Default value: we can set a default value to column definition2) Identity value: we can set an identity value with initial value and increment value to column definition.
-
Query to find amout of salary hike
In table "salary" with emp_no,sal_dt,salary which records salary of all employees every month.find the query to give the amount of salary hike and date of hike for all the employees.
ngiridhar
- Oct 26th, 2010
SELECT empno,
DECODE (SIGN ( salary
- LEAD (salary, 1, 0) OVER (ORDER BY empno DESC,
sal_dt DESC)
),
-1, 'LAST',
salary
- LEAD (salary, 1, 0) OVER (ORDER BY empno DESC, sal_dt DESC)
) AS sal_diff
FROM salaryramkrishnag
- Oct 17th, 2008
Select distinct S1.empno , s2.sal_date , (s2.sal-s1.sal) Salhike from salary s1 , salary s2 where s1.empno = s2.empno and (s2.sal-s1.sal) > 0 Above results in all hikes of all employees
-
INSERT Statement Optimization
A script is inserting data into one table, it was taking 15 min to complete before. Today it is taking more than 2 hours. What kind of action you will take?
smonroy
- Sep 28th, 2011
Also you can do the below steps
1.- Normalization your DB
2.- Break the INSERT in some INSERTs instead have one
3.- You need to create indexes on the tablearindam_0006in
- Aug 11th, 2010
Do not paste in sql client and run the script. Use sql administration console. First analyze it and browse the sql dump file and execute it. Think there will be no issue.
-
DROP command and VIEW
How does dropping a table affects its view?
sivaraj6389
- Sep 7th, 2011
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Table Name'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'view Name' because of binding errors. -
Remove redundant rows
How to remove redundant rows without any distinct columns in the table itself ?
smonroy
- Sep 27th, 2011
Hi my Friend this is the best way to you issue DELETE FROM table_name where camp1 in (SELECT camp1 FROM table_name GROUP BY camp1 HAVING Count(camp1)>1) Example: DELETE FROM ubication where serial...
Allan Hunter
- Sep 22nd, 2011
You're question is vague, but from what I understand it to mean is to remove duplicate rows wihle keeping the columns in tact. The easiest way I know is to make your query based on what makes the dat...
-
How to retrieve uncommon fields from two different tables?
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.
-
Retrieve all columns except 1 in a table.
I have a table with 20 columns and I have to retrieve 19 except one.
Is there any way easier then giving
SELECT Col1,Col2,Col3,..........Col19 from table1. -
SQL Query to convert single row from multiple tables into single row in a table
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 ... -
Demoralization is done toA. Check for Data IntegrityB. Reduce Data RedundancyC. Introduce Security CheckD. Increase Query performance
sweety
- Aug 23rd, 2014
Denormalization is done to increase query performance and
normalization is done to reduce data redundancy -
Given an unnormalized table with columns:Name,Phone number,Address You notice that some records in Name column are duplicate. Write a T-SQL statement to delete all duplicate record under Name column.
goksn
- Sep 7th, 2009
Delete from table-name where Name in (Select Name from table_name group by name having count(name)>1)
rroplekar
- Nov 16th, 2007
I think your query is perfect.select t.name from table t where t.rowid not in ( select max(t1.rowid) from table t1 group by name)
-
SubQuery Performace - IN clause or EXISTS clause
In subqueries, which is efficient ,the IN clause or EXISTS clause? Does they produce the same result?
PrabhatKumar
- Oct 7th, 2010
The EXISTS function searches for the presence of a single row meeting the stated criteria as opposed to the IN statement which looks for all occurrences. Rule of thumb:If the majority of the filt...
Arpana
- Oct 10th, 2007
EXISTS is more efficient that IN , Because EXISTS use indexes of the table and hence scans the table faster, as well as it returns the boolean value (T or F), If T is received for EXISTS clause than t...
-
It is also mentioned that column aliases cannot be used in the WHERE clause, then how did this query worked :"select * from (select rownum rnum,a.* from testsal a) where rnum=3;".
Praveen Vaddadi
- Mar 7th, 2008
Lets examing the query as:select * from (select rownum rnum,a.* from testsal a) b where b.rnum=3;".The subquery here is used as another table for the main query and only its structure will b...
-
What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;TROUBLETHETROUBLE
sharathtest
- Aug 16th, 2007
The result is ' TROUBLETHETROUBLE ' Space before and after the text.
-
State true or false. EXISTS, SOME, ANY are operators in SQL.
True
-
-
-
-
_______data type is used to store binary data such as graphics, sound, etcSQL uses ____________ clause to impose an order on the result of a query _________ function determines the number of rows or column values.The limit that determines the sphere of influence and control over a system is called_______________ is a graphic technique that can be used by analyst to represent the inputs and outputs.
-
Case function
if i have a Customer table with following datacust id custname1 A2 Band cars table with following datacar id cust id Model1 1 Toyota2 1 Honda3 2 Suzukiwhat is the query to give follwing out putcustid cust name model model 11 A Toyota Honda2 ...
pravintule
- May 28th, 2010
Select c.custid,c.custname,m1.model,m2.model as model1 from customer c1,car m1,car m2where c.custid=distinct(m1.custid) and c.custid(+)=m2.custid
vinayak1234
- Mar 30th, 2010
SELECT c.custid, c.custname, m.model FROM custtable AS c JOIN carstable AS m ON c.custid = m.custidgroup BY c.custid, c.custname, m.model
-
Create View
Write an SQL statement to create views.
SQL Interview Questions
Ans