Lock Modes in SQL Server Part 2

Lock Modes in SQL Server ensures the concurrency and data integrity for multiple users. This is essential because we want all users to be able to use the database without any delays. Every thing to know about Levels of Locking in SQL Server read below link

Read More : Locking in SQL Server Part 1 

Lock Modes

Following are the lock modes which that can engage with the resources :

  • Exclusive (X)
  • Shared (S)
  • Update (U)

Exclusive lock (X)

This lock type, when imposed, will ensure that a page or row will be reserved exclusively for the transaction that imposed the exclusive lock, as long as the transaction holds the lock.

The exclusive lock will be imposed by the transaction when it wants to modify the page or row data, which is in the case of DML statements DELETE, INSERT and UPDATE. An exclusive lock can be imposed to a page or row only if there is no other shared or exclusive lock imposed already on the target. This practically means that only one exclusive lock can be imposed to a page or row, and once imposed no other lock can be imposed on locked resources

Shared lock (S) 

This lock type, when imposed, will reserve a page or row to be available only for reading, which means that any other transaction will be prevented to modify the locked record as long as the lock is active. However, a shared lock can be imposed by several transactions at the same time over the same page or row and in that way several transactions can share the ability for data reading since the reading process itself will not affect anyhow the actual page or row data. In addition, a shared lock will allow write operations, but no DDL changes will be allowed

Update lock (U)

This lock is similar to an exclusive lock but is designed to be more flexible in a way. An update lock can be imposed on a record that already has a shared lock. In such a case, the update lock will impose another shared lock on the target row. Once the transaction that holds the update lock is ready to change the data, the update lock (U) will be transformed to an exclusive lock (X). It is important to understand that update lock is asymmetrical in regards of shared locks. While the update lock can be imposed on a record that has the shared lock, the shared lock cannot be imposed on the record that already has the update lock

Intent locks (I)

This lock is a means used by a transaction to inform another transaction about its intention to acquire a lock. The purpose of such lock is to ensure data modification to executed properly by preventing another transaction to acquire a lock on the next in hierarchy object. In practice, when a transaction wants to acquire a lock on the row, it will acquire an intent lock on a table, which is a higher hierarchy object. By acquiring the intent lock, the transaction will not allow other transactions to acquire the exclusive lock on that table (otherwise, exclusive lock imposed by some other transaction would cancel the row lock).

This is an important lock type from the performance aspect as the SQL Server database engine will inspect intent locks only at the table level to check if it is possible for transaction to acquire a lock in a safe manner in that table, and therefore intent lock eliminates need to inspect each row/page lock in a table to make sure that transaction can acquire lock on entire table

This is all the basics about locking in SQL Server . See you with next article.

 

Locking in SQL Server

Locking in SQL Server is mechanism to control and manage concurrency maintaining data integrity and validation. This allows multiple users to access  and use database concurrently.

Consider the scenario and let me paint the complete picture here. When we talk about concurrency in DBMS , we are talking about DBMS allowing multiple users conducting multiple transactions and operations all at the same time. The operation can be read , update , delete or create. All of this , should execute on correct data. If Transaction T1 updates a row transaction T2 should be able to read the updated data. All transactions should satisfy their ACID properties.

Must-Read : SQL Server Concurrency & Transaction Management

100 transactions are trying to use the same table. Some of these transactions will only read data others will change the data. There has to be some way or mechanism in place , which should decide whether to show committed data or uncommitted data to other transactions. Maybe , we need to make sure that unless a transaction is not complete , no other transaction can read the data. Depending on situation , may be some other approach can be used. However , To do this SQL Server offers isolation levels.

Also-Read  : SQL Server Isolation Levels

Now that all the pieces are in place and if you are familiar with the topics from above . Can you tell about transaction life cycle?

Transaction Life Cycle

Locking in SQL Server

Transaction has ACID properties and its execution is very simple. If a transaction is successful and all the operations in it are complete. Transaction is successful and it gets commit. If not, all operations which were previously completed in that transaction will face a rollback.

Lets take two transactions here .

Transaction T1


SELECT * FROM Students

Transaction T2


delete FROM Students

SQL Server take these two transactions to execute them concurrently  checks the isolation level . Default Isolation level for SQL Server is Read committed isolation level , so naturally T2 won’t be able to delete data unless T1 is complete. SQL Server does it by using LOCKS. 

Locking in SQL Server Explained

Mechanism to control and manage concurrency maintaining data integrity and validation. This allows multiple users to access  and use database concurrently.

Default Isolation level of SQL Server will lock the data for Transaction T1 and will only allow T2 to access the same data once T1 is complete. This way it will manage the concurrency and data integrity.

Now we have tables , diskpages in database . Tables have rows. A transaction may engage all database , or simply a row or may be just a diskpages in database. SQL Server decides upon seeing the transaction what to lock and what not. Because if a transaction is reading only a row of one table , it won’t make sense to block all the tables . Similarly , if one table is involved in the transaction other tables should be available to execute any operations. This way concurrency is achieved and at the same time data validation and integrity also remains intact.

Lock Granularity

Lock granularity tells us about the level of lock use. We have following levels

  1. Database Level Locks
  2. Table Level Locks
  3. Page Level Locks
  4. Row-Level Locks
  5. Field-Level Locks

Database Level Lock

  1. Good for batch processing but unsuitable for online multi-user DBMSs
  2. T1 and T2 can not access the same database concurrently even if they use different tables

Locking in SQL Server

See how two transaction behaves when this lock is in action? Whole database is locked until T1 is complete.

Table Level Locks

  1. T1 and T2 can access the same database concurrently as long as they use different tables
  2. Can cause bottlenecks when many transactions are trying to access the same table (even if the transactions want to access different parts of the table and would not interfere with each other)
  3. Not suitable for multi-user DBMSs

Locking in SQL Server

Here T1 requires row 5 to be updated , so the lock and after that T2 gets the lock .

Page-Level Lock

  1. An entire disk page is locked (a table can span several pages and each page can contain several rows of one or more tables)
  2. Most frequently used multi-user DBMS locking method

Locking in SQL Server

Row-Level Lock

  1. Concurrent transactions can access different rows of the same table even if the rows are located on the same page
  2. Improves data availability but with high overhead (each row has a lock that must be read and written to)

Locking in SQL Server

Field-Level Lock

  1. Allows concurrent transactions to access the same row as long as they require the use of different fields with that row
  2. Most flexible lock buy requires an extremely high level of overhead

These diagrams illustrates how concurrency and locks works together on the basis of isolation levels to make sure that data is always consistent . Each transaction exhibits ACID properties.

 

MS SQL Query Execution Order Examples | SQL Beginner Guide

MS SQL Query Execution Order or SQL order of operations determines the sequence in which SQL clauses are executed by database engine . This helps in writing the error free and optimised queries .This SQL Query Processing is applied from a basic query to a thousands lines of queries , our database engine uses this execution order . See below the MS SQL Query Execution Order

  1. FROM
  2. ON
  3. OUTER
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. TOP

Also see : SQL SELECT Query & Statements

Recommended : MS SQL Group By Clause

Also see : MS SQL Having Clause

