CREAREA BD FOLOSIND INSTRUCTIUNI SQL

CREAREA BD FOLOSIND INSTRUCTIUNI SQL

Primul pas il constituie crearea tabelelor. Deci vom prezenta ma intai codul sql pentru crearea tabelelor apoi codul pentru stergere si in cele din urma instructiuni pentru inserarea datelor.

Codul corespunzator pentru crearea tabelelor:

CREATE TABLE MEDICI(

IDMed counter(1,1) not null primary Key,

Nume Char(64) not null,

Sex char(2) not null,

DataN date not null,

Adresa char(255) not null,

CodMed char(10) not null unique,

Specializare char(64) not null,

Telefon int not null

CREATE TABLE CABINETE(

IDCab counter(1,1) not null primary key,

NumarCabinet char(64) not null,

TipCabinet char(64) not null,

Firma char(64) not null);

CREATE TABLE FIRME(

IDFirma counter(1,1) not null primary key ,

NumeF char(64) not null unique,

Adresa char(255) not null,

Telefon int not null

CREATE TABLE PROGRAMARI (

IDProg counter(1,1) not null primary key ,

CodMed int not null,

CNPPac int not null,

Data date not null,

Ora date not null,

Medic char(64) not null,

Pacient char(64) not null);

CREATE TABLE PACIENTI (

IDPac counter(1,1) not null primary key ,

Nume Char(64) not null,

CNPPac int not null unique,

DataN date not null,

Adresa char(255) not null,

TelefonPac int not null

CREATE TABLE SPECIALIZARI (

IdSpec counter(1,1) not null primary key ,

Specializare char(64) not null unique);

CREATE TABLE CABINETE_PRACTICA (

IDCP counter(1,1) not null primary key ,

Medic char(64) not null,

Cabinet char(64) not null);

CREATE TABLE CONSULTATII (

IDCons counter(1,1) not null primary key ,

Medic char(64) not null,

Pacient char(64) not null);

CREATE TABLE SPECIALIZARI_MEDICI (

IDSM counter(1,1) not null primary key ,

Specializare char(64) not null,

Medic char(64) not null);

Codul corespunzator pentru stergerea tabelelor:

DROP TABLE MEDICI;

DROP TABLE CABINETE;

DROP TABLE FIRME;

DROP TABLE PROGRAMARI;

DROP TABLE PACIENTI;

DROP TABLE SPECIALIZARI;

DROP TABLE CABINETE_PRACTICA;

DROP TABLE CONSULTATII;

DROP TABLE SPECIALIZARI_MEDICI;

Codul corespunzator pentru inserarea datelor in tabele:

INSERT INTO MEDICI (Nume, Sex, DataN, Adresa, CodMed, Specializare,Telefon)

VALUES("Enescu George", "M", 5/15/1961, "Libertatii112 Buc.", "10225A", "Urologie", 2542985);

INSERT INTO MEDICI (Nume, Sex, DataN, Adresa, CodMed, Specializare,Telefon)

VALUES("Bran Ana", "F", 12/23/1962, "Liliacului39 CT. ", "35241G", "Stomatologie", 549342);

INSERT INTO CABINETE (NumarCab, TipCab, Firma)

VALUES("52A", "Urologie", "UNIVED");

INSERT INTO FIRME (NumeF, AdresaF, TelefonF)

VALUES("GREENLIFE", "Pompiliu 101 Buc.", 6525600);

INSERT INTO CABINETE_PRACTICA (Medic, Cabinet)

VALUES ("Enescu George", "13B");

In continuare vom prezenta, folosind limbajul SQL cateva interogari asupra continutului bazei de date:

Query 1: Ce pacienti a consultat doctorul X am ales doctorul Enescu George):

SELECT MEDICI.Nume, PACIENTI.NumePac

FROM PACIENTI INNER JOIN (MEDICI INNER JOIN CONSULTATII ON MEDICI.IDMed = CONSULTATII.Medic) ON PACIENTI.IDPac = CONSULTATII.Pacient

WHERE (MEDICI.Nume Like '*Enescu George*')

ORDER BY PACIENTI.NumePac asc;

Query 2: Ne intereseaza o lista cu toate programarile din data de 5/22/2006, precum si ora de inceput,medicul si pacientul.

SELECT PROGRAMARI.IDProg, MEDICI.Nume, PACIENTI.NumePac, PROGRAMARI.DataProg, PROGRAMARI.OraProg

FROM PACIENTI INNER JOIN (MEDICI INNER JOIN PROGRAMARI ON MEDICI.IDMed = PROGRAMARI.Medic) ON PACIENTI.IDPac = PROGRAMARI.Pacient

WHERE (((PROGRAMARI.DataProg) Like '*5/22/2006*'))

ORDER BY PROGRAMARI.OraProg DESC;

Query 3: Ce medici au specializarea X.(am ales urologie)

SELECT MEDICI.Nume, MEDICI.Specializare

FROM MEDICI INNER JOIN SPECIALIZARI_MEDICI ON MEDICI.IDMed=SPECIALIZARI_MEDICI.Medic

WHERE (((MEDICI.Specializare) Like '*Urologie*'));