Here are several samples for read / select process in SQL Server. I’m using SQL Server 2005 and hopefully this script can be used for newer version of SQL Version. I’m using tempdb database which is always available after installing SQL Server.
[code language=”sql” title=”Create Data Sample”]
IF OBJECT_ID(‘tempdb.dbo.#sample_table1’
IS NOT NULL
DROP TABLE #sample_table1
CREATE TABLE #sample_table1 (
ID INT IDENTITY PRIMARY KEY,
COL1 VARCHAR(20),
COL2 VARCHAR(20),
COL3 VARCHAR(20),
COL4 VARCHAR(20),
COL5 VARCHAR(20)
)
INSERT INTO #sample_table1
(COL1, COL2, COL3, COL4, COL5)
SELECT ‘VAL11’, ‘VAL21’, ‘VAL31’, ‘VAL41’, ‘VAL51’ UNION ALL
SELECT ‘VAL12’, ‘VAL22’, ‘VAL32’, ‘VAL42’, ‘VAL52’ UNION ALL
SELECT ‘VAL13’, ‘VAL23’, ‘VAL33’, ‘VAL43’, ‘VAL53’ UNION ALL
SELECT ‘VAL14’, ‘VAL24’, ‘VAL34’, ‘VAL44’, ‘VAL54’ UNION ALL
SELECT ‘VAL15’, ‘VAL25’, ‘VAL35’, ‘VAL45’, ‘VAL55’ UNION ALL
SELECT ‘VAL16’, ‘VAL26’, ‘VAL36’, ‘VAL46’, ‘VAL56’ UNION ALL
SELECT ‘VAL17’, ‘VAL27’, ‘VAL37’, ‘VAL47’, ‘VAL57’;
IF OBJECT_ID(‘tempdb.dbo.#sample_table2’ IS NOT NULL
DROP TABLE #sample_table2
CREATE TABLE #sample_table2 (
ID INT IDENTITY PRIMARY KEY,
COL1 VARCHAR(20),
COL2 VARCHAR(20),
COL3 VARCHAR(20)
)
INSERT INTO #sample_table2
(COL1, COL2, COL3)
SELECT ‘VAL11’, ‘VAL21’, ‘VAL31’ UNION ALL
SELECT ‘VAL12’, ‘VAL21’, ‘VAL32’ UNION ALL
SELECT ‘VAL13’, ‘VAL21’, ‘VAL33’;
[/code]
[code language=”sql” title=”Sample Query”]
— SAMPLE READ USING * OR COLUMN NAME
SELECT * FROM #sample_table1
SELECT COL1, COL2, COL3, COL4, COL5 FROM #sample_table1
— SAMPLE READ WITH CONDITION (WHERE) :
— 1. EQUAL =
SELECT * FROM #sample_table1 WHERE COL1 = ‘VAL11’
— 2. NOT EQUAL <>
SELECT * FROM #sample_table1 WHERE COL1 <> ‘VAL11’
— 3. GREATER > OR >=
SELECT * FROM #sample_table1 WHERE ID > 4
— 4. LESS < OR <=
SELECT * FROM #sample_table1 WHERE ID < 3
— 5. LIKE / NOT LIKE CLAUSE
— 5.1 END BY COL1
SELECT * FROM #sample_table1 WHERE COL1 LIKE ‘%COL1’
— 5.2 BEGIN WITH COL1
SELECT * FROM #sample_table1 WHERE COL1 LIKE ‘COL1%’
— 5.3 CONTAIN COL1
SELECT * FROM #sample_table1 WHERE COL1 LIKE ‘%COL1%’
— 6. IN / NOT IN CLAUSE
SELECT * FROM #sample_table1 WHERE ID IN (1, 2, 3)
— 7. SUB QUERY
SELECT * FROM #sample_table1 WHERE COL1 IN (SELECT COL1 FROM #sample_table2 WHERE COL2 = ‘VAL21’
— 8. BETWEEN / NOT BETWEEN
SELECT * FROM #sample_table1 WHERE ID BETWEEN 1 AND 3
[/code]
Marifnst, 20130427
Leave a Reply