let's write some queries

0

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla

Q&A for Joomla! administrators, users, developers and designers

/*
On the right hand side of the window there's a box 
 It says "Database Schema" 
 You can click the names of the tables (like Posts) to collapse them
 You can click show sidebar (lower right) to collapse the entire thing
 When you run a query, it may ask for a captcha
 If you return multiple results, it will make you click the Results box to show them 
 

Don't worry if you dont get it exactly or cant remember the exact syntax.
There are often multiple correct answers depending on a schema.
Talk through your thought process when you're confused.
You're not expected to finish.
When we talk about users, generally we are talking about Post Owners.

Tell us about your experience with database objects, how you use data - 
and where your comfort zone is.

*/

-- If you normally write queries, let's work up to where you get murky. 

--- return 1 row from Posts
--select top 1 * from Posts

--- return the 1 row from VoteTypes with the biggest Id

--select top 1 * from VoteTypes ORDER BY ID DESC

-- return the count of all the Votes that where created in 2019

--select COUNT(ID) from Votes where CreationDate BETWEEN '1-1-2019' AND '12-31-2019'

-- sum the number of upvotes and downvotes against users in SO 
-- by locations that are not blank
-- who have at least 5 upvotes and 5 downvotes
-- ordered by the number of upvotes

--select * from VoteTypes

/*select Location, SUM(UpVotes), SUM(DownVotes) from Users
WHERE 
  Location <> ''
AND 
  Upvotes > 4 AND DownVotes > 4
GROUP BY Location
ORDER BY SUM(UpVotes) DESC
*/

-- Do you normally create tables in your work? 
--  If you do 
--    What impact does a type have in creating a table? 
--    What's a temp table? Where's it stored?
--    What happens when you write data to a table? As detailed as you understand.

-- create a temp table named post_count
-- it needs to store the count of posts by user
-- return the first row from the temp table


--select OwnerUserId, COUNT(ID) AS 'Id' INTO #post_count 
--FROM Posts
--GROUP BY OwnerUserID

--select top 1 * from #post_count


-- create a comma separated string of the 5 users display names 
-- with the most downvotes

--select TOP 5 DisplayName from Users
--ORDER BY DownVotes DESC

declare @result varchar(500)
set @result = ''
select top 5 @result = @result + DisplayName + ', ' 
from Users ORDER BY DownVotes DESC

select @result

-- Do you normally create indexes in your work? 
--  If you do -
--    What's a clustered index?
--    Does the order of index columns matter? Explain your answer.
--    What does the INCLUDE keyword do?

-- suggest possible indexes for the following query
-- SO will not let you make any, so dont worry 

-- What level of heirarchy for posts is supported via ParentID in SO? 
-- Prove it. 


-- What things traditionally found in a database seem to be missing from this one?
-- What improvements would/could you suggest in this current database design/arch?

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results