daily pastebin goal
31%
SHARE
TWEET

Untitled

a guest Dec 16th, 2018 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --
  2. -- SAFE ZONE
  3. --
  4.  
  5. -- dodaj usera (bez karty)
  6. insert into Users(Name, Surname, PersonalId) values ('Jan', 'Kowalski', '<numer dokumentu>');
  7.  
  8. -- pokaż userów
  9. select * from Users;
  10.  
  11. -- pokaż karty
  12. select * from TransportCards
  13.   join CardTypeDictionaries CTD on TransportCards.CardType = CTD.CardTypeId
  14. ;
  15.  
  16. -- pokaż userów i ich karty
  17. select User_Id, Name, Surname, PersonalId, IsActive, Description from Users
  18.   inner join TransportCards TC on Users.Id = TC.User_Id
  19.   inner join CardTypeDictionaries CTD on TC.CardType = CTD.CardTypeId
  20. ;
  21.  
  22. -- pokaż typy biletów
  23. select TPD.Description as "Time", DTD.Description as "Discount", ZD.Description as "Zone"
  24. from TicketTypes
  25.   inner join TicketPeriodDictionaries TPD on TicketTypes.TicketPeriod = TPD.TicketPeriodId
  26.   inner join ZoneDictionaries ZD on TicketTypes.Zone = ZD.ZoneId
  27.   inner join DiscountTypeDictionaries DTD on TicketTypes.DiscountType = DTD.DiscountTypeId
  28. ;
  29.  
  30. -- pokaż transakcje
  31. select * from Transactions order by Id;
  32.  
  33. -- pokaż ilość biletów w każdej transakcji
  34. select
  35.        Transaction_Id as "Transaction ID",
  36.        count(Transaction_Id) as "Number of tickets"
  37. from Tickets
  38. group by Transaction_Id;
  39.  
  40. -- pokaż bilety
  41. select * from Tickets;
  42.  
  43. -- pokaz bilety papierowe
  44. select Tickets.Id,
  45.        IssuedPrice as "Issued price (in PLN)",
  46.        TPD.Description as "Time",
  47.        ZD.Description as "Zone",
  48.        DPD.Description as "Discount type",
  49.        T.Date as "Transaction date"
  50. from Tickets
  51. inner join Transactions T on Tickets.Transaction_Id = T.Id
  52. inner join TicketTypes TT on Tickets.TicketType_Id = TT.Id
  53. inner join ZoneDictionaries ZD on TT.Zone = ZD.ZoneId
  54. inner join TicketPeriodDictionaries TPD on TPD.TicketPeriodId = TT.TicketPeriod
  55. inner join DiscountTypeDictionaries DPD on DPD.DiscountTypeId = TT.DiscountType
  56. where Card_Id is null
  57. order by Tickets.Id
  58. ;
  59.  
  60. -- pokaz bilety elektroniczne
  61. select Tickets.Id,
  62.        IssuedPrice as "Issued price (in PLN)",
  63.        TPD.Description as "Time",
  64.        ZD.Description as "Zone",
  65.        DPD.Description as "Discount type",
  66.        ValidFromDate as "From",
  67.        ValidToDate as "To"
  68. from Tickets
  69. inner join TicketTypes TT on Tickets.TicketType_Id = TT.Id
  70. inner join ZoneDictionaries ZD on TT.Zone = ZD.ZoneId
  71. inner join TicketPeriodDictionaries TPD on TPD.TicketPeriodId = TT.TicketPeriod
  72. inner join DiscountTypeDictionaries DPD on DPD.DiscountTypeId = TT.DiscountType
  73. where Card_Id is not null
  74. order by Card_Id
  75. ;
  76.  
  77. -- pokaż transakcje i wszystkie bilety
  78. select Tickets.Id as "Ticket ID",
  79.        T.Id as "Transaction ID",
  80.        TPD.Description as "Ticket period",
  81.        ZD.Description as " Zone ",
  82.        DTD.Description as "Discount type"
  83. from Tickets
  84.   inner join Transactions T on T.Id = Tickets.Transaction_Id
  85.   inner join TicketTypes TT on Tickets.TicketType_Id = TT.Id
  86.   inner join TicketPeriodDictionaries TPD on TT.TicketPeriod = TPD.TicketPeriodId
  87.   inner join ZoneDictionaries ZD on TT.Zone = ZD.ZoneId
  88.   inner join DiscountTypeDictionaries DTD on TT.DiscountType = DTD.DiscountTypeId
  89. where TPD.Description in ('Month', 'Three months')
  90. order by T.Id, TPD.Description
  91. ;
  92.  
  93. -- ile biletów łącznie
  94. select
  95.   count(*) as "Total number of tickets",
  96.   (select count(*) from Tickets where Card_Id is not null) as "Number of card tickets",
  97.   (select count(*) from Tickets where Card_Id is null) as "Number of paper tickets"
  98. from Tickets
  99. ;
  100.  
  101. -- ile userów łącznie
  102. select count(*) as "Number of users" from Users;
  103.  
  104. -- status bazy
  105. select
  106.   (select count(*) from Users) as "Number of users",
  107.   (select count(*) from TransportCards) as "Number of cards",
  108.   (select count(*) from Tickets) as "Total number of tickets",
  109.   (select count(*) from Tickets where Card_Id is not null) as "Number of card tickets",
  110.   (select count(*) from Tickets where Card_Id is null) as "Number of paper tickets",
  111.   (select count(*) from Transactions) as "Number of transactions"
  112. ;
  113.  
  114. --
  115. -- DANGER ZONE
  116. --
  117.  
  118. -- usuń wszystkie bilety elektroniczne
  119. delete from Tickets where Card_Id is not null;
  120.  
  121. -- zaoraj tabelki
  122. begin transaction;
  123.   delete from Tickets;
  124.   delete from TransportCards;
  125.   delete from Users;
  126.   delete from TicketTypes;
  127.   delete from Transactions;
  128. commit;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top