1. Selecionado os registros de uma tabela(aloc_cand) que nao existem na outra(car_res_le):

SELECT Num_Fic, Num_Seq_Car
  FROM Aloc_Cand
 WHERE ( Num_Fic NOT IN (SELECT  num_fic FROM Car_Res_Le)) 
 ORDER BY Num_Seq_Car;

2. Connection String do ADO (Delphi):

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=nome_do_usuario;Initial Catalog=nome_do_banco;Data Source=host_servidor

3. Email de todos os bancos(concurso):

SELECT     af.eMail
FROM         afpr2004.dbo.Candidato af
WHERE     (af.eMail <> '')
UNION
SELECT     ce.eMail
FROM         cefet2004.dbo.Candidato ce
WHERE     (ce.eMail <> '')
UNION
SELECT     cn.eMail
FROM         cnen2004.dbo.Candidato cn
WHERE     cn.eMail <> ''
UNION
SELECT     epr.eMail
FROM         ectampr2004.dbo.Candidato epr
WHERE     epr.eMail <> ''
UNION
SELECT     ego.eMail
FROM         ectgo2004.dbo.Candidato ego
WHERE     ego.eMail <> ''
UNION
SELECT     emt.eMail
FROM         ectmt2004.dbo.Candidato emt
WHERE     emt.eMail <> ''
UNION
SELECT     emt1.eMail
FROM         ectmt2004_1.dbo.Candidato emt1
WHERE     emt1.eMail <> ''
UNION
SELECT     ib.eMail
FROM         ibge2004.dbo.Candidato ib
WHERE     ib.eMail <> ''
UNION
SELECT     pa.eMail
FROM         pmmac2004.dbo.Candidato pa
WHERE     pa.eMail <> ''
UNION
SELECT     pd.eMail
FROM         prodaub2004.dbo.Candidato pd
WHERE     pd.eMail <> ''
UNION
SELECT     s1.eMail
FROM         satuba012004.dbo.Candidato s1
WHERE     s1.eMail <> ''
UNION
SELECT     s2.eMail
FROM         satuba022004.dbo.Candidato s2
WHERE     s2.eMail <> ''
UNION
SELECT     t4.eMail
FROM         trf2004.dbo.Candidato t4
WHERE     t4.eMail <> ''
UNION
SELECT     t3.eMail
FROM         trt2003.dbo.Candidato t3
WHERE     t3.eMail <> ''
UNION
SELECT     tt.eMail
FROM         trt2004.dbo.Candidato tt
WHERE     tt.eMail <> ''
UNION
SELECT     tp.eMail
FROM         trtpr2003.dbo.Candidato tp
WHERE     tp.eMail <> ''
UNION
SELECT     tr.eMail
FROM         trtrj2004.dbo.Candidato tr
WHERE     tr.eMail <> ''
UNION
SELECT     ro.eMail
FROM         trtro2004.dbo.Candidato ro
WHERE     ro.eMail <> ''

4. Total geral dos classificados

select  count(cg.cod)
  from candidato c, classificacao cla, cargo cg
 where c.num_ficha = cla.num_ficha
   and c.cod_cargo = cg.cod 
   and cla.flag_class = 'S'
group by cg.cod

  SELECT Cargo.Nome AS Cargo, (Candidato.num_ficha) AS Inscricao 
    FROM Candidato INNER JOIN Opcao_Local ON Candidato.Opcao_Local = Opcao_Local.Cod 
         INNER JOIN Cargo ON Candidato.Cod_Cargo = Cargo.Cod 
         INNER JOIN Classificacao ON Candidato.num_ficha = Classificacao.Num_Ficha
   WHERE Candidato.Flag_Exclusao = 'N'
     and Classificacao.Flag_Class = 'S'

5. Total de aprovados por cargo

SELECT Cargo.Nome AS Cargo, Count(Candidato.num_ficha) AS Inscricao 
FROM   Candidato INNER JOIN Opcao_Local ON Candidato.Opcao_Local = Opcao_Local.Cod 
       INNER JOIN Cargo ON Candidato.Cod_Cargo = Cargo.Cod 
       INNER JOIN Classificacao ON Candidato.num_ficha = Classificacao.Num_Ficha
