Archiwa kategorii: Wszystkie

SQL INSERT INTO SELECT

SQL INSERT INTO  SELECT



DEFINICJA

Chciałbym Wam dzisiaj opisać działanie składni INSERT INTO SELECT. Jak już widzimy po słowach kluczowych coś wstawiamy (INSERT) i określamy skąd i co wrzucamy (SELECT). Żeby jakieś dane wziąć i gdzieś dodać musimy określić dwie rzeczy źródło i zakres danych do pobrania oraz cel, czyli gdzie te pobrane dane chcemy „wrzucić”. To gdzie chcemy „wrzucić” określamy po słówkach INSERT INTO, a to co i skąd chcemy pobrać określamy po słówku SELECT, tutaj będziemy konstruowali po prostu zapytanie. Składnia całej konstrukcji poniżej. PAMIĘTAJ, że struktura danych kopiowanych i struktura danych do której te dane kopiujemy muszą być zgodne.


SQL INSERT INTO SELECT składnia

INSERT INTO tabela_cel(nazwy_kolumn_po_przecinkach)
SELECT nazwy_kolumn_po_przecinkach FROM tabela_źródło
— opcjonalnie możemy dodać „sekcję” WHERE

 


Przykład zastosowania INSERT INTO SELECT (baza: Northwind)

Do przykładu wykorzystamy treningową bazę Northwind. Dodamy do niej nową tabelę „Customers_tmp” z kolumnami (CustomersID, CompanyName, City). Zrzut z tworzenia tabeli poniżej.

Nasze zadanie będzie polegało na „skopiowaniu” rekordu o CustomersID = ‚ALFKI’ z tabeli Customers (ale tylko dane z kolumn: CustomersID, CompanyName, City) do tabeli „Customers_tmp” z takimi samymi kolumnami. Zapytanie poniżej.

INSERT INTO Customers_tmp
SELECT CustomerID, CompanyName, City FROM Customers WHERE CustomerID = ‚ALFKI’

czyli do tabeli „Customers_tmp” wrzucimy dane z rekordu o CustomersID = ‚ALFKI z tabeli „Customers”, ale tylko dane z kolumn: CustomerID, CompanyName, City.
Uruchamiamy zapytanie i o poprawności wykonania zapytania mówi nam komunikat poniżej.

Teraz żeby sprawdzić czy wszystko zadziałało musimy odpytać tabelę „Customers_tmp” o wszystkie rekordy.

SELECT
*
FROM
Customers_tmp

W wyniku powinniśmy otrzymać jeden rekord


Przykład zastosowania INSERT INTO SELECT (baza: Adventureworks)

W poprzednim przykładzie kopiowaliśmy tylko jeden rekord. Teraz spóbujmy skopiować więcej rekordow i do tego celu wykorzystam bazę AdventureWorks2008R2.

Tworzymy nową tabelę o nazwie „Person_SP” (skrót SP od Sales Person) do której będziemy kopiowali osoby z tabeli „Person.Person” ale tylko takie którę są sprzedawcami. Zakres kopiowanych danych to: BusinessEntityID, FirstName, LastName.

Tworzymy nową tabelę „Person_SP”

Teraz tworzymy strukturę z INSERT INTO SELECT gdzie w części SELECT zbudujemy zapytanie które pobierze nam dane (BusinessEntityID, FirstName, LastName) o osobach które sa sprzedawcami.

INSERT INTO Person_SP
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE PersonType = ‚SP’

W tabeli „Person.Person” takich osób jest 17 więc dane tylu osób powinny być skopiowane do tabeli „Person_SP”

Uruchamiamy zapytanie (F5).
Powinniśmy otrzymać komunikat o poprawnym wykonaniu naszej konstrukcji.

Teraz żeby sprawdzić czy w tabeli „Person_SP” jest 17 rekordów z danymi budujemy zapytanie.

SELECT
*
FROM
Person_SP

Wynikiem zapytanie powinno wyć wygenerowanie 17-tu rekordow, co widzimy na zrzucie poniżej.


SQL CREATE TABLE

SQL CREATE TABLE



DEFINICJA

