There Is No Constitutional Right to Education. That Needs To Change.

Legal scholar Derek Black argues that the 14th Amendment should have included a right to education.

Public school funding has shrunk over the past decade. School discipline rates reached historic highs. Large achievement gaps persist. And the overall performance of our nation’s students falls well below our international peers.

These bleak numbers beg the question: Don’t students have a constitutional right to something better? Many Americans assume that federal law protects the right to education. Why wouldn’t it? All 50 state constitutions provide for education. The same is true in 170 other countries. Yet, the word “education” does not appear in the United States Constitution, and federal courts have rejected the idea that education is important enough that it should be protected anyway.

After two decades of failed lawsuits in the 1970s and ‘80s, advocates all but gave up on the federal courts. It seemed the only solution was to amend the Constitution itself. But that, of course, is no small undertaking. So in recent decades, the debate over the right to education has mostly been academic.

The summer of 2016 marked a surprising turning point. Two independent groups – Public Counsel and Students Matter – filed lawsuits in Michigan and Connecticut. They argue that federal law requires those states to provide better educational opportunities for students. In May 2017, the Southern Poverty Law Center filed a similar suit in Mississippi.

At first glance, the cases looked like long shots. However, my researchshows that these lawsuits, particularly in Mississippi, may be onto something remarkable. I found that the events leading up to the 14th Amendment – which explicitly created rights of citizenship, equal protection and due process – reveal an intent to make education a guarantee of citizenship. Without extending education to former slaves and poor whites, the nation could not become a true democracy.

Why a federal right to education matters

Even today, a federal constitutional right to education remains necessary to ensure all children get a fair shot in life. While students have a state constitutional right to education, state courts have been ineffective in protecting those rights.

Without a federal check, education policy tends to reflect politics more than an effort to deliver quality education. In many instances, states have done more to cut taxes than to support needy students.

And a federal right is necessary to prevent random variances between states. For instance, New York spends US$18,100 per pupil, while Idaho spends $5,800. New York is wealthier than Idaho, and its costs are of course higher, but New York still spends a larger percentage on education than Idaho. In other words, geography and wealth are important factors in school funding, but so is the effort a state is willing to make to support education.

And many states are exerting less and less effort. Recent data show that 31 states spend less on education now than before the recession – as much as 23 percent less.

States often makes things worse by dividing their funds unequally among school districts. In Pennsylvania, the poorest districts have 33 percent lessper pupil than wealthy districts. Half of the states follow a similar, although less extreme, pattern.

Studies indicate these inequities deprive students of the basic resources they need, particularly quality teachers. Reviewing decades of data, a 2014 study found that a 20 percent increase in school funding, when maintained, results in low-income students completing nearly a year of additional education. This additional education wipes out the graduation gap between low- and middle-income students. A Kansas legislative study showed that “a 1 percent increase in student performance was associated with a .83 percent increase in spending.”

These findings are just detailed examples of the scholarly consensus: Money matters for educational outcomes.

The new lawsuits

While normally the refuge for civil rights claims, federal courts have refused to address these educational inequalities.  In 1973, the Supreme Court explicitly rejected education as a fundamental right. Later cases asked the court to recognize some narrower right in education, but the court again refused.

After a long hiatus, new lawsuits are now offering new theories in federal court. In Michigan, plaintiffs argue that if schools do not ensure students’ literacy, students will be consigned to a permanent underclass. In Connecticut, plaintiffs emphasize that a right to a “minimally adequate education” is strongly suggested in the Supreme Court’s past decisions. In Mississippi, plaintiffs argue that Congress required Mississippi to guarantee education as a condition of its readmission to the Union after the Civil War.

While none of the lawsuits explicitly state it, all three hinge on the notion that education is a basic right of citizenship in a democratic society. Convincing a court, however, requires more than general appeals to the value of education in a democratic society. It requires hard evidence. Key parts of that evidence can be found in the history of the 14th Amendment itself.

The original intent to ensure education

Immediately after the Civil War, Congress needed to transform the slave-holding South into a working democracy and ensure that both freedmen and poor whites could fully participate in it. High illiteracy rates posed a serious barrier. This led Congress to demand that all states guarantee a right to education.

In 1868, two of our nation’s most significant events were occurring: the readmission of southern states to the Union and the ratification of the 14th Amendment. While numerous scholars have examined this history, few, if any, have closely examined the role of public education. The most startling thing is how much persuasive evidence is in plain view. Scholars just haven’t asked the right questions: Did Congress demand that southern states provide public education, and, if so, did that have any effect on the rights guaranteed by the 14th Amendment? The answers are yes.

As I describe in the Constitutional Compromise to Guarantee Education, Congress placed two major conditions on southern states’ readmission to the Union: Southern states had to adopt the 14th Amendment and rewrite their state constitutions to conform to a republican form of government. In rewriting their constitutions, Congress expected states to guarantee education. Anything short was unacceptable.

Southern states got the message. By 1868, nine of 10 southern states seeking admission had guaranteed education in their constitutions. Those that were slow or reluctant were the last to be readmitted.

The last three states – Virginia, Mississippi and Texas – saw Congress explicitly condition their readmission on providing education.

The intersection of southern readmissions, rewriting state constitutions and the ratification of the 14th Amendment helps to define the meaning of the 14th Amendment itself. By the time the 14th Amendment was ratified in 1868, state constitutional law and congressional demands had cemented education as a central pillar of citizenship. In other words, for those who passed the 14th Amendment, the explicit right of citizenship in the 14th Amendment included an implicit right to education.

