CS计算机代考程序代写 SQL database Java Database Fundamentals

Database Fundamentals

SQL – Functions – CAST
CAST is used to convert an expression of one data type to another
Syntax
CAST ( expression AS data_type [ ( length ) ] )

Arguments
expression
Any valid expression.

data_type
Is the target data type (INT, VARCHAR, BIT etc)

length
Is an optional integer that specifies the length of the target data type. The default value is 30.

1
— change a value to text
CAST(count(actorID) AS varchar(50))

— change a number to text
CAST(5 AS varchar(50))

In SQL if you want to combine numbers and text, you must CAST the number as text first!

SQL Functions
DATE, STRING. . .
2

SQL – Functions – STRING
Functions are calculations performed by the DBMS
Common functions include:

Function Example Output
UPPER(col) UPPER(Name) Sam  SAM
LOWER(col) LOWER(Name) Sam  sam
RTRIM(col) RTRIM(Name) [Sam ]  [Sam]
LTRIM(col) LTRIM(Name) [ Sam]  [Sam]
LEN(col) LEN(Name) Returns int length of string
REVERSE(col) REVERSE(Name) Sam  maS

https://msdn.microsoft.com/en-us/library/ms181984.aspx
32
SELECT UPPER(‘Woodville’) AS Suburb

SQL – Functions – STRING
Functions are calculations performed by the DBMS
Common functions include:

Function Example Output
LEFT(string, length) LEFT(‘Sam’, 2) Sam  Sa
RIGHT(string, length) Right(‘Sam’, 2) Sam  am
CHARINDEX(string1, string2) CHARINDEX(‘Fun’, ‘DBFundamentals’) 3
SUBSTRING(col, start, length) SUBSTRING(Name, 1, 1) Returns char(s) at start position

https://msdn.microsoft.com/en-us/library/ms181984.aspx
33
CHARINDEX starts from 0 and counts to the length
SELECT SUBSTRING(FirstName, 1, 1) AS Initial

SQL – Functions – SUBSTRING
Syntax
SUBSTRING ( expression ,start , length )
Returns part of a character or text in SQL server
Arguments
expression
Is a character or text
start
Is an integer that specifies where the returned characters start.
If start is less than 1, the returned expression will begin at the first character that is specified in expression.
If start is greater than the number of characters in the value expression, a zero-length expression is returned.

length
Is a positive integer that specifies how many characters of the expression will be returned.
If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.
If length is negative, an error is generated and the statement is terminated.
5
More generous than Java strings!
SELECT SUBSTRING(FirstName, 1, 1) AS Initial

SQL – Functions – CHARINDEX
Syntax
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
Searches an expression for another expression and returns its starting position if found
Arguments
expressionToFind
Is a character expression that contains the sequence to be found.
expressionToFind is limited to 8000 characters.

expressionToSearch
Is a character expression to be searched.

start_location
Is an integer at which the search starts.
If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch.
6
More generous than Java strings!

SQL – Functions – STRING
Working with Strings in SQL is not easy:
7
DECLARE @address varchar(100) = ’13 Wayville road, Woodville, SA 5000′

SELECT
LEFT(@address, CHARINDEX(‘,’, @address) – 1) AS streetAddress,
LEFT(secondPart, LEN(secondPart) – CHARINDEX(‘ ‘, REVERSE(secondPart)) -1) AS suburb,
RIGHT(secondPart, CHARINDEX(‘ ‘, REVERSE(secondPart))) AS state,
REVERSE(SUBSTRING(REVERSE(@address), 1, 4)) AS postcode
FROM (
SELECT
RTRIM(
REVERSE(
SUBSTRING(
REVERSE(@address), 6, LEN(@address) – CHARINDEX(‘,’, @address) – 5
)
)
) AS secondPart
) AS t1

SQL – Functions – DATE
Functions are calculations performed by the DBMS
Common functions include:

Dateparts:
d | m | yy/yyyy => day | month | year number of the calander date
y |dy => day of year (note the difference from yy/yyyy!!!)
dw returns the day of the week number except when used with DATENAME where it returns the name of the day!
m will return the name of the month when used with DATENAME

Test it out for yourself by placing SELECT before the function
SELECT DATENAME(m, ’31/Dec/2015′)

Function Example Output
GETDATE() 01/09/2015
DATEPART(datepart, date) DATEPART(d, GetDate()) 3
DATENAME(datepart, date) DATENAME(dw, GetDate()) Wednesday
DATENAME(m, GetDate()) September
DATEADD(datepart, number, date) DATEADD(d, 7, GetDate()) Date 7 days from today!

https://msdn.microsoft.com/en-us/library/ms186724.aspx

37

8

SQL – Functions – CAST
CAST is used to convert an expression of one data type to another
Syntax
CAST ( expression AS data_type [ ( length ) ] )

Arguments
expression
Any valid expression.

data_type
Is the target data type (INT, VARCHAR, BIT etc)

length
Is an optional integer that specifies the length of the target data type. The default value is 30.

9
— change a value to text
CAST(count(actorID) AS varchar(50))

— change a number to text
CAST(5 AS varchar(50))

In SQL if you want to combine numbers and text, you must CAST the number as text first!

SQL CONTROL – IF and ELSE
Control statements allow actions to happen depending on a condition
The action may involve setting a value
The action may involve running a different query

Basic Syntax

10
IF ()
— Do Something

