SQL Queries such as SQL Select and SQL Statements with syntax and examples . How to define projection and selection on tables to compose the query. 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) FROM table-name
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 Queries
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 FROM STUDENT WHERE Student_ID=2;
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.