uni

University stuff
git clone git://git.christosmarg.xyz/uni-assignments.git
Log | Files | Refs | README | LICENSE

new_personnel.sql (3287B)


      1 CREATE TABLE DEPT (
      2 	DEPTNO INT(2) NOT NULL,
      3 	DNAME VARCHAR,
      4 	LOC VARCHAR,
      5 	PRIMARY KEY(DEPTNO)
      6 );
      7 
      8 CREATE TABLE EMP (
      9 	EMPNO INT(2) NOT NULL,
     10 	ENAME VARCHAR,
     11 	JOB VARCHAR,
     12 	HIERDATE DATE,
     13 	MGR INT(2),
     14 	SAL FLOAT(7,2),
     15 	COMM FLOAT(7,2),
     16 	DEPTNO INT(2) NOT NULL,
     17 	PRIMARY KEY(EMPNO),
     18 	FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
     19 );
     20 
     21 CREATE TABLE PROJ (
     22 	PROJ_CODE INT(3) NOT NULL,
     23 	DESCRIPTION VARCHAR,
     24 	PRIMARY KEY(PROJ_CODE)
     25 );
     26 
     27 CREATE TABLE ASSIGN (
     28 	EMPNO INT(2) NOT NULL,
     29 	PROJ_CODE INT(3),
     30 	A_TIME INT(3),
     31 	PRIMARY KEY(EMPNO, PROJ_CODE),
     32 	FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO),
     33 	FOREIGN KEY(PROJ_CODE) REFERENCES PROJ(PROJ_CODE)
     34 );
     35 
     36 INSERT INTO DEPT VALUES
     37 	(10, 'ACCOUNTING', 'ATHENS'),
     38 	(20, 'SALES', 'LONDON'),
     39 	(30, 'RESEARCH', 'ATHENS'),
     40 	(40, 'PAYROLL', 'LONDON');
     41 
     42 INSERT INTO EMP VALUES
     43 	(10, 'CODD', 'ANALYST', '1/1/89', 15, 3000, NULL, 10),
     44 	(15, 'ELMASRI', 'ANALYST', '2/5/95', 15, 1200, 150, 10),
     45 	(20, 'NAVATHE', 'SALESMAN', '7/7/77', 20, 2000, NULL, 20),
     46 	(30, 'DATE', 'PROGRAMMER', '4/5/04', 15, 1800, 200, 10);
     47 
     48 INSERT INTO PROJ VALUES
     49 	(100, 'PAYROLL'),
     50 	(200, 'PERSONELL'),
     51 	(300, 'SALES');
     52 
     53 INSERT INTO ASSIGN VALUES
     54 	(10, 100, 40),
     55 	(10, 200, 60),
     56 	(15, 100, 100),
     57 	(20, 200, 100),
     58 	(30, 100, 100);
     59 
     60 /* Using SQLite3 syntax*/
     61 
     62 /* .schema -> DESCRIBE */
     63 .schema
     64 
     65 /* Make output pretty */
     66 .header on
     67 .mode column
     68 
     69 /* Show contents of every table */
     70 SELECT * FROM DEPT;
     71 SELECT * FROM EMP;
     72 SELECT * FROM PROJ;
     73 SELECT * FROM ASSIGN;
     74 
     75 /*
     76  * Output after execution (`sqlite3 -init new_personnel.sql`):
     77  *
     78  * CREATE TABLE DEPT (
     79  *         DEPTNO INT(2) NOT NULL,
     80  *         DNAME VARCHAR,
     81  *         LOC VARCHAR,
     82  *         PRIMARY KEY(DEPTNO)
     83  * );
     84  * CREATE TABLE EMP (
     85  *         EMPNO INT(2) NOT NULL,
     86  *         ENAME VARCHAR,
     87  *         JOB VARCHAR,
     88  *         HIERDATE DATE,
     89  *         MGR INT(2),
     90  *         SAL FLOAT(7,2),
     91  *         COMM FLOAT(7,2),
     92  *         DEPTNO INT(2) NOT NULL,
     93  *         PRIMARY KEY(EMPNO),
     94  *         FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
     95  * );
     96  * CREATE TABLE PROJ (
     97  *         PROJ_CODE INT(3) NOT NULL,
     98  *         DESCRIPTION VARCHAR,
     99  *         PRIMARY KEY(PROJ_CODE)
    100  * );
    101  * CREATE TABLE ASSIGN (
    102  *         EMPNO INT(2) NOT NULL,
    103  *         PROJ_CODE INT(3),
    104  *         A_TIME INT(3),
    105  *         PRIMARY KEY(EMPNO, PROJ_CODE),
    106  *         FOREIGN KEY(EMPNO) REFERENCES EMP(EMPNO),
    107  *         FOREIGN KEY(PROJ_CODE) REFERENCES PROJ(PROJ_CODE)
    108  * );
    109  * DEPTNO  DNAME       LOC
    110  * ------  ----------  ------
    111  * 10      ACCOUNTING  ATHENS
    112  * 20      SALES       LONDON
    113  * 30      RESEARCH    ATHENS
    114  * 40      PAYROLL     LONDON
    115  * EMPNO  ENAME    JOB         HIERDATE  MGR  SAL     COMM   DEPTNO
    116  * -----  -------  ----------  --------  ---  ------  -----  ------
    117  * 10     CODD     ANALYST     1/1/89    15   3000.0         10
    118  * 15     ELMASRI  ANALYST     2/5/95    15   1200.0  150.0  10
    119  * 20     NAVATHE  SALESMAN    7/7/77    20   2000.0         20
    120  * 30     DATE     PROGRAMMER  4/5/04    15   1800.0  200.0  10
    121  * PROJ_CODE  DESCRIPTION
    122  * ---------  -----------
    123  * 100        PAYROLL
    124  * 200        PERSONELL
    125  * 300        SALES
    126  * EMPNO  PROJ_CODE  A_TIME
    127  * -----  ---------  ------
    128  * 10     100        40
    129  * 10     200        60
    130  * 15     100        100
    131  * 20     200        100
    132  * 30     100        100
    133  */