SQL 08-12-18

SELECT custid, companyname, country
from Sales.Customers
where country = N'UK';

SELECT TOP(5) orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

SELECT TOP(5) WITH TIES orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

SELECT TOP(10) Percent orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate DESC;

SELECT orderid, custid, orderdate
FROM Sales.Orders
ORDER BY orderdate, orderid DESC
OFFSET 0 ROWS FETCH NEXT 10 ROW ONLY;

--1.
SELECT custid, companyname, contactname, address, city,
country, phone
FROM Sales.Customers
WHERE country = 'Brazil';
--2.
SELECT custid, companyname, contactname, address, city,
country, phone
FROM Sales.Customers
WHERE country = 'Brazil' or country = 'USA' or country = 'UK';
--3.
SELECT custid, companyname, contactname, address,
city, country, phone
FROM Sales.Customers
WHERE contactname LIKE 'a%'
--4.
SELECT
c.custid, c.companyname, o.orderid
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON c.custid = o.custid AND c.city = 'Paris';

SELECT
c.custid, c.companyname, o.orderid
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON c.custid = o.custid
WHERE c.city = 'Paris';
--5.
SELECT
c.custid, o.orderid
FROM Sales.Customers as c
LEFT OUTER JOIN Sales.Orders as o
on c.custid = o.custid
WHERE o.orderid is NULL;
--6.
SELECT c.custid, c.contactname, o.orderdate, o.orderid
FROM Sales.Customers as c
LEFT OUTER JOIN Sales.Orders as o
on c.custid = o.custid
WHERE o.orderdate >= '2008-04-01'
ORDER BY o.orderdate desc, c.custid asc;
--7.
SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m
ON e.mgrid = m.empid
WHERE
m.lastname = N'Buck';
--8.
SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON e.mgrid = m.empid
ORDER BY m.firstname ASC;
--9.
SELECT TOP(20) orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate desc;
--10.
SELECT orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate desc
OFFSET 0 ROWS FETCH NEXT 20 ROW ONLY;
--11.
SELECT TOP(10) Percent productname, unitprice
FROM Production.Products
ORDER BY unitprice;

SELECT productname, unitprice
FROM Production.Products
ORDER BY unitprice
OFFSET 0 ROWS FETCH NEXT 8 ROW ONLY;
--12
SELECT TOP(20) custid, orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate, orderid;
--13
SELECT custid, orderid, orderdate
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 20 ROWS FETCH NEXT 20 ROW ONLY;

--------------------------------------------------------------------