Лабораторная Работа №1

«SQL»

Выполнил:

Арбатов Максим

I-1236R

№1
АЗС

1. ER-Диаграмма

2.QR-Диаграмма

3.Нормализованная Схема Таблиц.

4. Листинг.
(Стандарт Ansi)

Create database Azs

Create domain cod from int not null;

Create domain stroka from varchar[30] not null;

Create table Spravka(codt cod, marka stroka, price numeric(4,2) not null, koef numeric(4,2) not null);

Create index cod_t on Spravca(codt)

Create table Smena(cods cod, nums smallint not null, datas data not null);

Create index cod_s on Smena(cods)

Create table Rashod(codt cod, kolr int, datar data);

Create index cod_t on Rashod(codt)

Create table Prihod(cods cod, codt cod, kolp int not null);

Create index cod_t on Prihod(codt)

Create index cod_s on Prihod(cods)

Create table Peresmenka(cods cod,codt cod, ktns int not null, ktks int not null);

Create index cod_t on Peresmenka(codt)

Create index cod_s on Peresmenka(cods)

insert into Spravka(codt, marka, price, koef) values(10,'AI-72',6.10,1.30);

insert into Spravka(codt, marka, price, koef) values(11,'AI-76',6.20,1.30);

insert into Spravka(codt, marka, price, koef) values(12,'AI-80',6.30,1.30);

insert into Spravka(codt, marka, price, koef) values(13,'AI-92',7.20,1.40);

insert into Spravka(codt, marka, price, koef) values(14,'AI-95',8.00,1.40);

insert into Smena(cods,nums.datas) values(1001,1,25.09.2007);

insert into Smena(cods,nums.datas) values(1002,2,26.09.2007);

insert into Smena(cods,nums.datas) values(1003,1,27.09.2007);

insert into Smena(cods,nums.datas) values(1004,2,29.09.2007);

insert into Smena(cods,nums.datas) values(1005,1,30.09.2007);

insert into rashod(codt, kolr,datar) values(11,20,25.09.2007);

insert into rashod(codt, kolr,datar) values(13,25,25.09.2007);

insert into rashod(codt, kolr,datar) values(13,20,25.09.2007);

insert into rashod(codt, kolr,datar) values(14,30,25.09.2007);

insert into prihod(cods,codt,kolp) values(1001,11,200);

insert into prihod(cods,codt,kolp) values(1001,13,500);

insert into prihod(cods,codt,kolp) values(1002,13,500);

insert into prihod(cods,codt,kolp) values(1003,13,500);

insert into prihod(cods,codt,kolp) values(1001,14,700);

insert into Peresmenka(cods,codt,ktns,ktks) values (1001,13,500,300);

insert into Peresmenka(cods,codt,ktns,ktks) values (1001,14,650,200);

insert into Peresmenka(cods,codt,ktns,ktks) values (1002,15,600,200);

insert into Peresmenka(cods,codt,ktns,ktks) values (1002,13,500,200);

insert into Peresmenka(cods,codt,ktns,ktks) values (1002,14,400,200);

update Spravka set Koef = Koef+0.1 where Prisez <17

update Spravka set Prisez= Prisez-1 where Koef >1.5

update Rashod set KolR= KolR+5 where month(DateR)=MONTH (getdate());

delete from Rashod where DateR= '2007-09-25'

delete from Rashod where DAY (DateR)=22;

delete from Rashod where year (DateR)=2014;

insert into Rashod values (14,30,GETDATE());

create view SpravkaVIEW1 as

select marka,Prisez,CodT,Koef

from Spravka

*Подзапрос*
Select marka
from Spravka
Where codt in
(Select codt
From Rashod
Where datar in
(Select datas
From Smena
Where (nums=1) and (month datas=mont(getdate))
and cods in
(Select cods
From Peresmenka)))

*Агригация*

Select max(prisez) as Maxcena
from Spravka
where marka=’AИ-72’.

*Синтаксис*

Select Atr(груп), Atr(агр)
From R
Where Expression
group by Atr
Having Expression(агр)

№2.

Морские Сражения

1.ER-Диаграмма

2.QR-Диаграмма

3.Нормализованная Схема Таблиц.

4.Листинг:

create database sea_battles

create table classes(idClass int,nameClass varchar(30),type varchar(2),Country varchar(30),numguns int, bore int, displaceme int)

create index id_class on classes(idClass)

create table battles(idBattle int,nameBattle varchar(30),dates date,datee date)

create index id_battle on battles(idbattle)

create table ship(idShip int, nameShip varchar(30),idClass int,launched int)

