Sql Database | Drop database statement in sql - sql - sql tutorial - learn sql



  • To use DROP DATABASE , you need the DROP privilege on the database.
  • DROPSCHEMA is a synonym for DROP DATABASE .
  • When a database is dropped, user privileges on the database are not automatically dropped.
  • Sometimes we may decide that we need to delete of an entire database in the RDBMS.
  • In fact, if we cannot do so, we would be faced with a maintenance nightmare.
  • Fortunately, SQL allows us to do it, as we can use the DROP DATABASE command.

Syntax

DROP DATABASE "database_name";
  • So, if we wanted to drop the HOLIDAYS database that we created in the CREATE DATABASE section, we simply type
     DROP DATABASE HOLIDAYS;
  • Please note that any table that is still in the database will also disappear once the database is dropped.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

DROP STATEMENT CAUTION:

  • DROP is a powerful statement. A backup of data for a database to be deleted or drop is the best quick cheap recovery solution. So be smart with this statement!
  • To use DROP DATABASE, one needs to have the DROP privilege on the database.

Examples

A. Dropping a single database

  • The following example removes the Sales database.
DROP DATABASE Sales;  

B. Dropping multiple databases

  • Applies to: SQL Server 2008 through SQL Server 2017.
  • The following example removes each of the listed databases.
DROP DATABASE Sales, NewSales;  

C. Dropping a database snapshot

  • Applies to: SQL Server 2008 through SQL Server 2017.
  • The following example removes a database snapshot, named sales_snapshot0600, without affecting the source database.
DROP DATABASE sales_snapshot0600;  

Example:

create database first_db;
use first_db;
create table t1 (x int);

create database second_db;
use second_db;
  • Each database has its own namespace for tables.
  • You can reuse the same table names in each database.
create table t1 (s string);

create database temp; 

  • You can either USE a database after creating it,
  • or qualify all references to the table name with the name of the database.
  • Here, tables T2 and T3 are both created in the TEMP database.
create table temp.t2 (x int, y int);
use database temp;
create table t3 (s string);
  • You cannot drop a database while it is selected by the USE statement.
drop database temp;
  • ERROR: Analysis Exception: Cannot drop current default database: temp
    • The always-available database 'default' is a convenient one to USE
    • before dropping a database you created.
use default;
  • Before dropping a database, first drop all the tables inside it, or in CDH 5.5 and higher use the CASCADE clause.
    • The always-available database 'default' is a convenient one to USE
    • before dropping a database you created.
drop database temp;
  • ERROR: Impala Runtime Exception: Error making 'drop Database' RPC to Hive Meta store:
  • CAUSED BY: Invalid Operation Exception: Database temp is not empty
show tables in temp;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Output

Name
t3
-- CDH 5.5 and higher:
drop database temp cascade;

-- CDH 5.4 and lower:
drop table temp.t3;
drop database temp;

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Difference Between SQL, MySQL and Oracle Drop Statement

Difference Between SQL MySQL and Oracle Drop Statement

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Sql database | Drop database statement in sql