Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

TABLE CREATION AND DATA MANIPULATION COMMANDS

No description
by

Komal Kapadi

on 2 February 2015

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of TABLE CREATION AND DATA MANIPULATION COMMANDS

CREATE <table name>
Creating table from existing table
CREATE <table name> AS
(query);

Viewing table structure
DESCRIBE <tablename>;
TABLE
INSERTING data into table
INSERT INTO COMMAND
INSERT INTO <table name> [<column list>]
VALUES (<value list>);


INSERTING DATES

‘YYYY-MM-DD’ format
INSERT INTO <table name> [<column list>]
VALUES (<value1, value2, ‘YYYY-MM-DD’, value3>..);

INSERTING NULL VALUES
INSERT INTO <table name> [<column list>]
VALUES (<value1, value2, NULL, value3>..);

OPENING DATABASES
USE myDB;


SHOW DATABASES;
CREATING DATABASE
CREATE DATABASE myDB;

CREATE DATABASE IF NOT EXIXTS myDB;
CREATE COMMAND
CHANGING DATA with DML commands
DATABASES
TABLE CREATION AND DATA MANIPULATION COMMANDS
MODIFYING DATA WITH ALTER COMMAND
Add a column
ALTER TABLE <table name>
ADD (<column name><data type><size><constraint>);
Removing table components
ALTER TABLE <tablename>
DROP COLUMN <column name>;

ALTER TABLE <tablename>
DROP <constrain name>;

e.g. ALTER TABLE student
DROP PRIMARY KEY,
DROP FOREIGN KEY fk_1;
Modifying column definitions
*
DATATYPE AND SIZE
ALTER TABLE <tablename>
MODIFY (columnname newdatatype (newsize) [First|After column];
Changing column name
ALTER TABLE
CHANGE COLUMN <oldcolumnname> <newcolumnname> <columndefination>;
*DEFAULT VALUES
*INTEGRITY CONSTRAINTS
*ORDER OF COLUMN
DROP TABLE Command
DROP TABLE [IF EXISTS] <tablename>

DROP TABLE <tablename>
Changing data with DDL commands
ALTER TABLE <tablename>
ADD PRIMARY KEY(column name);
Different Constrains
CREATE TABLE STUDENT
(roll_no integer
UNIQUE
name char(20)
class integer
div char(10));
1. Unique Constraint
CREATE TABLE STUDENT
(roll_no integer
name char(20)
class integer
div char(10)
marks integer
CHECK
(marks>0));
CREATE TABLE STUDENT
(roll_no integer
name char(20)
class integer
div char(10)
DEFAULT
'garnet');
CREATE TABLE STUDENT
(roll_no integer PRIMARY KEY,
name char(20),
class integer,
div char(10));
CREATE TABLE Garnet
(roll_no integer
REFRENCES
Maroon roll_no,

name char(20)
class integer
div char(10));
2. Check Constraint
3. Default Constraint
4. Primary key Constraint
5. Foreign key Constraint
Full transcript