create index id_ship on ship(idShip)

create index id_class on ship(idClass)

create table outcomes(idShip int, idBattle int, result varchar(30))

create index id_ship on outcomes(idShip)

create index id_battle on outcomes(idBattle)

insert into classes values (1,'bismark','bb','germany',8,15,42000)

insert into classes values (2,'lowa','bb','usa',9,16,46000)

insert into classes values (3,'kongo','bc','japan',8,14,32000)

insert into classes values (4,'northCalifornia','bb','usa',9,16,37000)

insert into classes values (5,'renown','bc','gtBritain',6,15,32000)

---

insert into battles values (1,'northAntlactic','1941-05-24','1941-05-27')

insert into battles values (2,'guadalcanal','1942-11-15','1942-11-15')

insert into battles values (3,'northCape','1943-12-26','1943-12-26')

insert into battles values (4,'surigaoStrait','1944-10-25','1944-10-25')

---

insert into ship values (1,'bismarc',1,1919)

insert into ship values (2,'california',7,1921)

insert into ship values (3,'dukeOfYork',1,1911)

insert into ship values (4,'fuso',8,1921)

insert into ship values (5,'haruna',3,1915)

---

insert into outcomes values (1,1,'sunk')

insert into outcomes values (2,4,'ok')

insert into outcomes values (3,3,'ok')

insert into outcomes values (4,4,'sunk')

insert into outcomes values (7,1,'sunk')

Select NameShip,Numguns

from classes,battles,ship,outcomes

where (classes.idClass=ship.idClass)and(ship.idShip=outcomes.idShip)and

(battles.idBattle=outcomes.idBattle) and (battles.nameBattle='SurigaoStrait') and

(outcomes.result='sunk')

*Подзапрос*
Select name_battle
From Battle
Where id_battle in
(Select id_battle
From Outcomes
Where id_Ship in
(Select id_Ship
From Ships
Where id_Ship in
(Select id_Class
From Classes
Where nameClass=’Kongo’)))

*Агригация*
Select max(Numguns) as MaxOruj
From Classes
Where type=’bb’

*Агригация*+*Группировка*
Select NameClass,count(NameShip) as CountShips
From Classes,Ships,Outcomes
Where (Classes.idClass=Ship.idShip) and
(Ship.idShip=Outcomes.idShip) and
(Result=’sunk’)
group by NameClass

№3.

Кафе

1.ER-Диаграмма

2.QR-Диаграмма

3.Нормализованная Схема Таблиц.

4.Листинг:

create database kafe

create domain vc as varchar (50);

create table tipBl(codTip int,nameTip vc);

create index cod_tip on tipBl(codTip);

create table bliuda(codBl int,nameBl vc,codTip int, gramaj int,price real, sostav varchar(200))

create index cod_bl on bliuda(codBl);

create index cod_tip on bliuda(codTip);

create table meniu(datam date, codBl int)

create index cod_bl on meniu(codBl);

create table zakaz(nSceta longint, dataz date,codBl int,colPortii int)

create index cod_bl on zakaz(codBl);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',10,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1002,'Канапе с красной икрой',10,400,27);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1004,'Осетрина с лимоном',10,250,120);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1006,'Сельдь с маслинами',10,400,60);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1008,'Ассорти из свежих овощей',11,400,51);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1101,'От шеф-повора',11,400,25);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1104,'Крабовый',11,400,25);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1105,'Греческий',11,300,29);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1207,'Изсвежих овощей',11,300,24);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1201,'Жульен из птицы',12,200,35);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1202,'Жульен из куриной печени',12,200,38);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1204,'Жульен из грибов',12,200,38);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1301,'Зама из птицы',13,200,25);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1303,'Суп вегетарианский',13,200,36);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1304,'Окрошка',13,200,20);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1401,'Осетрина на гратаре',14,200,48);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1404,'Шашлык из свинины',14,200,48);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1405,'Филе куриное на глатаре',14,200,45);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1406,'Шашлык из птицы',14,200,50);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1501,'Филе судака в фольге',15,200,35);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1502,'Филе судака жареное',15,200,35);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1601,'Говядина с грибами',16,200,38);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1602,'Стэйк с вишей',16,200,38);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1603,'Стэйк закопченный сыром',16,200,20);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',17,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',18,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',19,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',20,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',21,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',22,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',23,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',24,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',25,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',26,250,42);

insert into bliuda(cod_bl,name_bl,cod_tipa, gramaj, price) values (1001,'Канапе с черной икрой',27,250,42);

