Search the Blog

Showing posts with label Sql Constraints. Show all posts
Showing posts with label Sql Constraints. Show all posts

Sunday, May 9, 2021

SQL INTERVIEW QUESTIONS FOR 15 MONTHES EXPERIANCE

 

This post have all the query and synatx which a biggner and experianced SQL users need to know and use in their daily working Life.

1 :- SQL INSERT


Type of SQL Insert

Syntax 1 :-Template for all columns update --

insert into Table_name value('ABC','ART','',.....,'Value n')
--   Note data type of each column should be match



Syntax 2:- Template for slelect columns update --

insert into Table_name(col_1,col_2,,,,,,,, col_n) value('ABC','ART','',.....,'Value n')


Syntax 3:- If all rows have to be fill up

insert Into Parameter_Blood_Group values('B0100A000004','GEN00A000001','AB-','AB-','0','2','Approved','','2019-05-04 17:27:58.913')



Syntax 4:- If select rows have to be fill up

insert Into Parameter_Blood_Group(URN_No,Co_code) values('B0100A000004','GEN00A000001')
Insert Query in SQL Server

Syntax 5:-Without index column is exists in Table

Example 1- Insert into 'table_name' (Col_1,Col_2,......,Col_n) 
                                      Values(Value_1,Value_2,.....,Value_n);

Initial Table - Table Name- User_Detail

   
SQL Insert Query Without Index



    
Example 2- Insert into User_Detail (User_name,User_Mobile,User_email_id,Pin_code)

                                     Values('Programming    Logic                                                       World',1234512345,'programminglogicworld@gmail.com',123456);


SQL Insert Query Without Index

See Also - SQL Query without Index





SQL LIKE Operator


Sql Query to select similar kind of data which have perticular pattern comman in their specipic data column

Basic Format

SQL Like operato


SELECT col1, col2, ...,columnN
FROM table_name
WHERE columnN LIKE pattern;

Demo-

SELECT * FROM Student
WHERE StudentName LIKE 'a%';

Note:-  Like Operator Consume Maximum Resources

SQL Like Opertaor


Like Operator Usage

Like Operator Usage


Search Type 1:-

All Students with a column name 'StudentName' that start with "s" in the Second position:
SELECT * FROM Students
WHERE Student_Name LIKE '_s%';



Search Type 2:-

All Students with a column name 'StudentName' that have "s" in the Third position:
SELECT * FROM Students
WHERE Student_Name LIKE '__s%';



Search Type 3:-

All Students with a column name 'StudentName' that have "s" in the Fourth position:
SELECT * FROM Students
WHERE Student_Name LIKE '___s%';

Search Type 4:-

All Students with a column name 'StudentName' that start with "s" 

SELECT * FROM Students

WHERE Student_Name LIKE 's%';


Search Type 5:-

All Students with a column name 'StudentName' that start with "sa" 
SELECT * FROM Students
WHERE Student_Name LIKE 'sa%';


Search Type 6:-

All Students with a column name 'StudentName' that start with "saw" 
SELECT * FROM Students
WHERE Student_Name LIKE 'saw%';



Search Type 7:-

All Students with a column name 'StudentName' that end with "s" 
SELECT * FROM Students
WHERE Student_Name LIKE '%s';


Search Type 8:-

All Students with a column name 'StudentName' that end with "sa" 
SELECT * FROM Students
WHERE Student_Name LIKE '%sa';


Search Type 9:-

All Students with a column name 'StudentName' that end with "saw" 
SELECT * FROM Students
WHERE Student_Name LIKE '%saw';





SQL Distinct Opeartor

SQL Distinct Opeartor


SELECT DISTINCT column1, column2, ...

FROM table_name;



Query:- 

Select Distinct it_name,it_code from Item_Master


Note- we use Distinct keyword to find unique data of columns.




SELECT DISTINCT column1, column2, ...

FROM table_name;

DEmo Table Data Rows- 10000


Query-


Select Distinct it_name from Item_Master

Result- 10

Note- we use Distinct keyword to find unique data of column.




Select Distinct it_name,it_code from Item_Master

Result- 159

Note- we use Distinct keyword to find unique data of two columns. 




Select Distinct it_name,it_code,uom from Item_Master

Result-1547

Note- we use Distinct keyword to find unique data of three Columns. 


Select Distinct it_name,* from Item_Master

Result- 5000

Note- we use Distinct keyword to find unique data of column.




Select Distinct it_name,it_code,* from Item_Master

Result- 5000

Note- we use Distinct keyword to find unique data of two columns. 



Select Distinct it_name,it_code,uom,* from Item_Master

Result- 5000



Note- we use Distinct keyword to find unique data of three Columns. 


   As all programming language have operator for conditions in the same way SQL have following operator
 1- AND
 2- OR
 3- NOT

1- AND
    Operator is used when we want both the conditions must be true.
Syntax- Select * from table_name
where condition1 and condition2 and condition3


2- OR
    Operator is used when we want only single the conditions must be true.
Syntax- Select * from table_name
where condition1 or condition2 or condition3

3- NOT
    Operator is used when we want  conditions must not be true.
Syntax- Select * from table_name

where  Not condition1 




JOIN


SQL Joins is used to Compose the data of two table into a single view-

SQL Joins are Five Types-

      1- Inner Joins
      2- Left Outer Joins
      3- Right outer Joins
      4- Full Joins
      5- Self Joins

Inner Joins syntax

Inner Join Demo Image in two tables

   select * from table A
   INNER join table B on A.ID=B.ID

Result- Only the common data from both table which have ID in both table


Left Joins syntax

