Get the last day of the current month

This query runs only in SQLite.
SELECT date('now','start of month','+1 month','-1 day');

Read file content

Load file content
SELECT LOAD_FILE("D:/Temp/test.txt");
Copy a file to another one
SELECT LOAD_FILE("D:/Temp/test.txt") INTO OUTFILE "D:/Temp/test2.txt"

Database Design - Introduction

Database Design - Introduction: "Database design with UML and SQL, 3rd edition

Also available on tomjewett.com: color tutorial, demo application, and video; for Web accessibility resources and consulting, please see The Enabled Web.

- Sent using Google Toolbar"

Ten of the Biggest Mistakes Developers Make With Databases — Developer.com

Ten of the Biggest Mistakes Developers Make With Databases — Developer.com: "Ten of the Biggest Mistakes Developers Make With Databases

- Sent using Google Toolbar"

OpenXML

What will be the output of the following scripts?

DECLARE @xml XML,@hdoc INT
SELECT @xml = '<myxml>
<value id = "1" name = "SQL Server">
 <category>2</category>
</value>
<value id = "2" name = "Oracle">
 <category>3</category>
</value>
</myxml>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT DISTINCT category 
FROM OPENXML (@hdoc, '/myxml/value',1)
WITH (id INT, name VARCHAR(10), category INT)
EXEC sp_xml_removedocument @hdoc

A. NULL
B. 3,2
C. 2,3
D. Error: Incorrect syntax near /value

Answer:[A]
Highlight to find out the answer.

Get user connections

Sometimes you need to determine who is connecting to the database by using which applications, so I think you can use this script.

SELECT t1.session_id, t1.local_tcp_port
           , t2.login_name, t2.host_name, t2.program_name
FROM sys.dm_exec_connections t1  
     INNER JOIN sys.dm_exec_sessions t2 on t1.session_id = t2.session_id

Delete duplicate records

Use this technique with small table only such as this example.^_^

DECLARE @Test TABLE (id int identity(1,1), a int, b int, c int)
INSERT INTO @Test(a,b,c)
SELECT 1,2,3 
UNION ALL SELECT 1,2,3 
UNION ALL SELECT 1,2,3
UNION ALL SELECT 2,3,4
UNION ALL SELECT 2,3,4
UNION ALL SELECT 3,4,5
UNION ALL SELECT 4,5,6
UNION ALL SELECT 4,5,6
UNION ALL SELECT 4,5,6
DELETE t2
FROM @Test t1
   INNER JOIN @Test t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c
      AND t2.id > t1.id;
SELECT * FROM @Test

SQL Server quiz

You are creating a stored procedure that will delete data from the Contact table in a SQL Server 2005 database. The stored procedure includes the following Transact-SQL statement to handle any errors that occur.

BEGIN TRY 
 BEGIN TRAN
 DELETE FROM Person.Contact 
 WHERE ContactID = @ContactID 
 COMMIT TRAN
END TRY
BEGIN CATCH 
 DECLARE @ErrorMessage nvarchar(2000) 
 DECLARE @ErrorSeverity int 
 DECLARE @ErrorState int 
 SELECT @ErrorMessage = ERROR_MESSAGE(), 
  @ErrorSeverity = ERROR_SEVERITY(), 
  @ErrorState = ERROR_STATE() 
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) 
END CATCH;

You test the stored procedure and discover that it leaves open transactions. You need to modify the stored procedure so that it properly handles the open transactions. What should you do?

A. Add a COMMIT TRAN command to the CATCH block.
B. Remove the COMMIT TRAN command from the TRY block.
C. Add a ROLLBACK TRAN command to the CATCH block.
D. Add a ROLLBACK TRAN command to the TRY block.

Answer:[C]
Highlight to find out the answer.

#Oracle #quiz

How many times does the following loop execute?
FOR iYear IN REVERSE  12 .. 1
LOOP
   CalculateSales(iYear);
END LOOP

A. 12
B. 11
C. 0
D. Error will occur

Answer:[C]
Highlight to find out the answer.

Generate a thousand daily dates starting today

CREATE TABLE test.ints(i tinyint);
INSERT INTO test.ints VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 
SELECT CURDATE() + INTERVAL t1.i*100 + t2.i*10 + t3.i DAY AS Date
FROM test.ints AS t1 
  JOIN test.ints AS t2
  JOIN test.ints AS t3
WHERE ( t1.i*100 + t2.i*10 + t3.i ) < 1000
ORDER BY Date;