Category: sql

SQL: What is SQL and its 5 Subgroups DQL, DML, TCL, DDL, DCL?

On By Ben Larson Ph.D.In MS SQL Server, sqlLeave a comment

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.

SQL is broken down into 5 sets of command groups:

DQL = Data Query Language

Querying (SELECT, FROM, etc)

DML = Data Manipulation Language

Data manipulation (INSERT, DELETE, etc)

TCL = Transaction Control Language

Transaction mgt. (COMMIT, ROLLBACK, etc)

DDL = Data Definition Language

Data definition (CREATE, DROP, etc)

DCL = Data Control Language

Data control (GRANT, REVOKE, etc)

SQL: Drop, Delete, Truncate commands

On By Ben Larson Ph.D.In Analytics, databases, sqlLeave a comment

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

SQL: Load (Insert) data into a table

On By Ben Larson Ph.D.In Analytics, data modeling, sqlLeave a comment

Here are the steps to add data to existing 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

SQL: Create a Table

On By Ben Larson Ph.D.In Analytics, databases, sql1 Comment

Create a table in MySql and Sql Server

Create table <table name> (
           <column 1> data type,
           <column 2> data type )

As an example, let’s create an employee table with 3 columns, ID, Name, and Date of Birth

create table Employee(
   Id int,
  Emp_NM varchar(255),
  DOB date);

** remember that unless you set your database to be case sensitive, most SQL databases are not

You may need to refresh your database explore view, but after running this command, you should now have a new table

If you need a reference table for the types of data types available, check out this page: data types

SQL: What is DDL and DML?

On By Ben Larson Ph.D.In sql1 Comment

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.

SQL: Common Data Types in MySQL

On By Ben Larson Ph.D.In sql1 Comment

Here is a table of the most commonly used data types in MySQL

Data TypeDescription
Char()Fixed length string, unused spaces get padded and eat up memory: size 0-255
Varchar()Variable length string, unused spaces don’t use memory: size 0 to 65535
MediumText()A string up to 16,777,215 characters long
LongText()A string up to 4,294,967,295 characters long
INTinteger (whole number, no decimals)
Double(x, d)floating point decimal number, x is size, d is number of places after the decimal
Bool or BooleanBinary choice, 0 = False and 1 = True
DateDate data type “YYYY-MM-DD” (if set to US settings)
DATETIMEdatetime data type “YYYY-MM-DD HH:MM:SS” (if set to US settings)
TIMETime “HH:MM:SS”
YEARyear in for digit representation (ex 1908,1965,2011)

SQL: Add, rename, or delete a column in an existing table

On By Ben Larson Ph.D.In Analytics, MS SQL Server, sqlLeave a comment

To add a column here is the syntax

alter table <table name>
add <column name> <datatype>;

example: (add a column named location to a table emp_info)

alter table emp_info
add location varchar(255);

To rename a column in sql, here is the syntax

alter table <table name>
rename column <old name> to <new name>;

example: (change name of column location to office) 

alter table emp_info
rename column location to office;

To delete or remove a column, use the drop command

alter table <table name>
drop column <column name>;

Example:

alter table emp_info
drop column office;

Return to SQL page:

Data Science Interview Questions: Parts of a SQL Query

On By Ben Larson Ph.D.In Analytics, Careers in Data, sql1 Comment

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)

Return to questions page

SQL: Create a temporary table

On By Ben Larson Ph.D.In Analytics, Languages, MS SQL Server, sqlLeave a comment

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:

numberslistDownload

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.

MySQL Code

create temporary table distCalls select distinct phonenum, callerNum

from dbtest.numberslist;

SQL Server

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 it

Oracle

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.