We will see about MS Sql Server in this post.

1. Introduction


SQL stands for Structure Query Language. SQL Server is developed by Microsoft. Sql Server is a relational database management system (RDBMS) and also ORDBMS. It is used for the basic functions of storing, retrieving data as required by other applications. It can run on the same computer or on another computer across a network. Sql server is platform dependent and case-insensitive language.

You will find the pdf document at the end of the post.

2. Databases


In MS SQL Server,there are two types of databases available.

  • System databases
  • User Databases

2.1 System Databases

System databases are created automatically when we install MS SQL Server. Following is a list of system databases −

  • Master – It is a System Configuration Database.
  • Model – It is used as Configuration Database for Services
  • MSDB – It is used as a template for new users.
  • Tempdb – It is used to provide temporary database for workable. Tempdb is always dropped & recreated every time sql restarts. It is better not to store important data in it.
  • Resource – It is hidden System Configuration Database that provides system object to other databases. It was introduced in 2005 version.
  • Distribution – It is used for Replication feature only.

2.2 User Databases

User databases are created by users  like (Admins, developers and testers who have access to create databases).

3. Data types


SQL data type specifies the  type of data of any object. In Sql server each column, variable and statement has related data type in SQL.

SQL Server consist of six categories of data types.

3.1 Exact Numeric Data Types

DATA TYPE FROM TO
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 -1
numeric -10^38 +1 10^38 -1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647

3.2 Approximate Numeric Data Types

DATA TYPE FROM TO
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

3.3 Date and Time Data Types

DATA TYPE FROM TO
datetime Jan 1, 1753 Dec 31, 9999
smalldatetime Jan 1, 1900 Jun 6, 2079
date Stores date E.g May 7, 1994
time Stores time like 10:15 A.M.

3.4 Character Strings Data Types

DATA TYPE Description
char Space taken is fixed and can’t shrink or grow dynamically.
varchar Dynamically the length grows or shrink as per need.

4. Operator


An operator is a reserved word or a character used primarily in an SQL statement’s WHERE clause to perform different operations. Which we will see shortly. Operators are used to specify conditions in an SQL statement.

  • Arithmetic operators
  • Comparison operators
  • Logical operators
  • Operators used to negate conditions

4.1 SQL Arithmetic Operators:

Assume variable a holds 10 and variable b holds 20, then:

Operator Description Example
+ Addition – Adds values on either side of the operator a + b
Subtraction – Subtracts right hand operand from left hand operand a – b
* Multiplication – Multiplies values on either side of the operator a * b
/ Division – Divides left hand operand by right hand operand b / a
% Modulus – Divides left hand operand by right hand operand and returns remainder b % a

4.2 SQL Comparison Operators:

Operator Description Example
= Checks whether the two operands are having equal value or not, if yes then condition becomes true. (a = b)
!= Checks whether the two operands are having equal value or not, if values are not equal then condition becomes true. (a != b)
Checks whether the two operands are having equal value or not, if values are not equal then condition becomes true. (a b)
> Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (a > b)
< Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (a < b)
>= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (a >= b)
<= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (a <= b)
!< Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true. (a !< b)
!> Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true. (a !> b)

4.3 SQL Logical Operators & Predicates:

Here is a list of all the logical operators and predicates available in SQL.

Operator Description
ALL The ALL operator is used to compare a value to all values in another value set.
AND The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause.
ANY The ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN The IN operator is used to compare a value to a list of literal values that have been specified.
LIKE The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause.
IS NULL The NULL operator is used to compare a value with a NULL value.
UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

[NOTE]: IN, BETWEEN & LIKE falls under predicates.

5. Categories of T-Sql


  • Data Definition Language:
    It consist of statements for object definitions.
    E.g: CREATE, ALTER, DROP
  • Data Manipulation Language:
    It consist of statements for querying and modifying data.
    E.g: SELECT, INSERT, UPDATE, DELETE
  • Date Control Language:
    It consist statements for security purpose and it’s mostly used by DBA.
    E.g: GRANT, REVOKE, DENY

5.1 DATA DEFINITION LANGUAGE


Now, before creating a table you should select a database in which you will work on. The database can either be selected from the System database like, Master, Model etc or you can create your own database.
To create the database make sure you have the admin rights for creating it & all the databases name should be unique.

 

5.1.1 CREATE

Creating the Database:
Syntax: CREATE DATABASE Database_name;
Example:CREATE DATABASE Student;