The reasoning of both Congress and the state conventions was clear: “Education is the surest guarantee of the … preservation of the great principles of republican liberty.”

The rest is history. Our country went from one in which fewer than half of states guaranteed education prior to the war to one in which all 50 state constitutions guarantee education today.

The new cases before the federal courts offer an opportunity to finish the work first started during Reconstruction – to ensure that all citizens receive an education that equips them to participate in democracy. The nation has made important progress toward that goal, but I would argue so much more work remains. The time is now for federal courts to finally confirm that the United States Constitution does, in fact, guarantee students the right to quality education.

The 7th Continent Kickstarter

I still haven’t really had the time to playtest The 7th Continent, a boardgame I got via Kickstarter. But now the second round of Kickstarter started. If you are interested, you can get the game here. The game isn’t available elsewhere. That is why I pledged $49 to get the expansion set.

P.S. The Kickstarter is already 2500% funded, so with them having made good on their first Kickstarter promises, this is a relatively sure bet. Might of course be late, but that is pretty normal on Kickstarter.

Planning a new D&D campaign

As I mentioned before, I am somewhat disappointed by official Dungeons & Dragons adventures these days. The one that comes in a box, the Starter Kit, is great and easy to run. The others, which come as hardcover books, are more problematic. It took me a while to realize that some of those campaign books aren’t in fact adventure modules like the ones from previous editions. Rather they are campaign settings with the odd encounter or dungeon thrown in. They are background and starting point for a campaign, but with much of the actual campaign and story line incomplete.

The intention is probably to get to a more sandbox style of role-playing. However I find that this causes a problem for preparation: I find that D&D sessions where the DM is well prepared run a lot smoother. If the sandbox style is too open and the DM *can’t* prepare and has to improvise everything, the game session becomes a lot more laborious. That is especially true if the DM uses visual aids, like I do: Battle maps, 3D printed miniatures, handouts, etc. all require preparation.

The advantages of full sandbox mode of infinite freedom are also somewhat illusionary. Most of the time players act on little or limited information. The freedom to go north or south isn’t worth much if the decision isn’t meaningful because you have no idea what happens if you go north or if you go south. But of course full sandbox or strictly linear gameplay aren’t the only two options, there are compromises in between the two. And that is what I will be going for in my campaigns in 2018. Basically I will present the players options, but with sufficient information to make each option meaningful. Instead of telling them that they can go in any compass direction they want (which isn’t how humans tend to travel anyway), I present them with a fork in the road with road signs to two different places, and some knowledge (e.g. with history checks or from passing travelers) what is going on in those two places. A meaningful choice between 2 locations is better than full freedom to go anywhere, just to face the same random encounter tables because otherwise there isn’t anything there.

For my Princes of the Apocalypse campaign this is already working well enough. I gave the players some information about the evil elemental cults, including an idea of relative strength. They usually know about at least 2 different locations where they could go next, and what cult is likely to be there. Which means I can prepare both places and be prepared for either choice. But I did use magical portals to block off the deeper dungeons, which not only I would be not prepared for, but which would also be not much fun, being much higher in level than the players.

Next year I’ll try to start a new campaign with new players at my local role-playing club, using the Out of the Abyss campaign setting. So over the holidays I have time to read the book front to cover, and fill out the blanks with the missing story line and alternative options. As I recently wrote, I learned from a good DM / group on YouTube that I shouldn’t worry too much about the story line, but rather make sure that there is enough opportunity for players to contribute to the story with their own ideas. Which means presenting situations in a way that make it clear that players can do other things than just roll initiative and attack. I still believe good tactical combat encounters are important and they are usually fun to play for the players, but they aren’t all there is to Dungeons & Dragons.

Amazon has shipped three times more smart speakers as Google

Right now, two heavyweights are vying for control of your living room. Google and Amazon are battling it out with their smart speakers, and so far, consumers are the winners.

The smart speaker war is the topic of a new report from Consumer Intelligence Research Partners. The report is bringing some surprising findings, like the total number of smart speakers sold and just how dominant Amazon has been so far. As of September 2017, Google has shipped 7 million Google Home products, whereas Amazon has shipped almost three times (20 million) as many Echo devices. Keep in mind that these numbers are just through September and don’t take into account the holiday shopping season.

The question is why? Why is there such a disparity between the two companies? What is Amazon doing so well that Google isn’t?

One of the biggest reasons for Amazon’s healthy lead is that it was on the market first. When people think of smart speakers, the Echo lineup is likely what they think of first simply because it has been around longer and they have had more exposure to it. The first generation Echo hit the market in June 2015 and Google didn’t release the Home until November of 2016, giving Amazon almost a year and a half head start.

That head start gave Amazon time to come up with other products too. When it added the Echo Dot to its lineup in March of 2016, it gave customers a much cheaper way to get a smart home speaker. The results speak for themselves: the Echo Dot has been Amazon’s biggest seller. That’s despite not going on sale for more than a year after the original Echo.

The lessons Amazon learned with the Echo Dot have spread to the rest of its lineup. Products like the second-generation Echo are cheaper than ever before. You can now get an Echo for as little as $79, even though they were upwards of $180 when they launched. Temporary price reductions are also getting more extreme, like the Echo Show which you can get for $80 off its normal asking price.

Editor’s Pick

Google is doing its best to keep up. It dropped the price of the Google Home Mini, which competes with the Dot, to $29 for the holiday season. It also slashed the price of the original Google Home down to $79 for the same period. To fill out its lineup, it finally released the Google Home Max that it announced earlier this year. The Home Max is a $399 smart speaker that offers all of the functionality of the Google Home or Home Mini but with a premium audio experience. Amazon currently has no competitor for the Home Max.

Why is Amazon so intent on beating Google to the bottom? It all has to do with getting people into the Amazon ecosystem. You might be shocked to learn that the Amazon doesn’t make much off the products it sells. It currently sits at a 3% operating margin, whereas Google is at 26% and Apple 27% this fiscal year.

The Echo Dot is an opportunity for Amazon to sell even more products, and thus, make more money. Discounting it to $30 entices shoppers to throw it in their cart since its so cheap. Once the Dot is in their home, they’re more likely to subscribe to Amazon Prime for things like music streaming or to purchase more items from Amazon’s marketplace.

See also: Echo vs Dot vs Tap vs Show: Which is right for you?

While the two companies are sitting pretty with almost 100% of the market share right now, Apple is coming. The HomePod was announced earlier this year and will start shipping early in 2018.

The speaker will be priced at $350 and compete with the Home Max for the premium smart speaker crown. With Apple customers willing to pay more for the company’s products, can it challenge for control of the living room or will its lack of competition for the at the low end of the price range hurt its chances? We’ve yet to hear plans for a cheaper speaker that would compete with the Dot/Home Mini or the Echo/Home.

If you’re looking to pick up a smart speaker for yourself or a loved one this holiday season, check out the links below.

  • Amazon Echo – $79.99
  • Amazon Echo Dot – $29.99
  • Amazon Echo Show – $149.99
  • Google Home – $79
  • Google Home Mini – $29
  • Google Home Max – $399

Merry Christmas!

Merry Christmas to you and your family!

I’d write my thoughts about my Switch, but frankly I’m still too busy having fun with Mario + Rabbids Kingdom Battles. And I haven’t even started Zelda or Super Mario Odyssey yet!

Working with Microsoft SQL Server


MS SQL Server


What is SQL Server Management Studio?

SQL Server Management Studio (SSMS) is the main administration console for SQL Server.


SSMS enables you to create database objects (such as databases, tables, stored procedures, views etc), view the data within your database, configure user accounts, perform backups, replication, transfer data between databases, and more.

Fig 3 : SSMS Console Window


User Login :


When creating a new user login, the administrator can assign that login to any number of roles and schemas. This will depend on what that particular login is entitled to.


Create a New Login

Step 1 :  Using SQL Server Management Studio, expand the Security option (at the
server level, not at the database level) and right click n Logins.

Step 2 :  Click on New Login.

Step 3 :   Complete the login properties in the General tab by providing a name for the login, choosing the Authentication method (providing a password if you choose SQL Server authentication), and selecting the database to use as a default.

Step 4 :   Click the Server Roles tab if you need to apply any server-wide security privileges.


Step 5 :
Click the User Mapping tab to specify which databases this user account is allowed to    access. By default, the login will be assigned to the Public role, which provides the login with basic access.
 If the login needs more access in one or more databases, it can be assigned to  another role with greater privileges. In this case, select Task Tracker database  and  db_owner role for that database.
Step 6 :  Click OK to create the login.


NOTE :
These roles are database roles and are different to the server roles in the previous tab. Server roles are for administering the SQL Server. Database roles are more limited. They are created within each database and specify what the login can do within that particular database.




Fig : Server Roles

Server Roles :


When we created a SQL Server login, we had the option of assigning the login one or more server roles. Server roles (not to be confused with database roles) are available to manage permissions on a server.


View the server roles in SSMS, from the Object Explorer, expand the Security folder







Database :

A database is a collection of information that is organized so that it can be easily accessed, managed and updated.
Creating a database can be done by running a SQL script or by “point and click”. 


System Databases

When you install SQL Server, the following four databases are created.
1.master
This database stores system level information such as user accounts, configuration settings, and info on all other databases.
2.model
This database is used as a template for all other databases that are created.
3.msdb
The msdb database is used by the SQL Server Agent for configuring alerts and scheduled jobs etc
4.tempdb


This one holds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.  


Create Database :

Fig 4 : Create DB 
The following steps demonstrate how to create a database in SQL Server 2014 using SQL Server Management Studio.

Step 1 : From the Object Explorer, right click on the Databases folder/icon and select New Database

Step 2 : 
Name your database and click OK:


Database with the Given Name will be created.

NOTE :
The new database is based on the Model database (System Database).
It already contains system functions, system views, system stored procedures, and (hidden) system tables. These are system objects which provide information about the database.


Create Table :

Fig 5 : Create Table
Steps to create a table in a database using SQL Server Management Studio (SSMS).

Step 1 :   Ensuring you have the correct database expanded (in our case, the TaskTracker database), right click on the Tables icon and select Table

Step 2 :   A new table will open in Design view. Fill in Column_name, Datatype & if(Null) checkbox for making column .
  ( for more options such as PRIMARY KEY, is Identity, etc. right click on left pane of  required tuple to add property )

Step 3 :   Save the table by selecting File > Save able_1 or by right-clicking on the Table’s  tab and selecting. 
Save Table_1  from the contextual menu:  





Insert Data :
There are many ways of getting data into your database. 



1.Manually: Type data directly into your table rows.


2.Copy/Paste: Similar to the previous option, but this one is where you copy data from another source, then paste it into a table in your database.

3.Import: You can use the Import and Export Wizard to import data from another source.

4.SQL Scripts: You can run a SQL script that contains all data to insert.

5.Application/Website: Users update the database via an application or website.





1. Manually


