Állatorvos

— 3. feladat, Listázza ki a kutyatábla minden olyan mezőjét, amely nem idegenkulcs(FK)

• use allatorvos
• select *
• from kutya

— 4. feladat, Listázza ki a 10. kerületben élő gazdák nevét ABC sorrendben

• use allatorvos
• SELECT nev as ‘Gazda neve’, kerulet as ‘Kerület’
• FROM gazda
• WHERE kerulet like ’10’
• ORDER BY nev ASC

— 5. feladat, Listázza ki fajtánként csoportosítva az adatbázisban szereplő kutyák számát!

• use allatorvos
• SELECT f.fajtanev as ‘Fajta neve’, COUNT(f.id) as ‘darabszám’
• FROM kutya k INNER JOIN fajta f on k.fajtaId= f.id
• group by f.fajtanev

— 6. feladat, Listázza ki azokat a 2010 első hónapjában megkezdett kezeléseket, amelyek JELLEG mezőjében megtalálható”kezelés” szórészlet! Jelenjen meg a kezelés jellege, kezdő és befejezés dátuma és a kezelés díja!

• use allatorvos
• select kt.jelleg, k.kezdet, k.veg, k.dij
• from kezelestipus kt inner join kezeles k on kt.id=k.kezelestipusId
• where kt.jelleg like ‘%kezelés%’ and k.kezdet >= ‘2010-01-01’ and k.veg <= ‘2010-01-31’

— 7 feladat, Határozza meg, hogy Verpeléti Klaudia hányszor vitte kedvencét állatorvoshoz,
— és összesen mennyit fizetett a szolgáltatásokért!
— A megjelenő oszlopok fejlécében „alkalom” és az „összesen” szöveg jelenjen meg!
— Feltételezheti, hogy csak egy Verpeléti Katalin nevű gazda van az adatbázisban.

• use allatorvos
• select COUNT(*) as ‘alkalom’, SUM(ke.dij) as ‘összesen’
• from kutya k inner join gazda g on k.gazdaId=g.id inner join kezeles ke on k.id=ke.kutyaId
• where nev like ‘%Verpeléti Klaudia%’
• group by nev

EUROSKILLS 2018

— (3. feladat:) Az ünnepélyes eredményhirdetésen számos arany-, ezüst- és bronzérmet osztottak ki. Azoknak, akik lemaradtak a dobogóról, de legalább 700 pontot kaptak, „kiválósági érem” lett a jutalma! (Tudjuk, hogy egyik dobogós sem kapott 700 pontnál kevesebbet.) Lekérdezéssel határozza meg, hogy hány érmet osztottak ki! A számított mező címkéjét a minta szerint állítsa be!

• use euroskills
• select COUNT (id) as ‘érmek’
• from versenyzo
• where pont > 700

— (4. feladat:) Az orszag táblában szereplő nemzetek közül nem mindegyik vett részt a budapesti versenyen. Lekérdezéssel jelenítse meg ismétlődések nélkül azoknak az országoknak a nevét ábécé rendben, melyek indítottak versenyzőt a 2018-as EuroSkillsen!

• use euroskills
• select distinct orszagNev as ‘Országok indítottak versenyzőt’
• from orszag o inner join versenyzo v on o.id=v.orszagId
• order by orszagNev asc

— (5. feladat:) Hány versenyző vett részt a megmérettetésen az egyes szakmákban? Lekérdezéssel jelenítse meg a szakmák megnevezését és a részt vevő versenyzők számát úgy, hogy a legnépesebb
szakmával kezdődjön a lista! A számított mező címkéjét a minta szerint állítsa be!

• use euroskills
• select szakmaNev,COUNT(*) as ‘versenyzők száma’
• from versenyzo v inner join szakma sz on v.szakmaId=sz.id
• group by szakmaNev
• order by 2 desc;

— (6. feladat:) Lekérdezéssel készítse el a versenyzők összesített eredménylistáját! A lekérdezés a versenyzők nevét, országának nevét, szakmájának nevét és az elért pontszámot tartalmazza! Az eredményt rendezze a minta alapján pontszám szerint csökkenően! Azonos pontszám esetén ábécé rendben jelenjenek meg a versenyzők nevei! A lekérdezés által megjelenített sorokat korlátozza a legjobb 25 eredményre!

