-- (1,1) gösterimi 1 den başlayıp 1'er 1'er artırmayı gösterir.
--
-- Müşteriler tablosu
CREATE TABLE Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100),
Email NVARCHAR(100)
);
ALTER TABLE Customers
ADD CONSTRAINT UQ_Customers_Email UNIQUE (Email);
ALTER TABLE Customers
ADD Phone NVARCHAR(20);
ALTER TABLE Customers
DROP COLUMN Phone;
-- Ürünler tablosu
CREATE TABLE Products (
ProductID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100),
Price DECIMAL(10,2),
Stock INT
);
-- INDEX
CREATE INDEX IX_Products_Name
ON Products (Name);
--INDEX oluşmuş mu kontrol et
sp_helpindex Products;
-- Index bu sorgunun daha hızlı olmasını sağlıyor.
SELECT * FROM Products WHERE Name = 'Laptop';
--Drop INDEX
-- Siparişler tablosu
-- ON DELETE CASCADE : Customer tablosundan Customer silinirse Order tablosundan da silinicek.
CREATE TABLE Orders (
OrderID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT,
OrderDate DATETIME default GETDATE(),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE
);
-- Sipariş detayları tablosu
CREATE TABLE OrderDetails (
OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT,
FOREIGN KEY(OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY(ProductID) REFERENCES Products(ProductID)
);
-- Örnek müşteriler
INSERT INTO Customers (Name, Email) VALUES
('Ali', 'ali@mail.com'),
('Ayşe', 'ayse@mail.com'),
('Mehmet', 'mehmet@mail.com');
--Create and Insert Cars
CREATE TABLE Cars(
Name NVARCHAR(100),
Model NVARCHAR(100)
);
INSERT INTO Cars (Name , Model) VALUES
('reno',2002),
('mercedes',2010),
('bmw',2005);
-- Örnek ürünler
INSERT INTO Products (Name, Price, Stock) VALUES
('Laptop', 1000.00, 5),
('Mouse', 50.00, 50),
('Klavye', 80.00, 30);
-- Örnek siparişler
INSERT INTO Orders (CustomerID) VALUES
(1),
(2),
(1);
-- Örnek sipariş detayları
INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES
(1, 1, 1),
(1, 2, 2),
(2, 3, 1),
(3, 1, 1),
(3, 3, 1);
SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM OrderDetails;
SELECT * FROM Cars;
SELECT * FROM Products WHERE Price > 100;
SELECT * FROM Products WHERE Price BETWEEN 50 AND 1000 ORDER BY Price DESC;
SELECT * FROM Products WHERE Name IN ('Laptop', 'Mouse');
SELECT * FROM Products WHERE Price > 100 AND Stock < 10;
SELECT COUNT(*) AS TotalCustomers FROM Customers;
SELECT AVG(Price) AS AveragePrice FROM Products;
SELECT SUM(Quantity) AS TotalItemsSold FROM OrderDetails;
-- OrderID'ye göre grupla bu grupların Quantity'sini topla toplamı 2 den büyük olanları yazdır.
SELECT OrderID, SUM(Quantity) AS TotalItems
FROM OrderDetails
GROUP BY OrderID
HAVING SUM(Quantity) > 2;
-- c.NAME'i CustomerName olarak adlandır.
SELECT o.OrderID, c.Name AS CustomerName, o.OrderDate
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
--Soldaki Customer tablosu . Customer tablosundakileri yazdırır Customer tablosundakilerin OrderId'si varsa yazdırır , yoksa null yazar .
SELECT c.Name, o.OrderID
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
SELECT o.OrderID, c.Name AS CustomerName, o.OrderDate
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.Name = 'Ali'
ORDER BY o.OrderDate DESC;
SELECT o.OrderID , c.Name AS CustomerName , o.OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.Name ='Mehmet'
ORDER BY o.OrderDate DESC;
SELECT c.Name AS CustomerName, COUNT(o.OrderID) AS TotalOrders
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Name
ORDER BY TotalOrders DESC;
SELECT o.OrderID, c.Name AS CustomerName, o.OrderDate
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2025-01-01'
ORDER BY o.OrderDate DESC;
SELECT c.Name AS CustomerName , o.OrderID
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID;
SELECT c.Name AS CustomerName , o.OrderID
FROM Customers c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL;
--VIEW
CREATE VIEW CustomerOrders AS
SELECT o.OrderID, c.Name AS CustomerName, o.OrderDate
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
SELECT * FROM CustomerOrders;
Telegram: DB developers channel