sql training
Download
Skip this Video
Download Presentation
SQL Training

Loading in 2 Seconds...

play fullscreen
1 / 115

SQL Training - PowerPoint PPT Presentation


  • 296 Views
  • Uploaded on

SQL Training. Sumali Conlon Associate Professor of MIS University of Mississippi University, MS 38677 [email protected] Phone: 662-915-5470. Outline. General Relational Database concepts (very briefly) Database Design Theory and Methodology (briefly) SQL

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL Training' - jamar


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql training
SQL Training

Sumali Conlon

Associate Professor of MIS

University of Mississippi

University, MS 38677

[email protected]

Phone: 662-915-5470

SQL-Training

outline
Outline

General Relational Database concepts (very briefly)

Database Design Theory and Methodology (briefly)

SQL

Table, View, Store Procedures, Triggers, DML, DDL, Indexes

Insert, Update, Delete

Retrieving Data – a lot of these

Simple SQL, Joins (inner, outer), Nested SQL, etc.

Union, minus, division, functions (max, min, count, sum, etc.)

Common T-SQL functions

Getdate, Convert, Dateadd, datepart, datedif, left, right

Common Table Expressions (CTE)

Performance – SQL tuning

Practical Information

-Saving to CSV, shortcuts, etc.

SQL-Training

to start
To start
  • Start SQL Server Management Studio
  • Add a new database
  • Restore the database

SQL-Training

relational data models relational constraints and relational algebra
Relational Data Models, Relational Constraints, and Relational Algebra

SQL-Training

  • Flat file: A two dimensional array of attributes or data items

ProductX 1 Bellaire 5

ProductY 2 Sugarland 5

ProductZ 3 Houston 5

Computerization 10 Stafford 4

Reorganization 20 Houston 1

Newbenefits 30 Stafford 4

slide9
SQL-Training

Attribute:

A name characteristic or property of an entity

= column header

Entity:

A “thing” in the real world with an independent existence

physical existence: person, student, car

slide10

Domain - The valid set of atomic value for an attribute in a relation

e.g. SSN set of 9 digits

GPA: 0<= GPA <= 4.0

Atomic - each value in the domain is indivisible

Name (Fname, Minit, Lname) – not atomic

Fname -- atomic

Minit -- atomic

Lname -- atomic

SQL-Training

definition summary
Definition Summary

SQL-Training

slide12

Candidate key: a super key such that no proper subset of its attributes is itself a super key. So candidate keys must have a minimal identifier.STUIDSSNPrimary key: the candidate key that is chosenOR the candidate key that is used to identify tuples in a relation-- unique, must exist

SQL-Training

slide13

