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')

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

   

    
Example 2- Insert into User_Detail (User_name,User_Mobile,User_email_id,Pin_code)
                                     Values('Programming    Logic                                                       World',1234512345,'programminglogicworld@gmail.com',123456);

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

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

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

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

   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

   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

   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

   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

   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?

Top 1000 SQL Query

 

SQL SELECT Statement

SELECT column1, column2, ... FROM table_name; 

SELECT Student_Id, Student_Name FROM Students;

 

 

SELECT * FROM table_name; 

 SELECT * FROM Students;

 

SQL SELECT DISTINCT Statement

SELECT DISTINCT column1, column2, ... FROM table_name;

 SELECT Student FROM Students;

SELECT DISTINCT  Student FROM Students;

 

 

SQL WHERE Clause

SELECT column1, column2, ...FROM table_name WHERE condition; 

Operator-Equal

 SELECT * FROM Students WHERE Student_Name='Sajan';

Operator-Greater than

 SELECT * FROM Students WHERE Salary > 1000

Operator-Less than

 SELECT * FROM Students WHERE Salary < 1000

Operator-Greater than or equal

 SELECT * FROM Students WHERE Salary >= 1000

Operator- Less than or equal

 SELECT * FROM Students WHERE Salary <= 1000

Operator-  Not equal

 SELECT * FROM Students WHERE Salary <> 1000

 SELECT * FROM Students WHERE Salary != 1000

Operator-  BETWEEN

 SELECT * FROM Students WHERE Salary  BETWEEN 1000 AND 2000

Operator-  LIKE

 SELECT * FROM Students WHERE  Student_Name Like '% SQL%'

Operator-  IN

 SELECT * FROM Students WHERE  Stalary IN (1000,2000,3000)

SQL AND

 SELECT column1, column2, ... 

FROM table_name 

WHERE condition1 AND condition2 AND condition3 ...;
 

Select * from Student where Salary =1000 AND Student_Name Like '% SQL%'

 

SQL OR

 SELECT column1, column2, ... 

FROM table_name 

WHERE condition1 OR condition2 OR condition3 ...;
 

Select * from Student where Salary =1000 OR Student_Name Like '% SQL%'

 

SQL NOT

 SELECT column1, column2, ... 

FROM table_name 

WHERE condition1 OR condition2 ! condition3 ...;
 

Select * from Student where Salary =1000 ! Student_Name Like '% SQL%'

 

SQL ORDER 

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;  

 Select * from Student Order by Student_name

SQL INSERT INTO Statement

With column name

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);  

INSERT INTO Student(Student_name,Salary)
VALUES ('Sajan', 10000); 

 

 Without Column Name

INSERT INTO table_name
VALUES (value1, value2, value3, ...);  

 INSERT INTO Student VALUES ('Sajan', 10000);   

 

SQL NULL Values

IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;  

SELECT Student_Name
FROM Student
WHERE Salary IS NULL;  

 

IS NOT NULL Syntax

SELECT Student_Name
FROM Student
WHERE Salary IS NOT NULL;  

SQL UPDATE Statement

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; 

Updating the Single Row

UPDATE Student
SET Student_Name = 'Sawan', Salary= 1000000
WHERE StudentID = 1; 

 

Updating the All Row

UPDATE Student
SET Salary= 1000000
  

SQL DELETE Statement

Delete Single row of data

DELETE FROM table_name WHERE condition;

DELETE FROM Student WHERE StudentName = 'Sawan'; 

 

Delete all records

DELETE FROM Student;

 

SQL TOP Clause

SELECT TOP number | percent column_name(s)
FROM table_name
WHERE condition;

 

SELECT TOP 10 Salary
FROM Student_name
WHERE where salary <100000; 

 

SELECT TOP 50 percent Salary
FROM Student_name
WHERE where salary <100000;  

 

SQL MIN() Functions

SELECT MIN(column_name)
FROM table_name
WHERE condition; 

SELECT MIN(Salary)
FROM
Student_name WHERE where salary <100000;  

 

SELECT MIN(Salary)
FROM
Student_name ;  

SQL  MAX() Functions

SELECT MAX(column_name)
FROM table_name
WHERE condition;  

SELECT MAX(Salary)
FROM
Student_name WHERE where salary >100000;   

 

SELECT MAX(Salary)
FROM
Student_name ;    

 

The SQL COUNT() Functions

 SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SELECT COUNT(StudentID)
FROM Student;

The SQL AVG() Functions

 SELECT AVG(column_name)
FROM table_name
WHERE condition; 

SELECT AVG(Salary)
FROM Student;

The SQL  SUM() Functions

SELECT SUM(column_name)
FROM table_name
WHERE condition;

 SELECT SUM(Salary)
FROM Student;

 

SQL LIKE Operator

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern; 

 

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

 

SQL IN Operator

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);  

SELECT StudentName
FROM
Student WHERE Salary IN (1000,2000,3000);  

 

SELECT StudentName
FROM
Student WHERE Studentid IN (Select Studentid from Student Where Salary>2000);