SQL Server Database Details

SQL Query Execution flow:-

The phases involved in the logical processing of an SQL query are as follows:

1. FROM clause
2. ON clause
3. OUTER clause
4. WHERE clause
5. GROUP BY clause
6. HAVING clause
7. SELECT clause
8. DISTINCT clause
9. ORDER BY clause

TOP clause

--Subquery: -

It is SQL query which is placed inside the another SQL query.

• Scalar Subquery:- Which have one row and one column
• MultiRow Subquery:- a) Which have one row and multiple column 
  b) Which have one column and multiple column 
• Correlated Subquery :- where inner subquery is depend on outer query
• Nested Sub Query:- query inside have another subquery have another subquery called nested subquery.

Update with Subquery:-  check link to know how to write subquery with update statement as below link :- 

SQL update with correlated subquery (bangtech.com)

• --About Subquery create the employee table :- 
• --Subquery:- It is SQL query which is placed inside the another SQL query.

/*

a) Scalar Subquery :- it always return just one row and one column.

b) Multiple Row Subquery :- it return multiple row

i) MultipleColumn :- Single Row and Multiple column

ii) MultipleRow:- Single Column and multiple Row

About join :- 

-- NATURAL Join :- 

/*NATURAL JOIN is similar to INNER join but we do not need to use the ON clause during the join. 

Meaning in a natural join we just specify the tables. We do not specify the columns based on 

which this join should work. By default when we use NATURAL JOIN, SQL will join the two tables 

based on the common column name in these two tables. So when doing the natural join, both the 

tables need to have columns with same name and these columns should have same data type.*/

--Note :- It's not work in SQL Server 

-- CROSS Join:-

/*

CROSS JOIN will join all the records from left table with all the records from right table. 

Meaning the cross join is not based on matching any column. Whether there is a match or not, 

cross join will return records which is basically number of records in left table multiplied 

by number of records in right table. In other words, cross join returns  a Cartesian product.

-- SELF Join:-

/*SELF JOIN is when you join a table to itself. There is no keyword like SELF when doing this join. 

We just use the normal INNER join to do a self join. Just that instead of doing an inner join with

two different table, we inner join the same table to itself. Just that these tables should have 

different alias name. Other than this, SELF join performs similar to INNER join.*/

About Group by :-

Group by :- The main purpose of group by clause is to perform some aggregation (using the aggregate functions like MIN, MAX, COUNT, SUM, AVG) based on the grouped by column values.

Example below:

Below query would group together the data from employee table based on name column and then for each name value, it would count how many records have the same name. SELECT name, COUNT(1) FROM employee GROUP BY name;

About Union :-

UNION operator can be used to combine two different SQL Queries. The output would be the result combined from both these queries. Duplicate records would not be returned.

You can combine two queries using UNION operator if they follow the below rules:

• Both queries must return same same no of columns.
• The columns in both the queries must be in same order.
• Data type of all the columns in both the queries must be same.

Remote Table :- 

A remote table is a table to which you can associate a script definition for retrieval of data from an external data source. Unlike a standard internal table, the data does not come from records in the current instance.

About View:-

Type of View :-

1) Standard view :- which is created normally and change the result of view if the used table data is updated. and it does not occupy any space.

2) Materialized view:-  the view which result change automatic or manually if any changes done with used table in query. we can schdedule it. and it occupy space.

3) Partioned view :-

a) local view :- The view in which the table used in query belong to same database or server called local view.

b) Distributed View :- The view inwhich the table belong to different server/db called distributed view.

*/

DATEADD:- The DATEADD() function adds a time/date interval to a date and then returns the date.
Syntax:- DATEADD(interval, number, date)

select DATEADD(day,-1,getdate()) [Previous Date]

--Output:-

/*

Previous Date

2022-03-30 19:12:43.210

*/

--Trigger :- 

/*

Trigger is a database object which is similar to a stored procedure which will automatically get invoked or executed when the specified event occurs in the database.

The most common type of triggers :-

a) DML triggers:- statement like - Insert, Delete, Or Update.

b) DDL triggers :- Create, Drop, Alter, Trancate.

c) Database triggers (also referred as Logon triggers):- like Logon, Logoff, Startup, Or Shutdown.

*/

--Note :-  Trigger can be defined on the table, view, schema, or database with which the event is assocaited.

/*

Difference between Before Trigger and After Trigger:- 

Benefits of Triggers:-

Trigger can be written for the following purposes:-

-> Gain strong control over Security

->Enforcing referential integrity:- (with input parent can perform insert in chile table like as pk -fk concept).

->Event logging and storing information on table access.

->Preventing invalid transactions.

Create Delete trigger:-

/*INSTEAD OF Triggers:-

An INSTEAD OF trigger allows you to override the INSERT, UPDATE, or DELETE operations on a table or view. The actual DML operations do not occur at all.

The INSTEAD OF DELETE trigger executes instead of the actual delete event on a table or view. In the Instead Of delete trigger example below, when a delete command is issued on the Employee table, a new row is created in the EmpLog table storing the operation as 'Delete', but the row doesn't get deleted.

*/

--Synonyms (Alias Name )

--A synonyms in SQL Server are database objects that give an alternative name to the database objects existing locally or on the remote server. Also, they provide an abstraction layer to protect the application from any changes in the base object.

--In T-SQL scripts, we use 3-part names for referencing database objects. The format is:

--[Database].[Schema].[Object]

-------------------Procedure create without pragma autonomous transaction:

--Store Procedure:- The CREATE PROCEDURE command is used to create a stored procedure.

/*

A Stored procedure in SQL Server, is a collection of queries that are used to achieve some goal. It is a prepared SQL code that is compiled and stored in a database and can be reused multiple times. The primary goal of stored procedures is to hide direct SQL queries from the code and speed up database operations like select, update, and delete.

*/

Sr. No.	Correlated Query	Subquery
	Correlated subquery is nothing but the subquery whose output is depending on the inner query used in that query.	Subquery is query within a query in which the value of inner query assigned to the outer query and outer query is executed.
	Correlated query is the query which is executed after the outer query is executed.	Subquery is the query which is executed before the outer query is executed.
	It’s type of subquery.	The types of subqueries :- Single Row Subquery Multirow Subquery Correlated Subquery Nested Subquery Scalar Subqueries
	Correlated Queries are also called as Synchronized queries.	It’s called inner/outer query. (when statement contain the subquery is called an outer select or outer query. And remaining part called inner query)

 

Database Files and Filegroups :

every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in filegroups for allocation and administration purposes.