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.
Jump to Section
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:
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.
Step 3: 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.
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.
To execute a program press the “F5” key. Once you create a database in SSMS, you will get a pop-up message below as;
These messages show that your 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.
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 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.
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 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;
How to Insert Records in a Table
You can simply insert records with the help of the ‘SELECT’ command as;
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.
Then, run a delete query then you will get an output as:
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:
Output: After the execution of query command.
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
- 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.
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.
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:
- Right-click the tblPerson table and select Design.
- Press “Right-click” on the GenderId column in the design window and select Relationships for it.
- Press on “Add button” in the window of Foreign Key Relationships.
- Now expand, in the Tables and Column Specification row by clicking the + sign.
- Press the square button, which presents the Tables and Column specification row.
- Drop as downlist, select tblGender in the primary key table.
- Click on the row below, and select the 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 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.
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;
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.
- Difference Between SQL and MySQL - April 14, 2020
- How to work with Subquery in Data Mining - March 23, 2018
- How to use browser features of Javascript? - March 9, 2018