· 8 years ago · Jun 09, 2018, 12:08 PM
1--===============================================
2--Created by Roman Makeev, index 253947, group 3.
3--0)
4CREATE TABLE dbo.Pracownik (
5 id_pracownik int NOT NULL IDENTITY PRIMARY KEY,
6 Name varchar(200) NULL DEFAULT (NULL),
7 Surname varchar(200) NULL DEFAULT (NULL),
8 data_zatr datetime NOT NULL DEFAULT GETDATE(),
9 salary MONEY NOT NULL DEFAULT 4000 CHECK (SALARY>=0) , -- i wanted to use money or decimal (18,2) but its dont works, idk
10 pesel varchar(11) NOT NULL UNIQUE,
11);
12--- Next
13
14Create table dbo.Customers (
15 id_customer int NOT NULL IDENTITY PRIMARY KEY,
16 Name varchar(200) NULL DEFAULT (NULL),
17 Surname varchar(200) NULL DEFAULT (NULL),
18 City varchar(200) NULL DEFAULT (NULL),
19)
20--- NEXT
21
22CREATE TABLE dbo.Goods (
23 id_goods int NOT NULL IDENTITY PRIMARY KEY,
24 Name varchar(200) NULL DEFAULT (NULL),
25 Type varchar(200) NULL DEFAULT (NULL),
26 date_goods datetime NOT NULL DEFAULT GETDATE(),
27 value int NOT NULL DEFAULT 0 CHECK (value>=0),
28);
29--NEXT
30
31CREATE TABLE dbo.Orders (
32 id_order int NOT NULL IDENTITY PRIMARY KEY,
33 id_pracownik int NOT NULL FOREIGN KEY REFERENCES dbo.Pracownik(id_pracownik) ON UPDATE CASCADE,
34 id_customer int NOT NULL FOREIGN KEY REFERENCES dbo.Customers(id_customer) ON UPDATE CASCADE,
35 id_goods int NOT NULL FOREIGN KEY REFERENCES dbo.Goods(id_goods) ON UPDATE CASCADE,
36 value int NOT NULL DEFAULT 0 , --should be trigger
37 data_order datetime NOT NULL DEFAULT GETDATE(),
38);
39--for insert
40--SET IDENTITY_INSERT Orders OFF;
41--NEXT
42
43CREATE TABLE dbo.Cars (
44 id_car int NOT NULL IDENTITY PRIMARY KEY,
45 Name varchar(200) NOT NULL CHECK(LEN(Name) >=2),
46 model varchar(200) NOT NULL CHECK(LEN(model) >=2), date_bought datetime NOT NULL DEFAULT GETDATE(),
47 number varchar(10) NOT NULL UNIQUE,
48 car_mileage int NOT NULL CHECK (car_mileage>=0),
49);
50-- Insert into Pracownik
51INSERT INTO dbo.Pracownik (Name, Surname, salary, pesel)
52VALUES ('Romka','Makeev',5000 ,'12345678910'),
53 ('Catty','Vostrikova',3500 ,'12345678911'),
54 ('Kate','Makeeva',7345 ,'12345678912'),
55 ('Kinga','Meleszko',4343 ,'56345678913'),
56 ('Ivan','Akulovicz',859 ,'00345678912');
57--Into Customers
58INSERT INTO dbo.Customers (Name, Surname, City)
59VALUES ('Name','Surname','Magnitogorsk'),
60 ('Kitano','Soprano','Minsk'),
61 ('Andrea','Gotti','Milano'),
62 ('Taylor','Swift','Praga'),
63 ('Johny','Avie','Calofornia');
64--Into Goods
65INSERT INTO dbo.Goods (Name, type,value)
66VALUES ('Cars','Toys', 100000),
67 ('Barbby','Toys', 123000),
68 ('Juice','Food',982),
69 ('Bananas','Food', 23),
70 ('Helicopter','Toys',9);
71--Into Orders
72INSERT INTO dbo.Orders ( id_pracownik,id_customer, id_goods,value)
73VALUES (2,3,4,40),
74 (2,2,1,900),
75 (3,2,3,523),
76 (5,3,4,80),
77 (2,1,2,9);
78--Insert Into Cars
79INSERT INTO dbo.Cars (Name, Model, number,car_mileage)
80VALUES ('Audi', 'A4','PR-456789', 123),
81 ('Tesla', 'Roadster','QR-456279',1245),
82 ('Ford', 'Focus ST','GD-956785', 43223),
83 ('VW', 'Polo Sedan','PR-903789', 123),
84 ('Land Rover', 'Range Rover Sport SVR','LR-736780', 73232);
85-- Car status
86CREATE TABLE dbo.car_status (
87 id_status int NOT NULL IDENTITY(1,1),
88 id_car int NOT NULL,
89 data_add datetime NOT NULL DEFAULT (getdate()),
90 new_mileage int NOT NULL
91);
92
93ALTER TABLE dbo.car_status ADD CONSTRAINT PK__car_stat__5D2DC6E892771D4C PRIMARY KEY (id_status);
94--===============================================
95--1a) VIEW
96-- Create a view, which gives information about pracownik and his orders they made
97--DROP VIEW raport;
98--GO
99CREATE VIEW raport
100AS
101SELECT s.id_pracownik, s.Name, s.Surname, COUNT(w.id_pracownik) AS "ilosc_wyp"
102FROM Pracownik s LEFT JOIN Orders w ON s.id_pracownik = w.id_pracownik
103GROUP BY s.id_pracownik, s.Name, s.Surname having COUNT(s.id_pracownik) > 0;
104GO
105--===============================================
106-- Informacja orders
107--1b)
108SELECT * FROM raport;
109-- Top list of "pracowniki" with ilosc orders
110--1b')
111SELECT max(ilosc_wyp) as "ilosc", Name from raport group by Name
112
113--===============================================
114-- procedure whick increase salary of pracownik, if salary less than (0) made a eror message
115-- Drop procedure salaryup
116IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'salaryup')
117DROP PROCEDURE salaryup
118GO
119
120CREATE PROCEDURE salaryup (@id_pracownik int, @salary money)
121AS
122IF @salary < 0
123 RAISERROR ('Wrong argument with salary',1,2);
124else
125Update pracownik set salary = salary + @salary WHERE id_pracownik = @id_pracownik;
126GO
127EXECUTE salaryup 1,10
128
129--===============================================
130-- To add millage from pracownik car, value of adding mileage must be less than old
131--Drop procedure add_milage
132IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'add_milage')
133DROP PROCEDURE add_milage
134
135CREATE PROCEDURE add_milage (@car int, @new int)
136AS
137begin
138IF (select car_mileage from Cars where id_car = @car) < @new
139 RAISERROR ('New mileage cant be more than old',1,2);
140else
141begin
142INSERT INTO dbo.car_status (id_car, new_mileage)
143VALUES (@car, @new);
144Update dbo.Cars set car_mileage = car_mileage + @new WHERE id_car = @car;
145end
146end
147GO
148EXECUTE add_milage 1, 1
149
150
151--===============================================
152--Create fuction, which count, how much in % goods take place on a stock, but not exact value
153--DROP FUNCTION dbo.Goodsvalue;
154--GO
155IF EXISTS (SELECT *
156 FROM sys.objects
157 WHERE object_id = OBJECT_ID(N'[dbo].[Goodsvalue]')
158 AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
159 DROP FUNCTION [dbo].[Goodsvalue]
160
161GO
162
163GO
164CREATE FUNCTION dbo.Goodsvalue ( @id_good INT) RETURNS varchar(50)
165BEGIN
166--224014
167declare @@capacity int = 500000
168declare @now int
169declare @roma float
170declare @test varchar(100)
171set @now = (select SUM(value) from Goods where id_goods = @id_good);
172--set @now = (select SUM(value) from Goods where id_goods = 1);
173set @roma = (@now * 100) / 500000;
174RETURN
175 CASE
176 WHEN @roma = 0 THEN 'Should buy more'
177 WHEN @roma > 0 AND @roma <=25 THEN 'about 25%'
178 WHEN @roma > 25 AND @roma <=50 THEN 'about 40%'
179 WHEN @roma > 50 AND @roma<= 100 THEN 'more than 50%'
180 ELSE ('The quantity is something else')
181 END
182END
183GO
184--===============================================
185-- Test
186SELECT dbo.Goodsvalue(2) AS 'information';
187
188
189
190--===============================================
191--Create fuction, which count pracownik and orders and return table
192--GO
193IF EXISTS (SELECT *
194 FROM sys.objects
195 WHERE object_id = OBJECT_ID(N'[dbo].[Custom]')
196 AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
197 DROP FUNCTION [dbo].[Custom]
198
199GO
200CREATE FUNCTION Custom()
201RETURNS @custom table
202 (pracownik varchar(100) null, ilosc int null)
203AS
204begin
205 INSERT @custom (pracownik, ilosc)
206 SELECT s.Name+' '+s.Surname as 'Name & surname',w.id_pracownik AS "ilosc_wyp"
207 FROM Pracownik s LEFT JOIN Orders w ON s.id_pracownik = w.id_pracownik
208RETURN
209end
210go
211
212--== Test
213SELECT * FROM Custom()