XAMPP SQL – Routines
1.1 Adding data (INSERT) into any arbitrary table from the cinema database: The first query adds information about the director to the table ‘rezisoor’. The second query adds informarion about the movie to the table ‘film’ (movie title, genre ID, language, movie durarion, director ID, movie type). 1.2 Deleting records in the žanr table based…
Trigger SQL
New query Concat()- объединение данных из нескольких полей
TASK – Adding User Permissions – M SQL Servis MS/ XAMPP
Security— Login—New Login Roles—Database Roles—New Database roles Kontroll: XAMPP
SQL database
In new query: In new query: –5. Агрегатные функцииSUM(), AVG(), MIN(), MAX(), COUNT() In new query The last one phpMyAdmin: Task: изменить столбец synnilinn и установить значение Haapsalu начиная с “S” Task: Обновить таблицу laps установить всем детям 2000года рождения рост 158. Task: Комбинирование условий. Вывести все имена детей, которые родились в 2000 году и…
SELECT query (MS SQL ServerManagementStudio)
https://moodle.edu.ee/pluginfile.php/1213077/mod_resource/content/1/ru/index_ru.html SELECT laused 2 tabelite põhjal Покажите названия стран континента Европа, их столицы, количество населения проживающего в столице, отсортированные в порядке убывания населения: – учитывая только страны, имеющие столицу (см.ответ) – учитывая все страны (см.ответ) Покажите страны, в которых название страны и название столицы совпадают (см.ответ) Найдите города, которые имеют одинаковые названия и находятся в одной стране. (см.ответ) Покажите,…
Routines in SQL
IN Microsoft SQL Server Managment Studio Creating a procedure (In new Query): Calling a procedure: Deleting a procedure: The user enter the first letter of the school, and then all the schools data is displayed: The user enters only table values and then the table is filled: Deletes one record or row by ID: Request…
MS ACCESS Database
Andmebaasiriisad=>Seosed Loo=>Tabeli kujundus (Primary key/Foreign key) Loo=>Vormiviisard
DB ArvestusTöö: CyberSport
Microsoft SQL Server Management Studio Adding data Deleting data XAMPP Adding data Deleting data
DB – Task in XAMPP (Test ja Test kuupäev)
DB – Random adding data to the database
Using SELECT, UPDATE, DELETE statements Adding your own table Use https://www.mockaroo.com to generate at least 100 pieces of data in the table 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…
A table is a database object that stores data in the form of rows and columns. The structure of a table is defined by the columns it contains.
SQL statements
DDL – Data Definition Language (CREATE TABLE, ALTER TABLE)
- CREATE TABLE – Creating a table
- Table name: The names of tables must be unique within the owner’s context. In other words, two tables with the same name cannot belong to the same owner, but another user may have a table with the same name.
- Column names (fields): Column names are unique within a table. Column names adhere to standard rules for choosing identifiers and consist of letters, numbers, and special characters.
- Data types:
- Character (string) types:
- CHAR(n) or CHARACTER(n) – default length: 1 and max length: 255
- CHARACTER VARYING(n) or CHAR VARYING(n)
- Numeric types:
- SMALLINT, INT2, INTEGER, INT, INT4, BIGINT, INT8
- REAL, FLOAT4, DOUBLE PRECISION, FLOAT8, FLOAT
- NUMERIC(p,s), DECIMAL (p,s)
- MONEY
- Boolean and binary types: BOOLEAN, BOOL, BIT
- Date and time type: DATE, TIME, INTERVAL, DATETIME
- Data types in MS Access SQL:
- TEXT(n) – Variable-length text, maximum length of n characters
- MEMO – Text, for volume exceeding 255 characters
- BYTE, INTEGER, LONG – Integer numbers
- COUNTER – Counter, integer numbers
- SINGLE, DOUBLE, CURRENCY – Floating-point numbers
- DATETIME – Date and time
- YESNO – Logical type
- LONGBINARY – OleObject, binary type
- Character (string) types:
- Memory size
- Mandatory columns
- Primary key columns
- Foreign key columns
- Constraints
- ALTER TABLE – Modifying the structure of a table
- DROP TABLE – Deleting a table
- CREATE INDEX – Creating an index
- ALTER INDEX – Modifying an index
- DROP INDEX – Deleting an index
DML -Data Manipulation Language (INSERT INTO, DELETE FROM, UPDATE)
Table creation (DDL)
CREATE TABLE toode(
toodeID int primary key AUTO_INCREMENT,
toodeNimetus varchar(50),
toodeHind decimal(5, 2),
v_kuupaev date,
kas_olemas bit
);
Adding data to the table (DML)
INSERT INTO toode(toodeNimetus, toodeHind, kas_olemas, v_kuupaev)
VALUES ('piim Alma', 1.50, 1, '2024-01-31');
SELECT * FROM toode;
CREATE TABLE `suhhanovabaas`.`tellimus` (`tellimusID` INT NOT NULL AUTO_INCREMENT , `tellimuseKuupaev` DATE NOT NULL , `toodeID` INT NOT NULL , `klient` VARCHAR(50) NOT NULL , PRIMARY KEY (`tellimusID`)) ENGINE = InnoDB;
Constraints – Piirangud (Ограничения)
PRIMARY KEY – The column value is unique
FORIGEIN KEY – The column uses a value from a related table.
NOT NULL – Non-empty value
UNIQUE – Unique values
CHECK – Selection of certain values
TEST
CREATE table Product(
idProduct int primary key AUTO_INCREMENT,
ProductName varchar(50),
idCategory int,
Price decimal(6, 2),
FOREIGN key(idCategory) REFERENCES category(idCategory)
);
create table customer(
idCustomer int PRIMARY KEY AUTO_INCREMENT,
nameCustomer varchar(100) UNIQUE,
contactCustomer varchar(50)
);
insert into product(ProductName, idCategory, Price)
VALUES ('boots', 3, 100.99)
insert into Sale(idProduct, Count_, DateOfSale)
values(1,200,'2024-01-31'),(3,200,'2024-01-31'),(4,100,'2024-01-31'),(3,250,'2024-01-31'),(3,57,'2024-01-31');
insert into customer(nameCustomer, contactCustomer)
values('Marko', 55614815');
ALTER TABLE sale ADD units char(5);
alter table sale add FOREIGN KEY(idCustomer) REFERENCES customer(idCustomer)
update sale set idCustomer=1;