We can use the Edit Top 200 Rows option to manually type data directly into the table rows. Manually entering data is OK if you only have a little bit of data to enter.


Steps :
1. In the Object Explorer, right click on the table you wish to open, and select Edit Top 200 Rows:
2.You can now start entering the data directly into your table.


2. Copy/Paste


You could use a similar method to the above by copying from another datasource and pasting into your database table. This is OK for a small number of records but not for a lot of records.


Steps :

1. Select all required records from the datasource

2. In the destination database (i.e. the one you want to populate with data), right-click on the destination table and select Edit Top 200 Rows. 

3. Select an empty row by right-clicking in the left-most column (it’s more of a button to the left of your left-most column that allows you to select the whole row) and select Paste from the contextual menu:


3 (a). Import 
You can import data from another datasource. The end result is similar to the copy/paste method (i.e. data is copied across to the destination database), but importing the data is more flexible and could be more suitable on many occasions.

Steps :
To import data, right-click on the database and select Tasks > Import Data… and follow the Wizard from there.

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. These include:
•SQL Server
•Flat files
•Microsoft Office Access
•Microsoft Office Excel


Start the SQL Server Import and Export Wizard to import data from an Excel worksheet to a SQL Server database. Click Next to bypass the welcome screen. On the Choose a Data Source page (Figure 8), configure the following:

Fig 6: Import Wizard


Step 1 : (Data Source)
Choose Microsoft Excel from the drop-down menu.

Step 2 : (Excel File Path )
Specify the path of the Excel file from which you are importing data.

Step 3: (Excel Version)
Choose the Excel version where you created the Excel.

Click Next to go to the Choose a Destination page (Figure 9), and configure the following:



Step 4 : (Data Source)
Choose SQL Server Native Client 11.0 from the drop-down menu.

Step 5 : (Server Name )
Fig 7 : Import Wizard Client

Type the name of the destination database’s SQL Server instance.

Step 6 : (Authentication)
Choose the appropriate authentication mode for the data destination connection.

Step 7 : (Database )
Choose which database to copy the data into.








Fig 8 : Finish Import

Step 8 :
Click the Next button to go to the Save and Run Package page. Here, select the Run immediately option and click the Next button. 

This takes you to the Complete the Wizard page where you can view the choices you made.

Click Finish to run the package.




3 (b). Exporting data  (from a SQL Server DB to a Microsoft Excel worksheet) :

To export data, start the SQL Server Import and Export Wizard. Then, click the Next button to bypass the Welcome Screen. On the Choose a Data Source page configure the following:

Step 1 : (Data Source )
Fig 9 : Export Wizard

 Choose SQL Server Native Client 11. from the drop-downmenu.

Step 2 : ( Server Name)
Type the name of the SQL Server instance that contains the source data.

Step 3 : (Authentication)
Choose authentication mode for the data source connection.

Step 4 : (Database)
 Choose the database that contains the source data.
Click Next to go to the Choose a Destination page (Figure 2). On this page, configure the following:





Fig 10 : Export Wizard Target

Step 5 : (Destination)
Choose Microsoft Excel from the drop-down menu.

Step 6: (Excel File Path)
Type in the Microsoft Excel worksheet operating system path.

Step 7 : (Excel Version)
Select the version of the Microsoft Excel worksheet.





Step 8:
Fig 11 : Finish Export 

Click the Next button to go to Review Data Type Mapping page. 

This is where you’ll see how different data types are mapped between the source and the destination and how any conversions issues will be handled.

Follow the wizard ahead.




4. SQL Scripts :
In many cases, you will find it more efficient to run a SQL script that contains the data you need to insert. You can use the SQL INSERT statement to insert just the data you specify in the statement.

Eg :
USE [Database_Name] 
GO
INSERT INTO Table_Name (column_name1,column_name2,column_name3) VALUES (‘val1’, ‘val2’, ‘val3’)
5. Application / Website


Server databases are the backend data storage for a front-end application. Users of the application are responsible for adding data to the database (as well as editing it). 
The Difference between these scripts and above scripts is, these scripts are dynamic. i.e Parameters are passed dynamically to the database.

T-SQL Scripts :

1. Create Database :
Creates a user defined database which can hold n number of user defined tables.

Syntax :
Create database

Query :
CREATE DATABASE SCTPL


2. Create Table :
Creates a user defined table which can hold n number of column with their respective constraints defined by user.

Syntax :
USE [DatabaseName]
GO
CREATE TABLE table_name
(
column_name1 Datatype,
column_name2 Datatype,
column_name3 Datatype
)

Query :
USE [SCTPL]
GO
CREATE TABLE Testdb
(
PrimaryID  int PRIMARY KEY IDENTITY,
Year  varchar(255)
)

3. Drop Table :
The SQL Server DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.

Syntax :
USE [Database_Name]
GO
DROP TABLE table_name

Query :
USE [SCTPL]
GO
DROP TABLETEST


4. Insert Into :

The SQL Server INSERT INTO statement is used to add new rows of data to a table in the database.

Syntax :
USE [Database_Name]
GO
INSERT INTO Table_Name (column_name1,column_name2,column_name3) VALUES(‘val1’, ‘val2’, ‘val3’)

Query :
USE [Database_Name]
GO
INSERT INTO Table_Name (Year, Country, Location)
VALUES (‘1996′,’Nigeria’,’Akwa Ibom’)
4. Select :
SQL Server SELECT statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-sets.
Syntax :
/*For selecting all entries*/
USE [Database_Name]
GO
SELECT * FROM TEST
/*For selecting Specific entries*/
USE [Database_Name]
GO

