Oracle Learning Notes

--oracle querying the current version
SELECT * from V$version;
----------Oracle Query server-side encoding----------
SELECT * from v$nls_parameters where parameter= ' nls_characterset ';

----------Three ways Oracle queries the current time----------
Select Sysdate from dual;
--2016/8/5 21:47:44
Select current_date from dual;
--2016/8/5 21:48:42
Select Systimestamp from dual;
--May-August-16 09.48.23.880000 pm +08:00

DESC Stuinfo;
Select name from V$database;
SELECT * from V$database;

SELECT * from User_tables;
SELECT * from User_tablespaces;
Select Index_name from User_indexes;
Select Database from User_datapump_jobs;
/* Create student Information table */
CREATE Table Stuinfo (
Stuname varchar2 () not NULL,
Stuno Char (6) Not NULL,
Stuage number (3,0) default 0,
Stuid Numeric (18,0),
Stuseat NUMERIC (2,0)
)

--Create Student score table
CREATE TABLE Stumarks
(
Examno CHAR (7) Not NULL,
Stuno CHAR (6) Not NULl,
Writtenexam NUMERIC (3,0),
Labexam Numeric (3,0)
);
--Delete Constraint
ALTER TABLE Stuinfo
Drop constraint ID;

--Increase the PRIMARY KEY constraint
ALTER TABLE Stuinfo
Add constraint Pk_stuno primary key (Stuno);

ALTER TABLE Stumarks
Add constraint Pk_examno primary key (EXAMNO);
--Add UNIQUE constraints
ALTER TABLE Stuinfo
Add constraint Uq_stuid UNIQUE (STUID);
--Adding CHECK constraint checks
ALTER TABLE Stuinfo
Add constraint ck_stuage Check (stuage between and 40);
--Add a foreign key index
ALTER TABLE Stumarks
Add constraint Fk_stuno foreign key (Stuno) references Stuinfo (Stuno);

--View Table space
SELECT * FROM User_tablespaces

--Modify the type of the field
ALTER TABLE Stuinfo Modify (stuaddress number (20));
--Query the index of all tables of the user
SELECT * FROM User_indexes

--Modify column names
ALTER TABLE stuinfo Rename column stuadress to stuaddress;

--Modify Table name
ALTER TABLE username. TEST1 RENAME to Stuinfo

Oracle Learning Notes