#AskMe

Basic SQL Statements with Example

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