Essay
Proiect Baze de date SQL
- Course
- Institution
Baza de date a unei clinici medicale cu 4 tabele si exersarea comenzilor ALTER TABLE, INSERT, SELECT.
[Show more]
1 review
By: luciveronicavasile • 3 year ago
Seller
Follow
laradocs
Reviews received
Content preview
Prezentul proiect exemplifica comenzile si clauzele fundamentale ale bazelorde date in SQL si vine in ajutorul studentilor de la matematica si informatica
din anii I-II
1. Elaborarea un script SQL pentru crearea tabelelor bazei de date
2. Exemplificarea comenzilor de tip ALTER TABLE
3. Exemplificarea comenzilor INSERT si SELECT
4. Selectarea datelor din mai multe tabele
Tipurile de asociere: inner join, left join, right join, full join, cross join
Clauzele WHERE, HAVING, FROM si SELECT
Elaborarea un script SQL pentru crearea tabelelor bazei de date
Crearea a patru tabele cu denumirile Pacienti, Programari, Medici, respectiv
Cabinete:
CREATE TABLE Pacienti (
ID int,
Nume varchar(100),
Prenume varchar(100),
Oras varchar(100)
);
CREATE TABLE Programari (
ID int,
NumePacient varchar(100),
PrenumePacient varchar(100),
Medic varchar(100),
Data date,
Ora time(0),
Cabinet int
);
1
,CREATE TABLE Medici (
ID int,
Nume varchar(100),
Prenume varchar(100),
Cabinet int
);
CREATE TABLE Cabinete (
ID int,
Data date,
Ora time,
Oras varchar(100),
Programare bit
);
Stergerea tabelului Programari:
DROP TABLE Programari;
2
,CREATE TABLE ProgramariStomatologie (
ID int,
NumePacient varchar(100),
PrenumePacient varchar(100),
Medic varchar(100),
Data date,
Ora time(0),
Cabinet int,
Programare bit
);
CREATE TABLE ProgramariRadiografie (
ID int,
NumePacient varchar(100),
PrenumePacient varchar(100),
Medic varchar(100),
Data date,
Ora time(0),
Cabinet int,
Programare bit
);
3
, Exemplificarea comenzilor de tip ALTER TABLE
Adaugarea coloanei Oras de tip varchar in tabelul Pacienti:
ALTER TABLE Pacienti
ADD Oras varchar(100);
Redenumirea tabelului Cabinet in Cabinete:
ALTER TABLE Cabinet
RENAME TO Cabinete;
Stergerea coloanei Adresa din tabelul Pacienti:
ALTER TABLE Pacienti
DROP COLUMN Adresa;
Comenzile INSERT si SELECT
Inserarea unor valori in tabelul Pacienti:
INSERT INTO Pacienti (ID, Nume, Prenume, Oras)
VALUES ('1', 'Ilie', 'Dominic', 'Bucuresti'), ('2', 'Matei', 'Ioan', 'Bucuresti'), ('3',
'Serafim', 'Radu', 'Bucuresti');
4
,Urmatoarea comanda selecteaza toate coloanele din tabelul Pacienti:
SELECT * FROM Pacienti;
Urmatoarea comanda selecteaza coloanele ID si Oras din tabelul Cabinete:
SELECT ID, Oras
FROM Cabinete;
Selectarea datelor din mai multe tabele
Puneti in evidenta urmatoarele tipuri de asociere: inner join, left join,
right join, full join, cross join.
Urmatoarea comanda selecteaza numele medicului ce are o programare
stabilita pentru un pacient:
SELECT Nume
FROM Medici
INNER JOIN Cabinete
ON Medici.Cabinet = Cabinete.ID
WHERE Cabinete.Programare='1';
5
,Urmatoarea comanda selecteaza orasul in care exista un pacient programat in
data 2020-10-10:
SELECT Cabinete.Oras
FROM Cabinete
RIGHT JOIN Pacienti
ON Cabinete.Oras = Pacienti.Oras
WHERE Data= '2020-10-10';
Exersati utilizarea subinterogarilor utilizand tabelele din baza de date
Evidentiati plasarea subinterogarilor in clauzele WHERE, HAVING,
FROM si SELECT
CLAUZA HAVING
Urmatoarea comanda selecteaza numele medicului ce lucreaza la cabinetul
2:
SELECT Nume, Cabinet
FROM Medici
GROUP BY Cabinet
HAVING Cabinet='2';
6
, CLAUZA WHERE
Urmatoarea comanda selecteaza numele medicului ce are o programare
stabilita pentru un pacient.
SELECT Nume
FROM Medici
INNER JOIN Cabinete
ON Medici.Cabinet = Cabinete.ID
WHERE Cabinete.Programare='1';
Sa se determine pacientii care au o programare la stomatologie, dar nu au
nicio programare la radiografii.
Mai intai vom adauga datele pentru returnare in tabelul
ProgramariStomatologie:
7
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller laradocs. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $4.49. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
67163 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now