************************************************************************00000199
-- SELECT CURRENT_TIMESTAMP FROM STUD; 00000299
--INSERT INTO DEPT VALUES('D09','MER'); 00001099
--CREATE TABLE STUD (SNO CHAR(3) NOT NULL PRIMARY KEY, 00010099
-- NAME CHAR(15) NOT NULL, 00020099
-- DNO CHAR(3) NOT NULL, 00030099
-- M1 INTEGER, 00040099
-- M2 INTEGER, 00050099
-- M3 INTEGER, 00060099
-- TOT INTEGER, 00060199
-- AVG DECIMAL(3,2))IN DB63.TS63; 00060299
CREATE TABLE ST (SNO CHAR(3) NOT NULL PRIMARY KEY,
NAME CHAR(15) NOT NULL,
DNO CHAR(3) NOT NULL,
M1 INTEGER
)IN DB63.TS63;
CREATE TABLE DEPT (DNO CHAR(3) NOT NULL PRIMARY KEY,
-- DNAME CHAR(3) NOT NULL)IN DB63.TS63; 00061231
--CREATE UNIQUE INDEX IND3 ON ST(SNO); 00061399
--CREATE UNIQUE INDEX IND2 ON DEPT(DNO); 00062031
--INSERT INTO ST(SNO,NAME,DNO,M1) VALUES 00071399
-- ('004','MANI','D04',81); 00071499
--CREATE TABLE DATAB 00071599
--(DAT DATE NOT NULL WITH DEFAULT,TIM TIME NOT NULL WITH DEFAULT, 00071699
--TIMST TIMESTAMP NOT NULL WITH DEFAULT)IN DB63.TS63; 00071799
--ALTER TABLE DATAB ADD T3 DATE WITH DEFAULT; 00071899
--SELECT * FROM DATAB; 00071999
--INSERT INTO DATAB(DAT) VALUES('2004-05-03'); 00072099
--DATE STUD SET PER=TOT/3; 00072199
--SELECT CHAR(DAT) FROM DATAB; 00072299
--SELECT * FROM STUD; 00072399
--SELECT SUBSTR(NAME,1,3) FROM STUD; 00072499
--LECT LENGTH(NAME) FROM STUD; 00072599
--LECT LENGTH(PER) FROM STUD; 00072699
--LECT LENGTH(AVG) FROM STUD; 00072799
--LECT HOUR(TIM)FROM DATAB; 00072899
--LECT HOUR(TIMST)FROM DATAB; 00072999
--SELECT DAYS(DAT) FROM DATAB; 00073099
--LECT DAYS(TIM) FROM DATAB; 00073199
--LECT DAYS(TIMST) FROM DATAB; 00073299
--LECT * FROM DATAB; 00073399
--SELECT SNO,NAME,PER,DNO FROM STUD X WHERE PER >= 00073499
--SELECT DNO FROM STUD GROUP BY DNO HAVING COUNT(*) >1; 00073599
--( SELECT AVG(PER) FROM STUD Y WHERE X.DNO=Y.DNO); 00073699
--SELECT SUM(M1+M2+M3) FROM STUD WHERE DNO IN ( SELECT DNO FROM STUD ) 00073799
--SELECT DNO,SUM(M1+M2+M3) FROM STUD 00073899
--GROUP BY DNO; 00073999
--SERT INTO DAI(A) VALUES('HEIO'); 00074099
--INSERT INTO DAI VALUES('2004-04-04'); 00074199
--SELECT DATE('2004-04-04') + 1 MONTH FROM DAI; 00074299
--SELECT * FROM DAI; 00074399
--SELECT COALESCE(TOT,0) FROM STUD WHERE AVG IS NULL; 00074499
--CREATE TABLE HAI(HAI CHAR(4) NOT NULL PRIMARY KEY) IN DB63.TS63; 00074599
--CREATE UNIQUE INDEX DIN ON HAI(HAI); 00074699
--DROP TABLE HAI; 00075099
--CREATE UNIQUE INDEX DIN ON STUD(SNO); 00076099
--SELECT * FROM STUD; 00080038
--INSERT INTO DEPT VALUES('D04','IT'); 00080142
--SELECT * FROM DEPT; 00080295
--ALTER TABLE STUD ADD FOREIGN KEY(DNO) REFERENCES DEPT(DNO); 00080344
--SELECT * FROM STUD ORDER BY (SNO); 00080447
--SELECT DISTINCT DNO FROM STUD; 00080549
--SELECT SNO,NAME,M1,M2,M3,'TOTAL=',M1+M2+M3 FROM STUD; 00080652
--UPDATE STUD SET DNO='D04' WHERE SNO='006'; 00080765
--SELECT * FROM DEPT; 00080868
--SELECT * FROM STUD WHERE DNO IN (SELECT DNO FROM DEPT WHERE DNAME IN 00080968
--('EEE','ECE')); 00081068
--UPDATE STUD SET AVG=3; 00082099
--UPDATE STUD SET TOT=M1+M2+M3 WHERE SNO IN (SELECT SNO FROM STUD); 00082188
--UPDATE STUD SET PER=TOT/3 WHERE SNO IN (SELECT SNO FROM STUD); 00082288
--SELECT * FROM STUD WHERE PER BETWEEN 60 AND 90; 00082388
--ALTER TABLE STUD ADD PER DECIMAL(5,2); 00082482
--CREATE VIEW VIEWSTUD AS SELECT SNO,NAME 00083099
--FROM STUD 00083199
--WHERE SNO=( SELECT SNO FROM STUD MAX(TOT)); 00083399
--SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR='MAPL737' 00083499
--SELECT * FROM V1; 00083599
--DROP VIEW V1; 00083699
--SELECT * FROM STUD; 00083799
--SELECT * FROM V1; 00083899
--SELECT SUM(TOTAL) FROM V1 GROUP BY DNO; 00083999
--CREATE VIEW V1(SNO,NAME,DNO,TOTAL) AS 00084099
--SELECT SNO,NAME,DNO,M1+M2+M3 FROM STUD; 00084199
--(SELECT DNO FROM DEPT WHERE DNAME = 'IT'); 00084299
--SELECT * FROM STUD WHERE NOT EXISTS 00084399
--(SELECT DNO FROM DEPT WHERE DNAME = 'AA'); 00084499
--DELETE FROM DEPT WHERE DNO='D09'; 00084599
--SELECT * FROM DEPT; 00085099
--SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME='STUD' AND 00086099
--TBCREATOR='MAPL737'; 00087099
--DROP TABLE DAI; 00089099
--DELETE FROM DEPT WHERE DNO = 'D08'; 00089199
--DELETE FROM EMP WHERE ENO < 5000; 00089799
--SELECT SUM(DNO) FROM EMP; 00089899
--DELETE FROM STUD WHERE SNO = '007'; 00089999
--SELECT * FROM EMP; 00090099
--SELECT * FROM ST; 00090199
--LECT * FROM ST,DEPT; 00090299
SELECT * FROM STUD; 00090399
SELECT MAX(TOT) FROM STUD; 00090499
SELECT MAX(TOT) FROM STUD WHERE TOT NOT IN (SELECT MAX(TOT) FROM STUD); 00090599
SELECT COUNT(*) FROM STUD A ,STUD B WHERE A.TOT=B.TOT; 00090699
SELECT MAX(TOT) FROM STUD A WHERE &2 =(SELECT COUNT(*) FROM STUD B 00090799
WHERE A.TOT=B.TOT); 00090899
--SELECT * FROM DEPT; 00090999
--SELECT * FROM DEPT,ST; 00091099
--SELECT * FROM DEPT,ST WHERE DEPT.DNO='D02'; 00100099
--SELECT * FROM DEPT,ST WHERE ST.DNO='D02'; 00110099
--SELECT MAX(M1) FROM ST; 00120099
--SELECT NAME FROM ST WHERE M1 IN ( SELECT MAX(M1) FROM ST ); 00130099
********************************************************************* 00140099
No comments:
Post a Comment