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
-
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
-
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 ----------
ACode: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 #14Genre 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 #16actor ... 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
-
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