After creating the database its time to use/select that database on which you are going to work.

Using/Selecting the Database:
Syntax: USE Database_name;
Example: USEStudent;

In this way you will be able to work on specific database.
Now that the database is ready and in use, its time to create a new table.

Creating a Table:
Now before creating a table you should know about constraints. These constraints allow the data in your table to comply certain properties. These properties are nothing but constraints.
List of Constraints:

  • Primary key:  All values to be unique.
  • Foreign key: Used for reference.
  • Unique key: Accept first null value.
  • Not null: Does not accept null values.
  • Check: Checks for the valid condition and returns true if the condition is matched.
  • Default: Default value is given.

Creating Table with constraints:
syntax:

 CREATE TABLE Table_name
(
variable_1   data_type   constraint,
                 ......
)

 

Example:

CREATE TABLE Product
(
Sr_no int identity(100,1), 
//Here 100 is Starting value & 1 is the incremental value 

Product_id   int   primary key, Product_name   varchar(20), 
// Here 20 is the length

Store_id int foreign key references Stores(Store_id), 
//"Stores"is the another table 

Store_name   varchar(20)  not null,

Quantity int check(Quantity > 10), 
// It checks whether the value of quantity is > 10 

Rate int default 100, // The default value of rate is set to 100 
)

[Note]:

  • Identity: Values are given by system. We have to just mention the starting value and increment value.

 

5.1.2 ALTER

Altering Table:
When you want to change the data type of specific column or add a new column or delete a column, then you can use alter statement.

Syntax:

  • ALTER TABLE Table_name ALTER COLUMN column_name new_datatype// Altering column data type
  • ALTER TABLE Table_name ADD column_name datatype// Adding new column in the specified table
  • ALTER TABLE Table_name DROP COLUMN column_name// Dropping a column from a table

Example:

  • alter table Product alter column Product_id varchar(20);
  • alter table Student add Roll_no int;
  • alter table Student drop column Roll_no;

 

 

5.1.3 DROP

Dropping a Table:
To completely delete the table records and its structure, drop statement is used.
Syntax:

 DROP TABLE Table_name;

Example:

DROP TABLE Student;

 

Dropping Database:
Deleting the database from the database list.
Syntax:

DROP DATABASE Database_name;

Example:

DROP DATABASE Student;

 

5.2 DATA MANIPULATION LANGUAGE


Data manipulation language or DML is used to retrieve, modify, add and delete records.
DML consist of SELECT, INSERT, UPDATE, DELETE Statements. Lets see them in detail.

 

5.2.1 INSERT

Insert statement is to insert records in the table. While inserting a new record in the table the definition should not be violated.
Syntax:

INSERT INTO table_name VALUES(...,...,...,...);

If the values are to be inserted in particular column then it can be mentioned as follows;

INSERT INTO table_name (column_name1,column_name2) VALUES(...,...,...,...);

The brackets for the column_names is optional and into is also optional.

Example:

INSERT INTO Student VALUES(53,'Gaurav','BE',2016);

now, for specific columns

INSERT INTO Student (id,name) VALUES(53,'xyz');

[NOTE] Now here onward ‘column_list’ means, list of columns like ‘column_name1,                           column_name2,…,column_nameN’.

 

 

5.2.2 UPDATE

Update statement is to update the existing records.
Syntax:

UPDATE Table_name SET statement WHERE condition;

Example:

UPDATE Student SET id=55, name='Gaurav' WHERE id=53;

 

 

5.2.3 DELETE

The delete statement deletes the records but the structure of the table is maintained.
Unlike drop statement which removes not only records but also the structure.
Syntax:

DELETE * FROM Table_name;
DELETE column_list FROM Table_name;
DELETE * FROM view_name; 
// Check about Views in detail below. But its better to stay with the flow.

Example:

DELETE * FROM Student;
DELETE year, marks FROM Student;
DELETE * FROM Student_view;

 

 

5.2.4 SELECT

Select statement retrieves the data from the table, view etc and displays it as a Result Table.
Syntax:

SELECT column_name1, column_name2,...,Column_nameN FROM Table_name;

If you want to select all the records then;

SELECT * FROM Table_name;

 

6. WHERE & HAVING


6.1 Where clause:

If you want to apply condition in the select statement, only then where clause is used.

Syntax:

SELECT column_list FROM Table_name WHERE condition;

Example:

SELECT * FROM Student WHERE marks>50;

6.2 Having clause:

  • A WHERE clause is used is filter records from a result.  The filter occurs before any groupings are made, ie WHERE clause applies to individual records.
  • A HAVING clause filters records that work on summarized GROUP BY results, ie HAVING clause is used to filter values from a group.
  • The only groups that meet the HAVING criteria will be returned.
  • HAVING requires that a GROUP BY clause is present.
  • WHERE and HAVING can be in same query.

Syntax:

SELECT column_list FROM Table_name1 WHERE condition
GROUP BY column_list HAVING condition ORDER BY column_list;

 

 

7. Group by & Order by clause


Group by clause is used with the select statement to arrange the identical records in a group. The group by clause is followed by where clause and precedes order by clause.
The order by clause is used to sort the data in ascending or descending order, by default it is ascending order.

Syntax:

SELECT column_list FROM table_name WHERE condition 
GROUP BY column_name ORDER BY column_name;

Example:

SELECT * FROM Student WHERE marks>50 GROUP BY total_sem
ORDER BY name, total_sem;

 

8. Merging columns using Select statement


Merging or concatenation can be achieved via sign.
Syntax:

SELECT column1 +' '+ column2 FROM Table_name;

Example:

SELECT id +' '+ name FROM Student;

 

 

9. Calculations in select statement


To make basic calculations like sum, sub, mul,div between columns.
Syntax:

SELECT column1, column2, column3, (column2+column3) AS column_alias 
FROM Table_name;

Here addition of two columns is performed which needs to be given a name, therefore aliasing is used.

Example:

SELECT id, name, sem1, sem2, (sem1+sem2) AS total_marks FROM Student;

 

 

10. Using AND, OR, NOT, BETWEEN with select statement


  • The AND and OR operators are used to combine multiple conditions to narrow record. These two operators are called conjunctive operators.
    These operators provide a means to make multiple comparisons with different operators in the same SQL statement.
  • The NOT operator shows record if the condition is NOT true.
  • The BETWEEN operator shows values within the given range. The values can be numbers, text, or dates

Syntax:

SELECT column_list FROM Table_name WHERE condition AND condition;

SELECT column_list FROM Table_name WHERE condition OR condition;

SELECT column_list FROM Table_name WHERE NOT condition;

SELECT col_list FROM Table_name WHERE col_name BETWEEN value1 AND value2;

Example:

SELECT * FROM Student WHERE marks>50 AND subject='Maths';
SELECT * FROM Student WHERE marks>50 OR total>75;

SELECT * FROM Student WHERE marks>50 AND(subject='Maths' OR subject='Bio');
//AND & OR in one statement.

SELECT Id, Customer_name, Country FROM Customer WHERE NOT Country = 'USA';
//Displaying customers who are not from USA

SELECT Id, Name, Marks FROM Student WHERE Marks BETWEEN 60 AND 80;

 

11. SELECT DISTINCT


To select distinct values from a column of a table Distinct is used.
Syntax:

SELECT DISTINCT (column_name) FROM Table_name WHERE condition;

Example:

SELECT DISTINCT (name) FROM Student WHERE condition;

 

12. Selecting TOP or upper values


Top is used to get the upper values from the record.
Syntax:

SELECT TOP n column_list FROM Table_name; 
// To select top n number of records  

SELECT TOP n% column_list  FROM Table_name// To select top n percent of records

Example:

SELECT TOP 10 * FROM Student;
SELECT TOP 10% * FROM Student;

 

13. SELECT INTO statement:


The Select Into statement selects records from one table and inserts into another table.
Syntax:

SELECT column_list into new_table FROM Table_name1;
SELECT column_list into new_table FROM Table_name1 WHERE condition;
SELECT column_list into new_table in 'file_name.mdb' 
FROM Table_name1 WHERE condition;

Example:

SELECT * into Student_backup FROM Student WHERE marks>60;
SELECT * into Student_backup in 'backup.mdb' FROM Student WHERE marks>60;

 

 

14. LIKE clause in SELECT statement


Like clause is used to check character string against a pattern.
The wildcard used in like clause are:
%: Represents string of any length
_: Represents single character
^: Represents single char not in the list
: Matches string within specified range
[]: Represents single character in supplied list
lets see them in detail.

syntax:

SELECT column_list FROM Table_name WHERE column1 LIKE 'Sa%';
//Finds string starting with Sa

SELECT column_list FROM Table_name WHERE column1 LIKE '%na';
//Finds string ending with na

