Results 1 to 18 of 18
Thread: SQL çàÿâêà, ïîìîù
Hybrid View
-
14th June 2010 18:42 #1
SQL çàÿâêà, ïîìîù
Çäðàâåéòå. Íàäÿâàì ñå íÿêîé äà ìè ïîìîãíå. Èìàì äâå òàáëèöè - Films è ReleaseDate. È ñå îïèòâàì íàêðàÿ äà èçâåäà çà âñåêè ôèëì â êîÿ ÷åòâúðò íà ãîäèíàòà å èçëú÷âàí. Äî ìîìåíòà óñïÿõ äà ãè ïîêàæà, íî âñè÷êèòå ñà îòäåëíî... À êðàéíèÿ ðåçóëòàò êîéòî èñêàì å äà áúäå ïîêàçàíî â åäíà êîëîíà ñðåùó âñåêè ôèëì â êîé Quarter ñå å èçëú÷âàë.
Åòî è ñàìàòà çàÿâêà
Òîâà å ðåçóëòàòà îò íåÿCode:SELECT f.FilmName, SUM (CASE DATEPART (quarter, r.ReleaseDate) WHEN 1 THEN r.FilmID ELSE 0 END) AS [First Quarter], SUM (CASE DATEPART (quarter, r.ReleaseDate) WHEN 2 THEN r.FilmID ELSE 0 END) AS [Second Quarter], SUM (CASE DATEPART (quarter, r.ReleaseDate) WHEN 3 THEN r.FilmID ELSE 0 END) AS [Third Quarter], SUM (CASE DATEPART (quarter, r.ReleaseDate) WHEN 4 THEN r.FilmID ELSE 0 END) AS [Fourth Quarter] FROM Release r INNER JOIN Films f ON f.FilmID=r.ReleaseID GROUP BY f.FilmName,r.ReleaseDate ORDER BY f.FilmName

-----------------------------------
À åòî è ñàìèòå òàáëèöè
*Films

*Release
-
14th June 2010 19:46 #2
È çàùî ïðàâèø öÿëà íîâà òàáëèöà çà åäèí "ReleaseDate", êàòî òîé ìîæå äà òè å â îñíîâíàòà òàáëèöà? Íåùî ìàé ìàé íå çàãðÿâàì èäåÿòà.
-
14th June 2010 19:56 #3
Ïîíåæå ìè å çà êóðñîâà ðàáîòà... è ñå èñêà äà ñà íîðìàëèçèðàíè äàííèòå. Íî òîâà íå å îò çíà÷åíèå. Âúïðîñà ìè å íàêðàÿ êàê ñðåùó âñåêè ôèëì àêî ðåëèéçà ìó å Q1 äà ãî èçïèñâà â åäíà êîëîíà, à íå êàêòî ñåãà çà âñÿêî Q äà èìàì 4 êîëîíè.
ÏÐÈÌÅÐ:
Avatar - Q1
Gladiator - Q2
è ò.í.
-
14th June 2010 20:41 #4Åáåíòóàëíî ïëþñà ìîæå äà ñå íàëîæè äà ñå ñìåíè ñ åäíà êîíêàò ôóíêöèÿ. À äæîéí(ò)à ùî å ïî r.ReleaseID, à íå ïî filmID?Code:
SELECT f.FilmName, 'Q' + (quarter, r.ReleaseDate) as Quarter FROM Release r INNER JOIN Films f ON f.FilmID=r.ReleaseID GROUP BY f.FilmName,r.ReleaseDate ORDER BY f.FilmName
Ïîñëåäíà ðåäàêöèÿ: èçâúðøåíà îò XaMaB; íà äíåøíà äàòà. 0.42 ñåêóíäè ñëåä ïóñêàíå íà ïîñòà
In God we Trust (all others must submit a X.509 certificate). Àêî ñïîðèø ñ èäèîò, âåðîÿòíî è òîé ïðàâè ñúùîòî èëè ñè ïîïàäíàë íà ïàðòèåí (íåïúëåí) ÷ëåí
-
14th June 2010 21:20 #5
Ïî FilmID òð äà å, ïðîñòî ñúì ãî îáúðêàë. Èíà÷å ïðîáâàõ òàêà çàÿâêàòà, íî íå ñå ïîëó÷è...
-
14th June 2010 22:56 #6
Íÿìàì âðåìå äà ìèñëÿ ïî çàÿâêàòà, íî íÿìà íèùî "íîðìàëèçèðàíî" â òîâà äà îòäåëèø äàòàòà íà èçëèçàíå íà ôèëìà.
Ëþáèìî ïèòèå çàïî÷âàùî ñ áóêâàòà 'Ê'? * Êàñà áèðà *
Àêî ìîæåø äà ëåæèø íà ïîäà áåç äà òè ñå íàëàãà äà ñå äúðæèø - âñå îùå ÍÅ ñè ïèÿí!
Äàâàéòå ñëåäâàùàòà Êàñà...
-
14th June 2010 22:59 #7
Ñëîæè ñè äàòàòà â ñúùàòà òàáëèöà, àêî èñêàø äà íîðìàëèçèðàø äîáàâè æàíð íà ôèëìà è íåãî ãî èçíåñè â îòäåëíà òàáëèöà.
-
14th June 2010 23:11 #8
âòîðè îïèò êàòî íÿìàì sql ïîä ðúêà, àìà â hardwarebg êàòî íå ñà ìè îïðàâèëè îùå "ñúðâúðà"

