sql concepts

 *SQL DDL Commands*

In SQL, DDL means Data Definition Language. The Sql Server DDL commands are used to create 

and modify the structure of a database and database objects like tables, views, procedures,....

*SQL DML Commands*

DML means Data Manipulation Language in Sql Server. As its name suggests, these Sql Server DML 

commands will perform data manipulation( manipulate data presented in the server).

/*Interview Question: What are the differences between P.K. and F.K.?

The PRIMARY KEY constraint uniquely identifies each record in a table.

primary key has to be unique and not empty(not null) and a table can have at most only ONE primary key; 

and in the table, this primary key can consist of single or multiple columns (fields).

A FOREIGN KEY is a field (or collection of fields) in one table that refers to 

another column(usually primary key) in another table and unlike P.k. it can be null and have duplicated

values and we can have several FKs in one table

The table containing the foreign key is called the child table, and the table containing the candidate key 

is called the referenced or parent table

*An aggregate function performs a calculation on a set of values and returns a single value. 

Except for COUNT(*), aggregate functions ignore null values. 

Aggregate functions are often used with the GROUP BY clause of the SELECT statement.

Transact-SQL provides the following aggregate functions:

AVG: calculating the mean or average of  aset of numeric values

COUNT: count the number of rows

COUNT_BIG: is used for counting large result sets

MAX

MIN

STDEV

STDEVP

STRING_AGG

SUM: calculates the sum of numeric values

VAR: calculating the variance of a set of numeric values

VARP:calculating the variance of population of a set of numeric values*/

/* so far we talked about where clause, group by, order by, now we  are going to talk about having clause, where is using in the situation that we have some conditions

on rows not columns, having is using when we want to apply condition on the results that we got from grouping*/

--Ex: find or retrieve the sum of the price for each category of items if the sum of the price is less than $2.3

--we want to add conditions to filter groups. For filtering groups, you must use Having

--------------------------------------------------

/*Having:The HAVING clause in SQL is used to filter the results of a GROUP BY operation based on a condition or an aggregate function's result.

It allows you to specify conditions that apply to grouped rows, and it is typically used in combination with the GROUP BY clause*/

-------------------------------------------------

/*Here's when you would use the HAVING clause:

1: Aggregating Data: When you have performed a GROUP BY operation to group rows based on a particular column or columns and you want to filter the groups 

or aggregations based on some condition.

2: Filtering Grouped Data: When you want to apply a condition to the result of an aggregate function (e.g., SUM, COUNT, AVG, etc.) for each group.

3: Post-Aggregation Filtering: When you need to filter the result set after aggregation. It's different from the WHERE clause, 

which filters rows before aggregation.

For filtering records(obs)(rows) we use the where clause but

For filtering groups, you must use the Having clause

1.for adding condition to filter records(observation) we use where clause after from clause and 

for adding conditions to filter groups you have to use the "Having" clause after group by clause

2. we are not allowed to use an aggregate function in where clause, If you want to filter rows based on the results of an aggregate function, 

you typically use the HAVING clause instead of the WHERE clause. The HAVING clause is used in conjunction with GROUP BY to filter the results of aggregate functions.

*/

--Aggregate functions (part2)

/*we use aggregate function to summarize one numeric column or to do bivariate analysis for 

numerical Vs. categorical columns for example we want to find average of salary for each department

Hint: For conducting Bivariate Analysis for Numerical Vs. Categorical column you must Group by categorical column

Note: we use aggregate function for Multi-Variate analysis as well in this situation we must

Group by all categorical column*/

/*Recall that if you have non-aggregate column in select besides aggregate function , you must

group by all non-aggregate columns*/

-- So important Hint : you can use Aggregate Functions only in the SELECT list and in the HAVING clause

-- Note: you have to group by all non-aggregate columns that you called them in select

-- Note: Each function eliminates(ignores) NULL values except count(*)

Having Vs. Where: In select statement If you want to add any condition to each records you 

should use the where clause after the from clause, but If you want to add any condition to each group 

you should use having clause after group by

unlike having we are not allowed to use aggregate functions in where clause*/

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without 

 returning any duplicate rows.

To use this UNION clause, each SELECT statement must have

1.The same number of columns selected

2.The same number of column expressions

3.The same data type and

4.Have them in the same order

But they need not have to be in the same length.

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

