WEEK—6 Lecture-3 hr
DEFINING DATA:DDL
1. DATA DEFINITION LANGUAGE (CREATE, DROP, ALTER)
A database object is any defined object in a database that is used to store or reference data.
Anything which we make from create command is known as Database Object. It can be used to
hold and manipulate the data.
Some of the examples of database objects are: Table, view, sequence, indexes, etc.
• Table – Basic unit of storage; composed rows and columns
• View – Logically represents subsets of data from one or more tables
• Sequence – Generates primary key values
• Index – Improves the performance of some queries
• Synonym – Alternative name for an object
CREATE
Creates new databases, tables and views from DBMS.
For example −
Create database company;
Create table employee;
Create view works_on1;
The CREATE TABLE command is used to specify a new relation by giving it a name and
specifying its attributes and initial constraints. The attributes are specified first, and each attribute is
given a name, a data type to specify its domain of values, and any attribute constraints, such as
NOT NULL. The key, entity integrity, and referential integrity constraints can be specified within
the CREATE TABLE statement.
Syntax of create command as follows:
CREATE TABLE TABLENAME
( COLUMN1 DATATYPE(SIZE) [CONSTRAINT],
COLUMN2 DATATYPE(SIZE) [CONSTRAINT],
.
.
.
COLUMNN DATATYPE(SIZE) [CONSTRAINT]);
Ex: CREATE TABLE EMPLOYEE(FNAME VARCHAR(15), LNAME VARCHAR(15));
DROP
Drops commands, views, tables, and databases from RDBMS.
Drop object_type object_name;
For example−
Drop database company;
Drop table employee;
Drop view works_on1;
ALTER
Modifies database schema.
Alter object_type object_name parameters;
For example−
Alter table employee add email_id varchar;
This command adds an attribute in the relation employee with the name email_id of string type.
Alter table employee Drop address ;
This command removes an attribute address from relation employee.
Alter table employee Modify(MINIT char, MNAME char);
This command modifies the attribute name from MINIT to MNAME.
TEMPORARY TABLES:
A temporary table is a base table that is not stored in the database but instead exists only while the
database session in which it was created is active. At first glance, this may sound like a view, but
views and temporary tables are rather different
TYPES OF TEMPORARY TABLES:
There are two types of temporary tables:
➢ Local Temporary tables.
➢ Global Temporary tables.
➢
Local temporary tables are visible only to their creators during the same connection to an instance
of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted
after the user disconnects from the instance of SQL Server.
Global temporary tables are visible to any user and any connection after they are created, and are
deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Create and use(Temporary Tables)
CREATE TEMPORARY TABLE NEW_TABLE
SELECT * FROM ORIGINAL_TABLE
( IN MYSQL)
USE SCHOOLDB;
SELECT NAME, AGE, GENDER INTO #MALESTUDENTS
FROM STUDENT
WHERE GENDER = 'MALE'
(IN ORACLE)
External tables:
External table is a table in which the data is stored in files in an external stage. External tables store
file-level metadata about the data files, such as the filename, a version identifier and related
properties. This enables querying data stored in files in an external stage as if it were inside a
database. External tables can access data stored in any format supported by COPY INTO
<table> statements.
External tables are read-only, therefore no DML operations can be performed on them; however,
external tables can be used for query and join operations. Views can be created against external
tables.
Managing constraints
