Exam 70-761 topic 1 question 101 discussion

Actual exam question from Microsoft's 70-761

Question #: 101
Topic #: 1

SIMULATION -
You have a database that includes the following tables. All of the tables are in the Production schema.

You need to create a query that returns a list of product names for all products in the Beverages category.
Construct the query using the following guidelines:
✑ Use the first letter of the table name as the table alias.
✑ Use two-part column names.
✑ Do not surround object names with square brackets.
✑ Do not use implicit joins.
✑ Do not use variables.
✑ Use single quotes to surround literal values.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position. You may check syntax as many times as needed.

Show Suggested Answer

Suggested Answer: Please see explanation
1 SELECT p.productname
2 FROM Production.categories AS c
3 inner join production.products as p on c.categoryid=p.categoryid
4 WHERE c.categoryname = 'Beverages'
Note: On line 3 change * to =

by FranckSYLLA at March 9, 2020, 9:04 p.m.

Comments

Submit Cancel

BabyBee

4 years, 5 months ago

DROP TABLE IF EXISTS #Categories CREATE TABLE #Categories (CategoryID tinyint Primary KEY, CategoryName varchar(50), Descripcion varchar(max)); INSERT INTO #Categories VALUES (1, 'Dairy', 'abcdefg'), (2, 'Vegetables', 'Fresh veggies'), (3, 'Beverages', 'alcoholic included'); DROP TABLE IF EXISTS #Products; CREATE TABLE #Products (ProductID tinyint PRIMARY KEY, ProductName varchar(50), supplierID Int, CategoryID TinyInt --CONSTRAINT ProdCatToCategory FOREIGN KEY REFERENCES #Categories(CategoryID) ); INSERT INTO #Products VALUES (10, 'Milk', 100, 1) ,(20, 'Celery', 200, 2) ,(30, 'Water', 100, 3) ,(40, 'Soda', 100, 3) ,(50, 'Yogurt', 300,1); SELECT * FROM #Categories; SELECT * FROM #Products; SELECT ProductName FROM #Products WHERE CategoryID = (SELECT CategoryID FROM #Categories WHERE CategoryName = 'Beverages');

upvoted 1 times

...

Stephan1010

4 years, 6 months ago

Wrong ✑ Do not use implicit joins. Inner join is an implicit joins

upvoted 1 times

KosteK

4 years, 5 months ago

Explicit join: SELECT x.col1, y.col2 FROM x JOIN y ON x.id = y.x_id Implicit join: SELECT x.col1, y.col2 FROM x, y WHERE x.id = y.x_id

upvoted 5 times

...

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 101 discussion

Comments

BabyBee

Stephan1010

KosteK

SY0-701