Ek; Çözümlü SQL Lab.

Aşağıdaki komut dosyasını indirin: OrdersAndData.sql.

Bölüm 1 - DDL

Şekil; Siparişler ve Veriler için ERD.

Tabloları oluşturan ve yukarıdaki şekildeki Orders and Data ERD'si için verileri ekleyen OrdersAndData.sql kodunu kullanın.
Orders adında bir veritabanı oluşturun. PK ve referans bütünlüğünü entegre etmek için kodu değiştirin. Adım 3'te verilen kısıtlamaları içeren değişikliklerle birlikte CREATE TABLE deyimlerini gösterin.
Aşağıdaki kısıtlamaları ekleyin:

tblCustomers tablosu: Country – default to Canada
tblOrderDetails: Quantity – > 0
tblShippers: CompanyName benzersiz olmalıdır.
tblOrders: ShippedDate, sipariş tarihinden büyük olmalıdır.

CREATE DATABASE Orders
Go
Use Orders
Go

Use Orders
Go
CREATE TABLE [dbo].[tblCustomers]
[CustomerID]       nvarchar(5) NOT NULL,
[CompanyName]      nvarchar(40) NOT NULL,
[ContactName]      nvarchar(30) NULL,
[ContactTitle]     nvarchar(30) NULL,
[Address]          nvarchar(60) NULL,
[City]             nvarchar(15) NULL,
[Region]           nvarchar(15) NULL,
[PostalCode]       nvarchar(10) NULL,
[Country]          nvarchar(15) NULL
Constraint    df_country DEFAULT ‘Canada’,
[Phone]            nvarchar(24) NULL,
[Fax]              nvarchar(24) NULL,
Primary Key (CustomerID)
);

CREATE TABLE [dbo].[tblSupplier] (
[SupplierID]     int NOT NULL,
[Name]           nvarchar(50) NULL,
[Address]        nvarchar(50) NULL,
[City]           nvarchar(50) NULL,
[Province]       nvarchar(50) NULL,
Primary Key (SupplierID)
);

CREATE TABLE [dbo].[tblShippers] (
[ShipperID] int NOT NULL,
[CompanyName] nvarchar(40) NOT NULL,
Primary Key (ShipperID),<
CONSTRAINT uc_CompanyName UNIQUE (CompanyName)
);

CREATE TABLE [dbo].[tblProducts] (
[ProductID]           int NOT NULL,
[SupplierID]          int NULL,
[CategoryID]          int NULL,
[ProductName]         nvarchar(40) NOT NULL,
[EnglishName]         nvarchar(40) NULL,
[QuantityPerUnit]     nvarchar(20) NULL,
[UnitPrice]           money NULL,
[UnitsInStock]        smallint NULL,
[UnitsOnOrder]        smallint NULL,
[ReorderLevel]        smallint NULL,
[Discontinued]        bit NOT NULL,
Primary Key (ProductID),
Foreign Key (SupplierID) References tblSupplier
);

CREATE TABLE [dbo].[tblOrders] (
[OrderID]            int NOT NULL,
[CustomerID]         nvarchar(5) NOT NULL,
[EmployeeID]         int NULL,
[ShipName]           nvarchar(40) NULL,
[ShipAddress]        nvarchar(60) NULL,
[ShipCity]           nvarchar(15) NULL,
[ShipRegion]         nvarchar(15) NULL,
[ShipPostalCode]     nvarchar(10) NULL,
[ShipCountry]        nvarchar(15) NULL,
[ShipVia]            int NULL,
[OrderDate]          smalldatetime NULL,
[RequiredDate]       smalldatetime NULL,
[ShippedDate]        smalldatetime NULL,
[Freight]            money NULL
Primary Key (OrderID),
Foreign Key (CustomerID) References tblCustomers,
Foreign Key (ShipVia) References tblShippers,
Constraint valid_ShipDate CHECK (ShippedDate > OrderDate)
);

CREATE TABLE [dbo].[tblOrderDetails] (
[OrderID]       int NOT NULL,
[ProductID]     int NOT NULL,
[UnitPrice]     money NOT NULL,
[Quantity]      smallint NOT NULL,
[Discount]      real NOT NULL,
Primary Key (OrderID, ProductID),
Foreign Key (OrderID) References tblOrders,
Foreign Key (ProductID) References tblProducts,
Constraint Valid_Qty Check (Quantity > 0)
);
Go

Bölüm 2 - Aşağıdaki SQL İfadelerini Oluşturun

Müşterilerin ve 2014 yılı boyunca oluşturdukları siparişlerin bir listesini gösterin. Müşteri kimliğini, sipariş kimliğini, sipariş tarihini ve sipariş tarihini görüntüleyin.

Use Orders
Go
SELECT CompanyName, OrderID, RequiredDate as ‘order date’, OrderDate as ‘date ordered’
FROM tblcustomers JOIN tblOrders on tblOrders.CustomerID = tblCustomers.CustomerID
WHERE Year(OrderDate) = 2014

2. ALTER TABLE deyimini kullanarak tblcustomer'a yeni bir alan (Active) ekleyin. Varsayılan değeri True olarak ayarlayın.

