DB - Random adding data to the database - Darja Suhhanova (Portnova)

create a database with the name epood_yourusername
create table customerAuto (kliendiAuto )
create fields (columns) to store data with a specific data type:
Add the details of at least 3 customers, one of which is you.
Show all data

CREATE DATABASE epood_Suhhanova;
create table kliendiAuto (
	id INT PRIMARY KEY,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50),
	car_make VARCHAR(50),
	car_model VARCHAR(50),
	car_year INT,
	car_price DECIMAL(10,2)
);
USE epood_Suhhanova;
SELECT * FROM kliendiAuto;

Using SELECT, UPDATE, DELETE statements

Change your added car make to Zaporozhets
Convert all Acura brands to Citroën
Delete rows with IDs 5, 10, and 15
Show rows with only ID, last name, car make and year, sort by ID in ascending order
Display 10 rows of cars where the customer’s last name starts with the same initial as yours, sort the cars by year in descending order
Display 2 lines containing a freely chosen car brand and exceeding a certain amount

UPDATE kliendiAuto
SET car_make = 'Zaporožets'
WHERE first_name = 'Darja' AND last_name = 'Suhhanova';
SELECT * FROM kliendiAuto
WHERE first_name = 'Darja' AND last_name = 'Suhhanova';

UPDATE kliendiAuto
SET car_make = 'Citroën'
WHERE car_make = 'Acura';
SELECT * FROM kliendiAuto
WHERE car_make = 'Citroën';

DELETE FROM kliendiAuto
WHERE id = 5 OR id = 10 OR id = 15;
SELECT * FROM kliendiAuto;
select COUNT(*) FROM kliendiAuto;

SELECT id, last_name, car_make, car_year
FROM kliendiAuto
ORDER BY id; --DESC

SELECT TOP 10 id, last_name, car_make, car_year
FROM kliendiAuto
WHERE LEFT(last_name, 1)='S'
ORDER BY car_year DESC;

SELECT * FROM kliendiAuto
WHERE car_make = 'Maserati' or car_make = 'Jaguar'
AND car_price >= 850.00;

Adding your own table

Use https://www.mockaroo.com to generate at least 100 pieces of data in the table

USE epood_Suhhanova;
create table serviceRecords1 (
	id INT PRIMARY KEY,
	client_id INT,
	FOREIGN KEY (client_id) REFERENCES kliendiAuto(id),
	service_date VARCHAR(20),
	service_cost DECIMAL(10,2),
	mechanic_name VARCHAR(50)
);

SELECT 
    sr.id,
    k.first_name AS client_name,
    sr.service_date,
    sr.service_cost,
    sr.mechanic_name
FROM 
    serviceRecords1 sr
INNER JOIN 
    kliendiAuto1 k
ON 
    sr.client_id = k.id; 

select COUNT(*) AS total_count
from serviceRecords1;

Procedures, Triggers, Users

Create a user named director who can create triggers (at least 2) and monitor their work (the director can see the table log, but cannot add or delete anything to the log table. The director cannot see other tables).

Set the trigger action independently.

Check the director’s work.

Make 2 procedures (functions) that simplify any work in the database.

Check

GRANT SELECT ON logi TO Direktor;
DENY SELECT ON kliendiAuto TO Direktor;
DENY SELECT ON serviceRecords TO Direktor;

Triggers

CREATE TABLE logi(
	id INT PRIMARY KEY IDENTITY(1,1),
	aeg DATETIME,
	toiming  varchar(100),
	sisestatud_andmed TEXT
)

Added some changes, to the logi table here.

I added the ‘kasutaja‘ field to the logs (logi).

I increased the number of VARCHAR in the ‘toiming‘ field as it was giving an error that there were too many values.

ALTER TABLE logi ADD kasutaja varchar(100);
SELECT * FROM logi;
ALTER TABLE logi
ALTER COLUMN toiming VARCHAR(1000);

Creating a TRIGGER is called ‘KliendiAutoUuendamine‘ (trigger for updating the data in the table ‘KliendiAuto‘).

CREATE TRIGGER KliendiAutoUuendamine
ON kliendiAuto1
FOR UPDATE
AS
BEGIN
    INSERT INTO logi(aeg, toiming, sisestatud_andmed, kasutaja)
    SELECT GETDATE(),
	'Andmed on uuednatud',
    CONCAT(
        'Vanad andmed: ', d.first_name, ', ',
        d.last_name, ', ', d.email, ', ',
        d.car_make, ', ', d.car_model, ', ',
        d.car_year, ', ', d.car_price, '. ',
        'Uued andmed: ', i.first_name, ', ',
        i.last_name, ', ', i.email, ', ',
        i.car_make, ', ', i.car_model, ', ',
        i.car_year, ', ', i.car_price),
	USER
    FROM deleted d 
    INNER JOIN inserted i
    ON i.id = d.id;
END

UPDATE kliendiAuto1
SET  first_name = 'Tyrion', last_name = 'Lannister', 
email = 'tyrion.lannister@mail.com', car_make = 'Toyota', 
car_model = 'RAV4', car_year = 2007, car_price = 6000
where id = 501;

SELECT * FROM kliendiAuto1;
SELECT * FROM logi;

Before:

After:

Creating a trigger ‘serviceRecordsLisamine‘, for tracking additions to the ‘serviceRecords1′ table.

CREATE TRIGGER serviceRecordsLisamine
ON serviceRecords1
AFTER INSERT
AS
BEGIN
    INSERT INTO logi(aeg, toiming, sisestatud_andmed, kasutaja)
    SELECT GETDATE(), 
	'Service record on lisatud',
    CONCAT('Client s name:', k.first_name,
	', Servise date: ', i.service_date,
	', cost: ', i.service_cost,
	', Mechanic name: ', i.mechanic_name),
	USER
    FROM inserted i
	INNER JOIN kliendiAuto1 k 
	ON i.client_id = k.id;
END

INSERT INTO serviceRecords1(id, client_id, service_date, service_cost, mechanic_name)
VALUES (50457, 251, '05/04/2024', 312.01, 'Minerva');
SELECT 
    sr.id,
    k.first_name AS client_name,
    sr.service_date,
    sr.service_cost,
    sr.mechanic_name
FROM 
    serviceRecords1 sr
INNER JOIN 
    kliendiAuto1 k
ON 
    sr.client_id = k.id;
SELECT * FROM logi;