#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
/* Note: on update/delete, no action (restriction) is
the default. (SQL Server amp; Oracle) */
CREATE TABLE Faculty (
FacNo CHAR(11) NOT NULL,
FacFirstName VARCHAR(30) NOT NULL,
FacLastName VARCHAR(30) NOT NULL,
FacCity VARCHAR(30) NOT NULL,
FacState CHAR(2) NOT NULL,
FacDept CHAR(6) NULL,
FacRank CHAR(4) NULL,
FacSalary DECIMAL(10,2) NULL,
FacSupervisor CHAR(11) NULL,
FacHireDate DATETIME NULL,
FacZipCode CHAR(10) NOT NULL,
CONSTRAINT FacultyPK PRIMARY KEY (FacNo),
CONSTRAINT SupervisorFK FOREIGN KEY (FacSupervisor) REFERENCES Faculty )
go
/* Note: on update cascade for FacSupervisor caused the following error
in SQL Server 2004:
Introducing FOREIGN KEY CONSTRAINT 'SupervisorFK' on table 'Faculty'
may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION
or ON UPDATE NO ACTION, or modify other FOREIGN KEY CONSTRAINTs.
*/
CREATE TABLE Course (
CourseNo CHAR(6) NOT NULL,
CrsDesc VARCHAR(50) NOT NULL,
CrsUnits INTEGER NULL,
CONSTRAINT CoursePK PRIMARY KEY (CourseNo) )
go
CREATE TABLE Offering (
OfferNo INTEGER NOT NULL,
CourseNo CHAR(6) NOT NULL,
OffTerm CHAR(6) NOT NULL,
OffYear INTEGER NOT NULL,
OffLocation VARCHAR(30) NULL,
OffTime VARCHAR(10) NULL,
FacNo CHAR(11) NULL,
OffDays CHAR(4) NULL,
CONSTRAINT OfferingPK PRIMARY KEY (OfferNo),
CONSTRAINT CourseFK FOREIGN KEY (CourseNo) REFERENCES Course,
CONSTRAINT FacultyFK FOREIGN KEY (FacNo) REFERENCES Faculty )
go
CREATE TABLE Student (
StdNo CHAR(11) NOT NULL,
StdFirstName VARCHAR(30) NOT NULL,
StdLastName VARCHAR(30) NOT NULL,
StdCity VARCHAR(30) NOT NULL,
StdState CHAR(2) NOT NULL,
StdZip CHAR(10) NOT NULL,
StdMajor CHAR(6) NULL,
StdClass CHAR(2) NULL,
StdGPA DECIMAL(3,2) NULL,
CONSTRAINT StudentPk PRIMARY KEY (StdNo) )
go
CREATE TABLE Enrollment (
OfferNo INTEGER NOT NULL,
StdNo CHAR(11) NOT NULL,
EnrGrade DECIMAL(3,2) NULL,
CONSTRAINT EnrollmentPK PRIMARY KEY (OfferNo, StdNo),
CONSTRAINT OfferingFK FOREIGN KEY (OfferNo) REFERENCES Offering
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT StudentFK FOREIGN KEY (StdNo) REFERENCES Student
ON DELETE CASCADE
ON UPDATE CASCADE )
go
INSERT INTO Faculty
VALUES ('543210987','VICTORIA','EMMANUEL','BOTHELL','WA','MS','PROF',120000.00,NULL,'4/15/2005','98011-2242')
go
INSERT INTO Faculty
VALUES ('765432109','NICKI','MACON','BELLEVUE','WA','FIN','PROF',65000.00,NULL,'4/11/2006','98015-9945')
go
INSERT INTO Faculty
VALUES ('654321098','LEONARD','FIBON','SEATTLE','WA','MS','ASSC',70000.00,'543210987','5/1/2003','98121-0094')
go
INSERT INTO Faculty
VALUES ('098765432','LEONARD','VINCE','SEATTLE','WA','MS','ASST',35000.00,'654321098','4/10/2004','98111-9921')
go
INSERT INTO Faculty
VALUES ('876543210','CRISTOPHER','COLAN','SEATTLE','WA','MS','ASST',40000.00,'654321098','3/1/2008','98114-1332')
go
INSERT INTO Faculty
VALUES ('987654321','JULIA','MILLS','SEATTLE','WA','FIN','ASSC',75000.00,'765432109','3/15/2009','98114-9954')
go
INSERT INTO Course
VALUES ('FIN300','FUNDAMENTALS OF FINANCE',4)
go
INSERT INTO Course
VALUES ('FIN450','PRINCIPLES OF INVESTMENTS',4)
go
INSERT INTO Course
VALUES ('FIN480','CORPORATE FINANCE',4)
go
INSERT INTO Course
VALUES ('IS320','FUNDAMENTALS OF BUSINESS PROGRAMMING',4)
go
INSERT INTO Course
VALUES ('IS460','SYSTEMS ANALYSIS',4)
go
INSERT INTO Course
VALUES ('IS470','BUSINESS DATA COMMUNICATIONS',4)
go
INSERT INTO Course
VALUES ('IS480','FUNDAMENTALS OF DATABASE MANAGEMENT',4)
go
INSERT INTO Offering
VALUES (1111,'IS320','SUMMER',2017,'BLM302','10:30:00',NULL,'MW')
go
INSERT INTO Offering
VALUES (1234,'IS320','FALL',2016,'BLM302','10:30:00','098765432','MW')
go
INSERT INTO Offering
VALUES (2222,'IS460','SUMMER',2016,'BLM412','13:30:00',NULL,'TTH')
go
INSERT INTO Offering
VALUES (3333,'IS320','SPRING',2017,'BLM214','8:30:00','098765432','MW')
go
INSERT INTO Offering
VALUES (4321,'IS320','FALL',2016,'BLM214','15:30:00','098765432','TTH')
go
INSERT INTO Offering
VALUES (4444,'IS320','WINTER',2017,'BLM302','15:30:00','543210987','TTH')
go
INSERT INTO Offering
VALUES (5555,'FIN300','WINTER',2017,'BLM207','8:30:00','765432109','MW')
go
INSERT INTO Offering
VALUES (5678,'IS480','WINTER',2017,'BLM302','10:30:00','987654321','MW')
go
INSERT INTO Offering
VALUES (5679,'IS480','SPRING',2017,'BLM412','15:30:00','876543210','TTH')
go
INSERT INTO Offering
VALUES (6666,'FIN450','WINTER',2017,'BLM212','10:30:00','987654321','TTH')
go
INSERT INTO Offering
VALUES (7777,'FIN480','SPRING',2017,'BLM305','13:30:00','765432109','MW')
go
INSERT INTO Offering
VALUES (8888,'IS320','SUMMER',2017,'BLM405','13:30:00','654321098','MW')
go
INSERT INTO Offering
VALUES (9876,'IS460','SPRING',2017,'BLM307','13:30:00','654321098','TTH')
go
INSERT INTO Student
VALUES ('123456789','HOMER','WELLS','SEATTLE','WA','98121-1111','IS','FR',3.00)
go
INSERT INTO Student
VALUES ('124567890','BOB','NORBERT','BOTHELL','WA','98011-2121','FIN','JR',2.70)
go
INSERT INTO Student
VALUES ('234567890','CANDY','KENDALL','TACOMA','WA','99042-3321','ACCT','JR',3.50)
go
INSERT INTO Student
VALUES ('345678901','WALLY','KENDALL','SEATTLE','WA','98123-1141','IS','SR',2.80)
go
INSERT INTO Student
VALUES ('456789012','JOE','ESTRADA','SEATTLE','WA','98121-2333','FIN','SR',3.20)
go
INSERT INTO Student
VALUES ('567890123','MARIAH','DODGE','SEATTLE','WA','98114-0021','IS','JR',3.60)
go
INSERT INTO Student
VALUES ('678901234','TESS','DODGE','REDMOND','WA','98116-2344','ACCT','SO',3.30)
go
INSERT INTO Student
VALUES ('789012345','ROBERTO','MORALES','SEATTLE','WA','98121-2212','FIN','JR',2.50)
go
INSERT INTO Student
VALUES ('876543210','CRISTOPHER','COLAN','SEATTLE','WA','98114-1332','IS','SR',4.00)
go
INSERT INTO Student
VALUES ('890123456','LUKE','BRAZZI','SEATTLE','WA','98116-0021','IS','SR',2.20)
go
INSERT INTO Student
VALUES ('901234567','WILLIAM','PILGRIM','BOTHELL','WA','98113-1885','IS','SO',3.80)
go
INSERT INTO Enrollment
VALUES (1234,'123456789',3.30)
go
INSERT INTO Enrollment
VALUES (1234,'234567890',3.50)
go
INSERT INTO Enrollment
VALUES (1234,'345678901',3.20)
go
INSERT INTO Enrollment
VALUES (1234,'456789012',3.10)
go
INSERT INTO Enrollment
VALUES (1234,'567890123',3.80)
go
INSERT INTO Enrollment
VALUES (1234,'678901234',3.40)
go
INSERT INTO Enrollment
VALUES (4321,'123456789',3.50)
go
INSERT INTO Enrollment
VALUES (4321,'124567890',3.20)
go
INSERT INTO Enrollment
VALUES (4321,'789012345',3.50)
go
INSERT INTO Enrollment
VALUES (4321,'876543210',3.10)
go
INSERT INTO Enrollment
VALUES (4321,'890123456',3.40)
go
INSERT INTO Enrollment
VALUES (4321,'901234567',3.10)
go
INSERT INTO Enrollment
VALUES (5555,'123456789',3.20)
go
INSERT INTO Enrollment
VALUES (5555,'124567890',2.70)
go
INSERT INTO Enrollment
VALUES (5678,'123456789',3.20)
go
INSERT INTO Enrollment
VALUES (5678,'234567890',2.80)
go
INSERT INTO Enrollment
VALUES (5678,'345678901',3.30)
go
INSERT INTO Enrollment
VALUES (5678,'456789012',3.40)
go
INSERT INTO Enrollment
VALUES (5678,'567890123',2.60)
go
INSERT INTO Enrollment
VALUES (5679,'123456789',2.00)
go
INSERT INTO Enrollment
VALUES (5679,'124567890',3.70)
go
INSERT INTO Enrollment
VALUES (5679,'678901234',3.30)
go
INSERT INTO Enrollment
VALUES (5679,'789012345',3.80)
go
INSERT INTO Enrollment
VALUES (5679,'890123456',2.90)
go
INSERT INTO Enrollment
VALUES (5679,'901234567',3.10)
go
INSERT INTO Enrollment
VALUES (6666,'234567890',3.10)
go
INSERT INTO Enrollment
VALUES (6666,'567890123',3.60)
go
INSERT INTO Enrollment
VALUES (7777,'876543210',3.40)
go
INSERT INTO Enrollment
VALUES (7777,'890123456',3.70)
go
INSERT INTO Enrollment
VALUES (7777,'901234567',3.40)
go
INSERT INTO Enrollment
VALUES (9876,'124567890',3.50)
go
INSERT INTO Enrollment
VALUES (9876,'234567890',3.20)
go
INSERT INTO Enrollment
VALUES (9876,'345678901',3.20)
go
INSERT INTO Enrollment
VALUES (9876,'456789012',3.40)
go
INSERT INTO Enrollment
VALUES (9876,'567890123',2.60)
go
INSERT INTO Enrollment
VALUES (9876,'678901234',3.30)
go
INSERT INTO Enrollment
VALUES (9876,'901234567',4.00)
go
Например, схема.
Получите все номера преподавателей (FacNo) и фамилии (FacLastName) двух разных преподавателей, которые предлагали курсы в один и тот же год предложения (OffYear). В выходных данных не должно быть дубликатов.
* Примечание: например, если оба P1 Smith и P2 Peters предлагали курсы в 2021 году, то необходимо выбрать [P1, Smith, P2, Peters] (или [P2, Peters, P1, Smith], но не оба). Итак, вывод должен выглядеть следующим образом:
FacNo FacLastName FacNo FacLastName
===== =========== ===== ===========
P1 Smith P2 Peters
... ... ... ...
===== =========== ===== ===========*
Я пытаюсь сделать что-то подобное, и я даже не могу придумать, с чего начать? Как мне подойти к этой проблеме?
Я пробовал что-то вроде этого
SELECT f1.FacNo, f1.FacLastName
FROM Offering o1
INNER JOIN Faculty f1 on o1.FacNo = f1.FacNo
INNER JOIN Offering o2 on o1.OffYear = o2.OffYear
Комментарии:
1. В сторону… SQL Server 2004? Такого не было. BizTalk Server 2004 был чем-то, но включал SQL Server 2000 SP4. Ошибка 1785 возникает из-за того, что вы не можете включать циклы в каскадные обновления и удаления, что включает в себя самоссылку на таблицу в вашем внешнем ключе SupervisorFK.
2. Вам нужно показать нам желаемые результаты для предоставленных образцов данных.
Ответ №1:
Вы на правильном пути. Объединение Offering
таблицы дважды в OffYear
правильном порядке дает вам комбинации курсов в том же году. Поскольку вам нужны имена двух преподавателей, вам также необходимо Faculty
дважды объединить таблицу:
SELECT f1.FacNo, f1.FacLastName, f2.FacNo, f2.FacLastName
FROM Offering o1
INNER JOIN Faculty f1
on o1.FacNo = f1.FacNo
INNER JOIN Offering o2
on o2.OffYear = o1.Offyea
INNER JOIN Faculty f2
on o2.FacNo = f2.FacNo
Однако это будет включать точные повторяющиеся строки (например, когда два преподавателя проходили два курса в один и тот же год), комбинации преподавателей с самими собой и повторяющиеся комбинации имен в обратном порядке (как [P1, Smith, P2, Peters], так и [P2, Peters, P1, Smith] ).
Чтобы избавиться от точных дубликатов, используйте SELECT DISTINCT
вместо SELECT
. Две другие проблемы можно легко устранить с помощью одного WHERE
условия: ограничивая результаты так, чтобы первый элемент FacNo
был меньше другого, нежелательные комбинации отфильтровываются.
SELECT DISTINCT f1.FacNo, f1.FacLastName, f2.FacNo, f2.FacLastName
FROM Offering o1
INNER JOIN Faculty f1
on o1.FacNo = f1.FacNo
INNER JOIN Offering o2
on o2.OffYear = o1.Offyear
INNER JOIN Faculty f2
on o2.FacNo = f2.FacNo
WHERE f1.FacNo < f2.FacNo
Посмотрите на эту скрипку dB<> .