第十天：
drop table km_data
create table km_data
(
mid int identity(1,1) primary key,
cluster_id int,
lat numeric(10,7) not null,
lng numeric(10,7) not null
);
drop table km_clusters
create table km_clusters
(
mid int identity(1,1) primary key,
lat numeric(10,7) not null,
lng numeric(10,7) not null
);
drop table km_clustersc
create table km_clustersc
(
mid int identity(1,1) primary key,
lat numeric(10,7) not null,
lng numeric(10,7) not null
);
INSERT INTO km_data VALUES(0,1.799+2,-1.2023+2)
INSERT INTO km_data VALUES(0,-1.2624,-1.5465)/*这是第二个数据*/
INSERT INTO km_data VALUES(0,1.9409+2,1.9863+2)
INSERT INTO km_data VALUES(0,0.0079083+2,0.48136+2)
INSERT INTO km_data VALUES(0,1.212+2,1.3274+2)
INSERT INTO km_data VALUES(0,1.2379+2,1.2341+2)
INSERT INTO km_data VALUES(0,-0.0077634+2,1.0215+2)
INSERT INTO km_data VALUES(0,0.25796+2,-0.0039445+2)
INSERT INTO km_data VALUES(0,2.0823+2,0.052854+2)
INSERT INTO km_data VALUES(0,0.8685+2,0.62557+2)
INSERT INTO km_data VALUES(0,1.3899+2,-0.18589+2)
INSERT INTO km_data VALUES(0,1.088+2,-0.055903+2)
INSERT INTO km_data VALUES(0,0.36453+2,2.4725+2)
INSERT INTO km_data VALUES(0,0.44043+2,1.0557+2)
INSERT INTO km_data VALUES(0,1.4437+2,-0.21732+2)
INSERT INTO km_data VALUES(0,0.050096+2,0.95877+2)
INSERT INTO km_data VALUES(0,1.7812+2,-0.12834+2)
INSERT INTO km_data VALUES(0,1.569+2,-0.34928+2)
INSERT INTO km_data VALUES(0,0.17829+2,0.7389+2)
INSERT INTO km_data VALUES(0,0.73439+2,1.9535+2)
INSERT INTO km_data VALUES(0,-0.87136,-1.3306)
INSERT INTO km_data VALUES(0,-0.34353,-1.8436)
INSERT INTO km_data VALUES(0,-2.1678,-0.50223)
INSERT INTO km_data VALUES(0,-1.4606,0.48849)
INSERT INTO km_data VALUES(0,-2.2132,-1.8468)
INSERT INTO km_data VALUES(0,-2.3194,-1.2463)
INSERT INTO km_data VALUES(0,-0.068782,-0.33698)
INSERT INTO km_data VALUES(0,-0.98876,-1.8542)
INSERT INTO km_data VALUES(0,-1.6451,-2.2013)
INSERT INTO km_data VALUES(0,-0.19427,-1.1199)
INSERT INTO km_data VALUES(0,-0.76837,-1.0653)
INSERT INTO km_data VALUES(0,-1.9898,-0.5147)
INSERT INTO km_data VALUES(0,0.33959,-1.5955)
INSERT INTO km_data VALUES(0,-0.7105,-1.1497)
INSERT INTO km_data VALUES(0,0.47892,-1.4348)
INSERT INTO km_data VALUES(0,0.13803,-1.0793)
INSERT INTO km_data VALUES(0,-1.6841,0.53515)
INSERT INTO km_data VALUES(0,-2.2919,-1.6065)
INSERT INTO km_data VALUES(0,-1.0729,-2.3474)
drop PROCEDURE kmains
CREATE PROCEDURE kmains
@k int
AS
declare @i int
set @i=0
declare cc cursor for select * from km_data
open cc
while @i<@k
begin
declare @xx numeric(10,7)
declare @yy numeric(10,7)
declare @id int
declare @c_id int
fetch next from cc into @id,@c_id,@xx,@yy
INSERT INTO km_clusters (lat, lng) values(@xx,@yy)
set @i=@i+1
end
close cc
deallocate cc
declare @basex numeric(10,7)
declare @basey numeric(10,7)
declare @cid int
declare @x numeric(10,7)
declare @y numeric(10,7)
declare @mid int
declare @mcid int
declare @mindistance numeric(10,7)
declare @mi int
declare @index int
set @index=1
while @index>0
begin
declare datac cursor for select * from km_data
open datac
fetch next from datac into @mid,@mcid,@x,@y
while @@FETCH_STATUS=0
begin
declare fin cursor for select * from km_clusters
open fin
fetch next from fin into @cid,@basex,@basey
set @mindistance = ((@x-@basex)*(@x-@basex)+(@y-@basey)*(@y-@basey))
set @mi=@cid
WHILE @@FETCH_STATUS=0
begin
if @mindistance < (@x-@basex)*(@x-@basex) + (@y-@basey)*(@y-@basey)
begin
set @mindistance = ((@x-@basex)*(@x-@basex)+(@y-@basey)*(@y-@basey))
set @mi = @cid
end
fetch next from fin into @cid,@basex,@basey
end
close fin
deallocate fin
update km_data set cluster_id=@mi where lat=@x and lng=@y
fetch next from datac into @mid,@mcid,@x,@y
end
/*declare @savex1 numeric(10,7)
declare @savey1 numeric(10,7)
declare @savex2 numeric(10,7)
declare @savey2 numeric(10,7)
declare mycursor cursor for select lat,lng from km_clusters
open mycursor
fetch next from mycursor into @savex1,@savey1
fetch next from mycursor into @savex2,@savey2
close mycursor
deallocate mycursor */
declare @isFinished int
set @isFinished=1
declare @save1 numeric(10,7)
declare @save2 numeric(10,7)
declare @save0 numeric(10,7)
declare @now1 numeric(10,7)
declare @now2 numeric(10,7)
declare cforsave cursor for select mid,lat,lng from km_clusters
open cforsave
fetch next from cforsave into @save0,@save1,@save2
while @@FETCH_STATUS=0
begin
select @now1=avg(lat) from km_data where cluster_id<>@save0
select @now2=avg(lng) from km_data where cluster_id<>@save0
print @now1
print @now2
print @save1
print @save2
print (power(@save1-@now1,2)+power(@save2-@now2,2))
if(power(@save1-@now1,2)+power(@save2-@now2,2) > 0.1)
set @isFinished=0
fetch next from cforsave into @save0,@save1,@save2
end
close cforsave
deallocate cforsave
if @isFinished=1
break;
/*insert into km_clustersc (select * from km_clusters)*/
update km_clusters set lat=(select avg(lat) from km_data where km_clusters.mid = cluster_id)
update km_clusters set lng=(select avg(lng) from km_data where km_clusters.mid = cluster_id)
close datac
deallocate datac
end
RETURN
GO
exec kmains 2
select * from km_data
select * from km_clusters
第九天：
作业1：
使用cursor完成查询1～3，要求将查询结果打印到屏幕
1.
declare c cursor for
select T.Name,T.[Year]
from Title as T,Author as A
where T.CallNumber = A.CallNumber and A.Lname = 'Churchill'
declare @book_name varchar(200)
declare @book_year varchar(20)
open c
fetch next from c into @book_name,@book_year
while @@FETCH_STATUS=0
begin
print('book name is ' + @book_name + ' book year is ' + @book_year);
fetch next from c into @book_name,@book_year
end
close c
deallocate c
2.
declare c cursor for
select T.Name
from Title as T
where T.CallNumber in
(select B.CallNumber from Member as M,Book as B where M.Memno = B.BorrowerMemno
and (M.Fname = 'John' or M.Fname = 'Susan'))
declare @book_name varchar(200)
open c
fetch next from c into @book_name
while @@FETCH_STATUS=0
begin
print('book name is ' + @book_name);
fetch next from c into @book_name
end
close c
deallocate c
3.
declare c cursor for
select M.Fname,M.Lname,M.MI,M.Memno
from Member as M
where
(
M.Memno
in
(select Book.BorrowerMemno from Book,Title where Book.CallNumber = Title.CallNumber and Title.Name = 'Iliad')
and
M.Memno
in
(select Book.BorrowerMemno from Book,Title where Book.CallNumber = Title.CallNumber and Title.Name = 'Odyssey'))
declare @first_name varchar(20)
declare @last_name varchar(20)
declare @mi varchar(10)
declare @memo varchar(20)
open c
fetch next from c into @first_name,@last_name,@mi,@memo
while @@FETCH_STATUS=0
begin
print('name is ' + @first_name +' ' + @last_name + ' mi is ' + @mi + ' memo is ' + @memo);
fetch next from c into @first_name,@last_name,@mi,@memo
end
close c
deallocate c
作业2：
1.
CREATE PROCEDURE get_salary_by_name2
@person_name varchar(20),
@salary int OUTPUT
AS
SELECT @salary = salary
FROM works
WHERE person_name = @person_name
RETURN
GO
2.
CREATE PROCEDURE get_manager_by_employees
@person_name varchar(20),
@manager_name varchar(20) OUTPUT
AS
SELECT @manager_name = manager_name
FROM managers
WHERE person_name = @person_name
RETURN
GO
declare @manager_name varchar(20);
exec get_manager_by_employees @person_name='Smith',@manager_name=@manager_name
print @manager_name;
3.
CREATE PROCEDURE get_employees_by_company_name1
@company_name varchar(20),
@mycursor cursor VARYING OUTPUT
AS
set @mycursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT person_name
FROM works
WHERE company_name = @company_name
open @mycursor
GO
declare @person_name varchar(20)
declare @c cursor
exec get_employees_by_company_name1 @company_name='First',@mycursor=@c output
fetch next from @c into @person_name
WHILE @@FETCH_STATUS=0
begin
print('person is ' + @person_name)
fetch next from @c into @person_name
end
close @c
deallocate @c
作业三：
1.
create trigger reorder_trigger
on inventory for update
as
if update(levels)
begin
declare @item varchar(20)
declare @level_new int
declare @level_old int
select @item=item from deleted
select @level_old=levels from deleted
select @level_new=levels from inserted
if @level_new<=(select levels
from minlevel where minlevel.item=@item)
and @level_old>(select levels
from minlevel where minlevel.item=@item )
insert into orders
select item,amount
from reorder
where reorder.item=@item
end
2.
CREATE TABLE Person(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime)
CREATE TABLE EmployeeTable(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN) REFERENCES Person (SSN))
CREATE VIEW Employee
AS
SELECT P.SSN as SSN, Name, Address, Birthdate,EmployeeID, Department,Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN
CREATE TABLE PersonDuplicates(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime)
CREATE TRIGGER Employee_TRIGGER ON Employee
INSTEAD OF INSERT
AS
BEGIN
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END
第五天：
DROP TABLE author4 ;
DROP TABLE hold4 ;
DROP TABLE TITLE4 ;
DROP TABLE MEMBER4 ;
DROP TABLE BOOK4;
CREATE TABLE TITLE4
(callnumber char(8) NOT NULL,
isbn char(8),
year datetime,
name char(14),
authorfname char(14),
authorlname char(14),
PRIMARY KEY (callnumber)
);
CREATE TABLE MEMBER4
(libid char(6) NOT NULL,
fname char(14),
lname char(14),
address char(20),
phonenumber char(10),
PRIMARY KEY (libid)
);
CREATE TABLE BOOK4
(book_id char(8) NOT NULL,
edition char(10),
status char(5),
borrowerid char(6),
borrowduedate datetime,
callnumber char(8),
PRIMARY KEY (book_id)
);
INSERT INTO TITLE4 VALUES ('AB123456', '12345687', '1997/01/01', 'Discrete Maths', 'Shadow', 'Jones');
INSERT INTO TITLE4 VALUES ('CD456756', '45454598', '1996/01/01', 'Intro to CS', 'Kelvin', 'Smith');
INSERT INTO TITLE4 VALUES ('EF121221', 'A4245678', '1998/01/01', 'Intro to CE', 'Stella', 'Potter');
INSERT INTO MEMBER4 VALUES ('123423', 'Jessy', 'Owens', '35 Side Pgh 15255', '4126874534');
INSERT INTO MEMBER4 VALUES ('A34565', 'Diana', 'Emily', '25 Side Pgh 15245', '4123456452');
INSERT INTO BOOK4 VALUES ('Q76.98.1', 'first', 'good', 'A34565', '1997-10-10', 'AB123456');
INSERT INTO BOOK4 VALUES ('Q76.95.6', 'third', 'ok', '123423', '1998-06-06', 'EF121221');
作业答案：
1INSERT INTO TITLE VALUES ('AB123456', '12345687', '1997/01/01', 'Discrete Maths', 'Shadow', 'Jones');
INSERT INTO TITLE VALUES ('CD456756', '45454598', '1996/01/01', 'Intro to CS', 'Kelvin', 'Smith');
INSERT INTO TITLE VALUES ('EF121221', 'A4245678', '1998/01/01', 'Intro to CE', 'Stella', 'Potter');
INSERT INTO MEMBER VALUES ('123423', 'Jessy', 'Owens', '35 Side Pgh 15255', '4126874534');
INSERT INTO MEMBER VALUES ('A34565', 'Diana', 'Emily', '25 Side Pgh 15245', '4123456452');
INSERT INTO BOOK VALUES ('Q76.98.1', 'first', 'good', 'A34565', '1997-10-10', 'AB123456');
INSERT INTO BOOK VALUES ('Q76.95.6', 'third', 'ok', '123423', '1998-06-06', 'EF121221');
INSERT INTO READ_BY VALUES ('AB123456','123423',3)
INSERT INTO READ_BY VALUES ('AB123456','123423',5)
INSERT INTO READ_BY VALUES ('CD456756','A34565',1)
INSERT INTO SUPPLIER VALUES ('001','WANGWENHAN','NEIMENGGU')
INSERT INTO SUPPLIER VALUES ('002','LIQIAN','TIANJINSHI')
INSERT INTO SUPPLIER VALUES ('003','LIZIWEI','SHANGHAISHI')
SELECT * FROM PURCHASE_ORDER
INSERT INTO PURCHASE_ORDER VALUES ('P001',3,'1995-01-16','1995-01-28','1995-02-24')
INSERT INTO PURCHASE_ORDER VALUES ('P002',6,'2008-08-08','2009-08-08','2009-09-09')
INSERT INTO ORDER4 VALUES ('AB123456','P001','001')
INSERT INTO ORDER4 VALUES ('CD456756','P001','002')
INSERT INTO ORDER4 VALUES ('CD456756','P002','002')
INSERT INTO ORDER4 VALUES ('CD456756','P002','001')
INSERT INTO ORDER4 VALUES ('AB123456','P002','001')
INSERT INTO ORDER4 VALUES ('AB123456','P001','002')
2
delete from title4
delete from member4
delete from book4
3.1
CREATE TABLE TITLE
(callnumber char(8) NOT NULL,
isbn char(8),
year datetime,
name char(14),
authorfname char(14),
authorlname char(14),
PRIMARY KEY (callnumber)
);
CREATE TABLE MEMBER
(libid char(6) NOT NULL,
fname char(14),
lname char(14),
address char(20),
phonenumber char(10),
PRIMARY KEY (libid)
);
CREATE TABLE BOOK
(book_id char(8) NOT NULL,
edition char(10),
status char(5),
borrowerid char(6),
borrowduedate datetime,
callnumber char(8),
PRIMARY KEY (book_id),
foreign key(callnumber) references title,
foreign key(borrowerid) references member
);
CREATE TABLE read_by(
callnumber char(8),
lib_id char(6),
timesread int,
PRIMARY KEY (callnumber, lib_id),
FOREIGN KEY (lib_id) REFERENCES member(libid),
FOREIGN KEY (callnumber) REFERENCES title(callnumber)
);
CREATE TABLE purchase_order(
ponum char(6),
qty int,
orderdate datetime,
duedate datetime,
receiveddate datetime,
PRIMARY KEY (ponum)
);
CREATE TABLE supplier(
supplier_id char(6),
name char(20),
address char(20),
PRIMARY KEY (supplier_id)
);
CREATE TABLE order4(
callnumber char(8),
ponum char(6),
supplier_id char(6)
PRIMARY KEY (callnumber, ponum, supplier_id),
FOREIGN KEY (ponum) REFERENCES purchase_order(ponum),
FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id),
FOREIGN KEY (callnumber) references title(callnumber)
);
3.2
ALTER TABLE title4 ADD CONSTRAINT ak unique(isbn,name)
ALTER TABLE title4 ADD CONSTRAINT ak2 check(isbn is not null)
ALTER TABLE title4 ADD CONSTRAINT ak3 check(name is not null)
3.3
alter table book4 add CONSTRAINT fk foreign key(callnumber) references title4
alter table readby4 add constraint fk1 foreign key(libid)
references member4
alter table ordered4 add constraint fk2 foreign key(callnumber) references title4
alter table ordered4 add constraint fk2 foreign key(ponum) references purchase_order4
alter table ordered4 add constraint fk2 foreign key(supplier_id) references supplier4
3.4
alter table member4 add Gender varchar(20)
alter table member4 add Join_date varchar(20)
3.5
alter table read_by4 add check(TimesRead >= 0)
5
5.1 Display the ID, first name, and joining date of each member whose ID begins with the letters "A%B" or "A&B"
and who joined the library no later than Nov. 30, 1997.
SELECT * FROM MEMBER
select libid,fname,join_date
from member
where (libid like 'A\%B%' escape '\' or libid like 'A&B%')
and join_date<='1997-10-30'
5.2 List the name, ISBN, and call number of each title, provided it has been ordered from more than one supplier and the total quantity ordered is more than 10. The list should be arranged in non-increasing order of the total quantity ordered.
select t.name,t.isbn,t.callnumber,sum(p.qty) as totalOrder
from title as t,order4 as o,purchase_order as p
where t.callnumber=o.callnumber
and o.ponum = p.ponum
and (select count(distinct supplier_id) from order4 where callnumber = t.callnumber) > 1
group by t.name,t.isbn,t.callnumber
having sum(qty) > 10
order by sum(qty) desc
3.List the IDs and first names of all the members who have read less than 5% of the total number
of books read from the library, in non-decreasing order of the number of books read by each.
select m.libid,m.fname,sum(timesread)as numbersOfReading
from member as m,title as t,read_by as r
where m.libid=r.lib_id and t.callnumber=r.callnumber
group by m.libid,m.fname
having sum(timesread) < (select sum(timesread)*0.05 from read_by)
5.4List in alphabetical order the first names and IDs of all female members of the library
who have read at least one title more than once.
select * from read_by4
select distinct m.libid,m.fname
from member4 as m,read_by4 as r
where m.libid=r.libid
and m.gender='famale'
and r.timesread > 0
order by m.fname
5.List the names of the suppliers and their respective average quantities of title orders received,
whose average quantities of title orders received is more than the average quantity of an
order in the system.
select s.name
from supplier as s,order4 as o,purchase_order as p
where o.supplier_id=s.supplier_id and p.ponum=o.ponum
group by o.supplier_id,s.name
having(avg(qty))>(select avg(qty) from purchase_order);
5.6
select m.fname,m.libid
from member4 as m,read_by4 as r,book4 as b
where m.libid=b.borrowerid and r.lib_id=m.libid
and m.gender='male' and m.join_date<'1995-11-10'
and (select count(book_id) from book4 where borrowerid=m.libid) <= 5
and 2 >= all(select timesread from read_by4 where libid=m.libid)
order by m.fname
第三天：
1. List the titles of all books written by "Churchill," along with their Year of Publication.
select T.Name,T.[Year],A.Lname from Title as T,Author as A where T.CallNumber = A.CallNumber and A.Lname = 'Churchill';
2. Retrieve the titles of all books borrowed by members whose first name is "John" or "Susan".
select distinct T.Name from Title as T where T.CallNumber in
(select B.CallNumber from Member as M,Book as B where M.Memno = B.BorrowerMemno
and (M.Fname = 'John' or M.Fname = 'Susan'))
3.3.List the names and IDs of all members who have borrowed the "Iliad" and the "Odyssey"—both books.
select M.Fname,M.Lname,M.MI,M.Memno
from Member as M
where
(
M.Memno
in
(select Book.BorrowerMemno from Book,Title where Book.CallNumber = Title.CallNumber and Title.Name = 'Iliad')
and
M.Memno
in
(select Book.BorrowerMemno from Book,Title where Book.CallNumber = Title.CallNumber and Title.Name = 'Odyssey'))
4.
List the names and IDs of all the members who have borrowed all titles written by "Collins".
Assume that a member may have borrowed multiple copies of the same title.
select M.Fname,M.Lname,M.MI from Member as M
where
(select count(*) from Title,Author where Title.CallNumber = Author.CallNumber and Author.Lname = 'Collins')
= (select count(distinct Book.CallNumber) from Book,Author where Book.CallNumber = Author.CallNumber
and Book.BorrowerMemno = M.Memno and Author.Lname = 'Collins')
5.Find the phone numbers of all members who have borrowed a book
written by an author whose last name is "Tanenbaum."
select M.PhoneNumber from Member as M,Book as B,Title as T,Author as A
where A.CallNumber = T.CallNumber and B.CallNumber=T.CallNumber and B.BorrowerMemno = M.Memno
and A.Lname='Tanenbaum'
6.Find those members who have borrowed more than three books
and list their names, IDs, and the number of books they borrowed.
Sort the results in descending order based on the number of books borrowed.
select M.Fname,M.Lname,M.MI,count(Book.BOOK_ID) from Member as M,Book
where M.Memno = Book.BorrowerMemno
group by M.MI,M.Fname,M.Lname having count(BOOK.BOOK_ID) > 3
order by count(BOOK.BOOK_ID);
7.List all members who have not borrowed any book.
select distinct
member.memno
from member where member.memno not in (select member.memno from member,book where member.memno =
book.borrowermemno)
8.select M.memno from Member as M
where
M.memno in
(
select M.memno from Member as M where m.phonenumber like '412%')
and
M.memno
not in
(select M1.memno from Member as M1,Book,Title where M1.memno =
Book.borrowerMemno and Title.CallNumber = Book.CallNumber and Title.Name='Pitt Roads')
8.List in alphabetical order the first names of all the members
who are residents of Pittsburgh (Phone numbers starting with "412")
and who have not borrowed the book titled "Pitt Roads."
select M.memno,M.Fname from Member as M
where
M.memno in
(
select M.memno from Member as M where m.phonenumber like '412%')
and
M.memno
not in
(select M.memno from Member as M,Book,Title where M.memno =
Book.borrowerMemno and Title.CallNumber = Book.CallNumber and Title.Name='Pitt Roads')
order by M.Fname
第二天：
create database MySecondDatabaseHomeWork
drop table manages
drop table works
drop table employees
drop table company
create table employees(
person_name varchar(40),
street varchar(40),
city varchar(40)
primary key(person_name)
);
create table company(
company_name varchar(40),
city varchar(40)
primary key(company_name)
);
create table manages(
person_name varchar(40),
manager_name varchar(40)
primary key(person_name),
foreign key(person_name) references employees,
foreign key(person_name) references employees
);
create table works(
person_name varchar(40),
company_name varchar(40),
salary int
primary key(person_name),
foreign key(person_name) references employees,
foreign key(company_name) references company
);
insert into employees values('Jones','First street','A');
insert into employees values('Mike','Second street','Newton');
insert into employees values('Sunny','First street','Beijing');
insert into employees values('Jane','Third street','Tianjin');
insert into employees values('Maria','Fifth street','NewYork');
insert into employees values('Tom','First street','A');
insert into employees values('Sara','Second street','Newton');
insert into employees values('Jacky','Fifth street','Shanghai');
insert into employees values('Micky','Second street','Tokyo');
insert into employees values('Hero','First street','Tokyo');
insert into employees values('Joyce','First street','Beijing');
insert into company values('First Bank Corporation','Tianjin');
insert into company values('Second Bank Corporation','NewYork');
insert into company values('Third Bank Corporation','Beijing');
insert into company values('Big Bank Corporation','Tokyo');
insert into company values('Small Bank Corporation','Newton');
insert into works values('Jones','First Bank Corporation','10000');
insert into works values('Mike','Second Bank Corporation','15000');
insert into works values('Sunny','Small Bank Corporation','5000');
insert into works values('Jane','First Bank Corporation','20000');
insert into works values('Maria','Third Bank Corporation','11000');
insert into works values('Tom','Small Bank Corporation','9000');
insert into works values('Sara' ,'Big Bank Corporation','20000');
insert into works values('Jacky','First Bank Corporation','7000');
insert into works values('Micky','Big Bank Corporation','16000');
insert into works values('Hero','Big Bank Corporation','19000');
insert into manages values('Jones','Max');
insert into manages values('Mike','Un');
insert into manages values('Micky','Uie');
insert into manages values('Sunny','Joyce');
insert into manages values('Jane','Hero');
insert into manages values('Maria','Jacky');
insert into manages values('Tom','Hero');
1.找出First Bank Corporation的所有员工的姓名
select person_name
from works where company_name='First Bank Corporation'
2.找出First Bank Corporation的所有员工的姓名和居住的城市
select e.person_name,e.city
from works w,employees e
where w.company_name = 'First Bank Corporation'
and e.person_name=w. person_name
3.找出First Bank Corporation所有年收入在10 000美元以上的员工的姓名和居住的街道城市
select e.person_name,e.street,e.city
from employees as e,works as w
where e.person_name = w.person_name
and w.salary>10000
and w.company_name = 'First Bank Corporation'
4.找出所有居住地与工作的公司在同一城市的员工的姓名
select e.person_name
from employees as e,works as w,company as c
where e.person_name=w.person_name and w.company_name=c.company_name
and e.city = c.city and w.company_name=c.company_name
5.找出与其经理居住在同一城市同一街道的所有员工的姓名
select Y.person_name
from employees Y,employees J,manages M
where Y.person_name = M.person_name
and M.manager_name = J.person_name
and Y.street = J.street
and Y.city = J.city
6.找出此数据库中不在First Bank Corporation工作的所有员工的姓名(not in)
select eo.person_name
from employees as eo
where eo.person_name not in
(select e.person_name
from works w,employees e
where w.company_name = 'First Bank Corporation'
and e.person_name=w. person_name)
7.找出比Small Bank Corporation的所有员工收入都高的所有员工的姓名
select w.person_name
from works as w
where w.salary>all(
select salary from works where company_name = 'Small Bank Corporation'
)
8.修改数据库，使Jones现在居住在Newtown
update employees set city='Newtown' where person_name='Jones'
9.为First Bank Corporation的所有员工都提高工资10%
update works set salary = salary*1.1
10.为数据库中所有经理都提高工资10%
update works set salary = salary*1.1 where person_name in (select manager_name from managers)
11.为数据库中所有工资不高于100000美元的经理提高工资10%，而高于100000美元的经理提高工资3%
update works
set salary =
case
when person_name in (select manager_name from manages) and salary <=10000 then salary*1.1
when person_name in (select manager_name from manages) and salary >10000 then salary*1.3
else salary
end
12.删除works关系中Small Bank Corporation的员工的所有信息
delete from works where company_name='Small Bank Corporation'
13.找出员工最多的公司
select company_name from works group by company_name having
(count(person_name))>=all(select count(person_name) from works group by company_name)
14.找出工资最少的员工所在公司
select person_name from works where salary <=all(select salary from works)
15.找出人均工资比First Bank Corporation人均工资高的公司
select company_name from works group by company_name having avg(salary)>(select avg(salary) from works where company_name = 'First Bank Corporation')
第二个作业：
create table person(
driver_id int,
person_name varchar(20),
address varchar(20)
primary key(driver_id)
)
create table car(
license varchar(20),
model varchar(20),
car_year varchar(20),
primary key(license)
)
create table accident(
report_number int,
a_date varchar(20),
location varchar(20)
primary key(report_number)
);
create table owns(
driver_id int,
license varchar(20)
primary key(driver_id,license),
foreign key(driver_id) references person,
foreign key(license) references car
);
create table participated(
driver_id int,
license varchar(20),
report_number int,
damage_amount int
primary key(driver_id,license,report_number),
foreign key(driver_id) references person,
foreign key(license) references car,
foreign key(report_number) references accident
);
insert into person values(1,'wangwenhan','neimenggu')
insert into person values(2,'zhangqian','tianjin')
insert into person values(3,'zhuminjie','henan')
insert into person values(4,'zhouzhimei','henshui')
insert into person values(5,'John Smith','sichuan')
insert into car values('001','Mazda','1989')
insert into car values('002','bengchi','1999')
insert into car values('003','baoma','1898')
insert into car values('004','aodi','1995')
insert into car values('005','xiandai','2008')
insert into owns values(5,'001')
insert into owns values(1,'002')
insert into owns values(2,'003')
insert into owns values(3,'004')
insert into owns values(5,'005')
insert into accident values(1,'1989','shandong')
insert into accident values(2,'1989','fujian')
insert into accident values(3,'1989','shanghai')
insert into accident values(4,'2008','beijing')
insert into participated values(5,'001',1,6)
insert into participated values(5,'005',2,4)
insert into participated values(3,'004',2,1)
insert into participated values(2,'003',3,6)
1.找出所有在1989年其车辆出过车祸的人员总数
select sum(p.damage_amount)
from accident as a,participated as p
where a.report_number = p.report_number
and a.a_date='1989'
2.找出和John Smith的车有关的车祸数量
select count(p.report_number)
from participated as p,person as pe where pe.driver_id=p.driver_id and
pe.person_name='John Smith'
3.为数据库添加一个新的车祸
insert into participated values(1,'001',7,5)
insert into accident values (7,'2014','tianjin')
4.删除John Smith的车马自达（Mazda)
delete from car where license in(select c.license from car as c,person as p,owns as o where c.license=o.license and p.driver_id = o.driver_id
and p.person_name='John Smith' and c.model = 'Mazda') and model='Mazda'
delete from owns where driver_id in (select driver_id from person where person_name='John Smith')
and license in (select license from car where model='Mazda')
第一天：
create database myFisrtDatabaseHomework;
create table customer(
customer_name varchar(20) not null,
customer_street varchar(20),
customer_city varchar(20),
primary key(customer_name)
);
create table branch(
branch_name varchar(20),
branch_city varchar(20),
assets numeric(16,2),
primary key(branch_name)
);
create table account(
account_number varchar(20),
branch_name varchar(20),
balance numeric(12,2)
primary key(account_number),
foreign key (branch_name) references branch
);
create table depositor(
customer_name varchar(20),
account_number varchar(20),
primary key(customer_name,account_number),
foreign key (customer_name) references customer,
foreign key (account_number) references account
);
create table loan(
loan_number varchar(20),
branch_name varchar(20),
amount int
primary key (loan_number),
foreign key (branch_name) references branch
);
create table borrower(
customer_name varchar(20),
loan_number varchar(20)
primary key (customer_name,loan_number),
foreign key (loan_number) references loan,
foreign key (customer_name) references customer
);
insert into customer values ('name1','street1','city1');
insert into branch values('yinhang1','city1',1111111111.11);
insert into account values('a_number1','yinhang1',1111111111.11);
insert into depositor values('name1','a_number1');
insert into loan values('loan_number1','yinhang1',1400);
insert into borrower values('name1','loan_number1');
insert into customer values ('wangwenhan','weijinlu','neimenggu');
insert into branch values('zhongguyinhang','neimenggu',1111111111.11);
insert into account values('A001','zhongguyinhang',1111111111.11);
insert into depositor values('wangwenhan','A001');
insert into loan values('001','zhongguyinhang',1000);
insert into borrower values('wangwenhan','001');
insert into customer values ('huangyi','nanjing','sichuan');
insert into branch values('zhaoshangyinhang','sichuan',2111111111.11);
insert into account values('A002','zhaoshangyinhang',2111111111.11);
insert into depositor values('huangyi','A002');
insert into loan values('002','zhaoshangyinhang',1200);
insert into borrower values('huangyi','002');
insert into customer values ('zhangqian','wuqing','tianjin');
insert into branch values('jiansheyinhang','tianjin',3111111111.11);
insert into account values('A003','jiansheyinhang',3111111111.11);
insert into depositor values('zhangqian','A003');
insert into loan values('003','jiansheyinhang',1300);
insert into borrower values('zhangqian','003');
insert into customer values ('liuyumei','langfang','hebei');
insert into branch values('gongshangyinhang','hebei',4111111111.11);
insert into account values('A004','gongshangyinhang',4111111111.11);
insert into depositor values('liuyumei','A004');
insert into loan values('004','gongshangyinhang',1400);
insert into borrower values('liuyumei','004');
insert into customer values ('lvhuangbing','baidi','hubei');
insert into branch values('nongyeyinhang','hubei',5111111111.11);
insert into account values('A005','nongyeyinhang',5111111111.11);
insert into depositor values('lvhuangbing','A005');
insert into loan values('005','nongyeyinhang',1500);
insert into borrower values('lvhuangbing','005');
insert into customer values ('guoning','binjiang','tianjin');
insert into branch values('feiyinhang','tianjin',6111111111.11);
insert into account values('A006','feiyinhang',6111111111.11);
insert into depositor values('guoning','A006');
insert into loan values('006','feiyinhang',1600);
insert into borrower values('guoning','006');
insert into customer values ('chengrui','nankai','tianjin');
insert into branch values('beijingyinhang','tianjin',6111111111.11);
insert into account values('A007','beijingyinhang',6111111111.11);
insert into depositor values('chengrui','A007');
insert into loan values('007','beijingyinhang',1600);
insert into borrower values('chengrui','007');
insert into customer values ('chengyuanxu','nankai','tianjin');
insert into branch values('tianjinyinhang','tianjin',6111111111.11);
insert into account values('A008','tianjinyinhang',6111111111.11);
insert into depositor values('chengyuanxu','A008');
insert into loan values('008','tianjinyinhang',1600);
insert into borrower values('chengyuanxu','008');
insert into customer values ('wwh','nankai','tianjin');
insert into branch values('zongyinhang','tianjin',9111111111.11);
insert into account values('A009','zongyinhang',9111111111.11);
insert into depositor values('wwh','A009');
insert into loan values('009','zongyinhang',1900);
insert into borrower values('wwh','009');
select * from customer;
select * from branch;
select * from account;
select * from depositor;
select * from loan;
select * from borrower;数据库作业
最新推荐文章于 2022-10-12 19:40:18 发布
扫一扫
2423
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
