ΣΤΑΥΡΟΠΟΥΛΟΣ ΒΑΣΙΛΕΙΟΣ-ΟΡΕΣΤΗΣ (ΑΜ : 3070249)

ΕΡΩΤΗΜΑ Α

1.
SELECT FirstName,LastName
FROM Employees
WHERE SUBSTRING ( LastName, 1 , 1 )>= 'F' AND SUBSTRING ( LastName, 1 , 1) = 2;

2.
SELECT City, COUNT(DISTINCT EmployeeID) AS NumberOfEmployees
FROM Employees
WHERE Title = 'Sales Representative'
GROUP BY City
HAVING COUNT(EmployeeID) > = 2
ORDER BY NumberOfEmployees

ΕΡΩΤΗΜΑ Γ

1.
SELECT o.OrderID, n.ContactName, e.FirstName AS EmployeeFirstName, e.LastName AS EmployeeLastName, o.OrderDate
FROM Orders o
JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
JOIN Customers n ON (n.CustomerID = o.CustomerID)
WHERE o.OrderDate > '1998-03-01'
ORDER BY n.ContactName;

2.
SELECT COUNT(DISTINCT Employees.EmployeeID) AS NumberOfEmployees, ISNULL(Employees.Country, 'None.') AS Country, COUNT(DISTINCT Customers.CustomerID) AS NumberOfCustomers, Customers.Country
FROM Employees
RIGHT JOIN Customers
ON Employees.Country = Customers.Country
GROUP BY Customers.Country, Employees.Country
ORDER BY NumberOfEmployees DESC, NumberOfCustomers DESC;

ΕΡΩΤΗΜΑ Δ

1.
SELECT Address, Code, Country
FROM Suppliers
UNION
SELECT Address, Code, Country
FROM Customers
UNION
SELECT Address, Code, Country
FROM Employees
ORDER BY Country;

2.
SELECT AVG(OD.Quantity) AS AverageQuantity, SUM(OD.Quantity * OD.UnitPrice) AS TotalOrderAmount, DATEDIFF(yy, e.BirthDate, e.HireDate) AS HireAge
FROM [Order Details] OD
JOIN Orders o ON ( OD.OrderID =O.OrderID)
JOIN Employees E ON ( E.EmployeeID =O.EmployeeID)
WHERE DATEDIFF(yy, e.BirthDate, e.HireDate) > = 30
GROUP BY DATEDIFF(yy, e.BirthDate, e.HireDate)
ORDER BY DATEDIFF(yy, e.BirthDate, e.HireDate) DESC;

ΕΡΩΤΗΜΑ Ε

1.
SELECT o.ShippedDate, o.OrderDate
FROM Orders o
JOIN Suppliers sp ON (o.EmployeeID = sp.SupplierID)
WHERE sp.CompanyName LIKE '%Pavlova%' ;

2.
SELECT c.CompanyName,