SELECT StudentNO, FName, LName, TO_CHAR(DOB,'DD-MON-YYYY') AS DOB, FeePaid, TO_CHAR(LastFeeDate,'DD-MON-YYYY') AS LastFeeDate FROM STUDENT ORDER BY StudentNO;
javascript
ALTER TABLE STUDENT ADD (StreetAddress VARCHAR2(70), Suburb VARCHAR2(40));
javascript
SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'STUDENT' ORDER BY COLUMN_ID;
javascript
ALTER TABLE STUDENT DROP (CiTTy);
javascript
ALTER TABLE STUDENT ADD (City CHAR(40));
javascript
ALTER TABLE STUDENT MODIFY (City VARCHAR2(40));
javascript
SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'STUDENT' ORDER BY COLUMN_ID;
javascript
UPDATE STUDENT SET StreetAddress = '12 New St' WHERE StudentNo = 30001;
javascript
SELECT StudentNo, FName, LName, StreetAddress, City FROM STUDENT ORDER BY StudentNo;
javascript
-- 故意合在一起试验：大概率报错 ALTER TABLE STUDENT ADD (Email VARCHAR2(60)) DROP (PostCode);
javascript
COMMIT
javascript
CREATE TABLE SUBJECT AS SELECT * FROM dtaniar.SUBJECT; CREATE TABLE LECTURER AS SELECT * FROM dtaniar.LECTURER; CREATE TABLE LECTURE AS SELECT * FROM dtaniar.LECTURE; CREATE TABLE STUDENT AS SELECT * FROM dtaniar.STUDENT; CREATE TABLE STUDENT_ENROLMENT AS SELECT * FROM dtaniar.STUDENT_ENROLMENT; CREATE TABLE TUTOR AS SELECT * FROM dtaniar.TUTOR; CREATE TABLE LAB AS SELECT * FROM dtaniar.LAB; CREATE TABLE LAB_SIGNUP AS SELECT * FROM dtaniar.LAB_SIGNUP;
javascript
SELECT table_name FROM user_tables ORDER BY table_name;
javascript
SELECT l.StaffNo, l.FName || ' ' || l.LName AS lecturer_name, s.SubjectCode, s.Name AS subject_name, le.LectDay, TO_CHAR(le.LectTime, 'HH24:MI') AS lect_time, le.Venue FROM LECTURER l LEFT JOIN LECTURE le ON le.StaffNo = l.StaffNo LEFT JOIN SUBJECT s ON s.SubjectCode = le.SubjectCode ORDER BY l.LName, l.FName, le.LectDay, le.LectTime;