· 7 years ago · Dec 12, 2018, 08:06 PM
1USE [master]
2GO
3
4--DROP DATABASE [udb_ÐверьÑнов_Лощенко]
5--GO
6/****** Object: Database [udb_ÐверьÑнов_Лощенко] Script Date: 01.11.2018 18:27:07 ******/
7CREATE DATABASE [udb_ÐверьÑнов_Лощенко]
8 CONTAINMENT = NONE
9 ON PRIMARY
10( NAME = N'udb_ÐверьÑнов_Лощенко', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\udb_ÐверьÑнов_Лощенко.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
11 LOG ON
12( NAME = N'udb_ÐверьÑнов_Лощенко_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\udb_ÐверьÑнов_Лощенко_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
13GO
14
15ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET COMPATIBILITY_LEVEL = 120
16GO
17
18IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
19begin
20EXEC [udb_ÐверьÑнов_Лощенко].[dbo].[sp_fulltext_database] @action = 'enable'
21end
22GO
23
24ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET ANSI_NULL_DEFAULT OFF
25GO
26
27ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET ANSI_NULLS OFF
28GO
29
30ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET ANSI_PADDING OFF
31GO
32
33ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET ANSI_WARNINGS OFF
34GO
35
36ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET ARITHABORT OFF
37GO
38
39ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET AUTO_CLOSE OFF
40GO
41
42ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET AUTO_SHRINK OFF
43GO
44
45ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET AUTO_UPDATE_STATISTICS ON
46GO
47
48ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET CURSOR_CLOSE_ON_COMMIT OFF
49GO
50
51ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET CURSOR_DEFAULT GLOBAL
52GO
53
54ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET CONCAT_NULL_YIELDS_NULL OFF
55GO
56
57ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET NUMERIC_ROUNDABORT OFF
58GO
59
60ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET QUOTED_IDENTIFIER OFF
61GO
62
63ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET RECURSIVE_TRIGGERS OFF
64GO
65
66ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET DISABLE_BROKER
67GO
68
69ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
70GO
71
72ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET DATE_CORRELATION_OPTIMIZATION OFF
73GO
74
75ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET TRUSTWORTHY OFF
76GO
77
78ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET ALLOW_SNAPSHOT_ISOLATION OFF
79GO
80
81ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET PARAMETERIZATION SIMPLE
82GO
83
84ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET READ_COMMITTED_SNAPSHOT OFF
85GO
86
87ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET HONOR_BROKER_PRIORITY OFF
88GO
89
90ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET RECOVERY FULL
91GO
92
93ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET MULTI_USER
94GO
95
96ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET PAGE_VERIFY CHECKSUM
97GO
98
99ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET DB_CHAINING OFF
100GO
101
102ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
103GO
104
105ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET TARGET_RECOVERY_TIME = 0 SECONDS
106GO
107
108ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET DELAYED_DURABILITY = DISABLED
109GO
110
111ALTER DATABASE [udb_ÐверьÑнов_Лощенко] SET READ_WRITE
112GO
113
114---- 2 лаба ----
115
116USE [udb_ÐверьÑнов_Лощенко]
117GO
118
119/****** Object: Table [dbo].[authors] Script Date: 22.11.2018 18:27:47 ******/
120SET ANSI_NULLS ON
121GO
122
123SET QUOTED_IDENTIFIER ON
124GO
125
126SET ANSI_PADDING ON
127GO
128
129CREATE TABLE [dbo].[authors](
130 [au_id] [varchar](11) NOT NULL CHECK (au_id LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'),
131 [au_lname] [varchar](40) NOT NULL,
132 [au_fname] [varchar](20) NOT NULL,
133 [phone] [char](12) NOT NULL,
134 [address] [varchar](40) NULL,
135 [city] [varchar](20) NULL,
136 [state] [varchar](2) NULL,
137 [zip] [varchar](5) NULL CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]'),
138 [contract] [bit] NOT NULL,
139 CONSTRAINT [PK_authors] PRIMARY KEY CLUSTERED
140(
141 [au_id] ASC
142)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
143) ON [PRIMARY]
144
145GO
146
147SET ANSI_PADDING OFF
148GO
149
150ALTER TABLE [dbo].[authors] ADD CONSTRAINT [DF_authors_phone] DEFAULT ('UNKNOWN') FOR [phone]
151GO
152
153
154USE [udb_ÐверьÑнов_Лощенко]
155GO
156
157/****** Object: Table [dbo].[titles] Script Date: 22.11.2018 18:29:05 ******/
158SET ANSI_NULLS ON
159GO
160
161SET QUOTED_IDENTIFIER ON
162GO
163
164SET ANSI_PADDING ON
165GO
166
167CREATE TABLE [dbo].[titles](
168 [title_id] [varchar](6) NOT NULL,
169 [title] [varchar](80) NOT NULL,
170 [type] [char](12) NOT NULL,
171 [pub_id] [char](4) NULL,
172 [price] [money] NULL,
173 [advance] [money] NULL,
174 [royalty] [int] NULL,
175 [ytd_sales] [int] NULL,
176 [notes] [varchar](200) NULL,
177 [pubdate] [datetime] NOT NULL,
178 CONSTRAINT [PK_titles] PRIMARY KEY CLUSTERED
179(
180 [title_id] ASC
181)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
182) ON [PRIMARY]
183
184GO
185
186SET ANSI_PADDING OFF
187GO
188
189ALTER TABLE [dbo].[titles] ADD CONSTRAINT [DF_titles_type] DEFAULT ('UNDECIDED') FOR [type]
190GO
191
192ALTER TABLE [dbo].[titles] ADD CONSTRAINT [DF_titles_pubdate] DEFAULT (getdate()) FOR [pubdate]
193GO
194
195USE [udb_ÐверьÑнов_Лощенко]
196GO
197
198/****** Object: Table [dbo].[title_author] Script Date: 22.11.2018 18:30:11 ******/
199SET ANSI_NULLS ON
200GO
201
202SET QUOTED_IDENTIFIER ON
203GO
204
205SET ANSI_PADDING ON
206GO
207
208CREATE TABLE [dbo].[title_author](
209 [au_id] [varchar](11) NOT NULL,
210 [title_id] [varchar](6) NOT NULL,
211 [au_ord] [tinyint] NULL,
212 [royalTyper] [int] NULL,
213 CONSTRAINT [au_id, title_id] PRIMARY KEY CLUSTERED
214(
215 [au_id] ASC,
216 [title_id] ASC
217)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
218) ON [PRIMARY]
219
220GO
221
222SET ANSI_PADDING OFF
223GO
224
225ALTER TABLE [dbo].[title_author] WITH CHECK ADD FOREIGN KEY([au_id])
226REFERENCES [dbo].[authors] ([au_id])
227GO
228
229ALTER TABLE [dbo].[title_author] WITH CHECK ADD FOREIGN KEY([title_id])
230REFERENCES [dbo].[titles] ([title_id])
231GO
232
233USE [udb_ÐверьÑнов_Лощенко]
234GO
235
236/****** Object: Table [dbo].[sales] Script Date: 22.11.2018 18:31:23 ******/
237SET ANSI_NULLS ON
238GO
239
240SET QUOTED_IDENTIFIER ON
241GO
242
243SET ANSI_PADDING ON
244GO
245
246CREATE TABLE [dbo].[sales](
247 [stor_id] [char](4) NOT NULL,
248 [ord_num] [varchar](20) NOT NULL,
249 [ord_date] [datetime] NOT NULL,
250 [qty] [smallint] NOT NULL,
251 [payterms] [varchar](12) NOT NULL,
252 [title_id] [varchar](6) NOT NULL,
253 CONSTRAINT [stor_id, ord_num, title_id] PRIMARY KEY CLUSTERED
254(
255 [stor_id] ASC,
256 [ord_num] ASC,
257 [title_id] ASC
258)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
259) ON [PRIMARY]
260
261GO
262
263ALTER TABLE [dbo].[sales] WITH CHECK ADD FOREIGN KEY([title_id])
264REFERENCES [dbo].[titles] ([title_id])
265GO
266
267--ALTER TABLE [dbo].[title_author]
268--DROP CONSTRAINT [FK__title_aut__au_id__267ABA7A]
269--GO
270
271--ALTER TABLE [dbo].[title_author]
272--ADD FOREIGN KEY ([au_id]) REFERENCES [dbo].[authors] ([au_id])
273--GO
274
275SET ANSI_PADDING OFF
276GO
277
278---- 3 лаба ----
279
280set dateformat mdy
281/* Включение Ñтрок в таблицу authors */
282INSERT authors VALUES('111-00-0004', 'ÐлекÑей', 'Хохлов', '223 226-2295', '589 5-Ñ Ð¡Ñ‚Ñ€Ð¾Ð¸Ñ‚ÐµÐ»ÐµÐ¹', 'С-Петербург', 'РФ', '94113', 0)
283INSERT authors VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
284INSERT authors VALUES('213-46-8915', 'Green', 'Marjorie', '415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618', 1)
285INSERT authors VALUES('238-95-7766', 'Carson', 'Cheryl', '415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', '94705', 1)
286INSERT authors VALUES('111-00-0001', 'Игорь', 'Фигурнов', '223 226-8884', '589 Б.ПолÑнка', 'МоÑква', 'РФ', '94111', 1)
287INSERT authors VALUES('998-72-3567', 'Ringer', 'Albert', '801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1)
288INSERT authors VALUES('899-46-2035', 'Ringer', 'Anne', '801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1)
289INSERT authors VALUES('111-00-0002', 'Сергей', 'Каратыгин', '223 226-2294','589 2-Ñ Ð¯Ð¼ÑкаÑ', 'МоÑква', 'РФ', '94112', 1)
290INSERT authors VALUES('722-51-5454', 'DeFrance', 'Michel', '219 547-9982', '3 Balding Pl.', 'Gary', 'IN', '46403', 1)
291INSERT authors VALUES('807-91-6654', 'Panteley', 'Sylvia', '301 946-8853', '1956 Arlington Pl.', 'Rockville', 'MD', '20853', 1)
292INSERT authors VALUES('893-72-1158', 'McBadden', 'Heather', '707 448-4982', '301 Putnam', 'Vacaville', 'CA', '95688', 0)
293INSERT authors VALUES('724-08-9931', 'Stringer', 'Dirk', '415 843-2991', '5420 Telegraph Av.', 'Oakland', 'CA', '94609', 0)
294INSERT authors VALUES('274-80-9391', 'Straight', 'Dean', '415 834-2919', '5420 College Av.', 'Oakland', 'CA', '94609', 1)
295INSERT authors VALUES('756-30-7391', 'Karsen', 'Livia', '415 534-9219', '5720 McAuley St.', 'Oakland', 'CA', '94609', 1)
296INSERT authors VALUES('724-80-9391', 'MacFeather', 'Stearns', '415 354-7128', '44 Upland Hts.', 'Oakland', 'CA', '94612', 1)
297INSERT authors VALUES('427-17-2319', 'Dull', 'Ann', '415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301', 1)
298INSERT authors VALUES('672-71-3249', 'Yokomoto', 'Akiko', '415 935-4228', '3 Silver Ct.', 'Walnut Creek', 'CA', '94595', 1)
299INSERT authors VALUES('267-41-2394', 'O''Leary', 'Michael', '408 286-2428', '22 Cleveland Av. #14', 'San Jose', 'CA', '95128', 1)
300INSERT authors VALUES('472-27-2349', 'Gringlesby', 'Burt', '707 938-6445', 'PO Box 792', 'Covelo', 'CA', '95428', 3)
301INSERT authors VALUES('527-72-3246', 'Greene', 'Morningstar', '615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN', '37215', 0)
302INSERT authors VALUES('172-32-1176', 'White', 'Johnson', '408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025', 1)
303INSERT authors VALUES('712-45-1867', 'del Castillo', 'Innes', '615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', '48105', 1)
304INSERT authors VALUES('846-92-7186', 'Hunter', 'Sheryl', '415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', '94301', 1)
305INSERT authors VALUES('486-29-1786', 'Locksley', 'Charlene', '415 585-4620', '18 Broadway Av.', 'San Francisco', 'CA', '94130', 1)
306INSERT authors VALUES('648-92-1872', 'Blotchet-Halls', 'Reginald', '503 745-6402','55 Hillsdale Bl.', 'Corvallis', 'OR', '97330', 1)
307INSERT authors VALUES('341-22-1782', 'Smith', 'Meander', '913 843-0462', '10 Mississippi Dr.', 'Lawrence', 'KS', '66044', 0)
308
309
310
311
312/* Включение Ñтрок в таблицу titles */
313
314INSERT titles VALUES ('PC1111', 'ПопулÑрно о компьютере', 'popular_comp', '1389', $20.00, $8000.00, 10, 4095, 'Muckraking reporting on the world''s largest computer hardware and software manufacturers.', '06/12/94')
315INSERT titles VALUES ('PC8888', 'Secrets of Silicon Valley', 'popular_comp', '1389', $20.00, $8000.00, 10, 4095, 'Muckraking reporting on the world''s largest computer hardware and software manufacturers.', '06/12/94')
316INSERT titles VALUES ('BU1032', 'The Busy Executive''s Database Guide', 'business', '1389', $19.99, $5000.00, 10, 4095, 'An overview of available database systems with emphasis on common business applications. Illustrated.', '06/12/91')
317INSERT titles VALUES ('PS7777', 'Emotional Security: A New Algorithm', 'psychology', '0736', $7.99, $4000.00, 10, 3336, 'Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized.', '06/12/91')
318INSERT titles VALUES ('PS3333', 'Prolonged Data Deprivation: Four Case Studies', 'psychology', '0736', $19.99, $2000.00, 10, 4072, 'What happens when the data runs dry? Searching evaluations of information-shortage effects.', '06/12/91')
319INSERT titles VALUES ('BU1111', 'Cooking with Computers: Surreptitious Balance Sheets', 'business', '1389', $11.95, $5000.00, 10, 3876, 'Helpful hints on how to use your electronic resources to the best advantage.', '06/09/91')
320INSERT titles VALUES ('MC2222', 'Silicon Valley Gastronomic Treats', 'mod_cook', '0877', $19.99, $0.00, 12, 2032, 'Favorite recipes for quick, easy, and elegant meals.', '06/09/91')
321INSERT titles VALUES ('TC7777', 'Sushi, Anyone?', 'trad_cook', '0877', $14.99, $8000.00, 10, 4095, 'Detailed instructions on how to make authentic Japanese sushi in your spare time.', '06/12/91')
322INSERT titles VALUES ('TC4203', 'Fifty Years in Buckingham Palace Kitchens', 'trad_cook', '0877', $11.95, $4000.00, 14, 15096, 'More anecdotes from the Queens favorite cook describing life among English royalty. Recipes, techniques, tender vignettes.', '06/12/91')
323INSERT titles VALUES ('PC1035', 'But Is It User Friendly?', 'popular_comp', '1389', $22.95, $7000.00, 16, 8780, 'A survey of software for the naive user, focusing on the friendliness of each.', '06/30/91')
324INSERT titles VALUES('BU2075', 'You Can Combat Computer Stress!', 'business', '0736', $2.99, $10125.00, 24, 18722, 'The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.', '06/30/91')
325INSERT titles VALUES('PS2095', 'Is Anger the Enemy?', 'psychology', '0736', $10.95, $2275.00, 12, 2045, 'Carefully researched study of the effects of strong emotions on the body. Metabolic charts included.', '06/15/91')
326INSERT titles VALUES('PS2106', 'Life Without Fear', 'psychology', '0736', $7.00, $6000.00, 10, 111, 'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately.', '10/05/91')
327INSERT titles VALUES('MC3021', 'The Gourmet Microwave', 'mod_cook', '0877', $2.99, $15000.00, 24, 22246, 'Traditional French gourmet recipes adapted for modern microwave cooking.', '06/18/91')
328INSERT titles VALUES('TC3219', 'Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean', 'trad_cook', '0877', $20.95, $7000.00, 10, 375, 'Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.', '10/21/91')
329INSERT titles (title_id, title, pub_id) VALUES('MC3026', 'The Psychology of Computer Cooking', '0877')
330INSERT titles VALUES ('BU7832', 'Straight Talk About Computers', 'business', '1389', $19.99, $5000.00, 10, 4095, 'Annotated analysis of what computers can do for you: a no-hype guide for the critical user.', '06/22/91')
331INSERT titles VALUES('PS1372', 'Computer Phobic AND Non-Phobic Individuals: Behavior Variations', 'psychology', '0877', $21.59, $7000.00, 10, 375, 'A must for the specialist, this book examines the difference between those who hate and fear computers and those who don''t.', '10/21/91')
332INSERT titles (title_id, title, type, pub_id, notes) VALUES('PC9999', 'Net Etiquette', 'popular_comp', '1389', 'A must-read for computer conferencing.')
333--повторный первичный ключ
334--INSERT titles VALUES ('PC8888', 'Secrets of Silicon Valley', 'popular_comp', '1389', $20.00, $8000.00, 10, 4095, 'Muckraking reporting on the world''s largest computer hardware and software manufacturers.', '06/12/94')
335
336/* Включение Ñтрок в таблицу titleauthor */
337
338-- нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ PC2222
339--INSERT title_author VALUES('111-00-0004', 'PC2222', 1, 100)
340INSERT title_author VALUES('409-56-7008', 'BU1032', 1, 60)
341INSERT title_author VALUES('486-29-1786', 'PS7777', 1, 100)
342INSERT title_author VALUES('486-29-1786', 'PC9999', 1, 100)
343INSERT title_author VALUES('712-45-1867', 'MC2222', 1, 100)
344INSERT title_author VALUES('172-32-1176', 'PS3333', 1, 100)
345INSERT title_author VALUES('213-46-8915', 'BU1032', 2, 40)
346INSERT title_author VALUES('238-95-7766', 'PC1035', 1, 100)
347-- нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ BU2075
348--INSERT title_author VALUES('213-46-8915', 'BU2075', 1, 100)
349-- нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ PS2091
350--INSERT title_author VALUES('998-72-3567', 'PS2091', 1, 50)
351--INSERT title_author VALUES('899-46-2035', 'PS2091', 2, 50)
352INSERT title_author VALUES('998-72-3567', 'PS2106', 1, 100)
353-- нет автора Ñ Ñ‚Ð°ÐºÐ¸Ð¼ id
354--INSERT title_author VALUES('722-51-5454', 'MC3021', 1, 75)
355INSERT title_author VALUES('899-46-2035', 'MC3021', 2, 25)
356-- нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ TC3218
357--INSERT title_author VALUES('807-91-6654', 'TC3218', 1, 100)
358INSERT title_author VALUES('274-80-9391', 'BU7832', 1, 100)
359INSERT title_author VALUES('427-17-2319', 'PC8888', 1, 50)
360INSERT title_author VALUES('846-92-7186', 'PC8888', 2, 50)
361INSERT title_author VALUES('756-30-7391', 'PS1372', 1, 75)
362INSERT title_author VALUES('724-80-9391', 'PS1372', 2, 25)
363INSERT title_author VALUES('724-80-9391', 'BU1111', 1, 60)
364INSERT title_author VALUES('267-41-2394', 'BU1111', 2, 40)
365INSERT title_author VALUES('672-71-3249', 'TC7777', 1, 40)
366INSERT title_author VALUES('267-41-2394', 'TC7777', 2, 30)
367INSERT title_author VALUES('472-27-2349', 'TC7777', 3, 30)
368INSERT title_author VALUES('648-92-1872', 'TC4203', 1, 100)
369/* Включение Ñтрок в таблицу sales */
370--нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ PS2091
371--INSERT sales VALUES('7066', 'QA7442.3', '09/13/94', 75, 'ON invoice','PS2091')
372--нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ PS2091
373--INSERT sales VALUES('7067', 'D4482', '09/14/94', 10, 'Net 60','PS2091')
374--нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ PS2091
375--INSERT sales VALUES('7131', 'N914008', '09/14/94', 20, 'Net 30','PS2091')
376INSERT sales VALUES('7131', 'N914014', '09/14/94', 25, 'Net 30','MC3021')
377INSERT sales VALUES('8042', '423LL922', '09/14/94', 15, 'ON invoice','MC3021')
378INSERT sales VALUES('8042', '423LL930', '09/14/94', 10, 'ON invoice','BU1032')
379--нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ PS2091
380--INSERT sales VALUES('6380', '722a', '09/13/94', 3, 'Net 60','PS2091')
381INSERT sales VALUES('6380', '6871', '09/14/94', 5, 'Net 60','BU1032')
382INSERT sales VALUES('8042','P723', '03/11/93', 25, 'Net 30', 'BU1111')
383INSERT sales VALUES('7896','X999', '02/21/93', 35, 'ON invoice', 'BU2075')
384INSERT sales VALUES('7896','QQ2299', '10/28/93', 15, 'Net 60', 'BU7832')
385INSERT sales VALUES('7896','TQ456', '12/12/93', 10, 'Net 60', 'MC2222')
386INSERT sales VALUES('8042','QA879.1', '5/22/93', 30, 'Net 30', 'PC1035')
387INSERT sales VALUES('7066','A2976', '5/24/93', 50, 'Net 30', 'PC8888')
388INSERT sales VALUES('7131','P3087a', '5/29/93', 20, 'Net 60', 'PS1372')
389INSERT sales VALUES('7131','P3087a', '5/29/93', 25, 'Net 60', 'PS2106')
390INSERT sales VALUES('7131','P3087a', '5/29/93', 15, 'Net 60', 'PS3333')
391INSERT sales VALUES('7131','P3087a', '5/29/93', 25, 'Net 60', 'PS7777')
392--нет книги Ñ ÐºÐ»ÑŽÑ‡Ð¾Ð¼ TC3218
393--INSERT sales VALUES('7067','P2121', '6/15/92', 40, 'Net 30', 'TC3218')
394INSERT sales VALUES('7067','P2121', '6/15/92', 20, 'Net 30', 'TC4203')
395INSERT sales VALUES('7067','P2121', '6/15/92', 20, 'Net 30', 'TC7777')
396
397SELECT * INTO authors_new FROM authors WHERE state = 'UT'
398INSERT authors SELECT * FROM authors_new AS an
399WHERE NOT EXISTS(
400 SELECT au_id FROM authors AS a WHERE an.au_id = a.au_id
401)
402
403UPDATE authors_new SET au_id=REPLACE(au_id, SUBSTRING(au_id, 0, 2), '1')
404INSERT authors SELECT * FROM authors_new
405
406SELECT a.au_id, a.au_lname, a.au_fname, a.phone, a.address, a.city, a.state, a.zip, a.contract, ta.au_ord, ta.royalTyper, t.title_id, t.title, t.type, t.pub_id, t.price, t.advance, t.royalty, t.ytd_sales, t.notes,
407 t.pubdate AS stor_id, s.ord_num, s.ord_date, s.qty, s.payterms
408FROM dbo.authors AS a FULL OUTER JOIN
409 dbo.title_author AS ta ON a.au_id = ta.au_id FULL OUTER JOIN
410 dbo.titles AS t ON ta.title_id = t.title_id FULL OUTER JOIN
411 dbo.sales AS s ON t.title_id = s.title_id
412
413
414INSERT authors VALUES('111-11-1112', 'БориÑ', 'Советов', '999 843-0625', 'ул. Мира, 32', 'Екатеринбург', 'РФ', '62007', 0)
415INSERT authors VALUES('111-11-1113', 'ВладиÑлав', 'ЦехановÑкий', '999 843-0624', 'ул. Мира, 32', 'Екатеринбург', 'РФ', '62007', 1)
416
417INSERT titles VALUES ('BD222', 'Базы данных: Ñ‚ÐµÐ¾Ñ€Ð¸Ñ Ð¸ практика', 'comp_sciense', '2354', $2.00, $800.00, 99, 5, 'Ð’ работе изложены вопроÑÑ‹ поÑÑ‚Ñ€Ð¾ÐµÐ½Ð¸Ñ Ð¸ иÑÐ¿Ð¾Ð»ÑŒÐ·Ð¾Ð²Ð°Ð½Ð¸Ñ Ñ‚ÐµÑ…Ð½Ð¾Ð»Ð¾Ð³Ð¸Ð¸ баз данных в процеÑÑе выработки и принÑÑ‚Ð¸Ñ Ñ€ÐµÑˆÐµÐ½Ð¸Ð¹.', '06/10/05')
418
419INSERT title_author VALUES('111-11-1112', 'BD222', 1, 50)
420INSERT title_author VALUES('111-11-1113', 'BD222', 2, 50)
421
422INSERT sales VALUES('244','S111', '6/14/06', 3, 'prepayment', 'BD222')
423INSERT sales VALUES('245','S112', '6/14/06', 3, '99% sale', 'BD222')
424INSERT sales VALUES('246','S113', '6/14/06', 3, 'for free', 'BD222')
425
426---- 4 лаба ----
427
428USE [udb_ÐверьÑнов_Лощенко]
429GO
430
431-- Оператор SELECT выбирает из таблицы authors, указанной в параметре FROM, Ñтолбцы au_id, au_lname, au_fname.
432Select au_id, au_lname, au_fname from authors
433-- Ðовое Ð¸Ð¼Ñ Ñтолбца задаётÑÑ Ð»Ð¸Ð±Ð¾ приÑваиванием "идентификатор = au_id", либо Ñ Ð¸Ñпользованием ключевого Ñлова AS "au_id AS идентификатор".
434Select идентификатор =au_id, Ñ„Ð°Ð¼Ð¸Ð»Ð¸Ñ = au_lname, имÑ=au_fname from authors
435-- При наличии в новом имени Ñтолбца пробелов или других ÑпецÑимволов, Ð¸Ð¼Ñ Ð·Ð°ÐºÐ»ÑŽÑ‡Ð°ÐµÑ‚ÑÑ Ð² кавычки или в квадратные Ñкобки.
436Select идентификатор =au_id, 'Ñ„Ð°Ð¼Ð¸Ð»Ð¸Ñ Ð°Ð²Ñ‚Ð¾Ñ€Ð°'=au_lname, имÑ=au_fname from authors
437-- Столбец может быть конÑтантным, ÐºÐ°Ð¶Ð´Ð°Ñ Ñтрочка будет равна заданному значению.
438Select 'Цена ($)= ', price from titles
439-- Команда WHERE позволÑет задать уÑловие выбора Ñтрок из таблицы.
440Select id=au_id, name=au_lname, fullname=au_fname from authors WHERE city='Oakland'
441-- То же Ñамое, но Ñ Ð¿Ñ€Ð¸Ñваиванием новых имён Ñтолбцам на руÑÑком Ñзыке.
442Select id=authors.au_id, фамилиÑ=authors.au_lname, имÑ=authors.au_fname from authors WHERE city='Oakland'
443-- Ð’ Ñтом запроÑе иÑпользуетÑÑ Ð²Ñ‹Ñ‡Ð¸ÑлÑемое выражение, результат которого попадает в новый Ñтолбец.
444-- Путь к таблице задаётÑÑ Ð¿Ð¾Ð»Ð½Ð¾Ñтью (полный локальный путь).
445-- Ð’ уÑловии выбора применён логичеÑкий оператор "не меньше чем" (!<).
446select title_id, price, new_price=price*1.15 from udb_ÐверьÑнов_Лощенко..titles where advance !< $5000
447-- обработка NULL
448-- Ð’ поÑледующих двух запроÑах поле ytd_sales может быть равно NULL, поÑтому можно проверÑÑ‚ÑŒ его оператором IS NOT NULL во избежание ошибок.
449select title_id, ytd_sales, '2*ytd'=2*ytd_sales from titles
450select title_id, ytd_sales, price*ytd_sales from titles
451-- Таблице можно задавать новое Ð¸Ð¼Ñ Ð¸ иÑпользовать его при указании выбираемых Ñтолбцов.
452Select 'Идентификатор'=a.au_id, name=a.au_lname, fullname=a.au_fname from authors a
453/* объÑÑните результаты Ñледующих запроÑов */
454-- ПоÑледующие два запроÑа выводÑÑ‚ вÑе данные из таблиц titles и sales ÑоответÑтвенно.
455select * from titles
456
457select * from sales
458
459/* Ñравните и объÑÑните результаты Ñледующей группы
460запроÑов */
461-- Ðтот Ð·Ð°Ð¿Ñ€Ð¾Ñ ÑцеплÑет каждую Ñтроку таблицы titles Ñо Ñтрокой таблицы sales.
462select * from titles, sales
463-- То же Ñамое, но Ñтроки будут Ñцеплены и выведены по уÑловию поÑле команды WHERE.
464select * from titles, sales where titles.title_id=sales.title_id and titles.title_id= 'PS2106'
465-- Ð’ данном запроÑе Ñтроки будут Ñцеплены Ñ Ð¿Ð¾Ð¼Ð¾Ñ‰ÑŒÑŽ оператора INNER JOIN по уÑловию ON и отфильтрованы по уÑловию WHERE и выведено только два Ñтолбца и один новый вычиÑлÑемый Ñтолбец.
466select titles.title_id, stor_id, qty*price from titles
467INNER JOIN sales ON titles.title_id= sales.title_id where titles.title_id= 'PS2106'
468/* Ñравните и объÑÑните результаты трех Ñледующих
469запроÑов */
470-- Ð’ результате Ñтого запроÑа будут выбраны только те Ñтроки, Ð´Ð»Ñ ÐºÐ¾Ñ‚Ð¾Ñ€Ñ‹Ñ… выполнÑетÑÑ ÑƒÑловие поÑле Ñлова ON.
471select titles.title_id, stor_id, qty*price from titles INNER JOIN sales ON titles.title_id=sales.title_id;
472-- Ð’ результате Ñтого запроÑа будут выбраны вÑе Ñтроки таблицы titles, а Ð·Ð½Ð°Ñ‡ÐµÐ½Ð¸Ñ Ð¿Ñ€Ð°Ð²Ñ‹Ñ… полей, не подходÑщие под уÑловие, будут равны NULL.
473select titles.title_id, stor_id, qty*price from titles LEFT OUTER JOIN sales ON titles.title_id=sales.title_id
474-- Результат Ñтого запроÑа такой же, поÑкольку запиÑÑŒ в таблице sales не может ÑущеÑтвовать без запиÑи title_id и поÑтому результат FULL JOIN равен результату LEFT JOIN.
475select titles.title_id, stor_id, qty*price from titles FULL OUTER JOIN sales ON titles.title_id=sales.title_id
476/* выполните и Ñформулируйте результат данного
477запроÑа */
478-- Ðтот Ð·Ð°Ð¿Ñ€Ð¾Ñ Ð²Ñ‹Ð²Ð¾Ð´Ð¸Ñ‚ вÑе данные о книге (автор, название, дата продажи и Ñ‚.п.) из таблиц authors, titles, sales Ð´Ð»Ñ ÐºÐ½Ð¸Ð³Ð¸ Ñ id = PS2106.
479select * from authors inner join title_author
480inner join titles on title_author.title_id = titles.title_id
481on authors.au_id = title_author.au_id where titles.title_id= 'PS2106'
482/* выполните и объÑÑните результаты Ñледующих запроÑов */
483-- Выводит вÑе Ð·Ð½Ð°Ñ‡ÐµÐ½Ð¸Ñ Ñтолбца au_id.
484select au_id from title_author
485-- Выводит только уникальные Ð·Ð½Ð°Ñ‡ÐµÐ½Ð¸Ñ Ñтолбца au_id.
486select distinct au_id from title_author
487-- Выводит количеÑтво Ñтрок в таблице.
488select count(*) from title_author
489-- То же Ñамое, поÑкольку оператор DISTINCT применÑетÑÑ ÑƒÐ¶Ðµ к результату подÑчёта.
490select distinct count(*) from title_author
491-- Выводит количеÑтво Ñтрок Ñ ÑƒÐ½Ð¸ÐºÐ°Ð»ÑŒÐ½Ñ‹Ð¼Ð¸ значениÑми au_id.
492select count(distinct au_id) from title_author
493/*ЗÐПРОСЫ к СИСТЕМÐЫМ таблицам и предÑтавлениÑм */
494/*что возвращают Ñледующие запроÑÑ‹? */
495
496-- ПоÑледующие два запроÑа выводÑÑ‚ имена пользовательÑких Ñтолбцов и их таблиц в текущей базе данных (sysobjects.type = U Ñто тип "пользовательÑÐºÐ°Ñ Ñ‚Ð°Ð±Ð»Ð¸Ñ†Ð°").
497select syscolumns.name, sysobjects.name from syscolumns,
498sysobjects where sysobjects.id=syscolumns.id and sysobjects.type= 'U'
499
500select sys.columns.name, sys.tables.name from
501sys.columns inner join sys.tables on sys.columns.object_id=sys.tables.object_id
502
503-- Выводит вÑÑŽ информацию о Ñтолбцах в текущей базе данных.
504select * From INFORMATION_SCHEMA.COLUMNS
505-- Ðти два запроÑа выводÑÑ‚ вÑех пользователей текущей БД.
506select * from sysusers
507-- или
508select * from sys.sysusers
509/* объÑÑните критерии Ð´Ð»Ñ Ð¾Ñ‚Ð±Ð¾Ñ€Ð° Ñтрок в каждом из
510Ñледующих запроÑов */
511-- УÑловие иÑтинно, когда удвоенное значение Ð¿Ð¾Ð»Ñ advance больше Ð¿Ñ€Ð¾Ð¸Ð·Ð²ÐµÐ´ÐµÐ½Ð¸Ñ Ð·Ð½Ð°Ñ‡ÐµÐ½Ð¸Ð¹ ytd_sales и price.
512select title_id, ytd_sales,'advance*2'=advance*2,'price*ytd_sales'=price *ytd_sales from titles
513where advance*2 > ytd_sales*price
514-- УÑловие иÑтинно, когда значение Ð¿Ð¾Ð»Ñ ytd_sales находитÑÑ Ð² пределах от 4095 до 12000.
515select title_id, ytd_sales,price*ytd_sales from titles
516where ytd_sales between 4095 and 12000
517-- УÑловие иÑтинно, когда значение Ð¿Ð¾Ð»Ñ "Штат" находитÑÑ Ð¼ÐµÐ¶Ð´Ñƒ 'CA' и 'IN' (в лекÑикографичеÑком порÑдке).
518select id=au_id, name=au_lname, [Штат/Страна]=state from authors
519where state between 'CA'and 'IN'
520-- УÑловие иÑтинно, когда значение Ð¿Ð¾Ð»Ñ "Штат" ÐЕ находитÑÑ Ð¼ÐµÐ¶Ð´Ñƒ 'CA' и 'IN' (в лекÑикографичеÑком порÑдке).
521select id=au_id, name=au_lname, state=state from authors
522where state not between 'CA'and 'IN'
523-- УÑловие иÑтинно, когда значение Ð¿Ð¾Ð»Ñ "Штат" равно одному из ÑпиÑка ('CA', 'IN', 'MD').
524select id=au_id, name=au_lname, state=state from authors
525where state in ('CA', 'IN', 'MD')
526-- УÑловие иÑтинно, когда номер телефона начинаетÑÑ Ñ Ñ†Ð¸Ñ„Ñ€ 415.
527select au_lname as ФамилиÑ, au_fname ИмÑ, Телефон=phone from authors
528where phone like '415%'
529-- УÑловие иÑтинно, когда номер телефона ÐЕ начинаетÑÑ Ñ Ñ†Ð¸Ñ„Ñ€ 415.
530select id=au_id, name=au_lname, phone=phone from authors
531where phone not like '415%'
532-- УÑловие иÑтинно, когда номер телефона начинаетÑÑ Ñ Ñ†Ð¸Ñ„Ñ€Ñ‹ 4, далее Ð»ÑŽÐ±Ð°Ñ Ñ†Ð¸Ñ„Ñ€Ð°, далее цифра 5 и заканчиваетÑÑ Ð½Ð° любые другие.
533select id=au_id, name=au_lname, phone=phone from authors
534where phone like '4_5%'
535-- УÑловие иÑтинно, когда номер телефона начинаетÑÑ Ñ Ñ†Ð¸Ñ„Ñ€Ñ‹ от 2 до 7, далее цифра 1, далее цифра от 2 до 9 и заканчиваетÑÑ Ð½Ð° любые другие.
536select id=au_id, name=au_lname, phone=phone from authors
537where phone like '[2-7]1[2-9]%'
538-- УÑловие иÑтинно, когда номер телефона ÐЕ начинаетÑÑ Ñ Ñ†Ð¸Ñ„Ñ€Ñ‹ от 2 до 7, далее цифра 1, далее цифра от 2 до 9 и заканчиваетÑÑ Ð½Ð° любые другие.
539select id=au_id, name=au_lname, phone=phone from authors
540where phone like '[^2-7]1[2-9]%'
541-- УÑловие иÑтинно, когда поле advance равно NULL.
542select title_id,type, advance from titles where advance is NULL
543-- УÑловие иÑтинно, когда поле advance ÐЕ равно NULL.
544select title_id,type, advance from titles where advance is NOT NULL
545-- УÑловие иÑтинно, когда поле advance имеет значение больше 0.
546select title_id,type, advance from titles where advance > 0
547/* объÑÑните в чем отличие Ñтих запроÑов */
548-- Выводит данные двух Ñтолбцов из таблицы titles.
549select title_id, title from titles
550-- Выводит данные двух Ñтолбцов и запиÑывает их в новую таблицу NEW_TITLES.
551select title_id, title INTO NEW_TITLES from titles
552-- Выводит данные двух Ñтолбцов и запиÑывает их во временну таблицу #NEW_TITLES, ÐºÐ¾Ñ‚Ð¾Ñ€Ð°Ñ Ð±ÑƒÐ´ÐµÑ‚ удалена по окончании ÑеÑÑии.
553select title_id, title INTO #NEW_TITLES from titles
554-- Выводит вÑе данные из новой таблицы.
555select * from NEW_TITLES
556-- Выводит вÑе данные из временной таблицы.
557select * from #NEW_TITLES
558-- УдалÑет новую таблицу.
559drop table NEW_TITLES
560
561-- Вариант 6
562-- ПодÑчет количеÑтва изданий, в напиÑании которых принÑли учаÑтие авторы Советов или Карпова или Попова.
563SELECT count([title_id]) FROM [dbo].[title_author] WHERE [au_id] = (
564SELECT [au_id] FROM [dbo].[authors] WHERE [au_fname] = 'Советов' OR [au_fname] = 'Карпова' OR [au_fname] = 'Попова'
565)
566
567-- Вывод вÑех Ñведений об авторах изданий, Ñодержащих в названии Ñлово «ТеориÑ» в любом падеже.
568SELECT a.au_id, a.au_lname, a.au_fname, a.phone, a.address, a.city, a.state, a.zip, a.contract
569 FROM [dbo].authors AS a JOIN
570 [dbo].title_author AS ta ON a.au_id = ta.au_id JOIN
571 [dbo].titles AS t ON t.title_id = ta.title_id AND t.title LIKE '%теори%'
572
573-- Вывода без повторений облаÑтей знаний (Ñтолбец type), по которым имеютÑÑ ÐºÐ½Ð¸Ð³Ð¸.
574SELECT DISTINCT [type] from titles
575
576-- ЗапроÑ, выводÑщий вÑе данные из базы.
577SELECT a.au_id, a.au_lname, a.au_fname, a.phone, a.address, a.city, a.state, a.zip, a.contract, ta.au_ord, ta.royalTyper, t.title_id, t.title, t.type, t.pub_id, t.price, t.advance, t.royalty, t.ytd_sales, t.notes,
578 t.pubdate AS stor_id, s.ord_num, s.ord_date, s.qty, s.payterms
579FROM dbo.authors AS a FULL JOIN
580 dbo.title_author AS ta ON a.au_id = ta.au_id FULL JOIN
581 dbo.titles AS t ON ta.title_id = t.title_id FULL JOIN
582 dbo.sales AS s ON t.title_id = s.title_id
583
584---- 5 лаба ----
585
586USE [udb_ÐверьÑнов_Лощенко]
587GO