COMS W4111代写、代写SQL编程语言

日期：2023-03-09 09:47

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