TEORIE ZPRACOVÁNÍ DAT Cvičení 8 Cvičení je zaměřené na práci v SQL, zejména dotazování. Zadání je rozděleno do typových úloh. Jedna úloha obsahuje základní dotaz a několik alternativních dotazů ke stejné problematice. Studentům je doporučeno vypracovat všechny úlohy samostatně, aby byli schopni sestavit dotazy při zápočtovém testu.
Customer
ProductCategory
CustomerAddrress
Address
Product SalesOrderHeader
SalesOrderDetail
Pokud si chcete data nakopírovat do své databáze, postupujte níže uvedeným způsobem. V opačném případě se v Microsoft SQL Management studiu přihlaste pod účtem studenttzd a s heslem studenttzd společná databáze pak je tzd. (Pozor ve společné databázi TZD jsou i jiné tabulky!) Importování dat do vlastní databáze proveďte skrze nabídku: Programy Microsoft SQL Server 2008 Import and Export Data (32 or 64 bit). Po spuštění programu: 1. Vyberete data source Microsoft Excel a z disku vyberete soubor se zdrojovými daty (shop_data.xls) 2. Jako Destination zvolíte, SQL Server Native Klient 10.0, přihlašovací údaje jsou stejné jako při práci s managementem studiem (win-edu.cs.vsb.cz\mssqlwinedu, pak váš login a heslo) následně v záložce Database vyberete vaši databázi 3. Potom si importujete jednotlivé listy Excel souboru jako tabulky Nebo máte možnost si stáhnout soubor shop.mdf a ten připojit do vaší databáze na lokálním serveru. 1. Nalogujte se do vlastní databáze v MS SQL Server Management Studio 2008 a klikněte pravým tlačítkem na složce Databases. 2. V dané nabídce zvolte možnost „Attach … „, zde pomocí tlačítka „Add“ vyberte soubor shop.mdf a následně potvrďte tlačítkem OK 3. Vytvoří se databáze Shop se strukturou tabulek a datama
Mějme jednoduchou databázi shopu, kde máme sedm tabulek: •
•
•
•
•
Customer - tabulka se zákazníky atributy: o CustomerID – id zákazníka o Title – informace o pohlaví zákazníka o FirstName – jméno zákazníka o MiddleName – prostřední jméno zákazníka o LastName – příjmení zákazníka o Suffix – přípona za jménem o CompanyName – jméno společnosti o SalesPerson – označení prodejce o EmailAddress – emailová adresa zákazníka o Phone – telefon zákazníka o PasswordHash – zahashované heslo zákazníka o PasswordSalt – šifrování hesla o rowguid – vygenerované číslo pro daný řádek v databázi o ModifiedDate – datum editace Address – tabulka adres zákazníků o AddressID – id adresy o AddressLine1 – adresa ulice o AddressLine2 – dodatek k adrese ulice (např. číslo bytu atd.) o City – město o StateProvince – oblast státu o CountryRegion – stát o PostalCode – směrovací číslo o rowguid – vygenerované číslo pro daný řádek v databázi o ModifiedDate – datum editace CustomerAddress – tabulka pro přiřazení adres zákazníkům o CustomerID – id zákazníka o AddressID – id adresy o AddressType – typ adresy (fakturační a poštovní) o rowguid – vygenerované číslo pro daný řádek v databázi o ModifiedDate – datum editace Product – tabulka pro informace o produktech o ProductID – id produktu o Name – název produktu o ProductNumber – katalogové číslo produktu o Color – barva produktu o StandardCost – standardní cena o ListPrice – katalogová cena o Size – velikost o Wight – váha o ProductCategoryID – id kategorie produktu o SellStartDate – datum začátku prodeje o SellEndData – datum ukončení prodeje o rowguid – vygenerované číslo pro daný řádek v databázi o ModifiedDate – datum editace ProductCategory – tabulka pro kategorie o ProductCategoryID – id kategorie produktu o ParentProductCategoryID – id nadřízené kategorie produktu
•
•
o Name – název kateogrie o rowguid – vygenerované číslo pro daný řádek v databázi o ModifiedDate – datum editace SalesOrderHeader – tabulka pro objednávky o SalesOrderID – id objednávky o OrderData – datum vytvoření objednávky o DueDate – datum zaplacení obejdnávky o ShipDate – datum poslání objednávky o Status – status vyřízené objednávky o SalesOrderNumber – číslo objednávky o PurchaseOrderNumber – číslo zaplacení objednávky o AccountNumber – číslo účtu o ShipToAddressId – id adresy pro doruční objednávky o BillToAddressID – id adresy pro fakturu o ShipMethod – způsob odeslání objednávky o SubTotal – cena za objednané zboží o TaxAmt – daň za objednané zboží o Freight – cena poštovného o TotalDue – celková cena za objednávku (= SubTotal + TaxAmt + Freight) o Comment – komentář o rowguid – vygenerované číslo pro daný řádek v databázi o ModifiedDate – datum editace SalesOrderDetail – tabulka pro položky v objednávkce o SalesOrderID – id objednávky o SalesOrderDetailID – id položky o OrderQty – počet daného produktu o ProductID – id produktu o UnitPrice – cena za jednotku produktu o UnitPriceDiscount – sleva v procentech o LineTotal – konečná cena za pložku (= UnitPrice * OrderQty * (1 - UnitPriceDiscount) ) o rowguid – vygenerované číslo pro daný řádek v databázi o ModifiedDate – datum editace
Zadání úloh k řešení: 1. Vypište informace o zákaznících s příjmením "Miller" [5 z.] SELECT * FROM Customer WHERE LastName = 'Miller';
2. Vypište informace o zákaznících setřízený sestupně podle příjmení [440 z.] SELECT * FROM Customer ORDER BY LastName DESC;
3. Vypište jména hlavích kategorií velkými písmeny [4 z.] SELECT UPPER([Name]) FROM ProductCategory WHERE ParentProductCategoryID IS NULL;
4. Vypište seznam produktů, jejichž barva je červená ("RED") a váha je větší než 1000 g [34 z.] SELECT * FROM Product WHERE Color = 'Red' AND Weight > '1000';
5. Vypište seznam adres, kde oblast (StateProvince) obsahuje řetězec "COL" [30 z.] SELECT * FROM Address WHERE StateProvince LIKE '%col%';
6. Vypište celkovou částku zaokrouhlenou na celá čísla, maximánlní a minimální cenu za poštovné, průměrnou cenu za daň objednaného zboží [1 z.] SELECT ROUND(SUM(SubTotal),-0), MAX(Freight), MIN(Freight), AVG(TaxAmt) FROM SalesOrderHeader;
7. Vypište příjmení a počet zákazníků s daným příjmením [398 z.] SELECT LastName, COUNT(*) AS Count
FROM Customer GROUP BY lastname;
8. Vypište státy (CountryRegion), které mají více než 5 oblastí [2 z.] SELECT CountryRegion FROM Address GROUP BY CountryRegion HAVING COUNT(DISTINCT StateProvince) > 5;
9. Vypište všechny informace o produktech a k nim název kategorie, do které spadají [295 z.] SELECT P.*, PC.Name FROM Product AS P INNER JOIN ProductCategory AS PC ON P.ProductCategoryID=PC.ProductCategoryID;
10. Vypište mìsto, oblast a stát pro zakázky, jejichž celková cena (TotalDue) je v rozmezí 50 000 a 100 000 [6 z.] SELECT City, StateProvince, CountryRegion FROM Address AS A INNER JOIN SalesOrderHeader AS S ON A.addressID=S.ShipToAddressID WHERE TotalDue BETWEEN 50000 AND 100000;
11. Vypište příjmení, prefix emailové adresy (řetězec před @) a délku celé emailové adresy pro zákazníky, kteří nežijí v USA (United Stats) nebo Kanadě (Canada) [39 z.] SELECT LastName, LEFT(EmailAddress,CHARINDEX('@',EmailAddress)-1) AS EmailPrefix, LEN(AddressLine1) AS LenghtStreet FROM Customer AS C INNER JOIN CustomerAddress AS CA ON C.CustomerID=CA.CustomerID INNER JOIN Address AS A ON A.AddressID=CA.AddressID WHERE CountryRegion NOT IN ('United States', 'Canada');
12. Vypište názvy státù tak, aby místo United States bylo vypsánio USA a počet oblastí těchto státù [3 z.]
SELECT REPLACE([CountryRegion],'United States', 'USA') AS 'CountryRegion', COUNT(StateProvince) AS 'Count' FROM Address GROUP BY CountryRegion;
13. Vypište příjmení zákazníkù, kteří mají evidovanou adresu pro fakturu a zároven poštovní [10 z.] SELECT LastName FROM Customer AS C INNER JOIN CustomerAddress AS CA ON C.CustomerID=CA.CustomerID GROUP BY C.CustomerID, LastName HAVING COUNT(AddressType) > 1;
14. Vypište příjmení zákazníkù, kteří nemají uvedenou adresu (pomocí vnořeného dotazu) [6 z.] SELECT LastName FROM Customer WHERE CustomerID NOT IN (SELECT CustomerID FROM CustomerAddress);
15. Vypište informace o zákaznicích, kteří měli minimálně jednu objednávku (pomocí spojení tabulek) [408 z.] SELECT C.* FROM Customer AS C LEFT JOIN SalesOrderHeader AS SOH ON C.CustomerID=SOH.CustomerID WHERE SOH.CustomerID IS NULL;
16. Vypište informace o zákaznicích, kteří mìli minimálně jednu objednávku s počtem položek větší než 10 [13 z.] SELECT C.* FROM Customer AS C INNER JOIN SalesOrderHeader AS SOH ON C.CustomerID=SOH.CustomerID WHERE SOH.SalesOrderID IN (SELECT SalesOrderID FROM SalesOrderDetail GROUP BY SalesOrderID HAVING COUNT(SalesOrderDetailID) > 10);
17. Vypište zákazníky s nejkratším a nejdelším příjmením [4 z.] SELECT * FROM Customer WHERE LEN(LastName) IN (SELECT MIN(LEN(LastName)) FROM Customer UNION ALL SELECT MAX(LEN(LastName)) FROM Customer);
