Copyright

SQL DROP View: Tutorial & Overview

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

The SQL DROP command may be one that asks for caution, since it removes data. However, dropping a View does not delete the source data. This lesson will cover the DROP View command, providing an overview and examples.

DROP View

Anytime you DROP anything using the SQL DROP command, you should feel a little nervous tension. After all, something is being removed (possibly permanently) from the database! However, with by dropping a view, the original data is unaffected. This is why the View is such a great tool for end users in the first place: the table behind the scenes is safe.

Although you can use the DROP command to drop/delete an entire table or column of data, it's usually not wise to remove a table full of data! However, if you create Views of the data in the database, it's a lot easier to drop the View and start over should you run into snags or glitches.

That is why a view is a great option for end users, and dropping a view (instead of a table) is much less frightening.

The SQL View

Let's quick review the View. Here is the basic syntax for creating a view from an Employee table:


CREATE VIEW Employee_View AS
  SELECT empID, empFullName, empJobTitle
  FROM tblEmployee
  WHERE empID > 0;


We'll be using this Employee_View as our sample going forward.

DROP VIEW Syntax

The syntax for dropping a view is:


DROP VIEW name_of_view;


Let's apply that to our Employee table:


DROP VIEW Employee_View;


Note the semicolon at the end of the line: it is required in SQL syntax. It tells the database application that the command is complete. Leaving it out will cause errors or strange system behavior.

Now that we have deleted the view, if we try to write a query using that view, the system will return an error. How can we make sure that the view is even in the database before we drop it? Most database management systems (MySQL, Oracle, and so on) provide an additional statement to check if the view exists.

IF EXISTS

Use IF EXISTS in the statement to ensure that you aren't trying to drop a view that isn't in the database.


DROP VIEW IF EXISTS
  Employee_View;


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support