SELECT p.*

FROM GUEST p

WHERE NOT EXISTS (

SELECT 1

FROM BOOKING c

JOIN ROOM f ON f.RoomId = c.BookingId

WHERE c.GuestId = p.GuestId

);

SELECT p.*

FROM GUEST p

LEFT JOIN BOOKING c ON c.GuestId = p.GuestId

LEFT JOIN ROOM f ON f.RoomId = c.BookingId

WHERE c.BookingId IS NULL AND f.RoomId IS NULL;

SELECT p.*

FROM GUEST p

WHERE NOT EXISTS (

SELECT 1 FROM BOOKING c

WHERE c.GuestId = p.GuestId

);

CREATE TABLE ACCOUNT (

AccountId INT IDENTITY(1,1) NOT NULL,

ClientId INT NOT NULL,

CONSTRAINT PK_ACCOUNT PRIMARY KEY (AccountId),

CONSTRAINT FK_ACCOUNT_CLIENT FOREIGN KEY (ClientId)

REFERENCES CLIENT(ClientId)

ON DELETE NO ACTION

ON UPDATE NO ACTION

);

CREATE TABLE ACCOUNT (

AccountId INT IDENTITY(1,1) NOT NULL,

ClientId INT NULL,

CONSTRAINT PK_ACCOUNT PRIMARY KEY (AccountId),

CONSTRAINT FK_ACCOUNT_CLIENT FOREIGN KEY (ClientId)

REFERENCES CLIENT(ClientId)

ON DELETE CASCADE

ON UPDATE CASCADE

);

CREATE TABLE ACCOUNT (

AccountId INT IDENTITY(1,1) NOT NULL,

ClientId INT NOT NULL,

CONSTRAINT PK_ACCOUNT PRIMARY KEY (AccountId),

CONSTRAINT FK_ACCOUNT_CLIENT FOREIGN KEY (ClientId)

REFERENCES CLIENT(ClientId)

ON DELETE CASCADE

);

CREATE FUNCTION dbo.GetCUSTOMERTotal(@Id INT)

RETURNS DECIMAL(18,2)

AS

BEGIN

DECLARE @res DECIMAL(18,2);

SELECT @res = SUM(CAST(f.LineAmount AS DECIMAL(18,2)))

FROM ORDERS s

JOIN ORDER_ITEM f ON f.OrderId = s.OrderId

WHERE s.CustomerId = @Id;

RETURN ISNULL(@res, 0);

END;

GO

SELECT d.CustomerId, d.FullName, dbo.GetCUSTOMERTotal(d.CustomerId) AS TotalValue

FROM CUSTOMER d;

CREATE FUNCTION dbo.GetCUSTOMERTotal(@Id INT)

RETURNS TABLE

AS

RETURN (

SELECT SUM(f.LineAmount) AS TotalValue

FROM ORDERS s

JOIN ORDER_ITEM f ON f.OrderId = s.OrderId

WHERE s.CustomerId = @Id

);

GO

SELECT d.CustomerId, d.FullName, dbo.GetCUSTOMERTotal(d.CustomerId) AS TotalValue

FROM CUSTOMER d;

CREATE FUNCTION dbo.GetCUSTOMERTotal(@Id INT)

RETURNS DECIMAL(18,2)

AS

BEGIN

RETURN (SELECT SUM(f.LineAmount) FROM ORDER_ITEM f);

END;

GO

SELECT d.CustomerId, d.FullName,

(SELECT * FROM dbo.GetCUSTOMERTotal(d.CustomerId)) AS TotalValue

FROM CUSTOMER d;

SELECT p.*

FROM USER_ACCOUNT p

WHERE NOT EXISTS (

SELECT 1 FROM POST c

WHERE c.AuthorId = p.UserId

);

SELECT p.*

FROM USER_ACCOUNT p

WHERE NOT EXISTS (

SELECT 1

FROM POST c

JOIN COMMENT f ON f.PostId = c.PostId

WHERE c.AuthorId = p.UserId

);

SELECT p.*

FROM USER_ACCOUNT p

LEFT JOIN POST c ON c.AuthorId = p.UserId

LEFT JOIN COMMENT f ON f.PostId = c.PostId

WHERE c.PostId IS NULL AND f.PostId IS NULL;