Skip to content

Instantly share code, notes, and snippets.

@gmathiou4

gmathiou4/1.sql

Created June 4, 2017 14:50
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save gmathiou4/8bbede8f35d51617baef562836a9d522 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save gmathiou4/8bbede8f35d51617baef562836a9d522 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. gmathiou4 created this gist Jun 4, 2017.
    78 changes: 78 additions & 0 deletions 1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,78 @@
    create database epanalhpsh;
    use epanalhpsh;

    create table professor (
    ID integer,
    fname varchar(10),
    lname varchar(20),
    salary float,
    position varchar(20),
    dno integer,
    primary key(ID));


    create table department(
    Dnumber integer,
    dname varchar(15),
    address varchar(30),
    M_ID integer,
    primary key(Dnumber),
    constraint foreign key(M_ID) references professor(ID));

    alter table professor add constraint foreign key(dno) references department(dnumber);

    insert into professor values(3, 'Nikos','Vaios',1220.87,'Assistant Professor',NULL);
    insert into professor values(4, 'kostas','Papas',1000.98,'Lecture',NULL);
    insert into professor values(7, 'Manolis','Veras',1500.23,'Associate Professor',NULL);
    insert into professor values(8, 'Maria','Lamprou',1678.23,'Professor',NULL);
    insert into professor values(9, 'Stella','Petraki',1102.34,'Lecture',NULL);
    insert into professor values(10, 'Manolis','Pateros',1200.33,'Assistant Professor',NULL);
    insert into professor values(11, 'Eirini','Kafatou',1498.34,'Associate Professor',NULL);

    insert into Department values(1,'Chemistry','Khpoupoli 3',8);
    insert into Department values(2,'Computer Science','Ikarou 23',7);

    update professor
    set dno=1
    where ID=3 OR id=8 OR ID=9 OR ID=10;

    update professor
    set dno=2
    where ID=4 OR id=7 OR ID=11;






    select fname,lname
    from professor
    where salary in (select max(salary)
    from professor);

    select fname,lname
    from professor
    where salary>=all(select salary
    from professor);

    select position, count(*),avg(salary)
    from professor
    group by position;

    select fname,lname
    from professor,department
    where dname='Chemistry' and M_ID=ID;














    101 changes: 101 additions & 0 deletions 2.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,101 @@
    drop database if exists company;

    create database company;
    use company;

    create table employee (
    FNAME varchar(10),
    MINIT char(1),
    LNAME varchar(15),
    SSN integer,
    BDATE date,
    ADDRESS varchar(20),
    SEX char(1),
    SALARY float,
    SUPERSSN integer,
    DNO integer,
    primary key(SSN));

    create table department(
    DNAME varchar(10),
    DNUMBER integer,
    MGRSSN integer,
    MGRSTARDATE date,
    primary key(DNUMBER),
    CONSTRAINT foreign key(MGRSSN) references employee(ssn));

    create table dept_locations(
    DNUMBER integer,
    DLOCATION varchar(15),
    primary key(DNUMBER,DLOCATION),
    CONSTRAINT foreign key(DNUMBER) references DEPARTMENT(DNUMBER));


    create table PROJECT(
    PNAME varchar(10),
    PNUMBER integer,
    PLOCATION varchar(15),
    DNUM integer,
    primary key(PNUMBER),
    CONSTRAINT foreign key(DNUM) references DEPARTMENT(DNUMBER));

    create table WORKS_ON(
    ESSN integer,
    PNO integer,
    hours integer,
    primary key(ESSN,PNO),
    CONSTRAINT foreign key(ESSN) references employee(ssn),
    CONSTRAINT foreign key(PNO) references project(PNUMBER));

    create table dependent(
    essn integer,
    dependentname varchar(25),
    sex char(1),
    bdate date,
    relationship varchar(10),
    primary key (essn, dependentname),
    CONSTRAINT foreign key(essn) references employee(ssn));


    ALTER TABLE employee ADD CONSTRAINT foreign key(superssn) references employee(ssn);

    ALTER table employee ADD CONSTRAINT foreign key(DNO) references department(dnumber);


    insert into employee values ('Nikos','I','Vairis',7,'1970-06-13','Ikarou 7','M',1700.73,NULL,NULL);

    insert into department values('Research',2,7,'2009-08-31');

    insert into project values('nshield',19, 'Stafford',2);

    insert into works_on values(7,19,20);

    insert into dept_locations values(2,'Stafford');

    insert into dependent values(7,'Stelios Vairis','M','1996-08-08','son');

    insert into employee values ('Manolis','I','Drakos',8,'1965-06-23','Faistou 8','M',1900.93,NULL,2);

    update employee
    set Dno=2,superssn=8
    where ssn=7;

    insert into department values('Develop',3,8,'2011-08-31');

    insert into employee values ('Nikos','I','Kakas',27,'1980-06-13','Kalokairinou 7','M',1600.73,7,3);

    insert into employee values ('Stelios','I','Perakos',26,'1975-06-13','Faistou 9','M',1670.73,7,2);

    insert into employee values ('Manoliss','G','Perakakhs',22,'1979-06-13','Faistou 19','M',1170.73,8,3);

    insert into project values('ithaki',29, 'Stafford',3);

    insert into project values('TOWL',229, 'New York',3);

    insert into works_on values(8,29,15);

    insert into works_on values(26,29,11);

    insert into works_on values(27,229,10);

    insert into works_on values(22,229,30);
    235 changes: 235 additions & 0 deletions 3.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,235 @@
    drop database if exists aeroporiki;

    create database aeroporiki;
    use aeroporiki;

    create table airplane (
    Airname varchar(10),
    oikthe integer,
    prthe integer,
    primary key (Airname)
    );

    create table dromologio (
    Code varchar(10),
    afet varchar(10),
    proo varchar(10),
    timeanax date,
    timeaf date,
    apost float,
    oikthe integer,
    prthe integer,
    primary key (Code)
    );

    create table im_pra (
    Co varchar(10),
    de char(1),
    tr char(1),
    te char(1),
    pe char(1),
    pa char(1),
    sa char(1),
    ki char(1),
    constraint foreign key (Co) references dromologio(Code) on delete cascade
    );

    create table pelates (
    Onpe varchar(20),
    city varchar(15),
    street varchar(15),
    ari integer,
    primary key (Onpe)
    );

    create table employee (
    AT varchar(15),
    hmgen date,
    salary float,
    onep varchar(20),
    city varchar(15),
    street varchar(15),
    ari integer,
    onre varchar(20),
    rela varchar(10),
    tel integer,
    primary key (AT)
    );

    create table pilotos (
    ATpil varchar(15),
    ores integer,
    primary key (ATpil),
    constraint foreign key(ATpil)references employee (AT)
    );

    create table aerosinodos (
    ATaer varchar(15),
    years integer,
    primary key (ATaer),
    constraint foreign key(ATaer)references employee (AT)
    );

    create table dioikitikos (
    ATdi varchar(15),
    years integer,
    know varchar(20),
    primary key (ATdi),
    constraint foreign key(ATdi)references employee (AT)
    );

    create table praktoras (
    Prcode integer,
    ATpr varchar(15),
    onpra varchar(20),
    dieuthinsi varchar(20),
    primary key (Prcode , ATpr)
    );

    create table aerometafora (
    Airn varchar(15),
    Ima date,
    primary key (Airn , Ima),
    constraint foreign key(Airn)references airplane (Airname)
    );

    create table pilotos_aero (
    Airn varchar(15),
    Im date,
    ATpil1 varchar(15),
    ATpil2 varchar(15),
    primary key (Airn, Im),
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)
    );

    create table aerosinodos_aero (
    Airn varchar(15),
    Im date,
    ATaer varchar(15),
    primary key (Airn, Im),
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)

    );

    create table dioikitikos_aero (
    Airn varchar(15),
    Im date,
    ATdi varchar(15),
    primary key (Airn, Im),
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)

    );

    create table kratisi_pelati (
    Airn varchar(15),
    Im date,
    tikra date,
    tiak date,
    oikthe integer,
    prthe integer,
    primary key (Airn,Im),
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)
    );

    create table kratisi_praktora (
    Airn varchar(15),
    Im date,
    onpel varchar(20),
    oikthe integer,
    prthe integer,
    primary key (Airn, Im),
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)
    );

    alter table pilotos_aero add constraint foreign key(ATpil1)references pilotos (ATpil);
    alter table pilotos_aero add constraint foreign key(ATpil2)references pilotos (ATpil);
    alter table aerosinodos_aero add constraint foreign key(ATaer)references aerosinodos (ATaer);
    alter table dioikitikos_aero add constraint foreign key(ATdi)references dioikitikos (ATdi);


    insert into airplane values ('plane1',50,15);
    insert into airplane values ('plane2',20,19);
    insert into airplane values ('plane3',80,5);
    insert into airplane values ('plane4',53,22);
    insert into airplane values ('plane5',15,50);


    insert into dromologio values ('p112','ath','zak','2014-04-11','2014-04-12',150.45,50,15);
    insert into dromologio values ('p125','ira','ath','2014-07-01','2014-05-01',150.45,20,19);
    insert into dromologio values ('p222','ioa','the','2014-05-11','2014-05-11',350.15,80,5);
    insert into dromologio values ('p002','the','ira','2014-05-17','2014-05-17',580.49,50,15);
    insert into dromologio values ('p190','cha','ale','2014-04-20','2014-04-21',650.22,53,22);
    insert into dromologio values ('p182','zak','rod','2014-04-01','2014-04-01',580.49,15,50);
    insert into dromologio values ('p099','rod','ioa','2014-05-11','2014-05-11',210.00,53,22);


    insert into im_pra values ('p112','1',NUll,NUll,NUll,NUll,NUll,NUll);
    insert into im_pra values ('p125',NUll,'1',NUll,NUll,NUll,NUll,NUll);
    insert into im_pra values ('p222',NUll,NUll,'1',NUll,NUll,NUll,NUll);
    insert into im_pra values ('p002',NUll,NUll,NUll,'1',NUll,NUll,NUll);
    insert into im_pra values ('p190',NUll,NUll,NUll,NUll,'1',NUll,NUll);
    insert into im_pra values ('p182',NUll,NUll,NUll,NUll,NUll,'1',NUll);
    insert into im_pra values ('p099',NUll,NUll,NUll,NUll,NUll,NUll,'1');


    insert into pelates values ('m_papadakis','irakleio','dim',12);
    insert into pelates values ('k_miaoulis','thessaloniki','mok',99);
    insert into pelates values ('g_goalstoglou','zakinthos','ele',105);
    insert into pelates values ('p_anastasiadis','ioannina','kal',58);
    insert into pelates values ('n_papadopoulos','patra','ali',4);


    insert into employee values ('ab1234567','1970-07-07',3112.58,'kostas_m','irakleio','dim',10,'maria_m','kori',210999111);
    insert into employee values ('gd1234567','1960-07-07',3522.58,'giannis_m','irakleio','ali',22,'eleni_m','kori',210555111);
    insert into employee values ('ez1234567','1980-07-07',2112.58,'giorgos_m','irakleio','kom',11,'dora_m','kori',210999222);
    insert into employee values ('ht1234567','1990-07-07',1112.58,'mixalis_m','irakleio','kal',88,'viky_m','kori',210777111);
    insert into employee values ('ik1234567','1979-07-07',2802.58,'maria_m','irakleio','mok',75,'anna_m','kori',210998444);

    insert into pilotos values ('ab1234567',1235);
    insert into pilotos values ('gd1234567',1578);
    insert into pilotos values ('ez1234567',2189);


    insert into aerosinodos values ('ht1234567',4);


    insert into dioikitikos values ('ik1234567',10,'metaptixiako');


    insert into praktoras values (123,'a1234567','nikos','kapou');
    insert into praktoras values (456,'b1234567','alex','pouthena');
    insert into praktoras values (789,'c1234567','niki','pantou');


    insert into aerometafora values ('plane1','2014-04-11');
    insert into aerometafora values ('plane2','2014-05-11');
    insert into aerometafora values ('plane3','2014-05-01');
    insert into aerometafora values ('plane4','2014-05-17');
    insert into aerometafora values ('plane5','2014-04-20');

    insert into pilotos_aero values ('plane1','2014-04-11','ez1234567','ab1234567');
    insert into pilotos_aero values ('plane2','2014-05-11','ez1234567','gd1234567');

    insert into aerosinodos_aero values ('plane1','2014-04-11','ht1234567');
    insert into aerosinodos_aero values ('plane2','2014-05-11','ht1234567');

    insert into dioikitikos_aero values ('plane1','2014-04-11','ik1234567');
    insert into dioikitikos_aero values ('plane2','2014-05-11','ik1234567');


    insert into kratisi_pelati values ('plane1','2014-04-11','2014-02-12','2014-03-12',0,1);
    insert into kratisi_pelati values ('plane2','2014-05-11','2014-03-12','2014-04-12',1,0);


    insert into kratisi_praktora values ('plane1','2014-04-11','n_papadopoulos',0,1);
    insert into kratisi_praktora values ('plane2','2014-05-11','m_papadakis',1,0);




    update employee inner join pilotos on ATpil=AT and ores>2000 set salary=salary*0.1+salary;




    delete from dromologio where afet='zak';