Monday, November 30, 2020
Thursday, November 5, 2020
SQL Server Inportant Inteview Questions
Difference between Stored Procedure and Function in SQL Server
1-The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.2- Functions can have only input parameters for it whereas Procedures can have input or output parameters.
3-Functions can be called from Procedure whereas Procedures cannot be called from a Function.
4-The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
5-Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
6-Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
7-Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
8- Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
9-An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
10-We can use Transactions in Procedure whereas we can't use Transactions in Function.
Difference between CTE and Temp Table and Table Variable
CTE:-
It is a temporary result set and typically it may be a result of complex sub-query. Unlike the temporary table, its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with a semicolon.
Temporary Tables:-
Temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside the Tempdb database.
Local Temp Table:-
Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. The local temporary table name is stared with a single hash ("#") signThe scope of Local temp table exists to the current session of the current user means to the current query window. If you will close the current query window or open a new query window and will try to find above-created temp table, it will give you the error.
Global Temp Table:-
Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. The global temporary table name is stared with double hash ("##") sign.
Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.
Table Variable:-This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of the batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index.
NOTE:----
Temp Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, an index like normal tables.
CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than the Tempdb database. You cannot create an index on CTE.
Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of a batch. This is also created in the tempdb database but not the memory.
Thursday, October 15, 2020
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);
Subscribe to:
Posts (Atom)