SQL SERVER 2000 QUERIES |
|
||||||||||||||
Answer: Insert SQL query:- Insert queries are designed to insert new rows of data in a table.This insert queries use T-SQL insert statement to insert data.Insert statement is simple then the other two DML statement Delete and update statement. Insert statement can isert multiple rows of data if we use derived table to get the record from he other table.
Example of Insert Sql Query:
Inserting Rows.
The INSERT statement has two distinct variations the first, and simplest, inserts a single row into the named table. Here are some simple Insert query to insert data in Emp table:
(I) INSERT INTO tablename [( column-list )] VALUES ( constant-list ) ;
Inset into emp(name,id,address,sex) values(‘DotNet’,1,’New York’,’M’);
Inset into emp(name,id,address,sex) values(‘Net’,2,’India’,’F’);
Inset into emp(name,id,address,sex) values(‘Question’,3,’England’,’M’);
after executing we get the records in the table.
|
|||||||||||||||||||||
Insert multiple rows from one table to another table:
(II) Insert into table_name(name,id,address,sex) select name,id,address,sex from emp;
Question: Write an Update Sql Query ?
Answer: Update Sql Query:- Update statement logged if we are replacing or modifying large blocks of text or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged.
Update Sql query is used to modify some records in the table and also use some condition:-
The UPDATE statement consists of three clauses:
UPDATE table_name
SET column-list(for update)
WHERE cond-expression ;
(i)Update emp set name=’Pervej’,address=’sonipat’,sex=’M’ where id=1;
Emp Table |
|||
| Name | ID | Address | Sex |
| Pervej | 1 | Sonipat | M |
| Net | 2 | India | F |
| Question | 3 | England | M |
Question: Write an Delete Sql Query ?
Answer: Delete SQL query:- DELETE only affect one table at a time, you cannot use DELETE with a view that has a FROM clause naming more than one table.
Deleting Rows:
The general form of the DELETE statement is
DELETE FROM tablenameWHERE conditional-expression
The DELETE statement removes those rows from a given table that satisfy the condition specified in the WHERE clause.
(I)delete from emp where id=2;
after executing we get the output.
|
||||||||||||||||
Here i have explain two functions ROW_NUMBER() ,RANK(),DENSE_RANK()
|
||||||||||||||
select COL1,COL2,row_number() over (order by COL2) as rownumber from DOTNET (output 1)
select COL1,COL2,rank() over (order by COL2) as rownumber from DOTNET(output 2)
select COL1,COL2,dense_rank() over (order by COL2) as rownumber from DOTNET(output 3)
and its
|
|||||||||||||||||||||
|
|||||||||||||||||||||
|
|||||||||||||||||||||
To delete all the records from table
(II)delete from emp;
Question: Write an query to count number of row in table (count)?
Answer:- (i)select count(*) from emp
(ii)SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2
we can also put column name in place of '*'.
Question: How to know how many tables contains name as a column in a database ?
Answer:- SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'name') '.
Question: Write a query to convert all the letters in a word to upper case ?
Answer:- SELECT UPPER('dotnet').
Question: Write query to round up a number. For example even if the user enters 4.1 it should be rounded up to 5?
Answer:-
SELECT CEILING (4.1).
Question: Write query to get datetime in different format ?
Answer:-
select convert(varchar, getdate(), 1)
Output:- 12/30/06
select convert(varchar, getdate(), 2)
Output:- 06.12.30
select convert(varchar, getdate(), 3)
Output:- 30/12/06
select convert(varchar, getdate(), 4)
Output:-30.12.06
select convert(varchar, getdate(), 5)
Output:-30-12-06
select convert(varchar, getdate(), 6)
Output:-30 Dec 06
select convert(varchar, getdate(), 7)
Output:-Dec 30, 06
select convert(varchar, getdate(), 10)
Output:-12-30-06
select convert(varchar, getdate(), 11)
Output:-06/12/30
select convert(varchar, getdate(), 101)
Output:-12/30/2006
select convert(varchar, getdate(), 102)
Output:-2006.12.30
select convert(varchar, getdate(), 103)
Output:-30/12/2006
select convert(varchar, getdate(), 104)
Output:-30.12.2006
select convert(varchar, getdate(), 105)
Output:-30-12-2006
select convert(varchar, getdate(), 106)
Output:-30 Dec 2006
select convert(varchar, getdate(), 107)
Output:-Dec 30, 2006
select convert(varchar, getdate(), 110)
Output:-12-30-2006
select convert(varchar, getdate(), 111)
Output:-2006/12/30
select convert(varchar, getdate(), 8)
Output:-00:38:54
select convert(varchar, getdate(), 9)
Output:- Dec 30 2006 12:38:54:840AM
select convert(varchar, getdate(), 14)
Output:- 00:38:54:840
Question: Write a query when we need a common row between two tables ?
Answer:-
We use intersect to do this
SELECT col1,col2,col3 FROM table1
INTERSECT
SELECT col1,col2,col3 FROM table2
Question: Write a query when we need a copy structure of one table to another table or you can say example of cloneing ?
Answer:-
select * into tabel1 from table2
Question: Write a query when we need a row that are not common between two tables ?
Answer:-
We use EXCEPT to do this
SELECT col1,col2,col3 FROM table1
EXCEPT
SELECT col1,col2,col3 FROM table2
Question: Find top salary among two tables (table1,table2) ?
Answer:-
SELECT TOP 1 sal FROM (SELECT MAX(sal) AS sal FROM table1 UNION SELECT MAX(sal) AS sal FROM table2) a ORDER BY sal DESC
Question: Write an query to get Max,Min,Second max number from the table?
Answer:- (i) select max(id) from emp
we can get the max id value from this query .
(ii) select min(id) from emp
we can get min id value fom the table emp.
(iii)select max(id) from emp where id<(select max(id) from emp)
this query is combinination of two sql query.