PROIECT
BAZE DE DATE
Andrei Dragos Andrei
GRUPA 223, ANUL II,
INFORMATICA
II.Crearea tabelelor
use Andrei
drop table Salariati
drop table Hotel
drop table Rezervare
drop table Tarif
CREATE TABLE Salariati
Nume_hotel nvarchar 50 Primary key NULL,
Nr_salariati int NULL
CREATE TABLE Hotel
Nume_hotel nvarchar 50 Primary key NULL,
Cod_hotel int NULL,
Nume_oras nvarchar 50 NULL,
Nume_strada nvarchar 50 NULL,
Nr_hotel int NULL,
Categorie_hotel nvarchar 50 NULL,
Nr_cam1 int NULL,
Nr_cam2 int NULL,
Nr_cam3 int NULL,
Nr_cam4 int NULL,
Obt_camera_simpla nvarchar 5 NULL,
Obt_camera_lux nvarchar 5 NULL
CREATE TABLE Rezervare
Nume nvarchar 50 NULL,
Prenume nvarchar 50 NULL,
Data_rez datetime NULL,
Data_inceperii datetime NULL,
Nr_zile int NULL,
Nr_locuri int NULL,
Nume_hotel nvarchar 50 NULL,
Obt_camera_simpla nvarchar 5 NULL,
Obt_camera_lux nvarchar 5 NULL
CREATE TABLE Tarif
Nume_hotel nvarchar 50 Primary key NULL,
Nr_camere_simple int NULL,
Nr_camere_lux int NULL,
Tarif_camera_simpla int NULL,
Tarif_camera_lux int NULL
insert into Salariati VALUES 'Parang'
insert into Salariati VALUES 'Jiu'
insert into Salariati VALUES 'Europa'
insert into Salariati VALUES 'Gorj'
insert into Salariati VALUES 'Sport'
insert into Salariati VALUES 'Continental'
insert into Salariati VALUES 'Iaki'
insert into Salariati VALUES 'Moon'
insert into Salariati VALUES 'Ana'
insert into Salariati VALUES 'Carpati'
insert into Salariati VALUES '
insert into Salariati VALUES 'Golden'
insert into Salariati VALUES 'Euphoria'
insert into Salariati VALUES 'Doina'
insert into Salariati VALUES 'President'
insert into Hotel VALUES 'Parang' 'Targu Jiu' 'Minerilor' 'DA' 'NU'
insert into Hotel VALUES 'Jiu' 'Craiova' 'Calea Bucuresti' 'DA' 'NU'
insert into Hotel VALUES 'Europa' 'Bucuresti' 'Selari' 'DA' 'DA'
insert into Hotel VALUES 'Gorj' 'Targu Jiu' 'Trandafirilor' 'DA' 'NU'
insert into Hotel VALUES 'Sport' 'Craiova' 'Bd. Carol' 'DA' 'NU'
insert into Hotel VALUES 'Continental' 'Timisoara' 'Bucuresti' 'DA' 'DA'
insert into Hotel VALUES 'Iaki' 'Mamaia' 'Mamaia' 'DA' 'DA'
insert into Hotel VALUES 'Moon' 'Timisoara' 'Lalelelor' 'DA' 'DA'
insert into Hotel VALUES 'Ana' 'Iasi' 'Gh Duca' 'DA' 'NU'
insert into Hotel VALUES 'Carpati' 'Predeal' 'Plopilor' 'DA' 'DA'
insert into Hotel VALUES 'Aurora' 'Brasov' '9 Mai' 'DA' 'NU'
insert into Hotel VALUES 'Golden' 'Craiova' 'B Ionescu' 'DA' 'DA'
insert into Hotel VALUES 'Euphoria' 'Sibiu' 'Pietii' 'NU' 'DA'
insert into Hotel VALUES 'Doina' 'Predeal' 'Plopilor' 'DA' 'NU'
insert into Hotel VALUES 'President' 'Sibiu' 'A. I. Cuza' 'DA' 'DA'
insert into Rezervare VALUES 'Popescu' 'Ion' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Carpati' 'DA',NULL)
insert into Rezervare VALUES 'Patrut' 'Ionut' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'Manole' 'Madalina' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'Alba' 'Jessica' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'SUA' 'Iaki',NULL,'DA'
insert into Rezervare VALUES '
insert into Rezervare VALUES 'Jackson' 'Curtis' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'SUA' 'Moon',NULL,'DA'
insert into Rezervare VALUES 'Popeci' 'Bogdan' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Doina' 'DA',NULL)
insert into Rezervare VALUES 'Andrei' 'Andrei' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Europa' 'NULL' 'DA'
insert into Rezervare VALUES 'Stefan' 'Vasile' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'Georgescu' 'Elena' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'Cretu' 'Laura' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'Vouiton' 'Louis' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Franta' 'Euphoria',NULL,'DA'
insert into Rezervare VALUES 'Popescu' 'Georgel' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Doina' 'DA',NULL)
insert into Rezervare VALUES 'Ionel' 'Marius' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'James' 'Jay' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'Vaduva' 'Ovidiu' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Sport' 'DA',NULL)
insert into Rezervare VALUES 'Nita' 'Livia' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Sport' 'DA',NULL)
insert into Rezervare VALUES 'Mateita' 'Camelia' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Sport' 'DA',NULL)
insert into Rezervare VALUES 'Nicolae' 'Marin' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Gorj' 'DA',NULL)
insert into Rezervare VALUES 'Nedelcu' 'Diana' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Jiu' 'DA',NULL)
insert into Rezervare VALUES 'Ion' 'Ion' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Parang' 'DA',NULL)
insert into Rezervare VALUES 'Ciobanu' 'Alin' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'Pitea' 'Laura' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Gorj' 'DA',NULL)
insert into Rezervare VALUES 'Deaconu' 'Oana' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'Romania' 'Iaki' 'DA',NULL)
insert into Rezervare VALUES 'Andrei' 'Vasile' CONVERT smalldatetime 103 CONVERT smalldatetime 103 '
insert into Rezervare VALUES 'Willis' 'Bruce' CONVERT smalldatetime 103 CONVERT smalldatetime 103 'SUA' 'Golden',NULL,'DA'
insert into Tarif VALUES 'Parang' ,NULL, ,NULL)
insert into Tarif VALUES 'Jiu' ,NULL, ,NULL)
insert into Tarif VALUES 'Europa'
insert into Tarif VALUES 'Gorj' ,NULL, ,NULL)
insert into Tarif VALUES 'Sport' ,NULL, ,NULL)
insert into Tarif VALUES 'Continental'
insert into Tarif VALUES 'Iaki'
insert into Tarif VALUES 'Moon'
insert into Tarif VALUES 'Ana' ,NULL, ,NULL)
insert into Tarif VALUES 'Carpati'
insert into Tarif VALUES '
insert into Tarif VALUES 'Golden'
insert into Tarif VALUES 'Euphoria',NULL, ,NULL,
insert into Tarif VALUES 'Doina' ,NULL, ,NULL)
insert into Tarif VALUES 'President'
go
select
from Salariati
select
from Hotel
select
from Rezervare
select
from Tarif
lll Selecturi
select
from Salariati
select
from Hotel
select
from Rezervare
select
from Tarif
use Andrei
select Nume_hotel
from Hotel
order by Cod_hotel
use Andrei
select nume_hotel nr_salariati
from salariati
where nr_salariati>
use Andrei
select Nume_hotel nume_oras
from hotel
where obt_camera_lux 'DA'
use Andrei
select nume prenume nume_hotel
from rezervare
where nr_zile>
use Andrei
select nume_hotel Nr_camere_lux
from tarif
where Tarif_camera_lux<
order by nr_camere_lux
IV.Uniri
/*left outer join-uri*/
use turism
select *
from rute r left outer join excursii e
on r.cod_excursii=e.cod_excursii
use turism
select *
from excursii e left outer join rute r
on e.cod_excursii=r.cod_excursii
use turism
select *
from autocare a left outer join administrare b
on a.nr_locuri=b.nr_locuri
/*cross join*/
use turism
select *
from administrare a cross join autocare b
where a.nr_locuri=b.nr_locuri
order by b.nr_locuri
/*Right outer join --> Unire*/
use turism
select
from autocare b RIGHT OUTER JOIN administrare a
on b.nr_locuri=a.nr_locuri
order by a.nr_locuri
/* right outer join --> Unire*/
use turism
SELECT *
FROM rute r RIGHT OUTER JOIN excursii e
ON e.cod_excursii=r.cod_excursii
ORDER BY r.cod_excursii
/* right outer join --> Unire*/
use turism
SELECT *
FROM excursii e RIGHT OUTER JOIN rute r
on e.cod_excursii=r.cod_excursii
WHERE e.cod_excursii=r.cod_excursii
ORDER BY e.cod_excursii
----- ----- -----equi Join----- ----- --------
/* Equi Unire */
SELECT *
FROM CAMERE C,INREGISTRARE I
WHERE C.CAMERA=I.CAMERA
/* Equi Unire */
SELECT *
FROM administrare a, autocare b
WHERE a.nr_locuri=b.nr_locuri
order by b.nr_locuri
/* Equi Unire*/
SELECT *
FROM rute r INNER JOIN administrare a
on r.cod_excursii=a.cod_excursii
order by a.cod_excursii
/*SELF-JOIN:
use turism
SELECT *
FROM administrare a,excursii e
WHERE a.cod_excursii=e.cod_excursii
use turism
SELECT *
FROM rute r JOIN excursii e
ON r.cod_excursii=e.cod_excursii
use turism
SELECT *
FROM administrare a JOIN autocare b
ON a.nr_locuri=b.nr_locuri
V. VIEW-uri
/* Crearea unui view */
CREATE VIEW oana123
AS SELECT *
FROM administrare
/*!EXECUTE INTAI CREARE DE VIEW SI APOI URMATOAREA INTEROGARE... :P*/
SELECT *
FROM oana123
/* Creare view */
CREATE VIEW oana12
AS
SELECT distinct b.nr_locuri,a.nr_ghiduri
FROM administrare a,autocare b
WHERE b.nr_locuri>3 and a.nr_ghiduri>1
SELECT * FROM oana12
/* Creare view(contzine unire d tabele) */
CREATE VIEW oana21
AS
select r.cod_excursii,opt_hotel,oras1,nr_oras
FROM excursii e,rute r
WHERE r.cod_excursii>2 and opt_hotel='da' and oras1='bucuresti' and nr_oras>4
SELECT * FROM oana21
VI.Proceduri
/*proceduri stocate*/
if exists (select name from sysobjects
where name = 'proc1' and type = 'PR')
drop procedure proc1
go
use turism
go
create procedure proc1 as
select nume,prenume,functie
from salariati
go
exec proc1
if exists (select name from sysobjects
where name = 'proc2' and type = 'PR')
drop procedure proc2
go
use turism
go
create procedure proc2 as
select nr_oras,opt_cazare,opt_hotel,opt_mese
from excursii
go
exec proc2
if exists (select name from sysobjects
where name = 'proc3' and type = 'Pr')
drop procedure proc3
go
use turism
go
create procedure proc3 as
select oras1,oras2,oras3,oras4,oras7
from rute
go
exec proc
VII.Trigger-e
IF EXISTS SELECT name FROM sysobjects WHERE name 'trig_mesaj' AND type 'TR'
DROP TRIGGER trig_mesaj
GO
CREATE TRIGGER trig_mesaj
ON administrare
FOR INSERT UPDATE
AS RAISERROR 'S-a executat un INSERT sau un UPDATE' 16 1
GO
INSERT INTO administrare values
Go
UPDATE administrare SET nr_locuri_libere 18
IF EXISTS SELECT name FROM sysobjects
WHERE name 'trig1' AND type 'TR'
DROP TRIGGER trig1
GO
CREATE TRIGGER trig1
ON salariati
FOR INSERT UPDATE
AS
DECLARE @@num varchar 20 @@sal int
SELECT @@num i nume
@@sal i salariu
FROM salariati s inserted i
WHERE s nume i nume
IF @@sal <400 )and(@@num like 'POPECI'
BEGIN
RAISERROR 'Salariatul POPECI are salariu <300' 16 1
ROLLBACK TRANSACTION
END
ELSE
BEGIN
RAISERROR 'Salariatul cu nume=%s are salariu=%d'
16 1 @@num @@sal
ROLLBACK TRANSACTION
END
go
INSERT INTO Salariati values 'POPECI' 'FLORINA' 'ghid'
Go
INSERT INTO Salariati values 'BALTOIU' 'NICOLETA' 'sofer'
go
IF EXISTS SELECT name FROM sysobjects
WHERE name 'trig4' AND type 'TR'
DROP TRIGGER trig4
GO
CREATE TRIGGER trig4
ON autocare
FOR UPDATE
AS
IF UPDATE marca
RAISERROR 'S-a modificat campul marca' 16 1
ELSE
RAISERROR 'S-a modificat alt camp al tabelei autocare' 16 1
go
UPDATE autocare SET marca 'BMW' WHERE stele_autocar
go
SELECT FROM autocare
go
UPDATE autocare SET marca 'BMW' WHERE stele_autocar
go
select from autocare
|
