ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam
Go to Exam

Exam 70-761 topic 1 question 100 discussion

Actual exam question from Microsoft's 70-761
Question #: 100
Topic #: 1
[All 70-761 Questions]

SIMULATION -
You create a table named Sales.Categories by running the following Transact-SQL statement:

You add the following data to the table.

You need to create a query that uses a common table expression (CTE) to show the parent category of each category. The query must meet the following requirements:
✑ Return all columns from the Categories table in the order shown.
✑ Exclude all categories that do not have a parent category.
Construct the query using the following guidelines:
✑ Name the expression ParentCategories.
✑ Use PC as the alias for the expression.
✑ Use C as the alias for the Categories table.
Use the AS keyword for all table aliases.

✑ Use individual column names for each column that the query returns.
✑ Do not use a prefix for any column name.
✑ Do not use implicit joins.
Do not surround object names with square brackets.


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 Hide Answer
Suggested Answer: Please see explanation
1 WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS (SELECT c.categoryID,c.name,c.parentcategoryid
2 FROM sales.categories c
3 WHERE parentcategoryid is not null
4 )
5 SELECT * FROM parentcategories
Note: On Line 1 replace c with WITH ParentCategories pc (CategoryID, Name, PatentCategoryID) AS
Note: The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,...n] ) ]