SELECT column_name1, column_name2 FROM Table_name


Query :
/*For selecting all entries*/
USE [Database_Name]
GO
SELECT * FROM TEST
/*For selecting Specific entries*/
USE [SUVEN]
GO
SELECT Year, Country FROM TEST
5. Update :
The SQL Server UPDATE Query is used to modify the existing records in a table.
You can use WHERE clause with UPDATE query to update selected rows otherwise all the rows would be affected.

Syntax :
/* Type 1 */
Use [Database_Name]
GO
UPDATE table_name SET column1 = value1, column2 = value2…., columnN = value WHERE [condition]
/* Type 2 */
USE [Database_Name]
GO
UPDATE TABLE_NAME
SET COLUMN_NAME = ‘val1′, Column_name =val


Query :
/* Type 1 */
USE [SUVEN]
GO
UPDATE TEST
SET Location = ‘RenamedColumn Akwa-Ibom
WHERE Location = ‘Akwa Ibom
/* Type 2 */
UPDATE CUSTOMERS SET ADDRESS = Pune‘, SALARY = 1000.00



6. Delete :
The SQL Server DELETE Query is used to delete the existing records from a table.
You have to use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.

Syntax :
/*For selecting specific entries*/
USE [Database_Name]
GO
DELETE FROM table_name WHERE [condition]
/*For selecting all entries*/
DELETE FROM column_name

Query :
/*For selecting specific entries*/
USE [SUVEN]
GO
DELETE FROM TEST
WHERE SALARY = 1000
/*For selecting all entries*/
DELETE FROM Location



Clauses

1. WHERE Clause:

The MS SQL Server WHERE clause is used to specify a condition while fetching the
data from single table or joining with multiple tables.

If the given condition is satisfied, only then it returns a specific value from the table. You will have to use WHERE clause to filter the records and fetch only necessary records.
The WHERE clause is not only used in SELECT statement, but it is also used in

UPDATE, DELETE statement, etc.


Syntax :
SELECT column1, column2, columnN FROM table_name WHERE [condition]

Query:
SELECT name FROM Stud WHERE id=1


2. LIKE  Clause:

The MS SQL Server LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator 
  1. The percent sign (%)
  2. The underscore (_)
The percent sign represents zero, one, or multiple characters. 
The underscore represents a single number or character. The symbols can be used in combinations.

Syntax:


/* Type 1 */
SELECT *column-list FROM table_name WHERE column LIKE ‘%XXXX%’
/* Type 2 */
SELECT *column-list FROM table_name WHERE column LIKE ‘_XXXX_’

Query:

/* Type 1 */
SELECT name from Stud
WHERE sid LIKE ‘200%’
(Finds any values that start with 200)
/* Type 2 */
SELECT name from Stud
WHERE SALARYLIKE ‘_00_’
Finds any values that have 00 in the second and third positions & is of four characters. 


3. ORDER BY


The MS SQL Server ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sort query results in ascending order by default.


Syntax :

Use DatabaseName
Go
SELECT columnlist FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]

Query :

/*TYPE 1*/
Use SUVEN
Go
SELECT * FROM Stud ORDER BY NAME

/*TYPE 2*/

SELECT * FROM Stud ORDER BY NAME DES

4. GROUP BY Clause :

The SQL Server GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax :

UseSUVEN
Go

SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2

Query: 

UseSUVEN
Go
SELECT NAME, SUM(SALARY) as [sum of salary] FROM Stud GROUP BY NAME

6. DISTINCT

The MS SQL Server DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.


There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.


Syntax :

Use Suven
Go

SELECT DISTINCTcolumn1, column2,…..columnN FROM table_nameWHERE [condition]

Query :
Use SUVEN
Go
SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY 


Joins :

The MS SQL Server Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

MS SQL Server Join Types −

There are different types of joins available in MS SQL Server −

INNER JOIN         −  Returns rows when there is a match in both tables.

LEFT JOIN            −  Returns all rows from the left table, even if there are no matches in the  
                                  right table.

RIGHT JOIN         −  Returns all rows from the right table, even if there are no matches in the 
                                  left  table.

FULL JOIN            −  Returns rows when there is a match in one of the tables.

SELF JOIN             − This is used to join a table to itself as if the table were two tables, 
                                 temporarily renaming at least one table in the MS SQL Server statement.

CARTESIAN JOIN − Returns the Cartesian product of the sets of records from the two or 
                                  more joined tables.

NOTE :

The join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.


Query Designer :

The query designer is a graphical user interface that assists in building queries for your SQL Server database. This can be particularly useful when building complex queries that involves many tables, views etc.

The Query Designer can also be beneficial for those who are learning how to write SQL. By using the Query Designer to generate the SQL, you can study the SQL and learn the syntax as you go.

Step 1 :  Open a new query by clicking New Query on the toolbar


Step 2 :  Open the Query Designer by selecting Query > Design Query in Editor. from the top menu:

Step 3 : Select the tables you want to run the query against (in this case, we will add both tables), click Add, and close the box by clicking Close:


Step 4 :  
Select the column/s you want to display in your query by checking the checkbox next to the column name. In the middle pane, you can deselect an Output checkbox to hide that field from being displayed when the query is run (but the field will still be involved in the query).

 Add a value under Filter to narrow the results down to only those you’re interested in (in this example, it is creating a WHERE clause to select only those records with a StatusId of “1” – which is “To Do”):

Step 5 :
Click OK once you’re happy with your query. Query will be automatically  Generated by query designer. Execute it using f5 key.


