vYMZnNeJ

1drop table dependent;
2drop table works_on;
3drop table employee;
4drop table project;
5drop table dept_locations;
6drop table department;
7
8Create database lesson5; 
9
10use lesson5;
11
12create table department (
13	dname varchar(16) not null,
14	dnumber int not null,
15	mgr_ssn int not null,
16	mgr_start_date date not null,
17	primary key(dnumber)
18);
19
20create table dept_locations (
21	dnumber int not null,
22	dlocation varchar(16) not null,
23	primary key(dnumber, dlocation),
24	foreign key(dnumber) references department(dnumber)
25);
26
27create table employee (
28	fname varchar(16) not null,
29	minit varchar(1) not null,
30	lname varchar(32) not null,
31	ssn int not null,
32	bdate date not null,
33	address varchar(32),
34	sex char(1) not null check(sex in ('M', 'F')),
35	salary int not null,
36	super_ssn int,
37	dno int not null,
38	primary key(ssn),
39	foreign key(dno) references department(dnumber),
40	foreign key(super_ssn) references employee(ssn)
41);
42
43create table dependent (
44	essn int not null,
45	dependent_name varchar(16) not null,
46	sex char(1) not null check(sex in ('M', 'F')),
47	bdate date not null,
48	relationship varchar(12) not null,
49	primary key(essn, dependent_name),
50	foreign key(essn) references employee(ssn)
51);
52
53create table project (
54	pname varchar(16) not null,
55	pnumber int not null,
56	plocation varchar(16) not null,
57	dnum int not null,
58	primary key(pnumber),
59	foreign key(dnum, plocation) references dept_locations(dnumber, dlocation)
60);
61	
62create table works_on (
63	essn int not null,
64	pno int not null,
65	hours decimal(6,2),
66	primary key(essn, pno),
67	foreign key(essn) references employee(ssn),
68	foreign key(pno) references project(pnumber)
69);
70
71insert into department values('Research', 5, 333445555, '1988-05-22');
72insert into department values('Administration', 4, 987654321, '1995-01-01');
73insert into department values('Headquarters', 1, 888665555, '1981-06-19');
74insert into dept_locations values(1, 'Houston');
75insert into dept_locations values(4, 'Stafford');
76insert into dept_locations values(5, 'Bellaire');
77insert into dept_locations values(5, 'Sugarland');
78insert into dept_locations values(5, 'Houston');
79insert into project values('ProductX', 1, 'Bellaire', 5);
80insert into project values('ProductY', 2, 'Sugarland', 5);
81insert into project values('ProductZ', 3, 'Houston', 5);
82insert into project values('Computerization', 10, 'Stafford', 4);
83insert into project values('Reorganization', 20, 'Houston', 1);
84insert into project values('Newbenefits', 30, 'Stafford', 4);
85
86insert into employee values('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1);
87insert into employee values('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5);
88insert into employee values('John', 'B', 'Smith', 123456789, '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5);
89insert into employee values('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4);
90insert into employee values('Ahmad', 'V', 'Jabbar', 987987987, '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4);
91insert into employee values('Alicia', 'J', 'Zelaya', 999887777, '1968-01-19', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4);
92insert into employee values('Ramesh', 'K', 'Narayan', 666884444, '1962-09-15', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5);
93insert into employee values('Joyce', 'A', 'English', 453453453, '1972-07-31', '5631 Rice Houston, TX', 'F', 25000, 333445555, 5);
94
95insert into dependent values(333445555, 'Alice', 'F', '1986-04-05', 'Daughter');
96insert into dependent values(333445555, 'Theodore', 'M', '1983-10-25', 'Son');
97insert into dependent values(333445555, 'Joy', 'F', '1958-05-03', 'Spouse');
98insert into dependent values(987654321, 'Abner', 'M', '1942-02-28', 'Spouse');
99insert into dependent values(123456789, 'Michael', 'M', '1988-01-04', 'Son');
100insert into dependent values(123456789, 'Alice', 'F', '1988-12-30', 'Daughter');
101insert into dependent values(123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse');
102
103insert into works_on values(123456789, 1, 32.5);
104insert into works_on values(123456789, 2, 7.5);
105insert into works_on values(666884444, 3, 40.0);
106insert into works_on values(453453453, 1, 20.0);
107insert into works_on values(453453453, 2, 20.0);
108insert into works_on values(333445555, 2, 10.0);
109insert into works_on values(333445555, 3, 10.0);
110insert into works_on values(333445555, 10, 10.0);
111insert into works_on values(333445555, 20, 10.0);
112insert into works_on values(999887777, 30, 30.0);
113insert into works_on values(999887777, 10, 10.0);
114insert into works_on values(987987987, 10, 35.0);
115insert into works_on values(987987987, 30, 5.0);
116insert into works_on values(987654321, 30, 20.0);
117insert into works_on values(987654321, 20, 15.0);
118insert into works_on values(888665555, 20, NULL);
119
120DROP VIEW IF EXISTS myView;
121
122CREATE View myView AS 
123SELECT project.pname, department.dname, COUNT(works_on.essn), SUM(works_on.hours)
124FROM project
125join works_on on project.pnumber = works_on.pno
126join department on project.dnum = department.dnumber
127Group by project.pname;
128
129SELECT * FROM myView;
130
131DROP VIEW IF EXISTS myView2;
132
133CREATE View myView2 AS 
134SELECT project.pname, department.dname, COUNT(works_on.essn), SUM(works_on.hours)
135FROM project
136join works_on on project.pnumber = works_on.pno
137join department on project.dnum = department.dnumber
138Group by project.pname
139having count(works_on.essn) > 2;
140
141SELECT * FROM myView2;