AS -
( CTE_query_definition )
References:
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
by raf77 at Sept. 7, 2019, 1:45 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
raf77
Highly Voted 5 years, 8 months ago
with parentcategories AS (Select c.categoryid, c.name, c.parentcategoryid from sales.categories c where parentcategory is not null ) select * from parentcategories pc
upvoted 16 times
M4x
5 years, 8 months ago
For best pratice avoid use of *. declare @Categories as table ( CategoryId smallint not null primary key, Name nvarchar(50) not null, ParentCategoryID int null ) insert into @Categories values (1, 'Electronics', null), (2, 'Cameras and photo', 1), (3, 'Computers and tables', 1), (4, 'Cell phones and accessories', 1), (5, 'TV and audio', 1), (6, 'Digital Cameras', 2), (9, 'Laptops', 3), (13, 'Household goods', null), (14, 'Bathroom items', 13), (15, 'Shower curtains', 14) ; with ParentCategories (CategoryId, Name, ParentCategoryId) as ( SELECT c.categoryID,c.name,c.parentcategoryid FROM @Categories c WHERE parentcategoryid is not null ) select CategoryId, Name, ParentCategoryId from ParentCategories pc
upvoted 4 times
imran
5 years, 6 months ago
WHERE IS THE NAME OF THE PARENT CATEGORY YOU SHOULD JOIN PC CTE WITH TABLE TO GET THAT
upvoted 4 times
...
imran
5 years, 6 months ago
THIS MIGHT BE CORRECT CREATE TABLE CATEGORIES ( CategoryId smallint not null primary key, Name nvarchar(50) not null, ParentCategoryID int null ) insert into CATEGORIES values (1, 'Electronics', null), (2, 'Cameras and photo', 1), (3, 'Computers and tables', 1), (4, 'Cell phones and accessories', 1), (5, 'TV and audio', 1), (6, 'Digital Cameras', 2), (9, 'Laptops', 3), (13, 'Household goods', null), (14, 'Bathroom items', 13), (15, 'Shower curtains', 14) ; with ParentCategories (CategoryId, Name, ParentCategoryId) as ( SELECT c.categoryID,c.name,c.parentcategoryid FROM CATEGORIES c --WHERE parentcategoryid ) select C.CategoryId, C.Name, C.ParentCategoryID,ParentCategories.Name from CATEGORIES C JOIN ParentCategories ON ParentCategories.CategoryId = C.ParentCategoryID
upvoted 3 times
...
...
Ilray
5 years, 8 months ago
'use the AS keyword for all table aliases' - i think correctly 'AS c', 'AS pc'
upvoted 3 times
...
imran
5 years, 6 months ago
WHERE IS THE NAME OF THE PARENT CATEGORY?
upvoted 1 times
...
...
Vermonster
Most Recent 4 years, 4 months ago
Lots of discussion. Agree with this: With ParentCategories as ( SELECT c.CategoryID, c.Name, c.parentcategoryid FROM sales.categories as c WHERE parentcategoryid is not null -- Exclude all categories not having a parent category ) SELECT pc.CategoryID, pc.Name , --category info pc.parentcategoryid , c.Name --parent info FROM ParentCategories as pc --for each category INNER JOIN Sales.Categories as c -- join to get parent ON pc.Parentcategoryid = c.CategoryID
upvoted 1 times
...
kiri2020
4 years, 7 months ago
create table #Categories (CategoryId smallint not null primary key, Name nvarchar(50) not null, ParentCategoryID int null) insert into #Categories values (1, 'Electronics', null), (2, 'Cameras and photo', 1), (3, 'Computers and tables', 1), (4, 'Cell phones and accessories', 1), (5, 'TV and audio', 1), (6, 'Digital Cameras', 2), (9, 'Laptops', 3), (13, 'Household goods', null), (14, 'Bathroom items', 13), (15, 'Shower curtains', 14) with ParentCategories as (Select CategoryID, Name from #Categories ) select C.CategoryID, C.Name, C.ParentCategoryID, PC.Name from #Categories as C join ParentCategories as PC on C.ParentCategoryID = PC.CategoryID
upvoted 1 times
NhiN
4 years, 4 months ago
Or you can do this way: With ParentCategories as ( SELECT c.CategoryID, c.Name, c.parentcategoryid FROM sales.categories as c WHERE parentcategoryid is not null -- Exclude all categories not having a parent category ) SELECT pc.CategoryID, pc.Name , --category info pc.parentcategoryid , c.Name --parent info FROM ParentCategories as pc --for each category INNER JOIN Sales.Categories as c -- join to get parent ON pc.Parentcategoryid = c.CategoryID
upvoted 1 times
...
...
Aghie
4 years, 9 months ago
DONT WE NEED TO ADD THE NAME OF THE PARENT CATEGORY? DECLARE @CATEGORIES TABLE(CategoryId INT, Name nvarchar(50), ParentCategoryID int) insert into @CATEGORIES values (1, 'Electronics', null), (2, 'Cameras and photo', 1), (3, 'Computers and tables', 1), (4, 'Cell phones and accessories', 1), (5, 'TV and audio', 1), (6, 'Digital Cameras', 2), (9, 'Laptops', 3), (13, 'Household goods', null), (14, 'Bathroom items', 13), (15, 'Shower curtains', 14); WITH ParentCategories (CategoryId, Name) AS(SELECT CategoryId, Name FROM @CATEGORIES) SELECT C.CategoryId, C.Name, PC.CategoryId, PC.Name FROM @CATEGORIES C JOIN ParentCategories PC ON PC.CategoryId = C.ParentCategoryID
upvoted 2 times
...
lilyzhang
4 years, 10 months ago
;WITH ParentCategories AS (select CategoryId, Name, ParentCategoryID FROM @Categories where ParentCategoryID IS NULL UNION ALL SELECT C.CategoryId,c.Name,c.ParentCategoryID FROM @Categories AS C INNER JOIN ParentCategories as PC on c.ParentCategoryID=PC.CategoryID) select * from ParentCategories where ParentCategoryID is not null
upvoted 3 times
...
Anette
4 years, 11 months ago
in thee type of questions there might be many correct answers. I am not sure how is calculated the right answer. I tested all the answers here and there are many of them correct. I agree to go with this: with ParentCategories as ( SELECT c.categoryID,c.name,c.parentcategoryid FROM categories c WHERE c.parentcategoryid is not null ) select * from parentcategories pc
upvoted 2 times
...
jortizhuedo
5 years, 1 month ago
After reviewing it, I think this is the best answer, agreed with moehijawe with ParentCategories as ( select categoryid, name, parentcategoryid from Sales.Categories as c where parentcategoryid is null union all select c.categoryid, c.name, c.parentcategoryid from ParentCategories as pc join Sales.Categories as c on pc.parentcategoryid = c.categoryid ) select categoryid, name, parentcategoryid from Sales.Categories where parentcategoryid is not null Example: http://www.besttechtools.com/articles/article/cte-recursive-query-for-data-hierarchy
upvoted 1 times
...
MML
5 years, 1 month ago
with pc (CategoryID,Name,ParentCategory) as ( select top(10) c1.CategoryID,c1.Name, c2.Name as ParentCategory from categories c1 inner join categories c2 on c1.ParentCategoryId=c2.CategoryID where c1.ParentCategoryId is not null order by c1.CategoryID ) select * from pc
upvoted 1 times
...
jortizhuedo
5 years, 1 month ago
Sorry, last comment is wrong, its implicit and no explicit, so join its permitted, but i think that it is not necessary... I think, its the correct answer: with parentcategories AS (Select c.categoryid, c.name, c.parentcategoryid from sales.categories c where c.parentcategory is not null ) select pc.categoryid, pc.name,pc.parentcategoryid from parentcategories pc
upvoted 1 times
...
jortizhuedo
5 years, 1 month ago
So its more simple: with parentcategories AS (Select c.categoryid, c.name, c.parentcategoryid from sales.categories c where parentcategory is not null ) select pc.categoryid, pc.name,pc.parentcategoryid from parentcategories pc
upvoted 1 times
...
jortizhuedo
5 years, 1 month ago
Its say: ✑ Do not use implicit joins.
upvoted 1 times
...
moehijawe
5 years, 4 months ago
with ParentCategories as ( select categoryid, name, parentcategoryid from Sales.Categories as c where parentcategoryid is null union all select c.categoryid, c.name, c.parentcategoryid from ParentCategories as pc join Sales.Categories as c on (pc.parentcategoryid = c.categoryid) ) select categoryid, name, parentcategoryid from Sales.Categories where parentcategoryid is not null
upvoted 3 times
...
mlourinho
5 years, 6 months ago
;WITH ParentCategories AS ( SELECT CategoryID AS ParentCategoryID, Name AS ParentCategoryName FROM Sales.Categories WHERE ParentCategoryID IS NULL ) SELECT PC.ParentCategoryID, PC.ParentCategoryName, C.CategoryID, C.Name FROM SalesCategories C INNER JOIN ParentCategories PC ON C.ParentCategoryID = PC.ParentCategoryID WHEER C.ParentCategoryID IS NOT NULL
upvoted 2 times
New_user
5 years, 4 months ago
You don't need to use 'IS NULL' and 'IS NOT NULL' statement because it removes the middle stage of groups' hierarchy. Laptops and dig. cameras also have parent category. This code works: with ParentCategories as ( select * from Categories c ) select c.categoryid, c.name, c.parentcategoryid, pc.name AS ParentCategoryName from ParentCategories pc join Categories c on pc.categoryid=c.parentcategoryid ;
upvoted 1 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago