COMS W4111辅导、辅导SQL编程语言

- 首页 >> Database作业


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


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


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


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

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



cutoer eployee office oreretil orer pyent prouctline prouct

In 12

yql+pyyqlrootloclot 1 row ffecte




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


In 25



In 26



In 32



%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




In 30

yql+pyyqlrootloclot 1 row ffecte




In 33

yql+pyyqlrootloclot 1 row ffecte




Written Quetion W1


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


Type Mrkown n LTeX



View re powerful concept in reltionl tbe ngeent yte Lit n briefly explin 3 benefit ofreon for creting view


Type Mrkown n LTeX



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


%ql elect count people_count fro lnb_iterpeople

%ql elect count btting_count fro lnb_iterbtting

%ql elect count pitcing_count fro lnb_iterpitcing




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


Type Mrkown n LTeX



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


Type Mrkown n LTeX



Briefly explin te ifference between

Dtbe tore proceure Dtbe function Dtbe trigger


Type Mrkown n LTeX



Briefly explin

Nturl join Equi-join Tet join Self-join


Type Mrkown n LTeX



Briefly explin te ifference between unique key contrint n priry key contrint


Type Mrkown n LTeX



Give two reon for uing n ocitive entity to ipleent reltionip inte of uing foreign key


Type Mrkown n LTeX



Briefly explin te concept of

Conceptul oel Logicl oel Pyicl oel

For t oeling


Type Mrkown n LTeX



Briefly explin te concept of

Dt nipultion lnguge Dt efinition lnguge

Given n exple tteent in SQL for DML n for DDL


Type Mrkown n LTeX



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


Type Mrkown n LTeX



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


Type Mrkown n LTeX



Conier two ifferent tteent in te reltionl lgebr or SQL

Depite being ifferent tteent te tteent y be equivlent Briefly explin wt ti en


Type Mrkown n LTeX



Conier te following reltion efinition

Wt i proble wit uing nturl join on te two tble


Type Mrkown n LTeX

Entity Reltionip Moeling ER-1


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


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



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


orerNuber orerDte requireDte ippeDte


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



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


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




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 ?


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


Type Mrkown n LTeX



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


Type Mrkown n LTeX



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


Deign Deciion Note etc

1 2


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






Ue te clic oel tbe tt you loe

Write query tt return te following reult

cutoerNuber cutoerNe no_of_orer totl_revenue


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



%ql rop ce if exit 23_iter_eicl %ql crete ce 23_iter_eicl




Bet Bebll Plyer


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



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


Execute your SQL tteent proucing te query reult below


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



In 35

%ql crete ce clicoel_iter

yql+pyyqlrootloclot 1 row ffecte


In 36



In 39

io_f pnre_cvwikipei-io-country-coecv io_fto_qlcountry_coe ceclicoel_iter

conql_engine inexFle if_exit"replce"


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