SQL SERVER Sachin Rawool Developer Enosis Learning WHAT

SQL SERVER -Sachin Rawool (Developer) Enosis Learning

WHAT IS DATA AND DATABASE ? • Data is a collection of facts, such as numbers, words, measurements, observations or even just descriptions of things. • Collection of the data leads to Database i. e. for example, the complete data of a company leads to a database for that company. • Database should be saved in such a organized format so that the user/computer can retrieve a piece of information from the database, so to achieve that DBMS come into the picture. Data. Bas e WWW. ENOSISLEARNING. COM

DATABASE MANAGEMENT SYSTEM (DBMS) • A DBMS makes it possible for end users to create, read, update and delete data in a database. The DBMS essentially serves as an interface between the database and end users or application programs, ensuring that data is consistently organized and remains easily accessible. • DBMS is that it lets end users and application programmers access and use the same data while managing data integrity. Data is better protected and maintained when it can be shared using a DBMS instead of creating new iterations of the same data stored in new files for every new application. The DBMS provides a central store of data that can be accessed by multiple users in a controlled manner. Data USER DBMS APP WWW. ENOSISLEARNING. COM Databas e

TYPES OF DBMS: • Hierarchical database: In a hierarchical database, records contain information about there groups of parent/child relationships, just like as a tree structure. The structure implies that a record can have also a repeating information. In this structure Data follows a series of records, It is a set of field values attached to it. • Network database: A network databases are mainly used on a large digital computers. It more connections can be made between different types of data, network databases are considered more efficiency It contains limitations must be considered when we have to use this kind of database. • Relational database: In relational databases, the relationship between data files is relational. These databases connect to the data in different files by using common data numbers or a key field. • Object-oriented database: It takes more than storage of programming language objects. Object DBMS's increase the semantics of the C++ and Java. It provides full-featured database programming capability, while containing native language compatibility. WWW. ENOSISLEARNING. COM

ADVANTAGES OF RDBMS • Data is only stored once (uniquely). • Complex queries can be carried out. • Better security. • Cater for future requirements. • Types of Relation Database Management System: • Oracle. • My. SQL (open source). • Postgre. SQL (open source). • SQLite (open source). • Microsoft SQL Server. WWW. ENOSISLEARNING. COM

MICROSOFT SQL SERVER • Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). • As of December 2016 the following versions are supported by Microsoft: • SQL Server 2005 • SQL Server 2008 R 2 • SQL Server 2014 • SQL Server 2016 WWW. ENOSISLEARNING. COM

SQL SERVER ARCHITECTURE : WWW. ENOSISLEARNING. COM

STORING DATA USING SQL SERVER Stores data in MDF LDF contains Page’ s WWW. ENOSISLEARNING. COM This stores the user queries, lo Page size 8 KB 1 MB Contains 128 pages

LANGUAGE USED FOR MICROSOFT SERVER • SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. • SQL is the standard language for Relation Database System. All relational database management systems like My. SQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language. • Also, they are using different dialects, such as: • MS SQL Server using T-SQL, Oracle using PL/SQL, MS Access version Why SQL? • Allows users to access data in relational database management systems. • Allows users to describe the data. • Allows users to define the data in database and manipulate that data. • Allows to embed within other languages using SQL modules, libraries & pre-compilers. • Allows users to create and drop databases and tables • Allows users to create view, stored procedure, functions in a database. • Allows users to set permissions on tables, procedures and views WWW. ENOSISLEARNING. COM

SQL COMMANDS: • The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature: DDL - Data Definition Language: • Command Description CREATE Creates a new table, a view of a table, or other object in database • ALTER Modifies an existing database object, such as a table. • DROP Deletes an entire table, a view of a table or other object in the database. DML - Data Manipulation Language: Command Description • INSERT Creates a record • UPDATE Modifies records • DELETE Deletes records DCL - Data Control Language: Command Description • GRANT Gives a privilege to user • REVOKE Takes back privileges granted from user DQL - Data Query Language: Command Description • SELECT Retrieves certain records from one or more tables WWW. ENOSISLEARNING. COM

