Home - Rasfoiesc.com
Educatie Sanatate Inginerie Business Familie Hobby Legal
Doar rabdarea si perseverenta in invatare aduce rezultate bune.stiinta, numere naturale, teoreme, multimi, calcule, ecuatii, sisteme




Biologie Chimie Didactica Fizica Geografie Informatica
Istorie Literatura Matematica Psihologie

Sql


Index » educatie » » informatica » Sql
» CREAREA BD FOLOSIND INSTRUCTIUNI SQL


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*'));





Politica de confidentialitate





Copyright © 2024 - Toate drepturile rezervate