· 9 years ago · Nov 30, 2016, 10:56 AM
1/* 1) The database of the project contains at least 7 tables
2 2) Install the database on SQL Server by using SQL expressions
3 -------> file housing_rentals.sql
4Xong rồi */
5
6-- 3) Create about 30 queries (nested subquery, query with aggregate funstions, update query,…)
7 /* 8 update queries tÆ°Æ¡ng ứng vá»›i 8 table --> SÆ N
8 7 queries bình thÆ°á»ng --> SÆ N
9 15 queries nested, with aggregate functions... --> THẢO + SÆ N
10 PS: trước các queries nhớ comment nội dung của queries
11 VD: get information of all emplyee. */
12 -- 8 cái updates.
13 -- update district name
14 UPDATE District
15 SET district_ID='CG', region_ID='HN'
16 WHERE district_Name='To Hieu';
17 -- update employee email
18 UPDATE Employee
19 SET employee_email = 'JuicyJ10@gmail.com'
20 WHERE employee_ID= 'EM0001';
21 -- update House quality
22 UPDATE House
23 SET house_description = CONCAT(house_description, ', have wooden floor'), price='2000.000'
24 WHERE house_ID='H00011';
25 -- update House type
26 UPDATE House_type
27 SET nb_of_bedrooms = 5
28 WHERE house_type_ID = 'VL3';
29 -- update Owner phone number
30 UPDATE Owner
31 SET owner_phone = '8071966539'
32 WHERE owner_ID = ''OW0019;
33 -- Extend contract length
34 UPDATE Renting_contract
35 SET date_to = CAST(N'2017-07-16' AS Date)
36 WHERE contract_ID = 'RC0016' house_ID = 'H00021' tenant_ID = 'TE0017';
37
38 -- 7 queries bình thÆ°á»ng.
39 --1. list all house in Quan 2
40 select a.house_ID, c.house_type_Descrition, a.house_description, a.price
41 from House a, District b, House_type c
42 where a.district_ID = b.district_ID
43 and a.house_type_ID = c.house_type_ID
44 and b.district_Name = 'Quan 2';
45 /* queries made by SOn */
46 --2. all contract signed this month
47 select *
48 from Renting_contract
49 where date_from >= '2016-11-01';
50 --3. show all houses which are villas
51 select a.house_ID, a.house_type_ID, a.owner_ID, a.district_ID, a.house_description, a.price, b.nb_of_bedrooms, b.nb_of_bathrooms
52 from House a, House_type b
53 where a.house_type_ID = b.house_type_ID
54 and b.house_type_ID like '%VL%';
55 --4. all houses have more than 3 bathrooms
56 select a.house_ID, a.house_type_ID, a.owner_ID, a.district_ID, a.house_description, a.price, b.nb_of_bedrooms, b.nb_of_bathrooms
57 from House a, House_type b
58 where a.house_type_ID = b.house_type_ID
59 and b.nb_of_bathrooms > 3;
60 --5. show all houses owned by a specific owner
61 select b.owner_ID, b.owner_name, b.owner_phone, a.house_ID, a.house_type_ID, a.owner_ID, a.district_ID, a.house_description, a.price
62 from House a, Owner b
63 where a.owner_ID = b.owner_ID
64 and b.owner_name like '%Denita%';
65 --6. show contract which last than 1 year
66 select *
67 from contract
68 where datediff(day, date_from, date_to) > 365;
69 --7. show all houses has been rent more than 1 time
70 select a.house_ID, COUNT(a.house_ID) as rent_time, b. a.house_type_ID, a.owner_ID, a.district_ID, a.house_description, a.price
71 from House a NATURAL JOIN Renting_contract b
72 where rent_time >= 2;
73 --8. show all houses in Ha Noi
74 select a.house_ID, b.district_Name, c.region_Name, a.house_description, a.price
75 from House a, District b, Region c
76 where a.district_ID = b.district_ID
77 and b.region_ID = c.region_ID
78 and c.region_Name = '%Ha Noi%';
79
80 -- 15 queries special.
81 --1. Rank employees by descending number of contracts successful obtained --
82 select a.employee_ID, b.employee_name, count(a.contract_ID) as successful_contracts
83 from Renting_contract a, Employee b
84 where a.employee_ID = b.employee_ID
85 group by a.employee_ID, b.employee_name
86 order by successful_contracts desc;
87
88 --2. Employee who obtain no contract over 1 year
89 select *
90 from Employee
91 where employee_ID not in (select employee_ID from Renting_contract)
92 and employee_dateStart <= '2015-11-16';
93
94 --3. Owners who haven't had his/her houses rented
95 select *
96 from House natural join Owner
97 where house_ID not in (select house_ID from Renting_contract);
98
99 --4. Tenants who have rented more than 2 different houses.
100 select b.tenant_ID, COUNT(b.contract_ID) as houses_rented
101 from Tenant a, Renting_contract b
102 where b.tenant_ID = a.tenant_ID
103 group by b.tenant_ID
104 having COUNT(b.contract_ID) >= 2;
105
106 --5. Top 5 most expensive villa in Quan 3, Ho Chi MInh
107 select top 5 *
108 from House
109 where district_ID = 'Q3' and house_type_ID like '%VL%'
110 order by price desc;
111
112 --6. Top 5 cheapest apartment in Thanh Xuan, Ha Noi
113 select top 5 *
114 from House
115 where district_ID = 'TX' and house_type_ID like '%AP%'
116 order by price asc;
117
118 --7. All apartment have 3 bedrooms,rank from price ascending.
119 select *
120 from House
121 where house_type_ID in (select house_type_ID
122 from House_type
123 where house_type_ID like '%AP%'
124 and nb_of_bedrooms = 3)
125 order by price;
126
127 --8. All apartment have no furniture and have area of 100 square meter.
128 select *
129 from House
130 where house_description like '%no furniture%'
131 and house_type_ID in (select house_type_ID
132 from House_type
133 where house_type_Descrition like '%100%');
134
135-- 4) Create about 5 procedures/functions; 3 triggers --> THẢO
136 DROP PROCEDURE IF EXISTS updateRentingContractDateStartEnd;
137 create procedure updateRentingContractDateStartEnd(
138 in n_contract_ID VARCHAR(10),
139 in n_date_from DATE,
140 in n_date_to DATE
141 )
142 update Renting_contract
143 set date_from = n_date_from, date_to = n_date_to
144 where contract_ID = n_contract_ID;
145
146 DROP PROCEDURE IF EXISTS searchFreeHouseByDate;
147 CREATE PROCEDURE searchFreeHouseByDate(
148 IN inputDate DATE
149 )
150 SELECT *
151 FROM House
152 WHERE house_ID NOT IN( SELECT house_ID
153 FROM Renting_contract
154 WHERE inputDate>=date_from
155 and inputDate<=date_to);
156
157 DROP PROCEDURE IF EXISTS listHouseByRegionName;
158 create procedure listHouseByRegionName(
159 in n_region_name VARCHAR(20)
160 )
161 select a.*
162 from House a, Region b, District c
163 where b.region_Name = n_region_name
164 and b.region_ID = c.region_ID
165 and a.district_ID = c.district_ID;
166
167-- 5) Create 3 users and give some rights for each user --> SÆ N
168
169-- 6) Connect SQL database to other programing languages, such as Java, ASP,… (webform) or C++, C#,… (winform)
170
171-- 7) Create the main interafe for the program
172
173-- 8) Create some forms for entering new tuples, delete tuples, find some tuples that satify the entering conditions.
174
175-- 9) Other functions…
176
177-- 10) Writing the project report: Using MS Word or Latex: --> SÆ N a,b
178
179 -- a. Describe the motivation of your project: Why you choose this project?
180
181 -- b. Describe the database design: Relation, datatype for each attribute (draw a table for this), show the diagram
182
183 -- c. Copy of all queries’ code
184
185 -- d. Describe the step how to connect the SQL database to other programing language
186
187 -- e. Describe all the functionalities of your applications
188
189 -- f. Captures some program interfaces and explain the picture
190
191 -- g. Conclusion
192
193 -- h. Reference material.