https://github.com/green-fox-academy/safely-syllabus/tree/master/materialreview/mssql
##### SQL alapok #####
SELECT IN SELECT, WHERE IN, WHERE NOT IN
SELECT TOP (1000) [SZAMLAFEJ_ID]
,[VEVO_ID]
,[KELTE]
,[TELJESITES]
,[FIZHATARIDO]
,[NAVATKULDVE]
,[STATUS]
FROM [CEGESADATOK].[szamlazas].[szamlafej]
WHERE VEVO_ID Not IN (select vevo_id
from szamlazas.vevo)
UPDATE
USE [CEGESADATOK]
GO
UPDATE [szamlazas].[vevo]
SET [STATUS] = 1
,[LASTUPDATE] = (GETDATE())
WHERE STATUS=0
GO
INSERT INTO
USE [CEGESADATOK]
GO
INSERT INTO [szamlazas].[vevo]
([VEVONEV], [ADOSZAM], [CIM])
VALUES
('öTÖDIK Nyrt','3499789012-4-04','4000 Kisvarda, Negyedik utca. 5'),
('Hatodik Nyrt','3456789099-4-04','4000 Kisvarda, Negyedik utca. 6')
GO
/* MASIK */
USE [CEGESADATOK]
GO
INSERT INTO [szamlazas].[vevo]
([VEVONEV]
,[ADOSZAM]
,[CIM]
,[TELEFON]
,[EMAIL]
,[STATUS])
VALUES
('Hetedik Bt.',
'11223344-2-05',
'Seholgy sincs',
'',
null,
0)
GO
DELETE
USE CEGESADATOK
Go
SELECT *
FROM szamlazas.szamlatetel
WHERE SZAMLAFEJ_ID Not IN (SELECT SZAMLAFEJ_ID
FROM szamlazas.szamlafej)
SELECT SZAMLAFEJ_ID
FROM szamlazas.szamlafej
DELETE FROM szamlazas.szamlatetel
WHERE SZAMLAFEJ_ID Not IN (SELECT SZAMLAFEJ_ID
FROM szamlazas.szamlafej)
GO
DISTINCT
SELECT DISTINCT v.*
FROM szamlazas.vevo v INNER JOIN szamlazas.szamlafej szf ON v.VEVO_ID=szf.VEVO_ID
WHERE v.STATUS=1 and szf.STATUS='normal'
CONVERT, AVG
SELECT *
FROM [CEGESADATOK].[szamlazas].[ARU]
SELECT AVG(EGYSEGAR)
FROM [CEGESADATOK].[szamlazas].[ARU]
SELECT *
FROM [CEGESADATOK].[szamlazas].[ARU]
where EGYSEGAR < (SELECT AVG(EGYSEGAR) as egysegar
FROM [CEGESADATOK].[szamlazas].[ARU])
SELECT ARU_ID, ARUNEV, MENNYEGYS,CONVERT(int,EGYSEGAR) as EGYSEGAR, CONVERT(int,AFAKULCS) as AFAKULCS, STATUS
FROM [CEGESADATOK].[szamlazas].[ARU]
where EGYSEGAR < (SELECT AVG(EGYSEGAR) as egysegar
FROM [CEGESADATOK].[szamlazas].[ARU])
FROMART, CONVERT
select getdate()
select FORMAT(getdate(), 'yyyy')
select FORMAT(getdate(), 'yyyy/MM')
select FORMAT(getdate(), 'yyyy/MM/dd')
select FORMAT(getdate()+1, 'yyyy/MM/dd')
select FORMAT(getdate()-2, 'yyyy/MM/dd')
select FORMAT(getdate(), 'yyyy/MM/dd HH:mm:ss.ms')
select '2022/3/15'
select CONVERT(datetime,'2022/3/25')
HAVING
/*Feladat:
Irassuk ki azoknak a vevőknek a nevet,címét, adószámát ésa vásárlások számát, akik legalább kétszer vásároltak
ROSSZ:
select v.VEVONEV
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
where (select COUNT(szf.VEVO_ID)
from szamlazas.szamlafej
group by szf.VEVO_ID) > 1
GROUP by v.VEVONEV
*/
select COUNT(szf.VEVO_ID), szf.VEVO_ID
from szamlazas.szamlafej szf
group by szf.VEVO_ID
select v.VEVONEV,v.CIM, v.ADOSZAM,
COUNT(szf.VEVO_ID) as VASARLASSZAM
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
GROUP by v.VEVONEV,v.CIM, v.ADOSZAM
HAVING COUNT(szf.VEVO_ID) >=2
ORDER BY VASARLASSZAM
/*Feladat:
Irassuk ki azoknak a vevőknek a nevet,címét, adószámát ésa vásárlások számát, bruttó szummáját*/
select v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM,
CONVERT(int,SUM(t.[BRUTTO ERTEK])) as SZUMMABRUTTO
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join szamlazas.tetelek t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
GROUP by v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM
ORDER BY SZUMMABRUTTO desc
select v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM,
CONVERT(int,SUM(t.MENNYISEG*a.EGYSEGAR*(a.AFAKULCS/100+1))) as SZUMMABRUTTO
from szamlazas.vevo v inner join szamlazas.szamlafej szf on v.VEVO_ID=szf.VEVO_ID
inner join szamlazas.szamlatetel t on szf.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
inner join szamlazas.ARU a on t.ARU_ID=a.ARU_ID
WHERE v.CIM like '%Budapest%'
GROUP by v.VEVO_ID,v.VEVONEV,v.CIM, v.ADOSZAM
HAVING CONVERT(int,SUM(t.MENNYISEG*a.EGYSEGAR*(a.AFAKULCS/100+1))) > 121000
ORDER BY SZUMMABRUTTO desc
CONSISTENT FIX
use CEGESADATOK
go
update szamlazas.szamlafej
set STATUS='hibas'
where SZAMLAFEJ_ID not in (select SZAMLAFEJ_ID from szamlazas.szamlatetel)
go
SOK JOIN, SOK WHERE
USE CEGESADATOK
GO
/* Kik akik vettek A7-et?*/
select VEVONEV
From szamlazas.vevo
WHERE VEVO_ID IN (select VEVO_ID
FROM szamlazas.szamlafej
WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
From szamlazas.szamlatetel
where ARU_ID IN (select ARU_ID
From szamlazas.ARU
where ARUNEV='A7')))
select v.*
FROM szamlazas.vevo v
INNER JOIN szamlazas.szamlafej f ON v.VEVO_ID=f.VEVO_ID
INNER JOIN szamlazas.szamlatetel t ON f.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
INNER JOIN szamlazas.ARU a ON t.ARU_ID=a.ARU_ID
WHERE a.ARUNEV='A7'
ORDER BY VEVO_ID
select *
From szamlazas.vevo v INNER JOIN szamlazas.szamlafej sz ON v.VEVO_ID=sz.VEVO_ID
INNER JOIN szamlazas.tetelek t ON sz.SZAMLAFEJ_ID=t.SZAMLAFEJ_ID
where t.ARUNEV='A7'
select *
FROM szamlazas.szamlafej
WHERE SZAMLAFEJ_ID in (select SZAMLAFEJ_ID
From szamlazas.szamlatetel
where ARU_ID IN (select ARU_ID
From szamlazas.ARU
where ARUNEV='A7'))
select *
From szamlazas.szamlatetel
where ARU_ID IN (select ARU_ID
From szamlazas.ARU
where ARUNEV='A7')
/*
select *
From szamlazas.tetelek t INNER JOIN szamlazas.szamlafej sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID
where ARUNEV='A7'
select *
From szamlazas.vevo v INNER JOIN (szamlazas.tetelek t INNER JOIN szamlazas.szamlafej sz ON t.SZAMLAFEJ_ID=sz.SZAMLAFEJ_ID) ON
where ARUNEV='A7'
*/
LEKÉRDEZÉS VIEW-HOZ
USE CEGESADATOK
GO
SELECT t.*, a.ARUNEV, a.MENNYEGYS, a.EGYSEGAR, a.EGYSEGAR*t.MENNYISEG as 'Netto ertek', a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100) as 'AFA ERTEK'
, (a.EGYSEGAR*t.MENNYISEG*(a.AFAKULCS/100))+(a.EGYSEGAR*t.MENNYISEG) as 'BRUTTO ERTEK'
FROM szamlazas.szamlatetel t INNER JOIN szamlazas.ARU a ON t.ARU_ID=a.ARU_ID
/*WHERE (t.ARU_ID=a.ARU_ID)*/
SELECT *
FROM szamlazas.tetelek
CREATE VIEW
create view szamlazas.rossz_szamlafej
as
select *
from szamlazas.szamlafej
where VEVO_ID not in (select vevo_id from szamlazas.vevo)
Create schema (séma)
USE [CEGESADATOK]
GO
/****** Object: Schema [szamlazas] Script Date: 2022. 10. 25. 0:11:57 ******/
CREATE SCHEMA [szamlazas]
GO
Create user, login, permissions, roles
USE [master]
GO
CREATE LOGIN [test1] WITH PASSWORD=N'Password123', DEFAULT_DATABASE=[CEGESADATOK], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [CEGESADATOK]
GO
CREATE USER [test1] FOR LOGIN [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [test1]
GO
USE [CEGESADATOK]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [test1]
GO
Tábla törlése, truncate table
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [SZAMLAFEJ_ID]
,[VEVO_ID]
,[KELTE]
,[TELJESITES]
,[FIZHATARIDO]
,[NAVATKULDVE]
,[STATUS]
FROM [CEGESADATOK].[szamlazas].[szamlafej]
truncate table szamlazas.szamlafej
bulk insert szamlazas.szamlafej
FROM N'E:\szamlafej.table.csv'
with (fieldterminator=';',rowterminator='\n')
###### 3. vizsga SQL #####
Táblák létrehozása (create table)
CREATE TABLE apprentices(
id int,
name varchar(50),
age int,
country varchar(50),
cohort_id int)
CREATE TABLE cohorts(
id int,
name varchar(50),
started_at date,
type varchar(50))
Táblák importálása (bulk insert)
/****** Script for SelectTopNRows command from SSMS ******/
bulk insert apprentices
FROM N'E:\apprentices.table.csv'
WITH (fieldterminator=';',rowterminator='\n'/*, FIRSTROW=2 ha van fejlec*/)
bulk insert cohorts
FROM N'E:\cohorts.table.csv'
WITH (fieldterminator=';',rowterminator='\n')
Listázd a nevét (name) és kezdési dátumát (started_at) az összes a évfolyamnak (cohorts tábla)
Filename: cohorts.sql
SELECT name, started_at
FROM cohorts
Listázd a nevét (name) és származási országát (country) az összes a hallgatónak (apprentices tábla)
Filename: apprentices.sql
SELECT name, country
FROM apprentices
Listázd a nevét (name) és korát (age) azoknak hallgatónak (apprentices tábla), akik fiatalabbak, mint 21
Filename: apprentices-younger-21.sql
SELECT name, age
FROM apprentices
WHERE age < 21
Listázd az id-ját és nevét (name) azoknak hallgatónak (apprentices tábla), akik nem Magyarországról származnak (country)
Filename: apprentices-not-from-hu.sql
SELECT id, name
FROM apprentices
WHERE country not like 'Hungary'
Írasd ki hány darab olyan hallgató(apprentices tábla) található az adatbázisban, akik legalább 20 és legfeljebb 30 évesek
Filename: apprentices-between-20-and-30.sql
SELECT count(id)
FROM apprentices
WHERE age >= 20 and age <= 30
Írasd ki hány darab olyan évfolyam(cohorts tábla) található az adatbázisban, amelyek “part-time” típusúak (type)
Filename: cohorts-part-time.sql
SELECT count(id)
FROM cohorts
WHERE type like 'part-time'
Listázd a hallgatók nevét (as apprentice_name) és évfolyam nevét (as cohort_name) azoknak a hallgatóknak, akik 2018-as évfolyamokban kezdték (started_at) a tanulmányaikat.
Filename: apprentices-in-2018.sql
SELECT a.name as apprentice_name, c.name as cohort_name
FROM apprentices a INNER JOIN cohorts c ON a.cohort_id=c.id
WHERE FORMAT(started_at, 'yyyy')=2018
Írasd ki hány darab olyan évfolyam található az adatbázisban, amelyekhez nincs még hallgató felvéve
Filename: cohorts-without-apprentice.sql
SELECT count(id)
FROM cohorts
WHERE id NOT IN (SELECT cohort_id FROM apprentices)
Listázd a hallgatók nevét és a képzés kezdődátumát a hallgató neve alapján rendezve ABC sorrendben
Filename: apprentices-start-date-ordered.sql
SELECT a.name, c.started_at
FROM apprentices a INNER JOIN cohorts c ON a.cohort_id=c.id
ORDER BY a.name
Listázd az évfolyamok nevét és a hallgatók számát (as numberOfApprentices) az adott évfolyamban, a hallgatók számával csökkenő sorrendben. Az az évfolyam is legyen benne, ahol jelenleg 0 hallgató van.
Filename: cohort-sizes.sql
SELECT c.name, count(a.id) as numberOfApprentices
FROM cohorts c LEFT JOIN apprentices a ON a.cohort_id=c.id
GROUP BY c.name
ORDER BY count(a.id) DESC
###### SQL admin projektfeladatok #####
1) A szerverhez hozzá van adva egy disk, ami még nincs használatban
(20GB). Add hozzá a géphez S: meghajtóként, a kötet neve pedig:
SQLDATA legyen.
2) Az S: meghajtón hozzál létre egy SQLDATA könyvtárat, majd abban
további két alkönyvtárat: DB és LOG névvel.
3) Jelentkezz be az SQL szerverbe (GREENFOX instance)! (Ezt a lépést
nem kötelező elmentened! :-)
4) Töltsd vissza a C:\install\MSSQL\AdventureWorks2016.bak
adatbázist a szerver GREENFOX instance-ába úgy, hogy az adatbázis
állományok az S:\SQLDATA\DB, míg a log állományok az S:\SQLDATA\
LOG könyvtárba kerüljenek.
USE [master]
RESTORE DATABASE [AdventureWorks2016] FROM DISK = N'C:\install\MSSQL\AdventureWorks2016.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'S:\SQLDATA\DB\AdventureWorks2016_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'S:\SQLDATA\LOG\AdventureWorks2016_Log.ldf', NOUNLOAD, STATS = 5
GO
5) Hozz létre egy interface_svc MS SQL autentikációjú account-ot,
minimális (public) jogokkal! A jelszava legyen: INTERface.
USE [master]
GO
CREATE LOGIN [interface_svc] WITH PASSWORD=N'INTERface', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
6) Hozz létre egy INTERFACE nevű adatbázist az alábbi beállításokkal:
• Adatbázis neve: INTERFACE
• Tulajdonos: interface_svc
• Recovery model: FULL
• Induló adatbázis file:
(a)méret: 32 MByte
(b)helye: S:\SQLDATA\DB
• Induló log file:
(a) méret: 16 MByte
(b)helye: S:\SQLDATA\LOG
CREATE DATABASE [INTERFACE]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'INTERFACE', FILENAME = N'S:\SQLDATA\DB\INTERFACE.mdf' , SIZE = 32768KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'INTERFACE_log', FILENAME = N'S:\SQLDATA\LOG\INTERFACE_log.ldf' , SIZE = 16384KB , FILEGROWTH = 65536KB )
GO
USE [INTERFACE]
GO
ALTER AUTHORIZATION ON DATABASE::[INTERFACE] TO [interface_svc]
/*
exec sp_changedbowner 'interface_svc'
GO
*/
ALTER DATABASE [INTERFACE] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [INTERFACE] SET ARITHABORT OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [INTERFACE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [INTERFACE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [INTERFACE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [INTERFACE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [INTERFACE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [INTERFACE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [INTERFACE] SET DISABLE_BROKER
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [INTERFACE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [INTERFACE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [INTERFACE] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [INTERFACE] SET READ_WRITE
GO
ALTER DATABASE [INTERFACE] SET RECOVERY FULL
GO
ALTER DATABASE [INTERFACE] SET MULTI_USER
GO
ALTER DATABASE [INTERFACE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [INTERFACE] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [INTERFACE] SET DELAYED_DURABILITY = DISABLED
GO
USE [INTERFACE]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
USE [INTERFACE]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [INTERFACE] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
7) Adj csak lekérdezési jogot az interface_svc account számára az
AdventureWorks2016 adatbázis HumanResources sémában levő
vEmployee nézettáblájára (VIEW)!
use [AdventureWorks2016]
GO
CREATE USER [interface_svc] FOR LOGIN [interface_svc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [interface_svc]
GO
8) INTERFACE adatbázisban hozz létre egy AWDB sémát! A szkript
„Create to » formátumú legyen
USE [INTERFACE]
GO
/****** Object: Schema [AWDB] Script Date: 12/9/2022 8:21:47 AM ******/
CREATE SCHEMA [AWDB]
GO
9) Az AWDB sémában hozz létre egy szinonimát AW_Employee néven,
ami az AdventureWorks2016 adatbázis HumanResources sémában
levő vEmployee nézettáblára mutat!
USE [INTERFACE]
GO
/****** Object: Synonym [AWDB].[AW_Employee] Script Date: 12/6/2022 1:59:44 PM ******/
CREATE SYNONYM [AWDB].[AW_Employee] FOR [AdventureWorks2016].[HumanResources].[vEmployee]
GO
10) Készíts lekérdezést, ami az INTERFACE adatbázisnak az AWDB
sémájának az AW_Employee szinonimájának minden sorát és oszlopát
kilistázza!
USE [INTERFACE]
GO
SELECT *
FROM AWDB.AW_Employee
11) Készíts az INTERFACE adatbázisról egy FULL mentést, az alábbiak
szerint:
a) Backup file neve: S:\SQLdata\INTERFACE.bak
b) Mentés felülírja az azonos nevű media set-et!
BACKUP DATABASE [INTERFACE] TO DISK = N'S:\SQLdata\INTERFACE.bak' WITH NOFORMAT, INIT, NAME = N'S:\SQLdata\INTERFACE.bak', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
1) Kérdezd le az SQL szerver verzióját és az SQL szerver kiadását!
2) Készíts egyszerű “Hello World!” lekérdezést!
3) Készíts olyan SQL lekérdezést, ami kiszámítja 30 négyzetgyökét!
4) Készíts olyan SQL lekérdezést, ami visszaadja a mai dátumot!
SELECT CAST(GETDATE() as date)
5) Készíts olyan SQL lekérdezést, ami kiszámítja, hogy hány nap telt el
1989. március 15-e óta!
SELECT DATEDIFF(day,'1989-03-15', GETDATE());
6) Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Address táblájának első 20 elemét
(minden mezőjét)!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (20) *
FROM [AdventureWorks2016].[Person].[Address]
7) Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának első 20 elemét
(minden mezőjét), de úgy, hogy ahol a PersonType mező értéke ‘EM’!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (20) *
FROM [AdventureWorks2016].[Person].[Person]
WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'
8) Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának első 20 elemét, de
úgy, hogy ahol a PersonType mező értéke ‘EM’ és csak a PersonType,
FirstName, LastName mezők legyenek a lekérdezés eredményében!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (20) PersonType, FirstName, LastName
FROM [AdventureWorks2016].[Person].[Person]
WHERE [AdventureWorks2016].[Person].[Person].PersonType='EM'
9) Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának első 20 elemét, de
úgy, hogy ahol a PersonType mező értéke ‘EM’ és csak a PersonType,
FirstName, LastName mezők legyenek a lekérdezés eredményében, de a
FirstName és LastName egy oszlopban jelenjen meg egy darab szóköz
karakterrel elválasztva!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (20) PersonType, FirstName + ' ' + LastName
FROM [AdventureWorks2016].[Person].[Person]
WHERE PersonType='EM'
10)Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának azon rekordjait
(minden mezőjét), ahol a PersonType mező értéke ‘EM’ és a FirstName
mezőben csak a ‘John’ nevűek közül azok legyenek megjelenítve!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT *
FROM [AdventureWorks2016].[Person].[Person]
WHERE PersonType='EM' AND FirstName='John'
11)Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának azon rekordjait
(minden mezőjét), ahol a PersonType mező értéke ‘EM’ és a FirstName
mezőben csak a ‘John’ nevűek közül azok legyenek megjelenítve, ahol a
LastName mező értéke ‘e’-karakterrel végződik!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT *
FROM [AdventureWorks2016].[Person].[Person]
WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'
12)Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person táblájának azon rekordjait
(minden mezőjét), ahol a PersonType mező értéke ‘EM’ és a FirstName
mezőben csak a ‘John’ nevűek közül azok legyenek megjelenítve, ahol a
LastName mező értéke vagy ‘Chen’ vagy ‘Kane’ neveket tartalmazzák!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT *
FROM [AdventureWorks2016].[Person].[Person]
WHERE PersonType='EM' AND FirstName='John' AND (LastName='Chen' OR LastName='Kane')
13) Számold meg, hogy hány különböző keresztnév (FirstName) van a
korábban betöltött AdventureWorks adatbázis Person.Person táblájában!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT COUNT(DISTINCT FirstName)
FROM [AdventureWorks2016].[Person].[Person]
14) Számold meg, hogy hány ‘Ken’ keresztnévű (FirstName) rekord van a
korábban betöltött AdventureWorks adatbázis Person.Person táblájában!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT COUNT(FirstName)
FROM [AdventureWorks2016].[Person].[Person]
WHERE FirstName='Ken'
15) Számold meg, hogy hány azonos nevű (FirstName és LastName) rekord
van a korábban betöltött AdventureWorks adatbázis Person.Person
táblájában! Rendezd az eredményt úgy, hogy a legtöbbet használt
névpáros a lista elején szerepeljen és csak a legelső 20 eredmény
szerepeljen!
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (20) FirstName, LastName, COUNT(1)
FROM [AdventureWorks2016].[Person].[Person]
GROUP BY FirstName, LastName
ORDER BY COUNT(1) DESC
16) Számold meg, hogy hány azonos nevű (FirstName és LastName) rekord
van a korábban betöltött AdventureWorks adatbázis Person.Person
táblájában! Rendezd az eredményt úgy, hogy csak a 4 és a 4-nél többször
használt névpáros eredménye szerepeljen!
SELECT FirstName, LastName, COUNT(1)
FROM [AdventureWorks2016].[Person].[Person]
GROUP BY FirstName, LastName
HAVING COUNT(1) >= 4
ORDER BY COUNT(1) DESC
17)Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött
AdventureWorks adatbázis Person.Person és Person.EmailAddress
tábláinak a következő mezőit: Person.Person.FirstName,
Person.Person.LastName, Person.EmailAddress.EmailAddress úgy hogy a
táblák összekapcsolásához a BusinessEntityID mezőt használjuk mindkét
táblából! Az eredmény első 50 sora jelenjen csak meg!
SELECT TOP (50) p.FirstName, p.LastName, e.EmailAddress
FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID
18)Az előző lekérdezésből készíts olyan megoldást is, ahol aze-mail címből
(EmailAddress) csak a név rész (@ előtti rész látható)!
SELECT TOP (50) p.FirstName, p.LastName, LEFT(e.EmailAddress,CHARINDEX('@',e.EmailAddress)-1)
FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID
19)Illetve egy olyan lekérdezést is, ahol csak a domain rész (@ utáni rész)
látható!
SELECT TOP (50) p.FirstName, p.LastName, RIGHT(e.EmailAddress,LEN(e.EmailAddress)- CHARINDEX('@',e.EmailAddress))
FROM [AdventureWorks2016].[Person].[Person] p INNER JOIN [AdventureWorks2016].[Person].[EmailAddress] e on p.BusinessEntityID=e.BusinessEntityID
20) (IZMOS FELADAT) Készíts egy lekérdezést ami visszaadja a
Sales.SalesPerson táblából a BusinessEntityID mezőt és egy „minsales”
nevű számított oszlopot. A minsales értéke legyen soronként a SalesYTD
mező értéke, amennyiben a SalesYTD > 1000000, egyéb esetben NULL
értéket adjon.
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD > 1000000 THEN SalesYTD ELSE NULL END as minsales
FROM [AdventureWorks2016].[Sales].[SalesPerson]
21) (NAGYON IZMOS FELADAT) Készíts egy lekérdezést ami visszaadja a
Sales.SalesPerson táblából a BusinessEntityID mezőt és egy „salesinfo”
nevű számított oszlopot. A salesinfo értéke legyen soronként a SalesYTD
mező értéke, amennyiben a SalesYTD >= 2000000, Amennyiben
SalesYTD<2000000 de nagyobb vagy egyenlő mint 1000000, akkor az
érték legyen 1500000, Ha 1000000-nál is kisebb akkor pedig NULL. pl.
kimenet :
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [BusinessEntityID], CASE WHEN SalesYTD >= 2000000 THEN SalesYTD ELSE CASE WHEN SalesYTD>=1000000 THEN 1500000 ELSE NULL END END as salesinfo
FROM [AdventureWorks2016].[Sales].[SalesPerson]
Microsoft SQL szerver (továbbiakban SQL) tudásszint felmérő
- Használj T-SQL kódot
- Mindenképp a megadott SQL fájlneveket használd
- A feltöltött ZIP fájlodban ne legyenek alkönyvtárak, csak a fájlokat tartalmazza
- Ügyelj rá, hogy az adatbázis-, felhasználó-, és egyéb objektumelnevezések nagybetűérzékenyek
- A lekérdezésekben a megjelenítendő oszlopok nevén csak akkor változtass, ha a feladat külön előírja
- A lekérdezésekben a megjelenítendő oszlopok a feladatban megadott sorrendben jelenjenek meg
- Figyelj oda, hogy mindig a megfelelő adatbázis legyen kiválasztva
- Használj ASCII, vagy UTF-8 (without BOM) karakterkódolást
- A Docker konténerben a feladatokat “futtató” MSSQL LOGIN alapértelmezett kiinduló adatbázisa az első 10 feladatban:
master.
- A Docker konténerben a feladatokat “futtató” MSSQL LOGIN alapértelmezett kiinduló adatbázisa 11)-től 20)-es feladattal bezárólag:
AdventureWorks.Ezekben a feladatokban elegendő ha a feltöltött fájlod csak a lekérdezést tartalmazza.
- Az sql kiterjesztésú fájlok kiértékelése elsődlegesen autograder révén történik, a többit manuális pontozással értékeljük.
Feladatok:
- A szerverhez hozzá van adva egy diszk, ami még nincs használatban (20GB). Helyezd üzembe, hozzál létre egy 20 GByte-os partíciót NTFS file rendszerrel és a géphez S: meghajtóként mountold fel, a kötet neve pedig: SQLDATA legyen.
| Evidencia: |
Computer Management => Storage => Disk Management |
| File neve: |
WINSQL-01.jpg |
- S: meghajtón hozzál létre egy SQLDATA könyvtárat, majd abban további két alkönyvtárat: DB és LOG névvel.
| Evidencia: |
File Explorer => S: => SQLDATA könyvtárat megnyitva, amikor látszik a kért két alkönyvtár is. |
| File neve: |
WINSQL-02.jpg |
- Jelentkezz be az SQL szerverbe (GREENFOX instance) a Microsoft SQL Server Management Studio-val!
| Evidencia: |
Microsoft SQL Server Management Studio-ban GREENFOX instance megnyitva. |
| File neve: |
WINSQL-03.jpg |
- Töltsd vissza a C:\install\MSSQL\AdventureWorks2016.bak adatbázist a szerver GREENFOX példányába
AdventureWorks néven úgy, hogy az adatbázis állományok az S:\SQLDATA\DB, míg a log állományok az S:\SQLDATA\LOG könyvtárba kerüljenek.
Gradescopeban itt találod a backupot: /AdventureWorks2016.bak, az adatbázis állományok: /SQLDATA/DB/AdventureWorks_Data.mdf, míg a log állományok az /SQLDATA/LOG/AdventureWorks_Log.ldf fájlokba kerüljenek
| Evidencia: |
Microsoft SQL Server Management Studio-ban restore típusú szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-04.sql |
USE [master]
RESTORE DATABASE [AdventureWorks] FROM DISK = N'/home/AdventureWorks2016.bak' WITH FILE = 1, MOVE N'AdventureWorks2016_Data' TO N'/SQLDATA/DB/AdventureWorks_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'/SQLDATA/LOG/AdventureWorks_Log.ldf', NOUNLOAD, STATS = 5
GO
- Hozz létre egy isvc MS SQL Server autentikációjú account-ot, minimális (public) jogokkal! A jelszó legyen: interfAce21 A jelszót ne kelljen megváltoztatnia a felhasználónak első belépéskor.
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-05.sql |
USE [master]
GO
CREATE LOGIN [isvc] WITH PASSWORD=N'interfAce21', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
- Hozz létre egy INTERFACE nevű adatbázist az alábbi beállításokkal:
- Adatbázis neve: INTERFACE
- Tulajdonos: isvc
- Recovery model: FULL
- Induló adatbázis file:
- méret: 64 MByte
- helye: S:\SQLDATA\DB (Gradescope-on /SQLDATA/DB)
- Induló log file:
- méret: 24 MByte
- helye: S:\SQLDATA\LOG (Gradescope-on /SQLDATA/LOG)
CREATE DATABASE [INTERFACE]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'INTERFACE', FILENAME = N'/SQLDATA/DB/INTERFACE.mdf' , SIZE = 65536KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'INTERFACE_log', FILENAME = N'/SQLDATA/LOG/INTERFACE_log.ldf' , SIZE = 24576KB , FILEGROWTH = 65536KB )
GO
USE [INTERFACE]
GO
ALTER AUTHORIZATION ON DATABASE::[INTERFACE] TO [isvc]
ALTER DATABASE [INTERFACE] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [INTERFACE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [INTERFACE] SET ARITHABORT OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [INTERFACE] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [INTERFACE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [INTERFACE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [INTERFACE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [INTERFACE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [INTERFACE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [INTERFACE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [INTERFACE] SET DISABLE_BROKER
GO
ALTER DATABASE [INTERFACE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [INTERFACE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [INTERFACE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [INTERFACE] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [INTERFACE] SET READ_WRITE
GO
ALTER DATABASE [INTERFACE] SET RECOVERY FULL
GO
ALTER DATABASE [INTERFACE] SET MULTI_USER
GO
ALTER DATABASE [INTERFACE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [INTERFACE] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [INTERFACE] SET DELAYED_DURABILITY = DISABLED
GO
USE [INTERFACE]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
USE [INTERFACE]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [INTERFACE] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
- Adj csak lekérdezési jogot az isvc account számára az AdventureWorks adatbázis HumanResources sémában levő vEmployee nézettáblájára!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-07.sql |
USE [AdventureWorks]
GO
CREATE USER [isvc] FOR LOGIN [isvc]
GO
GRANT SELECT ON [HumanResources].[vEmployee] TO [isvc]
GO
- INTERFACE adatbázisban hozz létre egy AWdatabase sémát!
| Evidencia: |
Microsoft SQL Server Management Studio-ban GREENFOX instance-ban az INTERFACE adatbázis AWdatabase séma „Create To” script. |
| File neve: |
WINSQL-08.sql |
USE [INTERFACE]
GO
/****** Object: Schema [dbo] Script Date: 2023. 01. 15. 13:45:32 ******/
CREATE SCHEMA [AWdatabase]
GO
- Az AWdatabase sémában hozz létre egy szinonímát AW_Employee néven, ami az AdventureWorks adatbázis HumanResources sémában levő vEmployee nézettáblára mutat!
| Evidencia: |
Microsoft SQL Server Management Studio-ban GREENFOX instance-ban az INTERFACE adatbázis AWdatabase séma AW_Employee szinoníma „Create To” script. |
| File neve: |
WINSQL-09.sql |
USE [INTERFACE]
GO
CREATE SYNONYM [AWdatabase].[AW_Employee ] FOR [AdventureWorks].[HumanResources].[vEmployee]
GO
- Készíts az INTERFACE adatbázisról egy FULL mentést, az alábbiak szerint:
- Backup file neve: S:\SQLDATA\INTERFACE.bak (Gradescope-ban
/var/backups/INTERFACE.bak)
- Mentés felülírja az azonos nevű media set-et!
| Evidencia: |
Microsoft SQL Server Management Studio-ban GREENFOX instance-ban az INTERFACE adatbázis backup script. |
| File neve: |
WINSQL-10.sql |
BACKUP DATABASE [INTERFACE] TO DISK = N'/var/backups/INTERFACE.bak' WITH NOFORMAT, INIT, NAME = N'INTERFACE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
- Készíts olyan SQL lekérdezést, ami kiszámítja 25 négyzetgyökét!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-11.sql |
- Készíts olyan SQL lekérdezést, ami visszaadja a mai dátumot ebben a formátumban:
2022-10-25
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-12.sql |
SELECT CAST(GETDATE() as date)
- Készíts olyan SQL lekérdezést, ami kiszámítja, hogy hány nap telt el 1994. április 25-e óta!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-13.sql |
SELECT DATEDIFF(day,'2022-04-25',GETDATE())
- Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Address táblájának első 15 elemét (minden mezőjét – az oszlopok sorrendjén ne változtass)!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-14.sql |
/****** Script for SelectTopNRows command from SSMS ******/
USE [AdventureWorks]
GO
SELECT TOP (15) *
FROM [AdventureWorks].[Person].[Address]
- Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának első 5 elemét (minden mezőjét – az oszlopok sorrendjén ne változtass -), de úgy, hogy ahol a PersonType mező értéke ‘EM‘!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-15.sql |
USE [AdventureWorks]
GO
SELECT TOP (5) *
FROM [AdventureWorks].[Person].[Person]
WHERE PersonType='EM'
- Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának első 30 elemét, de úgy, hogy ahol a PersonType mező értéke ‘EM‘ és csak a PersonType, FirstName, LastName mezők legyenek a lekérdezés eredményében!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-16.sql |
USE [AdventureWorks]
GO
SELECT TOP (30) PersonType, FirstName,LastName
FROM [AdventureWorks].[Person].[Person]
WHERE PersonType='EM'
- Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának első 20 elemét, de úgy, hogy ahol a PersonType mező értéke ‘EM‘ és csak a PersonType, FirstName, LastName mezők legyenek a lekérdezés eredményében, de a FirstName és LastName egy oszlopban jelenjen meg egy darab szóköz karakterrel elválasztva, FullName néven!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-17.sql |
USE [AdventureWorks]
GO
SELECT TOP (20) PersonType, FirstName+ ' ' + LastName as FullName
FROM [AdventureWorks].[Person].[Person]
WHERE PersonType='EM'
- Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának azon rekordjait (minden oszlopát, minden mezőt – az oszlopok sorrendjén ne változtass -), ahol a PersonType mező értéke ‘EM‘ és a FirstName mezőben csak a ‘John‘ nevűek szerepeljenek!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-18.sql |
USE [AdventureWorks]
GO
SELECT *
FROM [AdventureWorks].[Person].[Person]
WHERE PersonType='EM' AND FirstName='John'
- Készíts olyan SQL lekérdezést, ami visszaadja a korábban betöltött AdventureWorks adatbázis Person.Person táblájának azon rekordjait (minden oszlopát, minden mezőt – az oszlopok sorrendjén ne változtass -), ahol a PersonType mező értéke ‘EM‘ és a FirstName mezőben csak a ‘John‘ nevűek közül is azok legyenek csak megjelenítve, ahol a LastName mező értéke ‘e‘-karakterrel végződik!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-19.sql |
USE [AdventureWorks]
GO
SELECT *
FROM [AdventureWorks].[Person].[Person]
WHERE PersonType='EM' AND FirstName='John' AND RIGHT(LastName,1)='e'
- Számold meg, hogy hány ‘Ken‘ keresztnevű (FirstName) rekord van a korábban betöltött AdventureWorks adatbázis Person.Person táblájában!
| Evidencia: |
Microsoft SQL Server Management Studio-ban szkript kimásolása vagy mentése. |
| File neve: |
WINSQL-20.sql |
USE [AdventureWorks]
GO
SELECT COUNT(*)
FROM [AdventureWorks].[Person].[Person]
WHERE FirstName='Ken'
Magyar 
English (UK) 
Deutsch 
Русский 
简体中文
Hagyj üzenetet