Difference Between Delete and Drop in SQL

In this post you will understand the main differences between Delete and Drop in SQL. Both Delete and Drop are the commands to remove the elements from the database.

DELETE:

Delete is a Data Manipulation Language.The DELETE command is used to remove some or all rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

If WHERE clause is missing from the DELETE statement then by default all the tuples are removed from the relation, though the relation containing those tuples still exist in the schema. You can not delete an entire relation or domains or constraints using DELETE command.

The syntax of DELETE command is as follow:

DELETE FROM relation_name WHERE condition;

DROP:

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

he syntax of DROP command is as follow:

DROP SCHEMA schema_name RESTRICT;

DROP Table table_name ;


The Key Differences Between DELETE AND DROP:

1. DELETE command is used to remove some or all the tuples from the table. On the other hand, the DROP command is used to remove schema, table, domain or  Constraints from the database.
2. DELETE is a Data Manipulation Language command whereas, DROP is a Data Definition Language command.
3. DELETE can be used along with the WHERE clause but, DROP is not used along with any command.
4. Actions performed by the DELETE  command can be rollbacked, but not in the case of DROP command.
5. As DELETE  command do not delete the table hence, no space is freed whereas, DROP deletes the entire table frees the memory space.

What Skills Every SQL Developer Should have

In this post you will know what are the most valuable skills a SQL developer must have. Standard Query Language (SQL) is used by relational databases, including IBM's DB2, Oracle, Microsoft's SQL Server, and open source database MySQL.SQL was devised for manipulating data in relational database tables. SQL skills are applicable to any application or database development, support and administration role. Some of them being:

Security:

This should always be first. Understand SQL injection and parameterized queries.
Restoring a database. Notice how I did not say, "making a backup." That will obviously be a necessary step, but it's far more important to know how to restore, because otherwise your backups are for naught.

Query load monitoring: How do you identify which queries account for the greatest share of your database load?

Query optimization: Once you find the bad queries, improve them.

Creating indexes
Rewriting query logic
Partitioning or sharding
Denormalization
Using caches or message queues in your application

DBMS tuning: Most databases have many options to tune resource use. For example, allocating as much RAM as possible to cache data and indexes.

Capacity planning:

 Know the limits of your server platform, how many queries per second can it execute, how much IO do those queries demand of your disk array, how much space do you have for data, indexes, and logs. Measure how much of these resources you are using now, and how does that compare to the maximum. Is your resource usage increasing? How long will it be before you run out of capacity?

SQL types: Understand the characteristics of different SQL types as they pertain to logic, performance, and storage. I'll include SQL NULLs as part of this entry, although that could be a separate item itself.

Transactions: Understand the importance of grouping changes and committing them as an atomic unit of work. Understand isolation levels.
Set-based data operations. Many developers think of a database as a collection of individual rows, and fail to use powerful features of SQL to update sets of data in one query. Developers who use object-relational mapping (ORM) tools are especially guilty of this.

High availability: What happens if/when your database server shuts down, or needs to be upgraded? Does your application handle this gracefully? Can you queue data changes temporarily until the database is back online? Do you have a spare database instance for failover? How do you get the spare back in sync with the primary?

In simple terms the following skills must have every sql developer:

Learn the joins properly.Specially OUTER JOIN .

-> Get a clear view of Primary key and Foreign key.

-> ACID properties along with E-R diagram

-> Query optimization and performance tuning(Use of tools like explain plan,tkproof)

use of packge DBMS_PROFILER for performance issues.

->Normalization ( In data modelling its necessary) and Autonomous Transaction.

-> Use of Index,Materlized view(refreshing techniques)

-> Context Switching and Referential integrity.

-> Dynamic SQL

->use of functions like SUBSTR,INSTR,LEAD,LAG,CASE,DECODE,FLOOR.

-> Getting into named blocks like PROCEDURES/FUNCTIONS/TRIGGERS/CURSORS/COLLECTIONS/PACKAGES and know there use in a real life requiremnet. (In PLSQL only)