È äàòàòà íàèñòèíà íÿìà íóæäà îò îòäåëíà òàáëèöà, îñâåí àêî íå å ñ íÿêîëêî äàòè çà ðàçëè÷íè äúðæàâè, àìà òîãàâà ïàê íå ñà òè íàðåä òàáëèöèòå.Code:SELECT f.FilmName, 'Q' + cast((quarter, r.ReleaseDate)as char(1)) as Quarter FROM Release r
Ïîñëåäíà ðåäàêöèÿ: èçâúðøåíà îò XaMaB; íà äíåøíà äàòà. 0.42 ñåêóíäè ñëåä ïóñêàíå íà ïîñòà
In God we Trust (all others must submit a X.509 certificate). Àêî ñïîðèø ñ èäèîò, âåðîÿòíî è òîé ïðàâè ñúùîòî èëè ñè ïîïàäíàë íà ïàðòèåí (íåïúëåí) ÷ëåí
-
15th June 2010 17:29 #9
Ñàìî äà íå ïîêàçâàì è îñòàíàëèòà òàáëèöè òîãàâà. Õè÷ íå ìå áèâà ñ òàçè íîðìàëèçàöèÿ.
---------- Äîáàâåí ïîñëåäâàù êîìåíòàð â 17:29 ---------- Îðèãèíàëíèÿò êîìåíòàð å ïóñíàò â 01:49 ----------
A åòî è ñíèìêà îò äèàãðàìàòàCode:CREATE TABLE Films ( FilmID int NOT NULL IDENTITY PRIMARY KEY, FilmName varchar(30) NOT NULL, FilmRating decimal (3,1) NOT NULL, ReleaseDate datetime NOT NULL ) CREATE TABLE Actors ( ActorID int NOT NULL IDENTITY PRIMARY KEY, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, FilmID int NOT NULL, CONSTRAINT CK_ActorName CHECK ( FirstName NOT LIKE '%[0-9]%' AND FirstName LIKE '[A-Z]%' AND LastName NOT LIKE '%[0-9]%' AND LastName LIKE '[A-Z]%' ) ); ALTER TABLE Actors ADD CONSTRAINT FK_Actor FOREIGN KEY (FilmID) REFERENCES Films(FilmID) CREATE TABLE Directors ( DirectorID int NOT NULL IDENTITY PRIMARY KEY, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, FilmID int NOT NULL, CONSTRAINT CK_DirectorName CHECK ( FirstName NOT LIKE '%[0-9]%' AND FirstName LIKE '[A-Z]%' AND LastName NOT LIKE '%[0-9]%' AND LastName LIKE '[A-Z]%' ) ); ALTER TABLE Directors ADD CONSTRAINT FK_Director FOREIGN KEY (FilmID) REFERENCES Films(FilmID) CREATE TABLE Genre ( GenreID int NOT NULL IDENTITY PRIMARY KEY, GenreName varchar(20) NOT NULL, FilmID int NOT NULL ); ALTER TABLE Genre ADD CONSTRAINT FK_Genre FOREIGN KEY (FilmID) REFERENCES Films(FilmID)