left outer join demo image

   select * from table A
   LEFT outer join table B on A.ID=B.ID

Result- All data from table A and matched data from table B where id of a Exists in B


Rights Joins Syntax


right outer join image

   select * from table A
   RIGHT outer join table B on A.ID=B.ID

Result- All data from table B and matched data from table A where id of a Exists in A


Full joins Joins Syntax

full join image

   select * from table A
   FULL OUTER join table B on A.ID=B.ID

Result- All data from table A and B and All A data whose ID not in B and A data whose ID not in B and Common of A& B no duplicate ID

Self  JoinsSyntax

Self Join  Image

   select * from Sawanchauhan A
   left outer join Sawanchauhan B
where A.PermanentCity=B.CurrentCity

Result- All data from table (SawanChauhan) where permanenet city and current city are same only.


Where Clause Uses

Sql where keyword is used to filter the records 

Syntax- Select Col1,Col2,Col3 from table_name where condition;

select item_name,Item_code,Item_uom from item_master where created_By_Id=12




More Questions -
1. Why Truncate is a example of DDL statement? 2. Difference between cluster index and non-cluster index ? 3. Difference between CTE and Temp. table? 4. Difference between Rank and dense rank. 5. explain type of cursor ? 6. Query to find duplicate rows in table? 7. What are the differences between Stored Procedure and the dynamic SQL? 8. What is Collation? 9. What are Magic Tables in SQL Server? 10. What is the difference between varchar and nvarchar types? 11. What are the differences between left join and outer left join ? 12. explain types of user defined functions?

Monday, September 7, 2020

SQL server Upgrade

 

When you upgrade to a new version of SQL Server, there are some critical things you should do to help avoid any surprise SQL Server performance issues. I mean new version  of SQL Server is 2016,2017 or 2019, Because Microsoft  closed its support up to some extent to older version


Why need the Requirement to upgrade the Version?

Basically it is because Microsoft ended Its support of some versions.

There are two type of Support

1- MainStream Support

2- Extended Support


SQL Server Versions

MainStream Support

Extended Support

SQL Server 2005

April 12, 2011

April 12, 2016

SQL Server 2008 or R2

July 8, 2014

July 9,2019

SQL Server 2012

July 11, 2017

July 12, 2022

SQL Server 2014

July 9, 2019

July 9, 2024

SQL Server 2016

July 13, 2021

July 14, 2026

SQL Server 2017

Oct 11, 2022

Oct 12, 2027

SQL Server 2019

July 1, 2025

Aug 1, 2030


For Upgrading any Version there are some preparations which need to be taken care.

1- Find the Upgrade Blockers. 

2- Clear Understanding of Version and Editions.

3- Clear Understanding of Features introduced and obsolete

3- Make a List of objects which use the features which  are obsolete from the newer version and currently in use.

4- Clear Hardware Requirement for newer Versions. 






















For this Activity i am upgrading the Server from 2012 to 2017.


Process to Upgrade the SQL Server Version.

There are 3 ways to upgrade

  1. - In-Place Upgrade

  2. - Side-by-Side Upgrade

  3. - Rolling Upgrade


Steps for an In-Place Upgrade 

In-place upgrades are the easiest to perform, but the most difficult to rollback should there be any issues. The steps involved in an in-place upgrade are as follows:

  1. Verify that backups exist for all databases (user and system). 

  2. Review the list of requirements for SQL server 2017 and install whatever is needed.

  3. Install SQL Server 2017 

  4. Restore the Backup

Side-by-Side Upgrade

  • 1- Backup all the database

  • 2-Script out any and all necessary system objects.

            3-Review the list of requirements for SQL server 2017 and install whatever is needed.

  • 4- Run the script to create the Object

  • 5-Select database(s) to migrate and it take offline. 

  • 6-Migrate database to new instance. Repeat for each database.

  •  

 

 

 

Rolling Upgrade


1- Select any High-Availability 

2- Install new Server instance 2017 and make it  as a Secondary node

3- Fail the Primary Node now New Instance will be active and will be Primary Node

 

Checklist need to Perform Before Planning to Upgrade about version of Server


1-Checking of Deprecated Features  in new version

SELECT * FROM sys.dm_os_performance_counters   WHERE object_name  = 'MSSQL$JFT:Deprecated Features   

2- Get the List top indexes which are in use maximum times in the current version.

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name

      ,IX.name                     AS Index_Name

      ,IX.type_desc                 AS Index_Type

      ,SUM(PS.[used_page_count]) * 8     IndexSizeKB

      ,IXUS.user_seeks AS             NumOfSeeks

      ,IXUS.user_scans AS             NumOfScans

      ,IXUS.user_lookups AS             NumOfLookups

      ,IXUS.user_updates AS             NumOfUpdates

      ,IXUS.last_user_seek AS         LastSeek

      ,IXUS.last_user_scan AS         LastScan

      ,IXUS.last_user_lookup AS         LastLookup

      ,IXUS.last_user_update AS         LastUpdate

FROM sys.indexes IX

INNER JOIN sys.dm_db_index_usage_stats IXUS 

ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats PS 

on PS.object_id=IX.object_id

WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1

GROUP BY 

OBJECT_NAME(IX.OBJECT_ID) ,

IX.name ,

IX.type_desc ,

IXUS.user_seeks ,

IXUS.user_scans ,

IXUS.user_lookups,

IXUS.user_updates ,

IXUS.last_user_seek ,

IXUS.last_user_scan ,

IXUS.last_user_lookup ,

IXUS.last_user_update

3- Current Used Space at current instance and Available Space in the new instance.

4- Current OS Hardware Configuration.

5- Scripting of all user defined Objects.

 

Translate