• use euroskills
• select top 25 nev, orszag.orszagNev, szakma.szakmaNev, pont
• from versenyzo inner join szakma
• on versenyzo.szakmaId = szakmaId inner join orszag
• on versenyzo.orszagId = orszagId
• order by pont desc

CNT – Alagút

— 3. Listázza ki az adatbázisban található alagutak nevét, hosszát! A lista az alagutak hossza szerint növekvő rendben legyen!

• use cnt
• select nev, hossz
• from alagut
• order by hossz asc

— 4. Listázza ki azoknakk az alagutaknak a nevét, melyek két tartományban fekszenek!

• use cnt
• select a.nev as nev
• from alagut a inner join tartomany t on a.tartomany2Id=t.id

— 5. Határozza meg régiónként az alagutak számát! A darabszám oszlop fejléc a minta szerint legyen!

• use cnt
• select r.nev, COUNT(*) as darab
• from alagut a inner join tartomany t on a.tartomanyId=t.id inner join regio r on r.id=t.regioId
• group by r.nev

— 6. Készítsen tarományok szerinti statisztikát az alagutak darabszámáról és hosszúságáról a következő minta sdzerint! A listába csak azok a tartományok kerüljenek bele, ahol az alagutak darabszáma 10 vagy annál több! Az oszlopok fejlécei a minta szerint legyenek!

• use cnt
• SELECT t.nev, COUNT() AS Darab, CAST(AVG(a.hossz) AS numeric (10,4)) as ‘Átlagos hossz’, MIN(a.hossz) as ‘Minimum hossz’, MAX(a.hossz) as ‘Maximum hossz’ FROM alagut a INNER JOIN tartomany t ON a.tartomanyId=t.id GROUP BY t.nev HAVING COUNT() >= 10
• FROM alagut a INNER JOIN tartomany t ON a.tartomanyId=t.id
• GROUP BY t.nev
• HAVING COUNT(*) >= 10

— 7. Határozza meg az előző feladatban bevitt atartományban fekvő leghosszabb alagút adatait és írja ki a minta szerint.

• use cnt
• SELECT *
• FROM alagut
• where hossz = (select MAX(hossz) from alagut)

VERSEK

1. feladat: Hozzon létre Versek adatbázist a szerveren!

• CREATE DATABASE Versek
• CHARACTER SET utf8
• COLLATE utf8_hungarian_ci;

• CREATE DATABASE Versek
• COLLATE hungarian_ci_as;

• SELECT SERVERPROPERTY(‘Collation’)

3. feladat: Állítsa be az idegenkulcsokat az ábra alapján!

• ALTER TABLE versek
• ADD CONSTRAINT FK_versek_szerzok_id FOREIGN KEY (szerzoid)
• REFERENCES szerzok (id);

• ALTER TABLE versek
• ADD CONSTRAINT FK_versek_verstipusok_id FOREIGN KEY (verstipusid)
• REFERENCES verstipusok (id);

4. feladat: Listázza ki az adatbázisban szereplő szerzők nevét ABC sorrendben!

• SELECT s.nev
• FROM szerzok AS s
• ORDER BY s.nev;

5. feladat: Listázza ki azokat a verscímeket, amik ‘haiku’ stílusban íródtak.

• SELECT v.cim
• FROM versek AS v
• INNER JOIN verstipusok AS v1 ON v.verstipusid = v1.id
• WHERE v1.elnevezes = ‘haiku’;

6. feladat: Írja ki, mely szerző regisztrált a legrégebben!

• SELECT s.nev
• FROM szerzok AS s
• ORDER BY s.reg_date ASC
• LIMIT 1;

7. feladat: Listázza ki azon szerzők nevét és az általuk írt vers címét, akik 2012-ben regisztráltak!

• SELECT s.nev, v.cim
• FROM szerzok AS s INNER JOIN versek AS v ON s.id = v.szerzoid
• WHERE s.reg_date BETWEEN ‘2012.01.01’ AND ‘2012.12.31’;

/vagy*/

• SELECT s.nev, v.cim
• FROM szerzok AS s
• INNER JOIN versek AS v ON s.id = v.szerzoid
• WHERE YEAR(s.reg_date) = 2012;

