-- SQL KEELE HARJUTUSED
--Access'i näidisABfail AK213C-s: C:\PROGRAM FILES\DEVSTUDIO\VB\NWIND.MDB
--päringu tegemiseks Database>Queries>New>Design View>Tables>Close>(View>)SQL View
--5.3.98 HARJUTUSED
--leida Andrew Fuller'i alluvad. NB! 1 tabel (employees), 2 nime (a,b).
SELECT a.firstname as eesnimi, a.lastname as perenimi
from employees a, employees b
where a.reportsto=b.employeeid and b.firstname="Andrew" and b.lastname="Fuller";
SELECT p.unitprice as tegelik_hind, o.unitprice as hind_arvel, o.discount as allahindlus,
p.unitprice*(1-o.discount)-o.unitprice as vahe
from [order details] o, products p
where p.productid=o.productid;
--kogusumma arvel
SELECT orderid as arve_nr, sum(unitprice*quantity) as summa from [order details]
group by orderid;
--mitu arvet on mingi töötaja kirjutand
SELECT lastname as perenimi, count(o.employeeid) as arveid
from orders o, employees e
where o.employeeid=e.employeeid
group by o.employeeid, lastname;
-- tellimuste täitmisajad
SELECT avg(shippeddate-orderdate) as keskmine,
min(shippeddate-orderdate) as pisim,
max(shippeddate-orderdate) as suurim from orders;
--19.3.98 HARJUTUSED
--kes võeti Buchananiga sama päev tööle?
SELECT lastname, firstname from employees
where hiredate=
(select hiredate from employees where firstname="Steven" and lastname="Buchanan"
--leia need arved, mille täitmisaeg on keskmisest suurem
SELECT e.FirstName, Count(o.EmployeeID) AS Expr1
FROM employees AS e INNER JOIN orders AS o ON e.EmployeeID = o.EmployeeID
WHERE ((([shippeddate]-[orderdate])>(select avg(shippeddate-orderdate) from orders)))
GROUP BY e.FirstName;
--kes võttis töölevõtmispäeval tellimusi vastu
SELECT employees.LastName
FROM employees, orders
WHERE orders.OrderDate=[employees].[hiredate];
--väljastada Londonis elavate töötajate ja klientide nimed
select e.lastname from employees e
where e.city='London'
UNION
select c.companyname from customers c where c.city='London';
--2.4.98 KONTROLLTÖÖ
--leia tellimuste keskmine summa
--selleks teha 2 päringut.
--1)teha päring nimega SUMMAD, mis leiab iga arve summa :
SELECT Sum([unitprice]*quantity) AS arved
FROM orderdetails AS o
GROUP BY o.OrderID;
--2)leida päringu SUMMAD põhjal keskmine :
SELECT avg(arved) AS Keskmine
FROM summad;
--leia töölevõetute arvud kuupäevade lõikes
SELECT [hiredate], count([lastname]) AS palgati
FROM employees
GROUP BY [hiredate]