DataBase.txt - CREATE TABLE Student StudentId INTEGER ...

CREATE TABLE Student(
StudentId INTEGER,
FName VARCHAR2(20),
LName VARCHAR2(20),
DOJ DATE,
Email VARCHAR2(50),
Phone VARCHAR2(12)
);
DESCRIBE Student;
SELECT * FROM user_tables;
INSERT INTO Student VALUES (1001, 'Rachel', 'Green', '01-Jan-2001',
'[email protected]','123-456-7890');
INSERT INTO Student VALUES (1002, 'Monica', 'Geller', '01-Feb-2001',
'[email protected]','123-456-7890');
INSERT INTO Student VALUES (1003, 'Phoebe', 'Buffay', '01-Mar-2001',
'[email protected]','123-456-7890');
INSERT INTO Student VALUES (1004, 'Joey', 'Tribbiani', '01-Apr-2001',
'[email protected]','123-456-7890');
INSERT INTO Student VALUES (1005, 'Chandler', 'Bing', '01-May-2001',
'[email protected]','123-456-7890');
INSERT INTO Student VALUES (1006, 'Ross', 'Geller', '01-Jun-2001',
'[email protected]','123-456-7890');
SELECT * FROM Student;
DROP TABLE Student;
INSERT INTO Student(Student ID, LName) VALUES (1008, 'Potato'); must be in same
order
CREATE TABLE Student(
StudentId INTEGER Constraint nn_studID NOT NULL,
FName VARCHAR2(20) Default 'FNU',
LName VARCHAR2(20) NOT Null,
DOJ DATE Default SysDate,
Email VARCHAR2(50),
Phone VARCHAR2(12)
);
Insert Into Student ( StudentID, FName, LName) Values ( 1009, 'Jacob', 'Smith');
//Primary Key w/
CREATE TABLE Student(
StudentID INTEGER CONSTRAINT pk_studID PRIMARY KEY,
FName VARCHAR2(20) DEFAULT 'FNU',
LName VARCHAR2(20) NOT NULL,
DOJ DATE DEFAULT SYSDATE,
Email VARCHAR2(50),
Phone VARCHAR2(12)
);
//Checking
CREATE TABLE Student (
StudentId INTEGER,
FName VARCHAR2(10),
Gender CHAR(1) CONSTRAINT Stud_gender_ck1 CHECK(Gender IN('M', 'F')));
//Gender
CREATE TABLE Student(
StudentID INTEGER CONSTRAINT pk_studID PRIMARY KEY,

FName VARCHAR2(20) DEFAULT 'FNU',
LName VARCHAR2(20) NOT NULL,
Gender CHAR(1) CONSTRAINT chk_gender CHECK(Gender IN ('M','F','O')),
DOJ DATE DEFAULT SYSDATE,
Email VARCHAR2(50),
Phone VARCHAR2(12)
);
//Email w/ Gender and Date
CREATE TABLE Student(
StudentID INTEGER CONSTRAINT pk_studID PRIMARY KEY,
FName VARCHAR2(20) DEFAULT 'FNU',
LName VARCHAR2(20) NOT NULL,
Gender CHAR(1) CONSTRAINT chk_gender CHECK(Gender IN ('M','F','O')),
DOJ DATE DEFAULT SYSDATE CHECK(DOJ <= '01-Jan-2022'),
Email VARCHAR2(50) CHECK(Email LIKE '%@%.%'),
Phone VARCHAR2(12)
);
//checks everything
CREATE TABLE Student(
StudentId INTEGER CONSTRAINT pk_studid PRIMARY KEY,
FName VARCHAR2(20) DEFAULT 'FNU',
LName VARCHAR2(20) NOT NULL,
Gender CHAR(1) CONSTRAINT chk_gender CHECK(Gender IN ('M','F','O')),
DOJ DATE DEFAULT SYSDATE CONSTRAINT chk_doj CHECK(DOJ <= '01-Jan-
2022'),
Email VARCHAR2(50) CONSTRAINT chk_email CHECK(Email LIKE '%@%.%'),
Phone VARCHAR2(12) CONSTRAINT check_phone CHECK(Phone LIKE'___-___-
____')
);
//added Birth
CREATE TABLE Student(
StudentId INTEGER CONSTRAINT pk_studid PRIMARY KEY,
FName VARCHAR2(20) DEFAULT 'FNU',
LName VARCHAR2(20) NOT NULL,
Gender CHAR(1) CONSTRAINT chk_gender CHECK(Gender IN ('M','F','O')),
DOJ DATE DEFAULT SYSDATE CONSTRAINT chk_doj CHECK(DOJ <= '01-Jan-
2022'),
DOB DATE,
Email VARCHAR2(50) CONSTRAINT chk_email CHECK(Email LIKE '%@%.%'),
Phone VARCHAR2(12) CONSTRAINT check_phone CHECK(Phone LIKE'___-___-
____'),
CONTRAINT chk_valid_date CHECK(DOB < DOJ)
);
// creation of new tables
CREATE TABLE Course(
CourseId INTEGER CONSTRAINT pk_courseid PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL);
CREATE TABLE Marks(
CourseId INTEGER CONSTRAINT fk_cid REFERENCES Course(CourseId),
StudentId INTEGER CONSTRAINT fk_sid REFERENCES Student(StudentId),
Score NUMBER(5,2) NOT NULL);
//Altering The Table at the Column Level or a the Table Level
Add New Column: ALTER TABLE Student ADD Address VARCHAR2(20);
Change DataType: ALTER TABLE Student MODIFY Address VARCHAR2(50);