0

What I want to do is to select the orders that are hapened in 2001 ONLY which is "order3" here whats the SQL statment shlould be written HOW I can catch the year part from this full date ???

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
FirstName varchar(255),
LastName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT 'unknown'
);

CREATE TABLE Orders
(
O_Id int NOT NULL,
P_Id int,
OrderName varchar(255),
OrderDate varchar (255),
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

insert into persons values ('1','sara','lala','street10','de');
insert into persons values ('2','toto','no','street10','nl');
insert into persons values ('3','momo','yes','street10','ch');
insert into persons values ('4','bobo','yea','street10','sy');
insert into persons values ('5','malk','tata','street10','sy');
insert into persons (P_Id,FirstName,LastName,Address) values ('6','yuo','zoo','street10');


insert into Orders values ('1','2','Order1','29/5/200');
insert into Orders values ('2','1','Order2','7/6/2000');
insert into Orders values ('3','2','Order3','9/9/2001');
insert into Orders values ('4','1','Order4','22/3/2002');

Edited by xXghostXx

2
Contributors
17
Replies
19
Views
5 Years
Discussion Span
Last Post by pritaeas
0

Are you still mainly referring to Oracle? If so, please post your next questions in that forum, or state it clearly in your question.

Can you please explain why you are using a 'varchar' column for a date (instead of 'date')?

0

@pritaeas yea man but I am using SQL not oracle... and im using varchar instead of date coz the website u provide to me http://www.sqlfiddle.com wont accept date as keyword

I had tried this but its not working and I am getting errors

OrderDate datetime DEFAULT GETDATE(),

I am still beginner so please be Patient and help me to get through my problems no matter how its stupid please man :(

Edited by xXghostXx

0

Even in SqlFiddle you'll have to select a server. If you set it to Oracle then you can use DATE as column type.

It should work in MySQL or MSSQL. So if you have a problem, please post your full query, the server you are using (because the are SQL differences between them) and the error message ("it's not working" is just too vague). Without those it's just difficult to answer correctly.

Edited by pritaeas

0

I tried to select oracle, MySQl 5.5.28 server with the above code
the error message is
"Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getdate(),
ordercost int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Pers' at line 6: "

and I am looking for the query that list orders that happened in 2001 ONLY how ??

Edited by xXghostXx

0

DEFAULT GETDATE() works for MSSQL, but not for MySQL.

I am looking for the query that list orders that happened in 2001 ONLY how

In MySQL you can use:

SELECT * FROM orders WHERE YEAR(OrderDate) = 2001

Edited by pritaeas

0

uh nice nice and how about the time data type sqlfiddle wont accept it as keyword

OrderDate time,

and about the query u had typed as future if are these two query right ??

SELECT * FROM orders WHERE month(OrderDate) = 3
SELECT * FROM orders WHERE day(OrderDate) = 18

and is that works if i want to select the month and year ?

    SELECT * FROM orders WHERE month year(OrderDate) = 3 2001

if there is something to read about this idea ???

Edited by xXghostXx

0
  1. You forgot to say which server you are trying. It should wprk on MySQL.

  2. Yes, they are right.

  3. Should be this:

    SELECT * FROM orders WHERE MONTH(OrderDate) = 3 AND YEAR(OrderDate) = 2001

0

first of all thanks alot for being Patient
I am working on that site sqlfiddle.com I tried "date","time", and "datetime" keywords on oracle,and MySQl 5.5.28 server

thanks thanks thanks :))

anything you could advice me to read about nested SQL statment ??
i tried this site but it questions is not really understoodble from me
http://www.sqlzoo.net/wiki/AdventureWorks

and i found it not very useful
thanks

Edited by xXghostXx

0

Hard to think of something. Just make some tables and experiment.

This is what I had to do last week, perhaps you can research that. I have the following table:

galleries_photos
----------------
id int (pk autoinc)
gallery_id int (fk)
order int
filename varchar(255)
title varchar(255)

Sample data
order by gallery_id, order
-----------
1,1,1,dsc001.jpg,yellow
2,1,2,dsc002.jpg,brown
3,1,3,dsc003.jpg,red
4,2,1,dsc006.jpg,null
5,2,2,dsc005.jpg,null
6,2,3,dsc004.jpg,null

What I want is an update query that changes the order of gallery_id 1. The new order should be sorted by title. So after you run the query your output must be:

New data
order by gallery_id, order
-----------
2,1,1,dsc002.jpg,brown
3,1,2,dsc003.jpg,red
1,1,3,dsc001.jpg,yellow
4,2,1,dsc006.jpg,null
5,2,2,dsc005.jpg,null
6,2,3,dsc004.jpg,null
0

ummmm I create the table but i did not clearlly understand what the query you want the only changes I saw is the order of title column... here is what i have done and the query as I understand it even if its not working since its unique constraint which cant be changed

create table mytable
(
id int not null primary key,
gallery_id int not null,
orders int,
filename varchar(255),
title varchar(255) default 'null',  
FOREIGN KEY (gallery_id) REFERENCES mytable(id)
); 

insert into mytable (id,gallery_id,orders,filename,title) values ('1','1','1','dsc001.jpg','yellow');
insert into mytable (id,gallery_id,orders,filename,title) values ('2','1','2','dsc002.jpg','brown');
insert into mytable (id,gallery_id,orders,filename,title) values ('3','1','3','dsc003.jpg','red');
insert into mytable (id,gallery_id,orders,filename) values ('4','2','1','dsc006.jpg');
insert into mytable (id,gallery_id,orders,filename) values ('5','2','2','dsc005.jpg');
insert into mytable (id,gallery_id,orders,filename) values ('6','2','3','dsc004.jpg');

update mytable set id=1 where filename='dsc002.jpg';
update mytable set id=2 where filename='dsc003.jpg';
update mytable set id=3 where filename='dsc001.jpg';

Edited by xXghostXx

0

I did not mean 3 queries. I meant one single update query (updating the order, not the id).

Why did you rename the column order to orders?

Edited by pritaeas

0

uh ill work on it and I used orders insted of order becouse order is keyword

0

hahahahhaha i catch it and get through it ;)
and as I told u ill work on it and let know

0

I meant you can use this:

create table mytable
(
    id int not null primary key,
    gallery_id int not null,
    `order` int,
    filename varchar(255),
    title varchar(255)
); 
0

thanks man and to be honest im not able to write the query u ask me for

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.