Polecenie CREATE TABLE służy do tworzenia table w bazie danych. Przy okazji tworzenia tabel określamy jakie kolumny mają się znaleźć w danej tabeli, jakiego typu dane mają tam być przechowywane (np. INTEGER, VARCHAR, DECIMAL itp.) i jakie są powiązania danej tabeli z innymi tabelami w bazie danych. Przy tworzeniu tabeli możemy wskazać także czy dana kolumna ma być kluczem głównym czy obcym (klucze główny i obcy opisany w innym wpisie) lub czy może przechowywać wartości NULL.


Składnia polecenia CREATE TABLE

  CREATE TABLE nazwa_tabeli
(
nazwa_kolumny1 typ_danych(rozmiar),
nazwa_kolumny2 typ_danych(rozmiar),
….
);


Przykład tworzenia tabeli „KLIENCI”

  CREATE TABLE Klienci
(
ID_klienta INT CONSTRAINT PK_klient PRIMARY KEY
,ID_oddzialu INT CONSTRAINT FK_ID_oddzial_Klienci REFERENCES Oddzialy(ID_oddzialu)
,imie NVARCHAR(20)
,nazwisko NVARCHAR(25)
,telefon INT
,mail NVARCHAR(50)
,dataUrodzenia DATE
,dataZalozeniaKonta DATE
,dataZamknieciaKonta DATE
);


Adventureworks 33. Zaprezentuj zestawienie sprzedaży w latach (wg liczby zamówień)

SQL tutorial. AdventureWorks exercises. SQL SERVER.

Baza: AdventureWorks

Zadanie nr: 33

Treść:  Zaprezentuj zestawienie sprzedaży w latach (wg liczby zamówień)

Polecenia/funkcje w zapytaniu: SELECT, FROM, GROUP BY, ORDER BY, YEAR(), COUNT()

Rozwiązanie:

Zapytanie:

SELECT
YEAR(O.OrderDate) AS Rok
,COUNT(*) AS LiczbaZamowien
FROM
Sales.SalesOrderHeader O
GROUP BY
YEAR(O.OrderDate)
ORDER BY
2 DESC

Wynik:

Pobierz skrypt sql

Więcej przykładów na anonco.pl

Northwind – ćwiczenia praktyczne. 8. Pokaż wszystkich pracowników których nazwiska zaczynają się na literę D. Rozwiązanie.

Zadanie 8.

Pokaż wszystkich pracowników których nazwiska zaczynają się na literę D.

 

Użyte polecenia: SELECT, FROM, WHERE, LIKE, Aliasy

Użyte tabele: Employees

Zapytanie

northwind ćwiczenia praktyczne northwind practical exercises

skrypt

Efekt uruchomienia zapytania:

Wynik: 2 rekordy.

northwind ćwiczenia praktyczne northwind practical exercises 8

 

Kurs SQL. Zadanie 10. Pokaż wszystkich pracowników, którzy pracują w dziale logistyki lub informatyki.

Czego się dowiesz czytając ten wpis:

  • poznasz predykat IN,
  • poznasz operator OR,
  • powtórzysz wiedzę nt. ALIASÓW i WHERE.

 

Zadanie 10.

Pokaż wszystkich pracowników, którzy pracują w dziale logistyki lub informatyki. W wyniku wyświetl tylko imię i nazwisko pracownika.

Rozwiązanie:

Ponieważ nie znamy jeszcze łączenia tabel będziemy musieli się posłużyć się identyfikatorami wydziałów logistyki i informatyki, które znajdziemy w tabeli „Działy”. Logistyka ma ID=60 a informatyka ma ID=70.

Zaczynamy oczywiście od klauzul SELECT i FROM. Źródłem danych będzie tabela „Pracownicy”, więc:

FROM
Pracownicy AS P

Przypominam i zachęcam do stosowania aliasów tak jak powyżej, nadaliśmy alias P dla tabeli „Pracownicy”, którym za chwilę będziemy się posługiwać w klauzuli SELECT. A w klauzuli SELECT „wyświetlamy” imię i nazwisko (wykorzystując nadany alias), czyli:

SELECT
P.imie
,P.nazwisko