Views :

In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table. 

Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table.
Syntax :

CREATE VIEW ViewName AS
SELECT …

Query :

CREATE VIEW ToDoList AS
SELECT  Tasks.TaskName, Tasks.Description
FROM  Status INNER JOIN
Tasks ON
Status.StatusId = Tasks.StatusId
WHERE  (Status.StatusId = 1)
NOTE : Tasks & Status are two different Tables in a single database.


To execute a View  : 

Syntax : select * from View_name


NOTE :
The view will return upto date data. If the data in the table changes, the results of the view will change too. So if you were to add a new task with a status of “To Do”, next time you run the view, it will include the new record in the result set.


Stored Procedures :

A stored procedure will typically contain some business logic. For example, a stored procedure can accept parameters that are passed to it and test against those parameters using IF statements. Eg, if the parameter is one value, do this, if it’s another value, do that.

Benefits of Stored Procedures :

1. Modular programming
You can write a stored procedure once, then call it again and again, from different parts of an application (and even from multiple applications).

2. Performance
Stored procedures provide faster code execution and reduce network traffic.

3. Security
Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn’t normally have access to these tasks, but this functionality is made available in a tightly controlled way.

Create a Stored Procedure :

To create a stored procedure, you need to use the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure. If your stored procedure is going to accept parameters, they need to be included after the name.

Syntax :

CREATE PROCEDURE myStoredProcedure AS
OR
CREATE PROCEDURE myStoredProcedure @{Parameter Name} {data type} AS

Query :

CREATE PROCEDURE
LatestTasks @Count int
AS
SET ROWCOUNT @Count
SELECT TaskName AS LatestTasks, DateCreated
FROM Tasks

ORDER BY DateCreated DESC


Execute a Stored Procedure

1. T-Sql Script :

Now that you’ve created your stored procedure, any time you want to execute it, you need to call it using either EXECUTE or EXEC. If the stored procedure requires parameters you provide those after the procedure name. 


Syntax :
EXECUTE LatestTasks
EXEC LatestTasks
OR
EXEC LatestTasks @Count = 5





2. Using The GUI

Step 1 : Using the Object Explorer, navigate to the stored procedure.

Step 2 : Right click on the stored procedure and select Execute Stored Procedure.

Step 3 : A dialog will appear. Enter your parameter values.

Step 4 : Click OK. SQL Server will generate the SQL code and execute the stored 




System Stored Procedures :

SQL Server includes a large number of system stored procedures to assist in database administration tasks. Many of the tasks you can perform via the GUI can be done via a system stored procedure. For example, some of the things you can do with system stored procedures include:
  • Configure security accounts
  • Set up linked servers
  • Create a database maintenance plan
  • Create full text search catalogs
  • Add remote login
  • Configure replication
  • Set up scheduled jobs
  • and much more…
NOTE :
System Stored Procedure has a prefix sp_ as naming convention.
It is a good idea to develop a consistent naming convention for your stored procedures, like usp_,  select_ , etc.

About Trees, different Traversals and BST

A tree is a data structure made up of nodes or vertices and edges without having any cycle. The tree with no nodes is called the null or empty tree. A tree that is not empty consists of a root node and potentially many levels of additional nodes that form a hierarchy.

Tree

Tree Terminology

Lets see some tree terminologies:-
Root: The top node in a tree.
Child: A node directly connected to another node when moving away from the Root.
Parent: The converse notion of a child.
Siblings: A group of nodes with the same parent.
Descendant: A node reachable by repeated proceeding from parent to child.
Ancestor: A node reachable by repeated proceeding from child to parent.
Leaf: A node with no children.
Internal node: A node with at least one child.
Degree: The number of sub trees of a node.
Edge: The connection between one node and another.
Path: A sequence of nodes and edges connecting a node with a descendant.
Level: The level of a node is defined by 1 + (the number of connections between the node and the root).
Height of node: The height of a node is the number of edges on the longest path between that node and a leaf.
Height of tree: The height of a tree is the height of its root node.
Depth: The depth of a node is the number of edges from the tree’s root node to the node.
Forest: A forest is a set of n ≥ 0 disjoint trees.

Tree Node

Tree Node has a data part and references to its left and right child nodes.

struct node {
int data;
struct node *leftChild;
struct node *rightChild;
};
In a tree, all nodes share common construct.

Tree Traversals

Traversal is a process to visit all the nodes of a tree and may print their values too. Because, all nodes are connected via edges (links) we always start from the root (head) node. That is, we cannot randomly access a node in a tree. Unlike linear data structures (Array, Linked List, Queues, Stacks, etc) which have only one logical way to traverse them, trees can be traversed in different ways. Following are the generally used ways for traversing trees.

  • In-order Traversal
  • Pre-order Traversal
  • Post-order Traversal

In-Order Traversal

In this traversal method we first visit the left sub-tree, then the root and later the right sub-tree. If a binary tree is traversed in-order, the output will produce sorted key values in an ascending order.
For e.g.
Output of the In-Order traversal for above tree is
4 -> 2 -> 5 -> 1 -> 3
Algorithm Inorder(tree)
1. Recursively traverse the left subtree, i.e., call Inorder(left-subtree)
2. Visit the root
3. Recursively traverse the right subtree, i.e., call Inorder(right-subtree)

Pre-Order Traversal

In this traversal method we first visit the root node, then the left sub tree and finally the right sub-tree.
For e.g.
Output of the In-Order traversal for above tree is 
1-> 2 -> 4 -> 5 -> 3
Algorithm Preorder(tree)
1. Visit the root.
2. Recursively traverse the left subtree, i.e., call Preorder(left-subtree)
3. Recursively traverse the right subtree, i.e., call Preorder(right-subtree)