One of the biggest differences & advantages of SQL Server is the way it processes SQL Code. Where other programming languages have sequential order i.e. from top to bottom . MS SQL Query Execution Order is unique and systematic phases. Each phase is responsible for the successful execution of next phase . Each phase creates a virtual tables and feed it to the next phase and at the end all the pieces of puzzle are put together to display a result.

Let’s see some examples & we will apply the MS SQL Query Execution Order on the examples to see how it all goes behind the scene .

MS SQL Query Execution Order Examples


SELECT CourseID ,[StudentID] 

FROM [School].[dbo].[StudentGrade] 

WHERE [StudentID] in (7,8,9) 

First of all “FROM [School].[dbo].[StudentGrade]” clause will apply to the query

FROM Clause BI Talks BI

This table has 40 rows , we are only showing 10 here but SQL Server will obviously takes the complete table. So , SQL Server now has 40 rows in it’s virtual table. Next phase applicable is WHERE clause.

SQL Server will now execute this line WHERE [StudentID] in (7,8,9) . Result will be as follows

WHERE Clause BI Talks BI

Out of those 40 rows Where clause have filtered students with StudentID 7,8,9. At the end SELECT will select the columns which will be displayed.

So , you see how SQL Server handles the query. Can you write the order for the following query?

 
SELECT SUM([Grade]) As TotalGrades , StudentID 
FROM [StudentGrade] 
GROUP BY StudentID 
HAVING SUM(Grade); 

3 Things To Know About MS SQL Query Execution Order

  1. Aliases for the column cannot be used in WHERE clause. Because WHERE is executed first & SQL Server has no way of knowing about your alias.
  2. For INNER JOIN ON is like a WHERE clause. So if you put your filter criteria in ON clause it will behave as WHERE. For details read this from Pinal Dave. Huge fan of his blog for years.
  3. Virtual tables formed in each phases are logical only.

 

This is pretty much it about MS SQL Query Execution , if you like this article share with your friends 0n Social Media & recommend them to read us.

We are moving to advance tutorials on SQL in future, so if you have some certain topics in mind . I’ll advise you to comment them below and we will surely add them in the series as well.

See you next time

May the Force be with you!

 

MS SQL Having Clause | SQL Beginner Guide

Filtering the Groups in MS SQL ( Transact SQL-TSQL)  requires MS SQL Having clause . We discussed in detail about sets & groups in our post on Group by clause.   In order to be able to practice the examples that we will discuss , i recommend you to use our sample database. It’s a script you just have to copy paste in your MS SQL Server Management Studio & everything will be ready.

Also see : How to Install MS SQL Server Management Studio 

MS SQL Having Clause

MS SQL Having clause is used to apply a filter on groups that are formed as a result of group by clause.

SYNTAX


SELECT aggregatefunction(columname), columname2

FROM tablename

GROUP BY columnname2

HAVING condition

These four are mandatory steps to follow for MS SQL Having clause to work. You can use ORDER BY & WHERE as well if needed be.

Before we jump to the examples , I want to clear one more confusion here which is one of the biggest mistake people make while working with MS SQL Group by and MS SQL Having group clauses.

MS SQL Having VS Where

While working with groups you may often come across scenarios where you have to decide between where & having clause. Understanding what they do will help you to write a sound query .

Where is used when we want to apply filtering on a data set or simply when we want to use them on a simple data table. For example , I want to see how many courses are studied by any particular student or all students. Let those Students have Student ID (7,8,9) .See below


SELECT CourseID
,[StudentID]

FROM [School].[dbo].[StudentGrade]
WHERE [StudentID] in (7,8,9)

This is the data without grouping . Data looks like this now

Where example

Now time to apply grouping. My requirement was that I needed to see how many courses are studied by any particular student or all students.  You can see that StudentID 7 & 8 has two rows while 9 has only one.

Here you can count them but imagine you have a data set of thousands of rows. Counting them won’t be an option. So Let’s apply aggregate function to make our life easier.


SELECT COUNT([CourseID]) CourseID
,[StudentID]

FROM [School].[dbo].[StudentGrade]
WHERE [StudentID] in (7,8,9)
GROUP BY [StudentID]

This gives us

Grouping SQL

Much easier to count now right?

So this completes my requirement of how many courses are studied by any particular student or all students! Now comes the interesting part ! Are you ready?

My next requirement is to find the students who are studying 2 courses . Let’s see again the original data

Where example

Can you use a where clause? try & you will find the error saying An aggregate may not appear in the WHERE clause….. because you cannot use aggregated functions with WHERE & you certainly cannot apply a WHERE clause on GROUPED data.

Write this query now


SELECT COUNT([CourseID]) CourseCount
,[StudentID]
FROM [School].[dbo].[StudentGrade]
WHERE [StudentID] in (7,8,9)
GROUP BY [StudentID]
HAVING COUNT([CourseID])=2

 

Her’s how it works . See CourseCount was a column which was grouped so applying a Where clause on it wasn’t possible so we used HAVING Clause.

Where is working on StudentID because that is not a grouped column .

So the difference between where & having is simple . Where is applied use for simple set & Having is used with grouping.

MS SQL Group By & Having Clause In SQL

COUNT()

SELECT COUNT (Title) AS TotalCourses , CourseID
FROM [Course]
GROUP BY CourseID
HAVING COUNT(CourseID)=1 ;

SQL GROUP BY SUM()

SELECT SUM([Grade]) As TotalGrades , StudentID
FROM [StudentGrade]
GROUP BY StudentID
HAVING SUM(Grade);

GROUP BY COUNT() MULTIPLE COLUMNS

SELECT C.Title,COUNT([StudentID]) AS TotalStudentsinCourse ,DepartmentID
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
GROUP BY C.Title ,DepartmentID
HAVING COUNT([StudentID]);

So in last three of our articles we have learned what is aggregated data , about SQL Server functions on aggregation and how to apply filtering groups in SQL.

Also see

  1. SQL Server Aggregate Functions 
  2. Groups in SQL 

I have received a request from one of our readers about how grouping works in actual. Very interesting article is on its way! See you next time with our first Question & Answers post.

MS SQL Group By Clause | SQL Beginner Guide

MS SQL Group By Clause in SQL is used with aggregate functions to group data based on columns. Before , we jump to details of the Group By clause let’s try to understand about grouping.

SQL is based on set-theory and whenever you’re composing a query you treat a table as a whole & not row-by-row. Similarly , when we use aggregate functions we have to group the sets . This way you will have aggregated view of the set.

What is the Difference Between a Group & Set?

What is a SET in SQL

A set is collection of elements . In our sample database , we have a set of courses with the students who have taken them formed up as a table Studentgrade. See below

What do you see? Collection of different courses right?

But as a whole they’re all courses! Wonderful you’re learning already!

What is a Group in SQL

Group is also a set but it is enclosed under certain conditions. What those conditions might be is entirely up to us. We humans , learn by examples.

Let’s apply a condition on our StudentGrade table to see how many students have taken up a particular course. This we’ll form a Group!

SQL Group By

 

This is just a snapshot of 40 rows of data. Run this code


SELECT C.Title,COUNT([StudentID]) AS TotalStudentsinCourse

FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID

GROUP BY C.Title

This will return SQL Grouping Example