DATABASE COSISTS OF TABLE: • A table is set of data elements using a model of vertical columns and horizontal rows where each columns are identified by its unique name. • A cell in a table is a unit where the row and a column intersect also called as field. • A table can have specified number of column but n number of rows. • A row of a table is also called as tuple. • When a data is filled in the table as per the column in an row then that particular data is called as a record of that table. EMPID EMP_NAM E SALARY ADDRESS CONTAC T 1 RON 15000 Vishrantwa di 12345678 9 WWW. ENOSISLEARNING. COM

CREATE DATABASE AND TABLE: • To Create a data base we use the command : • Create database [database name] for ex. Create database Company. • After creation you have to perform operation on that database you have to use that database for that the command is. • Use [database name] for ex. Use Company. • After creating a database in object explorer under database tab you can see newly created database named Company , Lets get introduced to sub tabs under a database. Contains diagram to show relationship of tables easily Contains the table created under specific database Contains the table system views and user views of tables Contains the alternate short names given by user to other database Contains the stored procedures, functions and trigers created for that database. WWW. ENOSISLEARNING. COM

CREATE A TABLE: • To create a table inside a database use command: Create table [table name] ([column name] data type [Constraint], …………. ) For example : Create Table Employee( [empid] [int] , [empname] [nvarchar](40), [empaddress] [nvarchar](100), [salary] [int] ); Then in Table tab you can see the new created table as given below. Contains the number of columns in that table Contains the key like primary, foreign keys for that table Contains the Constraints such as check , default for table. Contains the index for that specific table. WWW. ENOSISLEARNING. COM

DATA TPYES IN SQL SERVER WWW. ENOSISLEARNING. COM

ALTER TABLE • Using Alter command we can make changes in the table such as add a column, drop a column or change data type of a column or delete a constraint in a table. • Adding Colum : • Syntax: Alter table [Table Name] add [Column Name 1] [Data Type] , …[Column Name n] • Alter Column : • Syntax: Alter table [Table Name] Alter Column [Column Name ] [ new Data Type] • Drop Column: • Syntax: Alter table [Table Name] Drop Column [Column Name ] WWW. ENOSISLEARNING. COM

ADD CONSTRAINT USING ALTER TABLE • Adding Not Null: Syntax: Alter table [Table Name] Alter Column [column name] Data Type Not Null • Adding UNIQUE CONSTRAINT: Syntax: Alter table [Table Name] add constraint [constraint name ] Unique (Column Name) • Adding DEFAULT CONSTRAINT: Syntax: Alter table [Table Name] add constraint [constraint name ] Default (Default Value) for (Column Name) • Adding CHECK CONSTRAINT: Syntax: Alter table [Table Name] add constraint [constraint name ] Check (Check Expression) • Adding PRIMARY KEY: Syntax: Alter table [Table Name] add constraint [constraint name ] Primary key (Column Name) • Adding FOREGIN KEY: Syntax: Alter table [Table Name] add constraint [constraint name ] Foreign key (Column Name) References Refered_Table_Name(Column name). WWW. ENOSISLEARNING. COM

INSERTING DATA • To insert data inside a table in sql command used is • Syntax : • Insert Into [table name] values( column 1_value, columne 2_value, ……. ); • If the data is in String or in Date format then you have to specify the data in inverted commas for example: • Insert into emp_details values(1, ’HYDEN’, ’NEW YORK’, 10000) In above table emp name and address are in nvarchar so the The data is specified in inverted commas. • If you have to enter data to specific columns then you have to mention the column names that you have to enter for example : • Insert into emp_details (empid, empname) values(2, ’jack’). • In the above example the data will enter into those particular two columns only and rest will filled with NULL values if allowed to be NULL or else Error will occur. WWW. ENOSISLEARNING. COM

SELECT • Select command is used to retrieve a particular piece of information from the data or result set or to select that information. • For Example Select 4*4 , select * from [Table. Name] • • In above example * denotes all, i. e the available data inside the table will be return. You can also specify specific columns that you want to select from the table seperated by comma for example : select empid, empname from emp_details. WWW. ENOSISLEARNING. COM

ALIASES IN SELECT • Aliases are the temporary column name given to the column of the table while selecting the information from the table so that it can be recognized by the user, sometimes we perform mathematical or concatenation operation on the column so after executing query there is no specific name is given to the column. • For Example : • select ename, sal+comm from emp • select ename , sal+comm as Total_Sal from emp WWW. ENOSISLEARNING. COM

