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.