This is what we did here. We have applied a condition on a set of courses & student with their grades & formed a group of Number of students who have taken up a course.

Another way to explain it is this – > Group is based on Course Name & they are grouped together on a condition which is total number of students in a course

So , the difference between two is very simple now .

Set is collection of elements & group is a set based on any condition.

MS SQL Group By Clause

Aggregate functions (SUM () , COUNT(),AVG(),MIN(),MAX() ) requires data to be grouped by Group By Clause based on any one or more than one columns.

MS SQL Group By Clause appears at the end of the SELECT query .

SYNTAX

SELECT aggregatefunctions(columnname1) , columnname2
FROM tablename
GROUP BY columns2

Let’s use the example we have

SELECT C.Title,COUNT([StudentID]) AS TotalStudentsinCourse
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
GROUP BY C.Title

This is the result
SQL Grouping Example

5 Things to Know About MS SQL Group By Clause

  1. In aggregate functions if we use only one column on which aggregation is applied , then it’s not necessary to have a GROUP BY clause . But if you want to introduce another column then it becomes necessary
  2. When you apply Group by clause on a column , all the values in columns will appear only once. Since , they are now in groups. See above example of Courses
  3. All the columns that you will introduce in SELECT query , also will appear in GROUP BY clause .
  4. Grouping will be for all the columns in the group by clause .
  5. Group data should be filter by HAVING clause

We will write about HAVING Clause in next article.

Time to apply some aggregate functions and apply all the theory we have learned today!

GROUP BY EXAMPLES

SQL GROUP BY

COUNT()

SELECT COUNT (Title) AS TotalCourses , CourseID
FROM [Course]
GROUP BY CourseID;

 

SQL GROUP BY SUM()

SELECT SUM([Grade]) As TotalGrades , StudentID
FROM [StudentGrade]
GROUP BY StudentID

GROUP BY COUNT() MULTIPLE COLUMNS

SELECT C.Title,COUNT([StudentID]) AS TotalStudentsinCourse ,DepartmentID
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
GROUP BY C.Title ,DepartmentID

Aggregate functions & Group by functions are responsible when we want to summarise the data for our reports. Both of these concepts are comprehensively use by the ETL developers & query writers to do the reporting. So if you’re looking to be in any of these areas you have to have a great understanding of SETs , Groups & then , understanding of aggregate functions with Group by clause should be a piece of cake.
I hope you have enjoyed this article. Do share on your Social media & subscribe us for the latest articles.

See you with HAVING Clause article next time

Cheers!

SQL Server Aggregate Functions | SQL Beginner Guide

SQL Server offers several built-in functions. SQL Aggregate functions are among the ones most widely used in reporting & data joining scenarios. Following are few SQL Aggregate functions we will discuss today

  1. COUNT ()                  returns the total number of values in a given column
  2. SUM ()                       returns the sum of the numeric values in a given column
  3. AVG  ()                       returns the average value of a given column
  4. MIN()                         returns the smallest value in a given column
  5. MAX ()                       returns the largest value in a given column

What is Aggregate

Anytime when I start learning about anything , my best practice is to learn meaning of the word used. So , let’s start with aggregate  and it’s meaning.

If you search google it says

A whole formed by combining several separate elements

This couldn’t be more precise than this. In SQL Aggregate is when we combine a column to give out the single value result. For instance , you may be interested in average grades of a student. You may want to see the total number of courses taken up by a Student. Maximum marks obtained , minimum marks obtained & many such scenarios can come up where you want to see aggregated form rather than a long 100 rows of data. For such purposes SQL Server has Aggregate Functions.

SQL Server Aggregate Functions

If you’re looking to work in database , data warehouse or Business Intelligence you have to have a very strong grip on this topic. Reason is simple , databases are built so that they can be used later to retrieve data for reports. People who make decisions only see the aggregated data. Then if necessary , they go for detailed data.

I will use our sample database for the demonstration of these functions. I recommend you to copy paste the code in your SQL Server Management Studio and just execute the code. You will have a sample database to practice it on.

The SQL COUNT(), AVG() and SUM() Functions

SQL COUNT( ) Function

It is use for counting the number of rows for any columns. It only counts them regardless of what type of data they have. Let’s use Course table for this function. In case you don’t have the sample database. Look at the table below.

sql table

 

SQL COUNT Syntax

SELECT COUNT    (columnname)
FROM  tablename

COUNT() Examples

Just put column name and you will have the count of rows for that columns. Using our sample database let’s see total courses we have .


SELECT COUNT (Title) AS TotalCourses

FROM [Course];

Count function in sql

Our column Title has 10 rows according to our query which is correct.

 

What if you want to see how many courses have 4 credit hours . Simply introduce Where clause

SELECT COUNT (Title) AS CoursesWithCreditH
FROM [Course]
WHERE Credits =4;
Result :
Count where

You can find total number of rows in a table by using this function as well.


SELECT COUNT (*) As NumberOfRows
FROM [Course]

Result will be 10.

SQL Server Average Function AVG()

This function returns the average value of a numeric column. Simply returns the average

SYNTAX

SELECT AVG (columnname)

FROM tablename

AVG() Examples

For this we will use StudentGrade table. See it’s data belowsql server average function

If you want to see the average grades of students for all subjects. Run this query


SELECT AVG([Grade]) As AverageGrades
FROM [StudentGrade]

Can you introduce a WHERE clause ? Comment below if you can!

SUM() Function In SQL

This SQL aggregate function sums up all the values in the column & returns a single value

SYNTAX

SELECT SUM (columnname)

FROM tablename

SUM() Example

Write a query that returns total grades obtained by the whole class.


SELECT SUM([Grade]) As TotalGrades
FROM [StudentGrade]

sql sum

Introduce a StudentID in where clause so that we can see the total marks by a student


SELECT SUM([Grade]) As TotalGrades
FROM [StudentGrade]
WHERE StudentID=2

sql sum count

Difference Between SUM() & Count ()

Database developers often mix Sum with Count. These two functions have very clear names & their purpose is also exactly as written. The mistake is committed in it’s execution. Sometimes , we want the total number of rows in SQL table. You should use count function for that & when you want to sum the values in it then Use Sum Function.

See below


SELECT COUNT(*) As TotalRows
FROM [StudentGrade]

This will give you total number of rows which is 40, reason is simple because now you are counting the number of rows in table.

Now execute this query


SELECT COUNT(Grade) As TotalRows
FROM [StudentGrade]

Result will be 36 rows. You know why ? Because our Column Grade has 4 NULLS in it.

Now consider this . What if you want to see the total marks obtained by the StudentID 2. Will you use Count or SUM?

Best way is to run queries on it.  When we run this query


SELECT COUNT(Grade) As TotalRows
FROM [StudentGrade]
WHERE StudentID=2

It returns

Does this answer my question. I wanted to see the total marks not the total rows.

Let’s Run SUM query


SELECT SUM(Grade) As TotalGrades
FROM [StudentGrade]
WHERE StudentID=2

It Returns

sql sum vs count

This is actual answer.

So keep in mind the difference between Total Number of Rows is Count & Total of a column in SUM

 

MIN () & MAX () FUNCTION

Min() Stands for minimum & Max () Stands for Maximum. They are used to find minimum/maximum values in a column