Teraz musimy ograniczyć w wynik zapytania tylko do tych pracowników, którzy pracują w logistyce lub informatyce. Jak już wcześniej pisałem wykorzystamy do tego identyfikatory (ID) tych działów i tak kolejno logistyka ma ID=60 a informatyka ma ID=70.

Aby zawęzić wyniki zapytania tylko do tych rekordów, które spełniają nasze kryterium, w naszym przypadku pracownik musi pracować w logistyce lub informatyce, wykorzystamy znaną nam już klauzulę WHERE, po której definiujemy nasze kryterium. Wiemy, że identyfikator działu znajduje się w polu „ID_dzialu”. Spójrz na definicję poniżej.

WHERE
P.ID_dzialu IN (60,70)

Skorzystaliśmy z operatora IN. Powyższy zapis znaczy tyle co, znajdź tylko te rekordy w których pole „ID_dzialu” przyjmuje wartości ze zbioru który zadeklarowaliśmy, czyli przyjmuje wartość 60 lub 70. Jeśli w tym polu jest wartość z naszego zbioru dany rekord będzie wyświetlony w wynikach zapytania.

Jest jeszcze inna metoda na osiągnięcie tego samego efektu. Wykorzystamy do tego operator OR, który oznacza LUB. Patrz kod poniżej.

WHERE
P.ID_dzialu = 60
OR P.ID_dzialu = 70

Powyższy kod spowoduje wyświetlenia rekordów gdzie w polu „ID_dzialu” znajduje się wartość 60 LUB (OR) znajduje się wartość 70.

Efekt uruchomienia zapytania z IN lub z OR będzie identyczny to Wam pozostawiam osąd, która metoda jest lepsza. Wydaje mi się jednak, że zapis z IN jest czytelniejszy i łatwiejszy w definicji. Kompletne zapytania z IN i OR poniżej.

wariant z IN wariant z OR
SELECT
P.imie
,P.nazwisko
FROM
Pracownicy AS P
WHERE
P.ID_dzialu IN (60,70)
SELECT
P.imie
,P.nazwisko
FROM
Pracownicy AS P
WHERE
P.ID_dzialu = 60
OR P.ID_dzialu = 70

 

Efekt uruchomienia zapytania poniżej.

kurs sql sql course sql where sql in sql or

 
poprzedni | następny

Kurs SQL. Zadanie 9. Pokaż wszystkich pracowników, których nazwiska zaczynają się na M.

Czego się dowiesz czytając ten wpis:

  • poznasz klauzulę WHERE,
  • poznasz predykat LIKE,

Zadanie 9.

Pokaż wszystkich pracowników, których nazwiska zaczynają się na M. Wyświetl tylko Imię i Nazwisko pracownika.

Rozwiązanie:

Określmy na początek źródło danych, czyli w naszym przypadku będzie to tabela „Pracownicy”.

FROM
Pracownicy AS P

P to alias dla tabeli „Pracownicy”.

W wynikach zapytania mamy wyświetlić tylko imiona i nazwiska więc nasza część SELECT będzie wyglądała następująco. Pamiętaj, że utworzyliśmy alias P.

SELECT
P.imie
,P.nazwisko

I teraz dochodzimy do sedna sprawy. Jak wyświetlić tylko tych pracowników, których nazwisko zaczyna się a literę „M”. Pomoże nam w tym predykat LIKE, który sprawdza czy wyrażenia znakowe w danej kolumnie są zgodne ze zdefiniowanym wzorcem. Poniżej definicja predykatu LIKE.

LIKE wzorzec

W naszym przypadku chcemy sprawdzić czy dane nazwisko zaczyna się na literę „M”, więc w miejsce „wzorca” wpisujemy ‘M%’. Znak % zastępuje ciąg znaków. Czyli każde wyrażenie (u nas Nazwisko) którego pierwszym znakiem jest M (kolejne znaki nie mają znaczenia za to odpowiada znak %) będzie zgodny ze zdefiniowanym wzorcem. Ale jeszcze musimy skorzystać z klauzuli WHERE (definicja poniżej).

WHERE pole = kryterium

