Sql training
This presentation is the property of its rightful owner.
Sponsored Links
1 / 115

SQL Training PowerPoint PPT Presentation


  • 189 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

SQL Training

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


Sql training

SQL-Training


Sql training

SQL-Training


Sql training

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


Sql training

SQL-Training


Sql training

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


Sql training

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


Sql training

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


Sql training

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

SQL-Training


Sql training

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


Sql training

SQL-Training


Sql training

Referential Integrity Constraints for COMPANY database

SQL-Training


Sql training

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


Sql training

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


Sql training

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


Sql training

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


Sql training

Find employees who work for department number 5.

 employee

DNO = 5

SQL:

SELECT *

FROM employee

WHERE dno = 5;

SQL-Training


Sql training

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


Sql training

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


Sql training

E.g. Show the names of all employees

employeefname, minit, lname

SELECT fname, minit, lname

FROM employee;

SQL-Training


Sql training

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


Sql training

fname,minit,lname

DNO = 5

Employee

SQL-Training


Examples of applying select and project operations

Examples of applying SELECT and PROJECT operations

SQL-Training


Sql training

Example of Query Tree

SQL-Training


Sql training

Equijoin

SQL-Training


Query tree for equijoin

Query Tree for Equijoin

X ID = STUID

Credit_Hours

Student

SQL-Training


Sql training

Natural Join |X|

Is an equijoin which the repeated column is eliminated

Usually join performs over column with the same names

SQL-Training


Sql training

Remove this column

SQL-Training


Sql training

SQL-Training


Query tree for natural join

Query Tree for Natural Join

|X|

Credit_Hours

Student

SQL-Training


Sql training

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


Sql training

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


Sql training

Left Semi-Join

SQL-Training


Sql training

Right Semi-Join

SQL-Training


Sql training

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


Sql training

  • 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


Sql training

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


Sql training

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

WHEREE.SSN = D.ESSN(+);

SQL-Training


Sql training

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 Delay2

Tom Delay3

Tom Delay4

Tom Delay5

Larry Brown3

Jane English1

Jane English2

Joe Hugh1

Joe Hugh2

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 Delay2

Tom Delay3

Tom Delay4

Tom Delay5

Larry Brown3

NULLNULL5

NULLNULL6

NULLNULL6

Jane English1

Jane English2

NULLNULL4

Joe Hugh1

Joe Hugh2

NULLNULL4

NULLNULL6

SQL-Training


Right outer join

Right-outer join

select *

from student, credit_hours

where id(+) = stuid;

SQL-Training


Sql training

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


Sql training

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


Sql training

=

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


Sql training

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


Sql training

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


Sql training

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


Sql training

Division

SQL-Training


Example of division

Example of 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


Sql training

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


Sql training

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


Sql training

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

FunctionExplanation

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


T sql date functions used by fnc

T-SQL -- Date functionsUsed by FNC

SQL-Training


T sql string functions

T-SQL -- String Functions

SQL-Training


Sql training

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


Sql training

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


Sql training

  • 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.0002008-03-04 14:42:38.000 268

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

2008-05-30 08:18:35.0002008-05-30 08:23:59.0000

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

    PropertyAddressST

    914201001 NW 30TH CTMS

    82502 TEST LNMS

    82518 TEST LNMS

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


Sql training

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


Sql training

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


Sql training

http://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


Sql training

SQL-Training


Sql training

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


Sql training

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


Sql training

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


  • Login