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
SQL
Raw
1.sql
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;
Raw
2.sql
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);
Raw
3.sql
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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment