UFO takie jak my - forum

sd · Gość

use Northwind

--select TOP 5 orderid, productid, quantity,
--from [Order Details]
--order by Quantity desc

--select TOP 5 with ties orderid, productid, quantity
--from [Order Details]
--order by Quantity desc

--select COUNT (*) from employees
--*z nullem

--select COUNT (reportsto) from employees
--*bierze pod uwage reportsto (bez nulla)

--select AVG(unitprice) from products

--select SUM(quantity) from [Order Details]

-------------Ćwiczenie
--select COUNT(*) from products where UnitPrice not between 10 and 20
--select TOP 1 unitprice from products where UnitPrice <20> AVG(unitprice)
--select sum(quantity*unitprice*(1-discount)) from [Order Details] where OrderID = 10250

--select sum(quantity*unitprice*(1-discount)) from [Order Details] where OrderID = 10249

--select productid, sum(quantity) as unitsinstock from [Order Details] group by productid

--select MAX(unitprice), productid from products group by productid
--select MAX(unitprice), MIN(unitprice), productid from [Order Details] group by productid

--select TOP 1 Count(*) shipvia from orders
--where Datepart(year,shippeddate) = 1997
--group by shipvia
--order by COUNT(*) desc

--select productid, SUM(quantity) from orderhist group by productid having SUM(quantity)>=30

--select orderid, count(*) from [Order Details]
--group by orderid having COUNT(*)>5

--select customerid, COUNT(*) from orders
--where DATEPART(year,OrderDate) = 1998
--group by customerid having COUNT(*)>8

--select productid, orderid, SUM(quantity)
--from orderhist
--group by productid, orderid
--with rollup
--order by productid, orderid

--select null, null, SUM(quantity)
--from orderhist
--group by productid

--select productid, null, SUM(quantity)
--from orderhist
--group by productid

--select productid, orderid, SUM(quantity)
--from orderhist
--group by productid, orderid
--order by productid, orderid

-- NULL reprezentuje wszystko (wszystkie zamówienia, wszystkie produkty)

--select productid, orderid, quantity
--from orderhist
--order by productid, orderid
--compute sum(quantity)

--select productid, orderid, quantity
--from orderhist
--order by productid, orderid
--compute sum(quantity) by productid
--compute sum(quantity)

Gość

select top 10 with ties Sum(sasd) as sum from orders group by orderid order by sum.

select count (quantity) from order where orderid < 3 group by orderid

Gość

use joindb
-- * Wyswietlenie bazy
select * from Sales
select * from Produce
select * from Buyers

-- * Iloczyn kartezjanski
select b.buyer_name AS [b.buyer_name],
b.buyer_ID AS [b.buyer_id],
s.buyer_ID AS [s.buyer_id],
qty AS [s.qty]
from Buyers AS b, Sales AS s
where b.buyer_name = 'Adam Barr'

-- * Iloczyn
select b.buyer_name AS [b.buyer_name],
b.buyer_ID AS [b.buyer_id],
s.buyer_ID AS [s.buyer_id],
qty AS [s.qty]
from Buyers AS b, Sales AS s
where s.buyer_id = b.buyer_id AND
b.buyer_name = 'Adam Barr'

-- * Join
select buyer_name, sales.buyer_id, qty
from Buyers INNER JOIN Sales
ON Buyers.buyer_id = Sales.buyer_id

use northwind

select productname, companyname from products
Inner join suppliers
On products.supplierid = suppliers.supplierid

select distinct companyname, orderdate from
orders inner join Customers
on Orders.CustomerID = Customers.CustomerID
where OrderDate > '3/1/98'

use library
select companyname, Customers.CustomerID, orderdate
from Customers
left outer join Orders
on Customers.CustomerID = Orders.CustomerID

select firstname, lastname, birth_date
from juvenile inner JOIN member
on juvenile.member_no = member.member_no

select title
from title inner join loan
on title.title_no = loan.title_no

select title_no from title where title = 'Tao Teh King'

select in_date, DATEDIFF(day,in_date,loanhist.due_date) as [days], fine_paid
from loanhist inner join title
on title.title_no = loanhist.title_no
where title.title_no = 24 AND DATEDIFF(day,in_date,loanhist.due_date) > 0
order by [days] desc

select isbn from reservation inner join member
on member.member_no = reservation.member_no
where member.lastname like 'graff'
order by isbn

use Northwind

select productname, unitprice, address from products inner join Suppliers
on Suppliers.SupplierID = Products.supplierid
where UnitPrice BETWEEN 20 AND 30
order by unitprice

select productname, unitsinstock from products inner join Suppliers
on Suppliers.SupplierID = Products.SupplierID
where CompanyName like 'Tokyo Traders'

select companyname, [address] from Customers left outer join Orders
on Orders.CustomerID = Customers.CustomerID and YEAR(OrderDate) = 1997
where OrderDate is null

use joindb

select buyer_name, qty from buyers cross join sales

select buyer_name, prod_name, qty from Buyers inner join Sales
on Buyers.buyer_id = Sales.buyer_id inner join Produce
on Sales.prod_id = Produce.prod_id

use Northwind

select orderdate, productname from orders as o
inner join [Order Details] as od
on o.OrderID = od.OrderID
inner join products as p
on od.ProductID = p.ProductID
where OrderDate = '7/8/96'

use Northwind

select productname, unitprice from products inner join Suppliers
on Products.SupplierID = Suppliers.SupplierID inner join Categories
on Categories.CategoryID = Products.CategoryID where CategoryName like 'meat/poultry'

select productname, unitprice from products inner join Categories
on Products.CategoryID = Categories.CategoryID inner join Suppliers
on Products.SupplierID = Suppliers.SupplierID where CategoryName like 'Confections'

select customers.companyname, customers.phone from Customers inner join Orders
on Orders.CustomerID = Customers.CustomerID inner join shippers
on Orders.ShipVia = Shippers.ShipperID where YEAR(ShippedDate) = 1997 and Shippers.CompanyName = 'United Package'

use Northwind

select distinct customers.CompanyName, Customers.Phone from Customers inner join Orders
on Customers.CustomerID = Orders.CustomerID inner join [Order Details]
on Orders.OrderID = [Order Details].OrderID inner join Products
on [Order Details].ProductID = Products.ProductID inner join Categories
on Products.CategoryID = Categories.CategoryID where CategoryName like 'Confections'

use library

select firstname, lastname, birth_date, adult.street from juvenile inner join member
on juvenile.member_no = member.member_no left outer join adult
on adult.member_no = member.member_no

asda · Gość

--use joindb
--select a.buyer_id AS buyer1, a.prod_id, b.buyer_id AS buyer2
-- from Sales as a join Sales as b
-- on a.prod_id = b.prod_id
-- where a.buyer_id <b> b.buyer_id

use Northwind

--select worker.LastName, worker.FirstName, boss.LastName as reportsTo
-- from Employees as boss
-- inner join Employees as worker on worker.reportsTo = boss.EmployeeID

--select worker.LastName, worker.FirstName
-- from Employees as boss
-- right outer join Employees as worker
-- on worker.ReportsTo = boss.EmployeeID

--use library

--select adult.street from member inner join adult
-- on member.member_no = adult.member_no left outer join juvenile
-- on adult.member_no = juvenile.member_no and YEAR(birth_date) >= 1996

--select adult.street from member inner join adult
-- on member.member_no = adult.member_no left outer join juvenile
-- on adult.member_no = juvenile.member_no and YEAR(birth_date) >= 1996 left outer join loan
-- on member.member_no = loan.member_no
-- where loan.out_date > loan.due_date

--use Northwind

--select (firstname + ' ' + LastName) as name, City, PostalCode, 'Employee' from employees
--UNION
--select companyname, city, postalcode, 'Customer' from Customers
--UNION
--select companyname, city, postalcode, 'Supplier' from Suppliers

use library

--select member.member_no as ID, FirstName, LastName, COUNT(juvenile.adult_member_no) as ChildCounter, 'AZ' as State
-- from member join adult
-- on adult.member_no = member.member_no join juvenile
-- on juvenile.adult_member_no = adult.member_no
-- where adult.state like 'AZ'
-- group by member.member_no, firstname, lastname
-- HAVING COUNT(juvenile.adult_member_no) > 2
--UNION
--select member.member_no as ID, FirstName, LastName, COUNT(juvenile.adult_member_no) as ChildCounter, 'CA'
-- from member join adult
-- on adult.member_no = member.member_no join juvenile
-- on juvenile.adult_member_no = adult.member_no
-- where adult.state like 'CA'
-- group by member.member_no, firstname, lastname
-- HAVING COUNT(juvenile.adult_member_no) > 3
--order by State

use Northwind [/img]