Part 1 - Selecting Data
Switch to a Database USE Family; Select table SELECT * FROM Person; Select data ’type:string’ in any field select 'kidd'; SELECT * FROM data source(s) WHERE condition -- a boolean condition SELECT * FROM dbo.Person WHERE 1 = 1; -- Comparison Operator SELECT * FROM dbo.Person WHERE PersonID <= 3; -- Between SELECT * FROM dbo.Person WHERE PersonID BETWEEN 2 AND 4; SELECT * FROM dbo.Person WHERE PersonID >= 2 AND PersonID <= 4; -- In Condition SELECT * FROM dbo.Person WHERE PersonID IN (1, 3); -- (Subquery) --Update UPDATE dbo.Person SET LastName = 'NewName' WHERE LastName = 'Kidd'; --to select certain fields SELECT * FROM dbo.Person WHERE LastName IN ('Ramsey', 'Miller'); Like Condition /* multiple characters single character match in range match not in range */
% _ [ ] [^ ]
SELECT * FROM dbo.Person WHERE LastName LIKE 'r%';
SELECT * FROM dbo.Person WHERE LastName LIKE '%son'; SELECT * FROM dbo.Person WHERE FirstName LIKE '[ABC]%'; SELECT * FROM dbo.Person WHERE FirstName LIKE '[^ABC]%';
-- Multiple Conditions SELECT * FROM dbo.Person WHERE PersonID = 1 OR FirstName LIKE 'Dav%' AND PersonID = 14; SELECT * FROM dbo.Person WHERE (PersonID = 1 OR FirstName LIKE 'Dav%') AND PersonID = 14; -- Working with Nulls -- Is Null USE Family SELECT FirstName, LastName, FatherID FROM Person WHERE FatherID IS NULL ORDER BY LastName, FirstName -- return datetime - date only / midnight time SELECT DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP), '20040101') -- Order By -- Column name or alias SELECT FirstName, LastName as Last FROM dbo.Person ORDER BY Last, FirstName;
SELECT * FROM dbo.Person ORDER BY 3, 2;
- [All] Distinct SELECT ALL FirstName FROM dbo.Person WHERE FIRSTNAME IN ('David', 'Gary', 'Melissa') ORDER BY FirstName SELECT DISTINCT FirstName FROM dbo.Person WHERE FIRSTNAME IN ('David', 'Gary', 'Melissa') ORDER BY FirstName -- Top SELECT TOP 5 * FROM dbo.Person WHERE PersonID IS NOT NULL ORDER BY PersonID