8. feladat: Listázza ki a „K” betűvel kezdődő szerzőknek hány versük van az adatbázisban!

• SELECT s.nev,
• COUNT(v.cim)
• FROM szerzok AS s
• INNER JOIN versek AS v ON s.id = v.szerzoid
• WHERE s.nev LIKE ‘K%’
• GROUP BY s.nev;

9. feladat: Listázza ki, mely verscímek ismétlődnek egynél többször az adatbázisban, írja ki mellé az ismétlődés számát is, válaszát csökkenő sorrendben jelenítse meg!

• use versek
• SELECT v.cim, COUNT(*) as ‘Ismétlődés száma’
• FROM versek AS v
• GROUP BY v.cim
• HAVING COUNT(*) > 1
• ORDER BY COUNT(*) DESC;

NORTHWIND

• use NORTHWND;

• SELECT TOP (3) WITH TIES EmployeeID, LastName, FirstName, HireDate
• FROM Employees
• ORDER BY HireDate;

–Adjuk meg a Northwind adatbázis Dolgozók (Employees) táblában szereplő főnökök számát, a beosztottak számát, valamint a dolgozók számát! COUNT

• use NORTHWND
• SELECT COUNT(*) as [Dolgozók száma], COUNT(ReportsTo) as [Beoszottak száma], COUNT(DISTINCT ReportsTo) as [Főnökök száma]
• FROM Employees;

• use NORTHWND
• SELECT City, COUNT() as [Dolgozók száma] FROM Employees WHERE EmployeeID < 9 GROUP BY City HAVING COUNT() >= 2
• ORDER BY [Dolgozók száma] DESC , City;

— 1. Listázzuk ki a Customers táblából az ügyfél kódját, nevét és a kapcsolattartó nevét!

• SELECT CustomerID, CompanyName, ContactName
• FROM Customers

— 2. Csak azokat az ügyfeleket, akiknek a kódja BOLID

• use NORTHWND
• SELECT *
• FROM Customers
• WHERE CustomerID = ‘BOLID’ –ha string akkor az operátor jobb oldalán ” jelek..

— 3. Listázzuk ki azokat az ügyfeleket, akiknek a kódja a B-E betűtartományba eső betűvel kezdődik

• use NORTHWND
• SELECT *
• FROM Customers
• WHERE CustomerID like ‘[B-E]%’

— 5. Termékek listája terméknév szerinti sorrendben

• use NORTHWND
• SELECT *
• FROM Products
• ORDER BY ProductName

— 6. Listázzuk ki azokat a termékeket, amelyek neve nem A,B vagy C betűvel kezdődik!

• use NORTHWND
• SELECT *
• FROM Products
• WHERE ProductName not like ‘[A-C]%’

–8. Adjuk meg azokat a termékeket (Products tábla), amelyekből a rendelés (UnitsOnOrder) több mint 0 és a készlet (UnitsInStock) kisebb, mint 10. Az eredményt a rendelt mennyiség szerint csökkenő sorrendben listázzuk ki.

• use NORTHWND
• SELECT *
• FROM Products
• WHERE UnitsOnOrder > 0 AND UnitsInStock < 10
• ORDER BY UnitsOnOrder DESC

— 9. Listázzuk ki azokat a termékeket (kód=ProductId és név=ProductName) amelyek egységára 50 és 90 dollár között van

• use NORTHWND
• SELECT ProductID, ProductName
• FROM Products
• –WHERE UnitPrice > 50 AND UnitPrice < 90
• WHERE UnitPrice between 51 and 89 — megengedett az 50 és 90 es érték..

— 10. Készítsünk listát, az 1996. dec 10 és 20 között érkezett megrendelésekről (Orders tábla). A listát elsődlegesen rendezzük a megrendelés dátuma szerint, másodlagosan az ügyfélkód szerint csökkenő sorrendben

• use NORTHWND
• SELECT *
• FROM Orders
• WHERE OrderDate > ‘1996-12-10’ AND OrderDate < ‘1996-12-20’
• ORDER BY OrderDate DESC , CustomerID DESC;

–11. Adjuk meg a Termékek számát!

• use NORTHWND
• SELECT Count(*) as [Termékeink száma]
• FROM Products

–12. Adjuk meg a Termékek számát termék kategóriák szerint!