The same rules that apply to the UNION clause will apply to the UNION ALL operator.

subquery: A subquery, also known as an inner query or nested query, is a SQL query embedded within another 

SQL query. It is a powerful feature of SQL that allows you to retrieve data from one or more tables based 

on the results of another query. Subqueries are typically enclosed within parentheses and used in various parts

of SQL statements, including SELECT, WHERE, FROM, and HAVING clauses.*/

-In SQL, a view is a virtual table based on the result set of an SQL statement.

The difference between a view and a table is that 

1. views are definitions built on top of other tables 

(or views), and do not hold data themselves. If data is changing in the underlying table, 

the same change is reflected in the view.

2. they are not physically stored.

Space savings: Views take very little space to store since they do not store actual data.

it is a virtual table.

Note: All views can be dropped

--DROP VIEW does not affect any tuples of the underlying relation (table) 

--However, DROP TABLE will delete the table and also make the view  unusable

So We use a view

-For presenting different information to different users

-a view is just a SELECT statement that has been saved in the database (more or less, depending on 

your database).

-Instead of sending the complex query to the database all the time, you can save the query as a 

view and then SELECT * FROM that view

--Versatile Use: Views can be used in various parts of your database system, including within queries, stored procedures, or even within other views. 

This versatility allows you to encapsulate complex logic, make data access more convenient, and create a layered approach to your database.

--Enhanced Data Security: One of the key advantages of views is their role in enhancing data security. By creating views that expose only certain 

columns from a table, you can effectively control which data is exposed to end users. This is particularly useful for hiding sensitive or confidential 

information. Some database systems also allow views to have different security settings, which means that certain users or roles might have access to 

one view while being restricted from another.

       

Modifying Views: When you modify a view, you're essentially modifying the underlying table(s) through that view. This means that any INSERT, UPDATE, 

or DELETE operations performed on the view are translated into corresponding operations on the base table(s).

Updateable Views: Not all views are updateable. There are specific rules and restrictions to determine whether a view can be updated 

(i.e., used for INSERT, UPDATE, or DELETE operations). These rules help maintain data consistency and integrity.

SELECT, not SELECT DISTINCT: One common rule for updateable views is that they should be based on a simple SELECT statement, and they should 

not use SELECT DISTINCT. Using SELECT DISTINCT can introduce ambiguity when attempting to update the underlying data because distinct values might not

be directly mappable to individual rows in the base table(s).

Sufficient Attributes in SELECT Clause: For a view to be updateable, the SELECT clause of the view should include enough attributes (columns) to 

