Tabela KLIENCI
Utworzenie tabeli KLIENCI
CREATE TABLE customers
(customer_numb integer NOT NULL,
customer_first_name varchar(15) ,
customer_last_name varchar(15) ,
customer_phone char(12) ,
customer_email varchar(40)
, PRIMARY KEY (customer_numb));
Wprowadzanie rekordów do tabeli KLIENCI
insert into customers values
(1,'Jane','Jones','552-555-1234','jane_jones@anywhere.net');
insert into customers values
(2,'Tom','Smith','555-555-4321','tom_smith@this.net');
insert into customers values
(3,'Mary','Johnson','551-555-4567','mary_johnson@somewhere.net');
insert into customers values
(4,'John','Smith','552-555-9876','john_smith@somewhere.net');
insert into customers values
(5,'Emily','Jones','555-555-7654','emily_jones@somewhere.net');
Tabela ZAMÓWIENIA
1) Utworzenie tabeli ZAMÓWIENIA
CREATE TABLE orders
(order_numb integer NOT NULL,
customer_numb integer NOT NULL,
order_date date NOT NULL,
price real,
PRIMARY KEY (order_numb),
FOREIGN KEY orders2customers (customer_numb)
REFERENCES customers
ON DELETE RESTRICT
);
2) Wprowadzanie rekordów do tabeli ZAMÓWIENIA
insert into orders values (1,5,'2004/12/5',2259.90);
insert into orders values (2,3,'2005/7/6',547.70);
insert into orders values (3,4,'2004/11/12',125.78);
insert into orders values (4,6,'2005/3/18',233);
insert into orders values (5,2,'2005/3/18',3456);
Połączenia w SQL
1) wykonaj równozłączenia poprzez klucze główne
SELECT customer_first_name, customer_last_name, order_numb, order_date FROM
customers JOIN orders WHERE customers. customer_numb=orders. customer_numb;
2) wykonaj iloczyn tabel
SELECT customer_first_name, customer_last_name, order_numb, order_date FROM customers JOIN orders;
Tabela wydawnictwo
CREATE TABLE wydawnictwo
( idwyd INT AUTO_INCREMENT PRIMARY KEY,
nazwawyd VARCHAR(30));
INSERT INTO wydawnictwo (nazwawyd) VALUES
(`Biały Kruk'), (`Znak'), (`Mikom'), (`Arkady'), (`Helion');