We will see about MS Sql Server in this post.
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.
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
|decimal||-10^38 +1||10^38 -1|
|numeric||-10^38 +1||10^38 -1|
3.2 Approximate Numeric Data Types
|float||-1.79E + 308||1.79E + 308|
|real||-3.40E + 38||3.40E + 38|
3.3 Date and Time Data Types
|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
|char||Space taken is fixed and can’t shrink or grow dynamically.|
|varchar||Dynamically the length grows or shrink as per need.|
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:
|+||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:
|=||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.
|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.
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;
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:
CREATE TABLE Table_name ( variable_1 data_type constraint, ...... )
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 )
- Identity: Values are given by system. We have to just mention the starting value and increment value.
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.
- 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
- alter table Product alter column Product_id varchar(20);
- alter table Student add Roll_no int;
- alter table Student drop column Roll_no;
Dropping a Table:
To completely delete the table records and its structure, drop statement is used.
DROP TABLE Table_name;
DROP TABLE Student;
Deleting the database from the database list.
DROP DATABASE Database_name;
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.
Insert statement is to insert records in the table. While inserting a new record in the table the definition should not be violated.
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.
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’.
Update statement is to update the existing records.
UPDATE Table_name SET statement WHERE condition;
UPDATE Student SET id=55, name='Gaurav' WHERE id=53;
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.
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.
DELETE * FROM Student; DELETE year, marks FROM Student; DELETE * FROM Student_view;
Select statement retrieves the data from the table, view etc and displays it as a Result Table.
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.
SELECT column_list FROM Table_name WHERE condition;
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.
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.
SELECT column_list FROM table_name WHERE condition GROUP BY column_name ORDER BY column_name;
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.
SELECT column1 +' '+ column2 FROM Table_name;
SELECT id +' '+ name FROM Student;
9. Calculations in select statement
To make basic calculations like sum, sub, mul,div between columns.
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.
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
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;
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.
SELECT DISTINCT (column_name) FROM Table_name WHERE condition;
SELECT DISTINCT (name) FROM Student WHERE condition;
12. Selecting TOP or upper values
Top is used to get the upper values from the record.
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
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.
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;
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.
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
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;
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
- 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.
SELECT column_list FROM table_name WHERE column_name IN (...,...,...); SELECT column_list FROM table_name WHERE column_name IN (sub_query);
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.
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);
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);
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.
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.
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:
CREATE VIEW view_name AS SELECT query;
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.
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:
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 combines rows in comparison joins combines columns from different sources.
Union removes duplicate rows.
SELECT query 1 UNION SELECT query 2;
SELECT country, city FROM Product UNION SELECT country, city FROM Customer;
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).
SELECT query 1 UNION ALL SELECT query 2;
SELECT country, city FROM Product UNION ALL SELECT country, city FROM Customer;
Returns only distinct rows that appear in both result sets.
SELECT query 1 INTERSECT SELECT query 2;
SELECT country, city FROM Product INTERSECT SELECT country, city FROM Customer;
Returns only distinct rows that appear in both result sets.
SELECT query 1 except SELECT query 2;
SELECT country, city FROM Product EXCEPT SELECT country, city FROM Customer;
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.
SELECT column_list FROM table1 INNER JOIN table2 ON table1.common_col=table2.common_col;
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.
SELECT column_list FROM table1 LEFT OUTER JOIN table2 ON table1.common_col=table2.common_col;
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.
SELECT column_list FROM table1 RIGHT OUTER JOIN table2 ON table1.common_col=table2.common_col;
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.
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.
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.
SELECT table1.column1, table2.column2... FROM table1, table2 [, table3 ];
21. String Functions
Returns only a part of the string.
SELECT SUBSTRING('Microsoft sql server', 11, 3);
Provides metadata about the number of characters.
SELECT LEN('string')AS length;
SELECT LEN('MS SQL SERVER ')AS length;
output: length 13
Provides metadata about the bytes stored in a string.
SELECT DATALENGTH('string')AS length;
SELECT DATALENGTH('MS SQL SERVER ')AS length;
output: length 20
Returns a number representing the position of a string within another string.
SELECT CHARINDEX( 'part of string, to get its position','string') AS alias_name;
SELECT CHARINDEX('SQL','MS SQL SERVER')AS result;
output: result 4
Replacing one set of characters with another set within a string.
SELECT REPLACE('string','part of the string','to be replaced with') AS alias_name;
SELECT REPLACE('MS XYL SERVER','XYZ','SQL')AS length; output: MS SQL SERVER
- UPPER() & LOWER():
Converts the string in upper case or lower case.
SELECT UPPER('string')AS alias_name; SELECT LOWER('string')AS alias_name;
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.
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.
Allows to format an input value to a character string.
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.
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:
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:
CREATE FUNCTION getstudent() RETURNS TABLE AS BEGIN RETURN (SELECT * FROM student;) END
Output: select * from getstudent();
22.2.3 Multi-statement Table valued function:
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.
CREATE PROCEDURE procedure_name AS BEGIN query end
CREATE PROCEDURE stud1&2 AS BEGIN SELECT * FROM Student1; SELECT * FROM Student2; END
ALTER PROCEDURE procedure_name AS BEGIN query END
Creating Stored Procedure with parameters:
CREATE PROCEDURE procedure_name parameter_list AS BEGIN query END
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 procedure_name;
DROP PROCEDURE stud1&2;
Here is the link to download the whole post on SQL: SQL.pdf