SQL : Northwind Database Understanding and R&D with Example



--Company name ,total sale

SELECT C.CompanyName,SUM(OD.Quantity) as TotalQTy

FROM

Customers C,

Orders O,

[Order Details] OD,

Products P

WHERE

OD.OrderID = O.OrderID AND

P.ProductID = OD.ProductID AND

O.CustomerID = C.CustomerID

Group By C.CompanyName Order by C.CompanyName

============================================

--Company name , productwise sale

SELECT C.CompanyName,P.ProductName,SUM(OD.Quantity) as TotalQTy

FROM

Customers C,

Orders O,

[Order Details] OD,

Products P

WHERE

OD.OrderID = O.OrderID AND

P.ProductID = OD.ProductID AND

O.CustomerID = C.CustomerID AND

P.ProductID = OD.ProductID

Group By C.CompanyName,P.ProductName Order by C.CompanyName

===================================

--Company name , category wise sale

SELECT C.CompanyName,Cat.CategoryName,SUM(OD.Quantity) as TotalQTy

FROM

Customers C,

Orders O,

[Order Details] OD,

Products P,

Categories Cat

WHERE

OD.OrderID = O.OrderID AND

P.ProductID = OD.ProductID AND

O.CustomerID = C.CustomerID AND

P.ProductID = OD.ProductID AND

P.CategoryID = Cat.CategoryID

Group By C.CompanyName,Cat.CategoryName Order by C.CompanyName

<b>==========================Create DB=====================</b>

CREATE DATABASE TestDb

ON

(

NAME = TestDb,

FILENAME = 'C:\SQLDEMO\TestDb.mdf',

SIZE = 20MB,

MAXSIZE = 70MB,

FILEGROWTH = 5MB )

LOG ON

(

NAME = 'TestDb_log',

FILENAME = 'C:\SQLDEMO\TestDb.ldf',

SIZE = 10MB,

MAXSIZE = 40MB,

FILEGROWTH = 5MB )

exec sp_helpdb 'TestDb'

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled

<b> </b>

<b> </b>

<b>Alter Table , set File Growth</b>

Alter database TestDb modify file (name= 'testDb' , FILEGROWTH = 7);

use TestDb

exec sp_help dept

<b>============Create Table======</b>

Create table Dept

(

deptno int,

deptname varchar(20),

location varchar(10)

)

<b>=====ALTER</b>========

alter table dept

add country varchar (20)

===INSERT===

insert into Dept values (101,'a','b','c')

insert into Dept (deptno,deptname) values (101,'technology')

===UPADTE===

update Dept set deptname = '786' where deptno = 101

==DELETE==

delete from Dept where deptno = 101

<b>===Create new table from another table===</b>
<ol>
	<li>Select * into NewCustomers from Customers (Full Table)</li>
	<li>select CustomerId,Companyname into cust1 from Customers (Only few columns)</li>
	<li>select Companyname,City into cust_specific_country from Customers where Country = 'usa'
 (13 columns affected)</li>
	<li>select Country,COUNT(*) as customer_count into CountryCust from Customers group by Country
 (21 Rows affected)</li>
	<li><b>5.       </b>select Country,COUNT(*) as customer_count into CountryCust1 from Customers group by Country order by customer_count
 <b>(Sorted value in Table)</b></li>
</ol>
<b>========================Demonstration ===========================</b>

&nbsp;

<b>Create table with few columns , and then set Total quanity fetch it from order details</b>

select ProductId,ProductName,ProductId as TotalQty into <b>NewProduct</b> from Products

Update <b>NewProduct</b> set TotalQty = (select SUM(Quantity) from [Order Details] OD where OD.ProductID = <b>NewProduct</b>.ProductID )

<b> </b>

<b> </b>

<b>======================== CONSTRAINTS =====================</b>

<b>--Primary Key</b>

<b>--Foreign Key</b>

<b>--check</b>

<b>--unique</b>

<b>--Default</b>

<b> </b>

<b>In table , Primary key is one only, where as we can have multiple Unique key (Mobile no, aadahr card number)</b>

<b> </b>

<b> </b>

<b>Add Primary key to table</b>

ALTER TABLE Dept ALTER COLUMN deptno INT NOT NULL ;

alter table Dept add constraint Pk_Dept_Deptno

Primary key (deptno)

<b>============Constraint Definitions===================</b>

Create table Employee

(

empno int primary key,

name varchar (20) NOT NULL,

deptno int foreign key references Dept(deptno),

salary int check (salary >= 0),

ppno varchar (20) unique

)

Alter table Employee

add DOJ datetime Default GetDate()

//MSSQL – has datetime in miliseconds

//To avoid non space in name

Create table Employee1

(

empno int primary key,

name varchar (20) NOT NULL check (name != ‘ ’),

deptno int foreign key references Dept(deptno),

salary int check (salary >= 0),

ppno varchar (20) unique

)

<b>===============RULES=========================</b>

Create Rule MoreThenZeroRule as @morethenzero >= 0

Exec sp_bindrule 'MoreThenZeroRule' , 'Dept.deptno'

Exec sp_bindrule 'MoreThenZeroRule' , 'Employee.salary'

Output : Rule bound to table column.

<b>Unbind Operation</b>

Exec sp_unbindrule 'MoreThenZeroRule' , 'Employee.salary'

<b>==================Create View and Call View============</b>

Create View view_dept as select * from Dept

select * from view_dept

<b>//If we are joining two tables and using that in many places, it is better to create view and then filter it accordingly example below</b>

select * from view_dept where deptno = 1

<b>EXAMPLE 2</b>

Create View full_employee1 as Select Employee.empno,Employee.name,Employee.salary,Dept.deptname from Dept, Employee Where Dept.deptno = Employee.deptno

select * from full_employee1 where empno = 1

Create View full_employee1 as Select Employee.empno,Employee.name,Employee.salary,Dept.deptname from Dept, Employee Where Dept.deptno = Employee.deptno

select * from full_employee1 order by empno asc

select * from full_employee1 order by empno desc

select deptname from full_employee1 group by deptname

Drop View usa_customers <b>(Drop if exists)</b>

Create View usa_customers

AS

Select CustomerId,CompanyName,Address,City,Country FROM Customers where country = 'usa'
 (13 row(s) affected)

<b>==================Fire Update Command in View and Restrict it================</b>

Update usa_customers set country = ‘India’

select * from usa_customers

Update usa_customers set country = 'usa1'

(0 Rows found)

<b>SO to avoid such a condition , we need to create a view in such a way that , it should not affect the raw database.</b>

Create View usa_customers

AS

Select CustomerId,CompanyName,Address,City,Country FROM Customers where country = 'usa' <b>WITH CHECK OPTION</b>

And if we try to update it

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

========RESET DB==========

Update Customers set Country = 'USA' where Country = 'usa1'

&nbsp;

Share on Facebook




About Pragnesh Karia

Pragnesh Karia, Open Source Enthusiast, Software Professional, Software Developer, Technical Lead ,Magento, Joomla ,Joomla LMS , Moodel LMS ,PHP ,Mysql, Ajax, Javascript, Jquery, Linux, Fan of Open Sources , Annet Technologies , SEO Analyst , Mootools