-
How many tables are needed to create and their relationships ?
Supplier Name
Supplier Site
Supplier Street
Supplier Contact Person
Supplier City
Supplier State
Supplier Phone number
Business requirement is : One Supplier can have multiple ( n number of
Sites ), One Supplier Site can have Multiple Contacts Persons ( n
number of Contact persons ), One Supplier Contact Person can have
multiple phone...ashadevi21
- Oct 5th, 2011
Supplier Table Columns : Supplier id , supplier name (Supplier ID, supplier name as Primary Key) Supplier Site Details Columns : Supplier name , site, street, city, state (Supplier name as foreign Ke...
-
How to delete identical row from a table?
how to delete identical row from a table?
if we have 4 row and two of them having same data then how we delete identical row using delete querySiraj
- Oct 15th, 2011
Delete from table_name where rowid not in (SELECT max(rowid) FROM table_name group by (column_name))
-
How to retrieve the 2nd record from a table ?
How to retrieve the 2nd record from a table which has say 1000 records in it. Please answer this without using the row-id? I tried to say a query "select * from table where rowcount=1" which worked well and also "select * from table where rowcount=3" which gave me the result but not when I did the same query "select * from table where rowcount=2" ? why?
rima
- Nov 24th, 2011
Code- span style="color: #ff0000;">`table_name`
smonroy
- Sep 27th, 2011
The best way to issue is
select top 1 * from cars where id_car in
(select top 2 id_car from cars order by id_car)
order by id_car DESC
and with that
obtain the second record (This way is for the PK)What is the advantage to use trigger in your PL?
bhagya
- Feb 10th, 2012
1.Auditing the tables
2.Online transaction
3.Securing from another table
4.Automatic calculations
5.Replicate the tableschennoju
- Aug 3rd, 2006
The main advantages of the triggers are* To Audit the table* To check the complex conditions which are not possible by the constraints.
What is the advantage in creating force view?
Lokesh M
- Feb 18th, 2012
Force view creates the view even if the underlying tables / referenced objects are not present. Force View are used while executing a script that has create tables and views statements or while creating a view for remote table.
3 way join
Hi,
I have 3 files each having 2 cols
e.g
File1
empid deptid
1 100
2 200
3 200
4 100
5 300
File2
empid salary
1 10
2 20
3 30
4 40Mark Haynes
- Mar 2nd, 2012
I believe what you are looking for is a query like this:
select f3.deptname dept_name, sum(isnull(f2.salary,0)) amt_spent_each_dept
from file3 f3 left join file1 f1 on (f3.deptid = f1.deptid)
left join file2 f2 on (f1.empid = f2.empid)
group by f3.deptname
order by f3.deptname
Deleting duplicate records in a table without using rowid
1. How to delete a duplicate records in a table without using rowid?
2. What is the use of Connect by clause?
3. What is the use of Connect by clause?
4. How to display "Experience of employee"?E.g. 3 years 4 months 19 days?
5. What is select statement to spe?Muralidhar
- Apr 6th, 2012
We delete the duplicate records using level and rownum. The query is
Codepravesh28
- Nov 28th, 2010
1 Delete from table_name where ROWID not in ( selectmax(rowid) from table group by duplicate_values_field_name);
How do you print the last n rows or the first n rows of a table ?
Read Best Answer
Editorial / Best Answer
nirmal1in- Member Since Jan-2010 | Jan 17th, 2010
This can be accomplished in following way:
Example: table - emp
For First n rows:SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a < 5 --- say n is 5 display first 5 records
For last n rows:SELECT * FROM
FROM )WHERE .a<
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) x
WHERE x.a < 5This query can be used for finding nth row also say n = 5
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a = 5deepthi
- May 17th, 2012
CodeDifference between Cluster and Non-cluster index?
Read Best Answer
Editorial / Best Answer
Answered by: Sushant
- Oct 25th, 2006
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
Sameer
- Nov 1st, 2012
Now SQL SERVER 2008 onwards we can have 1 cluster index and 999 non cluster index on one table.
primarykey2000
- Apr 22nd, 2008
clustered index - the index order matches the order of physically stored data.cluster index can be used on table that doent get changes frequentlyusing DML statement on cluster index column have...
SQL query to change lower case in a table to Upper case
In a table the persons name is in lower case "abc" and is there any SQL query to obtain that persons name as "ABC" as an output?
What are steps to follow for query tuning ?
robindas
- Jan 11th, 2013
Few steps for query tuning. 1) Check for if any sub query or exists or not exists is there if yes then try to change it in the joins 2) Try to check the way joins had been written try to put it in f...
samareshoracle
- Aug 4th, 2012
Following a Tuning Methodology Objectives:: Determine performance problems Manage performance Describe tuning methodologies Identify goals for tuning Describe automatic SQL tunin...
Normalization in sql
Explain the normalization and why it is done. explain the case when we avoid the normalization in database.
ppad001
- Feb 27th, 2013
For perfomance optimization.
Like in case of datawarehousing operation the no of tables to be joined to get the desired output might be too high. In such a case we de-normalize to serve our purpose.DML operations on base tables using a simple view
Can we make DML operations on base tables using a simple view?Can we insert or update or delete rows to the base tables using a view?
lviswanath
- Jul 22nd, 2013
Of course we can do anything i.e. updating/deleting/inserting on a base table underlying a simple view, until unless its covering NOT NULL columns of the table in the query of the simple View.
Read Best Answer
Ans