Week 8 Weekly thoughts

SQL Syntax from https://www.w3schools.com/

qryShow_Paid_Posts

SELECT Lifestream_CH_Posts.PostTitle + ‘, ‘ + Lifestream_CH_Posts.PostSubject + ‘, Week ‘ + Lifestream_CH_Posts.intWeek AS [Listing], Lifestream_CH_Posts.PostTitle AS [Post Title],
Lifestream_CHPosts.Postbody AS [Post]
COALESCE(CONVERT(nvarchar(12), Lifestream_CH_Posts.PostDate,113), N”) AS [Date of Post],
Lifestream_CH_Viewers.ViewerType AS [Viewer Type],
Lifestream_CH_Payments.PaymentRcvd AS [Payment Received],
Lifestream_CH_Validations.ViewerValue AS [Viewer Validation]
FROM Lifestream_CH_Viewers INNER JOIN
Lifestream_CH_Posts INNER JOIN
Lifestream_CH_Payments INNER JOIN
ON LIfestream_CH_Validations
ON Lifestream_CH_Posts.PostID = Lifestream_CH_Payments.PostID
ON Lifestream_CH_Viewers.ViewerID = Lifestream_CH_Payments.ViewerID
ON Lifestream_CH_Validations.ViewerCode = Lifestream_CH_Viewers.ViewerID
WHERE (Lifestream_CH_Viewers.ViewerType IN (‘A2‘, ‘A3‘, ‘B3‘, ‘B7‘, ‘D17‘, ‘L23‘, ‘L30‘, ‘M25‘, ‘S7′, ‘S14‘, ‘T9‘))
AND (Posts.PostTitle IN  (‘On and Off, ‘Privacy Paradox’, ‘Algo Chat, ‘Acceptance Creep’, ‘Not so favourite‘, ‘Start the week‘))
AND (Lifestream_CH_Payments.PaymentRcvd=True)
AND (Max(Lifestream_CH_Validations.ViewerValue) > 600)

3 thoughts on “Week 8 Weekly thoughts

  1. jknox

    Well, this is possible the most confusing weekly summary, or blog post, I’ve ever had to comment on! I suppose that’s what I get for tasking people to think about algorithms, and all their inscrutability. With a bit of digging, I can see links to your activity this week – and good posts they are too! I’ll get round to commenting on some these shortly.

    So, very clever summary really, and one that definitely makes me feel like I’m somewhat entangled in the algorithmic culture of this block!

    Reply
  2. chills Post author

    Hi Jeremy
    I’m sorry, my summary this week was a clumsy attempt to make a few points about algorithms, but I don’t think I executed it very well and should have explained it better.

    What I was trying to show was a database query for a fictitious CH Lifestream database, asking it to display posts which matched certain criteria. It was ‘written’ in SQL or structured query language.

    Queries are based upon key commands:
    SELECT – list the stuff you want to see in the results of the query.
    FROM – say which tables in the database the stuff comes from.
    WHERE – set the criteria to filter the stuff so it shows what you want.

    In my SELECT statement I included fields such as Post Title, Post Date to display a list of my lifestream posts.
    In my FROM statement I listed the fictitious tables the fields came from and their relationships to each other.
    In my WHERE clause I listed the criteria.

    The points I was trying to make were mainly situated in the WHERE clause:

    Only show the posts called ‘On and Off’, ‘Privacy Paradox’ etc
    (I listed the posts I wanted you to see, implying I had control over what to include or exclude, and to a certain extent which ones to prioritise by the order of the list.)
    Only show the posts if the Viewer is of type (‘A2‘, ‘A3‘, ‘B3‘, ‘B7‘, ‘D17‘, ‘L23‘, ‘L30‘, ‘M25‘, ‘S7′, ‘S14‘, ‘T9‘)
    (I made the Viewer Type list inscrutable to mirror the obscure-to-us categories we are placed in by software and platforms.)
    Only show the posts if the Viewer had paid up!
    (Attempt at a joke.)
    Only show the posts if the Viewer had earned enough validations.

    Other points I failed to make were:
    The complicated relationships between the tables in the FROM clause. In a real database for a blogging platform which captured data about viewers or linked to other databases which held viewer information, these would be much more complicated. Tables are joined together contingently in a query and different types of joins can be used. The join type will determine, for example, which ‘segments’ of a venn diagram are returned in the results. It is possible to use the wrong join and, without careful examination of the results set, think everything is ok.

    The image displayed at the top of my post was a screenshot from an SQL syntax site to show how ‘constraints’ can be encoded into a database so that it accepts only specific values or values in a certain format thereby imposing a particular interpretation of the data it will store.

    Finally, the SQL is made-up and any proper database administrator would shudder in disgust at it – I just did it to make the points above. Finally, finally, I wanted to suggest how easy it is to make coding errors or leave out an item in a list and how small, unanticipated decisions in the design stage of a system must be made by coders on the fly which have undocumented effects on the system.

    Reply
  3. jknox

    ‘What I was trying to show was a database query for a fictitious CH Lifestream database, asking it to display posts which matched certain criteria. It was ‘written’ in SQL or structured query language.’

    Ah, now it makes a lot more sense 🙂

    ‘I listed the posts I wanted you to see, implying I had control over what to include or exclude, and to a certain extent which ones to prioritise by the order of the list.’

    I see, very clever, and good comment on control.

    ‘I made the Viewer Type list inscrutable to mirror the obscure-to-us categories we are placed in by software and platforms.’

    Fantastic, like it!

    ‘Only show the posts if the Viewer had paid up!’

    Yes, I did see that and was wondering what it was all about! Perhaps a goof comment on analytics and its association with finance?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *