--SELECT * FROM SYSIBM.
SYSSTOGROUP;
--SELECT * FROM [Link] WHERE CREATOR='IBMUSER';
--SELECT * FROM [Link] WHERE CREATOR='STUD13';
--SELECT * FROM [Link];
--SELECT * FROM [Link] WHERE CREATOR='STUD13' AND
-- VCATNAME='DSN910';
--SELECT * FROM [Link] WHERE SGNAME='SYSDEFLT'
--======================================================================
-- CREATE STOREGROUP
-- CREATE STOGROUP STUD13D
-- VOLUMES(Z9USR2)
-- VCAT DSN910;
--======================================================================
-- SELECT * FROM [Link];
-- WHERE CREATOR = 'STUD13' AND
-- VCATNANE = DSN910':
--======================================================================
-- SELECT * FROM [Link]
-- WHERE SGNAME='STUD13D';
--======================================================================
-- TO CREATE A DATABASE MODULE 1 BOA
-- CREATE DATABASE STD13DB
-- BUFFERPOOL BP0
-- STOGROUP STUD13D
-- CCSID ASCII;
--======================================================================
-- SELECT * FROM [Link] WHERE CREATOR='STUD13':
--======================================================================
-- TO CREATE SEGMENTED TABLESPACE BOR MODULE1
-- CREATE TABLESPACE STD13TBS
-- IN STD13DB
-- USING STOGROUP STUD13D
-- PRIQTY 10
-- SECQTY 10
-- ERASE NO
-- LOCKSIZE PAGE LOCKMAX SYSTEM
-- BUFFERPOOL BP0
-- CLOSE NO
-- SEGSIZE 4
-- CCSID EBCDIC;
--=====================================================================
-- SELECT * FROM [Link] WHERE CREATOR 'STUD13';
--====================================================================
-- SELECT * FROM [Link]
-- WHERE NAME='STD13TBS';
-- COMMIT;
--=========================================================
-- STOGROUP NAME: STUD13D
-- DATABASE NAME: STD13DB
-- TABLESPACE NAME: STD13TBS
-- DEPARTMENT TABLE:ST13DEPT
--=========================================================
--CREATE TABLE STUD13.ST13DEPTM
-- (DEPTNO CHAR (3) NOT NULL,
-- DEPTNAME VARCHAR (36) NOT NULL,
-- MGRNO CHAR (6) ,
-- ADMRDEPT CHAR (3) NOT NULL,
-- LOCATION CHAR (16),
-- PRIMARY KEY(DEPTNO))
-- IN STD13DB.STD13TBS
-- CCSID EBCDIC;
--===========CREATE INDEX FOR TABLE====================================
-- CREATE UNIQUE INDEX STUD13.XST13DEPT
-- ON STUD13.ST13DEPT(DEPTNO);
--============DML OPERATION============================================
-- SELECT * FROM STUD13.ST13DEPT;
-- SELECT * FROM [Link] WHERE CREATOR='STUD13'
-- AND DBNAME='STD13DB';
-- SELECT * FROM [Link] WHERE TBCREATOR='STUD13'
-- AND TBNAME='ST13DEPT'
--=========================INSERT VALUES INTO TABLE====================
-- INSERT INTO STUD13.ST13DEPT
-- (DEPTNO,DEPTNAME,MGRNO,ADMRDEPT,LOCATION)
-- VALUES('D01','DEVELOPMENTDEPT','DV1','QCK','CHENNAI');
-- VALUES('D02','DELIVERYDEPT', 'DV3','QCK','BANGALORE');
-- VALUES('D03','MANAGEMENTDEPT','DV3','QCK','BANGALORE');
-- VALUES('D04','MANAGEMENTDEPT','DV1','QCK','CHENNAI');
-- VALUES('D05','MANAGEMENTDEPT','DV1','QCK','CHENNAI');
--============================VIEW TABLE================================
-- SELECT * FROM STUD13.ST13DEPT;
-- SELECT * FROM STUD13.ST13DEPT WHERE LOCATION='BANGALORE';
-- SELECT * FROM ST13DEPT WHERE MGRNO='DV6' OR LOCATION='CHENNAI';
--=================UPDATE THE VALUES IN TABLE===========================
-- UPDATE ST13DEPT
-- SET LOCATION='PUNE'
-- WHERE DEPTNO='D03';
--===========CREATE A TABLE USING ANOTHER TABLE=========================
-- CREATE TABLE ST13DEPT1 AS (SELECT * FROM ST13DEPT) WITH NO DATA
-- IN STD13DB.STD13TBS;
-- INSERT INTO STUD13.ST13DEPT
-- (DEPTNO,DEPTNAME,MGRNO,ADMRDEPT,LOCATION)
-- VALUES('D01','DEVELOPMENTDEPT','DV1','QCK','CHENNAI');
-- VALUES('D02','DELIVERYDEPT', 'DV3','QCK','BANGALORE');
-- VALUES('D03','MANAGEMENTDEPT','DV3','QCK','BANGALORE');
-- VALUES('D04','MANAGEMENTDEPT','DV1','QCK','CHENNAI');
-- VALUES('D05','MANAGEMENTDEPT','DV1','QCK','CHENNAI');
--============================VIEW TABLE================================
-- SELECT * FROM STUD13.ST13DEPT;
-- SELECT * FROM STUD13.ST13DEPT WHERE LOCATION='BANGALORE';
-- SELECT * FROM ST13DEPT WHERE MGRNO='DV6' OR LOCATION='CHENNAI';
--=================UPDATE THE VALUES IN TABLE===========================
-- UPDATE ST13DEPT
-- SET LOCATION='PUNE'
-- WHERE DEPTNO='D03';
--===========CREATE A TABLE USING ANOTHER TABLE=========================
-- CREATE TABLE ST13DEPT1 AS (SELECT * FROM ST13DEPT) WITH NO DATA
-- IN STD13DB.STD13TBS;
--SELECT * FROM STUD13.ST13DEPT1;
--================CREATE A TABLE USING "LIKE"===========================
--CREATE TABLE STUD13.ST13DEPT2 LIKE STUD13.ST13DEPT
-- IN STD13DB.STD13TBS;
-- SELECT * FROM STUD13.ST13DEPT2;
--================DELETE THE TABLE======================================
-- SELECT * FROM STUD13.STD3EMP;
-- DROP TABLE STUD13.STD3EMP;
--=======TRANSFER ALL RECORDS INTO ONE TABLE TO ANOTHER TABLE==========
-- INSERT INTO STUD13.ST13DEPT1
-- SELECT * FROM STUD13.ST13DEPT;
-- SELECT * FROM STUD13.ST13DEPT1;
--====================EMPLOYEE TABLE====================================
-- CREATE TABLE [Link]
-- (EMPNO CHAR(3) NOT NULL,
-- EMPNAME VARCHAR(25) NOT NULL,
-- DEPT CHAR(10) ,
-- DOJ DATE WITH DEFAULT,
-- SALARY DECIMAL(9,2) ,
-- LOCATION CHAR(16) ,
-- PRIMARY KEY(EMPNO))
-- IN STD13DB.STD13TBS
-- CCSID EBCDIC;
--====================CREATE UNIQUE INDEX FOR EMP=======================
--CREATE UNIQUE INDEX STUD13.XEMPDATA1
-- ON [Link](EMPNO);
-- SELECT * FROM [Link];
--===============INSERT DATA TO THE EMPLOYEE TABLE======================
-- INSERT INTO [Link]
-- (EMPNO,EMPNAME,DEPT,DOJ,SALARY,LOCATION)
-- VALUES('EO1','AKASH','IT','2022-10-12',45000,'CHENNAI')
-- VALUES('EO2','ANANTH','SALES','2024-06-12',18000,'BANGALORE')
-- VALUES('EO3','BALA','SALES','2024-06-12',18000,'BANGALORE')
-- VALUES('EO4','CATHERINE','IT','2023-02-01',33000,'CHENNAI')
--======================================================================
-- UPDATE [Link] SET SALARY=SALARY+5000
--======================================================================
--SELECT EMPNO,SALARY,LOCATION FROM EMPDATA
-- ORDER BY SALARY;
--======================================================================
-- SELECT LOCATION,EMPNAME FROM EMPDATA WHERE LOCATION LIKE '%RE';
--========CHILD TABLE FOR STUD13.ST13DEPT=FOREIGN KEY-DEPTNO============
--CREATE TABLE STUD13.STD3EMP
-- (DEPTNO CHAR (3) NOT NULL,
-- EMPNAME VARCHAR (36) NOT NULL,
-- EMPNO CHAR (6) ,
-- LOCATION CHAR (16) NOT NULL,
-- PROJCODE VARCHAR (5) NOT NULL,
-- PRJSDATE DATE WITH DEFAULT,
-- FOREIGN KEY(DEPTNO) REFERENCES STUD13.ST13DEPT(DEPTNO)
-- ON DELETE CASCADE)
-- IN STD13DB.STD13TBS
-- CCSID EBCDIC;
--================DML OPERATIONS========================================
-- SELECT * FROM STUD13.ST13DEPT;
-- SELECT * FROM STUD13.STD3EMP;
--======================================================================
-- INSERT INTO STUD13.STD3EMP
-- (DEPTNO,EMPNAME,EMPNO,LOCATION,PROJCODE,PRJSDATE)
-- VALUES ('D01','AMALA','E001','CHENNAI','M1V2','2024-07-24')
-- VALUES ('D02','DAVID','E002M,'CHENNAI','M1V2','2024-07-24')
-- VALUES ('D03','RAMAN','E003','CHENNAI','M1V2','2024-07-24')
-- VALUES ('D04','VARUN','E004','CHENNAI','M1V2','2024-07-24')
-- VALUES ('D05','AKASH','E005','CHENNAI','M1V2','2024-07-24')
-- VALUES ('D01','ANANTH','E006','CHENNAI','M1V2','2024-07-24')
-- VALUES ('D02','BALA','E007','CHENNAI','M1V2','2024-07-24')
-- VALUES ('D04','CATHERINE','E008','CHENNAI','M1V2','2024-07-24')
--=========================================================
--CREATE TABLE [Link]
-- (DEPTNO CHAR (3) NOT NULL,
-- DEPTNAME VARCHAR (36) NOT NULL,
-- MGRNO CHAR (6) ,
-- ADMRDEPT CHAR (3) NOT NULL,
-- LOCATION CHAR (5),
-- PRIMARY KEY(DEPTNO))
-- IN STD13DB.STD13TBS
-- CCSID EBCDIC;
--===========CREATE INDEX FOR TABLE====================================
-- CREATE UNIQUE INDEX [Link]
-- ON STUD13.ST13DEPT(DEPTNO);
--====================================================================
-- CREATE TABLE [Link]
-- (DEPTNO CHAR(3) NOT NULL,
-- DEPTNAME CHAR(10) NOT NULL,
-- MGRNO CHAR(6) ,
-- SALARY DEC(8,2) NOT NULL,
-- LOCATION CHAR(16)
-- )
-- IN STD13DB.STD13TBS
-- CCSID EBCDIC;
--=========================INSERT VALUES INTO TABLE====================
--INSERT INTO [Link]
-- (DEPTNO,DEPTNAME,MGRNO,SALARY,LOCATION)
-- VALUES('101','DEVEDEPT','DV1',14000,'CHENNAI');
-- VALUES('102','DELYDEPT','DV3',15000,'BANGALORE');
-- VALUES('103','MANGTDEPT','DV3',14000,'BANGALORE');
-- VALUES('104','MANGTDEPT','DV1',16000,'CHENNAI');
-- VALUES('105','MANGTEPT','DV1',13000,'CHENNAI');
SELECT * FROM [Link];