Scribd
Upload
144 views13 pages

SQL Praktikum: UNION, APPLY, TRIGGER

The document discusses SQL queries using various operators and functions such as UNION, UNION ALL, CROSS APPLY, OUTER APPLY, EXCEPT, INTERSECT, and TRIGGERS. It provides examples of queries combining tables with these operators and functions, compares the behavior of UNION and UNION ALL, and shows how to create triggers that automatically update or insert rows in other tables in response to inserts, updates or deletes. It also discusses backup tables and nonclustered indexes.

Uploaded by

Ade Putra124
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
144 views13 pages

SQL Praktikum: UNION, APPLY, TRIGGER

The document discusses SQL queries using various operators and functions such as UNION, UNION ALL, CROSS APPLY, OUTER APPLY, EXCEPT, INTERSECT, and TRIGGERS. It provides examples of queries combining tables with these operators and functions, compares the behavior of UNION and UNION ALL, and shows how to create triggers that automatically update or insert rows in other tables in response to inserts, updates or deletes. It also discusses backup tables and nonclustered indexes.

Uploaded by

Ade Putra124
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Praktikum – Bagian 1: UNION & UNION ALL

1 SELECT productid, productname


FROM [Link]
WHERE categoryid = 4

1 SELECT [Link], [Link]


FROM [Link] P INNER JOIN [Link] OD
ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link] * [Link]) > 50000;

Soal 1.
1 SELECT productid, productname
FROM [Link]
WHERE categoryid = 4

UNION

SELECT [Link], [Link]


FROM [Link] P INNER JOIN [Link] OD
ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link] * [Link]) > 50000;
Soal 2.
1 SELECT productid, productname
FROM [Link]
WHERE categoryid = 4

UNION ALL

SELECT [Link], [Link]


FROM [Link] P INNER JOIN [Link] OD
ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link] * [Link]) > 50000;
Soal 3.
Perbedaan Union dan Union ALL adalah jika Union, maka hasil output nya adalah
diurutkan dari terkecil ke terbesar, Jika Union ALL, maka urutan table awal lalu diurut table
selanjutnya

Soal 4.
1 SELECT TOP(10)
[Link], [Link], [Link], [Link]
FROM [Link] a INNER JOIN [Link] b
ON [Link] = [Link] WHERE orderdate > '20080201' and orderdate <
'20080531'
UNION SELECT TOP(10)
[Link], [Link], [Link], [Link] FROM [Link]
a INNER JOIN [Link] b ON [Link] = [Link]
WHERE orderdate > '20080201' and orderdate < '20080531'
ORDER BY val desc;

Praktikum – Bagian 2: CROSS APPLY & OUTER APPLY


1 SELECT [Link], [Link], [Link]
FROM [Link] AS p
CROSS APPLY (
SELECT TOP(2)
[Link]
FROM [Link] AS d
WHERE [Link] = [Link]
ORDER BY [Link] DESC
) AS o
ORDER BY [Link];
1 IF OBJECT_ID('dbo.fnGetTop3ProductsForCustomer') IS NOT NULL
DROP FUNCTION dbo.fnGetTop3ProductsForCustomer;
GO
CREATE FUNCTION dbo.fnGetTop3ProductsForCustomer(@custid AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(3)
[Link],
[Link],
SUM([Link] * [Link]) AS totalsalesamount
FROM [Link] AS o
INNER JOIN [Link] AS d ON [Link] = [Link]
INNER JOIN [Link] AS p ON [Link] = [Link]
WHERE custid = @custid
GROUP BY [Link],[Link]
ORDER BY totalsalesamount DESC;
GO

1 SELECT [Link], [Link], [Link], [Link],


[Link]
FROM [Link] AS c
CROSS APPLY
dbo.fnGetTop3ProductsForCustomer ([Link]) AS p
ORDER BY [Link];
Soal 5.
1 SELECT [Link] , [Link],[Link] ,
[Link],[Link]
FROM [Link] sc
OUTER APPLY dbo.fnGetTop3ProductsForCustomer([Link]) pfc
ORDER BY [Link];

Soal 6.
1 SELECT
[Link],[Link],[Link],[Link],[Link]
ount
FROM [Link] sc
OUTER APPLY dbo.fnGetTop3ProductsForCustomer([Link]) pfc
WHERE [Link] is null;

Praktikum – Bagian 3: EXCEPT & INTERSECT


1 SELECT [Link]
FROM [Link] AS o
INNER JOIN [Link] AS d ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) > 20;

