Partial preview of the text
Download WGU D427 Data Management Applications |MYSQL - SQL Programing Test | Intermediate Level wi and more Exams Principles of Database Management in PDF only on Docsity!
WGU D427 Data Management Applications |MYSQL -
SQL Programing Test | Intermediate Level with
complete solutions | 100% Correct.
Question:
Thei,-Moviei,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
RatingCode—variable-lengthi,-string,
Year—integer,
Writei,-ai,-SQLi,-statementi,-toi,-createi,-ai,-viewi,-namedi,-MyMoviesi,-thati,-containsi,-
thei,-Title,i,-Genre,i,-andi,-Yeari,-columnsi,-fori,-alli,-movies.i,-Ensurei,-youri,-resulti,-seti,-
returnsi,-thei,-columnsi,-ini,-thei,-orderi,-indicated.?
Answer:
CREATEi,-VIEWi,-MyMoviesi,-AS
SELECTi,-Title,i,-Genre,i,-Year
FROMi,-Movie;
Question:
Thei,-Moviei,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
RatingCode—variable-lengthi,-string,
Year—integer,
Writei,-ai,-SQLi,-statementi,-toi,-modifyi,-thei,-Moviei,-tablei,-toi,-makei,-thei,-IDi,-columni,-
thei,-primaryi,-key.?
Answer:
ALTERi,-TABLEi,-Movie
ADDi,-PRIMARYi,-KEYi,-(ID);
Question:
Thei,-Moviei,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
RatingCode—variable-lengthi,-string,
Year—integer,
Thei,-YearStatsi,-tablei,-hasi,-thei,-followingi,-columns:
Year—integer,
TotalGross—biginti,-unsigned,
Releases—integer,
Writei,-ai,-SQLi,-statementi,-toi,-designatei,-thei,-Yeari,-columni,-ini,-thei,-Moviei,-tablei,-asi,-
ai,-foreigni,-keyi,-toi,-thei,-Yeari,-columni,-ini,-thei,-YearStatsi,-table.?
MiddleInitial—fixed-lengthi,-stringi,-withi,- 1 i,-character,i,-
LastName—variable-lengthi,-stringi,-withi,-upi,-toi,- 100 i,-characters,
DateOfBirth—date,
AnnualPledge—positivei,-decimali,-valuei,-representingi,-ai,-costi,-ofi,-upi,-toi,-
$999,999,i,-withi,- 2 i,-digitsi,-fori,-cents,
Writei,-ai,-SQLi,-statementi,-toi,-createi,-thei,-Memberi,-table.
Doi,-noti,-addi,-anyi,-additionali,-constraintsi,-toi,-anyi,-columni,-beyondi,-whati,-isi,-
stated.?
Answer:
CREATEi,-TABLEi,-Memberi,-(
IDi,-INTi,-PRIMARYi,-KEYi,-unsigned,
FirstNamei,-VARCHAR(100),
MiddleInitiali,-CHAR(1),
LastNamei,-VARCHAR(100),
DateOfBirthi,-DATE,
AnnualPledgei,-DECIMAL(8,i,-2)i,-unsigned
Question:
Thei,-Ratingi,-tablei,-hasi,-thei,-followingi,-columns:
RatingCode—variable-lengthi,-string,i,-primaryi,-key,
RatingDescription—variable-lengthi,-string,
Thei,-Moviei,-tablei,-shouldi,-havei,-thei,-followingi,-columns:
Title—variable-lengthi,-string,i,-maximumi,- 30 i,-characters,
RatingCode—variable-lengthi,-string,i,-maximumi,- 5 i,-characters,
Writei,-ai,-SQLi,-statementi,-toi,-createi,-thei,-Moviei,-table.i,-
Designatei,-thei,-RatingCodei,-columni,-ini,-thei,-Moviei,-tablei,-asi,-ai,-foreigni,-keyi,-toi,-
thei,-RatingCodei,-columni,-ini,-thei,-Ratingi,-table.?
Answer:
CREATEi,-TABLEi,-Moviei,-(
Titlei,-VARCHAR(30),
RatingCodei,-VARCHAR(5),
FOREIGNi,-KEYi,-(RatingCode)i,-REFERENCESi,-Rating(RatingCode)
Question:
Thei,-Moviei,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
RatingCode—variable-lengthi,-string,
Year—integer,
Ai,-newi,-columni,-musti,-bei,-addedi,-toi,-thei,-Moviei,-table:
Columni,-name:i,-Score,
Question:
Thei,-Moviei,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
RatingCode—variable-lengthi,-string,
Year—integer,
Writei,-ai,-SQLi,-statementi,-toi,-deletei,-thei,-rowi,-withi,-thei,-IDi,-valuei,-ofi,- 3 i,-fromi,-thei,-
Moviei,-table.?
Answer:
DELETEi,-FROMi,-Movie
WHEREi,-IDi,-=i,-3;
Question:
Thei,-Moviei,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
RatingCode—variable-lengthi,-string,
Year—integer,
Writei,-ai,-SQLi,-queryi,-toi,-displayi,-alli,-Titlei,-valuesi,-ini,-alphabeticali,-orderi,-A-Z.?
Answer:
SELECTi,-Title
FROMi,-Movie
ORDERi,-BYi,-Titlei,-ASC;
Question:
Thei,-Moviei,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
RatingCode—variable-lengthi,-string,
Year—integer,
Writei,-ai,-SQLi,-queryi,-toi,-returni,-howi,-manyi,-moviesi,-havei,-ai,-Yeari,-valuei,-ofi,-2019.?
Answer:
SELECTi,-COUNT(*)i,-ASi,-MovieCount
FROMi,-Movie
WHEREi,-Yeari,-=i,-2019;
Question:
Thei,-Moviei,-tablei,-hasi,-thei,-followingi,-columns:
IDi,--i,-integer,i,-primaryi,-key,
Question:
Thei,-Packagei,-tablei,-hasi,-thei,-followingi,-columns:
Weight—decimal,
Description—optionali,-variablei,-lengthi,-string,
LastChangedDate—date,
TrackingNumber—integer,
Whichi,-columni,-shouldi,-bei,-designatedi,-thei,-primaryi,-keyi,-fori,-thei,-Packagei,-
table??
Answer:
TrackingNumber
Question:
Whichi,-datai,-typei,-willi,-storei,-"2022- 01 - 10 i,-14:22:12"i,-asi,-ai,-temporali,-valuei,-
withouti,-lossi,-ofi,-information??
Answer:
DATETIME
Question:
Whichi,-SQLi,-commandi,-isi,-ani,-examplei,-ofi,-datai,-definitioni,-languagei,-(DDL)??
Answer:
CREATE,i,-ALTER,i,-DROP,i,-RENAME,i,-TRUNICATE
Question:
Whati,-doesi,-thei,-SQLi,-keywordi,-commandi,-TRUNCATEi,-do??
Answer:
TRUNCATEi,-removesi,-alli,-rows,i,-unlikei,-DELETE,i,-whichi,-removesi,-specifici,-rows.
Question:
Howi,-wouldi,-ai,-databasei,-enginei,-processi,-ani,-updatei,-thati,-violatesi,-ai,-RESTRICTi,-
referentiali,-integrityi,-constraint??
Answer:
RESTRICTi,-CONSTRAINTi,-willi,-rejecti,-thei,-UPDATEi,-ifi,-thei,-inputi,-doesi,-noti,-
coincidei,-withi,-predeterminedi,-valuesi,-andi,-generatesi,-ani,-error.
Question:
Whichi,-restrictionsi,-appliesi,-wheni,-usingi,-ai,-materializedi,-view??
Answer:
Materializedi,-viewsi,-requirei,-toi,-bei,-refreshedi,-frequentlyi,-andi,-requirei,-morei,-
storagei,-space.
Answer:
DELETEi,-FROMi,-table_name
WHEREi,-condition;
Question:
Thei,-Booki,-tablei,-hasi,-thei,-followingi,-columns:
genrei,--i,-varchar(20),
pagesi,--i,-integer,
author_idi,--i,-char(3),
isbn_numberi,--i,-varchar(20),
Whichi,-columni,-shouldi,-bei,-designatedi,-ati,-thei,-primaryi,-keyi,-fori,-thei,-Booki,-
table??
Answer:
isbn_number
Question:
Thei,-Booki,-tablei,-hasi,-thei,-followingi,-columns:
genrei,--i,-varchar(20),
pagesi,--i,-integer,
author_idi,--i,-char(3),
isbn_numberi,--i,-varchar(20),
Whichi,-columni,-shouldi,-bei,-designatedi,-asi,-thei,-foreigni,-keyi,-fori,-thei,-Booki,-
table??
Answer:
author_id
Question:
Whichi,-datai,-typei,-representsi,-numbersi,-withi,-fractionali,-values:?
Answer:
DECIMAL
Question:
Whichi,-ofi,-thei,-followingi,-isi,-ai,-DMLi,-command??
Answer:
INSERT,i,-DELETE,i,-UPDATE
Question:
CREATEi,-TABLEi,-Invoicei,-(i,-
i,-invoice_idi,-INTi,-NOTi,-NULLi,-AUTO_INCREMENT,i,-
i,-datei,-DATEi,-NOTi,-NULL,i,-
i,-customer_idi,-INTi,-NOTi,-NULL,i,-
Answer:
Thei,-deletei,-ofi,-thei,-Customeri,-wouldi,-noti,-bei,-allowed.
Question:
CREATEi,-TABLEi,-Invoicei,-(
invoice_idi,-INTi,-NOTi,-NULLi,-AUTO_INCREMENT,
datei,-DATEi,-NOTi,-NULL,
customer_idi,-INTi,-NOTi,-NULL,
PRIMARYi,-KEYi,-(invoice_id),
FOREIGNi,-KEYi,-(customer_id)i,-REFERENCESi,-Customeri,-(customer_id)i,-ONi,-
DELETEi,-SETi,-TOi,-NULL
Lookingi,-ati,-thei,-Customeri,-andi,-Invoicei,-tablesi,-andi,-thei,-CREATEi,-TABLEi,-fori,-thei,-
Invoicei,-tablei,-withi,-foreigni,-keyi,-referencei,-statementi,-above,i,-whati,-wouldi,-
happeni,-toi,-invoicesi,-ini,-thei,-Invoicei,-tablei,-thati,-arei,-linkedi,-toi,-ai,-customeri,-ifi,-
thati,-customeri,-isi,-deleted.?
Answer:
Thei,-Customeri,-IDi,-fori,-thosei,-invoicesi,-wouldi,-bei,-changedi,-toi,-NULL.
Question:
Whati,-arei,-somei,-constrainti,-factsi,-abouti,-materializedi,-view??
Answer:
Iti,-isi,-storedi,-andi,-iti,-musti,-bei,-refreshedi,-wheneveri,-thei,-basei,-tablei,-changes.
Question:
Thei,-Customeri,-tablei,-willi,-havei,-thei,-followingi,-columns:
CustomerID—positivei,-integer,
FirstName—variable-lengthi,-stringi,-withi,-upi,-toi,- 50 i,-characters,
MiddleInitial—fixed-lengthi,-stringi,-withi,- 1 i,-character,
LastName—variable-lengthi,-stringi,-withi,-upi,-toi,- 50 i,-characters,
DateOfBirth—date,
CreditLimit—positivei,-decimali,-valuei,-representingi,-ai,-costi,-ofi,-upi,-toi,-$19,999,i,-
withi,- 2 i,-digitsi,-fori,-cents,
Writei,-ai,-SQLi,-statementi,-toi,-createi,-thei,-Customeri,-table.
Doi,-noti,-addi,-anyi,-additionali,-constraintsi,-toi,-anyi,-columni,-beyondi,-whati,-isi,-
stated.?
Answer:
CREATEi,-TABLEi,-Customeri,-(
i,-CustomerIDi,-INTi,-UNSIGNED,
i,-FirstNamei,-VARCHAR(50),
i,-MiddleInitiali,-CHAR(1),
i,-LastNamei,-VARCHAR(50),
i,-DateOfBirthi,-DATE,
Thei,-Automobilei,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Make—variable-lengthi,-string,
Model—variable-lengthi,-string,
Year—integer,
Ai,-newi,-columni,-musti,-bei,-addedi,-toi,-thei,-Automobilei,-table:
Columni,-name:i,-SafetyRating,
Datai,-type:i,-decimal(3,1),
Writei,-ai,-SQLi,-statementi,-toi,-addi,-thei,-SafetyRatingi,-columni,-toi,-thei,-Automobilei,-
table.?
Answer:
ALTERi,-TABLEi,-Automobile
ADDi,-SafetyRatingi,-DECIMAL(3,1);
Question:
Thei,-Booki,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
Year—integer,
Writei,-ai,-SQLi,-statementi,-toi,-createi,-ai,-viewi,-namedi,-MyBooksi,-thati,-containsi,-thei,-
Title,i,-Genre,i,-andi,-Yeari,-columnsi,-fori,-alli,-moviesi,-books.i,-Ensurei,-youri,-resulti,-seti,-
returnsi,-thei,-columnsi,-ini,-thei,-orderi,-indicated.?
Answer:
CREATEi,-VIEWi,-MyBooksi,-AS
SELECTi,-Title,i,-Genre,i,-Year
FROMi,-Book;
Question:
Ai,-databasei,-hasi,-ai,-viewi,-namedi,-BookView.i,-
Writei,-ai,-SQLi,-statementi,-toi,-deletei,-thei,-viewi,-namedi,-BookViewi,-fromi,-thei,-
database.?
Answer:
DROPi,-VIEWi,-BookView;
Question:
Thei,-Booki,-tablei,-hasi,-thei,-followingi,-columns:
ID—integer,i,-primaryi,-key,
Title—variable-lengthi,-string,
Genre—variable-lengthi,-string,
Year—integer,
Writei,-ai,-SQLi,-statementi,-toi,-modifyi,-thei,-Booki,-tablei,-toi,-makei,-thei,-IDi,-columni,-
thei,-primaryi,-key.?
Answer:
