We will be working on the script attached during our posts . You can copy and paste the script in Microsoft SQL 2012 as we will be working on the tables , views , stored procedures , functions and other objects in the script.

Copy , paste and execute the whole script in a new MS SQL 2012 management Studio on the New Query tab to create the TSQL2012 database.

Attached is the script below in the TSQL2012.sql file .

SQL stands for : Structured Query Language.

Was designed to manage and maintain data in Relational Database Management Systems(RDBMS) which revolves around relational model and predicate logics.

Consider a Database as a main box which contains inside it different boxes(schemas) and those boxes(schemas) contain different objects like (tables,views, stored procedures and functions etc)

• When It is said that Objects are contained in the Schema ,so the best advantage of the Schema level is Security . You can assign the different users of a database , a certain amount of privileges/rights ;

• For example:

– Allowing User A : To use only select statements .

– Allowing User B : To see Encrypted Stored Procedures allowing all other privileges.

– Allowing User C : Not giving him rights to delete, update or execute any stored procedure.

The Below picture illustrates the design of Database ,schema and object . A schema can contain multiple objects in it .

In this blog , we shall teach how a SQL Processes the select statement .  Processing SQL Statement means here how SQL  sees , perceives and processes and gives the final result of SQL select Statement .

For Example :

=================================================

SELECT studentid, Firstname, Lastname,count(*) as Total

FROM dbo.Student

WHERE Studentid = 10

GROUP BY studentid, Firstname, Lastname

HAVING COUNT(*) > 1

ORDER BY studentid

===============================================

Now note the order of how the Select statements are actually processed IN SQL :

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Explanation:

1. Get the rows from the table
2. Chooses only those rows where the studentID is 10
3. Groups the records by studentid, Firstname, Lastname
4. It groups studentid, Firstname, Lastname Having more than one record
5. Select returns for the grouped rows
6. Orders sorts the rows in the output by studentid

The case statements are similar to the switch cases like in C/C#. The CASE executes the statement which is logically correct and matches the condition.

Take the following example , that the you want to select a particular teachername from the list through teacherId column, from the following table StudentDetail :

Select studentid, studentname, teacherid, teachername  from   StudentDetail 

Using the case expression :

SELECT studentid, studentname, teacherid, teachername
CASE teacherid
WHEN 1 THEN ‘Teacher1’
WHEN 2 THEN ‘Teacher2’
WHEN 3 THEN ‘Teacher3’
WHEN 4 THEN ‘Teacher4’
WHEN 5 THEN ‘Teacher5’
WHEN 6 THEN ‘Teacher6’
WHEN 7 THEN ‘Teacher7’
WHEN 8 THEN ‘Teacher8’
ELSE ‘Unknown’
END
FROM StudentDetail order by studentid

With Group by we can arrange rows in groups and apply aggregate functions against those groups.

USE TSQL2012;
SELECT COUNT(*) AS numorders
FROM Sales.Orders;

This query generates the following output.

numorders
———–
830

Because there is no GROUP BY clause, all rows queried from the Sales.Orders table are arranged in one group, and then the COUNT(*) function counts the number of rows in that group. Grouped queries return one result row per group, and because the query defines only one group, it returns only one row in the result set.

Now Using GROUP BY clause, you can group the rows based on a specified grouping set of expressions.

For example, the following query groups the rows by shipper ID and
counts the number of rows (orders, in this case) per each distinct group.
SELECT shipperid, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY shipperid;

This query generates the following output.
shipperid     numorders
———–          ———–
1                    249
2                   326
3                   255

The query identifies three groups because there are three distinct shipper IDs.

The grouping set can be made of multiple elements. For example, the following query groups the rows by shipper ID and shipped year.
SELECT shipperid, YEAR(shippeddate) AS shippedyear,
COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY shipperid, YEAR(shippeddate);

shipperid         shippedyear            numorders
———–                ———–                     ———–
1                           2008                         79
3                           2008                         73
1                         NULL                         4
3                         NULL                         6
1                          2006                         36
2                          2007                        143
2                         NULL                        11
3                          2006                         51
1                          2007                        130
2                          2008                        116
2                          2006                        56
3                          2007                        125

Notice that you get a group for each distinct shipper ID and shipped year combination that exists in the data, even when the shipped year is NULL. Remember that a NULL in the shippeddate column represents unshipped orders, so a NULL in the shippedyear column represents the group of unshipped orders for the respective shipper.
If you need to filter entire groups, you need a filtering option that is evaluated at the group level not like the WHERE clause, which is evaluated at the row level. For this, SQL provides the HAVING clause. Like the WHERE clause, the HAVING clause uses a predicate but evaluates the predicate per group as opposed to per row. This means that you can refer to aggregate computations because the data has already been grouped.