determine how to insert data into the view as well as the base table(s. This ensures that the database system can establish which values should be 

inserted where. Any other attributes not explicitly defined in the view might be filled with NULL values or default values, depending on the database

system and table schema.

 Views are virtual tables that provide a way to retrieve data from one or more underlying tables or views. They are essentially saved SQL

queries that generate a result set when queried, but they don't hold or store the data themselves. Instead, views display the data from the underlying 

tables or views in a structured and organized manner based on the query that defines them. Any changes in the data are reflected in the underlying tables,

and the view simply represents the data as it exists in those tables at the time of the query.*/

When we modify a view, we actually modify a table through a view. Many views are not updateable. 

Here are rules have been defined in SQL for updateable views:

-SELECT not SELECT DISTINCT

-The list in the SELECT clause must include enough attributes that will allow us to insert tuples into the view as well as table.

All other attributes will be filled

out with NULL or the proper default values

/*Creating temporary variables*/

----------------------------

/*Data Storage: Temporary variables allow you to store and manipulate data during the execution of a script or batch. This can be useful for calculations,

intermediate results, or simply to hold values that you need at various points in your SQL code.

Parameterization: Variables can be used to parameterize your SQL queries. Instead of hardcoding values directly into your queries, you can use variables

to make your code more dynamic and reusable. You can set the value of the variable before executing a query.

Control Flow: Variables can be used to control the flow of your SQL script. You can use conditional statements to make decisions based on the values 

stored in variables. For example, you might run different queries or perform different actions based on the value of a variable.

Improved Readability: Using variables can make your SQL code more readable and easier to maintain. Instead of having long, complex expressions or hardcoded

values throughout your script, you can use variables with meaningful names to improve clarity.*/

-- Creating temporary variables

--** Hint:this is temporary variable you must run whole batch together(highlight declare, set, and select then Execute) 

--  give it a data type and  initialize variable

explicit: by using cast or convert function 

--the syntax of convert and cast is a little different and only you can use convert for date and time 

/*Convert Vs. Cast:

1.convert can use with date/time and 

2. syntax of them are difference:

for cast function we specify the expression that we want to convert then as keyword then

new data type CAST(expression AS datatype)

for convert function we sepcify new data type then comma then expression that you want to 

convert :convert(datatype, expression, style)

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and 

over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, 

and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on 

the parameter value(s) that is passed.

-Procedure is stored in cache  area of memory when the stored procedure is first executed so that 

it can be used repeatedly. 

**So Importnat: SQL Server does not have to check syntax and recompile it every time the stored 

procedure is run i.e. When you call a stored procedure for the first time, SQL Server creates an

execution plan and stores it in the cache. In the subsequent executions of the stored procedure,

SQL Server reuses the plan so that the stored procedure can execute very fast with reliable

performance.

-It can accept input parameters, return several output values as parameters, or return success or

failure status messages.

*/

/*FYI:

Benefits of using a stored procedure

1.SQL Server does not have to check syntax and recompile it every time the stored 

procedure is run i.e. When you call a stored procedure for the first time, SQL Server creates an

execution plan and stores it in the cache. In the subsequent executions of the stored procedure,

SQL Server reuses the plan so that the stored procedure can execute very fast with reliable

performance.

2.It can be easily modified: We can easily modify the code inside the stored procedure without 

the need to restart or deploying the application. For example, If the T-SQL queries are written 

in the application and if we need to change the logic, we must change the code in the application 

and re-deploy it. SQL Server Stored procedures eliminate such challenges by storing the code in

the database. so, when we want to change the logic inside the procedure we can just do it by 

simple ALTER PROCEDURE statement.

3.Reduced network traffic: When we use stored procedures instead of writing T-SQL queries at the 

application level,only the procedure name is passed over the network instead of the whole T-SQL 

code.

4.Reusable: Stored procedures can be executed by multiple users or multiple client applications 

without the need of writing the code again.

5.Security: Stored procedures reduce the threat by eliminating direct access to the tables. 

we can also encrypt the stored procedures while creating them so that source code inside the 

stored procedure is not visible.

*/

/* I will talk about function later but you should know:

Difference between Stored Procedure and Function in SQL Server:

-***Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT 

section whereas Function can be.

-****The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas 

Function allows only SELECT statement in it.

for example :you can update tables by procedures but you can't update tables by functions

-An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot 

be used in a Function.

-We can use Transactions in Procedure whereas we can't use Transactions in Function.

In this syntax:

1)The sp_SumOfFourAndThree is the name of the stored procedure.

2)The AS keyword separates the heading and the body of the stored procedure.

3)If the stored procedure has one statement, the BEGIN and END keywords surrounding the statement 

are optional. However, it is a good practice to include them to make the code clear.

Note that in addition to the CREATE PROCEDURE keywords, you can use the CREATE PROC keywords to 

make the statement shorter.

--proc=procedure

--Exec=Execute

*Stored procedure

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and 

over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, 

and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on 

the parameter value(s) that is passed.*/

when you are using return or output:In Execution time You must assign

the result of procedure in Temporary variable and then print or select

it , 

so you need declaring taht temporary varible and its data type before 

execution i.e. for execution: :

1)you have to declare output variables(local variables)

2)for seeing the result you have to use print or select

Note:You can use return just whenever 

1.you have single output and 

2.its data type is intiger*/

--Method # 3: by using output

/*Return vs output

When you want to return one or more items with a data type then it is better to use an 

output parameter.

Generally, use an output parameter for anything that needs to be returned Except When you

want to return only one item with only an integer data type then it is better to use a return

value.*/

