Search

20 October, 2017

Basic Sql statements

INSERT        
   INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');

UPDATE      
 UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

DELETE     
 DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';
SELECT WHERE
 SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin'; SELECT * FROM Customers
WHERE City='Berlin' OR City='München';
SELECT * FROM Customers
WHERE NOT Country='Germany';
SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

SELECT TOP
SELECT TOP 3 * FROM Customers; SELECT * FROM Customers
LIMIT 3;
SELECT * FROM Customers
WHERE ROWNUM <= 3;
SELECT TOP 50 PERCENT * FROM Customers; SELECT * FROM Customers
WHERE Country='Germany' AND ROWNUM <= 3;
ORDER BY  
 SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
Aggregate function
SELECT MIN(Price) AS SmallestPrice
FROM Products;
SELECT COUNT(ProductID) FROM Products;
SELECT SUM(Quantity) FROM OrderDetails;
LIKE              
 SELECT * FROM Customers
WHERE CustomerName LIKE 'a%'; // a at the start
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%'; //or in any position
SELECT * FROM Customers
WHERE CustomerName LIKE 'a_%_%' //SQL statement selects all customers with a
CustomerName that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%'; //

IN                   
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
SELECT * FROM Customers

WHERE Country IN (SELECT Country FROM Suppliers);


BETWEEN
     SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);
JOIN              
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
INNER JOIN
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
LEFT JOIN   
 SELECT Customers.CustomerName, Orders.OrderID FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName;
RIGHT JOIN
 SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;


FULL OUTER JOIN
SELECT Customers.CustomerName, Orders.OrderID FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;
SELF JOIN     
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2,A.City FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID AND A.City = B.City
ORDER BY A.City; // join with itself

UNION          
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
GROUP BY
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

 HAVING 
  SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;
DDL:

PK/FK               
   CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

CHECK             
    CREATE TABLE Persons ( ID int NOT NULL,
LastName varchar(255) NOT NULL, FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
INDEX         
        CREATE INDEX idx_lastname
ON Persons (LastName);
AUTO INCREAMENT
CREATE TABLE Persons (
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL, FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);

VIEW                  
  CREATE VIEW [Current Product List] AS SELECT ProductID, ProductName
FROM Products

WHERE Discontinued = No;
on