Category Archives: Database

Stored Procedures

 

 

 

 

 

SQL Identity Key ,T-SQL

Microsoft SQL Server?s identity column generates sequential values for new records using a seed value. Seeding and reseeding an identity column is easy and relatively safe, if you do it correctly. Susan Sales Harkins shows you how it works.

 

LICT .NET Training | Kamal Protap Sir

DDL – Data definition Language(Which deals with database schemas and descriptions that how the data should reside in database) – create,alter,drop, truncate, comment
DML – Data manipulation language(Modify, Update, Delete data in database) – select, insert, update, delete
DCL – Data Control Language(Regarding the rights of the database)  grant,revoke

 

31-10-17 vids

SQL Query

SQL Aggregate functions, slides presentation: This presentation describes SQL Aggregate function covering SQL COUNT(), SUM(), MAX(), MIN(), AVG) functions with examples.

SubQuery:

A query within another query is called subquery or nested query . Inner Query, Outer query is in subquery.

Sub Query are two types they are, co-related sub query and non co-related subquery. They works for both DDL and DML statements
Co-related means relationship/dependent on
Non co-related means independent

There are two queries in sub query. You will see group by, order by is single type query but in sub query it needs multiple query

Non co related: They are two types,
Inner query: It will first execute. It will return some result. Independently it will execute.
Outer Query: Based on the inner query result now outer query will execute. The whole process is known as non co-related

 

= for comapring single value
IN for comparing multiple value

Co-related: It is dependent query. Inner query can’t execute independently. Inner query depends on outer query. Without returning row from the outer query inner can’t execute. In co-related sub query. All execute first.


Store Procedure:

MS SQL Server store procedure is used to save time write the same code again and again for storing in database and also get the rquired output by passing parameters.

Syntax:

Following the basic syntax of stored procedure creation.

Lots of parameters used in stored procedure:

IN paramater is a paramater whose value is past inside of the procedure
OUT paramater is a paramater whose value is pass outside the parameter
OUT parameter return some value
IN does not return any value
create procedure

 

Here are the store procedure example basic in my blog

Stored Procedures

 

Database Interview Questions

 

http://jompesh.com/videos/1/1760/lesson-26-database-normalization

Theory site: http://www.studytonight.com/dbms/generalization-and-specialization.php
Another Resource: http://www.guru99.com/database-normalization.html

SQL Join – Interview Clear

http://www.sql-join.com/sql-join-types/

In below:  The relationship between the two tables is specified by the customer_id key, which is the “primary key” in customers table and a “foreign key” in the orders table:

customer_id first_name last_name email address city state zipcode
1 George Washington [email protected] 3200 Mt Vernon Hwy Mount Vernon VA 22121
2 John Adams [email protected] 1250 Hancock St Quincy MA 02169
3 Thomas Jefferson [email protected] 931 Thomas Jefferson Pkwy Charlottesville VA 22902
4 James Madison [email protected] 11350 Constitution Hwy Orange VA 22960
5 James Monroe [email protected] 2050 James Monroe Parkway Charlottesville VA 22902
order_id order_date amount customer_id
1 07/04/1776 $234.56 1
2 03/14/1760 $78.50 3
3 05/23/1784 $124.00 2
4 09/03/1790 $65.50 3

Note that (1) not every customer in our customers table has placed an order and (2) there are a few orders for which no customer record exists in our customers table.

Inner Join Query:

Result:

first_name last_name order_date amount
george washington 07/04/1776 $234.56
john adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50

 

 

 

 

SQL Join – Interview Clear

http://www.sql-join.com/sql-join-types/

In below:  The relationship between the two tables is specified by the customer_id key, which is the “primary key” in customers table and a “foreign key” in the orders table:

customer_id first_name last_name email address city state zipcode
1 George Washington [email protected] 3200 Mt Vernon Hwy Mount Vernon VA 22121
2 John Adams [email protected] 1250 Hancock St Quincy MA 02169
3 Thomas Jefferson [email protected] 931 Thomas Jefferson Pkwy Charlottesville VA 22902
4 James Madison [email protected] 11350 Constitution Hwy Orange VA 22960
5 James Monroe [email protected] 2050 James Monroe Parkway Charlottesville VA 22902
order_id order_date amount customer_id
1 07/04/1776 $234.56 1
2 03/14/1760 $78.50 3
3 05/23/1784 $124.00 2
4 09/03/1790 $65.50 3

Note that (1) not every customer in our customers table has placed an order and (2) there are a few orders for which no customer record exists in our customers table.

Inner Join Query:

Result:

first_name last_name order_date amount
george washington 07/04/1776 $234.56
john adams 05/23/1784 $124.00
Thomas Jefferson 03/14/1760 $78.50
Thomas Jefferson 09/03/1790 $65.50

 

 

 

 

SQL

Good Learning Site for beginners:
Short:
http://www.dofactory.com/sql/left-outer-join

Elaborate:
https://www.w3schools.com/sql/sql_select.asp

Less Important But Okay:
https://www.tutorialspoint.com/sql/index.htm
Questions:

http://programabad.com/questions/5268/sql-normalization

http://www.studytonight.com/dbms/database-normalization.php

http://programabad.com/questions/3940/cgpa

Data Science MOOC (Will do someday)

http://www.kdnuggets.com/2015/09/top-20-data-science-moocs.html

https://lagunita.stanford.edu/courses/Home/Databases/Engineering/about

Dive into some SQL queries

In my 7th semester Level-3 Term -1 of my  BSc. Engineering course of Computer Science and Engineering in Daffodil International Univeristy(DIU) I am doing a course named Database Management System

Here our course teacher is using MS SQL Server 2008. I am sharing here what i have learnt from my class.

SQL stands for structured query language which help us to manage database easily

To create database in sql
Suppose a databse for university student

where we will create a table named student

after this we can show the empty table by this query

 

Now by this query  we will insert data into the table

Don’t forget to use values.It is actually SQL syntax and don’t forget to give
select * from statement at the end of the query as it will help to display the updated table where star or asterisks sign * means all info of this table

if we want to show some limited data from all then here is one example

 

Now I am going to update some of my data in the table then

2015-01-27 00_51_59-
Now I am going to delete some data from the table

 

2015-01-27 00_56_50-

Then here see the change.Age name with 22 and 23 have been deleted for this sql command.

We have used here OR statement because AND is not working in MS SQL Server 2008.I don’t know why…May be I don’t know the actual things about and/or in MS SQL. I apologize for this..

If this tutorial helped you to understand the basics of the sql query.Then don’t forget to keep soem comment below.

Thanks for reading 🙂