— Kasutaja loomine

Create database kasutajaTAR;
use kasutajaTAR;
— Security –> Login –> New Login

Create table test(
id int primary key,
nimetus varchar(10));

— õiguste määramine
–GRANT INSERT ON …NAzvanie table… TO ..kasuatajanimi..;
GRANT INSERT ON test TO opilaneJekaterina;
–õiguste keelamine
DENY DELETE ON test TO opilaneJekaterina;

Logime sisese opilaneJekaterina ja proovime

use kasutajaTAR;

SELECT * from test;
INSERT INTO test(id, nimetus)
VALUES (1, ‘neljapaev’);
DELETE FROM test;
CREATE TABLE test(id, int)

Задание 1.

Create database MovieBase;
use MovieBase;

CREATE TABLE movies(
id int primary key,
moviesNimi varchar(35),
moviesYear int,
movieDir varchar(35),
movieCost int,
);

CREATE TABLE guest(
id int primary key,
name varchar(15),
);

INSERT INTO movies(id, moviesNimi, moviesYear, movieDir, movieCost)
VALUES (1, ‘Maleficent’, 2014, ‘Stromberg’, 758);

INSERT INTO movies(id, moviesNimi, moviesYear, movieDir, movieCost)
VALUES (2, ‘Faster’, 2010, ‘George Tillman’, 78);

INSERT INTO movies(id, moviesNimi, moviesYear, movieDir, movieCost)
VALUES (3, ‘Raggie’, 2020, ‘Meelis Arulepp’, 788);

INSERT INTO movies(id, moviesNimi, moviesYear, movieDir, movieCost)
VALUES (4, ‘Eternals’, 2021, ‘Chloé Zhao’, 786);

INSERT INTO movies(id, moviesNimi, moviesYear, movieDir, movieCost)
VALUES (5, ‘The Gorge’, 2022, ‘Scott Derrickson’, 7866);

INSERT INTO movies(id, moviesNimi, moviesYear, movieDir, movieCost)
VALUES (6, ‘Tomorrowland’, 2015, ‘Brad Bird’, 78682);

INSERT INTO movies(id, moviesNimi, moviesYear, movieDir, movieCost)
VALUES (7, ‘Kraven the Hunter’, 2024, ‘J. C. Chandor’, 78);

SELECT * FROM movies;

Kasutajate loomine ja õiguste määramine

XAMPP

Ma loon andmebaasi ja tabeli reservations SQL Serveris, määrates veergude struktuuri.

Ma lisan andmeid tabelisse reservations, sealhulgas külaliste nimed, toa numbrid ja saabumis-lahkumiskuupäevad

status ENUM(‘confirmed’, ‘canceled’, ‘pending’) NOT NULL DEFAULT ‘pending’ – статус бронирования с предопределёнными значениями:

confirmed – подтверждено,
canceled – отменено,
pending – в ожидании (по умолчанию).

Looge kasutaja

Ma kontrollin, kas kasutaja hotel_manager saab teha INSERT, SELECT ja DELETE operatsioone tabelis reservations.

INSERT INTO reservations(guest_name, room_number, check_in_date, check_out_date)

VALUES (‘Jekaterina’, 1, ‘2025-02-18’, ‘2025-02-19’),

(‘Iryna’, 4, ‘2025-02-27’, ‘2025-02-28’),

(‘Erik’, 5, ‘2025-02-29’, ‘2025-03-01’),

(‘Itan’, 1, ‘2025-02-20’, ‘2025-02-27’);

PROOVIME mis peab olla keelatud

SQL SERVER

Create database hotel_booking;
use hotel_booking;

CREATE TABLE reservations(
id INT PRIMARY KEY,
guest_name varchar(100),
room_number int,
check_in_date date,
check_out_date date,
status varchar(3),
);

INSERT INTO reservations(id, guest_name, room_number, check_in_date, check_out_date, status)
VALUES (1, ‘Jekaterina’, 2, ‘2025-02-27’, ‘2025-02-28’, ‘on’);

INSERT INTO reservations(id, guest_name, room_number, check_in_date, check_out_date, status)
VALUES (2, ‘Iryna’, 2, ‘2025-02-20’, ‘2025-02-22’, ‘on’);

INSERT INTO reservations(id, guest_name, room_number, check_in_date, check_out_date, status)
VALUES (3, ‘Erik’, 2, ‘2025-03-27’, ‘2025-03-28’, ‘off’);

SELECT * FROM reservations;