Question

QUERY EXAMPLE2 SELECTfrom Employee em (Refer to query example 2 to answer questions 12- 13) 12. You plan to join the Location table and fear there may be some employees with no location. You want to make sure that the query returns a list of all employee What join clause would you add to the query above? records. A. LEFT JOIN Location lo ON em.LocationlD lo LocationID B. RIGHT JOIN Location lo ON em.LocationID lo.LocationlD C. INNER JOIN Location lo ON em. LocationID lo.LocationID D. FULL JOIN Location lo ON em LocationlD lo.Location D 13. You want to find all first names that have the letter A as the second letter and do not end with the letter Y. Which SQL code would you use? A. SELECT . FROM Employee wHERE FirstName LIKE-A% . AND FirstName NOT LIKE 'Y%. B. SELECT FROM Employee wHERE FirstName LIKE-A% . AND FirstName NOT LIKE '%Y' C. SELECT * FROM Employee WHERE FirstName LIKE 'A,4, AND FirstName NOT LIKE 'Y% D. SELECT * FROM Employee WHERE FirstName LIKE 'A- AND FirstName NOT LIKE-Y%. 14. Full outer join is a combination of A. Left outer and left inner join B. Left outer and right inner join C. left outer and right inner join D. left outer and right outer join 15. If you define a column as an identity column A. a number is generated for that column whenever a row is added to the table B. you must provide a unique numeric value for that column whenever a row is added to the table C. you cannot use the column as a primary key column D. you must also define the column with a default value 16. Which one of the following sorts rows in SQL? A. SORT BY B. ALIGN BY C. ORDER BY D. GROUP BY

Answer 1

12.

A. LEFT JOIN Location lo ON em.LocationID = lo.LocationID

//this query returns all the records in the left table i.e., employees table and matched records from right table if there are no values for particular employee location the result is null. All the employee records are returned along with locations.

13.

B. SELECT * FROM Employee WHERE FirstName LIKE '_A%' AND FirstName NOT LIKE '%Y'

//this query, returns all the records with firstname having second letter A and doesn't have Y as last letter.

14

D. left outer and right outer join

//Full outer join is a combination of left and right combination as it returns all the records which matches either from left and right tables.

15.

A. a number is generated for that column whenever a row is added to the table.

//identity column assigns unique number whenever each row is added usually starts with 0 or 1 and increments by 1 for each new row addition. It is much like autonumber.

16.

C. ORDER BY

//order by sorts the rows in by the followed column name

//any issue with the answer, post in the comment section