Useful SQL SELECT Statements That Must Be Glanced

The Following Queries are for the people who have some basic idea about databases and tables and these are some of the useful statements, mainly used in developing real time projects, so just remember their usage.

SINGLE TABLE

General Queries

SELECT FirstName AS 'First Name', LastName AS 'Last Name' FROM Customers

SELECT FirstName + ' ' + LastName AS 'Customer Name' FROM Customers

SELECT * FROM Products WHERE ProductID = 3

SELECT * FROM Products WHERE ProductPrice > 30

SELECT * FROM Products WHERE ProductID <> 2

SELECT * FROM Orders WHERE OrderDateTime > '1/10/2006'

(Note If You Dont Specify Time It Assumes 12.00A.M, Midnight)

Arithmetic Functions

SELECT Sum(TAX) AS 'TaxesCollected FROM Orders

SELECT Count(*) AS 'Total Orders' FROM Orders

(Shows NUMBER of Records, but not their values)

SELECT ProductID, ProductPrice AS 'Regular Price', ProductPrice - 10 AS 'Sales Price'

SELECT ProductID, ProductPrice AS 'Regular Price', ProductPrice * .9 AS 'Sales Price'

(10% Discount)

SELECT Min(ProductPrice) FROM Products

(Shows Only One Min Value In The Coloumn)

SELECT Max(ProductPrice) FROM Products

(Shows Only One Min Value In The Coloumn)

SELECT Avg(ProductPrice) FROM Products

(Shows Only Average of all Values In The Coloumn)

Date Functions

SELECT OrderID, Month(OrderDateTime), Day(OrderDateTime) From Orders

SELECT GetDate() (Returns the current date and Time)

--- Use The below functions to return the required value from date datatype.

--- yy = year

--- mm = month

--- dd = day

--- hh = Hour

--- mi = Minute

--- weekday = (Sunday through Saturday)

SELECT datename(mm,OrderDateTime) FROM Orders

(datename function gives detail info as sunday,etc)

SELECT datepart(mm,OrderDateTime) FROM Orders

(datepart function gives only no of the month Eg. 1 for january)

SELECT datediff(dd, GetDate(), OrderDateTime) FROM Orders

(Returns Diff Btn Two Dates)

SELECT dateadd(dd, 5, OrderDateTime) FROM Orders

(adds 5 days)

String Functions

SELECT substring(ProductDescription, 3, 5) FROM Products

--Starting At the 3rd Character and retriving next 5 characters

SELECT right(ProductName,5) FROM Products

--Select theRight Most 5 Characters

SELECT left(ProductName,5) FROM Products

--Select the Left Most 5 Characters

Upper Case

SELECT upper(ProductName) FROM Products

Lower Case

SELECT lower(ProductName) FROM Products

Reverse

SELECT reverse(ProductName) FROM Products

Wild Cards With LIKE

--% = any

SELECT * FROM Customers WHERE FirstName LIKE 'J%'

Using IN to filter data

SELECT * FROM Customers WHERE STATE IN ('AP', 'UP', 'TN')

Sorting data

ASC for Ascending, DESC for Descending, default is ASC

SELECT * FROM Customers ORDER BY lastname ASC

SELECT * FROM Customers ORDER BY lastname, firstname (Ordering 1st by lastname then by firstname)

GROUP BY statement

SELECT CustomerID, count(*), sum(shipping) FROM Orders GROUP BY CustomerID

Here GROUP BY is a little bit tricky, but easy when understand clearly. The above statement returns ???? We know that each customer can have several orders and their corresponding shipping values. If we want to have the total number of orders we simply use count(*), but if we want to know total number of orders placed by EACH AND EVERY CUSTOMER, we have to group them using GROUP BY. Hope you understand well.

HAVING statement

SELECT CustomerID, count(*), sum(shipping) FROM Orders GROUP BY CustomerID HAVING count(*) >= 5

Hope u understand this. In general the HAVING statement is used along with the GROUP BY.

TWO RELATED TABLES

While Going through these statements, one should have a brief idea about primary key and foreign key working. Hope u know those things, if not please refer to them.

Joins

The first and the interesting thing is JOINS, these are little bit tricky but after u understand we can play with them.

There are 2 types of Joins

1. Inner Joins

2. Outer Joins

Frankly speaking, i hav never came through outer joins while developing websites, all our real time needs are covered with inner joins so here i am going to speak about INNER Joins.

SELECT Products.ProductID, Products.ProductName, ProductTypes.ProductTypeName FROM Products INNER JOIN ProductTypes ON Products.ProductTypeID = ProductTypes.ProductTypeID

Confused..!!!, Dont worry i know thi is a little bit tricky, i will tell a SHORT CUT for this, but before that try to understand this traditional statement.

To Understand this imagine that there are two tables "Products' & 'ProductTypes', by their names u can understand what data they contain, they both contain one common coloumn primary key i.e., ProductID

Now try to understand the above query again, the '.' is a connectivity between table and its coloumn. Observe the FROM keyword it should give the table details which are linked. Here the products table is linked with ProductTypes table so we used a Inner Join btn(between) them. Now the ON keyword, its requirement is very much needed, here it filters the details by telling that the ProductID's should match in both tables. If it is not mentioned the data will repeat many times. These queries need some practice. Hope i have contributed something for u.

SELECT p.ProductID, p.ProductName, pt.ProductTypeName FROM Products p INNER JOIN ProductTypes pt ON p.ProductTypeID = pt.ProductTypeID

This is same as the earlier but just used alias names for the tables, hope u dont need explanation

Using WHERE with JOIN

SELECT p.*, pt.ProductTypeName FROM Products p INNER JOIN ProductTypes pt ON p.ProductTypeID = pt.ProductTypeID WHERE p.ProductPrice > 30

THREE or MORE RELATED TABLES

SELECT c.LastName, o.OrderID, p.ProductName FROM Customers c
INNER JOIN Orders 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

However, i think i covered the major real time useful queries which will be helpful in ur projects, there may be something that i left. If u find anything, post them as a comment.

Advise: Always try the queries on the database in offline before making the application online.

Hope this article may be useful.

Next Lesson is about Sub Queries Or Sub-Select Statements