SELECT column_list FROM Table_name WHERE column1 LIKE'[GSP]%'//Finds string starting with G or S or P

SELECT column_list FROM Table_name WHERE column1 LIKE '[G-P]%';
//Gives result which starts between G and P, i.e G to P

SELECT column_list FROM Table_name WHERE column1 LIKE 'Ga_';
//Third char to be any

SELECT column_list FROM Table_name WHERE column1 LIKE '_pa';
//First char to be any

SELECT column_list FROM Table_name WHERE column1 LIKE '^a';
// Matching string beginning other than 'a'

 

15. CASE Clause expression in SELECT statement


Syntax:

SELECT column_list
CASE column_name 
    WHEN expression1 THEN expression2
    WHEN expression3 THEN expression4
    WHEN expression5 THEN expression6
    ELSE expression7
END AS column_name_alias
FROM table_name;

Example:

SELECT p_id,p_name,category_id
CASE category_id
    WHEN 1 THEN  'Solid'
    WHEN 2 THEN 'Liquid'
    WHEN 3 THEN 'Gases'
    ESLE 'Unknown category'
END AS category_name
FROM table_name;

 

 

16. IN, ANY, ALL


16.1 IN:

  • WHERE IN returns values that matches with values in a list or sub query.
  • When you want to use multiple OR conditions then WHERE IN acts as a shorthand for multiple ORs.

Syntax:

SELECT column_list FROM table_name
WHERE column_name IN (...,...,...);

SELECT column_list FROM table_name
WHERE column_name IN (sub_query);

Example:

SELECT Id, Company_name, City, Country FROM Supplier
WHERE Country IN ('India','America','Russia');

SELECT Id, Customer_name, City, Country FROM Customer
WHERE Country IN (SELECT Country FROM Supplier);

 

 

16.2 ANY, ALL

  • ANY or ALL keywords are used with a WHERE or HAVING clause.
  • ANY & ALL operate on sub queries that return multiple values.
  • ANY returns true if any of the sub query values meet the condition.
  • ALL returns true if all of the sub query values meet the condition.

Syntax:

SELECT column_list FROM table_name
WHERE column_name OPERATOR ANY
(SELECT col_list FROM table_name WHERE condition);

SELECT column_list FROM table_name
WHERE column_name OPERATOR ALL
(SELECT col_list FROM table_name WHERE condition);

Example:

SELECT prod_name FROM Product
WHERE Id = ANY
(SELECT prod_Id FROM Order_item WHERE Quantity = 10);

SELECT prod_name FROM Product
WHERE Id = ALL
(SELECT prod_Id FROM Order_item WHERE Quantity = 10);

 

 

17. Index


Index is just like a reference which is used by the database search engine to speed up the data retrieval process.An index speeds up the Select queries and where clause, but may slow down the data input via Insert and Update statement.

Syntax:

Basic create index:
CREATE INDEX index_name ON tale_name;

Single column index: It is created based on only one column of the table.
CREATE INDEX index_name ON table_name (column_name);

Composite index: It is an index on two or more columns of the table.
CREATE INDEX index_name ON table_name (column1,column2);

Unique index: Unique index does not allow duplicate data to be inserted. 
              Preserves data integrity.
CREATE UNIQUE INDEX index_name ON table_name (column_name);

Drop: To drop the index.
DROP INDEX index_name;

When should indexes not to be used: [From tutorial site]
Although indexes are intended to enhance a database’s performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered:

  • Indexes should not be used on small tables.
  • Tables that have frequent, large batch update or insert operations.
  • Indexes should not be used on columns that contain a high number of NULL values.
  • Columns that are frequently manipulated should not be indexed.

 

18. Views


Views are named table expressions, whose definitions are stored in a metadata in sql server database.
Views don not persistently store data.
The definition of view is unpacked at runtime and the source objects are queried.

Creating simple view:
Syntax:

CREATE VIEW view_name
AS
SELECT query;

Example:

CREATE VIEW Student_view
AS
SELECT id, name from Student WHERE marks>70;

 

Displaying data of created view:

SELECT column_list FROM view_name;

 

Creating complex views:
Before going with complex views please refer to the topic of JOINS which is given below.
Example:

CREATE VIEW Student_view
AS
SELECT s.fname,s.lname,s.id,p.address 
FROM Student AS s JOIN Personal_info AS p ON s.fname=p.dname;

 

