SQL - Comparing tables using LEFT and RIGHT JOINS

Comparing tables using LEFT and RIGHT joins is easy. We will exploit the definitions of the LEFT and RIGHT joins to compare every records in every columns of each table. By definition, the LEFT join will returns all records of the left table even though the ON clause doesn't find any matching records on the right table. The non-matching records in the right table is still returned with the record of the left table but its value is NULLed. The RIGHT join is exactly the same as the LEFT join, except that it returns all records of the right table instead of the left table.

DOS - Variable

Set variable To declare a variable, use the following syntax:

SET variablename="value"


Call variable To call the variable, use the following syntax:

DOS - Using timestamp as unique filename

Using timestamp as unique filename %date% and %time% environment variables display the date and the time respectively. These environment variables can be used to generate a relatively unique string. Here is an example:

DOS - String Manipulation

String Substitution

SET Str="Hello, World"
ECHO %Str%
REM Replace all 'o' with 'X'
SET Str=%Str:o=X%
ECHO %Str%

Substring

REM Get string starting from the 5th position to the end.
SET mySubstring=This String will be cut.
ECHO %mySubstring%
SET mySubstring=%mySubstring:~5%
ECHO %mySubstring%

REM =================== OR ======================

REM Use CALL and SET commands to extract a substring with variable length.
SET start=5
SET length=12
SET string=This long string will be cutted.

DOS - Simulate Sleep command

Sleep for 10 seconds.

DOS - Read input from file

Reading simple input from file

REM Create an input file with data.
ECHO A > filename_test.txt
ECHO B >> filename_test.txt

REM Read input file and display each line.
for /f %%L in (filename_test.txt) do (
    ECHO I found %%L
)

Reading input from file with a delimiter

REM Create an input file with data.
ECHO A,> filename_test.txt
ECHO B C, B>> filename_test.txt
ECHO IP, C, B>> filename_test.txt

REM Read input file and display data of each line up to the 1st occurrence of the delimiter.

DOS - Loops

Infinite loop using GOTO

@ECHO OFF
 
:Loop
 
ECHO Infinite loop. Ctrl+c to stop.
 
GOTO Loop

Conditional loop using GOTO

@ECHO OFF

REM LOOP 10 times.
SET Max=10
SET i=0
 
 
:Loop
REM If i is greater than 10, then get out of this loop.
IF %i% GTR %Max% GOTO GetOut
 
ECHO %i%

REM Increment i.
SET /A i=i+1
 
GOTO Loop
 
 
:GetOut
REM Clean up variables.
SET Max=&::
SET i=&::
 
ECHO continue processing...

FOR loop in a set

REM Loop through a defined set.

SQL - Find rows with duplicate values

The ideal is to group and count the number of rows that have the same values. If there are more than 1 row, then it has duplicates. Here is the syntax of the query:

SELECT column1, column2, columnX, COUNT(*) AS Duplicates FROM tablename
GROUP BY column1, column2, columnX
HAVING COUNT(*) > 1


An example

-- Create a table t.
CREATE TABLE t
(
  a INT,
  b VARCHAR(5),
  c VARCHAR(5)
);
 
-- Insert some values into table t.
INSERT INTO t VALUES(3, 'j', 'h' );
INSERT INTO t VALUES(3, 'j', 'h' );

Debian - Install JDK

  1. Add non-free in your /etc/apt/source.lst.
    deb http://debian.yorku.ca/debian/ lenny contrib non-free
     
  2. Update the packages list.

ImageMagick - Add filename, date and time at the bottom of image

REM ##########################################################
REM Make a copy of the original image and then add filename, date and time at the bottom of the copy(*%suffix%).
REM This script was created because I need to know the filename of the printed image.
REM It requires convert.exe and identify.exe.
REM ##########################################################
SET imgExt=jpg
SET suffix=.tx1.%imgExt%
 
del /q *%suffix%
 
FOR /F "delims=" %%i IN ('dir /b *.%imgExt%') DO (CALL :addlabel "%%i")
GOTO EOF
 
:addlabel
SET filename=%1

Pages

Subscribe to OpenWritings.net RSS