ms sql server session1

8/2/2019 Ms SQL Server Session1

1/38

Structured Query Language

1


2/38

Outline

SQL Ingredients

Data Types

Syntax, Key words

Example queries

2


3/38

Database

Database is a structure that can house information about manydifferent type of objects as well as the relationships amongthose objects.A tool is required to easily manipulate such information. Thisis called a database management system or DBMS.Most popular approach for manipulating data in a relationalDBMS is SQL

3


4/38

Organized collection of data (Tables) Entries in the Table are single-valued Each column in the Table should have an uniquename (called attribute name) All values in a column are values of the same attribute Each row is distinct Order of columns and rows is flexible

4

Structure of the database


5/38

Terminology

Relation Table File Tuple Row Record Attribute Column Field

5


6/38

Data Types Integer(size), int(size), smallint(size), tinyint(size)

Holds integers only. The maximum number of digits specified inparenthesis.

decimal(size,d), numeric(size,d)Holds numbers with fractions. The maximum number of digits arespecified in "size". The maximum number of digits to the right of the

decimal is specified in "d". CHAR(size)

Holds a fixed length string (can contain letters, numbers, and specialcharacters). The fixed size is specified in parenthesis.

varchar(size)Holds a variable length string (can contain letters, numbers, and specialcharacters). The maximum size is specified in parenthesis.

DATEdate(yyyymmdd) Holds a date TIME Holds time

6


7/38

What is SQL ?

An ANSI standard computer language Allows you to access a database Execute queries against a database Retrieve data from a database Insert new records in a database Delete records from a database Update records in a database Querying Language for database programs like

MySQL, MS SQL Server, ADQL, MS Access, DB2,Informix, Oracle, Sybase, etc.

Easy

7

Note: Some database systems require a semicolon at the end of the SQL statement


8/388

SQL Data Manipulation Language (DML)It includes syntax to update, insert and delete records.

Ingredients

SQL Data Definition Language (DDL) It permits database tables to be created or deleted, define

indexes (keys), specify links between tables and imposeconstraints between database tables.


9/38

Data Manipulation Language (DML) :

SELECT - extracts data from a database table

UPDATE - updates data in a database table

DELETE - deletes data from a database table

INSERT INTO - inserts new data into a database table

9

Data Definition Language (DDL) : CREATE TABLE - creates a new database table

ALTER TABLE - alters (changes) a database table DROP TABLE - deletes a database table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

WHERE impose constraints


10/38

Basic Syntax Select - Attributes, column name

From - Table name

Where - Restrictions, constraints

[Note: To query data on an existing database which you are not allowed to change]

10

Select name, rank, amount

from salarywhere amount = 10000

Note: SQL statements are not case sensitive.

Select *

from Salarywhere amount > 10000

Name Rank Amount YearsFor example: SALARY


11/38

Select

Alias column name alias

Select Z as Redshift or Select psfmag_u as u Operations are permitted in select statement

Select cos(angle) from Triangles or

Select (income-expenditure) as Savings

11


12/38

Select

Select DISTINCT name FROM Orders

12

It returns the result set like :Mr.R.SharmaMr.s.Pal

DISTINCTOutputs only one entry/row of data for every group of rows/entries

that is identical.

OrderNumber

400

401

402

Time

1300

1310

1315

Name

Mr. R. Sharma

Mr. S. Pal

Mr. R. Sharma

Items

Tomato Soup

Fried rice

Pasta

Amount

45

80

75

ORDERS


13/38

Where

13

The following operators can be used with the WHERE clause:

Operator Description= Equal or!= Not equal> Greater than< Less than>= Greater than or equal


14/38

14

AND and OR join two or more conditions in a WHERE clause.The AND operator displays a row if ALL conditions listed are true.The ORoperator displays a row if ANY of the conditions listed are true

For example: SALARY Name Rank Amount Years

Select name, rank From Salary

Where amount >25000 and years > 5Select name, rank From Salary

Where amount > 25000 or years > 5

Where


15/38

Using Quotes

15