WHERE  Candidato.Flag_Exclusao = 'N'
GROUP BY Cargo.Nome, Classificacao.Flag_Class
HAVING Classificacao.Flag_Class = 'S'

6. ???

select c.num_ficha
  from candidato c, cargo cg, 
       prova p, nota_candidato n,
       provagrupo pg
 where c.cod_cargo = cg.cod 
   and cg.cod = p.cod_cargo
   and n.num_ficha = c.num_ficha
   and n.nota <> 0
   and n.cod_prova in(2,1)
   and pg.cod_prova = n.cod_prova
group by c.num_ficha
order by c.num_ficha

7. The following example copies the selected rows in a db grid to a list box:

procedure TForm1.Button1Click(Sender: TObject);
var
  i, j: Integer;
  s: string;
begin
  if DBGrid1.SelectedRows.Count>0 then
    with DBGrid1.DataSource.DataSet do
      for i:=0 to DBGrid1.SelectedRows.Count-1 do
      begin
        GotoBookmark(pointer(DBGrid1.SelectedRows.Items[i]));
        for j := 0 to FieldCount-1 do
        begin

          if (j>0) then s:=s+', ';
          s:=s+Fields[j].AsString;
        end;
        Listbox1.Items.Add(s);
        s:= '';
      end;
end;

8. Relacao por nome, cargo, num_ficha e nota especifico de classificados:

SELECT Cargo.Cod AS CodCargo, Cargo.Nome AS Cargo, Classificacao.Class_Objetiva AS ClassObjetiva, Candidato.num_ficha AS Inscricao, Candidato.Nome AS Candidato, Candidato.Data_Nasc AS DataNasc, Classificacao.Nota AS NotaObjetiva, Classificacao.Flag_Class
FROM (Candidato INNER JOIN Cargo ON Candidato.Cod_Cargo=Cargo.Cod) INNER JOIN Classificacao ON Candidato.num_ficha=Classificacao.Num_Ficha
WHERE candidato.num_ficha In ('3000753','3009645','3013634','3005208','3003647','3006581')
ORDER BY Cargo.Cod, Candidato.Nome;

9. Acesso a base de dados SQLServer atraves do VB:

Private Sub Total_Produtos()
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim total_geral

    db.ConnectionTimeout = TIME_OUT
    db.Open "Provider=SQLOLEDB;Data Source=192.168.1.1;DataBase=BCO_2003;", "eliel", "123456"

    sql = " SELECT COUNT(nome) as total " _
        & "   FROM Produto              "
       
    Set rs = db.Execute(sql)
    
    total_geral = rs("total")
    rotulo_total.Caption = Str(total_geral)

End Sub

10. ???

SELECT Cargo.Cod AS CodCargo, Cargo.Nome AS Cargo, Opcao_Vaga.Cod AS CodVaga, Opcao_Vaga.Nome AS Vaga, Candidato.num_ficha AS Inscricao, Candidato.Nome AS Candidato, Candidato.Data_Nasc AS DataNasc, Candidato.Vaga_Deficiente, Classificacao.Flag_Class, Classificacao.Class_Objetiva, Classificacao.Class_Def, Classificacao.Nota, IIf([class_objetiva] Is Null,"Desc.",[class_objetiva] & "°") AS Classificacao
FROM Opcao_Vaga INNER JOIN (Cargo INNER JOIN (Candidato INNER JOIN Classificacao ON Candidato.num_ficha = Classificacao.Num_Ficha) ON Cargo.Cod = Candidato.Cod_Cargo) ON Opcao_Vaga.Cod = Candidato.Opcao_Vaga
ORDER BY Cargo.Cod, Opcao_Vaga.Cod, Candidato.Nome;

11. Copiando partes de um campo e alterando para caixa alta:

