Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --1)
- create table furnizori (
- id int auto_increment primary key,
- nume char(20),
- oras char(20),
- data_inregistrarii date
- );
- --2)
- insert into furnizori (nume, oras, data_inregistrarii)
- values ('antennax','bucuresti','2001-11-20');
- --3)
- select * from furnizori;
- --4)
- select id_client, nume from furnizori where year(data)=2017;
- --5)
- select * from furnizori order by data desc;
- select nume_client from clienti where id_client in (select max(id_client) from clienti);
- --6) listati ultimul client din tabelul clienti
- create table clienti (
- id_client int auto_increment primary key,
- nume_client varchar(20) not null
- );
- insert into clienti(nume_client) values ('Oprea');
- create table medici(
- id_medic int auto_increment primary key,
- nume varchar(20) not null,
- tel varchar(20) not null
- );
- insert into medici(nume, tel) values ('Dr House' , '0769212146'), ('Dr Grey' , '6969696969');
- create table servicii(
- id_serviciu int auto_increment primary key,
- nume varchar(20) not null,
- pret varchar(20) not null
- );
- insert into servicii(nume, pret) values ('Operatie' , '1500'), ('CT' , '2000'), ('Ecografie' , '300'), ('Consultanta' , '30');
- create table cabinete(
- id_cabinet int auto_increment primary key,
- nume varchar(20) not null,
- strada varchar(20) not null
- );
- insert into cabinete(nume, strada) values ('MedLife' , 'Calea Grivitei'), ('Regina Maria' , 'Piata Victoriei');
- create table programari(
- id_programare int not null,
- id_client int not null,
- data_p date not null,
- tel varchar(20) not null
- );
- INSERT INTO `programari` (`id_programare`, `id_client`, `data_p`, `tel`) VALUES ('1', '1', '2018-01-01', '198273912');
- insert into
- programari(id_programare, id_client, data_p, tel)
- values
- (1 , 1 , '2018-11-11' , '075555555'),
- (2 , 1 , '2017-11-10' , '075545646'),
- (1 , 2 , '2018-12-10' , '075522215'),
- (2 , 2 , '2018-12-12' , '075500000'),
- (3 , 3 , '2018-05-21' , '075558798'); ;
- INSERT INTO `servicii_prestate` (`id`, `id_serviciu`, `id_programare`, `id_cabinet`, `id_medic`, `ora`, `data`) VALUES ('1', '1', '1', '1', '1', '12:00:00', '2018-01-02');
- --7) afisati toate serviciile ce urmeaza sa fie prestate astazi in cabinetul cu id 1 ordonate desc dupa ora programarii
- select
- s.nume, s.pret
- from servicii as s
- inner join programari as p
- on s.id_serviciu = p.id_serviciu
- inner join servicii_prestate as sp
- on sp.id_programare = p.id_programare
- where servicii_prestate.id_cabinet = 1
- and p.data_p = CURDATE()
- order by p.data_p desc;
- --11) afisati toti medicii (id) impreuna cu media valorii totale a serviciilor
- -- prestate de fiecare, dar numai pentru medicii
- -- care au prestat cel putin doua servicii
- select
- m.id_medic,
- avg(s.pret) as pret_mediu
- from medici as m
- inner join servicii_prestate as sp
- on m.id_medic = sp.id_medic
- inner join servicii as s
- on sp.id_serviciu = s.id_serviciu
- where
- (select count(id_serviciu) from servicii) > 2
- group by m.id_medic;
- -- 12) stergeti programarea cu id 1 precum si toate serviciile prestate in cadrul ei
- DELETE p, sp
- FROM programari p
- JOIN servicii_prestate sp ON sp.id_programare = p.id_programare
- WHERE p.id_programare = 1;
- delete from programari where id_programare in (select sp.id_programare, sp.id_serviciu from servicii_prestate as sp inner join programari as p
- where sp.id_programare=p.id_programare and id_programare=1);
- -- 13) scadeti cu o unitate pretul serviciilor ce nu au fost prestate niciodata
- update servicii set pret=pret-1 where id_serviciu not in (select id_serviciu from servicii_prestate);
- -- 14) modificati tipul coloanei nume din servicii sa fie de tipul char(40)
- alter table servicii modify nume char(40);
- -- 15) afisati toate cabinetele (id) impreuna cu suma totala a serviciilor prestate in ele.
- -- in cabinetele unde nu au fost prestate servicii se va afisa suma totala 0
- ALTER TABLE `cabinete` ADD `suma_totala` VARCHAR(20) NOT NULL DEFAULT '0' AFTER `strada`;
- -- 16) reduceti cu 10% valoarea programarilor de azi si cu 20% valoarea programarilor de ieri
- -- o singura interogare
- -- data de azi = CURDATE()
- -- data de ieri = date_add(CURDATE(), interval 1 day)
- -- todo: pune inserturile toate
- update programari
- set valoare=valoare-(0.2*valoare)
- where
- data_p = CURDATE()
- or
- data_p = CURDATE() - 1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement