LiPtBJYr

1use bruw;
2
3drop table if exists Customer, Orders, orderline, Product;
4
5#1.3
6CREATE TABLE Customer
7(
8customer_id INT NOT NULL AUTO_INCREMENT,
9email varchar(30) NOT NULL,
10firstname varchar(30) NOT NULL,
11lastname varchar(30) NOT NULL,
12billingadress VARCHAR(60) NOT NULL,
13PRIMARY KEY (customer_id)
14);
15
16#1.4
17INSERT INTO Customer (Customer_id, email, firstname, lastname, billingadress) VALUES (null, "eirsla14@uia.no", "Eirik", "Slagnes", "Oberstlnt. Omdalsvei 75");
18INSERT INTO Customer (Customer_id, email, firstname, lastname, billingadress) VALUES (null, "Birbog18@uia.no", "Birger", "Bogen", "DenNyeGata 6");
19INSERT INTO Customer (Customer_id, email, firstname, lastname, billingadress) VALUES (null, "kakesalg@uia.no", "Jonas", "Fredriksen", "Fiskevegen 17");
20
21UPDATE Customer 
22SET billingadress="DenNyeGata 7" WHERE customer_id=1;
23
24#1.5
25SELECT Customer.Customer_id, Customer.email FROM Customer;
26SELECT * FROM Customer;
27
28#1.6
29SELECT Customer.Customer_id, Customer.email FROM Customer;
30SELECT * FROM Customer WHERE lastname like '%nes';
31
32#2.4
33
34CREATE TABLE Orders
35(
36order_id INT NOT NULL,
37customer_id INT NOT NULL,
38orderdate DATETIME NOT NULL,
39orderstatus ENUM('SENT','DELIVERED'),
40PRIMARY KEY (order_id),
41FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
42);
43
44#2.5
45
46  INSERT INTO Orders VALUES 
47  (1234, 1, '2015-08-11', 'SENT'),
48  (2345, 1, '2015-08-14', 'DELIVERED'),
49  (3456, 3, '2015-08-11', 'SENT'),
50  (4567, 3, '2015-08-14', 'DELIVERED');
51  
52  #2.6,7
53  
54SELECT firstname, lastname, orderdate FROM customer, orders;
55
56#3
57
58CREATE TABLE if not exists product
59(
60product_id INT(10) NOT NULL AUTO_INCREMENT,
61productname VARCHAR(30) NOT NULL,
62price float NOT NULL,
63description VARCHAR(300) NOT NULL,
64instock VARCHAR (30) NOT NULL,
65  PRIMARY KEY (product_id)
66)ENGINE=InnoDB DEFAULT CHARSET=latin1;
67
68create table orderline
69(
70order_id INT NOT NULL,
71product_id INT(10) NOT NULL,
72quantity INT NOT NULL,
73FOREIGN KEY (order_id) REFERENCES Orders (order_id),
74FOREIGN KEY (product_id) REFERENCES product (product_id),
75PRIMARY KEY (order_id, product_id));
76
77
78SELECT * FROM orderline WHERE order_id = 2345;
79 
80INSERT INTO Product VALUES
81('4858328', 'Samsung 75 Smart LED TV', 14000, 'Med Samsungs avanserte Clear Motion Rate-teknologi CMR', 8),
82('3482946', 'Yamaha YAS105 Starbar', 1800, 'En solid høytaller med det elegante star utseende', 20),
83('4958323', 'LG 49 UHD LED Smart TV', 4000, 'En helt grei tv med HDR Pro og color mapping', 12);
84
85INSERT INTO Orderline VALUES (1,'4858328',2),(1,'348294',1),(2,'4958323',1),(3,'34829',1),(3,'4858328',1);