SYNTAX MIN()

SELECT MIN(columnname)

FROM tablename

SYNTAX MAX()

SELECT MAX(columnname)

FROM tablename

MIN() Example


SELECT MIN(Grade) As TotalGrades
FROM [StudentGrade]

This returns minimum marks obtained by a student

MAX() Example


SELECT MAX(Grade) As TotalGrades
FROM [StudentGrade]

SQL max()
This returns maximum marks obtained.

SQL Aggregate functions are super useful in summary reports , in ETL , in data auditing & plenty more. You may have noticed that in all Examples I all only used one column. That is not co-incidence, If you introduce a second column in SELECT query you have to use GROUP BY clause & HAVING Clause for slicing the data. Group By & Having is our next article. We will learn how to use SQL aggregate functions to find useful data & a report.

Hope you have enjoyed this article & please do comments if you think there is a better explanation needed. See you next time

Happy Learning!
Cheers!

Stored Procedure And Function SQL Example

Stored Procedure And Function In SQL With Examples is the topic for today . Hello future & present database experts let’s  discuss

  1. What is Stored Procedure in SQL
  2. Stored Procedure Syntax
  3. How to run Stored Procedure in SQL
  4. User-Defined Functions & SQL Functions.
  5. Difference between Stored Procedures & Functions in SQL Server.

We will take SQL Server Stored Procedure Example & Function Example from our sample database.  I recommend you create this database. It will help you in a better understanding of this tutorial.

Let’s cut to chase now & learn SQL Stored Procedures first.

What is Stored Procedure in SQL

A stored procedure is a set of pre-compiled SQL statements that are used to perform a special task

what do I mean by pre-compiled SQL Statements? It means that your DBMS compiles & parse the SQL Statements after you create a stored procedure & is awaiting your execution call to execute the query.

Stored Procedures are very favourites of DBAs for reasons such as

  1. They can accept input parameters
  2. They can have output parameters as well & in case you don’t want to return anything. That is fine with SPs as well
  3. They provide better security to data. You can allow users to execute the queries without letting them see the actual SQL Statements. Which can be of literally , hundreds of lines
  4. They are super-fast. Reason is everything that is to be done with SQL Statement , such as its parsing , preparing query plan are done when a SP is created.
  5. They come very handy with Dynamic SQL & to perform DDL queries.
  6. Create a 100 line query & then create SQL SP on it. Now you can call those hundred lines of code with just one line & use it as many time as you desire.

Stored Procedures are heavily used together with Dynamic SQL . In future , BI TALKS BI plans to write a comprehensive guide on Dynamic SQL , I will recommend you to Subscribe US and stay updated with latest articles.

SQL Stored Procedure Syntax

Note this Syntax to create a Stored Procedure

CREATE PROCEDURE procedurename
AS
SQL Statement

SQL Stored Procedure EXAMPLE


CREATE PROCEDURE HelloWorldprocedure
AS
PRINT 'Hello World'

A stored procedure is used to retrieve data, modify data, and delete data in database table. You don’t need to write a whole SQL command each time you want to insert, update or delete data in an SQL database.

EXAMPLE 2 FROM OUR SAMPLE DATABASE


CREATE PROCEDURE [dbo].[UpdatePerson]
@PersonID int,
@LastName nvarchar(50),
@FirstName nvarchar(50),
@HireDate datetime,
@EnrollmentDate datetime
AS
UPDATE Person SET LastName=@LastName,
FirstName=@FirstName,
HireDate=@HireDate,
EnrollmentDate=@EnrollmentDate
WHERE PersonID=@PersonID;

Input Parameter are given here , which means this SP will require you to input these in the same order & a record will be updated. This SP is already in our Sample database , so you can just view it from there or see the code here !

Let’s execute these SPs now.

How To Run Stored Procedure In SQL

Two SPs that we created . SP HelloWorldprocedure  doesn’t have any Input Parameter and our second DeletePerson have one Input Parameter . We will see how both of them are executed.

EXEC HelloWorldprocedure

Here’s how you will execute SP with parameters

EXEC [dbo].[UpdatePerson]
@PersonID = 1,
@LastName = N'Ali',
@FirstName = N'Awais',
@HireDate = N'2002-08-06 00:00:00.000',
@EnrollmentDate = N'2002-09-01 00:00:00.000'

This will update the PersonID 1 with my name . See screenshots Interesting Stuff! Right?

Stored Procedure
Before SP
Stored Procedure
After SP

So you now have SQL Server Stored Procedure

User-Defined Functions & SQL Functions

Functions are widely used in computer world. If you’re a familiar with other programming language you must have heard about them . Similarly , SQL Functions do more or less same job .

There are two types of functions in SQL

SQL Built-In Functions

These are built-in functions to make it easy for you to do the jobs a lot quicker. CONCAT , CAST are two of many built-in functions of SQL Server . You can just use them directly in any SQL Statement.  We are summing up a tutorial on this . We will give you Syntax and examples of SQL functions so stay Tuned.

User-Defined Functions

Also knows as UDFs is also an SQL statement that can be use to return one single value or even a table. However , unlike SPs they have to return a value. Further UDFs have two types

  1. Scalar Valued Functions
  2. Table Valued Functions

Scalar Valued Functions Syntax & Example

Returns a single value as a result . See the Syntax & then examples


CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

-- Add the T-SQL statements to compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>

END
GO

Let’s create a Scalar Function which will convert SQL Datatime format to int


CREATE FUNCTION [dbo].[DateTimetoInt] (@Datetime Datetime )
returns BIGINT
BEGIN

DECLARE @DATE AS BIGINT
SELECT @DATE =

CAST(
CAST(DATEPART(YEAR,@Datetime) AS VARCHAR) + -- get the year
right('0' + CAST (DATEPART(MONTH,@Datetime) AS VARCHAR),2) + --get the month and zero pad
right('0' + CAST (DATEPART(DAY,@Datetime) AS VARCHAR),2) +
right('0' + CAST (DATEPART(HOUR,@Datetime) AS VARCHAR),2)+
right('0' + CAST (DATEPART(MINUTE,@Datetime) AS VARCHAR),2) +
right('0' + CAST (DATEPART(SECOND,@Datetime) AS VARCHAR),2) --get the day and zero pad
as BIGINT)
RETURN @DATE

END;

We can call this function in any Select query as column or simply with Select only. Here , we will use this function to transform the SQL datetime format column to SQL Int format


SELECT [dbo].[DateTimetoInt] ( '2005-09-01 00:00:00.000')as DateTimeToInt

Stored Procedure
Scalar Function

Table Valued Functions Syntax & Example

Returns a table as a result.See the Syntax & then examples

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)

We will create a function which will give us complete information about Person , when I will pass that PersonID to it.


CREATE FUNCTION dbo.GetNamesFromID
( -- Add the parameters for the function here
@PersonID int )
RETURNS TABLE

AS RETURN

( -- Add the SELECT statement with parameter references here
SELECT * FROM [School].[dbo].[Person] AS Student

WHERE PersonID=@PersonID )

See the result


SELECT * FROM [dbo].[GetNamesFromID] (1)

 

Difference between Stored Procedures & Functions in SQL Examples