ALTER TABLE tblCustomers
ADD Active bit DEFAULT (‘True’)

3. Eylül 1, 2012'den önce satın alınan tüm siparişleri gösterin. Şirket adını, sipariş tarihini ve toplam sipariş tutarını (navlun dahil) görüntüleyin.

SELECT tblOrders.OrderID, OrderDate as ‘Date Ordered’, sum(unitprice*quantity*(1-discount))+ freight as ‘Total Cost’
FROM tblOrderDetails join tblOrders on tblOrders.orderID = tblOrderDetails.OrderID
WHERE OrderDate < ‘September 1, 2012’
GROUP BY tblOrders.OrderID, freight, OrderDate

4. Federal Kargo ile gönderilen tüm siparişleri gösterin. OrderID, ShipName, ShipAddress ve CustomerID'yi görüntüleyin.

SELECT OrderID, ShipName, ShipAddress, CustomerID
FROM tblOrders join tblShippers on tblOrders.ShipVia = tblShippers.ShipperID
WHERE CompanyName= ‘Federal Shipping’

5. 2011'de alışveriş yapmamış tüm müşterileri gösterin.

SELECT CompanyName
FROM tblCustomers
WHERE CustomerID not in
( SELECT CustomerID
FROM tblOrders
WHERE Year(OrderDate) = 2011
)

6. Hiç sipariş edilmemiş tüm ürünleri gösterin.

SELECT ProductID from tblProducts
Except
SELECT ProductID from tblOrderDetails

VEYA

SELECT Products.ProductID,Products.ProductName
FROM Products LEFT JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
WHERE [Order Details].OrderID IS NULL

7. Londra'da ikamet eden müşteriler için OrderID'leri gösterin. Bir alt sorgu kullanın. CustomerID, CustomerName ve OrderID'yi görüntüleyin.

SELECT Customers.CompanyName,Customers.CustomerID,OrderID
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CompanyName IN
(SELECT CompanyName
FROM Customers
WHERE City = ‘London’)

8. Supplier A ve Supplier B tarafından sağlanan ürünleri gösterin. Ürün adını ve tedarikçi adını görüntüleyin.

SELECT ProductName, Name
FROM tblProducts JOIN tblSupplier on tblProducts.SupplierID = tblSupplier.SupplierID
WHERE Name Like ‘Supplier A’ or Name Like ‘Supplier B’

9. Kutu içinde gelen tüm ürünleri gösterin. Ürün adını ve QuantityPerUnit değerini görüntüleyin.

SELECT EnglishName, ProductName, QuantityPerUnit
FROM tblProducts
WHERE QuantityPerUnit like ‘%box%’
ORDER BY EnglishName

Bölüm 3 - Ekle, Güncelle, Sil, Dizinler

Bir Employee tablosu oluşturun. Birincil anahtar EmployeeID (otomatik sayı) olmalıdır. Aşağıdaki alanları ekleyin: LastName, FirstName, Address, City, Province, Postalcode, Phone, Salary. Beş çalışan için CREATE TABLE deyimini ve INSERT deyimlerini gösterin. Çalışan tablosunu tblOrders ile birleştirin. Tabloyu oluşturmak, kısıtlamaları ayarlamak ve çalışanları eklemek için kodu gösterin.

Use Orders
CREATE TABLE [dbo].[tblEmployee](
EmployeeID Int IDENTITY NOT NULL ,
FirstName varchar (20) NOT NULL,
LastName varchar (20) NOT NULL,
Address varchar (50),
City varchar(20), Province varchar (50),
PostalCode char(6),
Phone char (10),
Salary Money NOT NULL,
Primary Key (EmployeeID)

Go
INSERT into tblEmployees
Values (‘Jim’, ‘Smith’, ‘123 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J6’, ‘2506155989’, ‘20.12’),
(‘Jimmy’, ‘Smithy’, ‘124 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J7’, ‘2506155984’, ‘21.12’),
(‘John’, ‘Smore’, ’13 Fake’, ‘Terrace’, ‘BC’, ‘V4G5J6’, ‘2506115989’, ‘19.12’),
(‘Jay’, ‘Sith’, ’12 Fake’, ‘Terrace’, ‘BC’, ‘V8G4J6’, ‘2506155939’, ‘25.12’),
(‘Jig’, ‘Mith’, ’23 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J5’, ‘2506455989’, ‘18.12’);
Go

2. tblOrders'a TotalSales adında bir alan ekleyin. DDL - ALTER TABLE deyimini gösterin.

ALTER TABLE tblOrders
ADD Foreign Key (EmployeeID) references tblEmployees (EmployeeID)

3. UPDATE deyimini kullanarak, sipariş ayrıntıları tablosuna dayalı olarak her sipariş için toplam satışı ekleyin.

UPDATE tblOrders
Set TotalSales = (select sum(unitprice*quantity*(1-discount))
FROM tblOrderDetails
WHERE tblOrderDetails.OrderID= tblOrders.OrderID
GROUP BY OrderID

Önceki Ders: Ek; Örnek ERD Alıştırmaları

Bu Blogda Ara

Üniversite Dersleri