Showing posts with the label SQL

Sql Cheatsheet

After my first job I realized the importance of learning a bit about it all. None should be in the dark when it comes to their website. I might not be a master of relational databases but give me a mongodb or reddis and even a firebase database any day. Still some projects are dependent on mysql and etc so I have enough info to help me move forward. Nothing I had more than a dba or team who locks me out. Throw exception from SQL Server function to stored procedure - Stack OverflowHOW TO: Return Errors and Warnings from a SQL Server Stored Procedure in ADO.NETRAISERROR (Transact-SQL)SQL server management studio local database connection error in windows 7Visual Studio Database Guide - HomeHow to: Use Source Control with SQL Server Management Studiomysql - Sending multiple data parameters with jQuery AJAXsql server - Inserting multiple rows in a single SQL query?LuaSQL: Database connectivity for the Lua programming language
More coming soon..

Coursera: Introduction to Databases Part II

Continuing from my previous article this post is a collection of my notes, and my study of SQL databasing.

Relational Algebra
Tuple - Wikipedia
sql - What does the term "Tuple" Mean in Relational Databases? - Stack Overflow
sql - Clear explanation of the "theta join" in relational algebra? - Stack Overflow

Two selection operators in a row can always be replaced by a single selection operator whose condition is the "and" of the two selection conditions. If there are two projection operators in a row, the attribute list of the second (outer) projection must be a subset of the attribute list of the first (inner) projection. Thus, the first projection can be removed without changing the result of the expression.
JoinInner Join on ConditionNatural JoinInner Join Using (attrs)Aggregation
Aggregate Functions (Transact-SQL)
Aggregate function - Wikipedia, the free encyclopedia
SQL Functions

Null Values in relational database are undefined or unknown, and used to de…

Using Entity Framework and a Storage Procedure with Parameters

So I was stuck sometime ago on a problem I had where I was using my sql store procedure with the Entity Framework. Eventually found what I needed and the purpose of this article is just to write out a small example. I've come up with multiple ways to achieve the same thing now but this is the most basic form of it.

public IList<KeyValuePair> GetIds(string id) { using (var db = DBFactory.GetDB()) { return db.Database.SqlQuery<KeyValuePair>( "EXEC db.MyDatabase @id", new SqlParameter("id", id)); } }
Resources How to pass parameters to DbSet.SqlQuery Method in Entity Framework How to use DbContext.Database.SqlQuery(sql, params) with stored procedure? EF Code First CTP5 The data reader has more than one field. Multiple fields are not valid for EDM primitive types
KeyValuePair(TKey, TValue) Structure (System.Collections.Generic)

Coursera: Introduction to Databases Part 1

Introduction to Databases is taught by Jennifer Widom and hosted by Coursera as a self paced class.  As someone who mainly does front-end developing I feel this class should be very helpful for me to add some new set of skill to my array. I also think this class will help betterprepare me later down the road  for my plans to take the SQL certifications. This is article is part of a series of other articles that will be on the class , but this article particularly is about XML, DTD and Json.
    When I first started this article it was a single entity but as I got through more of the class the article was a little more too much for me to maintain as well as keep track of since I went out of order with what the had class. I think the topic that most interested me for this class was learning about triggers. However so far this article is about nothing new but I felt it was important to document. 
XML - eXtensible Markup Language
An alternative to structuring data, similar to html. also…