Overview
Intruction
You y not ue lte y
COMS W4111 Introuction to Dtbe Spring 2023 Section 002
Tke Hoe Miter
You ve one week to coplete te tke oe portion of te iter All of te work ut be your own you y not work in group or te You y ue outie ource o long you cite te n provie link
Point will be tken off for ny nwer tt re extreely verboe Try to ty between 2-3 entence for efinition n 5 entence for longer quetion You y pot privtely on E or tten OH for clirifiction quetion TA will not be proviing int
Environent Setup ¶
Note You will nee to cnge te MySQL uerID n pwor in oe of te cell below to tc your configurtion
Environent
Different ppliction n ifferent cenrio ue ifferent wy to interction wit tbe
We ue tree ifferent connectioninterction oel to give tuent experience wit te vriou option
ipyton-SQL
In 1
In 2
Your connection URL i yql+pyyqlrootbuerbuerloclot
In 18
%lo_ext ql
Set te ueri n pwor for connecting to your intnce of SQL
yql_uer "root"
yql_pwor "buerbuer"
yql_url f"yql+pyyqlyql_ueryql_pworloclot" print"Your connection URL i" yql_url
Connect See te ipyton-ql ocuenttion for te vrible yntx
%ql yql_url
SQL Alcey n Pn
In 4
In 5
In 19
pyyql
In 7
In 20
Dt Loing Clic Moel
We will ue te Clic Moel ttpwwwyqltutorilorgyql-ple-tbepx ple tbe for ny of te quetion on ti ex
Te irectory contining ti notebook contin file clic-oel-pleql
Lo te t
Open te file in DtGrip uing File - Open ilog Select ll of te textSQL in te file
Click te green rrowe to run te file content
Running te following querie will tet if te lo worke
In 10
yql+pyyqlrootloclot 0 row ffecte
Out10
Ye I know te cool ki iport p I not cool
iport pn
Pn SQL opertion require SQL Alcey engine
fro qllcey iport crete_engine
ql_engine crete_engineyql_url
iport pyyql
pyyql_con pyyqlconnect uer yql_uer pwor yql_pwor ot "loclot"
port 3306
utocoit True
curorcl pyyqlcurorDictCuror
%ql ue clicoel
In 11
%ql ow tble
yql+pyyqlrootloclot 8 row ffecte
Out11
Tble_in_clicoel
cutoer eployee office oreretil orer pyent prouctline prouct
In 12
yql+pyyqlrootloclot 1 row ffecte
Out12
count
2996
Ln Bebll Dtbe
You previouly loe infortion fro Ln Bebll Dtbe ttpwwwenlnco If you ve not one o te following coe will lo te t into new ce lnb_iter
In 22
yql+pyyqlrootloclot 1 row ffecte
Out22
In 25
Out25
20370
In 26
Out26
110495
In 32
Out32
49430
%ql elect count count fro orer join oreretil uingorerNuber
%ql crete ce lnb_iter
people_f pnre_cv"Peoplecv"
people_fto_ql"people" ce"lnb_iter" conql_engineinexFle if_exit"replce"
btting_f pnre_cv"Bttingcv"
btting_fto_ql"btting" ce"lnb_iter" conql_engineinexFle if_exit"replce"
pitcing_f pnre_cv"Pitcingcv"
pitcing_fto_ql"pitcing" ce"lnb_iter" conql_engineinexFle if_exit"replce"
Ti will tet te t loing
In 29
yql+pyyqlrootloclot 1 row ffecte
Out29
people_count
20370
In 30
yql+pyyqlrootloclot 1 row ffecte
Out30
btting_count
110495
In 33
yql+pyyqlrootloclot 1 row ffecte
Out33
pitcing_count
49430
Written Quetion W1
Quetion
Define te concept of iutble colun n key
Wy o oe ource recoen tt priry key oul be iutble How woul to ipleent iutbility for priry key in tble
Anwer
Type Mrkown n LTeX
W2
Quetion
View re powerful concept in reltionl tbe ngeent yte Lit n briefly explin 3 benefit ofreon for creting view
Anwer
Type Mrkown n LTeX
W3
Quetion
Briefly explin te concept of proceurl lnguge n eclrtive lnguge SQL i pririly eclrtive lnguge SQL e proceure lnguge
cpbilitie in function proceure n trigger Wt i reon for ti ition
Anwer
%ql elect count people_count fro lnb_iterpeople
%ql elect count btting_count fro lnb_iterbtting
%ql elect count pitcing_count fro lnb_iterpitcing
In
W4
Quetion
Te following igr i iple repreenttion of te rcitecture of Jupyter notebook uing MySQL I ti two-tier rcitecture or tree-tier rcitecture Explin your nwer briefly
Anwer
Type Mrkown n LTeX
W5
Quetion
Conier US Socil Security Nuber An exple i "012-34-6789"
Te t type i crcter tring
Te reltionl oel require tt colun ttribute re fro oin
Ue te Socil Security Nuber exple to explin te ifference between type n oin
Anwer
Type Mrkown n LTeX
W6
Quetion
Briefly explin te ifference between
Dtbe tore proceure Dtbe function Dtbe trigger
Anwer
Type Mrkown n LTeX
W7
Quetion
Briefly explin
Nturl join Equi-join Tet join Self-join
Anwer
Type Mrkown n LTeX
W8
Quetion
Briefly explin te ifference between unique key contrint n priry key contrint
Anwer
Type Mrkown n LTeX
W9
Quetion
Give two reon for uing n ocitive entity to ipleent reltionip inte of uing foreign key
Anwer
Type Mrkown n LTeX
W10
Quetion
Briefly explin te concept of
Conceptul oel Logicl oel Pyicl oel
For t oeling
Anwer
Type Mrkown n LTeX
W11
Quetion
Briefly explin te concept of
Dt nipultion lnguge Dt efinition lnguge
Given n exple tteent in SQL for DML n for DDL
Anwer
Type Mrkown n LTeX
W12
Quetion
Co 4t rule i
Rule 4 - Dynic online ctlog be on te reltionl oel
Te t be ecription i repreente t te logicl level in te e wy orinry t o tt utorize uer cn pply te e reltionl lnguge to it interrogtion tey pply to te regulr t
Explin wt ti en n ue SQL to provie exple
Anwer
Type Mrkown n LTeX
W13
Quetion
Te forl efinition of tet join i
Briefly explin te efinition n give n exple
Wy i te fct tt te reltionl lgebr i cloe i iportnt to ti efinition
Anwer
Type Mrkown n LTeX
W14
Quetion
Conier two ifferent tteent in te reltionl lgebr or SQL
Depite being ifferent tteent te tteent y be equivlent Briefly explin wt ti en
Anwer
Type Mrkown n LTeX
W15
Quetion
Conier te following reltion efinition
Wt i proble wit uing nturl join on te two tble
Anwer
Type Mrkown n LTeX
Entity Reltionip Moeling ER-1
Quetion
Ti quetion tet your bility to "botto up" oel or "revere engineering" SQL ce to prouce n explntory ER-igr Ue Lucicrt to rw Crow Foot nottion igr repreenting te following SQL
You cn ue te iple tble ne eg tuent inte of 23_w4111_itertuent
Anwer
rop ce if exit 23_iter crete ce 23_iter
ue 23_iter
rop tble if exit eprtent crete tble if not exit eprtent
ept_coe vrcr4 not null priry key
ept_ne vrcr128 not null
rop tble if exit intructor crete tble if not exit intructor
UNI vrcr12 not null priry key
lt_ne vrcr128 not null firt_ne vrcr128 not null ept_coe vrcr4 null contrint intructor_ept
foreign key ept_coe reference eprtent ept_coe
rop tble if exit tuent crete tble if not exit tuent
UNI vrcr12 not null priry key
lt_ne vrcr128 null
firt_ne vrcr128 null
rop tble if exit tuent_vior crete tble if not exit tuent_vior
tuent_uni vrcr12 not null
intructor_uni vrcr12 not null
viing_trt_te te not null
viing_en_te te null
priry key tuent_uni intructor_uni viing_trt_te contrint tuent_vior_intructor
foreign key intructor_uni reference intructor UNI contrint tuent_vior_tuent
foreign key tuent_uni reference tuent UNI
Put your creen cpture in te e irectory te iter
A ig rcyfilene in te Mrkown cell uing te ctul file ne
ER-2
Quetion
Ti quetion tet your bility to convert un lnguge ecription of t oel into Crow Foot ER-Digr Conier te t oel for Clic Moel tt you loe
orer colun coent
In 15
yql+pyyqlrootloclot 10 row ffecte
Out15
orerNuber orerDte requireDte ippeDte
ttu
Sippe Sippe Sippe Sippe Sippe Sippe Sippe Sippe Sippe Sippe
coent cutoerNuber
None 363 Ceck on vilbility 128 None 181 None 121 None 141 None 145 None 278 Difficult to negotite wit cutoer We nee ore rketing teril 131 None 385 Cutoer requete tt FeEx Groun i ue for ti ipping 486
10100 2003-01-06
10101 2003-01-09
10102 2003-01-10
10103 2003-01-29
10104 2003-01-31
10105 2003-02-11
10106 2003-02-17
10107 2003-02-24
10108 2003-03-03
10109 2003-03-10
2003-01-13 2003-01-18 2003-01-18 2003-02-07 2003-02-09 2003-02-21 2003-02-24 2003-03-03 2003-03-12 2003-03-19
2003-01-10 2003-01-11 2003-01-14 2003-02-02 2003-02-01 2003-02-12 2003-02-21 2003-02-26 2003-03-08 2003-03-11
Tere re everl iue wit ti eign
If tere re ultiple coent or repone to coent te coent fiel becoe ulti-vlue
Te pproc oe not ve infortion on wen te coent w e wo e te coent n weter it i repone or elbortion
You will olve ti proble in iplifie verion of clic oel In te iplifie oel tere re tree entity type 1 peron te following ttribute
ID lt_ne firt_ne
2 orer te following ttribute orer_i
orer_ttu
orer_te
3 coent te following ttribute
coent_i i unique ID for ll coent
prent_coent_i i te coent_i of coent for wic ti coent i repone or elbortion coent_tietp wen te coent occure
coenter_i i te ID of te peron king te coent
orer_i i te ID of te orer for to wic ti coent pplie
Ue Lucicrt to rw logicl oel for te ecribe toel
You y note to te igr to ocuent reonble uption or egn eciion
Anwer
Put your creen cpture in te e irectory te iter
A ig rcyfilene in te Mrkown cell uing te ctul file ne
Type Mrkown n LTeX
Reltionl Algebr
Ue te RelX Clcultor n te Silberctz clcultor ttpbi-uibkgitubiorelxclcgit4f7866c17624c9f85e2482078be8relx- ilberctz-englitxt0 wit te Silberctz tbe for tee quetion
Your nwer will ve two Mrkown cell Te firt i te reltionl tteent you ue to olve te proble Te econ i creen cpture of te query execution n firt pge of reult row An exple i
%ql elect fro clicoelorer liit 10
π coure_i title intructor_i←ID intructor_ne←ne
intructor
R1
Quetion
Conier te reltion prouce by
π coure_i ec_i builing roo_nuber tie_lot_i ection
Ti contin ection teir tie ignent n roo ignent inepenent of te yer n eeter
Two ection in ti erive tble conflict if tey ve te e builing roo_nuber tie_lot_i
My nwer to ti quetion i
onecoure_i oneec_i onebuiling oneroo_nuber onetie_lot_i twocoure_i twoec_i twobuiling tworoo_nuber twotie_lot_i
coure ⨝ ection ⨝
tece
CS-347 1 Tylor 3128 A CS-190 2 Tylor EE-181 1 Tylor 3128 C CS-319 2 Tylor
Your nwer cnnot inclue coure n ection tt conflict wit teelve or ve two row tt ow te e conflict
Anwer
Type Mrkown n LTeX
R2
Quetion
You y ue te following opertor for ti quetion π σ ρ ←
Ue te intructor tuent vior tble for ti quetion
Tere re oe tuent tt o not ve vior Tt re oe intructor tt re not vior An intructor cn be n vior for tuent if tey re in te e eprtent ept_ne Prouce reltion of te for
intructor_i intructor_ne intructor_ept_ne tuent_i tuent_ne tuent_ept_ne
Tt tce intructor tt o not vie tuent n tuent tt o not ve vior n re in te e eprtent
Anwer
Type Mrkown n LTeX
SQL S1
Quetion
3128 A 3128 C
You ve logicl toel ER-igr ee below
You nee to ue DDL to efine ce tt relize te oel
Logicl oel re not pecific enoug for irect ipleenttion Ti en tt
You will ve to ign concrete type to colun n cooe ting like GENERATED DEFAULT etc
You y ve to ecopoe tble into two tble or extrct coon ttribute fro ultiple tble into ingle reference tble Ipleenting te reltionip y require ing colun n foreign key ocitive entitie etc
You y ve to ke oter eign n ipleenttion coice Ti en tt tere i no ingle correct nwer
You oul ocuent ny reonble uption you ke
Anwer
Deign Deciion Note etc
1 2
DDL
ER Digr
Execute your DDL in te cell below You y ue DtGrip or oter tool to elp buil te ce You cn copy n pte te SQL CREATE TABLE below but you MUST execute te tteent
In 16
yql+pyyqlrootloclot 0 row ffecte
yql+pyyqlrootloclot 1 row ffecte
Out16
In
In
S2
Quetion
Ue te clic oel tbe tt you loe
Write query tt return te following reult
cutoerNuber cutoerNe no_of_orer totl_revenue
were
cutoerNuber n cutoerNe re fro cutoer
no_of_orer i te nuber of orer te cutoer plce
totl_revenue i te u of quntityOrerepriceEc for ll orerDetil in orer ocite wit cutoer
If cutoer not plce ny orer no_of_orer n totl_revenue ut be 0
Anwer
In
%ql rop ce if exit 23_iter_eicl %ql crete ce 23_iter_eicl
%%ql
%%ql
%%ql
Bet Bebll Plyer
Quetion
Ti quetion ue lnb_iterbtting lnb_iterpitcing n lnb_iterpeople You previouly loe ti infortion
Tere query copute perfornce etric Btting
On-be percentge OBP i u + uBBub + uBB Ti vlue i NULL if ub 0 Slugging percentge SLG i efine by te function below Te vlue i NULL if ub 0
u - u1b - u2b - u3b - ur + 2u2b + 3u3b + 4r
ub
Pitcing
totl_win i uw
totl_loe i ul
win_percentge i uwuw + ul Ti vlue i NULL if uw + ul 0
Profeor Ferguon two criteri for oeone being gret bebll plyer A ply ut eet t let one of te criteri to be gret bebll plyer Btting
Totl nuber of b 1500
SLG Creer SLG 375 Pitcing
uw + ul 200
win_percentge 070 or uw 300
In your reult tble tere i oe itionl guince
gret_becue i eiter Pitcer or Btter be on weter te plyer tce te btting or pitcing criteri
Te vlue fro btting re None if te plyer i not qulify be on btting Te vlue fro pitcing re None if te plyer i not qulify on pitcing
Note For ti query to run efficiently you will nee to crete inexe on te tble Anwer
Execute your crete inex tteent below
In
Execute your SQL tteent proucing te query reult below
In
Dt n Sce Clenup Explntion n Setup
Tere re everl iue wit te ce for clicoel Two of te iue re cutoercountry Hving progr or people enter country ne i prone to error prouctprouctCoe i clerly not n toic vlue
Te following SQL crete ce wit copie of te t Te SQL lo lo tble of ISO country coe ttpenwikipeiorgwikiLit_of_ISO_3166_country_coe
%%ql
%%ql
In 35
%ql crete ce clicoel_iter
yql+pyyqlrootloclot 1 row ffecte
Out35
In 36
Out36
246
In 39
io_f pnre_cvwikipei-io-country-coecv io_fto_qlcountry_coe ceclicoel_iter
conql_engine inexFle if_exit"replce"
%%ql
ue clicoel_iter
lter tble clicoel_itercountry_coe
cnge Engli ort ne lower ce ort_ne text null
lter tble clicoel_itercountry_coe cnge Alp-2 coe lp_2_coe text null
lter tble clicoel_itercountry_coe cnge Alp-3 coe lp_3_coe text null
lter tble clicoel_itercountry_coe cnge Nueric coe nuberic_coe bigint null
lter tble clicoel_itercountry_coe cnge ISO 3166-2 io_text text null
yql+pyyqlrootloclot 0 row ffecte
0 row ffecte
0 row ffecte
0 row ffecte 0 row ffecte 0 row ffecte
Out39
In 38
yql+pyyqlrootloclot 0 row ffecte
122 row ffecte
110 row ffecte
Out38
DE-1
Quetion
Tere re four country vlue in cutoer tt re not in ort_ne of country_coe Te four iing vlue re
country
%%ql
ue clicoel_iter
crete tble cutoer elect fro clicoelcutoer crete tble prouct elect fro clicoelprouct
USA
Norwy UK Rui
Write n SQL query tt return te infortion bout by querying cutoer n country_coe Anwer
In
DE-2
Quetion
Norwy i on te lit becue tere re pce in te entry Te following query ow ti fct
%%ql
In 40
yql+pyyqlrootloclot 2 row ffecte
country
%%ql
elect cutoerNuber cutoerNe country
fro cutoer were lengtcountry lengttricountry
Out40
cutoerNuber
167 299
cutoerNe country
Herkku Gift Norwy Norwy Gift By Mil Co Norwy
Te pping of te oter country ne i
cutoercountry country_coeort_ne
Write ingle upte tteent tt correct te vlue for cutoercountry Anwer
In
DE-3
Quetion
Te finl tk re
A colun io_coe to cutoer tt i te lp_2_coe fro country_coe
Crete foreign key reltionip cutoerio_coe - country_coelp_2_coe
Drop country fro cutoer
Crete view cutoer_country of te for cutoerNuber cutoerNe country io_coe
Anwer
USA UK Rui
Unite Stte Unite Kingo Ruin Feertion
%%ql
In
%%ql
Put te ALTER TABLE n CREATE VIEW tteent ere
In
DE-4
Jut kiing
My firt intent w to ve you fix prouct
Ten I tougt I woul ke ti n extr creit quetion
Finlly I ecie tt ll tuent get 5 point e to tere core for ti ex Since I never "curve up" you ll get bonu on finl gre for putting up wit te cl
In
%%ql
Write SELECT tt iply 25 cutoer orte by cutoerNe
