SQL or T-SQL Statement/syntax are quite important for the database developer, here are some selected basic statements with example using SQL Server that may help you to get started with sql query.
Basic SELECT statement, * means all columns
SELECT * FROM Customers
Basic SELECT statement, specifying which columns to display
SELECT FirstName, LastName FROM Customers
Basic SELECT statement, changing the field name
SELECT FirstName as ‘First Name’, LastName as ‘Last Name’ FROM Customers
Basic SELECT statement, concatenating and formatting columns
SELECT FirstName + ‘ ‘ + LastName as ‘Customer Name’ From Customers
Selecting a record using a WHERE clause to specify the Product ID
SELECT * FROM Products WHERE ProductID=3
Selecting records using a WHERE clause to specify that you want
Products that have a price greater than $30
SELECT * FROM Products WHERE ProductPrice>30
Selecting records using a WHERE clause to specify that you want
Products that have a price less than $30
SELECT * FROM Products WHERE ProductPrice<30
Selecting records using a WHERE clause to specify that you want
Products that have a price GREATER OR EQUAL TO $29.95
SELECT * FROM Products WHERE ProductPrice>=29.95
Selecting records where ProductTypeID is NOT EQUAL to 2
SELECT * FROM Products WHERE ProductTypeID <>2
Selecting records where ProductName is NOT EQUAL to ‘Inspired By Widgets’
SELECT * FROM Products WHERE ProductName <> ‘Inspired By Widgets’
Selecting records where ProductName is EQUAL to ‘Inspired By Widgets’
SELECT * FROM Products WHERE ProductName =‘Inspired By Widgets’
Selecting records where Orders were between a certain date range
IMPORTANT!! If you don’t specify the TIME, it assumes 12:00:00 AM…….midnight
SELECT * FROM Orders WHERE OrderDateTime> ‘1/10/2006’
Selecting records where Orders were between a certain date range
IMPORTANT!! If you don’t specify the TIME, it assumes 12:00:00 AM…….midnight
SELECT * FROM Orders WHERE OrderDateTime> ‘1/10/2006’ AND OrderDateTime < ‘1/13/2006’
Select records where Orders are NOT between a certain date range
SELECT * FROM Orders WHERE OrderDateTime< ‘1/9/2006’ OR OrderDateTIme > ‘1/12/2006’
Calculate the total tax collected from all orders
SELECT SUM (Tax) as ‘Taxes Collected’ FROM Orders
Calculate the total number of orders in the database
SELECT Count (*) as ‘Total Orders’ FROM Orders
Display the regular price of all products, and the price less a $5 discount
SELECT ProductID, ProductPrice as ‘Regular Price’, ProductPrice-5 as ‘Sale Price’ FROM Products
Display the regular price of all products, and price with a 10% discount
SELECT ProductID, ProductPrice as ‘Regular Price’, ProductPrice*.9 as ‘Sale Price’ FROM Products
Display the least expensive item
SELECT Min(ProductPrice) FROM Products
Display the most expensive item
SELECT Max(ProductPrice) FROM Products
Display the average cost of all items
SELECT Avg(ProductPrice) FROM Products
Display only the ID, Year and Month of each order
SELECT OrderID, OrderDateTime, Month(OrderDateTime, Day(OrderDateTime) FROM Orders
Get the current date and time (Useful for the default value columns property
SELECT GetDate()
User the datename function to return the ‘friendly’ date name
–yy=Year
–mm=Month
–dd=Day
–hh=Hour
–mi=Minute
–weekday=(Sunday through Saturday)
Example:
SELECT datename(mm, OrderDateTime) FROM Orders
Similar to above, but returns an integer representing the date part you specify
SELECT datepart(mm, OrderDateTime) FROM Orders
Returns the difference between two dates in DAYS (dd)
SELECT datediff(dd, GetDate(), OrderDateTime) FROM Orders
Adds the specified time (in this case 5 days) to each OrderDateTime
SELECT dateadd(dd, 5, OrderDateTime) FROM Orders
Adds the specified time (in this case 5 days) to each OrderDateTime
SELECT OrderDateTime, dateadd(dd, 5, OrderDateTime) FROM Orders
Selects a subset of an entire field of information. In this case
I’m starting at the 3rd character and retrieving the next 5 characters.
SELECT substring(ProductDescription, 3, 5) FROM Products
SELECT right(ProductName, 5) FROM Products
SELECT left(ProductName, 5) FROM Products
–Upper case
SELECT upper(ProductName) FROM Products
–Lower Case
SELECT lower(ProductName) FROM Products
Reverses the text…not sure how useful, but if you were Harry Carey
this would have come in handy.
SELECT reverse (ProductName) FROM Products
Use LIKE to find records the fit a pattern
SELECT * FROM Customers WHERE FirstName LIKE ‘J%’
Only selecting those customers that are in one of the list of
possibilities…Illinois, Kansas or Pennsylvania
SELECT * FROM Customers WHERE StateProvince IN (‘IL’, ‘KS’, ‘PA’)
Ordering the customers by their last name
SELECT * FROM Customers ORDER BY LastName ASC
SELECT * FROM Customers ORDER BY LastName DESC
Ordering primarily by lastname, but secondarily by firstname
SELECT * FROM Customers ORDER BY LastName, FirstName
Calculate the total amounts paid by each customer for shipping
SELECT CustomerID, SUM (Shipping) FROM Orders GROUP BY CustomerID
SELECT CustomerID, Count(*), SUM (Shipping) FROM Orders GROUP BY CustomerID
Calculate the total amounts paid by each customer for shipping
ONLY IF the customer has purchased 3 or more times
SELECT CustomerID, SUM (Shipping) FROM Orders GROUP BY CustomerID HAVING COUNT (*) >=3
JOINS!!! OK, here’s where it gets intersting………….
SELECT Products, ProductID, Products.ProductName, ProductTypes.ProductTypeName FROM Products INNER JOIN ProductTypes ON Products.ProductTypeID=ProductTypes.ProductTypeID
This can be shortened using aliases….
SELECT p.ProductID, p.ProductName, pt.ProductTypeName FROM Products p INNER JOIN ProductTypes pt ON p.ProductTypeID=pt.ProductTypeID
You can use CLAUSES, and just about everything,
although you always have to be aware of the join.
SELECT p.*, pt.ProductTypeName FROM Products p INNER JOIN ProductTypes pt ON p.ProductTypeID=pt.ProductTypeID WHERE p.ProductPrice >29.95
You can join THERE or MORE TABLES using the name basic formula
SELECT c.LastName, o.OrderID, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID=o.CustomerID INNER JOIN OrderItems oi ON o.OrderID=oi.OrderID INNER JOIN Products p ON p.ProductID=oi.ProductID ORDER BY c.LastName, o.OrderID