Pojawiło nam się nowe słówko WHERE. Jest to klauzula, która zawęzi nam wyniki zapytania do tych rekordów, które spełnią zadane przez nas kryterium. W naszym przykładzie kryterium będzie zgodność ze zdefiniowanym wzorcem LIKE. Całość przybierze więc formę.

WHERE P.nazwisko LIKE ‘M%’

W naszym przykładzie zapis ten znaczy tyle co: pokaż mi wszystkie rekordy w których pole nazwisko zaczyna się na znak M.

Nasze całe zapytanie poniżej.

SELECT
P.imie
,P.nazwisko
FROM
Pracownicy AS P
WHERE
P.nazwisko LIKE 'M%'

 

Poniżej efekt uruchomienia zapytania.

kurs sql where like

poprzedni | następny

Kurs SQL. Zadanie 8. Pokaż dniówkę, tygodniówkę, pensję miesięczną i roczną wszystkich pracowników.

Czego się dowiesz czytając ten wpis:

  • poznasz funkcję ROUND i CAST,
  • poznasz klauzulę ORDER BY
  • powtórzysz jak stosować operacje arytmetyczne,
  • powtórzysz wiedzę na temat komend: SELECT i

 

Zadanie 8.

Oblicz ile dany pracownik zarabia dziennie, tygodniowo, miesięcznie i rocznie. Wynik posortuj malejąco. Dane wyświetl w konwencji: Imię i Nazwisko, Dniówka, Tygodniówka, Pensja miesięczna, Pensja roczna.

Rozwiązanie:

Na początek zajmijmy się tylko obliczeniem dniówki. W naszym przykładzie nie będziemy brali pod uwagę dodatków tylko pensję podstawową. Skoro w 2016 r. średnia ilość dni pracy w miesiącu wynosi 21 dni, właśnie taką wartością posłużymy się w zadaniu. Żeby obliczyć dniówkę wystarczy podzielić naszą pensję podstawową przed średnią ilość dni roboczych w miesiącu, w naszym przypadku to 21. Zacznijmy od wyświetlenia imienia i nazwiska, określenia odpowiedniej operacji arytmetycznej do obliczenia dniówki i określenia źródła danych.

1. SELECT
2. P.imie + ' ' + P.nazwisko AS [Imię i Nazwisko]
3. ,P.pensja/23 AS [Dniówka]
4. FROM
5. Pracownicy P

Widać, że w źródle danych dla tabeli „Pracownicy” zastosowałem alias P (linia 5), który wykorzystuję w klauzuli SELECT. W drugiej linii dodałem trzy wyrażenia tekstowe imię, nazwisko i spację. Spacja posłużyła mi do tego żeby w wynikach imię i nazwisko było ładnie oddzielone. Dodałem tutaj także alias [Imię i Nazwisko]. Trzecia linia to wyrażenie obliczające dniówkę. Jak wspomniałem wcześniej posłużymy się liczą 21 (średnia ilość dni roboczych w 2016 r.). Obliczenie dniówki to zwykła operacja arytmetyczna, czyli pensja pracownika podzielona przez 21, dodany także alias [Dniówka]. Uruchamiamy. Poniżej pierwsze 3 rekordy (poglądowo).

kurs-sql-round-cast-where-order-by

Wszystko fajnie nam się poobliczało ale w wynikach mamy dość dożą precyzję do 6 miejsc po przecinku. Do zaokrąglenia tego wyniku zastosujemy funkcję ROUND. Więc nasz wiersz z dniówką przybierze postać.

ROUND(P.pensja/23 , 2) AS [Dniówka]

Wynik uruchomienia poniżej.

kurs-sql-round-cast-where-order-by

Krótki komentarz do funkcji ROUND. Zaokrągla nam wynik do wymaganego miejsca po przecinku. Funkcja ta wymaga 2 parametrów.

ROUND( argument1 , argument2 )

Argument 1 to wyrażenie arytmetyczne, w naszym przypadku obliczenie dniówki, natomiast argument 2 to liczbą określająca, do którego miejsca po przecinku chcemy zaokrąglić wynik. Załóżmy, że my chcemy zaokrąglić nasz wynik do drugiego miejsca po przecinku, dlatego jako drugi argument powyżej jest wartość 2.

