SELECT COUNT(DISTINCT column_name) FROM table_name |
SELECT TOP 10 Elevation FROM [devDevelopment].[dbo].[Developments] |
INSERT INTO "table1" ("column1", "column2", ...) SELECT "column3", "column4", ... FROM "table2" |
CREATE table Class (classNo VARCHAR(32) NOT NULL, facId VARCHAR(32) NOT NULL, schedule VARCHAR(32) NOT NULL, room VARCHAR(32) NOT NULL, Primary Key(classNo), foreign Key(facId) References Faculty(facId)); |
INSERT INTO Faculty (facId, name, department, rank) VALUES("F101", "Adams", "Art", "Professor"), ("F105", "Tanaka", "CSC", "Instructor"), ("F110", "Byrne", "Math", "Assistant"), ("F115", "Smith", "History", "Associate"), ("F221", "Smith", "CSC", "Professor"); |
SELECT lastName, firstName, stuId FROM Student WHERE stuId IN (SELECT stuId FROM Enroll WHERE classNo IN (SELECT classNo FROM Class WHERE facId ="F110")) ORDER BY lastName, firstName ASC; |
-- Convert the first character to uppercase -- and the rest to lowercase. select Name = -- Assuming that the length of DESCRIPTION is varchar(500) convert(varchar(500), upper(substring(Name,1,1))+ lower(substring(Name,2,499))) from Developments |
Copy a table into a new table SELECT * into NewTable FROM Table1 |
Update a column with a new value--for the whole table--will NOT copy constraints******* UPDATE [Table] SET Attribute='All records contain this string as a value' ?? Append ?? 'Where 1=1' |
Copy a table into a new table /* Delete Duplicates where there are two records. Change row count otherwise.*/ set rowcount 1; Delete from [Projects$] where ([HPCProjectNumber] = '07-210') set rowcount 0; to reset |
| XML Encoding and SQL Server Good Article on how SQL Server makes XML |
| Concatenation of to columns: Select ContactPerson.ContactId, ContactPerson.FirstName + ' ' + ContactPerson.LastName As Full_Name from [ContactPerson] |
| - SET IDENTITY_INSERT products ON - where substring([columnName],0,2)= 9 /* Get elements that start with 9 */ - order by [columnName] asc |
| - update with an inner join, good when needing to add a foreign key column to the 'main' table--thus making a 1 to many, rather than a many to many. update Project set Project.ContactPerson = PC.ContactId from ProjectContacts as PC inner join Project on Project.ProjectNumber = PC.ProjectNumber |
| - adding a foreign key constraint to a new table - how to find the error. SELECT [ProjectId],[ContactPerson] FROM [dbo].[Project] where ContactPerson not in (Select ContactId From [dbo].Contacts) |
| - get a unique / explicit ordering SELECT [TypeId] ,[TypeName] ,[Description] FROM .[dbo].[Type] order by ( case when lower(TypeName) = 'access/recreational access' then 0 when lower(TypeName) = 'aerial survey' then 1 when lower(TypeName) = 'dirt tank/stock pond - maintenance/enhancement/renovation' then 2 when lower(TypeName) = 'elk stewardship' then 3 when lower(TypeName) = 'equipment purchase' then 4 when lower(TypeName) = 'fence - inventory' then 5 when lower(TypeName) = 'fence - modification/enhancement' then 6 else 7 end) |
| --GeoRSSing Select [GeographyBinary].STAsText() As Gml, [ProjectNumber] from LocationTable where [GeographyBinary].STDimension() = 3 for xml raw('item'), elements |
-[GeographyBinary].STDimension() = 0 POINTS
-[GeographyBinary].STDimension() = 1 POLYLINE
-[GeographyBinary].STDimension() = 2 POLYGON
-USE [DB] GO
DECLARE @Coords Varchar;
Set @Coords = '-112.017 32.8072 , -111.555 32.7980 , -111.566 32.6131 , -111.951 32.6039 , -112.017 32.8072'
INSERT INTO [DB].[dbo].[Location]
(HPCProjectNumber, GeographyBinary)
VALUES (9999, geography::STGeomFromText('POLYGON(( '+ @Coords +' ))', 4326))
