Results 1 to 18 of 18

Thread: SQL ,

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Registered User DarkPaIn's Avatar
    Join Date: Mar:2008
    Location:
    Posts: 1,773

    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

  2. #2
    White awesomeness z0ne's Avatar
    Join Date: Feb:2002
    Location: o
    Posts: 13,331
    "ReleaseDate", ? .

  3. #3
    Registered User DarkPaIn's Avatar
    Join Date: Mar:2008
    Location:
    Posts: 1,773
    ... . . Q1 , Q 4 .

    :
    Avatar - Q1
    Gladiator - Q2
    ..

  4. #4
    XaMaB's Avatar
    Join Date: Nov:2001
    Location:
    Posts: 20,387
    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
    . () r.ReleaseID, filmID?
    : XaMaB; . 0.42

    In God we Trust (all others must submit a X.509 certificate). , ()

  5. #5
    Registered User DarkPaIn's Avatar
    Join Date: Mar:2008
    Location:
    Posts: 1,773
    FilmID , . , ...

  6. #6
    ! vbTheKing's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 4,138
    , "" .
    ''? * *
    - !
    ...

  7. #7
    An Ocean Soul Guardian's Avatar
    Join Date: Jul:2001
    Location:
    Posts: 1,203
    , .

  8. #8
    XaMaB's Avatar
    Join Date: Nov:2001
    Location:
    Posts: 20,387
    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). , ()

  9. #9
    Registered User DarkPaIn's Avatar
    Join Date: Mar:2008
    Location:
    Posts: 1,773
    . .

    ---------- 17:29 ---------- 01:49 ----------

    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)
    A


    Uploaded with ImageShack.us

    ? ... ...

  10. #10
    An Ocean Soul Guardian's Avatar
    Join Date: Jul:2001
    Location:
    Posts: 1,203
    . FilmID Actors, Directors Genre. Films ActorsID, DirectorsID, GenreID.

  11. #11
    Registered User DarkPaIn's Avatar
    Join Date: Mar:2008
    Location:
    Posts: 1,773
    Quote Originally Posted by Guardian View Post
    . FilmID Actors, Directors Genre. Films ActorsID, DirectorsID, GenreID.
    Actors, Directors .., ActorID|ActorName, ActorID FK Films->ActorID ? PK... ID...

  12. #12
    XaMaB's Avatar
    Join Date: Nov:2001
    Location:
    Posts: 20,387
    . 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). , ()

  13. #13
    XaMaB's Avatar
    Join Date: Nov:2001
    Location:
    Posts: 20,387
    actorsID films.
    2 ( ) actorID, filmID. . . 100 , 100 -
    : XaMaB; . 0.42

    In God we Trust (all others must submit a X.509 certificate). , ()

  14. #14
    Registered User DarkPaIn's Avatar
    Join Date: Mar:2008
    Location:
    Posts: 1,773
    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
    );
    Genre FK Films(GenreID) , , PK... . , Film 3 PK... Genre Director... .. , ... FilmID...

  15. #15
    XaMaB's Avatar
    Join Date: Nov:2001
    Location:
    Posts: 20,387
    . ,
    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). , ()

  16. #16
    Registered User DarkPaIn's Avatar
    Join Date: Mar:2008
    Location:
    Posts: 1,773
    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)
    actor ... FK ...

    EDI: O .
    Last edited by DarkPaIn; 15th June 2010 at 19:57.

  17. #17
    Registered User PepiX's Avatar
    Join Date: Sep:2004
    Location:
    Posts: 13,376
    UNION ?
    ███████ ( Ilko)
    ███████-, ? ( )
    ███████

  18. #18
    Registered User
    Join Date: Feb:2006
    Location: Plovdiv
    Posts: 392
    "Person" (many to many) . (, ..), " ".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Copyright © 1999-2011 . .
iskamPC.com | mobility.BG | Bloody's Techblog | | 3D Vision Blog |