Notes

--------------------------------Introduction to Microsoft SQL Server
-- comment for single line

/* Multilines comments
adasd
asdsa
asd
*/

sp_helpdb -- system stored procedure to view Databases
--- Or
exec sp_helpdb

sp_helpdb 'ITS2019' --- Information about DB and its storage structure

--- Database: Collection of Objects (Tables,Views,SPs,UDFs,Triggers,Synonyms,Sequences etc...)

/*
SQL Server 7.5
Sql Server 2000 (8)
2005 (9)
2008 (10)
2012 (11)
2014 (12)
2016 (13)
2017 (14)
2019 (15)
*/


USE ITS2019 -- To set your working default database for this session

select * from INFORMATION_SCHEMA.TABLES

Go
sp_helpdb
use TSQL2012
select * from information_schema.tables
go

use ITS2019
go
select * from INFORMATION_SCHEMA.TABLES
GO

-- select * from <schemaname>.<tablename> -- OR --- <schemaname>.<viewname>

select * from hr.Employees
select * from sales.Orders
select * from Production.Products

sp_help 'hr.employees'
sp_help 'production.products'

sp_help 'hr.employees'

select * from INFORMATION_SCHEMA.TABLES

sp_help 'sales.orders'

/*
As an example of incorrect terms in T-SQL, people often use the terms “field” and “record” to refer to what T-SQL calls “column” and “row,” respectively.
• Tables are logical, and they have logical rows and columns.

T-SQL was designed as a declarative language where the instructions are provided in an English like manner. Therefore, the keyed-in order of the query clauses starts with the SELECT clause.
*/

select * from INFORMATION_SCHEMA.TABLES
select * from hr.Employees

select * from ITS2019.HR.Employees
select * from TSQL2012.dbo.courses

select * from Production.Categories

https://en.wikipedia.org/wiki/SQL

select * from HR.Employees

select * from INFORMATION_SCHEMA.TABLES

select * from sales.order
-- ERROR Incorrect syntax near the keyword 'order'.

select * from emp -- ERROR Invalid object name 'emp' --- we don't have table called "EMP"

select * from employees -- ERROR Invalid object name 'employees' -- you are missing to specify SCHEMA NAME

--- Solution
select * from hr.Employees

select *
from hr.Employees

select * from hr.Employees;select * from sales.Orders

select * from hr.Employees
select * from sales.Orders

select empid,firstname,lastname --- Column names like empid,firstname,hiredate,....
from hr.Employees

select * from INFORMATION_SCHEMA.TABLES

--- H/W write a select statements to display all tables data which are listed in InformationSchema.Tables Use SELECT statements for each tables in your DB

selct * from hr.employees
-- ERROR Incorrect syntax near '*'.

select * from sales.Orders

sp_help 'SALES.ORDERS' -- To get the column names of this given table

select orderdate,freight,shipcity,shippostalcode,shipcountry from sales.Orders

select shipcountry,shipcity,shippostalcode,orderdate from sales.Orders

select orderid,empid,orderdate,shipcity,shipcountry
from sales.Orders

select orderid,empid,firstname,lastname from hr.Employees
-- ERROR Invalid column name 'orderid'.

select * from INFORMATION_SCHEMA.TABLES
select * from Production.Products

select * from employees -- error Invalid object name 'employees'.

select * from Nums -- dbo is default schema so no need to mentioned before the table name
select * from dbo.Nums

---------
select * from hr.Employees
select * from sales.Orders

select empid,firstname,hiredate from hr.Employees


-------------------- Module 2: Introduction to T-SQL Querying
-------------------- Module 3: Writing SELECT Queries

sp_helpdb

USE ITS2019
go

select * from INFORMATION_SCHEMA.TABLES

select * from hr.Employees

/*
- Types of SQL Statements (https://en.wikipedia.org/wiki/SQL)
- DQL
- SELECT
- DML
- INSERT
- UPDATE
- DELETE
- DDL
- CREATE
- ALTER
- DROP
*/

select * from sales.Orders

select orderid,orderdate,shipcity,freight
from sales.orders

select 'Welcome to SQL Server'
select 10+20/45

-- Always write clauses in new line
select orderid,orderdate,shipcity,freight -- Columns,expressions, or '*' for all columns
from sales.orders -- two part name <schemaname>.<tablename>
where shipcity = 'Seattle' -- Conditions

