SQL – short for Sequel Query Language is a programming language designed to work with data stored in RDBMS (relational database management systems). The data managed by SQL is tabular, meaning it formatted in rows and columns, very much like an Excel spreadsheet.
When it comes to deleting data from a SQL server, you have 3 main options: Drop, Delete, and Truncate
Drop
The Drop command completely deletes a table from the database. Let’s take the table Employee
When I run the following code: Drop Table Employee;
You can see the entire table has been dropped from the database, data and all. There is no way to recover data from a dropped table, short of having a back up
Delete
Delete removes data from the table, not the table its. Also, Delete can be used in conjunction with a Where clause to choose exactly which data to delete
You see that only the row with ‘Chris’ was deleted
Without the Where clause, all data is deleted, however the table remains
Truncate
Truncate acts just like Delete but you can’t add a Where clause, it simply deletes all data from a table
Let’s use the employee table created on the create table page: Create_table
To insert some data into this table, first you need to know what the data types of each of the columns in the table are. In this example I am using my MySQL. An easy way to see column data types is to Right Click on Table name> Send to SQL Editor > Create Statement
The results will appear in the query window. In this table we can see the columns are a integer (INT), string (varchar), DOB (date)
The syntax for inserting data into the table is as follows
insert into <table name> (column1, column2,...)
values (value1, value2,...)
In the example below, we are loading 3 rows in, separating each row by a comma:
If we run this, we can then check on the results with a Select statement
You might have heard of DDL and DML and been confused. Are they part of SQL or are they their own language? Actually Yes and No…
If you look at it as a purist computer programmer or an academic, then you will probably going to say they are all different languages, you would not be wrong. However for someone like me, I view them as a subset of commands used in the SQL language.
DDL: Data Definition Language is a set of commands used to create, modify, or drop databases, tables, views, indexes, schemas, and users.
DML: Data Manipulation Language is a set of commands used to add data to a table, move data around, read data, update data, or delete data.
A common question I have seen good people get tripped up on. Remember KISS – Keep it Simple Stupid.
Don’t let the question trick you into overthinking. For a SQL query you need a SELECT command (what do we want), a FROM (what table is it in) and if you want to go for the extra credit point, throw in a WHERE (its our filter)
Temporary tables are a great way of working on complex data requests. They are easy to create and they delete themselves after every session, so you do not have to worry about creating a big mess with a bunch of tables you need to go clean up later.
In this tutorial, I am going to use a real world example from my work in Verizon’s Cyber Security Department. This is a simplified version of ask, and I am using completely made up data. There is no data from Verizon on my website every. I simply discuss use cases to make learning analytics more grounded in the real world
Below is a list of dates, PhoneNum: phone numbers called about, and the CallerNum: the number the person is calling from. While there are many legitimate reasons for someone to call customer support from another number (I drop and break my phone so I borrow my co-workers phone and call customer support to request a replacement), a number that calls in repeatedly about many different numbers is a red flag of someone that could be a fraudster.
If you want to play along, you can download the data set here:
I am using MySql in this example as my database, but I will include the code for SQL Server, Teradata, and Oracle platforms as well.
So the ask is find CallerNum that is calling about many different PhoneNum
While I am sure you can make a complex subquery to do this job, but I’m going to show you how to use temporary tables to make this ask very simple:
As you can see above, I loaded the data into a table called dbtest.numberslist
Now to find out how many CallerNum are calling about multiple PhoneNum, a simple solution is to get a list of all distinct combinations of PhoneNum and CallerNum and then do a count of CallerNums from this distinct list. Since the list is distinct, a CallerNum calling in about the same PhoneNum will only appear once, so a CallerNum calling about multiple PhoneNums will appear multiple times.
So using temporary tables, I will create a table that holds the distinct call combinations
MySql (code is create temporary table <table name> then query to fill table
create temporary table distCalls
select distinct phonenum, callerNum from dbtest.numberslist;
Select * from distCalls -- shows what is in the table now
Now, lets see if we can find potential fraud callers, let us do a count of callerNum from the distinct temporary table
As you can see above, there are 4 numbers that have called about 4 distinct phone numbers during this time period. Again, this could be for legitimate reasons, but this is still something we look at when trying to find questionable activity.
Select distinct PhoneNum, CallerNum
into #distCalls
from dbtest.numberslist
go
#tableName -- indicated temporary tables in SQL Server
Teradata
Create volatile table distCalls as (
select distinct PhoneNum, CallerNum
from dbtest.numberslist)
with data
on commit preserve rows;
with data and on commit preserve rows are needed at the end if you want any data to be in your table when you go to use itOracle
Create private temporary table distCalls as
select distinct PhoneNum, CallerNum
from dbtest.numberslist;
Remember, temp tables delete themselves after each session (each time you log off the database). If you are working in the same session and need to recreate the temp table for some reason, you can always drop the table just as you would any other table object in SQL.