CLAUSES IN SQL SELECT • There are different clauses in sql server used with select to filter the data according to the user requirement , The clauses are mentioned below according to there priorities : • SELECT [TOP ( top_expression ) ] • [ ALL | DISTINCT ] • { * | column_name | expression } • [ FROM { table_source } ] • [ WHERE <search_condition> ] • [ GROUP BY <group_by_clause> ] • [ HAVING <search_condition> ] • [ ORDER BY <order_by_expression> ] [ • OPTION ( <query_option> [ , . . . n ] ) ] WWW. ENOSISLEARNING. COM

CLAUSES CONTINUED • the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. • FROM • ON • JOIN • WHERE • GROUP BY • HAVING • SELECT • DISTINCT • ORDER BY • TOP WWW. ENOSISLEARNING. COM

EXAMPLE ON SELECT • Consider the following query as an example. • SELECT country, YEAR(hiredate) AS yearhired, COUNT(*) AS numemployees FROM HR. Employees WHERE hiredate >= '20030101' GROUP BY country, YEAR(hiredate) HAVING COUNT(*) > 1 ORDER BY country , yearhired DESC; • 1. Evaluate the FROM Clause In the first phase, the FROM clause is evaluated. That’s where you indicate the tables you want to query and table operators like joins if applicable. If you need to query just one table, you indicate the table name as the input table in this clause. WWW. ENOSISLEARNING. COM

SELECT EXAMPLE CONTINUE… • 2. Filter Rows Based on the Wh. ERE Clause The second phase filters rows based on the predicate in the WHERE clause. Only rows for which the predicate evaluates to true are returned. • 3. Group Rows Based on the GROUp BY Clause This phase defines a group for each distinct combination of values in the grouped elements from the input table. It then associates each input row to its respective group. WWW. ENOSISLEARNING. COM

SELECT EXAMPLE CONTINUE… • 4. Filter Rows Based on the ha. VING Clause This phase is also responsible for filtering data based on a predicate, but it is evaluated after the data has been grouped; hence, it is evaluated per group and filters groups as a whole. • 5. process the SELECT Clause The fifth phase is the one responsible for processing the SELECT clause. What’s interesting about it is the point in logical query processing where it gets evaluated—almost last. That’s interesting considering the fact that the SELECT clause appears first in the query. WWW. ENOSISLEARNING. COM

SELECT EXAMPLE CONTINUE… • 6. handle presentation Ordering The sixth phase is applicable if the query has an ORDER BY clause. This phase is responsible for returning the result in a specific presentation order according to the expressions that appear in the ORDER BY list. The query indicates that the result rows should be ordered first by country (in ascending order by default), and then by numemployees, descending, yielding the following output. WWW. ENOSISLEARNING. COM

DIFFERENCE BETWEEN HAVING AND WHERE • 1) Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query. For example following query, which involve WHERE clause will work but other which uses HAVING clause will not work : • update DEPARTMENT set DEPT_NAME="New. Sales" WHERE DEPT_ID =1 ; // works fine • update DEPARTMENT set DEPT_NAME="New. Sales" HAVING DEPT_ID =1 ; // error • 2) WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL. • 3) One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause. • 4) When WHERE and HAVING clause are used together in a SELECT query with aggregate function, WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified. WWW. ENOSISLEARNING. COM

OPERATORS IN SELECT • There are different operators can be used with were clause in Select statement : • AND • OR • BETWEEN • NOT • IN • LIKE • Wild Card Operators : • % • _ • [ ] • [^] WWW. ENOSISLEARNING. COM

JOINS • SQL is a special-purpose programming language designed for managing information in a relational database management system (RDBMS). The word relational here is key; it specifies that the database management system is organized in such a way that there are clear relations defined between different sets of data. • The are two types of joins: • Inner Join (Simple Join) • Outer Join : There are different types of outer join mentioned bellow: • Left Outer Join • Right Outer Join • Full Outer Join • Cross Join In next slide we will look into the complete detail’s of Joins. WWW. ENOSISLEARNING. COM

WWW. ENOSISLEARNING. COM

REFERNCES • https: //docs. microsoft. com/en-us/sql/release-notes/microsoft-sql-server • Microsoft toolkit 70 -461 WWW. ENOSISLEARNING. COM