Concatenated (composite) key: A primary key that is comprised of two or more attributes or data itemsG RADE_REPORT(STUID, COURSE#, GRADE) 

SQL-Training

slide14

Foreign key: A non-key attribute in one relation that appears as the primary key (or part of the key) in another relationEMPLOYEE(SSN, FNAME, MINIT, DNO)DEPARTMENT(DNUMBER, DNAME, MANAGER)

SQL-Training

relational algebra overview
Relational Algebra Overview
  • Relational algebra is the basic set of operations for the relational model
  • These operations enable a user to specify basic retrieval requests (or queries)
  • The result of an operation is a new relation, which may have been formed from one or more input relations
    • This property makes the algebra “closed” (all objects in relational algebra are relations)

SQL-Training

data definition language or data description language ddl
Data Definition Language or Data Description Language (DDL)
  • DDL – Define data structure

CREATETABLE employees

( id INTEGERPRIMARYKEY,

first_nameCHAR(50) NULL,

last_nameCHAR(75) NOTNULL,

dateofbirthDATENULL);

  • DROPTABLE employees;
  • ALTERTABLEemployee ADDemail Varchar(30)
  • ALTERTABLEemployee DROPCOLUMNemail;

SQL-Training

data definition language ddl
Data Definition Language (DDL)

Data Definition Language (DDL) statements are used to define the database structure or schema.

Some examples:

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object

http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

SQL-Training

data manipulation language dml statements transact sql
Data Manipulation Language (DML) Statements (Transact-SQL)
  • DML - statements are used for managing data within schema objects
    • SELECT - retrieve data from the a database
    • INSERT - insert data into a table
    • UPDATE - updates existing data within a table
    • DELETE - deletes all records from a table, the space for the records remain
    • MERGE - UPSERT operation (insert or update)
    • CALL - call a PL/SQL or Java subprogram
    • EXPLAIN PLAN - explain access path to data
    • LOCK TABLE - control concurrency
  • http://msdn.microsoft.com/en-us/library/ms177591(v=sql.90).aspx

SQL-Training

slide22
VIEW
  • a view consists of a stored query accessible as a virtual table composed of the result set of a query

create view V_staff_dno5

as select * from staff where dno = 5;

Benefits:

      • Restrict access to specific rows and columns in a table
      • Join columns from multiple tables and present them as though they are part of a single table
      • Present aggregate information (such as the results of the COUNT function)

SQL-Training

index
Index
  •  an index in a database
    • lets you quickly find specific information in a table or indexed view.
    • An index contains keys built from one or more columns in the table, or view, and pointers that map to the storage location of the specified data.
  • How to: Create a Spatial Index (SQL Server Management Studio)
  • http://msdn.microsoft.com/en-us/library/bb964714.aspx

SQL-Training

relational algebra overview1
Relational Algebra Overview
  • Relational Algebra consists of several groups of operations
    • Unary Relational Operations
      • SELECT (symbol:  (sigma))
      • PROJECT (symbol:  (pi))
    • Relational Algebra Operations From Set Theory
      • UNION ( ), INTERSECTION ( ), DIFFERENCE (or MINUS, – )
      • CARTESIAN PRODUCT ( x )
    • Binary Relational Operations
      • JOIN (several variations of JOIN exist)
      • DIVISION
    • Additional Relational Operations
      • OUTER JOINS, OUTER UNION
      • AGGREGATE FUNCTIONS (These compute summary of information: for example, SUM, COUNT, AVG, MIN, MAX)

SQL-Training

slide25

Unary Relational Operations: SELECT

  • The SELECT operation (denoted by  (sigma)) is used to select a subset of the tuples from a relation based on a selection condition.
    • The selection condition acts as a filter
    • Keeps only those tuples that satisfy the qualifying condition
    • Tuples satisfying the condition are selected whereas the other tuples are discarded (filtered out)
  • Examples:
    • Select the EMPLOYEE tuples whose department number is 4:

DNO = 4 (EMPLOYEE)

    • Select the employee tuples whose salary is greater than $30,000:

SALARY > 30,000(EMPLOYEE)

SQL-Training

slide26

Select Works on single table and takes rows that meet a specified condition, copy them into a new table

  • (Table name)

Condition(s)

SQL (Structured Query language)

SELECT *

FROM (table name)

WHERE condition 1

AND condition 2

AND condition 3…

SQL-Training

slide27

Find employees who work for department number 5.

 employee

DNO = 5

SQL:

SELECT *

FROM employee

WHERE dno = 5;

SQL-Training

slide28

Project Operates on a single table,

produces a vertical subset of the table,

extract the values of specified columns

eliminate duplicate rows

place the value in a new table

 (table name)

column1, column2, column3, …

SQL-Training

slide29

SQL:

SELECT column1, column2, column3, …

FROM (table name)

Sql Server – Select with (nolock)

NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

SQL-Training

slide30

E.g. Show the names of all employees

employeefname, minit, lname

SELECT fname, minit, lname

FROM employee;

SQL-Training

slide31

Select & project

Show the names of all employees who work for department number 5

 ( employee)

fname, minit, lnamedno = 5

SELECT fname, minit, lname

FROM employee

WHERE dno = 5;

SQL-Training

slide32

fname,minit,lname

DNO = 5

Employee

SQL-Training

slide35

Equijoin

SQL-Training

query tree for equijoin
Query Tree for Equijoin

X ID = STUID

Credit_Hours

Student

SQL-Training

slide37

Natural Join |X|

Is an equijoin which the repeated column is eliminated

Usually join performs over column with the same names

SQL-Training

slide38

Remove this column

SQL-Training

query tree for natural join
Query Tree for Natural Join

|X|

Credit_Hours

Student

SQL-Training

slide41

Semi-join: If R1 and R2 are tables

Semijoin of R1 and R2 is natural join of R1 and R2 and then projecting the result into the attributes of A

Semijoin is not cumulative

SQL-Training

slide42

Create tables

create table student1

(id char(3) primary key,

fnamechar(10),

lname char(10));

insert into student1 values(‘101’,’Jim’,’Smith’);

insert into student1 values(‘102’,’Tim’,’Brown’);

insert into student1 values(‘103’,’Babara’,’Houston’);

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

create table credit_hours

(stuid char(3) primary key,

hours number(3));

insert into credit_hoursvalues(101,60);

insert into credit_hoursvalues(102,85);

SQL-Training

slide43

Left Semi-Join

SQL-Training

slide44

Right Semi-Join

SQL-Training

slide45

Outer Join:

Is an extension of a THETA JOIN, an EQUIJOIN, or a NATURAL JOIN

An outer join consists of all rows that appear in the usual theta join, plus an additional row for each of the tuples from the original tables that do not participate in the theta join.

In those rows that are unmatched original tuples, extend it by assigning null values to the other attributes.

SQL-Training

slide46

Left outer join unmatched rows from the first (left) table appear in the resulting table

  • Right outer join unmatched rows from the second (right) table appear in the resulting table

SQL-Training

slide47

Left Outer Join

Right Outer Join

SQL-Training

sql server outer joins
SQL Server Outer Joins
  • LEFT OUTER JOIN or LEFT JOIN
  • RIGHT OUTER JOIN or RIGHT JOIN
  • FULL OUTER JOIN or FULL JOIN

SQL-Training

slide49

Outer Join -- Oracle

Left-outer join

select *

from student, credit_hours

where id = stuid(+);

SELECT E.FNAME, E.LNAME, dependent_name

FROM EMPLOYEE E, DEPENDENT D

WHERE E.SSN = D.ESSN(+);

SQL-Training

slide50
Join

select fname, lname,s_id, essn, pno

from staff S with (nolock)

join workson W with (nolock) on S.s_id = W.essn

where dno = 3;

fnamelnamepno

Tom Delay 2

Tom Delay 3

Tom Delay 4

Tom Delay 5

Larry Brown 3

Jane English 1

Jane English 2

Joe Hugh 1

Joe Hugh 2

SQL-Training

left outer join or left join
LEFT OUTER JOIN or LEFT JOIN

create view v_dno5_staff as select * from staff where dno = 5;

select essn, pno, s_id

from workson W with (nolock)

full outer join v_dno5_staff S with (nolock)on S.s_id = W.essn

order by pno;

SQL-Training

right outer join or right join
RIGHT OUTER JOIN or RIGHT JOIN

create view v_dno5_staff as select * from staff where dno = 5;

select fname, lname, pno

from v_dno5_staff S with (nolock)

right outer join workson W with (nolock) on S.s_id = W.essn;

fnamelnamepno

Tom Delay 2

Tom Delay 3

Tom Delay 4

Tom Delay 5

Larry Brown 3

NULL NULL 5

NULL NULL 6

NULL NULL 6

Jane English 1

Jane English 2

NULL NULL 4

Joe Hugh 1

Joe Hugh 2

NULL NULL 4

NULL NULL 6

SQL-Training

right outer join
Right-outer join

select *

from student, credit_hours

where id(+) = stuid;

SQL-Training

slide54

Sample SQL

create view:

create view v_emp_dno as select fname, lname, dno from employee;select * from v_emp_dno;create view v_department as select dnumber, dname from department;select * from v_department;Cartesian product:select * from v_emp_dno, v_department;Natural join:select * from v_emp_dno, v_department where dno = dnumber;Left Outer joinselect fname, lname, ssn, essn, dependent_namefrom employee, dependent where ssn = essn (+);Right Outer joinselect essn, dependent_name, fname, lname, ssnfrom employee, dependent where essn (+) = ssn;

SQL-Training

slide55

Set operations: Union, Difference, Intersection, Division

Union (U) tables must be compatible - they must have same basic structure, both relations must have the same domains.

The union of two relations is the set of tuples in either or both relations

SQL-Training

slide56

=

U

SSN

---------

123456789

333445555

453453453

666884444

987654321

5 rows selected

SSN

---------

123456789

333445555

666884444

453453453

4 rows selected

ESSN

---------

123456789

333445555

987654321

3 rows selected

SQL--Union

Select ssn from employee where dno = 5

Union

select distinct(essn) from dependent;

SQL-Training

slide57

Difference (-) The difference between two relations is the set of tuples that belong to the first relation but not in the second relation.

SQL-Training

slide58

SSN

---------

453453453

666884444

888665555

987987987

999887777

5 rows selected

-

=

SSN

---------

123456789

333445555

999887777

987654321

666884444

453453453

987987987

888665555

8 rows selected

ESSN

---------

123456789

333445555

987654321

3 rows selected

SQL--Minus

Select ssn from employee

minus

select distinct(essn) from dependent;

SQL-Training

slide59

Division () A binary operation that can be defined on two relations where the entire structure of one (the divisor) is a portion of the structure of the other (the dividen)

SQL-Training

slide60

Division

SQL-Training

aggregate functions and grouping
Aggregate Functions and Grouping

Script F:

(group attributes)  <function, attribute> (R)

Functions = sum, average, maximum, minimum, count

SQL-Training

example retrieve the department number number of employees and average salary in the department
Example: Retrieve the department number, numberof employees, and average salary in the department

RESULT(DNO, NUMBER_OF_EMPLOYEES, AVG_SAL) count SSN, Average SALARY EMPLOYEE

SQL-Training

slide64
SQL:

SELECT dno, sum(salary), Max (salary), min(salary), avg(salary)

FROM employee

GROUP BY dno

SQL-Training

if attribute list is not specified
If attribute list is not specified:

DNO count SSN, Average SALARY (EMPLOYEE)

SQL-Training

if grouping attributes are not specified
If grouping attributes are not specified

count SSN, Average SALARY (EMPLOYEE)

SQL-Training

slide67

SELECT sum(salary), Max (salary), min(salary)

avg(salary)

FROM employee

SELECT sum(salary), Max (salary), min(salary)

avg(salary)

FROM employee, department

WHERE dno = dnumber

AND dname = ‘Research’;

SQL-Training

examples
EXAMPLES …..

SQL-Training

t sql transact sql functions
T-SQL (Transact-SQL Functions)
  • T-SQL expands on the SQL standard to include proceduralprogramming,local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements. These additional features make Transact-SQL Turing complete.

SQL-Training

t sql transact sql functions1
T-SQL (Transact-SQL Functions)
  • Numeric functions
    • Numeric functions within Transact-SQL are mathematical functions for modifying numeric values.
  • Date functions – used a lot by FNC
    • Date functions calculate the respective date or time portion of an expression or return the value from a time interval.
  • String functions
    • String functions are used to manipulate data values in a column, usually of a character data type.
  • System functions
    • System functions of Transact-SQL provide extensive information about database objects. 
  • Metadata functions
    • Metadata functions return information concerning the specified database and database objects.

http://searchsqlserver.techtarget.com/feature/SQL-Server-2008-function-types-in-T-SQL

SQL-Training

t sql numeric functions
T-SQL -- Numeric functions

Function Explanation

ABS(n) Returns the absolute value (i.e., negative values are returned as positive) of the numeric expression n. Example:

SELECT ABS(–5.767) = 5.767, SELECT ABS(6.384) = 6.384

ACOS(n) Calculates arc cosine of n. n and the resulting value belong to the FLOAT data type.

ASIN(n) Calculates the arc sine of n. n and the resulting value belong to the FLOAT data type.

ATAN(n) Calculates the arc tangent of n. n and the resulting value belong to the FLOAT data type.

ATN2(n,m) Calculates the arc tangent of n/m. n, m, and the resulting value belong to the FLOAT data type.

CEILING(n) Returns the smallest integer value greater or equal to the specified parameter. Examples:

SELECT CEILING(4.88) = 5

SELECT CEILING(–4.88) = –4

COS(n) Calculates the cosine of n. n and the resulting value belong to the FLOAT data type.

COT(n) Calculates the cotangent of n. n and the resulting value belong to the FLOAT data type.

DEGREES(n) Converts radians to degrees. Examples:

SELECT DEGREES(PI()/2) = 90.0

SELECT DEGREES(0.75) = 42.97

EXP(n) Calculates the value e^n

SQL-Training

common t sql functions used by fnc
Common T-SQL Functionsused by FNC
  • Getdate
  • Convert
  • Dateadd
  • datepart
  • datedif
  • left
  • right

SQL-Training

getdate
GETDATE()

Returns the current system date and time. Example:SELECT GETDATE() = 2008-01-01 13:03:31.390

  • SELECT GETDATE() \'Current Date\';

Output:

2011-06-08 18:35:54.790

SQL-Training

slide78
FNC

OverDueOrders

SELECT TOP 300

f.applicant_lname + \', \' + f.applicant_fname \'Borrower\',

cust_caseno \'LoanNumber\',

rtrim(isnull(p.street_no,\'\')) + \' \' + rtrim(isnull(p.prefix,\'\')) + \' \' + rtrim(isnull(p.street,\'\')) + \' \' + rtrim(isnull(p.suffix,\'\')) \'PropertyAddress\',

rtrim(isnull(p.city,\'\')) \'City\',

rtrim(isnull(p.state,\'\')) \'State\',

rtrim(isnull(p.zip,\'\')) \'Zip\',

fd.service_id \'Service\',

fd.status \'Status\',

pro.fname + \' \' + pro.lname \'Processor\',

fd.due_from_vendor \'DueFromVendor\',

fd.order_date \'OrderDate\',

fd.doc_id \'DocID\',

CASE WHEN isnull(fd.preparer,0) > 0 AND isnull(a.is_apslport,0) =1 THEN \'Yes\'

WHEN isnull(fd.preparer,0) > 0 AND isnull(a.is_apslport,0) =0 THEN \'No\' END \'APUser\'

FROM folder f with(nolock)

join folder_docfd with(nolock) on fd.folder = f.folder

join doc_propdp with(nolock) on dp.doc_id = fd.doc_id

join property p with(nolock) on dp.prop_id = p.prop_id

LEFT JOIN processor pro WITH (NOLOCK) ON pro.prsn_pk = fd.review_determined_by

LEFT OUTER JOIN appr a WITH (NOLOCK) ON a.prsn_pk = fd.preparer

Where fd.service_id not in (\'49\',\'50\',\'200\',\'201\',\'202\',\'203\')

AND fd.status not in (\'XR\',\'W\',\'XS1\',\'XS2\',\'XS3\',\'N\',\'C\',\'CD\',\'CF\',\'CI\',\'CR\',\'L\',\'LD\',\'CL\')

and fd.due_from_vendor < getdate()

and fd.draft_date is null

and fd.assign_date is not null

AND (fd.completion_date IS NULL OR fd.completion_date > dateadd(dd,-30,GETDATE()))

ORDER BY fd.due_from_vendor, fd.status, fd.service_id

SQL-Training

slide79

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

  • Converts an expression of one data type to another 

SQL-Training

convert example
Convert (example)

select

CONVERT(varchar,fd.order_date,101) \'OrderDate\'

from folder_docfd with(nolock)

where fd.status not in (\'PC\',\'CQ\',\'L\',\'OH\',\'OD\')

and fd.order_date >= \'01/01/2011\'

and fd.order_date < \'02/01/2011\'

01/24/2011

01/04/2011

01/04/2011

01/04/2011

SQL-Training

convert example fnc
CONVERT (example -FNC)

select

CONVERT(varchar,fd.order_date,100) \'OrderDate\'

from folder_docfd with(nolock)

where fd.status not in (\'PC\',\'CQ\',\'L\',\'OH\',\'OD\')

and fd.order_date >= \'01/01/2011\'

and fd.order_date < \'02/01/2011\'

Output:

Jan 24 2011 3:08PM

Jan 4 2011 11:47AM

Jan 4 2011 11:47AM

Jan 4 2011 11:47AM

SQL-Training

convert example fnc1
Convert (Example FNC)

CountOfOrdersCreatedByServiceIDAndDate

select

CONVERT(varchar,fd.order_date,101) \'OrderDate\',

f.cust_caseno \'LoanNumber\',

COUNT(fd.doc_id) \'TotalCount\'

from folder_docfd with(nolock)

join folder f with(nolock) on f.folder = fd.folder

where fd.status not in (\'PC\',\'CQ\',\'L\',\'OH\',\'OD\')

and fd.order_date >= \'01/01/2011\'

and fd.order_date < \'02/01/2011\'

group by CONVERT(varchar,fd.order_date,101), f.cust_caseno

order by CONVERT(varchar,fd.order_date,101)

SQL-Training

convert example fnc2
Convert (Example FNC)

Net-OverDueOrders

select

d.LoanNo \'LoanNumber\',

d.DocID \'DocID\',

dd.OrderDt \'OrderDate\',

LTRIM(rtrim(isnull(p.StreetNo,\'\'))) + \' \' + LTRIM(rtrim(isnull(p.prefix,\'\'))) + \' \' + LTRIM(rtrim(isnull(p.street,\'\'))) + \' \' + LTRIM(rtrim(isnull(p.suffix,\'\'))) \'Property\',

p.city \'City\',

p.state \'State\',

p.zip \'Zip\'

from Doc d with(nolock)

join folder f with(nolock) on f.FolderID = d.FolderID

join DocDatesdd with(nolock) on dd.DocID = d.DocID

join DocPropdp with(nolock) on dp.DocID = d.DocID

join property p with(nolock) on p.PropID = dp.PropID

where dd.OrderDt >= \'01/01/2011\'

and dd.OrderDt < \'02/01/2011\'

and d.AppInstID = 128

order by CONVERT(varchar,dd.OrderDt,101)

SQL-Training

dateadd i n d
DATEADD(i,n,d)

Adds the number n of units specified by the value i to the given date d.

Examples

> select GETDATE() \'Now\';

Now

2011-06-08 19:10:44.073

  • select dateadd(dd,3,GETDATE())\'Add 3 days\';

Add 3 days

2011-06-11 19:11:58.123

  • select dateadd(dd,-3,GETDATE())\'Subtract 3 days\';

Subtract 3 days

2011-06-05 19:13:36.337

SQL-Training

dateadd i n d example
DATEADD(i,n,d) (example)

SELECT DATEADD(DAY,3,HireDate) AS age

FROM employee;

-> adds three days to the starting date of employment of every employee (see the sample database).

SQL-Training

dateadd fnc
DATEADD - FNC
  • ---

LEFT OUTER JOIN appr a WITH (NOLOCK) ON a.prsn_pk = fd.preparer

Where fd.service_id not in (\'49\',\'50\',\'200\',\'201\',\'202\',\'203\')

AND fd.status not in (\'XR\',\'W\',\'XS1\',\'XS2\',\'XS3\',\'N\',\'C\',\'CD\',\'CF\',\'CI\',\'CR\',\'L\',\'LD\',\'CL\')

and fd.due_from_vendor < getdate()

and fd.draft_date is null

and fd.assign_date is not null

AND (fd.completion_date IS NULL OR

fd.completion_date> dateadd(dd,-30,GETDATE()))

ORDER BY fd.due_from_vendor, fd.status, fd.service_id

SQL-Training

datepart datepart date
DATEPART ( datepart , date )

Returns the specified part item of a date date as an integer.

Examples:

SELECT DATEPART(month, \'01.01.2011\')\'Month\';

Month

1SELECT DATEPART(month, \'06.01.2011\')\'Month\';

Month

6

SELECT DATEPART(weekday, \'06.09.2011\')\'Weekday\';

Weekday

5

SELECT DATEPART(dd, getdate())\'today\';

today

9

http://msdn.microsoft.com/en-us/library/ms174420.aspx

SQL-Training

datediff item dat1 dat2
DATEDIFF(item,dat1,dat2)

Calculates the difference between the two date parts dat1 anddat2 and returns the result as an integer in units specified by the value item.

Example:

SELECT DATEDIFF(year, BirthDate, GETDATE()) AS age FROM employee;

-> returns the age of each employee.

SQL-Training

datediff fnc
DATEDIFF - FNC

use traininglegacy;

select cust_caseno \'LoanNumber\',

fd.doc_id \'DocID\',

p.fname + \' \' + p.lname \'Appraiser\',

fd.order_date \'OrderDate\',

fd.assign_date \'AssignDate\',

DATEDIFF(hh, order_date, assign_date) \'NumberOfHoursToAssign\',

fd.draft_date \'DraftDate\',

DATEDIFF(hh, assign_date, draft_date) \'NumberOfHoursToDraft\'

from folder_docfd with (nolock)

join folder f with (nolock) on f.folder = fd.folder

join preparer p with(nolock) on p.prsn_pk = fd.preparer

where fd.service_id in

(\'330\',\'365\',\'573\',\'406\',\'557\',\'559\',\'380\',\'574\',\'314\',\'553\',\'575\',\'576\',\'577\',\'578\')

and fd.order_date >= \'01/01/2011\'

and fd.order_date < \'05/01/2011\'

and fd.status =\'L\'

and assign_date is not null

and draft_date is not null

SQL-Training

datediff fnc1
DATEDIFF - FNC

select DATEDIFF(hh, assign_date, draft_date) \'NumberOfHoursToDraft\'

from folder_docfd with (nolock);

NumberOfHoursToDraft

268

NULL

select assign_date, draft_date, DATEDIFF(hh, assign_date, draft_date) \'Date Diff\'

from folder_docfd with (nolock);

assign_datedraft_dateDate Diff

2008-02-22 10:45:12.000 2008-03-04 14:42:38.000 268

2008-04-24 12:01:04.603 NULL NULL

2008-05-30 08:18:35.000 2008-05-30 08:23:59.000 0

SQL-Training

isnull expr value
ISNULL(expr, value)
  • Returns the value of expr if that value is not null; otherwise, it returns value (see Example 5.22).

FNC

select distinct

isnull(p.street_no,\'\')+\' \'+isnull(p.prefix,\'\') + \' \'+isnull(p.street,\'\')+\' \'+isnull(p.suffix,\'\') \'PropertyAddress\',

p.state \'ST\'

from property p

PropertyAddress ST

914201001 NW 30TH CT MS

82502 TEST LN MS

82518 TEST LN MS

SQL-Training

isnull fnc
ISNULL - FNC

select distinct

f.cust_caseno \'LoanNumber\',

fd.service_id \'Service\',

f.applicant_lname \'Borrower\',

isnull(p.street_no,\'\')+\' \'+isnull(p.prefix,\'\') + \' \'+isnull(p.street,\'\')+\' \'+isnull(p.suffix,\'\') \'PropertyAddress\',

p.state \'ST\',

fd.order_date \'OrderDate\',

fd.due_to_cust \'DueDate\',

fd.inspection_date \'InspectionDate\',

fd.draft_date \'DraftReceived\',

fd.doc_id \'DocID\',

fd.status \'Status\'

from folder f with (nolock)

join folder_docfd with (nolock) on fd.folder = f.folder

join doc_propdp with (nolock) on dp.doc_id = fd.doc_id

join property p with (nolock) on p.prop_id = dp.prop_id

where preparer = \'\'

SQL-Training

slide93

SELECT

fd.doc_id \'Doc_ID\',

f.cust_caseno \'Loan Number\',

CONVERT(VARCHAR (50),(LTRIM(RTRIM(ISNULL(p.street_no, \'\')))+ \' \' +

LTRIM(RTRIM(ISNULL(p.prefix, \'\')))+ \' \' +

LTRIM(RTRIM(ISNULL(p.street, \'\')))+ \' \' +

LTRIM(RTRIM(ISNULL(p.suffix, \'\'))))) \'Property Street Address\',

CONVERT(VARCHAR (35),ISNULL(p.city,\'\')) \'Property City\',

CONVERT(VARCHAR (2),ISNULL(p.state,\'\')) \'Property State\',

CONVERT(VARCHAR (10),ISNULL(p.zip,\'\')) \'Property Zip Code\',

CONVERT(DECIMAL (5),ISNULL(p.bed,\'\')) \'Total Bedroom Count\',

p.full_bath \'FullBath\',

p.half_bath \'HalfBath\',

CONVERT(DECIMAL (18,6),ISNULL(p.area_living,NULL)) \'Gross Living Area Square Feet Number (Square Feet)\',

ISNULL(p.year_built,NULL) \'Property Structure Year Built\',

CONVERT(VARCHAR (12),ISNULL(p.census,\'\')) \'Census Tract Identifer\',

CASE WHEN fd.service_id IN (452, 557) THEN \'Y\' ELSE \'N\' END \'Condominium Indicator\',

CASE WHEN fd.service_id = 555 THEN \'Y\' ELSE \'N\' END \'Manufactured Home Indicator\',

CONVERT(VARCHAR (5),ISNULL(p.unit_no,\'\')) \'Parsed Street Address Building Number\',

CONVERT(VARCHAR(20),fd.doc_id) \'Appraisal Identifier\',

fd.service_id \'Temp Service Name\',

SQL-Training

difference z1 z2
DIFFERENCE(z1,z2)
  • Returns an integer, 0 through 4, that is the difference of SOUNDEX values of two strings z1 and z2. (SOUNDEX returns a number that specifies the sound of a string. With this method, strings with similar sounds can be determined.) Example:SELECT DIFFERENCE(\'spelling\', \'telling\') = 2 (sounds a little bit similar, 0 = doesn\'t sound similar)

SQL-Training

difference z1 z2 examples
DIFFERENCE(z1,z2) - Examples
  • SELECT DIFFERENCE(\'spelling\', \'telling\')

2

  • SELECT DIFFERENCE(\'eat\', \'telling\')

0

  • SELECT DIFFERENCE(\'walk\', \'talk\')

3

  • SELECT DIFFERENCE(\'felling\', \'telling\')

3

SQL-Training

left z length
LEFT(z, length)
  • Returns the first length characters from the string z.
  • SELECT LEFT(\'Notebook\',1) -- N
  • SELECT LEFT(\'Notebook\',3) -- Not
  • SELECT LEFT(\'Notebook\',4) -- Note

SQL-Training

right z length
RIGHT(z,length)

Returns the last length characters from the string z. Example:

  • SELECT RIGHT(\'Notebook\',1) --- k
  • SELECT RIGHT(\'Notebook\',3) --- ook
  • SELECT RIGHT(\'Notebook\',4) -- book

SQL-Training

using common table expressions
Using Common Table Expressions
  • http://msdn.microsoft.com/en-us/library/ms190766.aspx

SQL-Training

stored procedures
Stored Procedures

CREATE PROCEDURE temp_Stor_P

AS

BEGIN

SELECT db1.dbo.agency.org_pk, db1.dbo.agency.u_id

FROM db1.dbo.agency

RETURN 0

END

How to use it—

>> temp_Stor_P;

SQL-Training

slide100

use db1;

CREATE PROCEDURE temp_Stor_P2

AS

BEGIN

SELECT dbo.agency.org_pk, dbo.agency.u_id

FROM dbo.agency

RETURN 0

END

temp_Stor_P2

SQL-Training

slide101

http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspxhttp://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

SQL-Training

common table expressions cte http msdn microsoft com en us library ms190766 aspx
Common Table Expressions (CTE)http://msdn.microsoft.com/en-us/library/ms190766.aspx
  • CTE- a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
    • Create a recursive query.
    • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
    • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
    • Reference the resulting table multiple times in the same statement.

SQL-Training

syntax structure for a cte
syntax structure for a CTE

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition)

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

USE AdventureWorks2008R2;

GO

– Define the CTE expression name and column list.

WITH Sales_CTE(SalesPersonID, SalesOrderID, SalesYear) AS

-- Define the CTE query.

( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL )

-- Define the outer query referencing the CTE name.

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear

FROM Sales_CTE

GROUP BY SalesYear, SalesPersonID

ORDER BY SalesPersonID, SalesYear; GO

SQL-Training

slide105

MasterTurntime

declare @startdatevarchar(20)

declare @enddatevarchar(20)

set @startdate = \'2008-07-01\'

set @enddate =\'2008-08-01\'

;with counts as (

select rece_from \'Channel\',

Case when (datediff(dd,fD.draft_date,fD.completion_Date))<1 then 1 else 0 end \'LessThan1\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=1 and datediff(dd,fD.draft_date,fD.completion_Date)<4) then 1 else 0 end \'OneToThree\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=4 and datediff(dd,fD.draft_date,fD.completion_Date)<8) then 1 else 0 end \'FourToSeven\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=8 and datediff(dd,fD.draft_date,fD.completion_Date)<13) then 1 else 0 end \'EightToTwelve\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=13 and datediff(dd,fD.draft_date,fD.completion_Date)<16) then 1 else 0 end \'ThirteenToFifteen\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=16 and datediff(dd,fD.draft_date,fD.completion_Date)<20) then 1 else 0 end \'SixteentoNineteen\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=20 and datediff(dd,fD.draft_date,fD.completion_Date)<24) then 1 else 0 end \'TwentyToTwentythree\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=24 and datediff(dd,fD.draft_date,fD.completion_Date)<28) then 1 else 0 end \'TwentyfourToTwentyseven\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=28 and datediff(dd,fD.draft_date,fD.completion_Date)<31) then 1 else 0 end \'TwentyeightToThirty\',

case when ((datediff(dd,fD.draft_date,fD.completion_Date))>=31 and datediff(dd,fD.draft_date,fD.completion_Date)<41) then 1 else 0 end \'ThirtyoneToFourty\',

case when datediff(dd,fD.draft_date,fD.completion_Date)>=41 then 1 else 0 end \'GreaterThan40\'

from folder_docfd with (nolock)

join folder f with (nolock) on f.folder = fd.folder

where fd.order_date >@startdate

and fd.order_date <@enddate

and fd.preparer is not null

SQL-Training

slide106

selectChannel,sum(LessThan1)\'<1\',

sum(OneToThree)\'1-3\', sum(FourToSeven)\'4-7\',

sum(EightToTwelve)\'8-12\', sum(ThirteenToFifteen)\'13-15\', sum(SixteentoNineteen)\'16-19\',

sum(TwentyToTwentythree)\'20-23\', sum(TwentyfourToTwentyseven)\'24-27\', sum(TwentyeightToThirty)\'28-30\',

sum(ThirtyoneToFourty)\'31-40\', sum(GreaterThan40)\'>40\'

from counts

group by channel

SQL-Training

triggers
Triggers
  •  special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table.
  • Microsoft® SQL Server™ allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement.
  • http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

SQL-Training

syntax
Syntax
  • CREATE TRIGGER trigger_nameON { table | view } [ WITH ENCRYPTION ] {     { {FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }         [ WITH APPEND ]         [ NOT FOR REPLICATION ]         AS         [ { IF UPDATE ( column )             [ { AND | OR } UPDATE ( column ) ]                 [ ...n ]         | IF ( COLUMNS_UPDATED ( ) { bitwise_operator} updated_bitmask)                { comparison_operator} column_bitmask [ ...n ]         } ] sql_statement [...n ]     } }

SQL-Training

variable names
Variable Names
  • MasterTurntime

declare @startdatevarchar(20)

declare @enddatevarchar(20)

set @startdate= \'2008-07-01\'

set @enddate=\'2008-08-01\'

Select …

From …

where fd.order_date>@startdate

and fd.order_date<@enddate

and fd.preparer is not null

SQL-Training

data types transact sql http msdn microsoft com en us library ms187752 aspx
Data Types (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms187752.aspx

SQL-Training

exact numerics
Exact Numerics

http://msdn.microsoft.com/en-us/library/ms187752.aspx

SQL-Training

date and time
Date and Time

SQL-Training

character strings
Character Strings

SQL-Training

shortcuts
Shortcuts
  • http://msdn.microsoft.com/en-us/library/ms174205.aspx

SQL-Training

ad