In our recent tutorial we learned how to install MS SQL Server Management Studio 2012 . Today , we will learn basic SQL SELECT query & SQL Statements & how to write SQL Statements. MS SQL is the extended version of SQL ( Check the link for explanation) and it is known as T-SQL . Therefore , naturally , You will learn SQL and TSQL both in this tutorial .
We have a lot of ground to cover today and following are the topics that we will learn today
SQL Syntax ( SELECT & FROM Clauses )
Difference between SQL Query & SQL Statement.
How To Write Basic SQL Query
SQL Syntax (SELECT & FROM Clauses ):
In DBMS Concept Series We kept saying that DBMS implementation is very closely related to how we deal with situations in our daily-lives . SQL syntax is the perfect example of it. The keywords used in SQL are plain and simple English words , that you use to do the respective operations. Point to remember . SQL is not case-sensitive , this could be very relaxing if you’re coming after learning other programming languages. One of the best Perks of SQL Programming it is.
Before , we explain the Syntax , behold this Database Table .
Let’s apply a very basic SQL query on this which will be the perfect example for the SQL syntax is
SELECT column-name (use * symbol to have all columns of table)
SELECT * FROM STUDENTS ;
SELECT and FROM are the two clauses which are most important and vital for any SQL query when retrieving data from database table. FROM clauses determines logically as to which table to get the data from and SELECT decides what to fetch from the table. As for Semi-Colon , this is the Standard defined for SQL by ANSI . Though in T-SQL , semi-colon is not required and many other database management software also doesn’t require it. However , a good Practice is to do it so that your code can be executed in other DBMS softwares as well without having to change it.
In above query , SELECT will get all the columns with all data FROM Table Student. Easy enough right?
Difference Between SQL Query & SQL Statement
Your use of terminology reflects on your knowledge. Therefore, you should make an effort to understand and use correct terminology. SQL query & SQL Statement are two terms that are widely misunderstood . You should however , have a clear understanding of it.
SQL Query :
SQL Query is simply a question asked to get an answer or in DBMS terms the data based on certain criteria from the database. It doesn’t change the state of the tables . Simply , returns data back. That is why SELECT is often known as SQL Query.
SELECT * FROM STUDENTS;
Above query only returns the data , no change in table Students. That is because this is SQL Query
SQL Statements :
Set of SQL commands which can change the state of database/table or related objects. Examples can be CREATE , DROP , INSERT etc. You see , these statements will change their previous state and give them new state according to our statement.
DROP TABLE STUDENTS;
Will drop ( remove ) table Students from database . Hence , the SQL Statement.
Comment below , if you have one more example.
One more terms used is called Batch.
Series of SQL Statements are batch separated by semicolon.
CREATE DATABASE STUDENT;
DROP DATABASE STUDENT;
Microsoft Management Studio executed these two SQL Statements together by sending them to It’s database engine in one go. This is batch execution of the SQL Statements.
How To Write Basic SQL Query
Thought process is very important to compose a SQL query. You need to know what you are doing and how your database management software where you are executing your query will behave. Trust me , if you don’t know this you can never be good at writing SQL queries .
SQL Query has mainly two parts Projection & Selection. No matter how complex query is it will always be reduced to these two parts. Therefore , it’s essential that we dig deep into it. See below
Selection of Column(s) which will be returned as part of data set . Since columns are vertical . Hence ,the vertical box in picture.
Selection of Row(s) which will be returned as part of data set. Since ,rows are horizontal Hence , the horizontal box in picture.
Interested to see how it Management Studio will behave with this SQL Query ?
SELECT STUDENT_ID, NAME
Projection -> Select Name , Student_ID because we have decided what columns we want.
Selection -> Where Student_ID=2 because we have decided what rows we want.
WHERE clause is use to filter the data set . If you don’t add WHERE clause in your query , your DBMS software automatically decided that you need all rows of data. Therefore , in the first example without WHERE clause it returned as us all rows. Simple and Easy Stuff!
In the next articles , we will practice SQL statements and create a relational SQL database because we will need a database to practice all our queries. So Stay tuned , Subscribe Us and share this article with your friends. We hope this tutorial was satisfactory for you.
Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows !
Happy Reading and Learning!