SQT TEST CASES

--SQL TEST CASES
--OK

SELECT Cus.Name , Cus.Country FROM ( Customers AS Cus INNER JOIN City ON Cus.City = City.ID ) ,  Clients AS Cl
WHERE Cus.ID > 5 AND ( City<>'London' OR NOT Cl.INDEX = 12 )
AND Cus.ID IN (SELECT Cus1.ID FROM Customers AS Cus1 WHERE Cus1.License=1)
ORDER BY Country;



SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;



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



SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;



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;



SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;



SELECT *
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22)
AND NOT EXISTS (SELECT 1 FROM Orders WHERE Suppliers.supplierId = SupplierId AND Licence <> 1)
GROUP BY supplierId
HAVING totalAmount > 1000
ORDER BY supplierId ASC;



SELECT Cus.Name , Cus.Country FROM ( Customers AS Cus INNER JOIN City ON Cus.City = City.ID ) ,  Clients AS Cl
--comment
--1
WHERE Cus.ID > 5 AND ( City<>'London' OR  Cl.INDEX = 12 )
AND Cus.ID IN (SELECT Cus1.ID FROM Customers AS Cus1 WHERE Cus1.License=1)
/* this is
    Comment
        2
            */
ORDER BY Country;

--comment3



SELECT DISTINCT S1.SNAME
FROM S AS S1 , S AS S2
WHERE S2.SNAME='JOHN' AND
NOT EXISTS(SELECT 1
FROM SPJ
WHERE SPJ.SN = S2.SN AND
NOT EXISTS(SELECT 1 FROM SPJ AS PJ
WHERE PJ.SN = S1.SN AND PJ.PN=SPJ.PN))



SELECT S1.SNAME
FROM S AS S1
WHERE NOT EXISTS (
        SELECT P.PN
        FROM (P INNER JOIN SPJ ON P.PN = SPJ.PN) INNER JOIN S ON S.SN = SPJ.SN
        WHERE S.SNAME = 'JOHN' AND P.PN NOT IN (
                SELECT P.PN FROM P INNER JOIN SPJ ON P.PN = SPJ.PN WHERE SPJ.SN = S1.SN
        )
);

--WRONG CASES

SELECT totalAmount
 Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22)
AND NOT EXISTS (SELECT 1 FROM Orders WHERE Suppliers.supplierId = SupplierId AND Licence <> 1)
GROUP BY supplierId
HAVING totalAmount > 1000
ORDER BY supplierId ASC;



SELECT totalAmount
FROM Suppliers
WHERE EXISTS (SELECT ProductName FORM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22)
AND NOT EXISTS (SELECT 1 FROM Orders WHERE Suppliers.supplierId = SupplierId AND Licence <> 1)
GROUP BY supplierId
HAVING totalAmount > 1000
ORDER BY supplierId ASC;



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



SELECT Cus.Name , Cus.Country FROM ( Customers AS Cus INNER JOIN City ON Cus.City = City.ID ) ,  Clients AS Cl
WHERE Cus.ID > 5 AND ( City<>'London' OR AND Cl.INDEX = 12 )
AND Cus.ID IN (SELECT Cus1.ID FROM Customers AS Cus1 WHERE Cus1.License=1)
ORDER BY Country;



SELECT *
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22)
AND NOT EXISTS (SELECT 1 FROM Orders WHERE Suppliers.supplierId = SupplierId AND Licence <> 1)

HAVING totalAmount > 1000
GROUP BY supplierId
ORDER BY COUNT(supplierId) ASC;