DataCamp SQL Functions

 Timestamp Functions in SQL

Higher Precision

  • SYSDATETIME()
  • SYSUTCDATETIME()
  • SYSDATETIMEOFFSET()

Lower Precision

  • GETDATE()
  • GETUTCDATE()
  • CURRENT_TIMESTAMP

Datename Function SYNTAX

SELECT 
first_name,
last_name,
first_vote_date,
    -- Select day of the week from the first vote date
DATENAME(dw,first_vote_date) AS first_vote_dayofweek
FROM voters;

SELECT 
first_name,
last_name,
    -- Extract the month number of the first vote
DATEPART(MONTH,first_vote_date) AS first_vote_month1,
-- Extract the month name of the first vote
    DATENAME(MONTH,first_vote_date) AS first_vote_month2,
-- Extract the weekday number of the first vote
DATEPART(WEEKDAY,first_vote_date) AS first_vote_weekday1,
    -- Extract the weekday name of the first vote
DATENAME(WEEKDAY,first_vote_date) AS first_vote_weekday2
FROM voters;

CREATING DATE FROM PARTS SYNTAX

DATEFROMPARTS FUNCTION 

SELECT 
first_name,
last_name,
    -- Select the year of the first vote
    YEAR(first_vote_date) AS first_vote_year, 
    -- Select the month of the first vote
MONTH(first_vote_date) AS first_vote_month,
    -- Create a date as the start of the month of the first vote
DATEFROMPARTS(YEAR(first_vote_date), MONTH(first_vote_date), 1) AS first_vote_starting_month
FROM voters;









Find and Search Functions

CHARINDEX()

The syntax is: CHARINDEX(expression_to_find, expression_to_search [, start_location])

SELECT 
first_name,
last_name,
email 
FROM voters
-- Look for the "dan" expression in the first_name
WHERE CHARINDEX('dan', first_name) > 0 
    -- Look for last_names that do not contain the letter "z"
AND CHARINDEX('z', last_name) = 0;


2] Function for looking a patter within a string

PATINDEX

PATINDEX('%pattern%', expression)


patternmatch
%any string of zero or more characters
_any single character
[]any single character within the range specified in brackets


SELECT 
first_name,
last_name,
email 
FROM voters
-- Look for first names that contain one of the letters: "x", "w", "q"
WHERE PATINDEX('%[xwq]%',first_name) > 0;

Comments

Post a Comment

Popular posts from this blog

Two - Sample T test

  2 Sample T-Test Suppose that a company has recently updated their website to make it more colorful and inviting. The company wants to know whether the new design is resulting in visitors staying on the site for a longer period of time. A sample of 100 visitors who saw the old design spent an average of 25 minutes on the site. A second sample of 100 visitors who saw the new version spent an average of 28 minutes on the site. Did the average time spent per visitor vary across groups? Or is this difference attributable to random chance? One way of testing whether this difference is significant is by using a  2 Sample T-Test . A 2 Sample T-Test compares two sets of numerical data. The null hypothesis of a 2 Sample T-Test is that the two observed samples come from populations with the same mean. In the example above, this means: if we could observe all site visitors in two alternate universes (one where they see each version of the site), the average visiting times in these unive...
Read more

Web Scraping using JSON and API

JSON–from the web to Python Wow, congrats! You've just queried your first API programmatically in Python and printed the text of the response to the shell. However, as you know, your response is actually a JSON, so you can do one step better and decode the JSON. You can then print the key-value pairs of the resulting dictionary. That's what you're going to do now! Pass the variable  url  to the  requests.get()  function in order to send the relevant request and catch the response, assigning the resultant response message to the variable  r . Apply the  json()  method to the response object  r  and store the resulting dictionary in the variable  json_data . # Import package import   requests # Assign URL to variable: url url  =  'http://www.omdbapi.com/?apikey=72bc447a&t=social+network' # Package the request, send the request and catch the...
Read more

Webscrapping using BeautifulSoup

  Assign the URL of interest to the variable  url . Package the request to the URL, send the request and catch the response with a single function  requests.get() , assigning the response to the variable  r . Use the  text  attribute of the object  r  to return the HTML of the webpage as a string; store the result in a variable  html_doc . Create a BeautifulSoup object  soup  from the resulting HTML using the function  BeautifulSoup() . Use the method  prettify()  on  soup  and assign the result to  pretty_soup . # Import packages import   requests from   bs4   import   BeautifulSoup # Specify url: url url  =  'https://www.python.org/~guido/' # Package the request, send the request and catch the response: r r  =  requests . get ( url ) # Extracts the response as...
Read more