· 10 years ago · Dec 18, 2015, 08:51 PM
1use urbany;
2
3SET SQL_SAFE_UPDATES = 0;
4
5DROP TABLE IF EXISTS jobs;
6DROP TABLE IF EXISTS skills;
7DROP TABLE IF EXISTS companyaddresses;
8DROP TABLE IF EXISTS education;
9DROP TABLE IF EXISTS sharedresume;
10DROP TABLE IF EXISTS schoolWentTo;
11DROP TABLE IF EXISTS schools;
12DROP TABLE IF EXISTS company;
13DROP TABLE IF EXISTS resume;
14DROP TABLE IF EXISTS users;
15DROP TABLE if exists gotskill;
16
17CREATE TABLE users
18(
19FirstName varchar(50) NOT NULL,
20LastName varchar(50) not null,
21email varchar(50) NOT NULL UNIQUE,
22user_id int Auto_increment primary key,
23password varchar(50) Not Null,
24address varchar(250)
25);
26CREATE TABLE education
27(
28gpa varchar(7),
29
30user_id int not null,
31startdate date,
32enddate date,
33
34foreign key (user_id) references users(user_id)
35);
36
37create TABLE schools
38(
39name varchar(50) not null unique,
40address varchar(250)not null,
41school_id int Auto_increment primary key
42
43);
44create table schoolWentTo
45(
46 uemail varchar(50) not null,
47 user_id int not null,
48 school_id int not null,
49 startdate date,
50 enddate date,
51 foreign key(school_id) references schools(school_id),
52 -- foreign key(uemail) references users(email),
53 foreign key (user_id) references users(user_id)
54
55);
56create table company
57(
58name varchar(50) not null unique,
59c_id int Auto_increment primary key
60);
61create table companyaddresses
62(
63name varchar(50) not null,
64address varchar(50) not null,
65c_id int not null,
66foreign key(name) references company(name),
67foreign key(c_id) references company(c_id)
68);
69create table jobs
70(
71uemail varchar(50)not null,
72user_id int not null,
73c_id int not null,
74company varchar(50) not null,
75profile varchar(250),
76start date,
77ending date,
78
79foreign key (user_id) references users(user_id),
80foreign key(c_id) references company(c_id),
81foreign key(uemail) references users(email),
82foreign key(company) references company(name)
83);
84create table skills
85(
86name varchar(50) not null,
87description varchar (250) not null,
88skill_id int Auto_increment primary key
89);
90create table resume
91(
92uemail varchar(50),
93user_id int not null,
94resume_id int NOT NULL AUTO_INCREMENT primary key,
95foreign key(uemail) references users(email),
96foreign key (user_id) references users(user_id)
97
98);
99create table sharedresume
100(
101uemail varchar(50),
102user_id int not null,
103
104company varchar(50),
105resume_id int,
106foreign key(uemail) references users(email),
107foreign key(company) references company(name),
108foreign key(resume_id) references resume(resume_id),
109foreign key (user_id) references users(user_id)
110
111);
112create table gotskill
113(user_id int not null,
114skill_id int not null);
115
116SELECT * FROM users WHERE email='bill@gmail.com';
117
118insert into users(FirstName,LastName, email, password,address)
119values ('larck', 'how', 'cool1@gmail.com','buttetscotch1','qwerty road'),
120('steve', 'bill', 'jay2@gmail.com','buttetscotch2','asdfg road'),
121('bill', 'harden', 'harden3@gmail.com','buttetscotch3','zxcvb road'),
122('larse', 'andies', 'bill4@gmail.com','buttetscotch4','stanford street'),
123('yellow', 'rogers', 'yrogers5@aol.com','buttetscotch5','cotati avecompany');
124
125
126insert into schools(name,address)
127values('harvard','1234 tigers st MA USA'),('stanford','3333 pizza st ca USA'),('cal','1234 burger st ca USA'),
128('MIT','3333 tigers st MA USA');
129
130insert into education(gpa,user_id)
131values('5',1),('7',4),('3',2),
132('2',4);
133insert into company(name)
134values('google'),('tiger'),('bing'),('yahoo');
135
136insert into companyaddresses(name,address,c_id)
137values('google','1234 hickup st ca',1),('tiger','111 jay st ca',2),('bing','555 pizza ave ca',3),
138('yahoo','333 locks st ka',4),('google','999 hickup st ja',1),('tiger','777 jay st ca',2),('bing','344 pizza ave ja',3),
139('yahoo','666 locks st ma',4);
140
141insert into schoolWentTo(uemail,school_id,startdate,enddate,user_id)
142values('cool1@gmail.com',1,'2009-09-01','2012-05-01',1),('cool1@gmail.com',4,'2006-09-01','2009-05-01',1),
143('jay2@gmail.com',1,'2012-09-01','2016-05-01',2),('bill@4gmail.com',2,'2010-09-01','2014-05-01',4);
144
145insert into jobs(uemail,company, profile,start,ending,user_id,c_id)
146values('cool1@gmail.com','google','programing1 and stuff with pizza','2009-09-01','2012-05-01',1,1),
147('cool1@gmail.com','yahoo','programing2 and stuff with pizza','2012-09-01','2014-05-01',1,4),
148('jay2@gmail.com','bing','programing3 and stuff with pizza','1999-09-01','2012-05-01',2,3),
149('bill4@gmail.com','tiger','programing4 and stuff with pizza','1991-09-01','2012-05-01',4,2);
150
151insert into skills(name, description)
152values('lumberjacking','chopping wood and studd int the woods'),
153('compters','chopping wood and studd int the woods'),
154('jessus','relugion andchopping wood and studd int the woods'),
155('korrean','korrean wood chopping wood and studd int the woods');
156
157insert into sharedresume(uemail,company,user_id)
158values('cool1@gmail.com','google',1),('harden3@gmail.com','google',3),('cool1@gmail.com','bing',1),('cool1@gmail.com','tiger',1);
159
160insert into resume(uemail,user_id)
161values('cool1@gmail.com',1),('harden3@gmail.com',3),('bill4@gmail.com',4),('jay2@gmail.com',2);
162
163SELECT COUNT(*) FROM users;
164
165SELECT MAX(gpa) FROM education WHERE user_id = 1;
166
167SELECT MIN(gpa) FROM education WHERE user_id = 1;
168
169SELECT AVG(gpa) FROM education WHERE user_id = 1;
170
171SELECT SUM(gpa)/COUNT(*) AS avg_gpa FROM education WHERE user_id = 1;
172
173/*
174SELECT u.LastName, SUM(gpa)/COUNT(*) AS avg_gpa FROM education e
175JOIN users u ON u.email = e.uemail
176WHERE u.email = 'bill@gmail.com';
177
178
179SELECT c.name, COUNT(company) as numberoftimes FROM sharedresume as sr
180join company c on sr.company = c.name
181where sr.company = 'google';
182
183
184SELECT s.name, SUM(gpa)/COUNT(*) AS avg_gpa FROM education e
185-- JOIN users u ON u.email = e.uemail
186JOIN schools s ON e.sname = s.name
187WHERE s.name = 'stanford';
188
189select FirstName, LastName from users ORDER by LastName, FirstName;
190
191select FirstName, LastName from users order
192by LastName desc, FirstName asc;
193*/
194select name from company order by name desc;
195
196
197
198select name from schools order by name desc;
199
200select count(*) from users;
201
202select count(*) from company;
203
204select count(*) from schools;
205-- 9
206
207create or replace view company_gpa as
208 select j.company, avg(e.gpa)as avgGPA, j.c_id
209 from jobs j
210join education e
211where j.user_id= e.user_id -- and gpa < 4
212group by company;
213
214create or replace view UserSchoolView as
215select email,s.name,u.FirstName,u.LastName, w.startdate,w.enddate,s.address, e.gpa from schoolWentTo w
216 join schools s
217 join users u
218 join education e
219where s.school_id = w.school_id and w.user_id= u.user_id
220order by s.name;
221
222
223create or replace view UserJobView as
224select email,u.FirstName,u.LastName,company,a.address, j.profile, start, ending ,u.user_id, c.c_id from jobs j
225join users u
226join company c
227join companyaddresses a
228where u.email = uemail and company = c.name and c.c_id = a.c_id
229
230group by a.address
231order by email;
232
233select * from company_gpa ;
234
235select * from UserJobView;