Post-Order Traversal

In this traversal method we first visit the left sub tree, then the right sub-tree and finally the root node.
For e.g.
Output of the In-Order traversal for above tree is 
4-> 5 -> 2 -> 3 -> 1
Algorithm Postorder(tree)
1. Recursively traverse the left subtree, i.e., call Postorder(left-subtree)
2. Recursively traverse the right subtree, i.e., call Postorder(right-subtree)
3. Visit the root

Binary Search Tree(BST)

In binary tree, every node can have maximum two children but there is no order of nodes based on their values. Binary search tree is a type of binary tree in which all the nodes in left subtree of any node contains smaller values and all the nodes in right sub-tree of that contains larger values as shown in following figure

Operations on a Binary Search Tree

Following Oprations performed on BST
1. Search
2. Insertion
3. Deletion

Search Operation

Whenever an element is to be searched, start searching from the root node. Then if the data is less than the key value, search for the element in the left subtree. Otherwise, search for the element in the right subtree. Follow the same algorithm for each node.
Algorithm
node* search(int data){
struct node *current = root;
printf("Visiting elements: ");
while(current->data != data){
if(current != NULL) {
printf("%d ",current->data);
//go to left tree
if(current->data > data){
current = current->leftChild;
}//else go to right tree
else {
current = current->rightChild;
}
//not found
if(current == NULL){
return NULL;
}
}
}
return current;
}

Insert Operation

Whenever an element is to be inserted, first locate its proper location. Start searching from the root node, then if the data is less than the key value, search for the empty location in the left subtree and insert the data. Otherwise, search for the empty location in the right subtree and insert the data.
Algorithm
Node * Insert(Node * root, int data)
{
if(root == nullptr)
{
Node * NN = new Node();
root = NN;
root->data = data;
root->left = root ->right = NULL;
}
else
{
if(data < root->data)
{
root->left = Insert(root->left, data);
}
else
{
root->right = Insert(root->right, data);
}
}
return root;
}

Deletion Operation in BST

In a binary search tree, the deletion operation is performed with O(log n) time complexity. Deleting a node from Binary search tree has following three cases:-
Case 1: Deleting a Leaf node (A node with no children)
Step 1: Find the node to be deleted using search operation
Step 2: Delete the node using free function (If it is a leaf) and terminate the function.
Case 2: Deleting a node with one child
Step 1: Find the node to be deleted using search operation
Step 2: If it has only one child, then create a link between its parent and child nodes.
Step 3: Delete the node using free function and terminate the function.
Case 3: Deleting a node with two children
Step 1: Find the node to be deleted using search operation
Step 2: If it has two children, then find the largest node in its left subtree (OR) the smallest node in its right subtree.
Step 3: Swap both deleting node and node which found in above step.
Step 4: Then, check whether deleting node came to case 1 or case 2 else goto steps 2
Step 5: If it comes to case 1, then delete using case 1 logic.
Step 6: If it comes to case 2, then delete using case 2 logic.
Step 7: Repeat the same process until node is deleted from the tree.

Do you want to learn Java Programming?

Switcheroo

I had a typical first-world problem in an affluent society: My wife asked me what I wanted for Christmas and I didn’t have a good answer. We were in the middle of a large electronics store and had just bought an expensive digital camera as my present for her, so she felt she needed to reciprocate. After some thinking and looking around, we settled on a Nintendo Switch for me, in a bundle with Super Mario Odyssey. And of course also bought Zelda, and a few other games. Normally I wouldn’t have bought a console for a few good games, but we are spending Christmas with family, and so the console wouldn’t just be for me, but also to keep the kids busy.

Being game language literate I immediately spotted a potential problem: The bundle doesn’t actually contain the game Super Mario Odyssey. It contains a code to download it. Whatever purists might think of it, you don’t want to unpack game stuff on Christmas and find it isn’t ready to play immediately with kids around. So I unpacked my Christmas present already, to set it up (not to play), charge the batteries, and then pack it again to be ready for the event. That turned out to be a good plan, as even on my ultra-fast VDSL the game needed 37 minutes to download, and over Christmas I will have much lower internet speed.

As this was a spontaneous buy, I hadn’t read up on the Switch. I’ve heard that it was usable both as a console connected to a TV and handheld, but hadn’t pondered that feature any further. It was on unpacking that the truth dawned on me: The Switch isn’t actually a game console, it is a tablet computer. A tablet computer with weird controllers, yes, but at its core a tablet computer. It turned out that for example setting it up and entering text like WiFi passwords, using the touch screen keyboard is far better than trying to type with the controllers.

On the other hand for a tablet computer the Switch has a lousy battery life (not tried myself, just read about it), and rather small screen. I imagine I will end up using it in the stand that turns it more or less into a console far more than handheld. It is a bit annoying that I’ll have to attach the controllers to the tablet after every playing session to recharge them, even if the controllers are reported to have a longer battery life than the handheld tablet.

I’ll report sometimes after Christmas on how playing games on the Switch turns out. I’ll start with a library of 4 games: Super Mario Odyssey (which I got more for the kids than for me, but the reviews are excellent), Mario + Rabbids, Zelda, and Disgaea 5. If you have any advice on good role-playing and strategy games for the Switch, I’m listening.

Idle Champions of the Forgotten Realms on iOS

