Laboratoriya mashg’ulotlarida berilgan topshiriqlar Создание таблиц

Masalani qo`yilishi: Predmet soha ma`lumotlar bazasi shakllantirilgandan so`ng ob`yektlarda tasavvurlarni yaratish va VIEW dan foydalanish.
VIEW yaratish va o’chirishning so’rovi sintaksisi
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;


DROP VIEW view_name;


Masalan:
CREATE VIEW Braziliya_xaridorlari AS
SELECT xaridor_familiyasi, xaridor_ismi
FROM xaridorlar
WHERE mamlakat = "Braziliya";
DROP VIEW Braziliya;


CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';


Example.csv yoki Customer.csv , Products.csv


Customer

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden
6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	Germany
7	Blondel père et fils	Frédérique Citeaux	24, place Kléber	Strasbourg	67000	France
8	Bólido Comidas preparadas	Martín Sommer	C/ Araquil, 67	Madrid	28023	Spain
9	Bon app'	Laurence Lebihans	12, rue des Bouchers	Marseille	13008	France
10	Bottom-Dollar Marketse	Elizabeth Lincoln	23 Tsawassen Blvd.	Tsawassen	T2F 8M4	Canada
11	B's Beverages	Victoria Ashworth	Fauntleroy Circus	London	EC2 5NT	UK
12	Cactus Comidas para llevar	Patricio Simpson	Cerrito 333	Buenos Aires	1010	Argentina
13	Centro comercial Moctezuma	Francisco Chang	Sierras de Granada 9993	México D.F.	05022	Mexico
14	Chop-suey Chinese	Yang Wang	Hauptstr. 29	Bern	3012	Switzerland
15	Comércio Mineiro	Pedro Afonso	Av. dos Lusíadas, 23	São Paulo	05432-043	Brazil

So’rov Natijasi

CustomerName	ContactName
Comércio Mineiro	Pedro Afonso
Familia Arquibaldo	Aria Cruz
Gourmet Lanchonetes	André Fonseca
Hanari Carnes	Mario Pontes

Quyidagi SQLkodi "Product" jadvalidagi o'rtacha narxdan yuqori narxga ega har bir mahsulotni ko'rinishni yaratadi:


CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);


LOAD DATA LOCAL INFILE 'c:/tmp/Example.csv'
INTO TABLE customers
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;


Products

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 - 12 oz bottles	19
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
4	Chef Anton's Cajun Seasoning	2	2	48 - 6 oz jars	22
5	Chef Anton's Gumbo Mix	2	2	36 boxes	21.35
6	Grandma's Boysenberry Spread	3	2	12 - 8 oz jars	25
7	Uncle Bob's Organic Dried Pears	3	7	12 - 1 lb pkgs.	30
8	Northwoods Cranberry Sauce	3	2	12 - 12 oz jars	40
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97
10	Ikura	4	8	12 - 200 ml jars	31
Avg_price					29.2

SQL kodi Natijasi

ProductName	Price
Uncle Bob's Organic Dried Pears	30
Northwoods Cranberry Sauce	40
Mishi Kobe Niku	97
Ikura	31

16 LABORATORIYA ISHI
Mavzu: SQLda protseduralar yaratish


Ishdan maqsad: Berilgan predmet soha ma`lumotlar bazasi uchun protsedura yaratish.
Masalani qo`yilishi: Predmet soha ma`lumotlar bazasi shakllantirilgandan so`ng ob`yektlarda proseduralarni yaratish va ulardan foydalanish.


Protsedura yaratish:
DELIMITER //
CREATE PROCEDURE protsedura_nomi(protsedura prototipi)
BEGIN
{protsedura so’rovlar amallar… maydoni}
END//
protsedurani chaqirish:
CALL protsedura_nomi(@prototip)//
protsedura natijasini ko’rish:
SELECT @prototip;

DELIMITER //

CREATE PROCEDURE Talaba
BEGIN
SELECT * FROM Talaba;
END
// DELIMITER ;
…..
…..
CALL Talaba;


create database qwerty123;
use qwerty123;
CREATE TABLE person (name varchar(45), age int);
INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);
CREATE TABLE average_age (average double);
INSERT INTO average_age SELECT AVG(age) FROM person;


CREATE TABLE person_archive (
name varchar(45),
age int,
time timestamp DEFAULT NOW());


delimiter //
CREATE TRIGGER person_bi BEFORE INSERT
ON person
FOR EACH ROW
IF NEW.age < 18 THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';
END IF; //


delimiter ;


SELECT * FROM average_age;


DELIMITER //
CREATE TRIGGER person_ai AFTER INSERT
ON person
FOR EACH ROW
UPDATE average_age SET average = (SELECT AVG(age) FROM person); //
delimiter ;


CREATE TABLE person_archive (
name varchar(45),
age int,
time timestamp DEFAULT NOW());


INSERT INTO person VALUES ('John', 14);
Customer

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden
6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	Germany
7	Blondel père et fils	Frédérique Citeaux	24, place Kléber	Strasbourg	67000	France