How To Rename The Database?
ans: sp_Renamedb 'oldname','newname'
How to rename The Table ?
ans : sp_rename 'oldtablename','newtablename'
Simple Select Statement
--Select *From TableName (Display total records of the table)
--Select columname1,columnname2 from table name (Displays mentioned column records of the table )
Simple Update Statement
--Update table TableName Set Columname=Newvalue
--Multiple columns update with CASE statement
Update Table TableName Set ColumnName=Case
When Condition Then Result1
when Condition Then Result2
---------------------
[else result]
End
ex:
update Table EMP set Bonus=Case
when sal<=6000 then 1000
when sal>6000 then 800
when sal>7000 and sal<9000 then 5000
(or)
else 3000
End
How to Delete the records in sqlserver Query ?
ans: Delete From Tablename
---with where Condition
Delet from tablename where deptid=10
How to Give Alias Names ?
ans :Select columnname as AliasName ,col2 as AN2 from TableNamec
How DELETE AND UPDATE CASCADE works in Sqlserver ?
ans: Cascade
---ON DELETE CASCADE
with out specifying the ON DELETE CASCADE it is not possible to delete the record in the parent table if there are dependent records form the child table for that record ON DELETE CASCADE if used when the record in the Parent table is deleted all the dependent records in the child table will be also be deleted
Same AS ON UPDATE CASCADE
How to Select Top N Records in Table ?
Ans: Select Top 10 *from Table Name
Select Top 10 Column name from Table Name
How to Select Top Nth Record From Table?
Ans : It Means Top Nth records or 3 rd top record like wise
(Select Distinct top 5 Column name from table name order by column name desc)
a order by column name
Here it displays 5th highest record in table
How to Display Duplicate Records in Sqlserver ?
SELECT Column name,
COUNT(column name)
FROM table name
GROUP BY column name
HAVING ( COUNT(column name) > 1 )
Difference Between Delete And Truncate ?
Ans: Delete Command is used to delete all the Records in table ,if we use delete command we can able to rollback the records which is delete
Truncate Command is used to delete all the records in table ,if we use truncate command we unable to roll back the records which is deleted
Drop :The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
ExamplesHow to Display Duplicate Records in Sqlserver ?
SELECT Column name,
COUNT(column name)
FROM table name
GROUP BY column name
HAVING ( COUNT(column name) > 1 )
Difference Between Delete And Truncate ?
Ans: Delete Command is used to delete all the Records in table ,if we use delete command we can able to rollback the records which is delete
Truncate Command is used to delete all the records in table ,if we use truncate command we unable to roll back the records which is deleted
Drop :The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
JOINS
What is JOIN ?
Join in sql server is used to select the records from multiple tables
How many types of joins in sql server ?
Three types of Joins in SQL Server
- INNER JOIN ---It Select only Matched records in multiple tables
- OUTER JOIN ---It selects matched records as well as Unmatched records
- CROSS JOIN --Every Row from first table is multiplied with all rows of another table(Multiplication of two tables)
INNER JOIN
--SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE2.COLUMN1,TABLE2.COLUMN2
FROM TABLE1, TABLE2 WHERE TABLE1.COLUMN=TABLE2.COLUMN
OUTER JOIN
---LEFT OUTER JOIN
It will select the records from both matched and unmatched records of only left side table
ex:
--SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE2.COLUMN1,TABLE2.COLUMN2
FROM TABLE1, TABLE2 WHERE TABLE1.COLUMN *= TABLE2.COLUMN
The '*' position will give records of left or right if it is right side its called RIGHT OUTER JOIN
---RIGHT OUTER JOIN
As mentioned above
---FULL OUTER JOIN (ALSO CALLED AS ANSI SQL SYNTAX)
LEFT OUTER JOIN+RIGHT OUTER JOIN
--SELECT TABLE1.COLUMN1,TABLE1.COLUMN2,TABLE2.COLUMN1,TABLE2.COLUMN2
FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COMMON COLUMN = TABLE2.COMMON COLUMN
CROSS JOIN
---SELECT TABLE1.COLUMN1,TABLE2.COLUMN2 FROM TABLE1,TABLE2
How to insert Records from one table to another table ?
ans:SELECT COLUMN1,COLUMN2,COLUMN3 INTO NEWTABLENAME FROM OLDTABLENAME
For Above query we have to create new table to insert the data
How to insert records from one table to another existing table ?
ans:
INSERT INTO TABLE1(COL1,COL2,COL3,COL4) SELECT COL1,COL2,COL3,COL4 FROM TABLE2
For above query it will insert all the records from the TABLE1 to TABLE2
ALSO WE CAN WRITE WITH WHERE CONDITION
INSERT INTO TABLE1(COL1,COL2,COL3,COL4) SELECT COL1,COL2,COL3,COL4 FROM TABLE2 WHERE STUDENTID=12
http://sqlserverqueryhelp.blogspot.com
