SQLServerCentral.com / SQL Server 2008 / SQL Server Newbies / Need help with Select statement!! / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 12:48:04 GMT20RE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxIt did appear as a school assignment to me as well. I love helping newbies and students however if I think it is an assignment I will typically insist on seeing some effort in the form of [i]what they have tried so far[/i] before I provide anything in the way of a working solution.Mon, 25 Mar 2013 09:05:27 GMTOrlando ColamatteoRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspx[quote][b]kevaburg (3/25/2013)[/b][hr]Sorry Lynn.....it was aimed at the original poster....[/quote]Hard to tell just from the comment. It is apparent that this was some sort of college project.Mon, 25 Mar 2013 09:01:44 GMTLynn PettisRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxSorry Lynn.....it was aimed at the original poster....Mon, 25 Mar 2013 08:48:21 GMTkevaburgRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspx[quote][b]kevaburg (3/25/2013)[/b][hr]Sorry for saying this but whether it is PL-SQL or T-SQL is somewhat irrelevant......the results you expected are somewhat basic and not particularly taxing. Are you sure this person had the right to expect you to perform a task like this....?[/quote]Curious, to whom was this comment addressed?Mon, 25 Mar 2013 08:39:37 GMTLynn PettisRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxSorry for saying this but whether it is PL-SQL or T-SQL is somewhat irrelevant......the results you expected are somewhat basic and not particularly taxing. Are you sure this person had the right to expect you to perform a task like this....?Mon, 25 Mar 2013 07:58:23 GMTkevaburgRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxmeh dont worry about it, it was sort of a last ditch effort. thanks for looking at it thoughFri, 22 Mar 2013 17:47:54 GMTyasser.akhanRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxYou;re really making us work over here...I tried polishing up the SQL you provided so it would run on SQL Server but it's not even close. Syntax errors everywhere, mis-aligned columns on the insert, constraints reffing non-existent columns. Try again?Oracle code is fine, we can easily switch it to run on SQL Server and we can write ANSI-standard SELECT statements that will run on both.PS ...oh yeah, and this time, expected results would be nice, and [i]show us what you have tried[/i] ;-)Fri, 22 Mar 2013 16:34:00 GMTOrlando ColamatteoRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxThat looks like Oracle code :ermm:Fri, 22 Mar 2013 16:28:51 GMTOrlando ColamatteoRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxOh srry about that, its a rather long project so i tried to spare some goring detail but here.CREATE DATABASE WebstoreCREATE TABLE Customers (Customer_ID number(5) not null primary key,Name varchar2(32) not null,Shipping_Address varchar2(32) not null,Email varchar2(32) not null,Credit_Card# integer(16) not null,);CREATE TABLE Movies(Barcode number(3) not null ,Title Varchar2(32),Genre Varchar2(32) not null,Year Number(4)Media_Type Char(1) not null,Cost money not null,CONSTRAINT Medt primary key (barcode));CREATE TABLE DVD(Format char(10) not null,Cost money not null,Quantity number(2) not null,CONSTRAINT Medt Foreign Key(barcode) references Movies(barcode),);CREATE TABLE Videos(Format char(10) not null,Cost money not null,Quantity number(2) not null,CONSTRAINT Medt Foreign Key(barcode) references Movies(barcode),);[u]Insert Statement [/u]INSERT INTO CustomerVALUES (11111, ‘Mike Smith’, ‘111 Ship Lane, IL 67892’, ‘mikesmith@gmail.com’, 1234 5678 9101 1121);INSERT INTO CustomerVALUES (22222, ‘John Doe’, ‘222 Miss Lane, IL 64592’, ‘jdoe@gmail.com’, 9876 5678 9101 3456);INSERT INTO CustomerVALUES (33333, ‘Rick Slick’, ‘333 tree Lane, IL 61253’, ‘slickrick@gmail.com’, 4589 5980 1256 1631)INSERT INTO CustomerVALUES (44444, ‘Cheap Guy’, ‘444 cheap Lane, IL 60007’, ‘nothing@gmail.com’, 1276 5980 9876 1631)INSERT INTO MoviesVALUES (223, ‘Dark Knight’, ‘Action’, 2013, ‘D’, $20.00);INSERT INTO MoviesVALUES (132, ‘Titanic’, ‘Romance’, 1998, ‘V’, $8.00);INSERT INTO MoviesVALUES (213, ‘Avengers’, ‘Action’, 2013, ‘D’, $20.00);INSERT INTO MoviesVALUES (145, ‘Lion King’, ‘Children’, 1996, ‘V’, $6.00);INSERT INTO SalesVALUES (10, $28.00, 11/3/2013, 157, 11111);INSERT INTO SalesVALUES (11, $40.00, 10/28/2013, 289, 22222);INSERT INTO SalesVALUES (12, $6.00, 11/10/2013, 865, 33333);INSERT INTO SalesVALUES (13, $20.00, 11/11/2013, 865, 33333);INSERT INTO DVDVALUES (‘DVDd’, $100.00, 5, 223);INSERT INTO VideoVALUES (‘SVHS’, $32.00, 4, 132 );INSERT INTO MoviesVALUES (‘DVDplus’, $100.00, 5, 213);INSERT INTO VideoVALUES (‘VHS’, $60.00, 10, 145);i was told to forget about the shopping cart.Fri, 22 Mar 2013 15:42:57 GMTyasser.akhanRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxMaybe I will look at this tomorrow.Fri, 22 Mar 2013 15:42:37 GMTLynn PettisRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxPlease provide:1. DDL in the form of CREATE TABLE statements to create your sample tables2. DML in the form of INSERT INTO statements to populate the sample tables3. The expected results of your SELECT statements based on that sample data4. Most importantly: what you have tried so far.Fri, 22 Mar 2013 14:53:27 GMTOrlando ColamatteoRE: Need help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxSelect statements usually come from tables. The pictures don't do much.Fri, 22 Mar 2013 14:43:04 GMTSean LangeNeed help with Select statement!!http://www.sqlservercentral.com/Forums/Topic1434511-1292-1.aspxfinal project is due tonight at midnight and i cant finish up these select statements a. Find all those customers who have not purchased anything from Niles Video Inc.b. Get the total number of DVDs and video tapes sold per genre.c. Get the average number of DVDs per purchase.heres the er model [IMG]http://i45.tinypic.com/34qx021.png[/IMG]followed by the relational[IMG]http://i47.tinypic.com/154gjzn.png[/IMG]please let me know if my er model or relational model is wrongFri, 22 Mar 2013 14:22:51 GMTyasser.akhan