SQL/MainCode.sql at main · metasina3/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118

CREATE TABLE customer (
  cust_id INTEGER PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  family VARCHAR(30) NOT NULL,
  address  VARCHAR(30) NOT NULL
);


CREATE TABLE room(
    room_id VARCHAR(30) NOT NULL PRIMARY KEY,
    stage INTEGER NOT NULL,
    bed_numer INTEGER NOT NULL,
    amount INTEGER NOT NULL
);


CREATE TABLE employ(
    emp_id VARCHAR(30) NOT NULL PRIMARY KEY,
    emp_name VARCHAR(30) NOT NULL,
    salary INTEGER NOT NULL
);


CREATE TABLE reserve(
     cust_id INTEGER NOT NULL,
     room_id VARCHAR(30) NOT NULL,
     FOREIGN KEY (cust_id) REFERENCES customer(cust_id) ,
     FOREIGN KEY (room_id) REFERENCES room(room_id) ,
     PRIMARY KEY (cust_id,room_id)
);

CREATE TABLE services(
     emp_id VARCHAR(30) NOT NULL,
     room_id VARCHAR(30) NOT NULL,
     FOREIGN KEY (emp_id) REFERENCES employ(emp_id) ,
     FOREIGN KEY (room_id) REFERENCES room(room_id) ,
     PRIMARY KEY (emp_id,room_id)
);


insert into customer values (1,'ali','shahedi','tehran');
insert into customer values (2,'sara','ahmadipor','tabriz');
insert into customer values (3,'reza','masoodi','tehran');
insert into customer values (4,'armin','mehrshadi','mazandaran');
insert into customer values (5,'nika','amiri','esfahan');
insert into customer values (6,'maryam','kashani','kashan');
insert into customer values (7,'ali','tehrani','tehran');


insert into room values('r1',1,2,3000);
insert into room values('r2',1,2,3000);
insert into room values('r3',2,3,2700);
insert into room values('r4',2,2,2400);
insert into room values('r5',3,1,1200);
insert into room values('r6',3,3,4000);
insert into room values('r7',4,1,2000);
insert into room values('r8',4,2,34000);
insert into room values('r9',5,3,5000);
insert into room values('r10',5,2,2000);

insert into  reserve values(1,'r1');
insert into  reserve values(2,'r3');
insert into  reserve values(3,'r2');
insert into  reserve values(4,'r5');
insert into  reserve values(5,'r7');
insert into  reserve values(6,'r10');
insert into  reserve values(7,'r6');

insert into employ values('em1','mohsen',12000);
insert into employ values('em2','negin',22000);
insert into employ values('em3','sara',7000);
insert into employ values('em4','ali',9000);
insert into employ values('em5','reza',10000);


insert into services values('em2','r3');
insert into services values('em1','r1');
insert into services values('em3','r2');
insert into services values('em4','r5');
insert into services values('em5','r6');
insert into services values('em2','r7');
insert into services values('em2','r4');


-- اسامی مسافران هتل

select * from customer;

-- اطلاعات مسافران هتل که تهرانی هستند
select * from customer where address='tehran';


--  ارزان ترین اتاق
select * from room
where amount <=(select min(amount) from room)
;

-- اتاق های رزرو نشده
select * from room
where room_id not in(
select room_id from reserve
);

-- تعداد اتاق های در حال فعالیت کارمندان
select emp_id,count(*) as number_room from services
GROUP BY emp_id
;


-- اطلاعات مسافر که اتاق شماره 3 را رزرو کرده است

select customer.cust_id,name,family from customer right join reserve
on customer.cust_id=reserve.cust_id
where room_id='r3';