cYx8j6yB

1use bruw;
2
3drop table if exists Customer, Orders, OrderProduct, 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
33CREATE TABLE Orders
34(
35order_id INT NOT NULL AUTO_INCREMENT,
36customer_id INT NOT NULL,
37orderdate DATETIME NOT NULL,
38orderstatus ENUM('SENT','DELIVERED'),
39PRIMARY KEY (order_id),
40FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
41);
42
43#2.5
44
45  INSERT INTO Orders VALUES 
46  (NULL, 1, '2006-08-11', 'SENT'),
47  (NULL, 1, '2006-08-14', 'DELIVERED'),
48  (NULL, 3, '2006-08-11', 'SENT'),
49  (NULL, 3, '2006-08-14', 'DELIVERED');
50  
51  #2.6,7
52  
53SELECT firstname, lastname, orderdate FROM customer, orders;
54
55#3
56
57CREATE TABLE product
58(
59product_id INT(10) NOT NULL AUTO_INCREMENT,
60productname VARCHAR(30) NOT NULL,
61price float NOT NULL,
62description VARCHAR(300) NOT NULL,
63instock VARCHAR (30) NOT NULL,
64  PRIMARY KEY (product_id)
65)ENGINE=InnoDB DEFAULT CHARSET=latin1;
66
67create table orderline
68(
69order_id INT UNSIGNED NOT NULL,
70product_id INT(10) NOT NULL,
71quantity INT NOT NULL,
72CONSTRAINT `orderline_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),
73CONSTRAINT `orderline_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)
74);
75 
76INSERT INTO Product VALUES
77('4858328', 'Samsung 75 Smart LED TV', 14000, 'Med Samsungs avanserte Clear Motion Rate-teknologi CMR', 8),
78('348294', 'Yamaha YAS105 Soundbar', 1800, 'En solid høytaller med elegant utseende', 20),
79('4958323', 'LG 49 UHD LED Smart TV', 4000, 'En helt grei tv med HDR Pro og color mapping', 12);
80
81INSERT INTO Orderline VALUES (1,'4858328',2),(1,'348294',1),(2,'4958323',1),(3,'34829',1),(3,'4858328',1);