• use NORTHWND
• SELECT CategoryID, Count(*) as [Terméke száma / Kategória]
• FROM Products
• GROUP BY CategoryID

–14. Adjuk meg azon termék kategóriáknak az átlagárait, amelyek átlagára több mint 30 dollár

• use NORTHWND
• SELECT CategoryID, AVG(UnitPrice) as [Átlagegységár / Kategória]
• FROM Products
• GROUP BY CategoryID
• HAVING AVG(UnitPrice) > 30
• ORDER BY [Átlagegységár / Kategória] DESC

–1. feladat Adjuk meg a 10251, 10255,10259 és 10263 rendelésszámú (OrderID) rendeléseket kezelő
–dolgozó kódját(EmployeeID) és a rendelés dátumát (OrderDate)!

• use NORTHWND
• SELECT OrderID, EmployeeID, OrderDate
• FROM Orders
• WHERE OrderID IN (10251,10255,10259,10263)

–2. feladat Adjuk meg a rendelési tételsorokban (Order Details) szereplő tételek rendelésszámát,
–termékkódját, mennyiségét (Quantity), egységárát (UnitPrice), kedvezményét (Discount) valamint a
–tételsor bruttó és nettó értékét, de csak azoknak a tételsoroknak, ahol kedvezmény jár.

• use NORTHWND
• SELECT OrderID, ProductID, UnitPrice, Quantity, Discount, QuantityUnitPrice as Brutto, QuantityUnitPrice*(1-Discount) as Netto
• FROM [Order Details]
• WHERE Discount > 0

–3. feladat Listázzuk ki a dolgozók kódját (EmployeeID) és teljes nevét (LastName és FirstName),
–valamint a születésnapját (születési dátum: BirthDate)!

• use NORTHWND
• SELECT EmployeeID, LastName + ‘ ‘ + FirstName as [Teljes Név], YEAR(BirthDate) as [Születési év],
  MONTH(BirthDate) as [Születési Hónap] ,
  DAY(BirthDate) as [Születési Nap]
• FROM Employees

DATE

• SELECT CURRENT_TIMESTAMP As ‘AktuálisIdő’ –returns host gép dátum és idő értékét
• SELECT GETDATE() As ‘AktuálisIdő’
• SELECT GETUTCDATE()

— DATENAME – returns string
— DATEPART – returns int

• SELECT DATENAME(YEAR,GETDATE()) as ÉV — SELECT ÉV=(DATENAME(YEAR,GETDATE()))
• SELECT DATENAME(QUARTER,GETDATE())
• SELECT DATENAME(WEEK,GETDate())
• SELECT DATENAME(WEEKDAY,GETDate())

• SELECT DATEPART(WEEKDAY,GETDate())
• SELECT DATEPART(HOUR,GETDAte()) as óra
• SELECT DATEPART(MINUTE,GETDATE()) as perc

— DAY , MONTH , YEAR

• SELECT DAY(GETDATE())
• SELECT MONTH(GETDATE())
• SELECT YEAR(GETDATE())

–DATEFROMPARTS

• SELECT DATEFROMPARTS(2019,11,4) –returns date
• SELECT SMALLDATETIMEFROMPARTS(2019,11,4,10,5) –returns datetime
• SELECT TIMEFROMPARTS(10,5,5,5,5) –returns time

–DATEDIFF, DATEADD

• SELECT DATEDIFF(DAY,2023-29-11, 2023-04-11) as Napeltérés –returns int
• SELECT DATEADD(DAY,-70000,GETDATE())

–ISDATE – returns boolean – 1-> ha a bemeneti érték/típus valid datetime típus 0-> ha nem..

• SELECT ISDATE(GETDATE())
• SELECT ISDATE(NULL)

• SELECT GETDATE() as Aktuális_dátum

–Formázzuk meg, hogy a kimenet az alábbi módon nézzen ki: 2023.11.04

• SELECT FORMAT(GETDATE(),’yyyy.MM.dd’)

— Listázzuk ki a dolgozók teljesen nevét , és a csatlakozásuk idejét. A formátum pl.: 2023.11.04 év.hónap.nap

