70-461 Querying MS SQL Server2012 course

95 Articles

Using Joins

Last Updated On : Tuesday, May 22, 20181) SELECT D.n AS theday, S.n AS shiftno FROM dbo.Nums AS D CROSS JOIN dbo.Nums AS S WHERE D.n <= 7 AND S.N <= 3 ORDER BY theday, shiftno; (The cross product will give cartitian product m*n i.e. each row multiplied by each row of second table ,this […]

Few examples

Last Updated On : Thursday, May 17, 2018Beside select satement there are other commonly used query where exists or not exists (use subquery with it). Few examples are as follow. SELECT custid, companyname¬† FROM Sales.Customers AS C WHERE EXISTS (SELECT * FROM Sales.Orders AS O WHERE O.custid = C.custid AND O.orderdate = ‘20070212’); you can […]

Working with builtin Functions

Last Updated On : Tuesday, May 22, 2018There are many functions provoded by SQL e.g. substring, len, left, right, print, avg , count,min,max,sum,variance, stdev , cast,Convert , datediff ,dateadds, isnumeric, isdate, year , month,day,getdate ,lower,upper,ltrim,rtrim,isnull, replace etc. Few examples are as follow. SELECT CAST(‘abc’ AS INT) ; SELECT shipperid,COUNT(*) AS numorders, COUNT(shippeddate) AS shippedorders, MIN(shippeddate) […]

Choosing appropriate Datatypes

Last Updated On : Tuesday, May 22, 2018Choosing the appropriate data types for your attributes is probably one of the most important decisions that you will make regarding your data. SQL Server supports many data types from different categories: exact numeric (INT, NUMERIC), character strings (CHAR, VARCHAR),Unicode character strings (NCHAR, NVARCHAR), approximate numeric (FLOAT, REAL), […]

Understanding Queries

Last Updated On : Tuesday, May 22, 2018Transact-SQL (T-SQL) is the main language used to manage and manipulate data in Microsoft SQL Server. Data can be stored in the form of tables. Data can be stored in the columns/fields defined in table. Each column has datatype e.g¬† numeric, datetime, varchar, decimal, image,identity, nchar, bit,float etc.Table […]