How to connect SQL Server and Advanced features

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 lots of people faced so many trouble to start to connect to SQL database, now let’s take a quick start with the following details;

First, open your SQL Server Management Studio.

Step 1: In the given Connect to Server dialog box, enter the following details:

Information to enter in the Connect to Server dialog box

Step 2: Click on Options in the Connect to Server dialog box. In the Connect to database section, enter your mySampleDatabase to connect to this database.

In the Connect to database section, enter your mySampleDatabase to connect to this database.

Step 3: Then, click on the button Connect. The Object Explorer window opens in SSMS.

Then, click on the 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.

Open your Object Explorer and expand as Databases

How to Create a Database

A database is a name where you can store multiples tables in it. There are two options for creating a database either manually by creating a query or from ‘Object Explorer’.

Option 1: By using query commands.

CREATE DATABASE NEW_EMPLOYEE;

To execute a program press the “F5” key. Once you create a database in SSMS, you will get a pop-up message below as;

Execute CREATE DATABASE NEW_EMPLOYEE;

These messages show that your database “New_Employee” has been stored with a given name in object explorer.

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 the database folder in ‘Object Explorer. Then, press ‘Right Click’ on a database.

Move your cursor to the 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;

Select as ‘New Database’ then later a window will pop-up

Now, put your database name in front of ‘Database name’ as per your project requirements. Then, click on the 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 it from the database. Once you drop the database, you can‘t do the Rollback command.

Once you drop the database, you can‘t do the 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;

To create a table give a unique table name and its data types

The column name PersonID is an integer type and it holds an integer value FirstName, LastName, Address, and City columns. The names of these values are of varchar type and these will hold characters. The maximum length for these fields consists of 255 characters.

The empty table “Persons” will look like this;

The empty table “Persons”

How to Insert Records in a Table

You can simply insert records with the help of the ‘SELECT’ command as;

Insert records with the help of the ‘SELECT’ command

You can easily insert any type of data in a table but there must be a logical data type of your record. There are two ways of insertion of records one of them is creating your own queries like the “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 a Table

When you insert records in the table now you need to delete records, you can use the delete command. With it, you can delete any specific records from the table. Delete command is only usable with the ‘WHERE’ clause condition.

We have shown two records in our table. Now, I want to remove the second row from our table.

How to remove a row in a table

Then, run a delete query then you will get an output as:

The output of running a delete command

How to Update Records in Table

The UPDATE command is very useful for the modification of specific data in a table. This command is only working with the ‘SET’ 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 the table as

Slide 1:

How to update entries

Output: After the execution of query command.

Result of executing the updating command

How to Differentiate SQL from MySQL

How to Differentiate SQL from MySQL

Nowadays, People are getting confused about the difference between SQL and MySQL. Let’s have a look at a clear difference 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 with 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 not 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

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 that 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. On the other side, PL/SQL executes whole blocks of 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 Between Truncate, Delete, and Drop

Below are the differences between Truncate, Delete, and Drop.

Truncate

  • A term of Data Definition Language (DDL) command.
  • Is executed with a table lock where a complete table is locked to remove all records.
  • You can’t use the ‘Where’ clause with the Truncate command.
  • Completely removes all rows from a table.
  • Deletes the data by deallocating the data pages, which are used to store the table data in the storage.
  • When you use the Truncate command on a table you need to go with ALTER permission on the table.
  • This syntax: Truncate TABLE table_name;
  • This query consists of less transaction space than the Delete statement.
  • This command can’t be used with indexed views.

Delete

  • This command is a data manipulation language (DML) command.
  • Delete is executed with a row lock; each row in the table contains a locked space for deletion.
  • We can use the ‘where’ clause with the delete command to filter & delete specific records from the table.
  • This command is always used to delete rows from a table based on the where condition.
  • The delete command is slightly slower than truncate while maintaining the log.
  • This statement deletes rows one at a single given time and records an entry in the transaction log for each deleted row.
  • When you use the delete command, you need to delete permission on the table.
  • This Syntax: delete from table_name [where condition];
  • The delete command contains much transaction space as compared to the truncate command.
  • This command can be used with indexed views.

DROP

  • 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 the drop command.
  • No rolled back commands will exist.
  • The delete command can be rolled back (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.

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 the Object Explorer window.
2. Select New Table
3. Complete Column Name, Data Type and Allow Nulls, then, save the table as tblPerson.

Create tblPerson table, graphically, using SQL Server Management Studio

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. The primary key does not allow nulls.
Create Table tblGender
(ID int Not Null Primary Key,
Gender nvarchar(50))

In the 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:

  1. Right-click the tblPerson table and select Design.
  2. Press “Right-click” on the GenderId column in the design window and select Relationships for it.
  3. Press on “Add button” in the window of Foreign Key Relationships.
  4. Now expand, in the Tables and Column Specification row by clicking the + sign.
  5. Press the square button, which presents the Tables and Column specification row.
  6. Drop as downlist, select tblGender in the primary key table.
  7. Click on the row below, and select the ID column.
  8. From the column on the right-hand side, select GenderId.
  9. Click OK and then click close.
  10. 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 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 duplicates in your records, you want to remove duplicate data from the table. This is one of the important questions where an interviewer asks you in an interview. There are several ways to delete duplicate records from the table.

Deletion of duplicate records with Common Table Expressions (CTE)

CTE, which defines a temporary named result data set.

Deletion of duplicate records with Common Table Expressions (CTE)

During the execution, a column name ‘rownumber’ contains some value, there is more than 1 value which shows the duplicate result, to remove this run a query below as;

The final result after deleting duplicate records from the table

Now you will see the final result after deleting duplicate records from the table.

Conclusion

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 the INSERT command.

SQL is a domain language that 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.

Follow me!
Latest posts by Imran Saifi (see all)

Leave a Comment