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 NULLSELECT 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]=3SELECT * 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.CoolPeopleNotice: 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.