|
Queries
SQL Examles | Cast
Examples
1. Declare variable : DECLARE @variable
datatype
2. Concatenate operator : +
3. Alias : AS
4. Convert Int to Char : CAST or CONVERT
// convert has 3rd argument in format
5. Remove Duplication: DISTINCT
6. Aggregate function
AVG(col) //average of col
COUNTt(col) // # of row
MAX (col) //highest value of col
MIN(Col) //lowest value of col
SUM(col) //return totol sum of col
eg
| |
|
Use Northwind
go
Declare @fn varchar(20) , @mn varchar(20),
@ln varchar(20)
Set @fn='soon' // only set one value to variable
Select @mn='chian' , @ln='lim'
// assign values to
more variables in one line
Select Distinct
@fn AS firstname, @mn AS middlename, @ln AS lastname
OR
select 'first name is '+ @fname,
'mid name is ' + @mname, 'lastname
is '+ @lname
Select ‘ the cost per unit of ‘ + productname
+ ‘is ‘ + CAST
(unitprice as varchar(10) )From Product
WHERE |
| |
|
Select lastname , firstname
From employees
Where lastname LIKE 'b%'
AND
city NOT IN('seattle',
'redmond', 'tacoma')
|
Declare
@tname varchar (20), @query varchar(100)
set @tname='shippers'
set @query='select * from ' + @tname
exec (@query)
|
Select lastname , firstname ,
city
From employees
Where city <> 'london' //Not =
AND
Hiredate BETWEEN
‘1993.1.1’ and ‘1993.2.3’ |
SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID
//unmatch row in Employees
will be listed in result set |
Select title , count(*)
as number
From employees
Group by title
Having count(*) >=0 and count(*)<7
Order by title asc |
Select Employees.Name, Orders.Product
FROM Employees RIGHT JOIN
Orders
ON Employees.Employee_ID=Orders.Employee_ID
//unmatch row in Orders
will be listed in result set |
Select lastname, firstname
Into #saleemployee
From employees
Where title='Sales Representative'
select * from #saleemployee
|
Select E_Name FROM Employees_Norway
UNION
Select E_Name FROM Employees_USA
// equal result will be listed from both statements |
| |
|
Back
|
| |
|
| |
| |
| |
|
