-
How do you know which index a table is using?
kirandba
- Apr 1st, 2010
Use below query to know index usage in SQL ServerSELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], ...
-
How foreign key can reduce redundancy in the database design
skcwebworld
- Aug 26th, 2015
Since Foreign key accepts null values, then if we inset a record as NULL then how we can make Customer id as primary key in another table.
malapati
- Oct 10th, 2008
A column included in the definition of referential integrity which would refer to a referenced key.? Referenced key is a primary key which is combination of UNIQUE+NOT NULL".
-
There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?
Select emp_name, Dept_name from (Employee_table left outer join Dept_table on Employee_table.Dept_id = Dept_table.Dept_id)
-
Count Records
I have following table as books101 BOOK_NO BOOK_NAME AUTHOR_NAME COST CATEGORY1234 C Dennis 450 System1235 Oracle Loni 550 Database1236 Sql Loni 250 Database1237 Pl/Sql Scott 750 Database Now I want to List all the authors and book name that has more then 1 book written by them.pls help
-
How to hide the code in procedures,packages?
kirandba
- Apr 1st, 2010
CREATE PROCEDURE xyxWITH ENCRYPTION ASBEGIN...ENDBut you need to know database master key to decrypt this stored procedure to view code.
vavasudevan
- Feb 13th, 2007
Hi,Even we can use "Encryption" Keyword in MSSQL with Proc... this'll hide text of the Proc from User.Eg.,Create Proc Proc1As Select 1 While Taking sp_HelpText for Proc1, it'll return text written ins...
-
Can we delete master records from the parent table ? if yes justify
Sunil Langeh
- Mar 22nd, 2007
Yes, to do so ,firstly we have to delete records from the child category/table
PRASHANTKULAT
- Mar 7th, 2007
We can delete record from Parent table. But to do so We have to delete records from child tableTo do so You can write Trigger (Before delete trigger) on the Parent Table which will delete all the child records.
-
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
SQL Interview Questions
Ans