Use SQL Like a Badass – Part 4/4

This final blog of the ‘SQL Like a Badass’ will round off with the DELETE command and VIEWs. The last section on Views can be powerful for business analytics, in that you can create virtual tables that can be queried when lots of data manipulation is required.

6. DELETE

If you want to remove certain rows from your table, you use the DELETE command.

One item to be careful of when using DELETE is that it needs to have a WHERE statement to delete selected data. If you omit the WHERE statement, you will delete your entire table.

In our Chuck Norris example, if we want to delete rows with an ID of more than 3 we could execute the below.

idquotecomment_qtycomment_rangeauthor_id
1When Chuck Norris does a pushup, he isn't lifting himself up, he's pushing the Earth down.4L1
2Chuck Norris' facial hair is known to cut diamonds.3L1
3Chuck Norris once robbed a gun store with a knife.2L4

Note that if you want to delete the entire table, you can use the DROP command. We can add a little code at the start to check if the table exists. If we do not add the IF statement at the start of the SQL code and the table has already been dropped, then an error will be returned.

7. VIEWS

Views are virtual tables we create from our real data set. Views are used in databases for two main reasons:

  • To hide database complexity – if you have a lot of data manipulation that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would for a regular table.
  • As a security mechanism – database administrators can limit what you see through a view. When you get access to your company’s  data, you may be given a view rather than access to the raw data.

For business analytics we normally use views for the first reason. Views are commonly used in a script for cleaning data where you do not want to write a whole lot of temporary tables for intermediary steps. Once all the steps have been executed in the views, you can write the final cleaned data into a single table, thereby avoiding clutter and mistaking the wrong table to use for further analysis. As a side note, a script is good practice when you do multiple steps for data manipulation in databases in that you create a repeatable process and the script captures the methodology you took for the analytics.

In our Chuck Norris example, we create a view where we replace ‘Chuck Norris’ with ‘Bruce Lee.’ Here we will make use of the REPLACE function, which replaces one string with another in the syntax:

REPLACE (column, ‘string_original’,’string_new’) new_column_name. Also note that you can have column names in square brackets. The square brackets are not compulsory in this case, but are required when there are spaces in column names e.g ‘comment qty.’

You can then select this view, just as if you were selecting a table.

idquote_replacecomment_qtycomment_rangeauthor_id
1When Bruce Lee does a pushup, he isn't lifting himself up, he's pushing the Earth down.4L1
2Bruce Lee' facial hair is known to cut diamonds.3L1
3Bruce Lee once robbed a gun store with a knife.2L4
4Bruce Lee abducts aliens.15H5
5Bruce Lee once fought superman. The loser had to wear his underwear over his pants.7M1
6Bruce Lee can make a happy meal cry.13H3
7Bruce Lee counted to infinity. Twice.8M3
8In space Bruce Lee can hear you scream.0L4
9Bruce Lee is the reason Waldo is hiding.11H2
10M.C. Hammer learned the hard way that Bruce Lee can touch this.3L2
11The Incredible Hulk is Green because he envies Bruce Lee.8M99

If there are more data cleaning steps, you then write another view based off the ‘v_chuckreplace’. For instance if we wanted to add the length of each quote in characters we could make a new view:

idquote_replacecomment_qtycomment_rangeauthor_idquote_length
1When Bruce Lee does a pushup, he isn't lifting himself up, he's pushing the Earth down.4L187
2Bruce Lee' facial hair is known to cut diamonds.3L148
3Bruce Lee once robbed a gun store with a knife.2L447
4Bruce Lee abducts aliens.15H525
5Bruce Lee once fought superman. The loser had to wear his underwear over his pants.7M183
6Bruce Lee can make a happy meal cry.13H336
7Bruce Lee counted to infinity. Twice.8M337
8In space Bruce Lee can hear you scream.0L439
9Bruce Lee is the reason Waldo is hiding.11H240
10M.C. Hammer learned the hard way that Bruce Lee can touch this.3L263
11The Incredible Hulk is Green because he envies Bruce Lee.8M9957

As one final exercise, we could put all views together in a script. Firstly we add which database we will use at the top of the script, in this case the database is called ‘ChuckNorris.’ We then add in some logic to drop views and recreate them each time we run the script, making the script repeatable. You may notice ‘GO’ stataments below. SQL needs to use ‘GO’  to group SQL commands into batches which are sent to the server together. Lastly, you will see that you can add comments inside the notation /* */ which helps to document each of the steps.

Summary

Well done for getting through all 4 SQL for business analytics posts, you are now a SQL Badass.We now have a solid understanding of the most useful SQL commands across our seven topics.

  1. Select – used to read data
  2. Alter – used to add or drop a column
  3. Update – used to change existing data
  4. Insert – used to add new data
  5. Joins – used to combine tables
  6. Delete – used to delete data
  7. Views – a virtual table which is the result of a query (this is useful when you do not want to write new tables)

 

Share on LinkedInEmail this to someoneTweet about this on TwitterShare on FacebookPrint this page

One Reply to “Use SQL Like a Badass – Part 4/4”

Leave a Reply

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

*