SQL

CREATE DATABASE uktc;

USE uktc;

CREATE TABLE students (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
firstname VARCHAR(100) NOT NULL,
lastname VARCHAR(100),
age INT,
class INT,
city VARCHAR(100),
email VARCHAR(200)
);

INSERT INTO students
VALUES (1, 'Slavi', 'Trifinov', 32, 171, 'Kaspichan', 'slavkata@uktc.com');

INSERT INTO students (id, firstname, lastname, age, class, city, email)
VALUES (2, 'Kiro', 'Breika', 45, 171, 'V.T.', 'kiro@uktc.com');

INSERT INTO students (firstname, lastname, age)
VALUES ('Ani', 'Borisova', 45);

INSERT INTO students
VALUES (5, 'Stamat', 'Ivanov', 32, 171, 'Kaspichan', 'slavkata@uktc.com');


DELETE FROM students WHERE id = 4;

UPDATE students SET city = 'Novi Pazar' WHERE firstname = 'Slavi';

SELECT * FROM students;


CREATE TABLE orders(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ordernum INT NOT NULL,
orderdesc VARCHAR(500),
studentid INT,
FOREIGN KEY (studentid) REFERENCES students(id)
);

INSERT INTO orders
VALUES (1, 3243, 'obuvki', 1);

INSERT INTO orders
VALUES (2, 545, 'uchevnici', 1);

INSERT INTO orders
VALUES (3, 545, 'risunki', 1);

INSERT INTO orders
VALUES (4, 767, 'drehi', 2);

SELECT * FROM orders;

UPDATE orders SET ordernum=545 WHERE id=2;

SELECT orders.id, orders.ordernum, orders.orderdesc, students.firstname
FROM orders
INNER JOIN students ON orders.studentid = students.id;