DML in Views:
Syntax:

INSERT INTO view_name column_list  VALUES(,,,);

UPDATE view_name SET expression WHERE condition;

DELETE column_list  FROM view_name WHERE condition;

SELECT column_list FROM view_name;

 

19. Set Operator


Union Operator:

Union combines rows in comparison joins combines columns from different sources.
Union removes duplicate rows.
Syntax:

SELECT query 1
UNION
SELECT query 2;

union

Example:

SELECT country, city FROM Product
UNION
SELECT country, city FROM Customer;

Union All:

Union all is just like union but it does not apply filter for duplicate input rows i.e union all returns a result set with duplicate rows (all rows from both the tables).
Syntax:

SELECT query 1
UNION ALL
SELECT query 2;

union all

Example:

SELECT country, city FROM Product
UNION ALL
SELECT country, city FROM Customer;

Intersect:

Returns only distinct rows that appear in both result sets.
Syntax:

SELECT query 1
INTERSECT
SELECT query 2;

Intersect

Example:

SELECT country, city FROM Product
INTERSECT
SELECT country, city FROM Customer;

Except:

Returns only distinct rows that appear in both result sets.
Syntax:

SELECT query 1
except
SELECT query 2;

Except

Example:

SELECT country, city FROM Product
EXCEPT
SELECT country, city FROM Customer;

 

20. Joins


Retrieve data from two or more tables based on data relationship between the tables is called join. Well in simple words, Join is used to combine records from two or more tables in a database by using values common to each other.
Types of Joins:

  • Inner Join
  • Outer Join
    • Left Outer Join
    • Right Outer Join
  • Full Join
  • Self Join
  • cartesian Join

20.1 Inner Join:

Inner join is also referred as an Equijoin.
The Inner join creates a new result table by combining column values of two tables based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of table1 and table2 are combined into a result row.

Syntax:

SELECT column_list FROM table1
INNER JOIN table2
ON table1.common_col=table2.common_col;

Example:

SELECT o.order_id, c.customer_name, o.order_date FROM Orders AS o
INNER JOIN Customers AS c
ON o.customer_id=c.customer_id
ORDER BY c.customer_name;

20.2 Left Outer Join:

Left outer joins returns all the values from the left table and matched values from the right table or no values are taken from right table if there is no matching predicate.
In left outer join if there is no matching records in right table, the join will still return all rows from the left table.

Syntax:

SELECT column_list FROM table1
LEFT OUTER JOIN table2
ON table1.common_col=table2.common_col; 

Or

SELECT column_list FROM table1
LEFT JOIN table2
ON table1.common_col=table2.common_col;

20.3 Right Outer Join:

Right outer joins returns all the values from the right table and matched values from the left table or no values are taken from left table if there is no matching predicate.
In right outer join if there is no matching records in left table, the join will still return all rows from the right table.

Syntax:

SELECT column_list FROM table1
RIGHT OUTER JOIN table2
ON table1.common_col=table2.common_col; 

Or

SELECT column_list FROM table1
RIGHT JOIN table2
ON table1.common_col=table2.common_col;

20.4 Full join:

Full joins combines the results of both the left and right outer join.
Full joins contains all records from both the tables and fill in NULL vales as well.

Syntax:

SELECT column_list FROM table1
FULL JOIN table2
ON table1.common_col=table2.common_col;

 

20.5 Self Join:

In self join a table is joined to itself, to compare rows in same table.
At least one alias of a table is required to differentiate between the two.
Self join creates two instances of the same table using from clause.

Syntax:

SELECT column_list FROM table1 a
SELF JOIN table1 b
ON a.common_col=b.common_col;

20.6 Cartesian Join or Cross Join:

The cartesian join or cross join  returns the cartesian product of the sets of records from the two or more joined tables. Thus, it is similar to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:

SELECT table1.column1, table2.column2...
FROM table1, table2 [, table3 ];

 

