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
Read Full Post »
All My Doubts 