ELSE
— Do Something Else
IF (1 = 0)
SELECT ‘True’ AS Result

ELSE
SELECT ‘False’ AS Result

SQL CONTROL – IF and ELSE
Control statements allow actions to happen depending on a condition
The action may involve setting a value
The action may involve running a different query

Basic Syntax

11
IF ()
— Do Something

ELSE
— Do Something Else

IF (1 = 0) — (false)
SELECT ‘Unlikely’ AS Result

ELSE IF (1 = 2) — (false)
SELECT ‘Just as Unlikely’ AS Result

ELSE
SELECT ‘False’ AS Result

SQL CONTROL – CASE WHEN
Control statements allow actions to happen depending on a condition
The CASE WHEN statement is similar to if-else but can be used within a query to change a particular value

Basic Syntax

12
CASE
WHEN ()
THEN — Do Something

WHEN ()
THEN — Do Something a bit different

ELSE
— Do Something different again
END
SELECT (
CASE WHEN (1 = 0) — (false)
THEN ‘Unlikely’

WHEN (1 = 2) — (false)
THEN ‘Just as Unlikely’

ELSE
‘False’
END
)
AS Result

SQL CONTROL – CASE WHEN
Control statements allow actions to happen depending on a condition
The CASE WHEN statement is similar to if-else but can be used within a query to change a particular value
Example

13
SELECT FirstName, Surname, Salary,
(
CASE WHEN (E.Salary > 75)
THEN ‘Over Paid’

WHEN (E.Salary <= 40) THEN 'Under Paid' ELSE 'Adequately Paid' END ) AS PayConclusion FROM Employees AS E Departments(DeptName, Address, City) Employee(FirstName, Surname, Dept, Office, Salary, City) FK(Dept) -> Departments(DeptName)

Single column result called “PayConclusion” with value that depends on the employee salary

SQL – NULL replacement Value
ISNULL is a function that can be used to provide a value when an unknown or NULL value is returned
ISNULL(expressionORattribute, replacementValue)

Find ALL Simpsons characters and where available their first aired episode else show ‘TBA’
14
SELECT CharacterName,
ISNULL(EpisodeName, ‘TBA’) AS FirstEpisode
FROM Characters C LEFT OUTER JOIN Episodes E
ON C.EpisodeID = E.EpisodeID

SQL – Functions
Functions are calculations performed by the DBMS

Remember NULL + string = NULL 

SELECT SUBSTRING(FirstName, 1, 1) AS Initial
FROM Employees
Is this safe?
SELECT
CASE WHEN LEN(FirstName) > 0 THEN SUBSTRING(FirstName, 1, 1)
ELSE ”
END AS Initial
FROM Employees
This will work even if FirstName is Empty/NULL
LEN(NULL) will return Unknown which is not >0 (it is unknown)
Therefore the ELSE will be used and ‘’ will be the initial.
39

SQL CONTROL – IF and ELSE
Control statements allow actions to happen depending on a condition
The action may involve setting a value
The action may involve running a different query

Basic Syntax

16
IF ()
— Do Something

ELSE
— Do Something Else
IF (1 = 0)
SELECT ‘True’ AS Result

ELSE
SELECT ‘False’ AS Result

SQL CONTROL – IF and ELSE
Control statements allow actions to happen depending on a condition
The action may involve setting a value
The action may involve running a different query

Basic Syntax

17
IF ()
— Do Something

ELSE
— Do Something Else

IF (1 = 0) — (false)
SELECT ‘Unlikely’ AS Result

ELSE IF (1 = 2) — (false)
SELECT ‘Just as Unlikely’ AS Result

ELSE
SELECT ‘False’ AS Result

SQL CONTROL – CASE WHEN
Control statements allow actions to happen depending on a condition
The CASE WHEN statement is similar to if-else but can be used within a query to change a particular value

Basic Syntax

18
CASE
WHEN ()
THEN — Do Something

WHEN ()
THEN — Do Something a bit different

ELSE
— Do Something different again
END
SELECT (
CASE WHEN (1 = 0) — (false)
THEN ‘Unlikely’

WHEN (1 = 2) — (false)
THEN ‘Just as Unlikely’

ELSE
‘False’
END
)
AS Result

SQL CONTROL – CASE WHEN
Control statements allow actions to happen depending on a condition
The CASE WHEN statement is similar to if-else but can be used within a query to change a particular value
Example

19
SELECT FirstName, Surname, Salary,
(
CASE WHEN (E.Salary > 75)
THEN ‘Over Paid’

WHEN (E.Salary <= 40) THEN 'Under Paid' ELSE 'Adequately Paid' END ) AS PayConclusion FROM Employees AS E Departments(DeptName, Address, City) Employee(FirstName, Surname, Dept, Office, Salary, City) FK(Dept) -> Departments(DeptName)

Single column result called “PayConclusion” with value that depends on the employee salary

SQL – NULL replacement Value
ISNULL is a function that can be used to provide a value when an unknown or NULL value is returned
ISNULL(expressionORattribute, replacementValue)

Find ALL Simpsons characters and where available their first aired episode else show ‘TBA’
20
SELECT CharacterName,
ISNULL(EpisodeName, ‘TBA’) AS FirstEpisode
FROM Characters C LEFT OUTER JOIN Episodes E
ON C.EpisodeID = E.EpisodeID

/docProps/thumbnail.jpeg