21. String Functions


  • SUBSTRING():
    Returns only a part of the string.
    Syntax:

    SELECT SUBSTRING(expression,start,length);

    Example:

    SELECT SUBSTRING('Microsoft sql server', 11, 3);

 

  • LEN():
    Provides metadata about the number of characters.
    Syntax:

    SELECT LEN('string')AS length;

    Example:

    SELECT LEN('MS SQL SERVER       ')AS length;

    output: length 13

 

  • DATALENGTH():
    Provides metadata about the bytes stored in a string.
    Syntax:

    SELECT DATALENGTH('string')AS length;

    Example:

    SELECT DATALENGTH('MS SQL SERVER       ')AS length;

    output: length 20

 

  • CHARINDEX():
    Returns a number representing the position of a string within another string.
    Syntax:

    SELECT CHARINDEX( 'part of string, to get its position','string')
    AS alias_name;

    Example:

    SELECT CHARINDEX('SQL','MS SQL SERVER')AS result;

    output: result 4

 

  • REPLACE():
    Replacing one set of characters with another set within a string.
    Syntax:

    SELECT REPLACE('string','part of the string','to be replaced with')
    AS alias_name;

    Example:

     SELECT REPLACE('MS XYL SERVER','XYZ','SQL')AS length;
    
     output: MS SQL SERVER
    

 

  • UPPER() & LOWER():
    Converts the string in upper case or lower case.
    Syntax:

    SELECT UPPER('string')AS alias_name;
    
    SELECT LOWER('string')AS  alias_name;

    Example:

    SELECT UPPER('hello')AS up; //Output: HELLO
    
    SELECT LOWER('HELLO')AS low; //Output: hello

 

  • LEFT() & RIGHT():
    LEFT():This function will extract characters from the left part of a given string (text) value. The given value can either be a String or a Column of a table.

    LEFT(string_value, number_of_characters);


    RIGHT():This function will extract characters from the right part of a given string (text) value. The given value can either be a String or a Column of a table.

    RIGHT(string_value, number_of_characters);

 

  • Format():
    Allows to format an input value to a character string.

22. Functions


22.1 Aggregate Functions:

Aggregate functions are
sum: Sums up the values of given column,
avg: Gives the average of given column,
min: Gives the minimum of given column,
max: Gives the maximum value of given column,
count:  Counts up the values of given column.

Syntax:

SELECT SUM(column_name)AS alias_name FROM table_name;
SELECT AVG(column_name)AS alias_name FROM table_name;
SELECT MIN(column_name)AS alias_name FROM table_name;
SELECT MAX(column_name)AS alias_name FROM table_name;
SELECT COUNT(column_name)AS alias_name FROM table_name;

22.2 Used defined functions:

22.2.1 Scalar function:
Example:

CREATE FUNCTION fngetfullname
(
@f_name varchar(20),
@l_name varchar(20)
)
RETURNS varrchar(50)
AS
BEGIN
           RETURN(select @first_name+' '+@last_name from Student;)
END
Output: SELECT dbo.fngetfullname(f_name,l_name) AS name, std FROM Student;

 

22.2.2 Inline Table Valued function:
Example:

CREATE FUNCTION getstudent()
RETURNS TABLE
AS
BEGIN
           RETURN (SELECT * FROM student;)
END
Output: select * from getstudent();

 

22.2.3 Multi-statement Table valued function:

Example:

CREATE FUNCTION getmulstudent()
RETURNS @stud TABLE
(
id int,
fname varchar(20),
lname varchar(20)
)
AS
BEGIN
--Inserting data
INSERT INTO @stud
SELECT s.id,s.firstname, lastname FROM Student AS s;

--Updating data
UPDATE @stud SET fname='kumar' WHERE id=120;
RETURN
END
Output: select * from getmulstudent();

23. Stored Procedure


Stored procedure is used to save time from writing same code again and again.

Creating Procedure:
Syntax:

CREATE PROCEDURE procedure_name
AS
BEGIN
query
end

Example:

CREATE PROCEDURE stud1&2
AS 
BEGIN
SELECT * FROM Student1;
SELECT * FROM Student2;
END
Output: stud1&2

Altering Procedure:
Syntax:

ALTER PROCEDURE procedure_name
AS 
BEGIN
query
END

Creating Stored Procedure with parameters:
Syntax:

CREATE PROCEDURE procedure_name
parameter_list
AS 
BEGIN
query
END

Example:

CREATE PROCEDURE stud1&2
@sp_id int,
@sp_rollno int,
AS
BEGIN
SELECT * FROM Student1 WHERE id=@sp_id;
SELECT * FROM Student2 WHERE rollno=@sp_rollno;
END
Output: stud1&2 @sp_id=123, @sp_rollno=57

Drop Procedure:
Syntax:

DROP PROCEDURE procedure_name;

Example:

DROP PROCEDURE stud1&2;

Here is the link to download the whole post on SQL: SQL.pdf

Advertisements