ADMINISTRACION DE BASE DE DATOS
ADMINISTRACION DE BASE DE DATOS Ejercicios Nombre: Delgado Lucas Kimberly Nayeska Curso: 5 Nivel “A”
2016
Docente:
Ing.Carlos Manosalva
o v i t a e r C r o l a V
2016
1
ADMINISTRACION DE BASE DE DATOS Ejercicios Ejerc icios sentencias SQL basicos Laboratorio #1 1 !se "aster o se$ect % &ro" s's s's(atabases
2 se$ect % &ro" s's s's(")tran)$oc*s +,ere reso!rce)(atabase)i( - DB)ID +,ere reso!rce)(atabase)i( DB)ID./ ./
ADMINISTRACION DE BASE DE DATOS Ejercicios Ejerc icios sentencias SQL basicos Laboratorio #1 1 !se "aster o se$ect % &ro" s's s's(atabases
2 se$ect % &ro" s's s's(")tran)$oc*s +,ere reso!rce)(atabase)i( - DB)ID +,ere reso!rce)(atabase)i( DB)ID./ ./
!se A(ent!reor*s201 !se se$ect na"e se$ect na"e &ro" &ro" 3ro(!ction 3ro(!ction3ro(!ctCateor' o
4 !se !se A(ent!reor*s201 se$ect na"e &ro" se$ect na"e &ro" 3ro(!ction 3ro(!ction3ro(!ctCateor' o
Laboratorio #2
1/ se$ect 3ro(!ctID5 Na"e5Co$or5List3rice &ro" 3ro(!ction3ro(!ct
2/ se$ect 3ro(!ctID5 Na"e5Co$or5List3rice &ro" 3ro(!ction3ro(!ct +,ere List3rice -0
4/ se$ect 3ro(!ctID5 Na"e5Co$or5List3rice &ro" 3ro(!ction3ro(!ct +,ere Co$or is N7LL
/ se$ect 3ro(!ctID5 Na"e5Co$or5List3rice &ro" 3ro(!ction3ro(!ct +,ere Co$or - 8N7LL8
9/ se$ect 3ro(!ctID5 Na"e5Co$or5List3rice &ro" 3ro(!ction3ro(!ct +,ere Co$or is not N7LL an( List3rice -0
6/ se$ect Na"e: 8;8 :Co$or as na"e)an()co$or &ro" 3ro(!ction3ro(!ct +,ere Co$or is not N7LL
>/ se$ect 3ro(!ctID5 Na"e &ro" 3ro(!ction3ro(!ct +,ere 3ro(!ctID ?900 an( 3ro(!ctID @00
/ se$ect 3ro(!ctID5 Na"e5 Co$or &ro" 3ro(!ction3ro(!ct +,ere Co$or - 8B$ac*8 or Co$or - 8b$!e8
10/ se$ect na"e5 List3rice &ro" 3ro(!ction3ro(!ct +,ere na"e $i*e 8S8
11/ se$ect na"e5 List3rice &ro" 3ro(!ction3ro(!ct +,ere na"e $i*e 8A8 or na"e $i*e 8S8 or(er b' na"e
12/ se$ect na"e5 List3rice &ro" 3ro(!ction3ro(!ct +,ere na"e $i*e 8S3O8 an( na"e not $i*e 8))))8
14/ se$ect Co$or &ro" 3ro(!ction3ro(!ct ro! b' Co$or
1/ se$ect 3ro(!ctS!bcateor'ID5 Co$or &ro" 3ro(!ction3ro(!ct +,ere Co$or is not N7LL an( 3ro(!ctS!bcateor'ID is not N7LL ro! b' 3ro(!ctS!bcateor'ID5 Co$or
19/ se$ect 3ro(!ctS!bCateor'ID5 LET.FNa"eG549/ AS FNa"eG 5 Co$or5 List3rice &ro" 3ro(!ction3ro(!ct +,ere Co$or IN .8Re(858B$ac*8/ AND List3rice BETEEN 1000 AND 2000 OR 3ro(!ctS!bCateor'ID - 1 or(er b' 3ro(!ctID
16/ se$ect FNa"eG5 isn!$$.Co$or5 87n*no+n8/ as Co$or List3rice &ro" 3ro(!ction3ro(!ct
Laboratorio#4 1/ se$ect CO7NT.3ro(!ctN!"ber/ &ro" 3ro(!ction3ro(!ct
2/ se$ect CO7NT.3ro(!ctS!bcateor'ID/ &ro" 3ro(!ction3ro(!ct +,ere 3ro(!ctS!bcateor'ID is not N7LL
4/ se$ect CO7NT.3ro(!ctS!bcateor'ID/ as ro(!ctI(Cateor'5 co!nt.3ro(!ctN!"ber/ as Co!nte(3ro(!ct &ro" 3ro(!ction3ro(!ct ro! b' 3ro(!ctS!bcateor'ID
/ se$ect co!nt .3ro(!ctS!bcateor'ID/ &ro" 3ro(!ction3ro(!ct +,ere 3ro(!ctS!bcateor'ID is not N7LL
se$ect co!nt .3ro(!ctS!bcateor'ID/ as Nos!bCat &ro" 3ro(!ction3ro(!ct +,ere 3ro(!ctS!bcateor'ID is not N7LL
9/ se$ect co!nt.3ro(!ctID/as ro(!ctoID5 S7M.Q!antit'/as T,eS!" &ro" 3ro(!ction3ro(!ctInentor' ro! b' 3ro(!ctID
6/ se$ect 3ro(!ctID5 S7M.Q!antit'/as T,eS!" &ro" 3ro(!ction3ro(!ctInentor' +,ere 3ro(!ctID @ 0 ro! b' 3ro(!ctID ,ain S7M.Q!antit'/ ?100
>/ se$ect a.Q!antit'/ as t,eA &ro" 3ro(!ction3ro(!ctInentor' +,ere LocationID - 10
/ se$ect S,e$& 53ro(!ctID5 a.Q!antit'/ as t,eA &ro" 3ro(!ction3ro(!ctInentor' +,ere LocationID - 10 ro! b' S,e$& 53ro(!ctID
10/ SELECT Co$or5 C$ass5co!nt.3ro(!ctID/ as t,eco!nt5 a.List3rice/ as a3rice &ro" 3ro(!ction3ro(!ct +,ere Co$or is not N7LL an( C$ass is not N7LL ro! b' HRO73INH SETS. Co$or5 C$ass/5List3rice
11/ SELECT 3ro(!ctS!bcateor'ID5 CO7NT.Na"e/ as Co!nte(5 as IsHran(Tota$ ROM 3ro(!ction3ro(!ct HRO73 B ROLL73 .3ro(!ctS!bcateor'ID/
12/ se$ect RO)N7MBER./ OJER.ORDER B Na"e ASC/ as i(5Na"e &ro" 3ro(!ction3ro(!ctCateor'
14/ SELECT inentario3ro(!ctID5 ro(!ctoNa"e5 inentarioLocationID5 inentarioQ!antit'5 RAN ./ OJER .3ARTITION B inentarioLocationID ORDER B inentarioQ!antit' DESC/ AS Ran* ROM 3ro(!ction3ro(!ctInentor' AS inentario INNER KOIN 3ro(!ction3ro(!ct AS ro(!cto ON inentario3ro(!ctID - ro(!cto3ro(!ctID ERE inentarioLocationID BETEEN 2 AND ORDER B inentarioLocationID
Laboratorio# I se$ect co!ntr'Na"e5 roinceNa"e ROM 3ersonCo!ntr'Reion AS co!ntr' INNER KOIN 3ersonState3roince AS roince On co!ntr'Co!ntr'ReionCo(e - roinceCo!ntr'ReionCo(e
II se$ect RO)N7MBER./ OJER.ORDER B co!ntr'Na"e ASC/ as i(5 co!ntr'Na"e5 roinceNa"e ROM 3ersonCo!ntr'Reion AS co!ntr' INNER KOIN 3ersonState3roince AS roince On co!ntr'Co!ntr'ReionCo(e - roinceCo!ntr'ReionCo(e +,ere co!ntr'Na"e - 8Cana(a8 or co!ntr'Na"e - 8Her"an'8
III se$ect Sa$esOr(erID5 Or(erDate5Sa$es3ersonID5 B!sinessEntit'ID5 Bon!s5 Sa$esTD &ro" Sa$esSa$esOr(erea(er as O INNER KOIN Sa$esSa$es3erson as 3 ON O Territor'ID-3 Territor'ID
IJ se$ect Sa$esOr(erID5 Sa$es3ersonID5 Kobtit$e5 Bon!s5 Sa$esTD &ro" Sa$esSa$esOr(erea(er as O INNER KOIN Sa$esSa$es3erson as 3 ON O Territor'ID-3 Territor'ID INNER KOIN !"anReso!rcesE"$o'ee as E ON EB!sinessEntit'ID - 3B!sinessEntit'ID
J se$ect Sa$esOr(erID5 Sa$es3ersonID5irstNa"e5 LastNa"e5 Bon!s &ro" Sa$esSa$esOr(erea(er as O INNER KOIN Sa$esSa$es3erson as 3 ON O Territor'ID-3 Territor'ID INNER KOIN !"anReso!rcesE"$o'ee as E ON EB!sinessEntit'ID - 3B!sinessEntit'ID INNER KOIN 3erson3erson as ON B!sinessEntit'ID - 3B!sinessEntit'ID
JI se$ect Sa$esOr(erID5 Sa$es3ersonID5irstNa"e5 LastNa"e5 Bon!s &ro" Sa$esSa$esOr(erea(er as O INNER KOIN Sa$esSa$es3erson as 3 ON O Territor'ID-3 Territor'ID INNER KOIN 3erson3erson as ON B!sinessEntit'ID - 3B!sinessEntit'ID
