STRUCTURED QUERY LANGUAGE(SQL)

Created by: B Mokshagna Reddy (BMR)

SQL DATA DEFINITION AND DATATYPES

SQL Schema:

An SQL schema is identified by a schema name and includes an authorization identifier name to indicate user or account who owns the schema, as well as descriptor for each elements in the schema.

Schema creation with authorization:

CREATE SCHEMA DATABASE_NAME AUTHORIZATION IDENTIFIER;
Eg:

CREATE SCHEMA COMPANY AUTHORIZATION ‘JSMITH’;

---

Catalog:

Named collection of schemas.
Data Definition, Constraints, and Schema Changes Used to CREATE, DROP, and ALTER the descriptions of the tables (relations) of a database

---

Creating a Database

Syntax:

CREATE DATABASE database_name;

---

Creating a Table

Syntax

CREATE TABLE table_name
(Column_name datatype[(size)],
Column_name datatype[(size)],
);

Specifies a new base relation by giving it a name, and specifying each of its attributes and their data types (INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n))
A constraint NOT NULL may be specified on an attribute

CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) );

In SQL, can use the CREATE TABLE command for specifying the primary key attributes, secondary keys, and referential integrity constraints (foreign keys).
Key attributes can be specified via the PRIMARY KEY and UNIQUE phrases

CREATE TABLE DEPT (
DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP );

---

DROP TABLE

Used to remove a relation (base table) and its definition
The relation can no longer be used in queries, updates, or any other
commands since its description no longer exists
Example:

DROP TABLE DEPENDENT;

---

ALTER TABLE

Used to add an attribute to one of the base relations
The new attribute will have NULLs in all the tuples of the relation right after the command is executed; hence, the NOT NULL constraint is not allowed for such an attribute
Example:

ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);

The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple.
This can be done using the UPDATE command.

---

Know More at BMR EDUCATION