Zerknij teraz na wynik. Widać, że owszem wynik jest zaokrąglony do dwóch miejsc po przecinku ale dalej po przecinku prezentowanych jest sześć znaków. Aby to „naprawić” zastosujemy drugą funkcję CAST. Ta funkcja służy do konwersji danych jednego typu na inny typ np. typ liczbowy na ciąg znaków, ale możemy ją także zastosować do naszych celów. Poniże definicja funkcji CAST.

CAST( WARTOŚĆ_KONWERTOWANA AS DOCELOWY_TYP_DANYCH

Za wartość konwertowaną musimy podstawić całe wyrażenie ROUND(P.pensja/23 , 2) a docelowy typ danych ustawiamy na DECIMAL(6,2). Czyli nasze zapytanie w tym momencie będzie wyglądało tak.

SELECT
P.imie + ' ' + P.nazwisko AS [Imiê i Nazwisko]
,CAST(ROUND(P.pensja/23,2) AS DECIMAL(6,2)) AS [Dniówka]
FROM
Pracownicy P

Wynik (3 pierwsze pozycje) uruchomienia zapytania poniżej.

kurs-sql-round-cast-where-order-by

Efekt jest więc osiągnięty. Wartości w kolumnie dniówka są zaokrąglone i wyświetlane do dwóch miejsc po przecinku.

Korzystając z dotychczasowej wiedzy możemy dodać do klauzuli SELECT nowe kolumny. Pozostało nam wyświetlenie kolumny tygodniówka, pensja miesięczna i pensja roczna.

Zajmijmy się linią dotyczącą obliczenia tygodniówki. Do tego celu możemy wykorzystać linię obliczającą dniówkę i lekko ją zmodyfikować. Wystarczy wyrażenie obliczające dniówkę pomnożyć przez liczbę roboczych dni w tygodniu, czyli przez 5.

Pensja miesięczna to nic innego jak wyświetlenie pola „pensja” z tabeli „Pracownicy” a pensja roczna to pensja miesięczna pomnożona przez 12.

Nasze zapytanie przybierze końcową postać.

SELECT
P.imie + ' ' + P.nazwisko AS [Imię i Nazwisko]
,CAST(ROUND(P.pensja/23,2)AS DECIMAL(6,2)) AS [Dniówka]
,CAST(ROUND(P.pensja/23*5,2)AS DECIMAL(6,2)) AS [Tygodniówka]
,P.pensja AS [Pensja miesięczna]
,P.pensja*12 AS [Pensja roczna]
FROM
Pracownicy P

Pozostaje tylko posortować malejąco wyniki naszego zapytania. Do tego służy klauzula ORDER BY. Zaraz po niej podajemy według których kolumn chcemy sortować wyniki i określamy model sortowania malejąco DESC lub rosnąco ASC. W sumie sortowania rosnącego nie musimy deklarować jest to domyślny model sortowania. W naszym przypadku chcemy posortować wyniki malejąco więc dopisujemy na końcu klauzuli DESC. Sortowanie może odbywać się po kilku kolumnach wtedy poszczególne argumenty (kolumny) przedzielamy przecinkiem. W naszym zadania skoro wszystkie wartości w kolumnach zależą od wartości pensji miesięcznej to wystarczy posortować wyniki właśnie po tej kolumnie. Sortować możemy także po aliasach co właśnie wykorzystałem w naszym przykładzie.

SELECT
P.imie + ' ' + P.nazwisko AS [Imię i Nazwisko]
,CAST(ROUND(P.pensja/23,2)AS DECIMAL(6,2)) AS [Dniówka]
,CAST(ROUND(P.pensja/23*5,2)AS DECIMAL(6,2)) AS [Tygodniówka]
,P.pensja AS [Pensja miesiêczna]
,P.pensja*12 AS [Pensja roczna]
FROM
Pracownicy P
Order by
[Pensja miesięczna]DESC

Wynik uruchomienia naszego ostatecznego zapytania poniżej.

kurs-sql-round-cast-where-order-by

poprzedni | następny