Difference Between View and Materialized View in SQL

In this post i am sharing difference between view and Managerial view in database or SQL. This is one of the popular interview question,it doesn't matter whether you are programmer,DBA,or backend developer. These SQL questions common for all. Now let us see what is view and materialized view.

View:

Views are virtual table only and run the query definition each time they are accessed. A view is also useful to hide sensitive data columns.The result is not stored any where in the disk and every time we need to fire the query when we need data.


Materialized view:

Materialized views are disk based and updated manually or using triggers, also the definition of the query will also store in the database.


Now let us see The key differences between View and Materialized view in SQL or Database:

• The view query result is never stored in the disk or database it is only displayed but Materialized view allow the  query result on the disk.
• .In case of view we always get latest data since view is updated each time the virtual table is used but Materialized view we need to refresh the view for getting latest data, Since it has to be updated manually.
• Performance of view is less but Materialized view is fast processing.
• view do not require memory space but Materialized view utilizes memory space.
• No separate copy of table but in case of Materialized view we get physically separate copy of table

All most all applications is used views,we use views because they are more feasible, only logical representation of table data no extra space needed.Materialized views are used mostly with data warehousing or business intelligence application.

Data Manipulation Language In SQL

Welcome Back to Software Engineers, In this article we will learn about Data Manipulation Language(DML) in SQL. By the end of this Article you will learn the fallowing things:

1. Describe each data manipulation language(DML) statement
2. insert rows into a table
3. update rows into a table
4. Delete rows from a table.

In This Article You will learn how to use DML statement in SQL , how to insert new rows in an existing Table,updating or modifying rows in existing table and how to delete rows from a table.

DML:  It stands for Data Manipulation Language. It is a core part of SQL.When you want add,update or delete data in the database, you execute a DML statement. A collection of DML statements that form a logical unit of work is called as Transaction

When DML Statement is executed:

• when you add new rows to a table
• when you modify existing rows in a table
• when you remove existing rows from a table

 Example:

                  Consider a banking database, when a bank customer transform money from a saving account to a checking account, by that time the transaction might consist of three operations:

• Decreasing the saving account
• Increasing the saving account
• Recording the Transaction

The database server must guarantee that all the three SQL statements are performed to maintain the proper balance.

Now we will learn DML statements one by one with examples,

a. Insert new rows into a table
b. Update rows into a table
c. Delete rows from a table

Insert new row(or)adding new row into a table:

This INSERT statement is used to insert (or) add new row(or) rows into a table.

Syntax:  insert into table[column [,column.....])] 
                                     values(value[,value......]);

From the above Syntax

table       :  name of the table
column  :  name of the column in the table
value      : corresponding value for the column

The fallowing is the employee table:

Suppose if we want  add new row by using insert statement  as fallows:

insert into employee(emp_id,ename,Dept_id,mgr_id,salary,job_id)
                          values(104,'rajesh',40,105,35000,'EX-DIR');

NOTE: you have to enclose character and date values within single quotation marks.

Inserting Special values:

You can use functions to enter special values in your table.

If you want insert Date,username,comments in the Report Table as below:

Then you need to write insert statement as fallow:

insert into Report(Date, user name, comments)
                          values(SYSDATE,'LUCKY','VERY GOOD');

NOTE:  if a date must be entered in a format other than the default format.
for example, with another century(or) specific time you must use the TO_DATE function.

 insert into Report(Date,username,comments)
                          values(to_date('15 OCT,2016'),'LUCKY','VERY GOOD');

 Result :  

Update rows in a table:

Update is used to modify the existing values in a table.

Syntax: update table column=value[,column=value,....]
                               [WHERE condition]

NOTE: you can update more than one row at a time(if required)

From the above syntax: 
table    :  name of the table
column: name of the column in the table
value: corresponding  value (or) sub query for the column
condition: identifies the rows to be updated and is composed of column names,expressions,constants,sub queries and comparison operators.

Examples:

