How to connect SQL Server and Advanced features
Microsoft SQL Server
SQL stands for Structured Query Language which is used for storing, manipulating and retrieving data in database storage. SQL launched its extended version SQL: 2016 in December.
How to connect database using SSMS
We have seen lot of people faced so many trouble to start to connect to SQL database, now let’s take a quick start with following details;
First, Open your SQL Server Management Studio.
Step 1: In the given Connect to Server dialog box, enter the following details:
Step 2: Click on Options in the Connect to Server dialog box. At Connect to database section, enter your mySampleDatabase to connect to this database.
Step 3: Then, click on button Connect. The Object Explorer window opens in SSMS.
Open your Object Explorer and expand as Databases and thereafter explore mySampleDatabase to view the objects in the folder name as a sample database.
How to create a Database
A database is a name where you can store multiples tables in it. There are two options for creating database either manually by creating a query or from ‘Object Explorer’.
Option 1: By using query commands.
To execute a program press “F5” key. Once you create database in SSMS, you will get a pop-up message below as;
These messages show that you database “New_Employee” has been stored with a given name in object explorer.
Option 2: You can create a database without creating your query. First, move your cursor to database folder in ‘Object Explorer’. Then, press ‘Right Click’ on a database.
Now, Select as ‘New Database’ then later a window will pop-up as;
Now, put your database name in front of ‘Database name’ as per your project requirements. Then, click on button ‘OK’. It will automatically execute your database in SQL server.
How to DROP a database
When you no longer need your database you can drop from the database. Once you drop database you can‘t do with Rollback command.
How to create a Table
A table in the database is a collection of records where you can create, insert, update, and delete data in a table.
Create Table: To create a table give a unique table name and its data types by this;
The column name PersonID is integer type and it holds an integer value, FirstName, LastName, Address, and City columns names are of varchar type and these will hold characters, and the maximum length for these fields consist 255 characters.
The empty table “Persons” will look like this;
How to insert records in table
You can simply insert records with the help of ‘SELECT’ command as;
You can easily insert any type of data in a table but there must be logical data type of your record. There are two ways of insertion of records one of them is creating your own queries like “SELECT” command and another one where you can go to the data and create your table and its records as we have shown you above.
How to delete records in table
When you insert records in the table now you need to delete records, you can use delete command. By Delete command, you can delete any specific records from the table. Delete command is only usable with ‘WHERE’ clause condition as.
We have shown two records in our table. Now, I want to remove the second row from our table.
Then, run a delete query then you will get an output as:
How to update records in table
UPDATE command is very useful for modification of specific data in a table. UPDATE command is only working with ‘SET’ clause and ‘WHERE’ clause condition. When you update your records in a table about one thing you must be very clear, always select the unique record with WHERE clause else it will update all records in the table.
Example: I want to update “LastName” as name “Ranbir” instead of “Kapoor” in table as
Output: After execution of query command.
How to differentiate SQL from MySQL
Nowadays, People are getting so trouble about the difference between SQL and MySQL. Let’s have a look at a clear difference in between them as follows;
- SQL is defined to keep data maintain, manipulate and update the data in the database where on the other side MySQL is an open source Relational Database Management System (RDBMS), like SQL Server, Oracle, Informix, and Postgres etc.
- MySQL is very popular web applications for storing and logging user data; SQL is a standard language for multiple components including, but not limited to, SQL Framework, SQL/CLI, and SQL/XML.
- SQL engines make configurations for MySQL and that allows the developer to perform very different design and programming. In MSSQL, you have to create a database and don’t specify different engines in the database.
- You have more options for Windows and Linux projects, but MySQL works with PHP and MSSQL is mainly used with.NET.
- Both platforms contain IDE tools, but you need to understand the right tool with the right server. MSSQL uses Management Studio and MySQL has Enterprise Manager.
How to differentiate SQL from PL/SQL
- SQL is a structured query language for manipulation, addition, and retrieval of data modification where PL/SQL is a relational database management system. PL/SQL is a Procedural language which is an extension of SQL. PL/SQL holds the SQL statements within its syntax in the stored database.
- SQL only executes the query at a single time where on the other side, PL/SQL executes whole block code at a single time. Also, PL/SQL used to create web applications where the displayed information is collected from SQL in a proper format.
- In SQL, you can only write queries and commands by using DDL, DML statements whereas, In PL/SQL you can write the whole block of code that involves procedures, functions, packages, and variables.
- You can embed SQL queries within the syntax of PL/SQL. However, the opposite is not possible.
How to differentiate in between Truncate, Delete and Drop
- This is a term of Data Definition Language (DDL) command.
- This is executed with a table lock where a complete table is locked to remove all records.
- You can’t use ‘Where’ clause with Truncate command.
- This completely removes all rows from a table.
- It deletes the data by deallocating the data pages, which is used to store the table data in the storage.
- When you use Truncate command on a table you need to go with ALTER permission on the table.
- This syntax: Truncate TABLE table_name;
- This query consists less transaction space than Delete statement.
- This command can’t be used with indexed views.
- This command is a data manipulation language (DML) command.
- delete is executes with a row lock; each row in the table contains locked space for deletion.
- we can use ‘where’ clause with delete command to filter & delete specific records from the table.
- This command is always used to delete rows from a table based on where condition.
- This command is slightly slower than truncate while maintaining the log.
- This statement deletes rows one at single given time and records an entry in the transaction log for each deleted row.
- when you use delete command, you need to delete permission on the table.
- This Syntax: delete from table_name [where condition];
- This command contains much transaction space as compared to truncate command.
- This command can be used with indexed views.
- DROP is also a DDL command.
- The DROP command removes a complete table from the stored database.
- Table rows, indexes, and privileges also are removed from the database.
- DROP syntax: DROP TABLE table_name;
- No DML triggers will be fired in drop command.
- No rolled back commands will exist.
- delete command can be rollback (Undo), on the other side drop and truncate commands cannot be rolled back.
Primary Key VS Foreign Key
This is to create table names for example as tblPerson and tblGender tables, establish the primary key and foreign key constraints. In SQL Server, tables can be created graphically using SQL Server Management Studio (SSMS) or using a query.
To create tblPerson table, graphically, using SQL Server Management Studio
1. First, Press “Right click” on the Tables folder in Object Explorer window.
2. Select New Table
3. Complete Column Name, Data Type and Allow Nulls, then, save the table as tblPerson.
A single statement that creates tblGender table, with ID and Gender columns. The ID column is the primary key column. The primary key identifies each row in a table uniquely. Primary key does not allow nulls.
Create Table tblGender
(ID int Not Null Primary Key,
In tblPerson table, GenderID is the foreign key referencing ID column in tblGendertable. Foreign key adds its references graphically using SSMS or using a query.
To graphically add a foreign key reference:
- Right-click tblPerson table and select Design.
- Press “Right click” on GenderId column in design window and select Relationships for it.
- Press on “Add button” in the window of Foreign Key Relationships.
- Now expand, in Tables and Column Specification row, by clicking the, + sign.
- Press the square button, which presents Tables and Column specification row.
- Drop as downlist, select tblGender in the primary key table.
- Click on the row below, and select ID column.
- From the column on the right-hand side, select GenderId.
- Click OK and then click close.
- Finally, save the table.
In the reference of foreign key:
Alter table tblPerson
add constraint tblPerson_GenderId_FK Foreign key (GenderId) reference tblGender(ID)
The general formula is :
Alter table ForeignKeyTable add constraint ForeignKeyTable_ForiegnKeyColumn_FK
Foreign key (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn).
A foreign key which defines database integrity. This key prevents wrong data from being insert into the foreign key column. Single values that you enter into the foreign key column.
How to delete duplicates from Table
Whenever you face duplicity in your records and you want to remove duplicate data from the table. This is one of the important questions where interviewer ask from you in an interview. There are several ways to delete duplicate records from the table as.
Deletion of duplicate records with Common Table Expressions (CTE)
CTE, which defines a temporary named result data set.
During the execution, a column name ‘rownumber’ contains some value, there is more than 1 value which shows duplicate result, to remove this run a query below as;
Now you will see the final result after deleting of duplicate records from the table.
The breadth and scope of the SQL commands provide the capability to create and manipulate a wide variety of database objects using the various CREATE, ALTER, and DROP commands. Database objects load data with INSERT command. SQL is domain language which can be used in the programming language. The data can be manipulated using a wide variety of commands, such as select, delete, and truncate, as well as the cursor commands, DECLARE, OPEN, FETCH, and CLOSE. SQL Server is a solid and great technology. It leads you straight up to Azure, Azure SQL Database, and Azure SQL Data Warehouse, where the future is.