/*Function

1)System Functions: All the built-in functions supported by the Server called System functions in SQL

Server. We don’t have to bother about the logic inside them because they cannot be modified. 

For example, Mathematical Functions, Ranking Functions, String Functions, etc. 

2)User Defined Functions(UDF):

SQL Server supports three types of user-defined functions:

1.Scalar Function or traditional scalar functions: It is a function that returns a single value. 

Generally, we have to define the function body between BEGIN … END block, but for inline scalar function, you 

   can omit them. We can use any SQL data type as the return type except text,

   image, ntext, cursor, and timestamp.

2.Inline Table valued Functions: This function returns a table data type based on a single SELECT 

Statement

3.Multi Statement Table valued Functions: This function also returns a table. But, unlike the 

  inline table valued function, we can use multiple select

  statements inside the function body.

Note:Please specify the return value, and it should match the Data Type. It can be a single value or 

     Table

/*Difference between Stored Procedure and Function in SQL Server:

-1.Stored Procedures cannot be used directly in the SQL statements anywhere in the WHERE/HAVING/SELECT section, 

instead they are executed as separate statments,whereas Functions can be used within SQL statements in the WHERE/HAVING/SELECT 

sections. They can be treated as expressions that return a value within a query.

-2.Stored Procedures: Stored procedures can contain both SELECT and Data Modification Language (DML) statements 

like INSERT, UPDATE, and DELETE. They are suitable for tasks that involve data manipulation and retrieval.

Functions: Functions are primarily designed for SELECT statements. They are intended for calculations and return 

a single value. Functions cannot contain DML statements.

for example :you can update tables by procedures but you can't update tables by functions

-3.An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be 

used in a Function.

-4.We can use Transactions in Procedure whereas we can't use Transactions in Function.

--inline table_value function example

/*In the case of an Inline Table-Valued Function, the body of the function will have

only a Single Select Statement prepared with the “RETURN” statement. And here, we need 

to specify the Return Type as TABLE by using the RETURNS TABLE statement. 

Points to Remember:

1.Return Type as TABLE: For an Inline Table-Valued Function, the return type is specified as TABLE. 

This is different from scalar functions where you specify a scalar data type as the return type.

2. Single SELECT Statement: The body of the function contains a single SELECT statement, which defines the structure of the table that will be returned. 

This SELECT statement determines the columns and data types of the resulting table.

3.No BEGIN/END Blocks: Unlike scalar functions or stored procedures, Inline Table-Valued Functions do not require the use of BEGIN and END blocks. This is because they are

designed to return the result of a single SELECT statement.

4.Structure of Returned Table: The structure of the table that the function returns is determined by the columns selected in the SELECT statement. 

Each column's data type and name in the result set will match the corresponding columns in the returned table..*/

/*Ex:create a fuction that takes a value and show us items that are more expensive than that 

value from table price*/

 Multi-Statement table-valued function example

/*The Multi-Statement Table Valued Function in SQL Server is the same as the Inline 

Table-Valued Function means it is also going to returns a table as an output but with the 

following differences.

1.The Multi-Statement Table-Valued Function body can contain more than one statement. 

In Inline Table-Valued Function, it contains only a single Select statement prepared 

by the return statement.

2.In Multi-Statement Table-Valued Function, the structure of the table returned from the

function is defined by us. But, in Inline Table-Valued Function, the structure of the

table is defined by the Select statement that is going to return from the function body.*/

/* maybe you have ine question in your exam from this part

What are the differences between Inline and Multi-Statement Table-Valued Functions in SQL Server?

1.The Multi-Statement Table-Valued Function body can contain more than one statement. 

In Inline Table-Valued Function, it contains only a single Select statement prepared 

by the return statement.

2.In an Inline Table-Valued Function, the returns clause cannot define the structure of the table 

that the function is going to return whereas in the Multi-Statement Table-Valued Function the

returns clause defines the structure of the table that the function is going to return.

3.The Inline Table-Valued Function cannot have BEGIN and END blocks whereas the Multi-Statement 

Table-Valued Function has the Begin and End blocks.

4.It is possible to update the underlying database table using the inline table-valued function 

but it is not possible to update the underlying database table using the multi-statement 

table-valued function.

5.Inline Table-Valued functions are better for performance than the Multi-Statement Table-Valued

function. So, if the given task can be achieved using an Inline Table-Valued Function, 

then it is always preferred to use Inline Table-valued Function over the Multi-Statement

Table-Valued function.

*/

/*create a fuction that takes a value and show us items that are more expensive than that 

value from table price*/

--Method #2 : By using subquery : We are looking for Id in tblproducts that not belongs to productId in tblproductsales

--select* from tblProducts

--select* from tblProductSales

-- in inner select query we are looking for a id that they are not null, so by user outer one we are looking for a id that it is not in inner query ids

--Method #3 :using except