When an alpha test comes with specific instructions not to share screen shots and videos, I read the absence of specific instructions on other forms of communication as them being allowed. So I think I can reveal that since yesterday I am in an alpha test of Idle Champions of the Forgotten Realms for mobile devices, which in my case specifically is on an iPad.

I got into that alpha via a link that appeared while I was playing the game on my PC. That is a pretty safe way to select your alpha testers, as existing players will be able to concentrate on bugs or differences to the PC version. Up to now I only found one minor issue, and when I reported it I got a non-automated mail back within minutes. Nice! I’m not a huge fan of the “stress test” type of beta tests where the input of the testers isn’t even wanted.

The good news is that the game on the iPad runs as smoothly as on the PC, and appears to be a straight port of the current PC version. The bad news is that the game on the iPad thus has exactly the same rules as the PC version. And those rules push people to let the game run 24/7, because you farm much more gold online than offline. That helped Codename Entertainment get their game in the “top 100 most played Steam games” category, which is somewhat misleading because you can’t compare an idle game left running with a game that only does something when you are at the controls. And while letting your PC run 24/7 is still feasible (I just turn the screen off), it is a lot more difficult to do that on a tablet. You need to connect the tablet to a charger, for one thing. And while on the PC the game runs perfectly in the background while you do something else in the foreground, the mobile version only works when it is the foreground, thus prohibiting other use for your tablet.

I would be okay if the “auto progress ON” function only worked when online. But I think that while not progressing, the game should gather gold online and offline at a comparable rate. Apparently there is a change coming in some future patch, but I do think the game needs that change to be viable on a mobile platform.

Elemental Evil: Sessions 5 and 6

It appears I forgot to chronicle the previous session of my D&D home campaign. The last report was from early July, after which we had a summer break, and then resumed mid-August, and then continued yesterday. Both of these sessions were action-centric, with the group clearing out first the abandoned village of Thundertree and then the goblin stronghold of Cragmaw Castle from monsters. A “door-monster-treasure” type of gameplay can be a lot of fun, but the details aren’t always all that interesting in a journal of events. So I will summarize and concentrate on the highlights in this post.

Thundertree is an abandoned village a day’s travel from Neverwinter. The eruption of Mount Hotenow, which caused quite a catastrophe for Neverwinter half a century ago, destroyed the village of Thundertree. Erdan, the druid of the group who is prone to visions and nightmares, dreamed that the eruption of Hotenow was caused by a group of chanting fire cultists, but probably didn’t go as planned, as the cultists were killed in the event. What remained in Thundertree was mostly abandoned houses, with a population of ash zombies and twig blights. The group had gone to Thundertree to meet the druid Reidoth, who was supposed to know the location of Cragmaw Castle. Their “pet goblin” Droop also claimed to be able to find the way from Thundertree to Cragmaw Castle. They met Reidoth, who was able to provide a safe haven in the village, as well as the directions needed.

After clearing out most of the village from monsters, the group came across another group which likewise was engaged in fighting twig blights. That group was wearing blue armor and white robes, beset with feathers. They explained that they were from a club of aerial enthusiasts, and were in Thundertree to try to tame a griffon nesting here, or get eggs from his nest to raise as aerial mounts. The heroes agreed to accompany them to the griffon’s lair in the highest tower of Thundertree. But once there the air cultists tried to becalm the griffon by offering the adventurers up as sacrifice, so the group ended up killing both the cultists and the griffon. They were able to make the link between a symbol the cultists carried and the same symbol they had seen on a letter to Glasstaff in Phandalin.

On the way to Cragmaw Castle the group tried to question Droop for information about the castle. That was somewhat complicated by the fact that Droop could only count to 3, and used “3” as an answer to any question about numbers in which the answer exceeded 2. Not trusting the goblin’s offer to negotiate safe entry into the castle, they knocked him out and attached him to a tree, guarded by the paladin (the player was absent that session). Instead they built a camouflage out of branches and approached the less guarded south side of the castle at night. From there they could see into the banquet hall, but the goblins there didn’t look out the arrow slits. So they managed after a few attempts to unlock the side door. But they didn’t like the idea of advancing with the goblins in the hall behind them, so they decided to attack there.

From there they moved clockwise room by room. That enabled them to eliminate most guards in small groups. However it did move them more towards the entrance of the castle, instead towards the throne room. The toughest fight was against a group of hobgoblins. Popée the sorceress used a web spell on them, but between succeeded saving throws initially and later the web wasn’t all that effective. Then they tried to burn the web, but in 5E that deals only 2d4 damage, and the player rolled double 1s, so the spell wasn’t really a big success. The hobgoblins however had an ability with which they dealt an extra 2d6 damage if next to an ally. And two of them rolled critical hits, which doubles the number of dice on all damage, knocking the druid out of his bear form. After another fight in the central chapel of the castle the group had enough and decided to go back into the woods to take a long rest.

Returning to the castle they found that the bugbear King Grol had obviously noticed that the group had raided his castle and killed most of the goblinoids in there. So King Grol has gathered all the remaining defenders in the chapel, including a priest from the air cult. That ended up being a tough fight, with Theren being knocked down to zero health, but then rescued. The air cultist priest was a real menace, with a dust devil spell that prevented the archers and casters from sniping from the back. But Popée used a scroll of lightning bolt on King Grol and his pet wolf, killing the wolf and seriously damaging the bugbear. Soon after all the bugbears were dead. The priest tried to transform into gaseous form and flee, but didn’t make it out of the arrow slit in one round and concentrated fire killed him before his next round. At this point it had gotten rather late, and we ended the session.