Программирование в Microsoft SQL Server 2000 - ответы
Количество вопросов - 636
SELECT TOP 7 CustID AS [ID], OrderID, SaleDate, ShipDate, DATEDIFF(Day, SaleDate, ShipDate) AS [DaysToShip]FROM SaleWHERE ShipDate IS NOT NULLДанный сценарий определяет:
CREATE VIEW SimpleViewAS SELECT Related, SimpleDescription, RelatedDescriptionFROM RelatedTable INNER JOIN SimpleTable ON RelatedTable.SimpleID = SimpleTable.SimpleIDДанный сценарий выполняет действия:
UPDATE MyOilsSET MyOils.PlantPartID = Oils.PlantPartIDFROM MyOils INNER JOIN Oils ON MyOils.OilName = Oils.OilNameДанный сценарий выполняет действия:
SELECT Oils.OilName, Properties.PropertyFROM Oils RIGHT OUTER JOIN OilProperties ON Oils.OilID = OilProperties.OilID INNER JOIN Properties ON OilProperties.PropertyID = Properties.PropertyIDДанный сценарий выполняет действия:
SELECT TOP 7 CustID AS [ID], OrderID, SaleDate, ShipDate, DATEDIFF(Day, SaleDate, ShipDate) AS [DaysToShip]FROM SaleWHERE ShipDate IS NOT NULLДанный сценарий определяет:
SELECT stdID, stdName, stdTypeFROM STUDENTSORDER BY stdName DESCДанный сценарий выполняет действия:
SELECT Oils.OilID, Oils.OilName, PlantParts.PlantPartFROM Oils INNER JOIN PlantParts ON Oils.PlantPartID = PlantParts.PlantPartIDДанный сценарий выполняет действия:
SELECT Oils.OilID, Oils.OilName, Odors.OdorFROM Oils INNER JOIN OilOdors ON Oils.OilID = OilOdors.OilID INNER JOIN Odors ON OilOdors.OdorID = Odors.OdorIDДанный сценарий выполняет действия:
SELECT Oils.OilName, Properties.PropertyFROM Oils RIGHT OUTER JOIN OilProperties ON Oils.OilID = OilProperties.OilID INNER JOIN Properties ON OilProperties.PropertyID = Properties.PropertyIDДанный сценарий выполняет действия:
SELECT DISTINCT PlantTypes.PlantTypeFROM Oils INNER JOIN PlantTypes ON Oils.PlantTypeID = PlantTypes.PlantTypeIDДанный сценарий выполняет действия:
SELECT PlantParts.PlantPart, Count(Oils.OilName) AS [NumberOfOils]FROM Oils INNER JOIN PlantParts ON Oils.PlantPartID = PlantParts.PlantPartIDGROUP BY PlantParts.PlantPartДанный сценарий выполняет действия:
INSERT INTO Oils(OilName, LatinName, Sample)VALUES('InsertDefault', NULL, DEFAULT)Данный сценарий выполняет действия:
UPDATE MyOilsSET MyOils.PlantPartID = Oils.PlantPartIDFROM MyOils INNER JOIN Oils ON MyOils.OilName = Oils.OilNameДанный сценарий выполняет действия:
DELETE MyOilsFROM PlantPartsWHERE (MyOils.PlantPartID = PlantParts.PlantPartID) AND (PlantParts.PlantPart = 'Roots')Данный сценарий выполняет действия:
CREATE TABLE RelatedTable( RelatedID smallint IDENTITY (1,1) PRIMARY KEY CLUSTERED, SimpleID smallint REFERENCES SimpleTable (SimpleID), RelatedDescription varchar(20))Данный сценарий выполняет действия:
CREATE VIEW SimpleViewAS SELECT Related, SimpleDescription, RelatedDescriptionFROM RelatedTable INNER JOIN SimpleTable ON RelatedTable.SimpleID = SimpleTable.SimpleIDДанный сценарий выполняет действия:
DECLARE @x INTSET @x = NULLIF ( (@x = 0) OR (@x <> 0) ) PRINT 'True'ELSE PRINT 'Unknown'Данный сценарий выполняет действия:
DECLARE @Count INTDECLARE @Limit INTSET @Count = 0SET @Limit = 10WHILE ( @Count < @Limit ) BEGIN PRINT CAST ( @Count AS VARCHAR(10) ) + ' iteration' SET @Count = @Count + 1ENDДанный сценарий выполняет действия:
DECLARE @CustId INT DECLARE @CustName VARCHAR( 30 ) DECLARE CustCursor CURSOR FOR SELECT CustId, Name FROM Customer ORDER BY CustId FOR READ ONLY OPEN CustCursor WHILE (0 = 0) BEGIN FETCH NEXT FROM CustCursor INTO @CustId, @CustName IF ( @@FETCH_STATUS <> 0 ) BREAK PRINT CAST ( @CustId AS VARCHAR(IO) ) + ' ' + @CustName ENDCLOSE CustCursor DEALLOCATE CustCursorДанный сценарий выполняет действия:
CREATE PROCEDURE InputSP@OilName char(50)ASSELECT OilName, LatinName FROM OilsWHERE OilName = @OilNameДанный сценарий выполняет действия:
CREATE PROCEDURE DefaultSP@OilName char(50) = 'Fennel'ASSELECT OilName, LatinName FROM OilsWHERE OilName = @OilNameДанный сценарий выполняет действия:
CREATE PROCEDURE GetCustDiscount @CustId INT, @Discount DEC( 5, 3 ) OUTPUTAS SET @Discount = ( SELECT Discount FROM Customer WHERE CustId = @CustIdДанный сценарий выполняет действия:
CREATE TRIGGER TrackCustomerUpdates ON AppDta.dbo.Customer FOR INSERT, UPDATE, DELETE AS DECLARE @InsertedCount INT DECLARE @DeletedCount INT SET @InsertedCount = ( SELECT COUNT (*) FROM INSERTED ) SET @DeletedCount = ( SELECT COUNT(*) FROM DELETED ) IF ( @InsertedCount > 0 ) BEGIN INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime ) SELECT CustId, CASE WHEN ( @DeletedCount > 0 ) THEN 'Update' ELSE 'Insert' END, CURRENT_USER, CURRENT_TIMESTAMP FROM INSERTED END ELSE IF ( @DeletedCount > 0 ) BEGIN INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime ) SELECT CustId, 'Delete', CURRENT_USER, CURRENT_TIMESTAMP FROM DELETED ENDДанный сценарий выполняет действия:
CREATE TRIGGER TrackDiscountUpdates ON AppDta.dbo.Customer FOR UPDATE AS IF UPDATE( Discount ) INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, Discount, UpdUser, UpdDateTime ) SELECT CustId, 'Update', Discount, CURRENT_USER, СURRENT_TIMESTAMP FROM INSERTED ENDIFДанный сценарий выполняет действия:
CREATE TRIGGER CustomerDelete ON Customer INSTEAD OF DELETE AS SET NOCOUNT ON IF ( EXISTS ( SELECT * FROM DELETED JOIN Sale ON DELETED.CustId = Sale.CustId WHERE DATEDIFF( DAY, SaleDate, CURRENT_TIMESTAMP ) <365 ) ) BEGIN RAISERROR( 'One or more customers have recent sales', 16, 1) RETURN END DELETE FROM Sale WHERE CustId IN ( SELECT CustId FROM DELETED ) IF ( @@ERROR > 0 ) BEGIN ROLLBACK RAISERROR( 'Could not delete all old sales', 16, 2 ) RETURN END DELETE FROM Customer WHERE CustId IN ( SELECT CustId FROM DELETED )IF ( @@ERROR > 0 ) BEGIN ROLLBACK RAISERROR( 'Could not delete all old customers', 16, 3 ) RETURNENDRETURNДанный сценарий выполняет действия:
CREATE FUNCTION CustWithDiscount ( @MinDiscount DEC( 5, 3 ) ) RETURNS @CustWithDiscountTable TABLE ( ( CustID INT NOT NULL, CustName CHAR( 30 ) NOT NULL, CustDisc DEC( 5, 3 ) NULL, PRIMARY KEY( CustID ) ) BEGIN INSERT INTO @CustWithDiscountTable SELECT CustId, Name, CAST( Discount AS VARCHAR( 7 ) ) FROM Customer WHERE Discount >= @MinDiscount RETURNENDДанный сценарий выполняет действия:
CREATE FUNCTION CustDiscount ( @CustId INT ) RETURNS DEC( 5, 3 )BEGIN DECLARE @Discount DEC( 5, 3 ) SET @Discount = ( SELECT Discount FROM Customer WHERE CustId = @CustId ) RETURN @Discount ENDДанный сценарий выполняет действия:
INSERT INTO Oils(OilName, LatinName, Sample)VALUES('InsertDefault', NULL, DEFAULT)Данный сценарий выполняет действия:
DECLARE @x INTSET @x = NULLIF ( (@x = 0) OR (@x <> 0) ) PRINT 'True'ELSE PRINT 'Unknown'Данный сценарий выполняет действия:
CREATE TRIGGER TrackCustomerUpdates ON AppDta.dbo.Customer FOR INSERT, UPDATE, DELETE AS DECLARE @InsertedCount INT DECLARE @DeletedCount INT SET @InsertedCount = ( SELECT COUNT (*) FROM INSERTED ) SET @DeletedCount = ( SELECT COUNT(*) FROM DELETED ) IF ( @InsertedCount > 0 ) BEGIN INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime ) SELECT CustId, CASE WHEN ( @DeletedCount > 0 ) THEN 'Update' ELSE 'Insert' END, CURRENT_USER, CURRENT_TIMESTAMP FROM INSERTED END ELSE IF ( @DeletedCount > 0 ) BEGIN INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime ) SELECT CustId, 'Delete', CURRENT_USER, CURRENT_TIMESTAMP FROM DELETED ENDДанный сценарий выполняет действия:
DECLARE @CustId INT DECLARE @CustName VARCHAR( 30 ) DECLARE CustCursor CURSOR FOR SELECT CustId, Name FROM Customer ORDER BY CustId FOR READ ONLY OPEN CustCursor WHILE (0 = 0) BEGIN FETCH NEXT FROM CustCursor INTO @CustId, @CustName IF ( @@FETCH_STATUS <> 0 ) BREAK PRINT CAST ( @CustId AS VARCHAR(IO) ) + ' ' + @CustName ENDCLOSE CustCursor DEALLOCATE CustCursorДанный сценарий выполняет действия:
INSERT INTO Oils(OilName, LatinName, Sample)VALUES('InsertDefault', NULL, DEFAULT)Данный сценарий выполняет действия:
SELECT Oils.OilID, Oils.OilName, PlantParts.PlantPartFROM Oils INNER JOIN PlantParts ON Oils.PlantPartID = PlantParts.PlantPartIDДанный сценарий выполняет действия:
SELECT Oils.OilName, Properties.PropertyFROM Oils RIGHT OUTER JOIN OilProperties ON Oils.OilID = OilProperties.OilID INNER JOIN Properties ON OilProperties.PropertyID = Properties.PropertyIDДанный сценарий выполняет действия:
SELECT PlantParts.PlantPart, Count(Oils.OilName) AS [NumberOfOils]FROM Oils INNER JOIN PlantParts ON Oils.PlantPartID = PlantParts.PlantPartIDGROUP BY PlantParts.PlantPartHAVING NumberOfOils > 5Ошибка сценария состоит в том, что:
DELETE MyOilsFROM PlantPartsWHERE (MyOils.PlantPartID = PlantParts.PlantPartID) AND (PlantParts.PlantPart = 'Roots')Данный сценарий выполняет действия:
CREATE TABLE RelatedTable( RelatedID smallint IDENTITY (1,1) PRIMARY KEY CLUSTERED, SimpleID smallint REFERENCES SimpleTable (SimpleID), RelatedDescription varchar(20))Данный сценарий выполняет действия:
CREATE VIEW SimpleViewAS SELECT Related, SimpleDescription, RelatedDescriptionFROM RelatedTable INNER JOIN SimpleTable ON RelatedTable.SimpleID = SimpleTable.SimpleIDДанный сценарий выполняет действия:
DECLARE @x INTSET @x = NULLIF ( (@x = 0) OR (@x <> 0) ) PRINT 'True'ELSE PRINT 'Unknown'Данный сценарий выполняет действия:
DECLARE @CustId INT DECLARE @CustName VARCHAR( 30 ) DECLARE CustCursor CURSOR FOR SELECT CustId, Name FROM Customer ORDER BY CustId FOR READ ONLY OPEN CustCursor WHILE (0 = 0) BEGIN FETCH NEXT FROM CustCursor INTO @CustId, @CustName IF ( @@FETCH_STATUS <> 0 ) BREAK PRINT CAST ( @CustId AS VARCHAR(10) ) + ' ' + @CustName ENDCLOSE CustCursor DEALLOCATE CustCursorДанный сценарий выполняет действия:
CREATE TRIGGER TrackDiscountUpdates ON AppDta.dbo.Customer FOR UPDATE AS IF UPDATE( Discount ) INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, Discount, UpdUser, UpdDateTime ) SELECT CustId, 'Update', Discount, CURRENT_USER, СURRENT_TIMESTAMP FROM INSERTED ENDIFДанный сценарий выполняет действия:
CREATE FUNCTION CustDiscount ( @CustId INT ) RETURNS DEC( 5, 3 ) BEGIN DECLARE @Discount DEC( 5, 3 ) SET @Discount = ( SELECT Discount FROM Customer WHERE CustId = @CustId ) RETURN @Discount ENDДанный сценарий выполняет действия:
CREATE PROCEDURE DefaultSP@OilName char(50) = 'Fennel'ASSELECT OilName, LatinName FROM OilsWHERE OilName = @OilNameДанный сценарий выполняет действия:
CREATE FUNCTION CustWithDiscount ( @MinDiscount DEC( 5, 3 ) ) RETURNS @CustWithDiscountTable TABLE ( ( CustID INT NOT NULL, CustName CHAR( 30 ) NOT NULL, CustDisc DEC( 5, 3 ) NULL, PRIMARY KEY( CustID ) ) BEGIN INSERT INTO @CustWithDiscountTable SELECT CustId, Name, CAST( Discount AS VARCHAR( 7 ) ) FROM Customer WHERE Discount >= @MinDiscount RETURNENDДанный сценарий выполняет действия:
SELECT Oils.OilID, Oils.OilName, Odors.OdorFROM Oils INNER JOIN OilOdors ON Oils.OilID = OilOdors.OilID INNER JOIN Odors ON OilOdors.OdorID = Odors.OdorIDДанный сценарий выполняет действия:
SELECT DISTINCT PlantTypes.PlantTypeFROM Oils INNER JOIN PlantTypes ON Oils.PlantTypeID = PlantTypes.PlantTypeIDДанный сценарий выполняет действия:
SELECT PlantParts.PlantPart, Count(Oils.OilName) AS [NumberOfOils]FROM Oils INNER JOIN PlantParts ON Oils.PlantPartID = PlantParts.PlantPartIDGROUP BY PlantParts.PlantPartДанный сценарий выполняет действия:
DELETE MyOilsFROM PlantPartsWHERE (MyOils.PlantPartID = PlantParts.PlantPartID) AND (PlantParts.PlantPart = 'Roots')Данный сценарий выполняет действия:
CREATE PROCEDURE InputSP@OilName char(50)ASSELECT OilName, LatinName FROM OilsWHERE OilName = @OilNameДанный сценарий выполняет действия:
CREATE PROCEDURE GetCustDiscount @CustId INT, @Discount DEC( 5, 3 ) OUTPUTAS SET @Discount = ( SELECT Discount FROM Customer WHERE CustId = @CustIdДанный сценарий выполняет действия:
CREATE TRIGGER CustomerDelete ON Customer INSTEAD OF DELETE AS SET NOCOUNT ON IF ( EXISTS ( SELECT * FROM DELETED JOIN Sale ON DELETED.CustId = Sale.CustId WHERE DATEDIFF( DAY, SaleDate, CURRENT_TIMESTAMP ) < 365 ) ) BEGIN RAISERROR( 'One or more customers have recent sales', 16, 1) RETURN END DELETE FROM Sale WHERE CustId IN ( SELECT CustId FROM DELETED ) IF ( @@ERROR > 0 ) BEGIN ROLLBACK RAISERROR( 'Could not delete all old sales', 16, 2 ) RETURN END DELETE FROM Customer WHERE CustId IN ( SELECT CustId FROM DELETED )IF ( @@ERROR > 0 ) BEGIN ROLLBACK RAISERROR( 'Could not delete all old customers', 16, 3 ) RETURNENDRETURNДанный сценарий выполняет действия:
CREATE FUNCTION CustWithDiscount ( @MinDiscount DEC( 5, 3 ) ) RETURNS @CustWithDiscountTable TABLE ( ( CustID INT NOT NULL, CustName CHAR( 30 ) NOT NULL, CustDisc DEC( 5, 3 ) NULL, PRIMARY KEY( CustID ) ) BEGIN INSERT INTO @CustWithDiscountTable SELECT CustId, Name, CAST( Discount AS VARCHAR( 7 ) ) FROM Customer WHERE Discount >= @MinDiscount RETURNENDДанный сценарий выполняет действия:
CREATE FUNCTION CustDiscount ( @CustId INT ) RETURNS DEC( 5, 3 ) BEGIN DECLARE @Discount DEC( 5, 3 ) SET @Discount = ( SELECT Discount FROM Customer WHERE CustId = @CustId ) RETURN @Discount ENDДанный сценарий выполняет действия:
SELECT DISTINCT PlantTypes.PlantTypeFROM Oils INNER JOIN PlantTypes ON Oils.PlantTypeID = PlantTypes.PlantTypeIDДанный сценарий выполняет действия:
CREATE TABLE RelatedTable( RelatedID smallint IDENTITY (1,1) PRIMARY KEY CLUSTERED, SimpleID smallint REFERENCES SimpleTable (SimpleID), RelatedDescription varchar(20))Данный сценарий выполняет действия:
SELECT PlantParts.PlantPart, Count(Oils.OilName) AS [NumberOfOils]FROM Oils INNER JOIN PlantParts ON Oils.PlantPartID = PlantParts.PlantPartIDGROUP BY PlantParts.PlantPartДанный сценарий выполняет действия:
DECLARE @Count INTDECLARE @Limit INTSET @Count = 0SET @Limit = 10WHILE ( @Count < @Limit ) BEGIN PRINT CAST ( @Count AS VARCHAR(10) ) + ' iteration' SET @Count = @Count + 1ENDДанный сценарий выполняет действия:
CREATE PROCEDURE DefaultSP@OilName char(50) = 'Fennel'ASSELECT OilName, LatinName FROM OilsWHERE OilName = @OilNameДанный сценарий выполняет действия:
CREATE PROCEDURE GetCustDiscount @CustId INT, @Discount DEC( 5, 3 ) OUTPUTAS SET @Discount = ( SELECT Discount FROM Customer WHERE CustId = @CustIdДанный сценарий выполняет действия:
CREATE TRIGGER TrackDiscountUpdates ON AppDta.dbo.Customer FOR UPDATE AS IF UPDATE( Discount ) INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, Discount, UpdUser, UpdDateTime ) SELECT CustId, 'Update', Discount, CURRENT_USER, СURRENT_TIMESTAMP FROM INSERTED ENDIFДанный сценарий выполняет действия:
SELECT Oils.OilID, Oils.OilName, Odors.OdorFROM Oils INNER JOIN OilOdors ON Oils.OilID = OilOdors.OilID INNER JOIN Odors ON OilOdors.OdorID = Odors.OdorIDДанный сценарий выполняет действия:
SELECT TOP 30 PERCENT stdID AS [ID], stdName AS [STUDENT_NAME], clsName AS [STUDENT_CLASS], GETDATE() AS [TODAY]FROM STUDENTS, CLASSESWHERE STUDENTS.stdID = CLASSES.clsIDДанный сценарий определяет:
SELECT Oils.OilID, Oils.OilName, PlantParts.PlantPartFROM Oils INNER JOIN PlantParts ON Oils.PlantPartID = PlantParts.PlantPartIDДанный сценарий выполняет действия:
SELECT 'PropertyTable' AS TableName, PropertyID AS ID, Property AS Quality FROM PropertiesUNIONSELECT 'OdorTable', OdorID, Odor FROM OdorsORDER BY QualityДанный сценарий выполняет действия:
PRINT 'I get executed'GOTO Point1PRINT 'I get executed - YES!'Point1:PRINT 'I get executed too'Данный сценарий выполняет действия:
CREATE PROCEDURE InputSP@OilName char(50)ASSELECT OilName, LatinName FROM OilsWHERE OilName = @OilNameДанный сценарий выполняет действия:
CREATE TRIGGER CustomerDelete ON Customer INSTEAD OF DELETE AS SET NOCOUNT ON IF ( EXISTS ( SELECT * FROM DELETED JOIN Sale ON DELETED.CustId = Sale.CustId WHERE DATEDIFF( DAY, SaleDate, CURRENT_TIMESTAMP ) < 365 ) ) BEGIN RAISERROR( 'One or more customers have recent sales', 16, 1) RETURN END DELETE FROM Sale WHERE CustId IN ( SELECT CustId FROM DELETED ) IF ( @@ERROR > 0 ) BEGIN ROLLBACK RAISERROR( 'Could not delete all old sales', 16, 2 ) RETURN END DELETE FROM Customer WHERE CustId IN ( SELECT CustId FROM DELETED )IF ( @@ERROR > 0 ) BEGIN ROLLBACK RAISERROR( 'Could not delete all old customers', 16, 3 ) RETURNENDRETURNДанный сценарий выполняет действия:
SELECT TOP 7 CustID AS [ID], OrderID, SaleDate, ShipDate, DATEDIFF(Day, SaleDate, ShipDate) AS [DaysToShip]FROM SaleWHERE ShipDate IS NOT NULLДанный сценарий определяет:
UPDATE MyOilsSET MyOils.PlantPartID = Oils.PlantPartIDFROM MyOils INNER JOIN Oils ON MyOils.OilName = Oils.OilNameДанный сценарий выполняет действия:
DECLARE @Count INTDECLARE @Limit INTSET @Count = 0SET @Limit = 10WHILE ( @Count < @Limit ) BEGIN PRINT CAST ( @Count AS VARCHAR(10) ) + ' iteration' SET @Count = @Count + 1ENDДанный сценарий выполняет действия:
CREATE TRIGGER TrackCustomerUpdates ON AppDta.dbo.Customer FOR INSERT, UPDATE, DELETE AS DECLARE @InsertedCount INT DECLARE @DeletedCount INT SET @InsertedCount = ( SELECT COUNT (*) FROM INSERTED ) SET @DeletedCount = ( SELECT COUNT(*) FROM DELETED ) IF ( @InsertedCount > 0 ) BEGIN INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime ) SELECT CustId, CASE WHEN ( @DeletedCount > 0 ) THEN 'Update' ELSE 'Insert' END, CURRENT_USER, CURRENT_TIMESTAMP FROM INSERTED END ELSE IF ( @DeletedCount > 0 ) BEGIN INSERT INTO AppDta.dbo.CustUpdLog ( CustId, Action, UpdUser, UpdDateTime ) SELECT CustId, 'Delete', CURRENT_USER, CURRENT_TIMESTAMP FROM DELETED ENDДанный сценарий выполняет действия:
SELECT stdID, stdName, stdTypeFROM STUDENTSORDER BY stdName DESCДанный сценарий выполняет действия: