modul 6

create database dbsales_18102029
use dbsales_18102029

create table Customer(
CustomerID int not null primary key,
CustomerName char(50),
ContactName char(50),
Address1 char(50),
City char(20),
PostalCode int,
Country char(30))

create table Suppliers(
SupplierID int not null primary key,
SupplierName char(50),
ContactName char(50),
Address1 char(50),
City char(30),
PostalCode char(30),
Country char(20))

insert into Customer
values (1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str.
57', 'Berlin', 12209, 'Germany')
insert into Customer
values (2, 'Ana Trjillo Emparedados y helados', 'Ana
Trujillo', 'Avda. de la Constitucion 2222', 'Mexico D.F.',
05021, 'Mexico')
insert into Customer
values (3, 'Antonio Moreno Taqueria', 'Antonio Moreno',
'Mataderos 2312', 'Mexico D.F.', 05023, 'Mexico')

select * from Customer

insert into Suppliers
values (1, 'Exotic Liquid', 'Charlotte Cooper', '49 Gilbert
St.', 'London', 'EC1 4SD', 'UK')
insert into Suppliers
values (2, 'New Orleans Cajun Delights', 'Shelley Burke',
'P.o. Box 78934', 'New Orleans', 70117, 'USA')
insert into Suppliers
values (3, 'Grandma Kellys Homestead', 'Regina Murphy', '707
Oxford Rd.', 'Ann Arbor', 48104, 'USA')

select * from Suppliers

select country from Customer
UNION ALL
select country from Suppliers

select country from Customer
union
select country from Suppliers


select city,country from Customer
where Country='Germany'
union all
select city,country from Suppliers
where Country='Germany'
order by city

select 'Customer',ContactName,City,Country
from Customer
union
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers

select 'Customer',ContactName,City,Country
from Customer
where country='Mexico'

insert into Customer(CustomerID,CustomerName,City,Country)
select supplierID,suppliername,city,country
from Suppliers

select * from Customer

insert into Customer(CustomerID,CustomerName,ContactName,Address1,City,Country)
select supplierID,suppliername,ContactName,Address1,City,Country
from Suppliers

insert into Customer(CustomerID,CustomerName,City,Country)
select supplierID,suppliername,city,country
from Suppliers where country='UK'