I love when people ask differences between Stored Procedures & Functions because that is truly when you will be able to understand them & know what to use in a situation.

Both of these can be confusing sometimes , because they look very same in the functionality . My rule for such situation is  If It looks Easy , It’s Fishy ! I mean why would smart people create two things which are very same in nature. The answer is they only look . In real they are as different as Moon from Sun.

Following are few differences

Stored ProceduresFunctions
Returning data is not necessary. You have a choice.Returning data is necessary. You do not have a choice.
Scalar/Table both types of return data
They can have input & output parametersThey Only have input parameters
You can do TRY CATCH in SPs which makes it easier for use in Dynamic SQL.You cannot do TRY CATCH at all.
You can use DDL , DML operatoinsOnly Select & Insert can be used for inserting data in variable
Can't be used in WHERE , SELECT HAVINGCan be used in WHERE , SELECT HAVING
Have the ability to call functionsThey cannot call SPs
Can handle Transaction ManagementUnable to handle Transaction Management
My favourite These SPs have the ability to use Dynamic SQLDoesn't handle Dynamic SQL

Ahhhhh! Such a long article but i hope you enjoy this & get to learn some useful concepts. There is a lot we can discuss on Stored Procedure And Function but this should be enough for now. As of now BI Talks BI is compiling Optimisation Practices & Dynamic SQL series. Hopefully , you will see much more about topics there.

You can Subscribe Us for more updates ,follow us on twitter , facebook , Quora & Reddit. Comment below if you have anything to discuss or share!

See you with next article very soon!

Happy Learning!
Cheers!

 

What Is View In SQL

In SQL one of the very useful database object is View.  We will learn SQL CREATE VIEW , ALTER VIEW & DROP VIEW Statements today with the conceptual understanding of it as well. Now , If you have our College database you can do basic SQL queries practice . You can use our Sample College Database script & generate one database in your system.

SQL View

                              View is the virtual table formed as a result of a query

Word Virtual needs an explanation , so here it goes. The tables are stored physically in our database systems. They’re assigned a space with reference address & everything computers do to store data in hard disks.
Views are different , because they are built on top of tables & they are filled from the data of these tables. Just a Simple SELECT Query can be formed as view or you can create a long complex query & store it as a view . Next time you wont have to write the SQL Query to obtain same data. Just call your view & it will give you the data. Think of it as an arbitrary layer that you can create on tables & only appears when you call it.

 

The reason they are so wonderful is that they do not disturb original data or operations such as write or read from their original tables. This is very useful when you want to create a distinction between users who only needs to read data or write data.

 

Another advantage is the data security. If you want your users to read only part of table hiding few columns & making visible only needed ones . Create a view and only allow their user to access that View. These are some basic level uses of Views. I will write detailed article on Advantages of SQL Views

Let’s understand this by explanations & create SQL views with examples.

 

WHY CREATE SQL VIEW

Take this example of Inner Join we discussed .



SELECT Student.PersonID As StudentID ,
 Student.FirstName,
 Student.LastName ,
 Course.Title ,
 Grade.Grade
 FROM [School].[dbo].[Person] AS Student 
 
 INNER JOIN StudentGrade AS Grade
 ON Student.PersonID=Grade.StudentID

JOIN Course AS Course
 ON Course.CourseID =Grade.CourseID

We learnt about SQL Joins in previous post. Now advantage of creating a view is that once you have written a basic SQL query , you only have to create a view and give it a name. See below ,

CREATE SQL VIEW SYNTAX

CREATE VIEW viewname  AS  query

VIEW EXAMPLE

I have created a view on three tables which I have used previously in inner join example  .

 
CREATE VIEW dbo.StudentResult AS 
SELECT Student.PersonID As StudentID , Student.FirstName,
       Student.LastName , Course.Title , Grade.Grade 
FROM [School].[dbo].[Person] AS Student 
INNER JOIN StudentGrade AS Grade ON Student.PersonID=Grade.StudentID 
INNER JOIN Course AS Course ON Course.CourseID =Grade.CourseID 

Take a long breath & give it a moment . In the start we said that the view is just a virtual table . So if you’re wondering how to call a view or use the view to fetch data. After creating the view , treat it just like a table for reading data.

See Below

 SELECT *
 FROM dbo.StudentResult

Now , instead of re-writing the query again to fetch same data . I will just use my VIEW to have it. Because , it behaves like a table , i can use it for joins or as a data source for applications.

ALTER SQL VIEW SYNTAX & EXAMPLE

ALTER  is the DDL command (In case you don’t know what is ddl? See our post on DDL & DML) and it is used to as name suggest alter the state of database objects. Why do we need to Alter View?
To add new columns or to change column names or just to rewrite the query.  Same Syntax as of Create View. Just Add Alter keyword here

 
ALTER VIEW dbo.StudentResult AS 
SELECT Student.PersonID As StudentID , 
Student.FirstName, Student.LastName , Course.Title , Grade.Grade 

FROM [School].[dbo].[Person] AS Student 
LEFT JOIN StudentGrade AS Grade ON Student.PersonID=Grade.StudentID 
LEFT JOIN Course AS Course ON Course.CourseID =Grade.CourseID 

 

I have changed the join type and now my view will give me results for left join .

ALTER SQL VIEW SYNTAX & EXAMPLE

 

DROP VIEW dbo.StudentResult 

All of this is basic View Concepts. Views are a topic of huge discussion . Their use is of utmost importance & Views are heavily used for reporting purposes. BI Talks BI is composing a detailed e-book on such topics & best practices for reporting . Views will be discussed heavily in the book because this article is for people who want to learn sql at the beginner level. Soon we will write on advance level. If you have enjoyed the article ! Subscribe Us & stay Tuned for more !
We are live on twitter & Quora as well with the name BI Talks BI. If you are an active member their & need anything you can contact us there or comment below!

Happy Learning!
Cheers!

Features Of Database Management System?

We have all heard so much about Database management systems that sometimes we confuse the two things. One is RDBMS which is  Dr. Codd’s data model which has 12 rules & the other is the software which is used to manage the RDBMS. Since , we humans love acronyms & like to shorten down everything these two terms are often used together . We have removed R from RDBMS  and started to use DBMS . Trust me it does confuse even the experienced people sometimes. Because S in both terms have different meaning. For RDBMS it is System and for DBMS it is Software. With this out-of-the-way let’s discuss features of database management system ( the software)

 

FEATURES OF DATABASE MANAGEMENT SYSTEMS

We will discuss few very important ones to familiarise ourselves with the basics of database management system.

Data Dictionary Management

Actual dictionary has meanings of words in it. Similarly , a DBMS has a dictionary where it stores information about all tables , columns and relationship between them & the change in them.  This saves you time to track & manage all of this by yourselves

Data Storage Management

Data is to be stored on disk of our computers right? Now , imagine you have to create the right structures or program the arrays , data list and create data structures to handle the data and the pressure of it’s growing size? It will be too much to handle . So a DBMS do this job for us . We just follow our designs & data structures and data storage is handled by our DBMS.

Data Transformation and Presentation

Modern databases have users from all parts of the world. You use facebook  & you see that it gives you language options or time & date options according to your region . Now this is so spotless that most of miss the genius work of programmers who do it. DBMS helps to achive this task by providing options to transform data in multiple formats & then present it to the appropriate users.