----
select
empid,firstname,hiredate,country
from
hr.employees
----
go

select empid,firstname,hiredate,country
from hr.employees
go

---- Way to execute SELECT statement with clauses
SELECT empid,firstname,lastname,hiredate,country -- third
FROM hr.Employees -- First execute
WHERE country = 'USA' -- second
ORDER BY firstname -- most of time execute last/end of statement
go

/* ----------- Filtering Data with Predicates
In the very first SQL queries that you ever wrote, you very likely already started using the WHERE clause to filter data based on predicates.

The WHERE filter returns only the rows for which the predicate evaluates to true.

WHERE <condition>
Condition would be combination of <Column/expression> <operator> <values>
e.g. country = 'USA'
*/

select * from Production.Products
select productid,unitprice,unitprice+50,1000+2000 from Production.Products

select productid,unitprice from Production.Products
where unitprice+50 > 100

select * from hr.Employees
select * from hr.Employees where empid = 5
----
select
empid,titleofcourtesy,firstname,lastname,title,phone,hiredate,birthdate,address,city,region,postalcode,country,mgrid
from
hr.Employees

----
select
empid,titleofcourtesy,firstname,lastname,title,phone,hiredate,birthdate,address,city,region,postalcode,country,mgrid
from
hr.Employees
where
empid = 3

select firstname,lastname
from hr.Employees
where city = London
--- ERRRO Invalid column name 'London'.
--- In where clause values of CHARs(String) and DATE will be enclosed with ' single quotaion mark e.g 'London'

--- Solution
select firstname,lastname
from hr.Employees
where city = 'London'

----
select * from Production.Products

select * from Production.Products where discontinued = 1
select * from Production.Products where discontinued = 0 -- equal to
select * from Production.Products where discontinued != 1 -- not aqual to
-- OR
select * from Production.Products where discontinued <> 1 -- not aqual to

select * from Production.Products where discontinued = 5
-- (0 rows affected)

/*
The predicate can evaluate to true or false. The type of logic used in such a case is known as two-valued logic.

When NULLs are possible in the data, a predicate can evaluate to true, false, and unknown. This type of logic is known as three-valued logic.

T-SQL provides the predicate IS NULL to return a true when the tested operand is NULL. Similarly, the predicate IS NOT NULL returns true when the tested operand is not NULL

A NULL is a mark for a missing value—not a (Unknown,Unidentified,skipped value) value itself. Hence, the correct usage of the term is either “NULL mark” or just “NULL.”
*/

select * from hr.Employees

select empid,firstname,lastname,hiredate
from hr.Employees
where region = 'WA'
-------
select empid,firstname,lastname,hiredate
from hr.Employees
where region != 'WA' -- not aqual to (!= <> )
-------
--- not allowed for NULL Mark
select * from hr.Employees where region = 'NULL'
-- or
select * from hr.Employees where region = NULL
-- or
select * from hr.Employees where region = ''

----------
--- Solution
select * from hr.Employees where region is null
-- 4 rows affected
select * from hr.Employees where region is not null
-- 5 rows affected

/* H/W
---- Display employees who don't work under any employees or who don't have any manager defined
---- From sales.orders table..
Find order details of which are not shipped yet(unshipped orders)
*/

-------------
/*
Comparison Operators
> < >= <= = != <>
Can use for any data type
*/

select * from Production.Products
select * from Production.Products where unitprice > 40

select * from Production.Products where unitprice >= 40

select * from Production.Products where unitprice < 5

select * from hr.Employees
select * from hr.Employees where hiredate >= '2003-01-01' and hiredate <= '2003-12-31'

select * from hr.Employees where birthdate < '1970-01-01' -- date filter

select * from hr.Employees where titleofcourtesy = 'Mr'
-- (0 rows affected)
-- Actual value is 'Mr.'
select * from hr.Employees where titleofcourtesy = 'Mr.'
select * from hr.Employees where titleofcourtesy = 'MR.'

select * from hr.Employees where firstname = 'Paul'

select * from hr.Employees where empid = '4'

/* H/W
Query
--- find employees who joinned before 2003
--- find employees who are working as 'Sales Manager'
--- find employees who work under employee id 5 (mean empid 5 is manager)
--- find products which selling in category id 3
*/

--- Check for Datatime in C# and SQL Server

select firstname,hiredate,city into emptest from hr.Employees

