SQL commands (Part 1)

Schema

What is Schema?  Schema is partition of a table. In Dbo.employee (table name is employee) and DBO is schema. What is schema in -HumanResource.Dept and HumanResource.Employee? HumanResource.HumanResource schema contains related tables. If schema is not specified then, dbo is set by default. In other words,  Select *from dbo.a  is same as Select *from a . However this is not true for HumanResource schema.

It is always a good idea to create schema first. You need to right click somewhere and create new schema. Find it! It needs to be created manually. Right click security tab inside Test database. You should be able to do after this.

SQL commands

Click on ‘New Query’ and Select Right database ‘Adventureworks2008R2‘ instead of Test as shown below.

In the window, type in commands as shown below and understand the output. If this is too advance make sure to go through tutorials available at w3schools. Here we go.

SELECT *FROM HumanResources.Shift

Let’s quickly describe following terms:

Keys	Description
Primary Key	Won’t allow duplicates and uniquely identifies a row. No nulls. Creates Clustered Index.
Composite Key	Combinations of two or more columns in order to identify a row uniquely.
Foreign Key	Points points toward Primary Key in another table. Grey in color.
Unique Key	Similar to Primary Key but allows one null value. Prevents duplicate values in column. Creates non clustered index.

There are other keys like candidate, alternate etc. Refer to the diagram below.

Keep in Mind: In  SSIS, you need to load parent table first before loading the child table. Child table is dependent on parent table. A lot of times remembering a fact like this will do good.

SELECT STATEMENTS

SELECT TOP 100 * FROM Sales.SalesPerson
SELECT PostalCode FROM Person.[Address]
SELECT Distinct PostalCode FROM Person.[Address]
SELECT Top 100* FROM Person.[Address]Where StateProvinceID=79
SELECT *FROM Person.StateProvince ORDER BY Name DESC
SELECT Top 100* FROM Person.[Address]Where StateProvinceID=79 AND AddressLine2 IS NOT NULL

SELECT Top 100* FROM Person.[Address]Where StateProvinceID=81 Or AddressLine2 IS NOT NULL ORDER BY City DESC

SELECT * FROM Person.[Address]Where StateProvinceID LIKE ‘%9’ or City like ‘%bo%’

SELECT * FROM Person.[Address] Where City LIKE ‘%Lake%’
UNION All
Select * FROM Person.[Address] Where City LIKE ‘%CAl%’

Remember: Any number of Union All is possible.

SELECT AddressID FROM Person.[Address] Where City LIKE ‘%Lake%’
UNION
SELECT AddressID FROM Person.[Address] Where City LIKE ‘%CAl%’

Remember: Union sorts the data whereas Union All does not. Union is costly operation! UNION ALL REQUIRES same number of columns AND COLUMN names should match.

SELECT * FROM Person.[AddressType]

EXCEPT
Select * FROM Person.[AddressType] Where [AddressTypeID]=3

SELECT * FROM Person.[AddressType]
INTERSECT
Select * FROM Person.[AddressType] Where [AddressTypeID]=3

INSERT STATEMENTS

Run this in sequence.

CREATE TABLE [Person].[CoolPeople](
[ContactTypeID] [int] NOT NULL,
[Name] [dbo].[Name])

SELECT * FROM Person.CoolPeople
INSERT INTO Person.CoolPeople (ContactTypeID,Name)VALUES (‘1′,’Sam’)

SELECT * FROM Person.CoolPeople

INSERT INTO Person.CoolPeople SELECT * FROM Person.CoolPeople
SELECT * FROM Person.CoolPeople

Notice: Two ways of using INSERT statements.

DELETE, DROP, TRUNCATE STATEMENTS:

DELETE [Person].[CoolPeople]

INSERT INTO Person.[CoolPeople] (ContactTypeID,Name)
VALUES (1,’Sam’),(2,’Jack’),(2, ‘Flora’)

DELETE [Person].[CoolPeople]Where ContactTypeID =2

Truncate table [Person].[CoolPeople]
DROP table [Person].[CoolPeople]

Differences: Truncate  removes all rows from table and does not use ‘where’ clause. It acts on object level and we cannot do a rollback for truncate. It is faster than delete command. Delete on the other hand simply deletes the rows specified. Drop removes the entire table from database. Operations can’t be rolled back.

Subsetting in R (An Alternative to SQL)

If you are familiar with  Structure Query Language(SQL), you could subset a table by using the WHERE clause. In R, there are three main ways of achieving this.

1. Method of Indices a.k.a [ ]
2. Conditional Statements
3. Using subset() function

Let’s first look at method of indices. I’ve created a matrix of 5 observations and 4 variables. The values come from a normal distribution of 30 observations with mean of 1 and standard deviation of 0.5 using z<-matrix(rnorm(20,1,.5),5,4). Your numbers will be different than mine.

We will make this post FAQ type so that it’s easier to relate to the questions asked. First I ask you to convert the matrix to a data frame and call it zfram. You should be able to do it using zfram<-as.data.frame(z).

1. From the data frame above, how would you extract data for a) first two days  b)  first 3 observations c) first observation for Day1 and Day2  d) Day 1,3,5 and observation 2,3 

Tip: If you want to exclude Day1, Day5  from your dataframe you could do something like zfram[c(-1,-5),]. Now that you get a basic idea of subsetting using [ ] approach, let’s look at other ways of subsetting. Let’s create a data set using employee<-edit(data.frame()) functions as shown below (a slightly different way).

Usually this is the structure of a table in your database and it will be an excellent exercise to get a feel for data management in R and compare it to SQL. Here you go!

1. The government is changing it’s policy and now have created different tax brackets for different gender. In my company I want  records of those people who are earning more than 45000 and is Male. How can you achieve this in R. Ans: ma<-employee[which(employee$Gender==’M’ & employee$Salary>45000),]


R is case sensitive unlike SQL. Btw, in order to achieve the same in SQL you would write SELECT *from Employee Where gender=’M’ and Salary>45000.  Also, pay attention to the comma at the end of R syntax.

2.  I’d like records of those people who aren’t analysts in the company? How will you achieve this in R? Ans: employee[which(employee$Position !=’Analyst’),]

What if you didn’t want Analyst and SME in your records? Ans: employee[which(employee$Position != c(‘Analyst’,’SME’)) ,]. In SQL, the syntax for getting the first output would be SELECT *FROM employee Where Position not in (‘Analyst’) and second would be SELECT *FROM employee Where Position not in (‘Analyst’,’SME’). We have covered method of indices and conditional statements so far. Let’s look in to subset() function. First type ?subset to understand it’s arguments.

1. Using subset() function, achieve the output to the first question asked earlier. Is it the same ?
Ans: ma<-subset(employee,Gender==’M’ & Salary>45000). Output’s the same. 

2. Using subset() function, achieve the first part of the second question.
Ans: subset(employee, Position !=’Analyst’)

3. I need  a) only the names and department of those people who have their salary greater than or equal to 38000 or less than 90000. b) only the names,department and salary of those people who have their salary greater than or equal to 38000 or less than 90000. Use subset() function.

If you were with me and came this far then you should be able to apply subsetting techniques to your data frames, vectors and matrices. Also, you can store the result of your output to a variable and do further analysis/modification. For example if you wanted a column called TaxRate for ” I want records of those people who are earning more than 45000 and is Male” question then you would do the following.
 Good luck!