Date format in England is dd/mm/yyyy & in United States it is mm/dd/yyyy . DBMS should be able to manage this change & present it to both American & English user their time formats. Howz that for a requirement 😉

Security Management

DBMS mustn’t allow breach of user’s access .  DBMS should have appropriate security rules that can restrict access to tables , schema & other database objects. Why is this so important?
My client wouldn’t like their employees to access HR information. Such as salaries of other employees &  their contract details. Such data should only be accessible for HR department. Makes sense? 

Backup and Recovery Management

Sometimes , names are enough to show what does it mean. DBMS should be able to take backups and provide methods to recover the lost data . Sometimes , maybe some data developer accidentally removes your production table . Accidents happen all the time? DBMS should have logs & other mechanism than can support data recovery!

Resigning employees or a sleepy developers tend to make mistakes more! DBMS should be able to  help us out!

Data Integrity Management

Wallmart , Amazon have thousands of transactions every second if not minutes. Their orders coming in , inventory coming up on the screen . Now it’s the job of DBMS to make sure that data integrity is not affected in any case. Remember , about primary keys? If DBMS allows duplication of primary key . Can you guess what will happen?

Let me tell you. You could log in in your account and end up seeing my account. Why? because DBMS failed to manage your user id against your account. Imagine the damage. Someone’s credit card shopping is billed to you.

So DBMS should be able to maintain data integrity , data concurrency & data consistency!

Database Access Languages and Application Programming Interfaces

You can access database by a query language. Standard Query Language is one example and mostly used around the world.

If you have more features to add & comment them below. We will add it to this article. If you have enjoyed reading this. Please , share this with your friends & colleagues. Subscribe Us to receive latest articles in your inbox.

Write to us if you want to add any topic & we will surely do so.

Happy Learning!

Cheers!

 

SQL Joins Explained | SQL Beginners’ Guide

SQL Joins is an SQL clause and it combines data from multiple tables on basis of related columns between them. So you have designed a wonderful DBMS & you have created an SQL database ( If Not? Use our sample database) to support your DBMS . You created an ERD & normalized it as well & now you are successfully able to store the transactions into your database. Great Job! But now client has asked you to show you data which you have placed in three four different tables. What Now? How do you do it? SQL Join is the answer you’re looking for.

More often than not , you will have to create reports & find data which is spread across multiple tables . Primary keys , foreign keys and other candidate keys that we have in our tables are used to join different tables and match the data that needs to be related.

I am a big fan of SQL , reason is the fact that most of the keywords it uses have obvious meanings & easy to understand. Meaning of the word Join is ‘Connect/Link’ & this is exactly what SQL Joins do .

SQL JOINS

We have following types of SQL Joins :

  1. INNER JOIN
  2. OUTER JOIN
  3. CROSS JOIN
  4. FULL OUTER JOIN

Mostly , you  will use the first two kind of joins because they are most suitable to find data . The last two are rarely used but for the sake of understanding we will discuss each one!

JOIN SYNTAX


SELECT columns

FROM table1 AS [alias for table1]

JOINNAME table2 AS [alias for table2]

ON[alias for table1].keycolumn=[alias for table2].keycolumn

Take a long breath & have a good at look at this syntax because this is the syntax that all joins follow.

INNER JOIN

Gives back data based on matching conditions. Rows returned should have matching columns present on both sides of the join.

 

Using our sample database , I want to see about the grades of Students in their courses with their names. We know that  table Person have student names and table StudentGrade has their grades. What should we do ? Take a look at the data & the Select query

sql joins sql joins

sql joins

SQL Tips:

Writing SQL Joins mainly involves identifying the matching column in both tables . Matching Columns should have same SQL datatype . Names can be different but SQL datatypes should be same . Can you identify the column for matching by looking at the data? Comment below ! If you can

SQL Server Management Studio by defaults , picks inner join for execution if you fail to mention the complete join name. See below

Let’s apply the following query and see results


SELECT Student.PersonID As StudentID ,
Student.FirstName,
Student.LastName ,
Course.Title ,
Grade.Grade
FROM [School].[dbo].[Person] AS Student

INNER JOIN StudentGrade AS Grade
ON Student.PersonID=Grade.StudentID

JOIN Course AS Course
ON Course.CourseID =Grade.CourseID

 

Result
sql joins

Take  a look and see how many courses are in our final result and how many missed. This is our SQL Inner Join with example.

sql joins

OUTER JOIN

Outer Join returns all data for one table & matching data from other table no matter matching columns match in both tables or not .  They are of two types and you will be able to understand them better by reading about them.

LEFT OUTER JOIN

SQL at it’s best , just as i said this language is so easy because it’s just so natural. Left Outer join returns all data from left side of the query & only matching from right side.


SELECT Course.Title ,Grade.StudentID,
Grade.Grade
FROM Course AS Course

LEFT OUTER JOIN StudentGrade AS Grade
ON Course.CourseID =Grade.CourseID

When you execute this query , you will find a course ‘Trigonometry ‘ which has no Student but still it appears in our result? Reason is because in this query Course table is on left side so everything on left side will be part of the result. Simple enough right! ?

sql joins

sql joins

RIGHT OUTER JOIN

Right Outer join returns all data from right side of the query & only matching from left side.


SELECT Course.Title ,Grade.StudentID,
Grade.Grade
FROM Course AS Course

RIGHT OUTER JOIN StudentGrade AS Grade
ON Course.CourseID =Grade.CourseID

sql joins

sql joins

FULL OUTER JOIN

This join is very ideal when you just want the data of two tables at one place , without worrying about matching columns . It just gives everything from both tables.


SELECT Course.Title ,Grade.StudentID,
Grade.Grade
FROM Course AS Course

FULL JOIN StudentGrade AS Grade
ON Course.CourseID =Grade.CourseID

Test it at your side and post the images in comments!

sql joins

SQL Tips:
  1.  SQL Server Management Studio don’t require Outer keyword.
  2. If someone asks the minimum & maximum number of rows produced in left outer join. What will be your response? If you say that minimum & maximum number of rows returned as a result will be the total number of rows on left side table. You’re partially correct. That will be the minimum number of rows & yeah about maximum . Ehhh!   here’s the thing you can never be sure about maximum. We will discuss this in our upcoming e-book  of SQL Tips & Design Practices which will be exclusively sent to all our subscribers.

CROSS JOIN

Cross Joins produces the cartesian product of the two tables. For instance if  StudentGrade has 40 rows and Course table has 10 rows . When you will cross join them the resultant will be 400 rows.


SELECT Course.Title ,Grade.StudentID,
Grade.Grade
FROM Course AS Course

CROSS JOIN StudentGrade AS Grade

ON keyword and matching columns are not required. Run this query in your SQL Server management studio and see the results.

There’s one more Join called Self Join. We will discuss it in our Advance SQL Concept series because if you’re a beginner i don’t want to mess up with your head. So for now just familiarise yourself with these SQL  joins and go through this SQL Joins Tutorials. Hopefully , it will help you!

I hope this was helpful . Do share this with your friends to help them & Subscribe to get that e-book free . See you with the next article.

