Need help with Select statement!!
-
final 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
followed by the relational
please let me know if my er model or relational model is wrong
-
Select statements usually come from tables. The pictures don't do much.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Please provide:
1. DDL in the form of CREATE TABLE statements to create your sample tables
2. DML in the form of INSERT INTO statements to populate the sample tables
3. The expected results of your SELECT statements based on that sample data
4. Most importantly: what you have tried so far.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Maybe I will look at this tomorrow.
-
Oh srry about that, its a rather long project so i tried to spare some goring detail but here.
CREATE DATABASE Webstore
CREATE 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 ,
TitleVarchar2(32),
GenreVarchar2(32) not null,
YearNumber(4)
Media_TypeChar(1) not null,
Cost money not null,
CONSTRAINT Medt primary key (barcode)
);
CREATE TABLE DVD
(
Format char(10) not null,
Costmoney not null,
Quantity number(2) not null,
CONSTRAINT Medt
Foreign Key(barcode) references Movies(barcode),
);
CREATE TABLE Videos
(
Format char(10) not null,
Costmoney not null,
Quantity number(2) not null,
CONSTRAINT Medt
Foreign Key(barcode) references Movies(barcode),
);
Insert Statement
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.
-
That looks like Oracle code :ermm:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
You;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 show us what you have tried 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
meh dont worry about it, it was sort of a last ditch effort. thanks for looking at it though
-
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....?
-
kevaburg (3/25/2013)
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....?Curious, to whom was this comment addressed?
-
Sorry Lynn.....it was aimed at the original poster....
-
kevaburg (3/25/2013)
Sorry Lynn.....it was aimed at the original poster....Hard to tell just from the comment. It is apparent that this was some sort of college project.
-
It 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 what they have tried so far before I provide anything in the way of a working solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply