Django Web Development

Here is list of common tasks which we do while developing websites using Django

Installation

• pip install Django

Creating Virtual Environment

• python -m venv C:\Users\salah\OneDrive\Desktop\Data\Git1\learning\Projects\venvDynamicInternalPages
• C:\Users\salah\OneDrive\Desktop\Data\Git1\learning\Projects\venvDynamicInternalPages\Scripts>activate

Creating New Django Project

• django-admin startproject dynamicinternalpages
• cd dynamicinternalpages
• python manage.py startapp internal
• python manage.py makemigrations
• python manage.py migrate

# Application definition

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'dynamicinternalpages',
    'web',
    'rest_framework',
    'api'
]

STATIC_URL = '/static/'

MEDIA_URL = '/media/'

STATIC_ROOT = os.path.join(BASE_DIR, 'static')

STATICFILES_DIR = [
    os.path.join('BASE_DIR', 'static')
    # BASE_DIR / "static"
]

Create Super User

• python manage.py createsuperuser

Creating requirements files

• pip freeze requirements.txt

Rest Framework

• pip install djangorestframework
• pip install markdown       
• pip install django-filter 

REST_FRAMEWORK = {
    'DEFAULT_PAGINATION_CLASS': 'rest_framework.pagination.PageNumberPagination',
    'PAGE_SIZE': 10
}

Run Site

• python manage.py runserver

Working with MYSQL Database

pip install mysqlclient

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'dynamicinternalpages',
        'HOST': '127.0.0.1',
        'PORT': '3306',
        'USER': 'root',
        'PASSWORD': 'titan#12',
    }
}

Steps for creating new branch using git bash

Step 1: Run below command to create new branch. here i am creating new branch QAReady_xxxx out of QAReady you can replace branch names as required. git checkout -b QAReady_xxxx QAReady

Step 2: Your branch is now created and is local to you. Please checkout new branch git checkout QAReady_xxxx

Step 3: Setup and Upstream branch so that you could push your changes to remote git branch --set-upstream-to=origin/QAReady_pdxxxx QAReady_pdxxxx

Step 4: Now you are all set. You can edit/create files. commit and push it to remote.

SQL Server Interview Question 26 - 50

Q26. Explain with example Recursive Stored Procedure

SQL Server allows stored procedure to call it self. Restricted to max 32 recursive calls.
This can be used to implement solutions which reques same peace of code calling itself untill some boundary condition is met.

Ex: Lets try to write stored procedure which calculates factorial. 

For example Factorial(5) = 5 * 4 * 3 * 2 * 1 = 120

CREATE PROCEDURE [dbo].[Usp_getfactorial] (@Number    INTEGER,
                                           @Factorial INTEGER OUTPUT)
AS
    DECLARE @In INTEGER
    DECLARE @Out INTEGER
    IF @Number != 1
      BEGIN
          SELECT @In = @Number - 1
          EXEC [Usp_getfactorial]
            @In,
            @Out OUTPUT
          SELECT @Factorial = @Number * @Out
      END
    ELSE
      BEGIN
          SELECT @Factorial = 1
      END
    RETURN
GO 

DECLARE @Number INTEGER = 5
DECLARE @Factorial INTEGER
EXEC [dbo].[Usp_getfactorial] @Number, @Factorial OUTPUT
SELECT @Factorial

Q27. List down some of the important SQL Server global variables 

@@MAX_PRECISION 

Returns the precision level used by decimal and numeric data types as currently set in the server.

SELECT @@MAX_PRECISION AS '@@MAX_PRECISION'

@@TEXTSIZE 

Returns the current value of the TEXTSIZE option.

SELECT @@TEXTSIZE AS '@@TEXTSIZE'
SET TEXTSIZE 1678
SELECT @@TEXTSIZE AS '@@TEXTSIZE'

@@LANGID 

Returns the local language identifier (ID) of the language that is currently being used.

SELECT @@LANGID AS 'Language ID'
SET Language 'Italian'
SELECT @@LANGID AS 'Language ID'
SET Language 'English'

@@CONNECTIONS 

Returns the number of attempted connections - both successful and unsuccessful - since SQL Server was last started.

SELECT @@CONNECTIONS AS '@@CONNECTIONS'

@@ERROR 

Returns the error number for the last Transact-SQL statement executed.

@@ERROR Returns 0 if the previous Transact-SQL statement encountered no errors.
SELECT 1/0
SELECT @@ERROR AS '@@ERROR'

@@IDENTITY 

Returns the last-inserted identity value

IF OBJECT_ID('dbo.IdentityDemo') IS NOT NULL BEGIN DROP TABLE dbo.IdentityDemo END
CREATE TABLE IdentityDemo
(
ID INT Identity(1,1),
FullName NVARCHAR(50)
)
INSERT INTO IdentityDemo (FullName) VALUES ('A'), ('B'), ('C')
SELECT @@IDENTITY AS '@@IDENTITY'

@@IDLE 

Returns the time that SQL Server has been idle since it was last started. 

The result is in CPU time increments, or "ticks," and is cumulative for all CPUs, so it may exceed the actual elapsed time.
SELECT @@IDLE AS '@@IDLE'

@@CPU_BUSY 

Returns the amount of time that SQL Server has spent in active operation since its latest start.

SELECT @@CPU_BUSY AS '@@CPU_BUSY'

@@ROWCOUNT 

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG

IF OBJECT_ID('dbo.RowCountDemo') IS NOT NULL BEGIN DROP TABLE dbo.RowCountDemo END
CREATE TABLE RowCountDemo
(
ID INT Identity(1,1),
FullName NVARCHAR(50)
)
INSERT INTO RowCountDemo (FullName) VALUES ('A'), ('B'), ('C')
SELECT * FROM RowCountDemo
SELECT @@ROWCOUNT AS '@@ROWCOUNT'

@@TOTAL_ERRORS 

Returns the number of disk write errors encountered by SQL Server since SQL Server last started.

SELECT @@TOTAL_ERRORS AS '@@TOTAL_ERRORS'

@@SERVERNAME 

Returns the name of the local server that is running SQL Server.

SELECT @@SERVERNAME AS '@@SERVERNAME'

@@VERSION 

Returns system and build information for the current installation of SQL Server.

SELECT @@VERSION AS '@@VERSION'

@@SERVICENAME 

Returns the name of the registry key under which SQL Server is running.

SELECT @@SERVICENAME AS '@@SERVICENAME'

@@SPID

Returns the session ID of the current user process.

SELECT @@SPID AS 'Session_Id'

@@TOTAL_READ 

Returns the number of disk reads, not cache reads, by SQL Server since SQL Server was last started.

SELECT @@TOTAL_READ AS '@@TOTAL_READ'

@@TOTAL_WRITE

Returns the number of disk writes by SQL Server since SQL Server was last started.

SELECT @@TOTAL_WRITE AS '@@TOTAL_WRITE'

@@TRANCOUNT

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

IF OBJECT_ID('dbo.TranCountDemo') IS NOT NULL BEGIN DROP TABLE dbo.TranCountDemo END
CREATE TABLE TranCountDemo
(
ID INT Identity(1,1),
FullName NVARCHAR(50)
)

BEGIN TRANSACTION T1 

INSERT INTO TranCountDemo (FullName) VALUES ('A')

SELECT @@TRANCOUNT AS '@@TRANCOUNT' 

BEGIN TRANSACTION T2

INSERT INTO TranCountDemo (FullName) VALUES ('B')

SELECT @@TRANCOUNT AS '@@TRANCOUNT' 

BEGIN TRANSACTION T3

SELECT @@TRANCOUNT AS '@@TRANCOUNT' 

INSERT INTO TranCountDemo (FullName) VALUES ('B') 

COMMIT TRANSACTION T1 

SELECT @@TRANCOUNT AS '@@TRANCOUNT' 

Q28. Disabling constraint on all tables

ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Q29. Query to get free space on hard drives

EXEC master..xp_fixeddrives

Q30. Query to Enable/Disable triggers

ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name
ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name
ALTER TABLE Table_Name DISABLE TRIGGER ALL
ALTER TABLE Table_Name ENABLE TRIGGER ALL

Use Database_Name

Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"

 Q31. Query to list stored procedures modified in N days

SELECT name, modify_date 
FROM sys.objects 
WHERE type='P' 
AND DATEDIFF(D,modify_date,GETDATE()) < 5

SQL Server Query Performance Tuning

Here are some tips for tuning SQL Server queries

Basics Tips