--------------- Between Operator
-- BETWEEN columnname startvalue AND endvalue
--- startvalue and endvalues are inclusive

select * from Production.Products where unitprice > 40 and unitprice < 60
select * from Production.Products where unitprice >= 40 and unitprice <= 60

-- same example using BETWEEN
select * from Production.Products where unitprice between 40 and 60 -- (inclusive both the range values)

select * from HR.Employees where empid between 3 and 7

--- find employees who joined in year 2003
select * from hr.Employees where hiredate >= '2003-01-01' and hiredate <= '2003-12-31'
-- Using between
select * from hr.Employees where hiredate between '2003-01-01' and '2003-12-31' -- Date format is 'YYYY-MM-DD'
-- OR
select * from hr.Employees where hiredate between '20030101' and '20031231' --- Language Natural format for the DATE data

---------------- IN operator
--- IN ('value1','value2','value3'.....n) -- use for aqual value (matched value)
--- It is a list so no ordered required

--- Want to display data for employee's 2,5,7,9
select * from hr.Employees where empid = 2 OR empid = 5 OR empid =7 OR empid = 9
-- Using IN
select * from hr.Employees where empid IN (1,2,5,9)

select * from hr.Employees where city in ('London','Seattle')

select empid,firstname,city,country from hr.Employees where city IN ('London','Seattle','Ahmedabad')

--- Orders which placed by 88,55,41 cust ID
select * from Sales.Orders where custid in (88,55,41)

/* H/W
--- Find products which we are selling at 40 or 50 or 80 or 10
--- Find orders which taken by 5 and 7 number employees
--- Find Products which belong to category id 3 or 8 or 10
*/

----- Combining Predicates (AND, OR, NOT)
/*In the WHERE clause by using the logical operators AND and OR. You can also negate predicates by using the NOT logical operator.

--- condition1 AND condition2 -- true if both the conditions are true
--- condition1 OR condition2 -- True if any of the Conditions become true
--- NOT condition
*/

select * from hr.Employees
-- employees who worked in USA and their birthdate must be before 1970

select empid,firstname,birthdate,city,country
from hr.Employees
where country = 'USA' and birthdate < '1970-01-01'
-- both condition must be matched to display result

--- employees who worked in USA <OR> either their birthdate must be before 1970
select empid,firstname,birthdate,city,country
from hr.Employees
where country = 'USA' OR birthdate < '1970-01-01'

--- employees who worked in USA or either their birthdate must be before 1970 but City must be 'Seattle'
select empid,firstname,birthdate,city,country
from hr.Employees
where country = 'USA' OR birthdate < '1970-01-01' and city= 'Seattle'

/*
Some precedence rules determine the logical evaluation order of the different predicates.
The NOT operator precedes AND and OR, and AND precedes OR.
WHERE col1 = 'w' AND col2 = 'x' OR col3 = 'y' AND col4 = 'z‘
Because AND precedes OR, you get the equivalent of the following
WHERE (col1 = 'w' AND col2 = 'x') OR (col3 = 'y' AND col4 = 'z‘)
*/

-- means default way to follow precedence
select empid,firstname,birthdate,city,country
from hr.Employees
where country = 'USA' OR (birthdate < '1970-01-01' and city= 'Seattle')

-- Change the Precedence by using parentheses have the highest precedence among all operators
-- want this
--- employees who worked in USA or either their birthdate must be before 1970 but City must be 'Seattle'
select empid,firstname,birthdate,city,country
from hr.Employees
where (country = 'USA' OR birthdate < '1970-01-01') and city= 'Seattle'

select * from hr.Employees where city = 'Seattle'

--- NOT
select empid,firstname,birthdate,city,country
from hr.Employees
where Not (country = 'USA' OR birthdate < '1970-01-01') and city= 'Seattle' -- 0 row

select empid,firstname,birthdate,city,country
from hr.Employees
where not ((country = 'USA' OR birthdate < '1970-01-01') and city= 'Seattle') -- 7 row

select empid,firstname,birthdate,city,country
from hr.Employees
where not (country = 'USA' and birthdate < '1970-01-01' and city= 'Seattle') -- 7 row

select empid,firstname,birthdate,city,country
from hr.Employees
where not (country = 'USA' or city = 'Seattle')

select * from hr.Employees

select * from hr.Employees where city not in ('London','Seattle')