SHARE
TWEET

modul 6

a guest Oct 21st, 2019 97 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create database dbsales_18102029
  2. use dbsales_18102029
  3.  
  4. create table Customer(
  5. CustomerID int not null primary key,
  6. CustomerName char(50),
  7. ContactName char(50),
  8. Address1 char(50),
  9. City char(20),
  10. PostalCode int,
  11. Country char(30))
  12.  
  13. create table Suppliers(
  14. SupplierID int not null primary key,
  15. SupplierName char(50),
  16. ContactName char(50),
  17. Address1 char(50),
  18. City char(30),
  19. PostalCode char(30),
  20. Country char(20))
  21.  
  22. insert into Customer
  23. values (1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str.
  24. 57', 'Berlin', 12209, 'Germany')
  25. insert into Customer
  26. values (2, 'Ana Trjillo Emparedados y helados', 'Ana
  27. Trujillo', 'Avda. de la Constitucion 2222', 'Mexico D.F.',
  28. 05021, 'Mexico')
  29. insert into Customer
  30. values (3, 'Antonio Moreno Taqueria', 'Antonio Moreno',
  31. 'Mataderos 2312', 'Mexico D.F.', 05023, 'Mexico')
  32.  
  33. select * from Customer
  34.  
  35. insert into Suppliers
  36. values (1, 'Exotic Liquid', 'Charlotte Cooper', '49 Gilbert
  37. St.', 'London', 'EC1 4SD', 'UK')
  38. insert into Suppliers
  39. values (2, 'New Orleans Cajun Delights', 'Shelley Burke',
  40. 'P.o. Box 78934', 'New Orleans', 70117, 'USA')
  41. insert into Suppliers
  42. values (3, 'Grandma Kellys Homestead', 'Regina Murphy', '707
  43. Oxford Rd.', 'Ann Arbor', 48104, 'USA')
  44.  
  45. select * from Suppliers
  46.  
  47. select country from Customer
  48. UNION ALL
  49. select country from Suppliers
  50.  
  51. select country from Customer
  52. union
  53. select country from Suppliers
  54.  
  55.  
  56. select city,country from Customer
  57. where Country='Germany'
  58. union all
  59. select city,country from Suppliers
  60. where Country='Germany'
  61. order by city
  62.  
  63. select 'Customer',ContactName,City,Country
  64. from Customer
  65. union
  66. SELECT 'Supplier', ContactName, City, Country
  67. FROM Suppliers
  68.  
  69. select 'Customer',ContactName,City,Country
  70. from Customer
  71. where country='Mexico'
  72.  
  73. insert into Customer(CustomerID,CustomerName,City,Country)
  74. select supplierID,suppliername,city,country
  75. from Suppliers
  76.  
  77. select * from Customer
  78.  
  79. insert into Customer(CustomerID,CustomerName,ContactName,Address1,City,Country)
  80. select supplierID,suppliername,ContactName,Address1,City,Country
  81. from Suppliers
  82.  
  83. insert into Customer(CustomerID,CustomerName,City,Country)
  84. select supplierID,suppliername,city,country
  85. from Suppliers where country='UK'
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