Uploaded with ImageShack.us
Òàêà íîðìàëèçèðàíè ëè ñà? ×åòîõ òóêà îò êíèãàòà íà Íàêîâ è íèùî... íå ìîãà äà ðàçáåðà èäåÿòà íà òàçè íîðìàëèçàöèÿ...
-
15th June 2010 17:43 #10
Íà ïðàâ ïúò ñè. Ìàõíè FilmID îò Actors, Directors è Genre. Â Films äîáàâè ActorsID, DirectorsID, GenreID.
-
15th June 2010 18:17 #11
-
15th June 2010 17:41 #12
Öåëòà íà íîðìàëèçàöèÿòà å äà òè èçáåãíå ïîâòîðåíèåòî íà äàííè â áàçàòà. Êàòî àêî ñå ïðèëàãà ïî êíèãà äî 2-3 íîðìàëíà ôîðìà (òàêà ëè ñå êàçâàõà) âîäè äî ãîëåìè èçâðàùåíèÿ íÿêîé ïúò.
Êàòî öÿëî â òâîÿ ñëó÷àé, àêî àêòüîðà ìîæå äà å è ðåæèñüîð, òðÿáâà èìåíàòà + ÈÄ äà ãè èçíåñåø â òàáëèöà person, è åâåíòóàëíî ñ åäíà äîïúëíèòåëíà òàáëèöà äà ñå îïèñâà person_id, role (actor/director), film_id. Êàòî ðîëÿòà ðàçáèðà ñå å õóáàâî äà å îòäåëíà òàáëèöà ñúñ ÈÄ/ñòîéíîñò è â òàçè êîÿòî å ñ âðúçêè ÷îâåê/ôèëì äà ïàçèø ñàìî ïî åäíî ÈÄ çà òîâà êàòî êàêúâ ñå ÿâÿâà âúâ ôèëìàÏîñëåäíà ðåäàêöèÿ: èçâúðøåíà îò XaMaB; íà äíåøíà äàòà. 0.42 ñåêóíäè ñëåä ïóñêàíå íà ïîñòà
In God we Trust (all others must submit a X.509 certificate). Àêî ñïîðèø ñ èäèîò, âåðîÿòíî è òîé ïðàâè ñúùîòî èëè ñè ïîïàäíàë íà ïàðòèåí (íåïúëåí) ÷ëåí
-
15th June 2010 18:40 #13
Íå òè òðÿáâà actorsID âúâ films.
òðÿáâà òè íîâà òàáëèöà ñ 2 êîëîíè (çà íà÷àëî) actorID, filmID. Çàùîòî âðúçêàòà ìîæå äà òè å ìíîãî êúì ìíîãî. åäèí àêòüîð ìîæå äà èãðàå â íÿêîëêî ôèëìà. Òàêà íÿìà äà èìàø íóæäà îò 100 çàïèñà çà äæàê íèêúëñúí, à ñàìî åäèí è 100 çàïèñà ñ ïî äâå èä-òà â òðåòàòà òàáëèöàÏîñëåäíà ðåäàêöèÿ: èçâúðøåíà îò XaMaB; íà äíåøíà äàòà. 0.42 ñåêóíäè ñëåä ïóñêàíå íà ïîñòà
In God we Trust (all others must submit a X.509 certificate). Àêî ñïîðèø ñ èäèîò, âåðîÿòíî è òîé ïðàâè ñúùîòî èëè ñè ïîïàäíàë íà ïàðòèåí (íåïúëåí) ÷ëåí
-
15th June 2010 18:47 #14Ñåãà îáà÷å êàòî òðúãíå äà äàâàì îò Genre FK êúì Films(GenreID) , òî ìè äàâà ãðåøêà, ÷å íå å PK... òâà ÿñíî. Îòòàì èäâà ïðîáëåìà, ÷å â Film ñå ïîÿâÿâàò îáùî 3 PK... Genre è Director... òðÿáâà è òÿõ äà îòäåëÿ â äðóãà òàáëèöà.. òîâà çíà÷è, ÷å îò ãëàâíàòà ùå îòïàäíàò íàïúëíî... è çà äà èìàò âðúçêà òðÿáâà âúâ âñÿêà îòòÿõ ïàê äà ñå ñëàãà FilmID...Code:
CREATE TABLE Film ( FilmID int NOT NULL IDENTITY PRIMARY KEY, FilmName varchar(30) NOT NULL, FilmRating decimal (3,1) NOT NULL, FilmRelease datetime NOT NULL, GenreID int NOT NULL, DirectorID int NOT NULL ); CREATE TABLE Genre ( GenreID int NOT NULL IDENTITY, GenreName varchar(12) NOT NULL ); CREATE TABLE Director ( DirectorID int NOT NULL IDENTITY, DirectorName varchar(30) NOT NULL ); CREATE TABLE FilmActor ( FilmID int NOT NULL IDENTITY, ActorID int NOT NULL ); CREATE TABLE Actor ( ActorID int NOT NULL IDENTITY, ActorName varchar(30) NOT NULL );
-
15th June 2010 19:01 #15
Íåùî åé òàêîâà. Êîäà å ìàëêî ãðîçåí, àìà òàêà ãî ãåíåðèðà ñòóäèîòî
Code:/****** Object: Table [dbo].[Genre] Script Date: 06/15/2010 18:59:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Genre]( [GenreID] [int] IDENTITY(1,1) NOT NULL, [GenreName] [varchar](12) NOT NULL, CONSTRAINT [PK_Genre] PRIMARY KEY CLUSTERED ( [GenreID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Director] Script Date: 06/15/2010 18:59:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Director]( [DirectorID] [int] IDENTITY(1,1) NOT NULL, [DirectorName] [varchar](30) NOT NULL, CONSTRAINT [PK_Director] PRIMARY KEY CLUSTERED ( [DirectorID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Actor] Script Date: 06/15/2010 18:59:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Actor]( [ActorID] [int] IDENTITY(1,1) NOT NULL, [ActorName] [varchar](30) NOT NULL, CONSTRAINT [PK_Actor] PRIMARY KEY CLUSTERED ( [ActorID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Film] Script Date: 06/15/2010 18:59:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Film]( [FilmID] [int] IDENTITY(1,1) NOT NULL, [FilmName] [varchar](30) NOT NULL, [FilmRating] [decimal](3, 1) NOT NULL, [FilmRelease] [datetime] NOT NULL, [GenreID] [int] NOT NULL, [DirectorID] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [FilmID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[FilmActor] Script Date: 06/15/2010 18:59:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FilmActor]( [FilmID] [int] IDENTITY(1,1) NOT NULL, [ActorID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: ForeignKey [FK_Film_Film] Script Date: 06/15/2010 18:59:35 ******/ ALTER TABLE [dbo].[Film] WITH CHECK ADD CONSTRAINT [FK_Film_Film] FOREIGN KEY([FilmID]) REFERENCES [dbo].[Film] ([FilmID]) GO ALTER TABLE [dbo].[Film] CHECK CONSTRAINT [FK_Film_Film] GO /****** Object: ForeignKey [FK_Film_Genre] Script Date: 06/15/2010 18:59:35 ******/ ALTER TABLE [dbo].[Film] WITH CHECK ADD CONSTRAINT [FK_Film_Genre] FOREIGN KEY([GenreID]) REFERENCES [dbo].[Genre] ([GenreID]) GO ALTER TABLE [dbo].[Film] CHECK CONSTRAINT [FK_Film_Genre] GO /****** Object: ForeignKey [FK_FilmActor_Actor] Script Date: 06/15/2010 18:59:35 ******/ ALTER TABLE [dbo].[FilmActor] WITH CHECK ADD CONSTRAINT [FK_FilmActor_Actor] FOREIGN KEY([ActorID]) REFERENCES [dbo].[Actor] ([ActorID]) GO ALTER TABLE [dbo].[FilmActor] CHECK CONSTRAINT [FK_FilmActor_Actor] GO /****** Object: ForeignKey [FK_FilmActor_Film] Script Date: 06/15/2010 18:59:35 ******/ ALTER TABLE [dbo].[FilmActor] WITH CHECK ADD CONSTRAINT [FK_FilmActor_Film] FOREIGN KEY([FilmID]) REFERENCES [dbo].[Film] ([FilmID]) GO ALTER TABLE [dbo].[FilmActor] CHECK CONSTRAINT [FK_FilmActor_Film] GO
Ïîñëåäíà ðåäàêöèÿ: èçâúðøåíà îò XaMaB; íà äíåøíà äàòà. 0.42 ñåêóíäè ñëåä ïóñêàíå íà ïîñòà
In God we Trust (all others must submit a X.509 certificate). Àêî ñïîðèø ñ èäèîò, âåðîÿòíî è òîé ïðàâè ñúùîòî èëè ñè ïîïàäíàë íà ïàðòèåí (íåïúëåí) ÷ëåí
-
15th June 2010 19:25 #16Ñàìî äåòî ñåãà êàòî âêàðàì åäèí çàïèñ çà actor è ïîâå÷å íå ìîãà äà äîáàâÿì... Òåçè FK ñúì ãè îáúðêàë äîñòà ñèãóðíî...Code:
CREATE TABLE Film ( FilmID int NOT NULL IDENTITY PRIMARY KEY, FilmName varchar(30) NOT NULL, FilmRating decimal (3,1) NOT NULL, FilmRelease datetime NOT NULL, GenreID int NOT NULL, DirectorID int NOT NULL ); CREATE TABLE Genre ( GenreID int NOT NULL IDENTITY, GenreName varchar(12) NOT NULL ); ALTER TABLE Genre ADD CONSTRAINT FK_Genre FOREIGN KEY (GenreID) REFERENCES Film(FilmID) CREATE TABLE Director ( DirectorID int NOT NULL IDENTITY, DirectorName varchar(30) NOT NULL ); ALTER TABLE Director ADD CONSTRAINT FK_Director FOREIGN KEY (DirectorID) REFERENCES Film(FilmID) CREATE TABLE Actor ( ActorID int NOT NULL IDENTITY, ActorName varchar(30) NOT NULL ); ALTER TABLE Actor ADD CONSTRAINT FK_Actor FOREIGN KEY (ActorID) REFERENCES Film(FilmID) ALTER TABLE Actor ADD CONSTRAINT PK_Actor PRIMARY KEY (ActorID) CREATE TABLE FilmActor ( FilmID int NOT NULL, ActorID int NOT NULL ); ALTER TABLE FilmActor ADD CONSTRAINT FK_FilmActor FOREIGN KEY (FilmID) REFERENCES Film(FilmID) ALTER TABLE FilmActor ADD CONSTRAINT FK_FilmActorTwo FOREIGN KEY (ActorID) REFERENCES Actor(ActorID)
EDIÒ: Oïðàâèõ ãî âå÷å. Áëàãîäàðÿ çà ïîìîùòà
Last edited by DarkPaIn; 15th June 2010 at 19:57.
-
15th June 2010 23:48 #17
UNION çà êîìáèíèðàíå íà êîëîíèòå íå ñòàâà ëè?
-
16th June 2010 01:37 #18Registered User
Join Date: Feb:2006
Location: Plovdiv
Posts: 392
Íàïðàâè ñè òàáëèöà "Person" è äðóãà çà (many to many) êîÿòî ãëåäà êúì ëè÷íîñòè è ôèëìè. Íåêà èìà è äîïúëíèòåëíî ïîëå êàêâà å âðúçêàòà (àêòüîð, ðåæèå è ò.í.), ïà ìîæå è íåùî äà ñå ïîëó÷è ïðîóìÿâàéêè ÷å îòêðèâàø "òîïëàòà âîäà".




Reply With Quote

Lenovo ThinkPad 15 èëè IdeaPad 15
5th May 2023, 22:16 in Ìîáèëíè êîìïþòðè