SQL-Queries

 

Contents 1 10.2 Database Engine Tuning Advisor (Dta) Tool 1 1.1 10.2.1 Recommendations - index /statistics /partitions 1

1. Sample DBs 

• https://github.com/microsoft/sql-server-samples/tree/master/samples/databases 

• https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

• adventure-works

• contoso-data-warehouse

• northwind-pubs

• wide-world-importers

• Sample_Scripts.zip downloaded 

2. Joins 

1. Types of Joins

1. CROSS JOIN - Provide all the possible joins 

2. INNER JOIN

1. list out employee and Manager name

SELECT Emp.Emp_Name, 'Not Available' as MgrName

FROM Employees Emp

WHERE  EMP.Manager_ID IS NULL

UNION ALL

SELECT Emp.Emp_Name, MGR.Emp_Name 

FROM Employees Emp

INNER JOIN Employees Mgr 

on EMP.Manager_ID = Mgr.Emp_ID

2. a

3. OUTER JOIN - LEFT OUTER JOIN

4. OUTER JOIN - RIGHT OUTER JOIN

5. OUTER JOIN - FULL OUTER JOIN

6. SELF JOIN

1. list out employee and Manager name

SELECT Emp.Emp_Name, 'Not Available' as MgrName

FROM Employees Emp

WHERE  EMP.Manager_ID IS NULL

UNION ALL

SELECT Emp.Emp_Name, MGR.Emp_Name 

FROM Employees Emp

INNER JOIN Employees Mgr 

on EMP.Manager_ID = Mgr.Emp_ID

2. List out employees and Managers name even without manager also

SELECT Emp.Emp_Name, MGR.Emp_Name 

FROM Employees Emp

left outer  JOIN Employees Mgr 

on EMP.Manager_ID = Mgr.Emp_ID

GO

7. a

2. Join Options:

1. HASH JOIN

2. MERGE JOIN

3. LOOP JOIN

3. wide-world-importers 

1. Union

/*

Database : WideWorldImporters

Tables used : Sales.Customers, Sales.CustomerTransactions and Sales.Invoices

*/

select 'Customers' As tbl,  Count(*) as RecCount  from Sales.Customers

UNION

select 'CustomerTransactions' As tbl,  Count(*) as RecCount  from  Sales.CustomerTransactions

UNION

select 'Invoices' As tbl,  Count(*) as RecCount  from  Sales.Invoices

2. Cross Join

SELECT 

(select COUNT(CUSTOMERID) from  Sales.Customers)  * 

(select COUNT(CUSTOMERID) from  Sales.CustomerTransactions) --64408461

--EXPECTED NUMBER OF RECORDS ARE 64408461

SELECT COUNT(*) 

FROM Sales.Customers SC

CROSS JOIN Sales.CustomerTransactions SCT -- 64408461

--ADD WHERE CONDITION THEN IT WILL GIVE ALL POSSIBLE RECORDS

SELECT COUNT(*) 

FROM Sales.Customers SC

CROSS JOIN Sales.CustomerTransactions SCT

WHERE SC.CustomerID = SCT.CustomerID -- 97147

--CHANGE THE ORDER OF THE TABLES - IT WILL GIVE ALL POSSIBLE RECORDS

SELECT COUNT(*) 

FROM Sales.CustomerTransactions SCT

CROSS JOIN Sales.Customers SC

WHERE  SCT.CustomerID = SC.CustomerID -- 97147

SELECT * 

FROM Sales.CustomerTransactions SCT

CROSS JOIN Sales.Customers SC

WHERE  SCT.CustomerID = SC.CustomerID -- 97147

3. INNER JOIN 

-- Top 5  customers who bought more

SELECT TOP 5 CustomerName, COUNT(SCT.CUSTOMERID) Qty

FROM Sales.Customers SC

INNER JOIN  Sales.CustomerTransactions SCT

ON SC.CustomerID = SCT.CustomerID

GROUP BY CustomerName -- 263

ORDER BY Qty DESC

--FIND HOW MANY TRANSACTIONS   ALREADY WE SAW BY USING CROSS JOIN AS GIVEN BELOW

SELECT COUNT(*) 

FROM Sales.Customers SC

CROSS JOIN Sales.CustomerTransactions SCT

WHERE SC.CustomerID = SCT.CustomerID -- 97147

--THE SAME RESULT WE CAN GET IT BY USING INNER JOIN

SELECT COUNT(*) 

FROM Sales.Customers SC

INNER JOIN Sales.CustomerTransactions SCT

ON SC.CustomerID = SCT.CustomerID -- 97147

--CUSTOMERWISE TRANSACTION REPORT

SELECT SC.CUSTOMERNAME,   COUNT(SCT.InvoiceID)  Cnt

FROM Sales.Customers SC

INNER JOIN Sales.CustomerTransactions SCT

ON SC.CustomerID = SCT.CustomerID

GROUP BY SC.CUSTOMERNAME -- 263 CUSTOMERS HAVE TRANSACTION RECORDS

SELECT CustomerName, COUNT(SCT.CUSTOMERID) Qty

FROM Sales.Customers SC

INNER JOIN  Sales.CustomerTransactions SCT

ON SC.CustomerID = SCT.CustomerID

GROUP BY CustomerName -- 263

4. LEFT OUTER JOIN 

1. How many customers doesnt buy anything

-- How many customers doesnt buy anything

SELECT SC.CustomerID, CustomerName, COUNT(SCT.CUSTOMERID) Qty

FROM Sales.Customers SC

LEFT OUTER JOIN  Sales.CustomerTransactions SCT

ON SC.CustomerID = SCT.CustomerID

WHERE SCT.CustomerID IS NULL

GROUP BY SC.CustomerID, CustomerName --400

--CUSTOMERS HAVE ORDER 263 / NO ORDERS 400 = 663

4. Find Duplicates 

1. Based on some fields - By using Having

• Method one - list of duplicates - really duplicate with unique records

SELECT username, email, COUNT(*)

FROM users

GROUP BY username, email

HAVING COUNT(*) > 1

2. entire records of duplicates

SELECT a.*

FROM users a

JOIN (

SELECT username, email, COUNT(*)

FROM users 

GROUP BY username, email

HAVING count(*) > 1 ) b

ON a.username = b.username

AND a.email = b.email

ORDER BY a.email

3. A

5. Nth Highest

1. By using Top with sub query

SELECT TOP 1 salary FROM ( 

SELECT DISTINCT TOP 3 salary FROM Employee ORDER BY salary DESC ) AS temp 

ORDER BY salary

2. SQL query to find employees hired in last n months

Select *

FROM Employees

Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N