insert into Meniu (datam,cad_bl) values(16.11.2007,1001);

insert into Meniu (datam,cad_bl) values(16.11.2007,1101);

insert into Meniu (datam,cad_bl) values(16.11.2007,1201);

insert into Meniu (datam,cad_bl) values(16.11.2007,1301);

insert into Meniu (datam,cad_bl) values(16.11.2007,1401);

insert into Meniu (datam,cad_bl) values(17.11.2007,1501);

insert into Meniu (datam,cad_bl) values(17.11.2007,1601);

insert into Meniu (datam,cad_bl) values(17.11.2007,1701);

insert into Meniu (datam,cad_bl) values(17.11.2007,1801);

insert into Meniu (datam,cad_bl) values(17.11.2007,1901);

insert into Meniu (datam,cad_bl) values(17.11.2007,2001);

insert into Meniu (datam,cad_bl) values(18.11.2007,2101);

insert into Meniu (datam,cad_bl) values(18.11.2007,2201);

insert into Meniu (datam,cad_bl) values(18.11.2007,2301);

insert into Meniu (datam,cad_bl) values(18.11.2007,2401);

insert into Meniu (datam,cad_bl) values(19.11.2007,2501);

insert into Meniu (datam,cad_bl) values(19.11.2007,2601);

insert into Meniu (datam,cad_bl) values(19.11.2007,2701);

insert into tipbl(cod_tipa,name_tipa) values(10,'Холодные блюда и закуски');

insert into tipbl(cod_tipa,name_tipa) values(11,'Салаты');

insert into tipbl(cod_tipa,name_tipa) values(12,'Горячие закуски');

insert into tipbl(cod_tipa,name_tipa) values(13,'Супы');

insert into tipbl(cod_tipa,name_tipa) values(14,'Гратарные блюда');

insert into tipbl(cod_tipa,name_tipa) values(15,'Блюда из рыбы');

insert into tipbl(cod_tipa,name_tipa) values(16,'Блюда из говядины');

insert into tipbl(cod_tipa,name_tipa) values(17,'Блюда из свинины');

insert into tipbl(cod_tipa,name_tipa) values(18,'Блюда из птицы');

insert into tipbl(cod_tipa,name_tipa) values(19,'Блюда из яиц');

insert into tipbl(cod_tipa,name_tipa) values(20,'Мучные блюда');

insert into tipbl(cod_tipa,name_tipa) values(21,'Блюда из овощей');

insert into tipbl(cod_tipa,name_tipa) values(22,'Гарниры');

insert into tipbl(cod_tipa,name_tipa) values(23,'Сладкие блюда');

insert into tipbl(cod_tipa,name_tipa) values(24,'Горячие напитки');

insert into tipbl(cod_tipa,name_tipa) values(25,'Коктейли');

insert into tipbl(cod_tipa,name_tipa) values(26,'Спиртное');

insert into tipbl(cod_tipa,name_tipa) values(27,'Шоколад');

Create view zakazik

Select name_bl,zakaz

From Bliuda,zakaz

*Подзапрос*
Select name_tipa,name_bl
From Tipbl,Bliuda
Where(Tipbl.cod_tipa=bliuda.cod_tipa) and
(Bliuda.cod_bl in
Select cod_bl
From Zakaz
Where Month(dataz)=month(getdate()-1));

*Группировка*+*Агригация*
Select name_bl,cod_tipa,AVG(price) as ViewMen
from Bliuda,TipBl
Where(Tipbl.CodTipa=Bliuda.CodTipa)
Group by CodTipa

№4.

Movie

1.ER-Диаграмма

2.QR-Диаграмма

3.Нормализованная Схема Таблиц.

4.Листинг:

Create database Videoteka

Create table MovieExec (cod_exec int, Name varchar(50), adres varchar(50), certif varchar(50),NetWorths int)

Create index codExec on MovieExec (cod_exec)

Create table MovieStar (cod_star int,Name varchar(50),adress varchar(50), gender char(1), birthday date)

Create index codStar on MovieStar(cod_star)

Create table Studio (cod_st int,Name char(50),Adress varchar(50),PresC int,cod_exec int)

Create index codExec on Studio(cod_exec)

Create index codSTD on Studio(cod_st)

Create table movie(cod_m int,Title varchar(50),god int,cod_exec int, dlitelinosti_minut int, Incolor varchar(1),cod_st int,ProduserC int)

Create index codM on movie(cod_m)

Create index codExec on movie(cod_exec)

Create index codSTD on movie(cod_st)

Create table StarSLn (cod_m int,s int, cod_star int)