• SELECT TeljeseNév=(LastName+ ‘ ‘ +FirstName), FORMAT(HireDate,’yyyy.MM.dd’) as [Belépési Dátum],
• YEAR(HireDate) as BelépésiÉV, MONTH(HireDate) as BelépésiHó, DAY(HireDate) as BelépésiNap
• FROM Employees;

–1. Adjuk vissza a 1992 április 2 és 1992 augusztus 15 között csatlakozott dolgozókat! (–datetime)

• use NORTHWND
• SELECT TeljeseNév=(LastName+ ‘ ‘ +FirstName), HireDate as BelépésiDátum
• FROM Employees
• –WHERE HireDate between ‘1992-04-02 00:00:00’ AND ‘1992-08-15 23:59:59’
• WHERE HireDate >= ‘1992-04-02’ AND HireDate <= ‘1992-08-15’

–2. Számoljuk ki és listázzuk a dolgozóinkat és az ő életkorukat!

• use NORTHWND
• SELECT TeljeseNév=(LastName+ ‘ ‘ +FirstName), Eletkor=DATEDIFF(YEAR,BirthDate,GETDATE())
• FROM Employees

–3. Mióta dolgoznak a cégnél ?

• use NORTHWND
• SELECT TeljeseNév=(LastName+ ‘ ‘ +FirstName), [Cégnél ledolgozott évek száma]=DATEDIFF(YEAR,HireDate,GETDATE())
• FROM Employees

–3b -DATEDIFF NÉLKÜL, „KÉZZEL” számolva

• use NORTHWND
• SELECT TeljeseNév=(LastName+ ‘ ‘ +FirstName), [Cégnél ledolgozott évek száma]=(YEAR(GetDate())-YEAR(HireDate))
• FROM Employees

–4. Listázzuk ki azokat a dolgozókat, akik több mint 30 éve dolgoznak a cégnél!

• use NORTHWND
• SELECT *
• FROM Employees
• WHERE DATEDIFF(YEAR,HireDate,GETDATE()) > 30

–4.b ALLEKÉRDEZÉS

• use NORTHWND
• SELECT *
• FROM (SELECT TeljeseNév=(LastName+ ‘ ‘ +FirstName), [Cégnél ledolgozott évek száma]=DATEDIFF(YEAR,HireDate,GETDATE())
• FROM Employees) as dolgozoevek
• WHERE [Cégnél ledolgozott évek száma] > 30

–4.c VIEW használata

• use NORTHWND
• CREATE VIEW dolgozoevek
• AS
• SELECT TeljeseNév=(LastName+ ‘ ‘ +FirstName), [Cégnél ledolgozott évek száma]=DATEDIFF(YEAR,HireDate,GETDATE())
• FROM Employees

use NORTHWND
SELECT *
FROM dolgozoevek
WHERE [Cégnél ledolgozott évek száma] > 30

–5.Évente hány dolgozót vettünk fel ?

• use NORTHWND
• SELECT YEAR(HireDate) as FelvételÉve, COUNT(*) as Felvett_dolgozók_száma
• FROM Employees
• WHERE HireDate IS NOT NULL
• GROUP BY YEAR(HireDate)
• –HAVING YEAR(HireDate) IS NOT NULL
• ORDER BY FelvételÉve

–5. FORMAT HASZNÁLATÁVAL

• use NORTHWND
• SELECT FORMAT(HireDate,’yyyy’) as FelvételÉve, COUNT(*) as Felvett_dolgozók_száma
• FROM Employees
• WHERE HireDate IS NOT NULL
• GROUP BY FORMAT(HireDate,’yyyy’)
• ORDER BY FelvételÉve

SELECT * FROM Employees;
— hiredate 2022-05-05 00:00:00

INSERT INTO Employees(LastName,FirstName,HireDate) VALUES (‘Kiss’,’Attila’,’2022-05-05 00:00:00′)
INSERT INTO Employees(LastName,FirstName,HireDate) VALUES (‘Kovács’,’Péter’,’2023-09-24 00:00:00′)

–Listázzuk ki az utolsó 3 hónapban felvett dolgozóinkát!
–DATEDIFF, DAYADD

• SELECT *
• FROM Employees
• –WHERE DATEDIFF(MONTH,HireDate,GETDATE()) <= 3 WHERE HireDate >= DATEADD(MONTH,-380,GETDATE())
• –DELETE FROM Employees
• –WHERE EmployeeID = 12