For example, suppose that you need to group only shipped orders by shipper ID and shipping year, and filter only groups having fewer than 100 orders. You can use the following query to achieve this task.
SELECT shipperid, YEAR(shippeddate) AS shippedyear,
COUNT(*) AS numorders
FROM Sales.Orders
WHERE shippeddate IS NOT NULL
GROUP BY shipperid, YEAR(shippeddate)
HAVING COUNT(*) < 100;
This query generates the following output

shipperid         shippedyear        numorders
———–              ———–                    ———–
1                       2008                          79
3                       2008                          73
1                       2006                          36
3                       2006                          51
2                       2006                          56

Notice that the query filters only shipped orders in the WHERE clause. This filter is applied at the row level conceptually before the data is grouped. Next the query groups the data by shipper ID and shipped year. Then the HAVING clause filters only groups that have a count of rows (orders) that is less than 100. Finally, the SELECT clause returns the shipper ID, shipped year, and count of orders per each remaining group.

The following query invokes the COUNT(*) function, in addition to a number of general set functions, including COUNT.

SELECT shipperid,
COUNT(*) AS numorders,
COUNT(shippeddate) AS shippedorders,
MIN(shippeddate) AS firstshipdate,
MAX(shippeddate) AS lastshipdate,
SUM(val) AS totalvalue
FROM Sales.OrderValues
GROUP BY shipperid;

This query generates the following output (dates formatted for readability).
shipperid   numorders   shippedorders  firstshipdate lastshipdate       totalvalue
———–          ———-             ————             ————-          ————-           ———–
3                       255                    249                    2006-07-15     2008-05-01       383405.53
1                       249                    245                    2006-07-10    2008-05-04       348840.00
2                       326                    315                    2006-07-11     2008-05-06       533547.69

The difference in count(*) and count(shippeddate) is count(*) does not ignore NULL where count(shippeddate) does.

With general set functions, you can work with distinct occurrences by specifying a DISTINCT clause before the expression, as follows.
SELECT shipperid, COUNT(DISTINCT shippeddate) AS numshippingdates
FROM Sales.Orders
GROUP BY shipperid;

This query generates the following output.
shipperid        numshippingdates
———–               —————–
1                          188
2                          215
3                          198

SELECT shipperid, COUNT(shippeddate) AS numshippingdates
FROM Sales.Orders
GROUP BY shipperid;

This query generates the following output.
shipperid        numshippingdates
———–               —————–
1                          249
2                          245
3                          315

Note that the DISTINCT option is available not only to the COUNT function, but also to other general set functions. However, it’s more common to use it with COUNT.

SELECT S.shipperid, S.companyname,
COUNT(*) AS numorders
FROM Sales.Shippers AS S
INNER JOIN Sales.Orders AS O
ON S.shipperid = O.shipperid
GROUP BY S.shipperid, S.companyname;
This query generates the following output.
shipperid            companyname               numorders
———–                       ————–                        ———–
1                               Shipper GVSUA               249
2                               Shipper ETYNR               326
3                               Shipper ZHISN                 255

Thanks

Truncate :

This statement removes all rows from the SQL table . The Truncate statement frees the space used by the truncated table . It does not create logs and adds additional space to memory.

Delete:

This statement removes specified rows as required by user in the WHERE clause from the SQL table . However, the Deleted needs to be Commit or Rollback in the SQL.The deleted data is stored in form of Logs ,so the deleted data can be reverted back to original state or in short ‘Undo’ the delete statement . Moreover , when logs are made , it creates additional space in the memory . So , we can say that Delete is costly statement when it comes to memory !

Thanks

To check the number of SQL Servers installed on a LAN or network , we can use the following Commands on your commandline(cmd) :

• osql -L
• sqlcmd -L

Also , we can run the following command on Powershell to check the SQL Servers installed on a network with detailed results :

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Tables are the database structures where your data actually gets stored.

Now , we shall create a database table  ,you can write the create table below script to create  :

use mydatabase;       –assuming this is the name of your database

CREATE TABLE dbo.Student      — dbo is schema & student is table name
(
studentid INT NOT NULL,
firstname VARCHAR(20) NOT NULL,
lastname VARCHAR(20) NOT NULL,
admissiondate DATE NOT NULL,
phoneNumber VARCHAR(18) NOT NULL,
TeacherId INT NULL
);