Technology, sound and fruit cake

• Home
• Site tree
• Sion who?

Sion IT

SELECT COUNT(forename) FROM Table1 WHERE forename like 'simon';

SELECT custid, forename, surname FROM table1 

	WHERE (forename IS not null) 

	ORDER BY forename, surname

select * from test_Results 

	where Test_Name like 'ServerService%'

Latest date only:

select top 1 *from BO_alive order by ba_run_time desc

How many states do we have customers in?

SELECT COUNT(DISTINCT customer_state) AS total FROM customers

select top 1 *from BO_alive order by ba_run_time desc

The database has two tables (gender and people), gender contains 1 a PK (GenderID) and 1 column (Gender). People table contains a few columns (p_name, p_age, p_town, gender). genderID is linked to genderID.gender table.

Here are 2 example joins that produce the same result.

SELECT p_name, p_Age, p_town, gender

	FROM people INNER join gender 

	ON (people.genderID = gender.genderID)

 go

 SELECT p_name, p_Age, p_town, gender 

	FROM people, gender

	WHERE people.genderID = gender.genderID

Adding a third table of country, with countryID and country columns, we can use the following to select all records:

SELECT p_name, p_Age, p_town, gender, country 

	FROM people, gender, country

	WHERE country.countryID = people.countryID

	and gender.genderID = people.genderID;

SELECT SSLogin,Left(UserLogon, PatIndex('%@%', UserLogon + '1') - 1) as SSLogin, SSDisplayname, Firstname, Lastname 

	FROM SS, BS

	where (BS.DisplayName = ss.ssdisplayName)

or similarly for a fixed amount chopped off the right:

SELECT SSLogin,Left(UserLogon,(len (UserLogon)-9)) as SSLogin, SSDisplayname,Firstname,Lastname 

	FROM SS, BS

	where (BS.DisplayName = ss.ssdisplayName)

Update is used to update EXISTING data, the syntax is:

UPDATE   TABLENAME   SET   COLUMN = "VALUE"   WHERE  COLUMN = "WHATEVER"

Examples

UPDATE customers SET salesperson = 'Mike' WHERE STATE = 'NH'

Update one column based on a column in another table

UPDATE nonVSphereVMs 

set vmIP = dns.ip from dns

where nonVSPhereVMS.vmDNS like dns.DNSname+'%'

UPDATE customers SET salesperson = 'Mike' WHERE STATE LIKE 'N%'

update Components SET CheckedOutLocation = 'D:\dsa-temp-se\' 

WHERE ItemName = 'DSA_2.pds'

DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00;

Clear down or elete all rows from a table

DELETE FROM Sales.SalesPersonQuotaHistory;

Delete rows based on a subquery

USE AdventureWorks2008R2;

GO

DELETE FROM Sales.SalesPersonQuotaHistory 

WHERE BusinessEntityID IN 

    (SELECT BusinessEntityID 

     FROM Sales.SalesPerson 

     WHERE SalesYTD > 2500000.00);

GO

Insert is used to squirt in NEW data

INSERT INTO customers(customer_id, customer_name)

    VALUES('12345', 'GIS Experts')

Copy data from one table to another

SELECT * into countrybackup

	FROM country

Copy table from one database table to another database

SELECT * into Test2.dbo.country

	FROM test1.dbo.country

Copy data from one table in to a pre existing table in another database

INSERT INTO test2.dbo.country

SELECT * FROM test1.dbo.country