1. Avoid use of SELECT *
2. Avoid too many joins
3. Avoid using cursors
4. Always restrict rows and columns of result set.
5. Verify if server is having enough free space. Ideally 30% of space should be free.
6. SQL Server is case insensitive, so avoid use of functions like UPPER, LOWER
7. Decreasing performance order of operators is >, >=, <, <=, Like,  <> (Slowest)
8. Use Exists, Not Exists instead of IN, NOT IN
9. When using like operator try to leave wild character on right side.
10. Avoid use of function in WHERE Clause.
11. Use LIKE instead of SUBSTRING in WHERE
12. Use OR ColunmName IS NULL instead of COALESCE/IsNULL functions.
13. It is better to have multiple queries with UNION ALL instead of one query with may ORs.
14. Create functions to reuse code but don't exaggerate use of functions.
15. Use truncate to delete all data instead of delete.
16. Define covering index.
17. Make sure that each table is having primary key.
18. Avoid use of triggers.
19. Avoid use of sub-queries.
20. Avoid use of DISTINCT clause
21. Use Union ALL instead of Union
22. Create joins with ON rather than WHERE
23. Use WHERE instead of Having to filter data to the extent possible.

Advance Tips

1. Have maintenance plan to automate Index rebuild and update stats.
2. Use WITH (NOLOCK) on SELECT queries running against data warehouse or any system where you know that data is updated only during non production hours.
3. Use batch commit, in case you are dealing with large volume of data.
4. Save data to temporary table and join in case same set of joins are being repeated inside a stored procedure.
5. Define aggregate tables in order to avoid long running aggregation queries, if possible.

Ways to Identify Non Performing Query

Use Activity Monitor to Identify long running queries

Right click on database instance and select Activity Monitor.

Query to find expensive queries

SELECT TOP 10 Substring(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset
                                                                              WHEN -1 THEN Datalength(qt.TEXT)
                                                                              ELSE qs.statement_end_offset
                                                                            END - qs.statement_start_offset ) / 2 ) + 1),
              qs.execution_count,
              qs.total_logical_reads,
              qs.last_logical_reads,
              qs.total_logical_writes,
              qs.last_logical_writes,
              qs.total_worker_time,
              qs.last_worker_time,
              qs.total_elapsed_time / 1000000 total_elapsed_time_in_S,
              qs.last_elapsed_time / 1000000  last_elapsed_time_in_S,
              qs.last_execution_time,
              qp.query_plan
FROM   sys.dm_exec_query_stats qs
       CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) qt
       CROSS APPLY sys.Dm_exec_query_plan(qs.plan_handle) qp
ORDER  BY qs.total_logical_reads DESC -- logical reads

-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

SQL Server Interview Questions 1 - 25

Q1. Query to insert random data in database table

IF Object_id('dbo.RandomData') IS NOT NULL
  BEGIN
      DROP TABLE dbo.RandomData
  END 

CREATE TABLE dbo.RandomData
  (
     RowId        INT IDENTITY(1, 1) NOT NULL,
     SomeInt      INT,
     SomeBit      BIT,
     SomeVarchar  VARCHAR(10),
     SomeDateTime DATETIME,
     SomeNumeric  NUMERIC(16, 2)
  )
GO
DECLARE @count INT
SET @count = 1

WHILE @count <= 100000
  BEGIN
      INSERT INTO dbo.RandomData
      SELECT @count,
             CASE
               WHEN Datepart(MILLISECOND, Getdate()) >= 500 THEN 0
               ELSE 1
             END AS [SomeBit],
             Char((Abs(Checksum(Newid())) % 26) + 97)
             + Char((Abs(Checksum(Newid())) % 26) + 97) AS [SomeVarchar],
             Dateadd
         (
           MILLISECOND,
           (Abs(Checksum(Newid())) % 6000 ) * -1,
           Dateadd(MINUTE, ( Abs(Checksum(Newid())) % 1000000 ) * -1, Getdate())
         ) AS [SomeDateTime],
            (
Abs(Checksum(Newid())) % 100001 )
+ ( ( Abs(Checksum(Newid())) % 100001 )
* 0.00001
) AS [SomeNumeric]
      SET @count += 1
  END

SELECT *
FROM   dbo.RandomData 

Q2. Query to do batch insert with delay of 1 second

-- Creating Sample Data
IF Object_id('dbo.SourceTable') IS NOT NULL
  BEGIN
      DROP TABLE dbo.A
  END 

CREATE TABLE SourceTable
  (
     id INT
  )
-- Creating Sample Data
IF Object_id('dbo.DestinationTable') IS NOT NULL
  BEGIN
      DROP TABLE dbo.A
  END
CREATE TABLE DestinationTable
  (
     id INT
  )
DECLARE @count INT
SET @count = 1
WHILE @count <= 100000
  BEGIN
      INSERT INTO dbo.SourceTable
      SELECT @count
      SET @count += 1
  END
SELECT Count(*)
FROM   dbo.SourceTable



-- Solution 1: Batch Insert Query
DECLARE @UK_ID INT
DECLARE @batchSize INT
DECLARE @results INT
SET @results = 1 -- stores the row count after each successful batch
SET @batchSize = 50000 -- How many rows you want to operate on each batch 

SELECT @UK_ID = Min(ID)
FROM   dbo.A 

-- when 0 rows are returned, exit the loop
WHILE ( @results > 0 )
  BEGIN
      INSERT INTO dbo.DestinationTable
                  (ID)
      SELECT *
      FROM   dbo.SourceTable
      WHERE  id >= @UK_ID
             AND id < @UK_ID + @batchSize
      SET @results = @@ROWCOUNT
      -- next batch
      SET @UK_ID = @UK_ID + @batchSize
      WAITFOR DELAY '00:00:01'
  END

Q3. Query to Swap Columns Values in SQL Server

CREATE TABLE SampleData ( Col1 INT, Col2 INT ) 

INSERT INTO SampleData VALUES (1, 2), (3, 4)

SELECT * FROM SampleData

UPDATE SampleData
SET Col1 = Col2,
Col2 = Col1

SELECT * FROM SampleData 

Q4. What would be output of below Query

SELECT 23A
SELECT 'A' =  23
SELECT A = 23

SELECT *
FROM (
  VALUES(1),(2),(3)
) t(a)

OR 

CREATE TABLE my_table(a INT) 

INSERT INTO my_table(a)
VALUES(1),(2),(3); 

SELECT * FROM my_table 

CREATE TABLE my_table(a INT, b INT)

INSERT INTO my_table(a, b)
VALUES(1,1),(2,2),(3,3);

SELECT * FROM my_table

Q5. Query to find Second Highest Salary of Employee 

CREATE TABLE Employee (EmpID INT, EmpName NVARCHAR(255), Salary MONEY, MgrID INT) 

INSERT INTO Employee
VALUES
(1, 'Siva', 10000, NULL),
(2, 'Chaitanya', 9000, 1),
(3, 'Ashutosh', 8000, 1),
(4, 'Sameer', 7000, 1) 

-- DELETE FROM Employee
SELECT * FROM Employee
 

Select *
from Employee e1
where 2 = (Select count(distinct Salary)
  from Employee e2
  where e1.salary<=e2.salary);

Q6. Query to find duplicate rows from a table

-- DROP TABLE dbo.DuplicateData
CREATE TABLE dbo.DuplicateData
(
ID INT,
Name1 NVARCHAR(255)
)

INSERT INTO dbo.DuplicateData
VALUES
(1, 'Sam'),
(1, 'Sam'),
(2, 'Ron'),
(3, 'Andrew'),
(3, 'Andrew'),
(4, 'Michel'),
(4, 'Michel')

SELECT * FROM dbo.DuplicateData

-- JUST Display Unique Values
SELECT ID, Name1
FROM   dbo.DuplicateData
GROUP BY ID, Name1 

;
WITH CTE(RowID, ID, Name1)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, Name1) AS RowID, ID, Name1
FROM dbo.DuplicateData
)
select * from CTE
WHERE RowID = 1

-- Deleting Duplicate Records
ALTER TABLE dbo.DuplicateData ADD PKID INT NOT NULL identity(1,1)
ALTER TABLE dbo.DuplicateData ADD Rnk INT 

UPDATE dd1
SET dd1.Rnk = dd2.Rnk
FROM dbo.DuplicateData dd1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, Name1) AS Rnk, PKID
FROM dbo.DuplicateData
) dd2 ON dd1.PKID = dd2.PKID 

DELETE FROM dbo.DuplicateData
where Rnk <> 1
SELECT * FROM dbo.DuplicateData

-- Deleting Duplicate Records using CTE
;
WITH CTE(RowID, ID, Name1)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, Name1) AS RowID, ID, Name1
FROM dbo.DuplicateData
) 

DELETE FROM CTE
WHERE RowID <> 1 

SELECT * FROM dbo.DuplicateData 

Q7. Query to find duplicate values in a table with a unique index

CREATE TABLE dbo.DuplicateData1
(
    ID INT NOT NULL IDENTITY(1,1),
    Value INT,
    CONSTRAINT PK_ID PRIMARY KEY(ID)
)

INSERT INTO dbo.DuplicateData1(Value)
VALUES(1),(2),(3),(4),(5),(5),(3),(5) 

SELECT *
FROM dbo.DuplicateData1

-- Retaining MaxID
SELECT a.*
FROM   dbo.DuplicateData1 a
       INNER JOIN (SELECT Max(ID) AS ID,
                          Value
                   FROM   dbo.DuplicateData1
                   GROUP  BY Value
                   HAVING Count(Value) > 1) b
               ON a.ID < b.ID
                  AND a.Value = b.Value  

SELECT a.*
FROM   DuplicateData1 a
WHERE  ID < (SELECT Max(ID)
             FROM   DuplicateData1 b
             WHERE  a.Value = b.Value
             GROUP  BY Value
HAVING Count(Value) > 1)  

SELECT a.*
FROM   DuplicateData1 a
       INNER JOIN (SELECT ID,
                          Rank()
                            OVER(
                              PARTITION BY Value
                              ORDER BY ID DESC) AS rnk
                   FROM   DuplicateData1) b
               ON a.ID = b.ID
WHERE  b.rnk > 1  

SELECT *
FROM DuplicateData1
WHERE ID NOT IN (SELECT MAX(ID)
                 FROM DuplicateData1
                 GROUP BY Value) 

Q8. Query to split comma separated values to column values

IF Object_id('dbo.Employee') IS NOT NULL
  BEGIN
      DROP TABLE dbo.Employee
  END 

CREATE TABLE dbo.Employee
  (
     ID           INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
     EmployeeName NVARCHAR(255),
     Roles        NVARCHAR(255)
  ) 

INSERT INTO dbo.Employee
            (EmployeeName,
             Roles)
VALUES      ('John Miller',
             'Therapist,Manager'),
            ('Smith Johnson',
             'Manager,Admin,Owner') 

SELECT *
FROM   dbo.Employee 

SELECT *
FROM   dbo.Employee e
       CROSS APPLY String_split(Roles, ',')

Alternatively, We can write our own function for previous versions  of sql server

CREATE FUNCTION [dbo].[Fnsplitstring] (@string    NVARCHAR(MAX),
                                       @delimiter CHAR(1))
RETURNS @output TABLE(
  splitdata NVARCHAR(MAX))
  BEGIN
      DECLARE @start INT,
                         @end   INT
      SELECT @start = 1,
                      @end = Charindex(@delimiter, @string) 

      WHILE @start < Len(@string) + 1
        BEGIN
            IF @end = 0
              SET @end = Len(@string) + 1 

            INSERT INTO @output
                        (splitdata)
            VALUES      (Substring(@string, @start, @end - @start)) 

            SET @start = @end + 1 

            SET @end = Charindex(@delimiter, @string, @start) 

        END
      RETURN
  END 

SELECT *
FROM   dbo.Employee e
       CROSS APPLY dbo.[Fnsplitstring](Roles, ',') 

Another one

CREATE FUNCTION [dbo].Stringsplitxml (@String    VARCHAR(MAX),
                                      @Separator CHAR(1))
RETURNS @RESULT TABLE(
  Value VARCHAR(MAX))
AS
  BEGIN
      DECLARE @XML XML
      SET @XML = Cast(( '<i>'
                        + Replace(@String, @Separator, '</i><i>')
                        + '</i>' ) AS XML)
      INSERT INTO @RESULT
      SELECT t.i.value('.', 'VARCHAR(MAX)')
      FROM   @XML.nodes('i') AS t(i)
      WHERE  t.i.value('.', 'VARCHAR(MAX)') <> ''
      RETURN
  END

SELECT *
FROM   dbo.Employee e
       CROSS APPLY dbo.StringSplitXML(Roles, ',')

 Q9. Query to get aggregated sales data at Month, Quarter, Year, Catagory & Subcategory levels

IF Object_id('dbo.Sales') IS NOT NULL
  BEGIN
      DROP TABLE dbo.Sales
  END 

CREATE TABLE dbo.Sales
  (
     ID             INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
     SaleDate       DateTime,
     ItemName       NVARCHAR(50),
     Catergory      NVARCHAR(50),
     Subcatergory   NVARCHAR(50),
Quantity       INT,
FinalSalePrice MONEY
  ) 

INSERT INTO dbo.Sales
(
SaleDate,
ItemName,
Catergory,
Subcatergory,
Quantity,
FinalSalePrice
)
VALUES
('2019-12-19 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-19 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-19 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-19 12:59:10.217', '90 Minute Body Massage', 'Services', 'Massage', 1, 150.00),
('2019-12-20 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-20 12:59:10.217', 'Hair Color', 'Services', 'Hair', 1, 125.00),
('2019-12-20 12:59:10.217', '45 Minute Body Massage', 'Services', 'Massage', 1, 90.00),
('2019-12-21 12:59:10.217', 'Hair Cut', 'Services', 'Hair', 1, 125.00),
('2019-12-21 12:59:10.217', '60 Minute Body Massage', 'Services', 'Massage', 1, 125.00),
('2019-12-21 12:59:10.217', '120 Minute Body Massage', 'Services', 'Massage', 1, 200.00)

  

SELECT GROUPING_ID(Year(SaleDate), DateName(MONTH, SaleDate), Catergory, Subcatergory, ItemName) AS GroupingID,

       Year(SaleDate)                   AS Year,
       DateName(MONTH, SaleDate) AS Month,
       Catergory,
       Subcatergory,
       ItemName,
       Sum(FinalSalePrice)              AS TotalSales
FROM   dbo.Sales
GROUP  BY GROUPING SETS 

( 

( Year(SaleDate)),

( Year(SaleDate), DateName(MONTH, SaleDate) ), 

( Year(SaleDate), DateName(MONTH, SaleDate), Catergory ), 

( Year(SaleDate), DateName(MONTH, SaleDate), Catergory, Subcatergory ), 

( Year(SaleDate), DateName(MONTH, SaleDate), Catergory, Subcatergory, ItemName ) ) 

Q10. Write An SQL Query To Show Only Even Rows From A Table

IF Object_id('dbo.Sales') IS NOT NULL
  BEGIN
      DROP TABLE dbo.SampleData
  END 

  CREATE TABLE dbo.SampleData
  (
ID INT IDENTITY NOT NULL,
Details nvarchar(50)
  ) 

  INSERT INTO dbo.SampleData
  (
    Details
  )
  VALUES
  ('John Smith'),
  ('Millinda Mechel'),
  ('Marsh'),
  ('Mikel'),
  ('Rosana'),
  ('Joseph'),
  ('Alina') 

SELECT * FROM dbo.SampleData

SELECT * FROM dbo.SampleData WHERE ID % 2 = 0;

  

Q11. Write An SQL Query To Fetch The List Of Employees With The Same Salary

IF Object_id('dbo.Employee') IS NOT NULL
  BEGIN
      DROP TABLE dbo.Employee
  END 

CREATE TABLE dbo.Employee
  (
     ID           INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
     EmployeeName NVARCHAR(255),
     Salary       MONEY
  ) 

INSERT INTO dbo.Employee
            (EmployeeName,
             Salary)
VALUES      ('John Miller',
             11000),
            ('Smith Johnson',
             15000),
('John Wick',
             11000),
('Alan Jon',
             13000) 

SELECT *
FROM   dbo.Employee

SELECT *
FROM   dbo.Employee e1, dbo.Employee e2
WHERE e1.Salary = e2.Salary
AND e1.ID != e2.ID

Q12 Write Query to display employee hierarchy

IF Object_id('dbo.Employee') IS NOT NULL
  BEGIN
      DROP TABLE dbo.Employee
  END 

CREATE TABLE dbo.Employee
  (
     EmployeeID           INT NOT NULL PRIMARY KEY,
     EmployeeName NVARCHAR(255),
     Salary       MONEY,
    ManagerID    INT
  ) 

INSERT INTO dbo.Employee
            (EmployeeID,
          EmployeeName,
             Salary,
          ManagerID)
VALUES      (1000,
             'John Miller',
             11000,
NULL),
            (1001,
'Smith Johnson',
             15000,
1000
),
(1002,
'John Wick',
             11000,
1000),
(1003,
'Alan Jon',
             13000,
1001)

SELECT *
FROM   dbo.Employee

SELECT e.EmployeeID,
       e.EmployeeName,
       m.EmployeeID   AS ManagerID,
       m.EmployeeName AS ManagerName
FROM   dbo.Employee e
       LEFT JOIN dbo.Employee m
              ON e.ManagerID = m.EmployeeID 

;

WITH EmpHierarchy(EmployeeID, EmployeeName, EmployeeLevel, ManagerID, ManagerName)

AS

(

SELECT 

e.EmployeeID, 

e.EmployeeName, 

0 AS EmployeeLevel, 

NULL AS ManagerID, 

CAST('' AS NVARCHAR(255)) AS ManagerName

FROM dbo.Employee e

WHERE e.ManagerID IS NULL

UNION ALL

SELECT  e.EmployeeID, 

e.EmployeeName, 

m.EmployeeLevel + 1, 

m.EmployeeID AS ManagerID, 

m.EmployeeName AS ManagerName

FROM dbo.Employee e

INNER JOIN EmpHierarchy m ON m.EmployeeID = e.ManagerID

)

SELECT * FROM EmpHierarchy

Q13. Write SQL Query To Show One Row Twice In Results From a Table

IF Object_id('dbo.Employee') IS NOT NULL
  BEGIN
      DROP TABLE dbo.Employee
  END 

CREATE TABLE dbo.Employee
  (
     ID           INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
     EmployeeName NVARCHAR(255),
     Salary       MONEY
  ) 

INSERT INTO dbo.Employee
            (EmployeeName,
             Salary)
VALUES      ('John Miller',
             11000),
            ('Smith Johnson',
             15000),
('John Wick',
             11000),
('Alan Jon',
             13000) 

SELECT *
FROM   dbo.Employee

SELECT *
FROM   dbo.Employee
UNION ALL
SELECT *
FROM   dbo.Employee

Q14. Write An SQL Query To Fetch The First 50% Records From A Table

IF Object_id('dbo.Employee') IS NOT NULL
  BEGIN
      DROP TABLE dbo.Employee
  END 

CREATE TABLE dbo.Employee
  (
     ID           INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
     EmployeeName NVARCHAR(255),
     Salary       MONEY
  ) 

INSERT INTO dbo.Employee
            (EmployeeName,
             Salary)
VALUES      ('John Miller',
             11000),
            ('Smith Johnson',
             15000),
('John Wick',
             11000),
('Alan Jon',
             13000) 

SELECT *
FROM   dbo.Employee

SELECT *
FROM   dbo.Employee
WHERE ID <= (SELECT count(ID)/2 from dbo.Employee);

Q15. Write a SQL Query to Check if first alphabet of column is Capitalized

IF Object_id('dbo.Customers') IS NOT NULL
  BEGIN
      DROP TABLE dbo.Customers
  END

CREATE TABLE dbo.Customers
  (
     ID           INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
     CustomerName NVARCHAR(255)
  )

INSERT INTO dbo.Customers
            (CustomerName)
VALUES      ('John Miller'),
            ('smith Johnson'),
('John Wick'),
('alan Jon')

SELECT *
FROM   dbo.Customers 

SELECT *
FROM   dbo.Customers
WHERE SUBSTRING(CustomerName, 1, 1) <> UPPER(SUBSTRING(CustomerName, 1, 1)) COLLATE LATIN1_GENERAL_CS_AS

Q16. Write SQL Query to Schedule Matches between teams such that each team play a match with another.

IF OBJECT_ID('dbo.Team') IS NOT NULL BEGIN DROP TABLE dbo.Team END

CREATE TABLE [Team]
(
     [TeamID] INT
    ,[TeamName] VARCHAR(10)
)
GO

INSERT INTO [Team] VALUES
(1,'Team1'),
(2,'Team2'),
(3,'Team3'),
(4,'Team4')
GO

SELECT * FROM [Team]

SELECT ROW_NUMBER() OVER(ORDER BY e1.[TeamID]) AS MatchID,
          CONCAT(e1.[TeamName] , ' Vs ', e2.[TeamName])
FROM [Team] e1
CROSS APPLY
(
SELECT *
FROM [Team] e2 where e1.[TeamID] < e2.[TeamID]
) e2
order by e1.[TeamID]

Q17. Write SQL Query to Schedule Matches between teams such that each team in Group A play a match with team in Group B.

IF OBJECT_ID('dbo.Team') IS NOT NULL BEGIN DROP TABLE dbo.Team END

CREATE TABLE [Team]
(
     [TeamID] INT,
     [TeamName] NVARCHAR(50),
[Group] NVARCHAR(50)
)
GO

INSERT INTO [Team] VALUES
(1,'Team1', 'A'),
(2,'Team2', 'A'),
(3,'Team3', 'B'),
(4,'Team4', 'B')
GO

SELECT * FROM [Team]

SELECT ROW_NUMBER() OVER(ORDER BY e1.[TeamID]) AS MatchID,
                CONCAT(e1.[TeamName] , ' Vs ', e2.[TeamName])
FROM [Team] e1
CROSS APPLY
(
SELECT *
FROM [Team] e2
WHERE e2.[Group] = 'B'
) e2
WHERE e1.[Group] = 'A'
order by e1.[TeamID]

Q18. List Down Different ways to add date and time

DECLARE @Dt DATE = '2018-08-10',
                    @Tm TIME = '07:48:46.4566667'


SELECT Cast(@Dt AS DATETIME)
       + Cast(@Tm AS DATETIME) [DateTime]

SELECT Dateadd(MILLISECOND, Datediff(MILLISECOND, '00:00:00', @Tm), CONVERT(DATETIME, @Dt)) [DateTime]

SELECT Dateadd(DAY, Datediff(DAY, 0, @Dt), Cast(@Tm AS DATETIME)) [DateTime]

SELECT Cast(Concat(@Dt, ' ', Cast(@Tm AS VARCHAR(12))) AS DATETIME) [DateTime]

Q20. Write SQL Query's to list down all stored procedures in a database

SELECT DISTINCT o.name,
                o.xtype
FROM   sysobjects o
WHERE  o.xtype = 'P'










SELECT *
FROM SYS.PROCEDURES 

Q21. Write SQL Query to fetch all stored procedures related to table

SELECT DISTINCT o.name,
                o.xtype
FROM   syscomments c
       INNER JOIN sysobjects o
               ON c.id = o.id
WHERE  o.xtype = 'P'
       AND c.text LIKE '%Invoice%' -- Table Name

Q22. T-SQL Query to get all dependency of stored procedure

;WITH stored_procedures
     AS (SELECT oo.name                       AS table_name,
                Row_number()
                  OVER(
                    partition BY o.name, oo.name
                    ORDER BY o.name, oo.name) AS row
         FROM   sysdepends d
                INNER JOIN sysobjects o
                        ON o.id = d.id
                INNER JOIN sysobjects oo
                        ON oo.id = d.depid
         WHERE  o.xtype = 'P'
                AND o.name LIKE '%GetPOInvoiceForVendorEmail%')
SELECT Table_name
FROM   stored_procedures
WHERE  row = 1  

Q23. SQL Query to rebuild all indexes

EXEC Sp_msforeachtable

  @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

GO

EXEC Sp_updatestats

GO 

Q24. Find Data Size of all tables in database

SELECT sob.name        AS Table_Name,

       Sum(sys.length) AS [Size_Table(Bytes)]

FROM   sysobjects sob,

       syscolumns sys

WHERE  sob.xtype = 'u'

       AND sys.id = sob.id

GROUP  BY sob.name 

Q25. Get List of tables without Identity Column

SELECT TABLE_NAME

FROM   INFORMATION_SCHEMA.TABLES

WHERE  Table_NAME NOT IN (SELECT DISTINCT c.TABLE_NAME

                          FROM   INFORMATION_SCHEMA.COLUMNS c

                                 INNER JOIN sys.identity_columns ic

                                         ON ( c.COLUMN_NAME = ic.NAME ))

       AND TABLE_TYPE = 'BASE TABLE'