SQL uses single quotes around text values(most database systems will also accept double quotes).Numeric values should not be enclosed in quotes.

For text values:

SELECT *FROM PersonsWHERE Surname=Sharma

Where


16/38

16

LIKE condition- used to specify a search for a pattern in a column.SELECT column FROM tableWHERE column LIKE patternA "%" sign can be used to define wildcards (missing letters in the pattern)

both before and after the pattern.For example:The following SQL statement will return persons with first names that start

with an 'R':SELECT * FROM PersonsWHERE FirstName LIKE R%The following SQL statement will return persons with first names that endwith an 'a':SELECT * FROM PersonsWHERE FirstName LIKE %a

Where


17/38

Join

17

Where Selects data from two or more tables to make the result complete or to binddata together across tables, without repeating all of the data in every table.

Tables in a database can be related to each other with keys calledprimary key. Each primary key value must be unique within the table.For example: SALARY EmployeeID Name Amount

EmployeeID Position YearsRANK

SELECT Salary.EmployeeID, Salary.Name, Rank.Position, Rank.YearsFROM Salary, RankWHERE Salary.EmployeeID= Rank.EmployeeID


18/38

Join

18

The INNER JOIN returns all rows from both tables where there is a match.

To select data from two tables with the JOIN keyword.

EmployeeID Name Amount

EmployeeID Position YearsRANK

For example: SALARY

SELECT Salary.EmployeeID, Salary.Name, Rank.PositionFROM SalaryINNER JOIN RankON Salary.EmployeeID= Rank.EmployeeID


19/38

Order by

19

The ORDER BY clause is used to sort the rows.To display the names in alphabetical order:

SELECT Name, Marks FROM MarksheetORDER BY Name

To display the company names in numerical order:SELECT Marks FROM MarksheetORDER BY Marks

To display the names in reverse alphabetical order:SELECT Name, Marks FROM MarksheetORDER BY Name DESC


20/38

Group by

20

GROUP BY...possible to find the aggregate functions like (SUM) for each individualgroup of column values.For example, considerTable containing expenditure by persons employed under a research project

Date Name Item Amount

DST2011

To find the total expenditure under each person:SELECT Name, SUM(Amount) FROM DST2011GROUP BY NameIn place of where we use HAVING to specify conditionsHAVING SUM(amount) > 5000


21/38

Aggregate Functions Having Clause

21

Find the names of all branches where the average account

balance is more than $1,200.

select branch-name, avg (balance)from account

group by branch-namehaving avg (balance) > 1200


22/38

Null Values

22

It is possible for tuples to have a null value, denoted by null,

for some of their attributes null signifies an unknown value or that a value does not exist.

The predicate is null can be used to check for null values.

E.g. Find all loan number which appear in the loan relationwith null values for amount.

select loan-number

from loanwhere amount is null

The result of any arithmetic expression involving null is null

E.g. 5 + null returns null However, aggregate functions simply ignore nulls

more on this shortly


23/38

Nested Subqueries

23

SQL provides a mechanism for the nesting of subqueries.

A subquery is a select-from-where expression that is nestedwithin another query.

Example :

Find all customers who have both an account and a loan at the bank.

select distinct customer-namefrom borrowerwhere customer-namein (select customer-namefromdepositor)Find all customers who have a loan at the bank but do not have an

account at the bankselect distinct customer-namefrom borrowerwhere customer-namenot in (select customer-namefrom depositor)


24/38

Modification of the Database Insertion

24

Add a new tuple to account

insert into accountvalues (A-9732, Perryridge,1200)or equivalently

insert into account (branch-name, balance, account- number)values (Perryridge, 1200, A-9732)

Add a new tuple to account with balance set to nullinsert into accountvalues (A-777,Perryridge, null)


25/38

Modification of the Database Updates

25

Increase all accounts with balances over $10,000 by 6%, allother accounts receive 5%.

Write two update statements:update accountset balance = balance 1.06where balance > 10000update accountset balance = balance 1.05where balance 10000

The order is important Can be done better using the case statement (next slide)


26/38

Case Statement for Conditional Updates

26

