Important SQL Queries. We will be discussing some important queries in this article with their examples because we have already set up the Adventureworks database. Before we start talking about the SQL Queries, we need to know some things about SSMS and how to use it.
Parts of SSMS
First of all, you need to log in to SSMS by Windows Authentication or SQL credentials. We will have three main parts in SSMS when you open it. See the screenshot below.
You can see on the very top, we have toolbars and a Menu. Below the toolbars, we have two portions.
On the left side, we have Object Explorer. On the right of Object Explorer, we have space to write SQL queries and below SQL queries, we have an output window that shows selected data and notifications of queries. If any query has syntax error or the query ran successfully. The notification will be shown here.
How to use SSMS
First of all, Click on the New Query button in the toolbars then we need to select the right database by typing the “use Adventureworks” and then highlight the query then press the Execute button.
Once you will click on Execute, you will see the message Command Completed Successfully. Just above the Object Explorer, you can see the name of the database that is selected. In this case, you should see Adventureworks. By default, the master database should be selected.
List of SQL Commands in the Article
- Select * from TableName
- CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype);
- DROP TABLE table_name;
- INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
- UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
- DROP DATABASE databasename;
Select Command in SQL
Select * from HumanResources.Department
The query above you see can select all the columns from HumanResources.Department table. The asterisk (*) sign means all in SQL. After from keyword, we need to mention the table name.
Selecting Few Columns from Table
Select Name,GroupName,DepartmentID from HumanResources.Department
In the above query, you can see, the selected columns Name, GroupName, DepartmentID are selected from the table. Please make sure to separate the column names by comma (,).
Create Table Command in SQL
CREATE TABLE Employee_Table ( SAPID int, Name varchar(20), Address varchar(30), );
The Create table command is used to create a table in the database. After creating the table command you need to mention the name of the table. Then in the brackets, the name of the columns followed by data type should be mentioned.
Every column that you want to add should be separated by comma (,). With the int columns, you don’t need to mention the length of the number but with varchar, you need to mention the length.
Drop Table Command in SQL
DROP TABLE Employee_Table;
The above command can be used to drop (delete) the table that we created above.
Insert Into command in SQL
insert into Employee_Table values ('123456','John', '123 New York')
Insert into command is used to insert the data into the SQL table. Just like I used it to fill the data into the Employee_Table table.
Update Command in SQL
UPDATE Employee_Table SET Name = 'Victor', SAPID = '22223' WHERE Name = 'John';
The update command is to update the filled data inside the table. We have used the command to update the data inside Employee_Table. After the Update Keyword Table name needs to be mentioned, then after the Set keyword.
After Set we need to give the values with its column names, for more than one column name, all the column names should be separated by comma (,). Where keyword is to tell the SSMS which record should be updated.
Drop Database in SQL
DROP DATABASE databasename;
Just like Drop table, the Drop database command can delete the database.
Note – Every keyword in the command that is mentioned here, it can be used with the specific order. The order can not be changed.
I hope the commands are helpful. Feel free to share any feedback in the comment.
Take Care Guys!