Błąd w pytaniach do egzaminu 70-433

Przygotowując się do egzaminu 70-433 przerabiałem pytania dołączone do książki. W jednym z nich znalazłem taki o to błąd :)

Zadanie (pytanie) polegało na ułożeniu zapytania z dostępnego do wyboru kodu. Zapytanie miało zwracać zwierzątka właścicieli, którzy mieszkają w Seatlle i mają powyżej 50-tki (dokładny opis na obrazku poniżej).

Niestety z dostępnego kodu nie dało się napisać zapytania. Zaciekawiło mnie więc jaka jest odpowiedź. Napisałem więc błędny kod i poprosiłem o sprawdzenie i wyświetlenie odpowiedzi. Oto co otrzymałem :

Jak można zauważyć poprawna odpowiedź jest błędna. W podzapytaniu nie ma kolumny OWNERNAME, po której następuje JOIN.
Poniżej dowód :

USE TEMPDB;

DECLARE @PETS AS TABLE
(
    PETNAME VARCHAR(20),
    OWNERNAME VARCHAR(20)
);

DECLARE @OWNERS AS TABLE
(
    OWNERNAME VARCHAR(20),
    AGE TINYINT,
    LOCATION VARCHAR(20)
);

INSERT INTO @PETS (PETNAME, OWNERNAME)
VALUES ('REX', 'BOB')
    , ('PIMPEK', 'ALA')
    , ('ŚLINIAK', 'OLA');

INSERT INTO @OWNERS (OWNERNAME, AGE, LOCATION)
VALUES ('BOB', 55, 'SEATTLE')
    , ('ALA', 10, 'WARSZAWA')
    , ('OLA', 12, 'SZCZECIN');


SELECT PET.PETNAME
FROM @PETS AS PET
JOIN
(SELECT AGE FROM @OWNERS WHERE LOCATION = 'SEATTLE') AS OWN
ON PET.OWNERNAME = OWN.OWNERNAME
WHERE OWN.AGE > 50

Jak można się domyślić zapytanie zwraca błąd :

 

Prawidłowe zapytanie powinno wyglądać tak :

SELECT PET.PETNAME
FROM @PETS AS PET
JOIN
(SELECT OWNERNAME, AGE FROM @OWNERS WHERE LOCATION = 'SEATTLE') AS OWN
ON PET.OWNERNAME = OWN.OWNERNAME
WHERE OWN.AGE > 50

Ciekawy jestem czy Wy też trafiliście na przypadki błędnych odpowiedzi do testów dołączonych to książek przygotowujących do egzaminu.

Przykład użycia ROW_NUMBER()

Jakiś czas temu jeden z moich kolegów poprosił mnie o pomoc w napisaniu zapytania gdyż sam przechodził tzw. "niemoc twórczą". A chodziło o to, że ...

Mamy tabelkę, w której jest id, nazwa, wartość. Nazwa i wartość mogą się powtarzać, id jest unikalne. Chodzi jednak o to, żeby z z tabelki pobrać tylko te wiersze (id, nazwa, wartość), które dla danej nazwy mają największą wartość. Jeżeli dla danej nazwy mamy kilka wpisów z tą samą wartością to pokazać obojętnie który.
Po chwili namysłu naskrobałem coś takiego :

USE TEMPDB;

DECLARE @T AS TABLE
(
    ID INT,
    NAZWA VARCHAR(10),
    WARTOSC INT
);

INSERT INTO @T (ID, NAZWA, WARTOSC)
VALUES (1, 'A', 10)
    , (2, 'B', 17)
    , (3, 'C', 23)
    , (4, 'A', 15)
    , (5, 'D', 33)
    , (6, 'B', 22)
    , (7, 'B', 22);

WITH CTE
AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY NAZWA ORDER BY WARTOSC DESC) AS I
        , ID
        , NAZWA
        , WARTOSC
    FROM @T
)
SELECT ID
    , NAZWA
    , WARTOSC
FROM CTE
WHERE I = 1;

Użyłem funkcji ROW_NUMBER(), aby w obrębie nazwy nadać unikalne id (PARTITION BY NAZWA). Pierwsze id nadawane jest dla największej wartości (ORDER BY WARTOSC DESC).

Samo CTE wygląda tak :

Jakieś pomysły jak można to napisać prościej, inaczej ?

Zamiana kilku wierszy w jeden za pomocą FOR XML

Jak kilka wierszy zamienić w jeden, a wartości rozdzielić przecinkiem? Odpowiedź poniżej.

DECLARE @T AS TABLE
(
    TEKST VARCHAR(50)
);

INSERT INTO @T (TEKST) 
VALUES ('ALA')
    , ('OLA')
    , ('KRZYŚ');
    
SELECT *
FROM @T;

SELECT STUFF((    SELECT ', ' + TEKST 
                FROM @T 
                FOR XML PATH('')), 1, 2, '') AS TEKST;

 

PS.

Całość odpalać na SQL 2008 i wyżej.

CTE i odwrócenie STRINGa

Dość często słyszę, że na rozmowach kwalifikacyjnych na stanowisko programisty dostaje się zadanie p.t. odwrócenie STRINGa. Oczywiście nie chodzi tu o użycie istniejącej funkcji, ale o napisanie swojej. I tu w ramach fascynacji CTE pomyślałem, że może zrobić to w T-SQL ale za pomocą pojedynczego zapytania. No i wyszło mi coś takiego :

DECLARE @STRING AS VARCHAR(8000);
SET @STRING = '123456789';

WITH CTE
AS 
(
    SELECT CAST(RIGHT(@STRING, 1) AS VARCHAR(8000)) AS REVERSE_STRING
        , DATALENGTH(@STRING) - 1 AS I
    
    UNION ALL
    
    SELECT REVERSE_STRING + RIGHT(LEFT(@STRING, I), 1)
        , I - 1
    FROM CTE
    WHERE I > 0
)
SELECT REVERSE_STRING
FROM CTE
WHERE I = 0
OPTION(MAXRECURSION 8000);

GO LOOP

Prawie nigdy nie rozdzielam zapytań w SSMS słówkiem GO. Jakoś nie jest to mi do niczego potrzebne. Dziś jednak odkryłem, że GO mam fajną właściwość. Jako parametr przyjmuje ilość wywołań danej sekwencji zapytań. Np. coś takiego :

SELECT 'WYŚWIETL MNIE 5 RAZY'
GO 5

SELECT 'A MNIE 3'
GO 3

... daje takie o to wyniki :

 

Do czego tego użyć ? Może proste wstawienie kliku testowych rekordów do tabeli, bez pisania np. pętli WHILE ?

T-SQL CHALLENGES

Jeżeli chcielibyście zmierzyć swoje siły w zadaniach praktycznych z T-SQLa to zapraszam na stronki :

1. dla początkujących : http://beyondrelational.com/blogs/tcb/default.aspx
2. dla bardziej zaawansowanych : http://beyondrelational.com/tc/

Od jakiegoś czasu biorę w nich udział i sprawia mi to wielką frajdę.

VIDEO SQL

Jakiś czas temu natknąłem się na stronkę www.sqlshare.com. Muszę przyznać, że dość często odwiedzam tą stronkę i oglądam zamieszczone tam filmy. Szczególnie upodobałem sobie publikacje, które zamieszcza Pan Andy Warren. Jego ostatnia seria na temat funkcji operujących na stringach jest świetna. Polecam wszystkim stawiającym pierwsze kroki w T-SQL.

Egzamin 70-433

Wczoraj udało mi się zdać kolejny egzamin z SQL. Tym razem był to egzamin numer 70-433 Microsoft SQL Server 2008, Database Development.