SELECT    CHARINDEX(' ', ltrim(
                      SUBSTRING(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, { fn LENGTH(c.Nome) 
                      })), CHARINDEX(' ', SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, 
                      { fn LENGTH(c.Nome) }))), { fn LENGTH(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) 
                      + 2, { fn LENGTH(c.Nome) }))) }) + ' ')
                 ) AS terceiro_espaco, 

                     substring(
                     ltrim(SUBSTRING(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, { fn LENGTH(c.Nome) 
                      })), CHARINDEX(' ', SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, 
                      { fn LENGTH(c.Nome) }))), { fn LENGTH(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) 
                      + 2, { fn LENGTH(c.Nome) }))) })),  
                        1,
                    CHARINDEX(' ', ltrim(
                      SUBSTRING(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, { fn LENGTH(c.Nome) 
                      })), CHARINDEX(' ', SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, 
                      { fn LENGTH(c.Nome) }))), { fn LENGTH(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) 
                      + 2, { fn LENGTH(c.Nome) }))) }) + ' '))
                    ) as terceiro_nome, 


                      SUBSTRING(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, { fn LENGTH(c.Nome) 
                      })), CHARINDEX(' ', SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, 
                      { fn LENGTH(c.Nome) }))), { fn LENGTH(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) 
                      + 2, { fn LENGTH(c.Nome) }))) }) + ' ' AS terceiro_completo, 

                     substring(SUBSTRING(ltrim(c.Nome), CHARINDEX(' ', ltrim(c.Nome)) + 1, 1) + LOWER(SUBSTRING(ltrim(c.Nome), CHARINDEX(' ', ltrim(c.Nome)) + 2, { fn LENGTH(ltrim(c.Nome)) })), 1, 
                           CHARINDEX(' ', SUBSTRING(ltrim(c.Nome), CHARINDEX(' ', ltrim(c.Nome)) + 1, 1)  + LOWER(SUBSTRING(ltrim(c.Nome), CHARINDEX(' ', ltrim(c.Nome)) + 2, { fn LENGTH(c.Nome) })))) as segundo_nome, 
                     
                     SUBSTRING(ltrim(c.Nome), CHARINDEX(' ', ltrim(c.Nome)) + 1, 1) + LOWER(SUBSTRING(ltrim(c.Nome), CHARINDEX(' ', ltrim(c.Nome)) + 2, { fn LENGTH(ltrim(c.Nome)) })) AS segundo_completo, 
                      CHARINDEX(' ', SUBSTRING(ltrim(c.Nome), CHARINDEX(' ', ltrim(c.Nome)) + 1, 1)  + LOWER(SUBSTRING(ltrim(c.Nome), CHARINDEX(' ', ltrim(c.Nome)) + 2, { fn LENGTH(c.Nome) }))) AS segundo_espacao, 

                      upper(substring(c.nome,1,1)) + LOWER(SUBSTRING(ltrim(c.Nome), 2, CHARINDEX(' ', ltrim(c.Nome)) - 1)) AS primeiro_nome, 
                      CHARINDEX(' ', ltrim(c.Nome)) AS primeiro_espaco, 
                      LOWER(SUBSTRING(c.Nome, 1, { fn LENGTH(c.Nome) }))  AS completo

FROM         Classificacao_bkp b 
INNER JOIN
                   Candidato c ON b.Num_Ficha = c.num_ficha

12. Copiando partes de um campo e alterando para caixa alta (parte 2):

SELECT     SUBSTRING(c.Nome, 1, 1) + LOWER(SUBSTRING(c.Nome, 2, CHARINDEX(' ', c.Nome) - 1)) + 
           SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, { fn LENGTH(c.Nome) })) AS Expr3, 
           SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 1, 1) + LOWER(SUBSTRING(c.Nome, CHARINDEX(' ', c.Nome) + 2, { fn LENGTH(c.Nome) })) AS Expr2, 
           SUBSTRING(c.Nome, 1, 1) + LOWER(SUBSTRING(c.Nome, 2, CHARINDEX(' ', c.Nome) - 1)) AS Expr5, 
           CHARINDEX(' ', c.Nome) AS Expr1, 
           SUBSTRING(c.Nome, 1, 1)  + LOWER(SUBSTRING(c.Nome, 2, { fn LENGTH(c.Nome) })) AS Expr5, b.Nota
FROM       Classificacao_bkp b INNER JOIN
           Candidato c ON b.Num_Ficha = c.num_ficha

13.