Soal 7.
1 SELECT custid
FROM [Link]
WHERE country ='USA'
EXCEPT
SELECT [Link]
FROM [Link] so
INNER JOIN [Link] d
ON [Link]=[Link]
GROUP BY [Link]
HAVING COUNT (distinct [Link]) > 20;
1 SELECT [Link]
FROM [Link] AS o
INNER JOIN [Link] AS d ON [Link] = [Link]
GROUP BY [Link]
HAVING SUM([Link] * [Link]) > 10000;

Soal 8.
1 SELECT [Link]
FROM [Link] AS c

EXCEPT

SELECT [Link]
FROM [Link] AS o
INNER JOIN [Link] AS d ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT (DISTINCT [Link]) > 20

INTERSECT

SELECT [Link]
FROM [Link] AS o
INNER JOIN [Link] AS d ON [Link] = [Link]
GROUP BY [Link]
HAVING SUM([Link] * [Link]) > 10000;

Soal 9.
Menampilkan kolom custid dari table [Link] kecuali tidak menduplikat data
yang sama, namun syaratnya harus menampilkan jumlah dari [Link] * [Link] > 10000.

Soal 10.
1 (SELECT [Link] FROM [Link] AS c
EXCEPT
SELECT [Link] FROM [Link] AS o
INNER JOIN [Link] AS d
ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT (DISTINCT [Link])>20)
INTERSECT
SELECT [Link]
FROM [Link] AS o
INNER JOIN [Link] AS d
ON [Link] = [Link]
GROUP BY [Link]
HAVING SUM([Link] * [Link])>10000;
Soal 11.
Akan di proses terlebih dahulu jika diberi kurung.

Praktikum – Bagian 4: TRIGGER (AFTER)


IF OBJECT_ID('[Link]') IS NOT NULL
DROP TRIGGER [Link];
GO

CREATE TRIGGER trgAutoAddOrderDetailsForOrder ON [Link]


AFTER INSERT
AS
PRINT 'TRIGGER trgAutoAddOrderDetailsForOrder dipanggil'

DECLARE @orderid INT =(SELECT orderid FROM inserted);


DECLARE @productid INT = 1;
DECLARE @unitprice MONEY =0;
DECLARE @qty SMALLINT = 1;
DECLARE @discount NUMERIC(4,3)=0;

INSERT INTO [Link] VALUES


(@orderid, @productid, @unitprice, @qty, @discount);

PRINT 'Data kosong ditambahkan secara otomatis ke tabel


[Link]';
GO

INSERT INTO [Link](


custid, empid, orderdate, requireddate, shipperid, freight,
shipname, shipaddress, shipcity, shipcountry)
VALUES (
85, 5, GETDATE(), GETDATE(), 3, 100, 'Kapal Api',
'Jl. Soekarno-Hatta','Malang','Indonesia');

IF OBJECT_ID('[Link]') IS NOT NULL


DROP TRIGGER [Link];
GO

CREATE TRIGGER trgAutoAddOrderDetailsUnitPrice ON


[Link]
AFTER UPDATE
AS
PRINT 'Trigger trgAutoAddOrderDetailsUnitPrice
Dipanggil';
DECLARE @productid INT = (SELECT productid FROM inserted);
DECLARE @unitprice MONEY =
COALESCE((SELECT unitprice FROM inserted),0.0);
UPDATE [Link] SET unitprice =@unitprice
WHERE productid =@productid
PRINT'Harga di tabel [Link] secara otomatis
disesuaikan..';
GO

Soal 12.
IF OBJECT_ID('[Link] Dipanggil') is not
null
DROP TRIGGER [Link];
GO

CREATE TRIGGER trgProductDiscontinue


ON [Link]
AFTER DELETE
AS
PRINT 'TRIGGER trgAutoProductDiscontinue DIPANGGIL!';

DECLARE @productid int = (SELECT productid FROM inserted);


