JOINS

JOINS

--============================================[JOINS]========================================================================

--JOIN clause is used whenever we have to select data from 2 or more tables.

--Our two table are below---

SELECT * FROM Person.Contact [19972 rows]

Select * From Sales.Individual [18484 rows]

--Joining without using JOIN Clause (Only Matched)[18484 rows]---------

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact,Sales.Individual

WHERE Person.Contact.ContactID = Sales.Individual.ContactID

--JOIN (By default it will be INNER JOIN)(Only Matched)[18484 rows]

--The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on.

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID

--LEFT OUTER JOIN (simply LEFT JOIN) (19972 rows)---

-- selects all the rows from the first table listed after the FROM clause, no matter if

--they have matches in the second table.

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact LEFT OUTER JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID

--RIGHT OUTER JOIN (simply RIGHT JOIN) (18484 rows)

-- selects all the rows from the second table listed after the FROM clause

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact RIGHT OUTER JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID

HAVING

HAVING

--===========================================[HAVING]=======================================================================

--HAVING clause is used to restrict conditionally the output of a SQL statement,

--by a SQL aggregate function used in your SELECT list of columns.

--This will throw Error because we can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used

SELECT Title, SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

Where UM(EmailPromotion)> 100

Group By Title

---Having------

SELECT Title, SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

Group By Title

HAVING SUM(EmailPromotion)> 100

GROUP BY

GROUP BY

--==========================================[GROUP BY]======================================================================

--GROUP BY statement is used along with the SQL aggregate functions like SUM to provide

--means of grouping the result dataset by certain database table column(s).

--Without Group By----

SELECT SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

--With Group By But No Correspoding Grouping----

SELECT SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

Group By Title

---With Group By and With additional column of the Group(Title)----

SELECT Title, SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

Group By Title

Average & Sum

AVG & SUM

--==========================================[AVG]===========================================================================

--AVG aggregate function selects the average value for certain table column.

--Avg---------

SELECT AVG(EmailPromotion) AS AvgEmailPromotion

FROM Person.Contact

--==========================================[SUM]===========================================================================

--SUM aggregate function allows selecting the total for a numeric column.

---Sum--------

SELECT SUM(EmailPromotion)

FROM Person.Contact

MAX & MIN

MAX & MIN

--==========================================[MAX and MIN]===================================================================

-- MAX aggregate function allows us to select the highest (maximum) value for a certain column.

--Max---------

SELECT MAX(EmailPromotion) AS MaxEmailPromo

FROM Person.Contact

--Min---------

SELECT MIN(EmailPromotion) AS MinEmailPromo

FROM Person.Contact

COUNT

COUNT

--========================================== [COUNT]=======================================================================

--COUNT aggregate function is used to count the number of rows in a database table.

--Count--

SELECT COUNT(LastName) AS NumberOfContacts

FROM Person.Contact

OR & AND

OR & AND

--============================================[OR and AND]=================================================================

--AND clause is used when you want to specify more than one condition in your SQL WHERE clause

--and at the same time you want all conditions to be true.

---AND----

SELECT * FROM Person.Contact

WHERE LastName = 'Achong'

AND

ContactID = '1'

---OR-----

--Using OR clause, we can display all the results which satisfies either of the condition

SELECT * FROM Person.Contact

WHERE LastName = 'Achong'

OR

ContactID <= '10'

--- AND + OR ------

--You can combine AND and OR clauses anyway you want and you can use parentheses to define your logical expressions.

SELECT * FROM Person.Contact

WHERE (FirstName = 'Gustavo' OR FirstName = 'Catherine') AND LastName = 'Achong'

ORDER BY

ORDER BY

--=============================================[ORDER BY]==================================================================

--Order By is used to sort SQL result sets by some columns

--Order By (default Accending)

SELECT * FROM Person.Contact

ORDER BY ContactID

--Order By (Decending order)

SELECT * FROM Person.Contact

ORDER BY ContactID DESC

--Order By (Ascending order)

SELECT * FROM Person.Contact

ORDER BY ContactID ASC

DELETE

DELETE

--=============================================[DELETE]==================================================================

--Delete statement is used to remove data from the table

--Delete specific data from a table

DELETE FROM Person.Contact

WHERE FirstName = 'Habib'

--Delete Entire data from the Table

DELETE FROM Table1

UPDATE

UPDATE

--==============================================[ UPDATE ]=================================================================

--The SQL UPDATE clause changes the data in already existing database row(s) and usually

--we need to add a conditional SQL WHERE clause to our SQL UPDATE statement in order to

--specify which row(s) we intend to update.

--Update table column value (specify the row using Where clause)--

UPDATE Person.Contact

SET Phone = 9550000520, EmailAddress = 'habib.ccna@gmail.com'

WHERE FirstName = 'Habib'

SELECT * FROM Person.Contact

WHERE LastName Like 'sheik%'

WHERE

WHERE

--==============================================[ WHERE ]=====================================================================

--The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query.

--Where (equal to)---

SELECT * FROM Person.Contact

WHERE LastName='Achong'

--Where (Not equal <>)--

SELECT * FROM Person.Contact

WHERE LastName <> 'Achong'

--Where (Greater than >)---

SELECT * FROM Person.Contact

WHERE ContactID > 5

--Where (Greater or equal >= )---

SELECT * FROM Person.Contact

WHERE ContactID >= 5

--Where (Less than <)---

SELECT * FROM Person.Contact

WHERE ContactID < 5

--Where (Less or equal <=)

SELECT * FROM Person.Contact

WHERE ContactID <= 5

--Where Like ----

SELECT * FROM Person.Contact

WHERE LastName Like 'Ach%'

SELECT * FROM Person.Contact

WHERE Phone Like '398%'

            -- Wildcard Concept: first digit of the number is between 3-6, second digit can be any number _, third digit should be 4 and rest cab be any number

SELECT * FROM Person.Contact

WHERE Phone LIKE '[3-6]_4%'

--Where Between--

SELECT * FROM Person.Contact

WHERE ContactID BETWEEN '1' AND '50'

--Where In-----

SELECT * FROM Person.Contact

WHERE ContactID IN ('1', '15')

DISTINCT

DISTINCT

--==============================================[ DISTINCT ]====================================================================

--The SQL DISTINCT clause is used together with the SQL SELECT keyword, to return

--a dataset with unique entries for certain database table column.

--Distinct (to see different surnames in lastname column)

SELECT DISTINCT LastName

FROM Person.Contact

SELECT INTO

SELECT INTO

--==============================================[SELECT INTO]===============================================================

--The SQL SELECT INTO statement is used to select data from a SQL database

--table and to insert it to a different table at the same time.

--Select Into table(copy entire data)---

SELECT *

INTO table2

FROM table1

--Select Into (copies selected columns)--

SELECT Column1, Column2, Column3,

INTO Table2

FROM Table1

SELECT

SELECT

--=========================================[SELECT]=====================================================================

--The SQL SELECT statement is used to select data from a SQL database table.

--This is usually the very first SQL command every SQL newbie learns and this

--is because the SELECT SQL statement is one of the most used SQL commands.

--Select table--

SELECT * FROM Person.Contact

--Select Coloumns of table--

SELECT FirstName,MiddleName FROM Person.Contact

INSERT INTO

INSERT INTO

--=========================[ INSERT INTO ]===================================================

--This is used to add a new row into the database table.

--Insert Into Table (Use AdventureWorks Database)--

INSERT INTO Person.Contact

VALUES ('2998', '0','Mr.','Habibar','Rahman','Sheikh'.....)

--Insert Into Table (in specified column)

INSERT INTO Person.Contact (FirstName, LastName,Phone,EmailAddress,PasswordHash,PasswordSalt)

VALUES ('Habib', 'Sheikh', '9985476188', 'habib.mvp@gmail.com','jhshdhsjkddujiushdfijmujsgudh','uyuhjs=')

SELECT * FROM Person.Contact

WHERE LastName Like 'sheik%'

Renaming

sp_rename

--===============[sp_rename==================================================================

-- Renaming Table

-- System procedure sp_rename modifies the name of an existing table

 sp_rename employee, customer

 SELECT * FROM customer

DROP

DROP

--=========================================[DROP]=======================================================================

--To delete the entire table with the date, Drop is used.

--NOTE: here employee table is referenced by foreign key of department table

--so, we need to drop department table first or drop Fkey first then drop employee table.

DROP TABLE department

DROP TABLE employee

TRUNCATE

TRUNCATE

--=========================================[TRUNCATE]===================================================================
--Truncate is used when we want to delete the data inside the table, and not the table itselfTRUNCATE TABLE employee

ALTER

ALTER

--===================[ALTER]============================================================

--ALTER is used to add or delete new columns to the existing table or to change the datatypes

-- Adds the column telephone_no to the employee table.

ALTER TABLE employee

ADD telephone_no CHAR(12) NULL

SELECT * FROM employee

--chnage the datatype of the column telephone_no

ALTER TABLE employee

ADD telephone_no NVARCHAR(15)

SELECT * FROM employee

--deletes the column telephone_no

ALTER TABLE employee

DROP telephone_no

SELECT * FROM employee

IDENTITY

IDENTITY

--=========================================[IDENTITY]==================================================================

--This is used when we would like the value of the primary key field to be created automatically

--every time a new record is inserted.

--Identity---

CREATE TABLE employee(

     ID          int PRIMARY KEY IDENTITY,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

SQL Constraints

CONSTRAINTS

--=========================================[SQL CONSTRAINTS]===========================================================

--Constraints are used to limit the type of data that can go into a table.

--We will focus on NOT NULL, UNIQUE,PRIMARY KEY ,FOREIGN KEY,CHECK ,DEFAULT constraints-----

--NOT NULL-------

CREATE TABLE employee(

     ID          int NOT NULL,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--UNIQUE---------

CREATE TABLE employee(

     ID          int NOT NULL UNIQUE,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--PRIMARY KEY-----

CREATE TABLE employee(

     ID          int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--FOREIGN KEY-----

--here we have two tables employee and department

CREATE TABLE employee(

     ID          int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

CREATE TABLE department(

     DepartmentID int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     designation nvarchar (10),

     empID          int references employee(ID))

--CHECK-----------

CREATE TABLE employee(

     ID          int NOT NULL CHECK (ID>0),

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--DEFAULT (see below example)--

--=========================================[DEFAULT]====================================================================

--To specify a default value, add "Default [value]" after the data type declaration

--Default City Unknown

CREATE TABLE employee(

     ID          int,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10)default 'Unknown',

     region      char (1))

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (3,  'Habib', 90020,  '12/03/99',DEFAULT,  'W')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')

SELECT * FROM employee