1.  UPDATE employee SET Dept_id=50 WHERE emp_id=104;
2. UPDATE employee SET salary=35000 WHERE emp_id=104;

In general, use the primary key column in the WHERE clause to identify a single row for update. If you want update all rows in a table WHERE condition is not required.


Delete rows From a table:
It is used to remove existing rows from a table.

Syntax: Delete[from]<table name>
                            [Where condition];

Ex:  Delete from Employee WHERE emp_id in(101,102);

Note: if you omit where clause all rows in the table are deleted.

Stored Procedure in SQL Server with Example

Definition:

A Stored Procedure is nothing but already written SQL  queries that are stored in the database. If you are using same Queries  over and over then put those queries into the Stored Procedure.After put the SQL Statements in the Stored Procedure then you can run the Stored Procedure from the database commands Environment.(Here we are using SQL Server as Database)

   stored procedure can be defined  as set of logical group of SQL statements or pre compiled one or more SQL Statements  to perform a specific task.

Advantages of Stored Procedure:

• The main Benefit of Stored Procedure,it saves the time since stored procedure are executed on the server side and perform set of actions before returning  the results to client side.
• By using Stored Procedure method we can reduce the network traffic because it allows  set of actions  to be executed(if we send SQL Query which is executing again and again  to the server through network ,the network might be disconnected ,therefore the expecting result does not return to client side.
• It improves the performance of the database(since set of actions executed at once)
• SQL Statements needs to be compiled every time whenever it sent for execution whereas Stored Procedure required only one compilation when stored procedure is created.

How to create Stored Procedure in SQL Server

Firstly we need to create a table called emp_details

CREATE TABLE emp_details
(
[emp_id] [int] Identity (1,1) NOT NULL,
[FirstName][nvchar](300) NOT NULL,
[LastName] [nvchar](300)  NULL,
[Emali] [nvchar](100) NULL
)

Here i am going to insert the data for the above structure of table:

insert into emp_details(FirstName,LastName,Email)values('lucky','raju','lucky&xyz.com')

insert into emp_details(FirstName,LastName,Email)values('akhil','rao','akki&xyz.com')

insert into emp_details(FirstName,LastName,Email)values('naga','raju','nag&xyz.com')

insert into emp_details(FirstName,LastName,Email)values('krishna','raju','krish&xyz.com')


Now we are doing in the first step creating Stored Procedure 

Syntax:

Create Procedure procedure-name
(
Input parameters,
Output Parameters(if need)
)
As
Begin
Sql statement is used in Stored Procedure
End

Here if you need to create Stored Procedure which you want return empname  whose emp_id is given as Input Parameter to the stored Procedure then it will look like as below

Example 1:

Create Procedure GetEmpname //GetEmpname is name of the Stored Procedure
{
@emp_id int // input parameter emp_id of the employee details table
)
As
Begin
Select firstName +'   '+LastName from emp_details WHERE Emp_id=@Emp_id;
End


Example 2:

Stored Procedure can be used to insert ,update or delete a SQL Statement. Suppose you want insert a value into the Emp_details table.

create procedure InsertEmprecord
(
@Empfirstname varchar(100),
@EmpLastname varchar(100),
@EmpEmail varchar(50)
As
Begin
Insert into emp_details(FirstName,LastName,Email)values('@Empfirstname',@EmpLastname,@EmpEmail)
End

Finally we will see how to execute Stored Procedure in SQL Server:

To Execute Stored Procedure in SQL Server we use "Exec" (or) "Execute" Keyword.

Suppose if you want execute a Stored Procedure name as " GetEmpname "then we have to write command as below
Exec GetEmpname
OR
Execute GetEmpname


Conclusion:

If same SQL Query is required to execute again and again so it takes lot of network traffic to sent information to the server.If you create Stored procedure it reduce the network traffic by reducing the amount of information sent over the network because all pre compiled one or more SQL Statements put into the Stored Procedure..


Also check the post: SQL Queries interview Questions and Answers for Freshers