DECLARE @discontinued int = 1;
UPDATE [Link] set discontinued = @discontinued
WHERE productid = @productid
PRINT 'discontinued';
GO
UPDATE [Link] SET discontinued = 1 WHERE productid = 10 ;
DELETE FROM [Link] WHERE productid = 10;
SELECT * FROM [Link] WHERE productid = 10;

Praktikum – Bagian 5: TRIGGER (INSTEAD OF)


1 CREATE TABLE [Link]
(
empid INT NOT NULL IDENTITY,
lastname NVARCHAR(20) NOT NULL,
firstname NVARCHAR(10) NOT NULL,
title NVARCHAR(30) NOT NULL,
titleofcourtesy NVARCHAR(25) NOT NULL,
birthdate DATETIME NOT NULL,
hiredate DATETIME NOT NULL,
address NVARCHAR(60) NOT NULL,
city NVARCHAR(15) NOT NULL,
region NVARCHAR(15) NULL,
postalcode NVARCHAR(10) NULL,
country NVARCHAR(15) NOT NULL,
phone NVARCHAR(24) NOT NULL,
mgrid INT NULL,
CONSTRAINT PK_EmployeesBackup PRIMARY KEY(empid),
CONSTRAINT FK_EmployeesBackup_EmployeesBackup FOREIGN KEY(mgrid)
REFERENCES [Link](empid),
CONSTRAINT CHK_birthdate_backup CHECK(birthdate <= CURRENT_TIMESTAMP)
);

CREATE NONCLUSTERED INDEX idx_nc_lastname ON


[Link](lastname);
CREATE NONCLUSTERED INDEX idx_nc_postalcode ON
[Link](postalcode);

INSERT INTO [Link] (


lastname, firstname, title, titleofcourtesy, birthdate, hiredate,
[address], city, region, postalcode, country, phone, mgrid
) select
lastname, firstname, title, titleofcourtesy, birthdate, hiredate,
[address], city, region, postalcode, country, phone, mgrid
from [Link];
----------------------------------------------------------
if object_id('[Link]') is not null
drop trigger [Link]
go

create trigger trgDivertInsertEmployeeToBackup ON [Link]


INSTEAD OF INSERT
AS
print 'TRIGGER trgDivertInsertEmployeeToBackup DIPANGGIL!';
INSERT INTO [Link](
lastname, firstname, title, titleofcourtesy, birthdate, hiredate,
[address],
city, region, postalcode, country, phone, mgrid)
SELECT
lastname, firstname, title, titleofcourtesy, birthdate, hiredate,
[address],
city, region, postalcode, country, phone, mgrid
FROM inserted;
PRINT 'Employee baru disimpan di tabel HR>EmployeesBackup..';
GO
-------------------------------------------------------------------
INSERT INTO [Link]
VALUES
('Santoso', 'Adi', 'Staff', 'Mr. ', '19830101', '20170101',
'[Link]-Hatta',
'Malang', 'Jawa Timur', '65150', 'Indonesia', '(085) 123-456', 1)
SELECT * FROM [Link]

DELETE FROM [Link] WHERE firstname='Maria'


SELECT*FROM [Link];

UPDATE [Link] SET firstname ='DEPAN', lastname='BELAKANG'


WHERE firstname='Adi';

Soal 13.
1 CREATE TRIGGER trgDiverUpdateEmployeeToBackup On [Link]
INSTEAD OF update AS PRINT 'trigger trgDiverUpdateEmployeeToBackup
DIPANGGIL';

INSERT INTO [Link](


lastname, firstname, title, titleofcourtesy, birthdate, hiredate,
[address], city, region, postalcode, country, phone, mgrid)
SELECT
lastname, firstname, title, titleofcourtesy, birthdate, hiredate,
[address], city, region, postalcode, country, phone, mgrid
FROM inserted;

PRINT 'karyawan dengan empid yang di [Link] yang di


update';
GO
Soal 14.
1 CREATE TRIGGER trgDiverDeleteEmployeeToBackup on [Link]
INSTEAD OF DELETE
as print 'TRIGGER trgDiverDeleteEmployeeToBackup dipanggil';

print 'Karyawan dengan nama : Maria Cameron di Hapus di


[Link] saja, Di tabel aslinya tetap';
go

delete from [Link] where firstname = 'Maria';


select * from [Link];

You might also like