SQL Commands (Part-2)

Before creating a table it is crucial to understand different properties of columns. After all table is collection of different columns. In order to create a column you need a column name, corresponding data type and whether it takes NULL or not. Make sure to be very careful while naming columns. For example if you name your column Emp ID and run a SELECT statement on it, then SQL server interprets ID as an alias of Emp. To Prevent this you could use square brackets [Emp Id]. However, it’s good practice to avoid spaces while naming columns.

Data Types:

1. Numbers  – int, bigint, small int, tiny int
2. Numbers with Decimal – numeric, decimal
3. Strings – char, varchar, nchar, text, varchar
4. Date/ Time – date, datetime, datetimeoffset, smalldatetime, datetime2
5. Money – money
6. Numbers with Floats – float, real

Appropriately defining data types can boost performance. For example using smallint for DepartmentID is lot better than using bigint or int. Departments are not going to grow a whole lot. Therefore, rangebetween 0-255 will do the trick.

More Details: http://msdn.microsoft.com/en-us/library/ms187752.aspx

SELECT *FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE=’decimal’

Change decimal to smallint, real etc to see what happens!

In functions, there are specific string functions which can be used only on specific data types. Char reserves the space while varchar does not. In other words, char is greedy! nvarchar also reserves the space like char but supports uni-code data.

char(500) – Uses 500 characters no matter what.
varchar(500) – Essentially takes up to however much space you need. Unused spaces are discarded. Non uni-code. Supports real English data. If you have Hebrew characters coming in than go with nvarchar. You can do varchar(max) as well.

So what’s the difference between char and nchar? You got it! The cool thing about text data type is, it supports line breaks. Date data type only stores date. Datetime store both date and time together. You can find more information on data types here. Things like binary strings are not used in Data Warehouse as it is difficult to retrieve them using queries.

Identity Column
Also known as a surrogate key or incremental column. For example you may not have a real employee id in your company and would like to create employee ids in database level. This serves primarily for joining tables. Once created you can specify start and incremental values. There’s no need to input any data. It automatically generate new ids. The way you will do so is simply this way –  [EmpId] [int] identity(1,1). There’s no need to specify nulls.

Create table [dbo].[Employee](
[EmpID] [int] identity(1,1),
[Name] varchar(20),
[Salary] [int]

)

Insert into [dbo].[Employee](EmpID, Name, Salary)
values (1,’Laura’, 50000)

This will throw in the following error. To prevent all this, simply don’t use EmpID and 1. 

Insert into [dbo].[Employee]( Name, Salary)
values (‘Laura’, 50000), (‘Mary’,80000), (‘Joe’,100000)

Select *from [dbo].[Employee]

Also, take a look at first snapshot. How do you know  EmpID has auto incremental going on? The columns don’t show golden colored key or anything for indication. To find out just right lick the EmpID column and click properties. Alternatively, if you use scripts to create table or insert values you can notice the identity column nature. If you delete old data and insert values again than it will start from 4,5,6 for  EmpID. If you truncate the table the identity starts from 1 and increases by 1. Delete does not reset the identity. However, in real life scenario, you can’t simply truncate.

What if you’d like to change the starting number in your identity column?

DBCC CHECKIDENT (‘dbo.employee’, Reseed,10)
Insert into [dbo].[Employee](Name, Salary) values (‘L’, 50000), (‘M’,80000), (‘J’,10000)
SELECT *from [dbo].[Employee]

Notice it starts from 11, because there’s already an existing row!

CREATING TABLES

It’s really easy to do all this things. SQL Server automatically generates scripts for doing so. You can right click a table, script table as and use create button to come up with the structure for creating tables.

Create table [dbo].[Employee](
[EmpID] [smallint] identity(1,1),
[Name] varchar(20) not null
[Salary] [int])

ALTERING TABLES

ALTER table [Employee]
ADD DeptID int NULL

ALTER table [Employee]
ALTER Column DeptID tinyint

ALTER table [Employee]
DROP Column DeptID

Remember: You can’t change the order of the columns! If you add a column and run a SELECT statement you will see the new column has NULL values. How can you add a column without a null? Follow along.

ALTER table [Employee]
ADD Presence varchar(15) NUll

Update [Employee]
SET Presence = ‘TRUE‘

ALTER table [Employee]
ALTER COLUMN Presence varchar(15) not null

Now compare DeptID with Presence. See the difference? Did we achieve our goal? We did. Can we add different values for each row? Yes we can be using conditions.

Update [Employee]
SET Presence = ‘L names’
Where Name like ‘L%’

 

Subqueries: It is simply a nested query i.e query within a query.

Non Correlated Query: Query Independent of Main Query.

SELECT * FROM dbo.Employee
WHERE EmpID in (
SELECT EmpID from Employee Where Presence = ‘L names’
)

Correlated Subquery: If queries are interdependent for output. This example below comes directly from technet.microsoft.com as it’s much more easier to understand than the one I came up with.

SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID FROM Person.Person AS c
JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
(SELECT Bonus FROM Sales.SalesPerson sp WHERE e.BusinessEntityID = sp.BusinessEntityID) ;

This query retrieves one instance of each employee’s first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables. The first SELECT STATEMENT results in the table below:

Overall,

Missing Values in R (NA)

No matter how meticulous one is in collecting data, missing values often show up. Therefore learning to get around them will surely be helpful. In R, they are  represented by string NA (Not Available) not to confuse with NaN (impossible values such as 1/0). The most important thing to note is any computations involving NA values will result in NA. You can also test your vectors and data frames for missing values using is.na() function. Let’s explore!

One of the most important thing in the snapshot is the last syntax. You know how to subset vectors right. So what we are doing here is subsetting non NA values by testing. We will later explore one additional way to do so. If you were to list NA values at the end of a vector, how would you do so using sort() function?

Also, if you want to find out mean, median, quantile etc for your data set containing missing values then you have to specify additional argument. For example mean(x) returns NA only. Whereas specifying na.rm=T excludes the missing values and returns the mean of the rest. Now let’s explore the first eight observations of air quality data set in R. If you look at your table, you can see three NA values in two different rows. Here’s the question. How would you get rid of all the observations with NAs in it?
We use complete.cases() function, which returns set of logical vectors for complete cases, to get rid of those missing values. What if you wanted observations that had missing values and not the other way around? You would use y[!good,]. One more way of achieving the result of y[good,] is by using na.omit() function. It deletes the NA values based on row number. Check the output below.

Tips:
1. Always run a summary of your data set. You will know how many NAs you have.
2. table() function ignores NA values.
3. You can also use na.exclude(y) to obtain same result as na.omit(y). Just be careful if you are dealing with residual functions.