SQL Data Manipulation Language (DML)
SELECT - extracts data from a database table
UPDATE - updates data in a database table
DELETE - deletes data from a database table
INSERT INTO - inserts new data into a database table
SQL Data Definition Language (DDL)
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
Syntax for a SELECT statement covering all the options:
SELECT select_list
INTO new_table
FROM table_source
WHERE search_condition
GROUP BY group_by_expression
HAVING search_condition
ORDER BY order_expression [ASC | DESC]
SQL NOTES:
1 use <databasename>
2 select top 10 * from <tablename>
3 select top 15 <column1>, <column2> from <tablename >
4 select distinct < column1> from <tablename>
5 select * from <employee> where <city> = ' Delhi ' AND zip like 123001 ==> single quote for text , not for numric values
6 select * from employees where city = 'London' AND extension like '432%'
7 insert into <tablename> (column1, column3) values ( value1, value3)
8 update <tablename> set <column1> = new_value where <column2> =any value
9 delete * from <tablename>
10 delete * from <tablename> where <column1> = any value
11 select orderID,employeeID,customerID from orders where customerName > 'g' order by employeeID desc
12 select count (*)from employee
13 SELECT @@version
14 sp_columns @table_name=<tablename>
15 sp_pkeys @table_name=<tablename>
16 DROP TABLE <tablename>
17 delete from rakeshtest where age=45 OR age =65
18 select * from pubs.dbo.publishers
19 use pubs; select * from titles ==> two statements in one line
20 SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson'
21 select * from publishers where country IN ('Germany', 'France')
22 select * from publishers where pub_id NOT BETWEEN 1 and 1389
23 select pub_id as abcd, city as cccc from publishers
24 create database <database_name>
25 CREATE TABLE Person
(
FirstName varchar(30),
DateOfJoin (yyyymmdd),
Address varchar,
Age int(3)
)
26 select empname, count (empname) from employee group by empname having count(empname)>1
27 select * into <newtable_name> from <oldtable_name>
28 select column1, column3 into #temp2 from <tablename>
29 sp_helptext <vw_view_Name>
30 select avg(fieldname) from tablename ==> Returns the arithmetic average of the fields.
31 select count(*) from tablename where fieldname=condition ; ==> Returns the number of records that match the condition. select max(fieldname) from tablename ==> Returns the largest value of fieldname.
32 select min(fieldname) from tablename ==> Returns the smallest value of fieldname.
33 select sum(fieldname) from tablename ==> Returns the summation value of fieldname.
34 select convert(newdatatype, fieldname) from tablename ==> converts one datatype into another.
--SQL TOOLs:
--Find a column in database
SELECT so.id, so.name as [Table Name], sc.name as [Column Name]
FROM sysObjects so inner join sysColumns sc
ON so.id=sc.id
WHERE sc.name like '%Column_Name_to_search%'
---------------------------------------------------------------------------
--view database schema
SELECT SO.NAME as [Table Name], SC.NAME as [Column Name], ST.NAME as [Data Type], ST.PREC as [Data Length], ST.SCALE
FROM SYSOBJECTS SO INNER JOIN SYSCOLUMNS SC ON SO.ID = SC.ID
INNER JOIN SYSTYPES ST ON SC.XTYPE = ST.XTYPE
WHERE SO.XTYPE IN ('U')
---------------------------------------------------------------------------
--view all table name of database
select name as [Table Name] from sysobjects where xtype='u'
---------------------------------------------------------------------------
Two files in SQL are required to copy the table structure and data - databasename.mdf, databasename.ldf
---------------------------------------------------------------------------
Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx#_FAQ31
---------------------------------------------------------------------------
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMN
WHERE COLUMN_NAME like 'TaxId";
---------------------------------------------------------------------------
SELECT COLUMN_NAME, DATA_TYPE, CHaRACTER_MAXIMUM_LENGHT
FROM INFORMATION.COLUMNS
WHERE TABLE_NAME ="Nick_account_NAME" ;
---------------------------------------- Joins -----------------------------------
SELECT <table1>.<column1> , <table1>.<column2> , <table2>.<column1>, <table2>.<column2>
FROM <table1> INNER JOIN / LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL OUTER JOIN <table2>
ON <table1>.<Common_column1> =<table2>.<Common_column1>
---------------------------------------------------------------------------
Who has ordered a product, and what did they order?
SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
List all employees, and their orders - if any.
SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
List all orders, and who has ordered - if any.
SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
Who ordered a printer?
SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
WHERE Orders.Product = 'Printer'
---------------------------------------------------------------------------
---Two inner joins
SELECT table1.column1, table2.column2, table3.column3
FFROM table1 inner join table2
ON table1.column1=table2.column2 inner join table3.column3
ON table2.column2=table3.column3
---where condition is optional if required
WHERE table1.column1=X and table3.column3=Y
GROUP BY table1.column1
---------------------------------------------------------------------------
A CROSS JOIN produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no ON clause because you're just joining everything to everything.
A FULL OUTER JOIN is a combination of a LEFT OUTER and RIGHT OUTER JOIN. It returns all rows in both tables that match the query's WHERE clause, and in cases where the ON condition cannot be satisfied for those rows it puts NULL values for the unpopulated fields.
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
Steps to see the schema of a table :
query analyzer-->select SQL server name on the network --> select sql server authontication --->login name and password-->ok--> select database ( if required )--> type command in right side white panel -->sp_help tblCNTCountry --> press F5
---------------------------------------------------------------------------
Copy table structure:
enterprise manager-->source database (medical web site )-->table--> tblAdminCountry -->right click-->all task--> generate SQL script --> ok -->save (1.sql)
Paste table structure
SQL query analizer--->select SQL server name on the network --> select sql server authontication --->login name and password-->ok--> open-->browse-->select (1.sql)-->select destination database --->Press F5
table structure is copied
---------------------------------------------------------------------------
--To search all columns of all tables in Pubs database for the keyword: "Computer"
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @DataType nvarchar(128)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
IF ISNUMERIC(@SearchStr) = 1
BEGIN
DECLARE @SearchStr3 DECIMAL(26, 8)
SET @SearchStr3 = CAST(@SearchStr AS DECIMAL(26, 8))
END
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @DataType = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND
TABLE_NAME = PARSENAME(@TableName, 1) AND
DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int','decimal','numeric','tinyint','bigint','float') AND
QUOTENAME(COLUMN_NAME) > @ColumnName
)
SET @DataType =
(
SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND
TABLE_NAME = PARSENAME(@TableName, 1) AND
QUOTENAME(COLUMN_NAME) = @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
IF (@DataType = 'char' OR @DataType = 'varchar' OR @DataType = 'nchar' OR @DataType = 'nvarchar')
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
IF ISNUMERIC(@SearchStr) = 1 AND (@DataType ='int' OR @DataType = 'decimal' OR @DataType = 'numeric' OR @DataType = 'tinyint' OR @DataType = 'bigint' OR @DataType = 'float')
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' = ' + @SearchStr3
)
END
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
Run this SP in below manner in query analyzer
SearchAllTables Computer
---------------------------------------------------------------------------
Create Trigger :
CREATE TRIGGER raviTrigger
ON emp_table_name
for insert
As
begin
insert into emp (Id, Name, age) values ( 4,444,30)
end
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
No comments:
Post a Comment