Create index codStar on StarSLn(cod_star)

Create index codM on StarSLn (cod_m)

---

Insert into movie values (1,'Mighty Afrodite', 1995, 95, 't',1,10975)

Insert into movie values (2,'Night at the museum', 2006, 104, 't',2,14675)

Insert into movie values (3,'Notting hill',1999, 115, 't',1,12536)

Insert into movie values (4,'Saw III', 2006, 112, 't',4,10987)

Insert into movie values (5,'State and main', 2000, 101, 't',3,10100)

---

Insert into MovieStar values (1,'Alec Badwin', 'New York', 'M', '1967-12-12')

Insert into MovieStar values (2,'Angus MacFadyen', 'New York', 'M', '1969-4-7')

Insert into MovieStar values (3,'Bob Stiller', 'Hollywood', 'M', '1970-9-25')

Insert into MovieStar values (4,'Bob Hoskins', 'Hollywood', 'M', '1975-1-30')

Insert into MovieStar values (5,'CameronDiaz', 'New York', 'F', '1973-12-31')

Insert into Studio values (1,'20th Century Fox', 'Columbia', 1004)

Insert into Studio values (2,'Columia Pictures', 'Hollywood', 1001)

Insert into Studio values (3,'Paramount Pictures', 'Hollywood', 1002)

Insert into Studio values (4,'Warner Bros.', 'Hollywood', 1003)

---

Insert into StarSLn values(7,2006,5)

Insert into StarSLn values(7,2006,18)

Insert into StarSLn values(7,2006,16)

Insert into StarSLn values(5,2000,1)

Insert into StarSLn values(5,2000,6)

---

Insert into MovieExec values ('Darren Lynn Bousman','New York','10987',1000000)

Insert into MovieExec values ('David Mamet','New York','10100',1300000)

Insert into MovieExec values ('John Polson','Hollywood','12000',600000)

Insert into MovieExec values ('Warlowe Farcett','Hollywood','10941',756000)

Insert into MovieExec values ('Nancy Meyers','Hollywood','11000',1240000

Create view Cinema as

select id_title,name_title,lenght

from Movie

Create view Actors as

select id_star,Name_star,Birthday

from MovieStar

Create view Studia as

select Name_Studio,Adress_Studio

from Studio

select name_title Film_s_etim_akterom

From Movie, MovieStar, StarSLN

Where Movie.id_title=StarSLN.id_title and MovieStar.id_star=StarSLN.id_star and name_star ='Tobin Bell’

select name_star,birthday

From Movie, MovieStar, StarSLN

Where Movie.id_title=StarSLN.id_title and MovieStar.id_star=StarSLN.id_star and movie.god_filma >'1950'

*Группировка*+*Агригация*
Select NameProd,Sum(length) as Duration
From MovieExec,Movie
Where (MovieExec.idProd=Movie.idProd)
Group by NameProd
Having min(year)<2000

*Агригация*
Select min(Length)
From Movie
Where year>2000

*Подзапрос*
Select NameStar
From Movie,StarSln,MovieStar
Where(Movie.idMovie=StarSLN.idMovie) and
(MovieStar.idStar=StarSLN.idStar) and
(Studio.idStudio=Movie.idStudio) and
(NameStudio=’Columbia Pictures’) and (gender=’f’);

№5.

Компьютеры

1.ER-Диаграмма

2.QR-Диаграмма

3.Нормализованная Схема Таблиц.

4.Листинг:

create database pc

create domain ch as varchar(10)

create table product(maker char(1) not null, idmodel int not null, type ch not null)

create index id_model on product(idmodel)

create table pc(idmodel int not null, speed numeric(2,1) not null, ram numeric(1) not null,hd int not null, dvd varchar(3) not null, price int not null);

create index id_model on pc(idmodel)

create table laptop(idmodel int not null, speed numeric(2,1) not null, ram numeric(1) not null,hd int not null, display varchar(3) not null, price int not null);

create index id_model on laptop(idmodel)

create table printer(idmodel int not null,free_in_1 ch not null, type ch not null, price int not null)

create index idModel on printer(idmodel)

insert into product values('A',4001,'pc')

insert into product values('A',4002,'pc')

insert into product values('A',4003,'pc')

insert into product values('B',4004,'pc')

insert into product values('B',4006,'pc')

insert into pc values(4001,5.4,4,500,'16x',550)

insert into pc values(4002,5.2,2,300,'16x',500)

insert into pc values(4003,5.6,4,1000,'16x',650)

insert into pc values(4004,5.6,2,500,'16x',600)