Cheers!

Sample Student Database SQL | SQL Training

Before we start  with Joins , Constraints , View , Stored Procedures & Functions  , we need a Sample Student Database SQL for practice . I looked around and found this sample. I have simply downloaded it from MSDN site and you can either go their official website and use this link or you can copy the entire code from below screen.

Our job is to provide you with learning material from all available resources. Another benefit of using this for all our SQL Queries to practice will be the fact that you may be able to find the solutions for the same data across different platforms.

SAMPLE STUDENT DATABASE SQL

 


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [master];
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'School')
 DROP DATABASE School;
GO

-- Create the School database.
CREATE DATABASE School;
GO

-- Specify a simple recovery model 
-- to keep the log growth to a minimum.
ALTER DATABASE School 
 SET RECOVERY SIMPLE;
GO

USE School;
GO

-- Create the Department table.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[Department]') 
 AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Department](
 [DepartmentID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Budget] [money] NOT NULL,
 [StartDate] [datetime] NOT NULL,
 [Administrator] [int] NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
 [DepartmentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Create the Person table.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[Person]') 
 AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Person](
 [PersonID] [int] IDENTITY(1,1) NOT NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [FirstName] [nvarchar](50) NOT NULL,
 [HireDate] [datetime] NULL,
 [EnrollmentDate] [datetime] NULL,
 CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED 
(
 [PersonID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Create the OnsiteCourse table.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]') 
 AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OnsiteCourse](
 [CourseID] [int] NOT NULL,
 [Location] [nvarchar](50) NOT NULL,
 [Days] [nvarchar](50) NOT NULL,
 [Time] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED 
(
 [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Create the OnlineCourse table.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]') 
 AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OnlineCourse](
 [CourseID] [int] NOT NULL,
 [URL] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED 
(
 [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

--Create the StudentGrade table.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[StudentGrade]') 
 AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StudentGrade](
 [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
 [CourseID] [int] NOT NULL,
 [StudentID] [int] NOT NULL,
 [Grade] [decimal](3, 2) NULL,
 CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED 
(
 [EnrollmentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Create the CourseInstructor table.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]') 
 AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CourseInstructor](
 [CourseID] [int] NOT NULL,
 [PersonID] [int] NOT NULL,
 CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED 
(
 [CourseID] ASC,
 [PersonID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Create the Course table.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[Course]') 
 AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Course](
 [CourseID] [int] NOT NULL,
 [Title] [nvarchar](100) NOT NULL,
 [Credits] [int] NOT NULL,
 [DepartmentID] [int] NOT NULL,
 CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED 
(
 [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Create the OfficeAssignment table.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]')
 AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OfficeAssignment](
 [InstructorID] [int] NOT NULL,
 [Location] [nvarchar](50) NOT NULL,
 [Timestamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED 
(
 [InstructorID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- Define the relationship between OnsiteCourse and Course.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
 WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]'))
ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADD 
 CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[OnsiteCourse] CHECK 
 CONSTRAINT [FK_OnsiteCourse_Course]
GO

-- Define the relationship between OnlineCourse and Course.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
 WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]'))
ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADD 
 CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[OnlineCourse] CHECK 
 CONSTRAINT [FK_OnlineCourse_Course]
GO

-- Define the relationship between StudentGrade and Course.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
 WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Course]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD 
 CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[StudentGrade] CHECK 
 CONSTRAINT [FK_StudentGrade_Course]
GO

--Define the relationship between StudentGrade and Student.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
 WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Student]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD 
 CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[StudentGrade] CHECK 
 CONSTRAINT [FK_StudentGrade_Student]
GO

-- Define the relationship between CourseInstructor and Course.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
 WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD 
 CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK 
 CONSTRAINT [FK_CourseInstructor_Course]
GO

-- Define the relationship between CourseInstructor and Person.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
 WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD 
 CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK 
 CONSTRAINT [FK_CourseInstructor_Person]
GO

-- Define the relationship between Course and Department.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
 WHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[Course]'))
ALTER TABLE [dbo].[Course] WITH CHECK ADD 
 CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO

--Define the relationship between OfficeAssignment and Person.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
 WHERE object_id = OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]'))
ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADD 
 CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[OfficeAssignment] CHECK 
 CONSTRAINT [FK_OfficeAssignment_Person]
GO

-- Create InsertOfficeAssignment stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[InsertOfficeAssignment]') 
 AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[InsertOfficeAssignment]
 @InstructorID int,
 @Location nvarchar(50)
 AS
 INSERT INTO dbo.OfficeAssignment (InstructorID, Location)
 VALUES (@InstructorID, @Location);
 IF @@ROWCOUNT > 0
 BEGIN
 SELECT [Timestamp] FROM OfficeAssignment 
 WHERE InstructorID=@InstructorID;
 END
' 
END
GO

--Create the UpdateOfficeAssignment stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[UpdateOfficeAssignment]') 
 AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]
 @InstructorID int,
 @Location nvarchar(50),
 @OrigTimestamp timestamp
 AS
 UPDATE OfficeAssignment SET Location=@Location 
 WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;
 IF @@ROWCOUNT > 0
 BEGIN
 SELECT [Timestamp] FROM OfficeAssignment 
 WHERE InstructorID=@InstructorID;
 END
' 
END
GO

-- Create the DeleteOfficeAssignment stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[DeleteOfficeAssignment]') 
 AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]
 @InstructorID int
 AS
 DELETE FROM OfficeAssignment
 WHERE InstructorID=@InstructorID;
' 
END
GO

-- Create the DeletePerson stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]') 
 AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[DeletePerson]
 @PersonID int
 AS
 DELETE FROM Person WHERE PersonID = @PersonID;
' 
END
GO

-- Create the UpdatePerson stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]') 
 AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[UpdatePerson]
 @PersonID int,
 @LastName nvarchar(50),
 @FirstName nvarchar(50),
 @HireDate datetime,
 @EnrollmentDate datetime
 AS
 UPDATE Person SET LastName=@LastName, 
 FirstName=@FirstName,
 HireDate=@HireDate,
 EnrollmentDate=@EnrollmentDate
 WHERE PersonID=@PersonID;
' 
END
GO

-- Create the InsertPerson stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]') 
 AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[InsertPerson]
 @LastName nvarchar(50),
 @FirstName nvarchar(50),
 @HireDate datetime,
 @EnrollmentDate datetime
 AS
 INSERT INTO dbo.Person (LastName, 
 FirstName, 
 HireDate, 
 EnrollmentDate)
 VALUES (@LastName, 
 @FirstName, 
 @HireDate, 
 @EnrollmentDate);
 SELECT SCOPE_IDENTITY() as NewPersonID;
' 
END
GO

-- Create GetStudentGrades stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]') 
 AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetStudentGrades]
 @StudentID int
 AS
 SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
 WHERE StudentID = @StudentID
' 
END
GO

-- Create GetDepartmentName stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[GetDepartmentName]') 
 AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetDepartmentName]
 @ID int,
 @Name nvarchar(50) OUTPUT
 AS
 SELECT @Name = Name FROM Department
 WHERE DepartmentID = @ID
'
END
GO

-- Insert data into the Person table.
USE School
GO
SET IDENTITY_INSERT dbo.Person ON
GO
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (3, 'Justice', 'Peggy', null, '2001-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (5, 'Harui', 'Roger', '1998-07-01', null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (6, 'Li', 'Yan', null, '2002-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (7, 'Norman', 'Laura', null, '2003-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (9, 'Tang', 'Wayne', null, '2005-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (12, 'Browning', 'Meredith', null, '2000-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (13, 'Anand', 'Arturo', null, '2003-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (15, 'Powell', 'Carson', null, '2004-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (16, 'Jai', 'Damien', null, '2001-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (18, 'Zheng', 'Roger', '2004-02-12', null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (19, 'Bryant', 'Carson', null, '2001-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (21, 'Holt', 'Roger', null, '2004-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (22, 'Alexander', 'Carson', null, '2005-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (24, 'Martin', 'Randall', null, '2005-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (26, 'Rogers', 'Cody', null, '2002-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (28, 'White', 'Anthony', null, '2001-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (30, 'Shan', 'Alicia', null, '2003-09-01');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (32, 'Xu', 'Kristen', '2001-7-23', null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (33, 'Gao', 'Erica', null, '2003-01-30');
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null);
GO
SET IDENTITY_INSERT dbo.Person OFF
GO

-- Insert data into the Department table.
INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2);
INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
VALUES (2, 'English', 120000.00, '2007-09-01', 6);
INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
VALUES (4, 'Economics', 200000.00, '2007-09-01', 4);
INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3);
GO

-- Insert data into the Course table.
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (1050, 'Chemistry', 4, 1);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (1061, 'Physics', 4, 1);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (1045, 'Calculus', 4, 7);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (2030, 'Poetry', 2, 2);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (2021, 'Composition', 3, 2);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (2042, 'Literature', 4, 2);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (4022, 'Microeconomics', 3, 4);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (4041, 'Macroeconomics', 3, 4);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (4061, 'Quantitative', 2, 4);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (3141, 'Trigonometry', 4, 7);
GO

-- Insert data into the OnlineCourse table.
INSERT INTO dbo.OnlineCourse (CourseID, URL)
VALUES (2030, 'http://www.fineartschool.net/Poetry');
INSERT INTO dbo.OnlineCourse (CourseID, URL)
VALUES (2021, 'http://www.fineartschool.net/Composition');
INSERT INTO dbo.OnlineCourse (CourseID, URL)
VALUES (4041, 'http://www.fineartschool.net/Macroeconomics');
INSERT INTO dbo.OnlineCourse (CourseID, URL)
VALUES (3141, 'http://www.fineartschool.net/Trigonometry');

--Insert data into OnsiteCourse table.
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (1050, '123 Smith', 'MTWH', '11:30');
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (1061, '234 Smith', 'TWHF', '13:15');
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (1045, '121 Smith','MWHF', '15:30');
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (4061, '22 Williams', 'TH', '11:15');
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (2042, '225 Adams', 'MTWH', '11:00');
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (4022, '23 Williams', 'MWF', '9:00');

-- Insert data into the CourseInstructor table.
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (1050, 1);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (1061, 31);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (1045, 5);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (2030, 4);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (2021, 27);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (2042, 25);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (4022, 18);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (4041, 32);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (4061, 34);
GO

--Insert data into the OfficeAssignment table.
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (1, '17 Smith');
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (4, '29 Adams');
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (5, '37 Williams');
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (18, '143 Smith');
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (25, '57 Adams');
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (27, '271 Williams');
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (31, '131 Smith');
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (32, '203 Williams');
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (34, '213 Smith');

-- Insert data into the StudentGrade table.
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2021, 2, 4);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2030, 2, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2021, 3, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2030, 3, 4);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2021, 6, 2.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2042, 6, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2021, 7, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2042, 7, 4);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2021, 8, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (2042, 8, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4041, 9, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4041, 10, null);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4041, 11, 2.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4041, 12, null);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4061, 12, null);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4022, 14, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4022, 13, 4);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4061, 13, 4);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4041, 14, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4022, 15, 2.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4022, 16, 2);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4022, 17, null);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4022, 19, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4061, 20, 4);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4061, 21, 2);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4022, 22, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4041, 22, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4061, 22, 2.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (4022, 23, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1045, 23, 1.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1061, 24, 4);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1061, 25, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1050, 26, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1061, 26, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1061, 27, 3);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1045, 28, 2.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1050, 28, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1061, 29, 4);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1050, 30, 3.5);
INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
VALUES (1061, 30, 4);
GO