Same query as before: Increase all accounts with balances over$10,000 by 6%, all other accounts receive 5%.

update accountset balance = casewhen balance


27/38

Create Table Construct

27

An SQL relation is defined using the create table command:

Example: create table branch(branch-name char(15) not null,branch-city char(30),assets integer)


28/38

Integrity Constraints in Create Table

28

not null

primary key (A1, ..., An)

check (P), where P is a predicate

Example: Declare branch-name as the primary keyfor branch and ensure that the values of assets arenon-negative.

create table branch(branch-name char(15),branch-city char(30)assets integer,primary key (branch-name),check (assets >= 0))


29/38

Drop and Alter Table Constructs

29

The drop table command deletes all information about the

dropped relation from the database. The after table command is used to add attributes to an

existing relation. All tuples in the relation are assignednull as the value for the new attribute. The form of thealter table command isalter table r add A D

where A is the name of the attribute to be added torelation r and D is the domain of A.

The alter table command can also be used to dropattributes of a relation

alter table r drop Awhere A is the name of an attribute of relation r

Dropping of attributes not supported by many databases


30/38

FunctionsSQL has a lot of built-in functions for counting and other calculations-

SELECT function(column) FROM table

Types of FunctionsThe basic types of functions are:

Aggregate Functions: operate against a collection of values, but

return a single value.

Scalar functions: operate against a single value, and return a single

value based on the input value.

30

AVG(column), COUNT(column), MAX(column),MIN(column), STDEV(column), SUM(column)

MOD(x,y), ABS(x), ROUND(c,decimals)


31/38

String Operations

31

SQL includes a string-matching operator for comparisons on

character strings. Patterns are described using two specialcharacters:

percent (%). The % character matches any substring.

underscore (_). The _ character matches any character.

Find the names of all customers whose street includes the

substring Main. select customer-namefrom customerwhere customer-street like %Main% Match the name Main%

like Main\% escape \

SQL supports a variety of string operations such as

concatenation (using ||)

converting from upper to lower case (and vice versa)

finding string length, extracting substrings, etc.


32/38

Stored Procedures

32

Stored Procedures

Can store procedures in the database

then execute them using the call statement

permit external applications to operate on the database withoutknowing about internal details


33/38

Creating a Procedure

33

CREATE OR REPLACE PROCEDURE hello ISGreetings VARCHAR(20);BEGINGreetings:= 'Hello World';DBMS_OUTPUT.PUT_LINE(greetings);END hello;/


34/38

Calling a Procedure

34

Copy the previous procedure into SQL PlusCall it using the following commandsset serveroutput on size 4000EXECUTE hello;Another way to execute it is from a command block:BEGINhello;END;/

To display output


35/38

Calling a Procedure with Arguments

35

DECLAREprice_increase NUMBER(6,2):=20;newp NUMBER(6,2):=0;BEGINDBMS_OUTPUT.PUT_LINE('Current price '||price_increase);increase(oldprice=>price_increase, newprice=>newp);DBMS_OUTPUT.PUT_LINE('Price after increase '|| newp);END;/


36/38

Creating Functions

36

CREATE OR REPLACE FUNCTION discount (amountNUMBER, percent NUMBER:=5)

RETURN NUMBER ISBEGIN

IF (amount>=0) THENreturn (amount*percent/100);

ELSEreturn(0);

END IF;END discount;/ The IF-THEN constructallows for errorchecking


37/38

Calling the Function

37

DECLAREcurrent_amt NUMBER:=100;incorrect_amt NUMBER:=-5;BEGINDBMS_OUTPUT.PUT_LINE(' Order and Discount');DBMS_OUTPUT.PUT_LINE(current_amt || ' '||discount(current_amt));DBMS_OUTPUT.PUT_LINE(incorrect_amt||''||discount(incorrect_amt));END;/


38/38

SummarySelect .

AS (alias for column) DISTINCT COUNT

Select from .. where

From

AS (alias for table)

Where AND/OR

BETWEEN JOIN

GROUP BY

ORDER BY

ms sql server session1

Documents