Data 3330 Exam 2 Questions And Answers Verified Solutions.

SELECT - Correct Answers column selection, TOP function, case expressions FROM - Correct Answers Table selection WHERE - Correct Answers Predicate logic operators BETWEEN IN, NOT IN Wild Card (like) Null (IS, IS NOT) GROUP BY - Correct Answers All fields that aren't apart of an aggregate function need to be in the group by HAVING - Correct Answersallows you to include, or eliminate entire groups that don't meet your aggregate function criteria Predicate Logic - Correct Answersmathematical concept based on something evaluating to True or False GROUP BY/HAVING - Correct Answerspredicate logic for rows versus groups CONCAT - Correct Answerscombine fields together CONCATE (FirstName, LastName) AS FullName RTRIM, LTRIM - Correct Answersremove leading spaces CONCATE(RTRIM(FirstName), ' ', LastName) as FullName CAST - Correct AnswersChange the datatype of a column/attribute CAST (CashPrize as Integer) As 'Cash Prize' FORMAT - Correct Answersadd a currency look to a column, add formatting to a column FORMAT (CashPrize, 'C') as "currency' SUBSTRING - Correct Answersallows you to obtain a portion of a string SUBSTRING (X, Y, Z) X = column of string to be parsed Y = starting position z = ending position Inner joins - Correct Answersallows you to access all columns from every table that is joined FROM STUDENT as S JOIN GYM_LOCKER as G ON S.StudentID = GSTUDENTID Outer Joins - Correct Answersthese include null values left join right join full join Left Join - Correct Answersinclude all rows in left table FROM AWARD as A LEFT OUTER JOIN STUDENT_AWARD as S ON A.AwardID = S.AwardID Right Join - Correct Answersinclude all rows in right table Full Join - Correct Answersinclude allr ows from both tables SQL - 89 - Correct AnswersSELECT * FROM STUDENT as S, GYM_LOCKER as G WHERE S.StudentID = G.StudentID; We don't use this method because it doesn't give us an error when we don't join the tables together correctly SQL - 92 - Correct AnswersSELECT * FROM STUDENT as A JOIN GYM_LOCKER as G ON A.StudentID = G.StudentID SUBqueries - Correct Answersanother method for solving problems based on information needs from multiple tables only works when the output columns from the select statement are from the same table Subqueries and aggregate functions - Correct Answersuseful when you need results from an aggregate function to make a calculation or to only see those with a MAX price that is higher than the AVG price How to format a subquery - Correct AnswersSELECT Fname, Lname FROM STUDENTWHERE StudentID IN(SELECT StudentID FROM GYM_LOCKER WHERE LockerLocation = 'Building B'); Type 1 Subquery - Correct Answersexecutes independently from the outer query the subquery executes then the outer query uses that temporary data set SELECT TeamID FROM Teams WHERE std_teamID IN (SELECT teamID FROM Teams WHERE Project like '%auto shop%'); Type 2 Subquery - Correct Answersreferences on or more columns in the outer query executes once for each row in the outer query · SELECT stdid, stdfname, stdlname, std_teamID · FROM students · WHERE EXISTS ( SELECT * · FROM teams · WHERE _teamID = ID and project like '%auto shops%')' Scalar Subquery - Correct Answersuses the = symbol and requires on unique row as output Multivalued - Correct Answersuses IN, and allows for multiple results to occur for the output UPDATE - Correct Answers· UPDATE STUDENT · SET Email = '' · WHERE StudentID = 1; Updates rows in existing table DELETE - Correct Answers· DELETE FROM STUDENT · WHERE StudentID = 3;