If you haven’t installed SQL Server Yet. You can use SQL Server Installation Guide. It has the setup link and details about all you need.

Copy this code and open New Query and paste it there. It will automatically create everything for you! After this you can follow use Select Query and browse through data & look around . If you have any questions , comment them below.

See you very soon with post on Joins.

Cheers!

What is DDL , DML , DCL & TCL

Welcome to the SQL Beginners Guide series’ latest article. We are about to discuss SQL Languages also known as categories of SQL Statements . We will discuss what is DDL , DML and other types and what they do . However , before we move forward. In case you need to check other articles.

Following are the most recent articles

  1. What is SQL ?
  2. MS SQL Installation Step By Step
  3. SQL Select Query
  4. Basic SQL Queries & Commands

Reason for referring you to the previous articles is very simple. This is a series and they are being composed in order . Therefore , this order will help you in developing a better understanding for SQL query development .

SQL commands have following categories

 

Types of SQL Statements/SQL Commands

  • DML  Data Manipulation Language
  • DDL   Data Definition Language
  • DCL   Data Control Language
  • TCL   Transaction Control Language

Let’s do it people , time to discuss them .

DML  ( Data Manipulation Language )

Remember , CRUD Operations ? The basic SQL queries we use to work with the data in table . It can be as simple as retrieving information from table  , inserting , deleting or update.

SELECT – Retrieves data from tables

INSERT – Inserts data into tables

UPDATE– Update data in tables.

DELETE – Delete data or objects

Previous article , has these SQL queries for practice. In case , you’re interested.

DDL ( Data Definition Language )

Statements that are used to change the state of database objects such as table , schema etc.

CREATE – Create the database objects

ALTER – Can you guess? Yes , Alter the already defined values. Can be datatypes of columns or names of the objects

DROP -Delete existing object from database

TRUNCATE – Also delete the existing from database but to the point that it cannot be restored because no log maintenance for this operation unlike others.

DCL  ( Data Control Language )

Statements that are used to manage and control the access of users on database objects

GRANT – allow user acces to read/write on certain database objects
REVOKE – removes user access from read/write permission on database objects.

TCL ( Transaction Control Language )

SQL Statements that allows you to control and manage transactions to maintain the integrity of data within SQL statements.

BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in case of any error.

In future , we are planning to write on Concurrency Control where you will see a good use of the TCL Statements. Therefore , my recommendation is to subscribe us and follow us for the latest articles.

if you have any questions , suggestion or feedback please post in the comments. Share us with your friends and fellows. This is what will keep BI Talks BI moving!

I have been using this Adidas Shoes.  in Dubai for a while now for my workouts. Was going through Amazon for something to recommend you guys as well. You can try them and let me know how you feel!

Happy Learning!

Cheers!