SQL commands

select * from customer; select firstname,surname from customer; select firstname as eponumo,surname as onoma from customer; //bgazei ta diplotipa select distinct surname as onoma from customer; //poses fores exei auto pou psaxnoume select * from customer where surname ='ΙΩΑΝΝΟΥ'; select * from customer where zipcode ='10030'; select * from customer where yob > 1970; select * from customer where yob != 1970; // diaforo select * from customer where yob between 1970 and 1990; //metaksu select * from customer where yob in (1970,1972,1980); select * from customer where yob not in (1970,1962,1980); // na teleione se 2 kai na me se 100 select * from customer where zipcode like '100_2'; //sundiasmos apo sunthikes select * from customer where yob between 1970 and 2000 and yob not in(1970,1972,1980); select * from customer where ( yob between 1970 and 2000 and yob not in(1970,1972,1980)) or zipcode like '1003_' ; //ksekinane prota me from select from where select * from customer order by firstname,surname; select * from customer order by yob DESC; select * from customer order by yob ASC; select * from customer order by yob DESC LIMIT 0,2 ; select firstname , surname , checkin, checkout from customer, booking; //paradeigma edo exoume customer pou exoune kanei kratish kai exoune parei kodiko select firstname , surname , checkin, checkout from customer, booking where booking.customer=customer.code; /join enonoum select a.code,firstname , surname , checkin, checkout from customer as a join booking as b on b.customer=a.code; select a.code,firstname , surname , checkin, checkout from customer as a join booking as b on b.customer=a.code where a.code>=2 order by checkin Desc ; select a.code,firstname , surname , checkin, checkout from customer as a join booking as b on b.customer=a.code where a.code>=2 order by firstname Desc ; // --ASKISI--- start date end date arithmos domation kai timh ...order kata domation kai kata periodo select start_date , end_date , room, price from room_cost join period on room_cost.period = period.code order by room,period ; // --ASKISI--- start date end date arithmos domation kai timh ...order kata domation kai kata periodo kai na emfanisoume thea domatiou select start_date , end_date , room, price ,roomview from (room_cost join period on room_cost.period = period.code) join room on room_cost.room= room.code order by room,period ; // --ASKISI--- pelates pou den exoune kanei kratish- select a.code,firstname , surname , checkin, checkout from customer as a left join booking as b on b.customer=a.code where checkin is null order by firstname Desc ; olous tous pelates kai pou exoune kanei kartish kai den exoune kanei kratish kai pote select firstname , surname , checkin, checkout, room from (customer left join booking on customer.code=booking.customer) left join room on room.code=booking.room order by firstname Desc ; // kai ta dupmatia pou egine kartish kai auta pou den egine from (customer left join booking on customer.code=booking.customer) left join room on room.code=booking.room order by firstname Desc) union (select firstname , surname , checkin, checkout, room.code from (customer left join booking on customer.code=booking.customer) right join room on room.code=booking.room order by firstname Desc) ; //enosi apo 2 pinakes se ena eikonoko kai emfanisi tou eikonikou create view invalid_customers as select customer.code , firstname , surname from(customer left join booking on customer.code = booking.customer) left join room on room.code= booking.room where checkin is null order by firstname desc ; select * from invalid_customer; //enosi apo 2 pinakes se ena eikonoko kai emfanisi tou eikonikou kai diagrafi enos sthxeiou create or replace view invalid_customers as select customer.code , firstname , surname from(customer left join booking on customer.code = booking.customer) left join room on room.code= booking.room where checkin is null order by firstname desc ; delete from customer where code=7; select * from invalid_customers; //sunarthseis kai hmerominies // paradeigma na doume ti kratiseis exoume gia ton epomeno mina select date_add(curdate(), interval 1 month); // akoma pio sugkekrimena apo auta ta table ti egine ena mina prin h ena mina sto meloon apo simera select customer ,checkin from booking where checkin> date_add(curdate(), interval -1 month); // sugkekrimeno diasthma apo sugkekrimena table select firstname , surname ,checkin,checkout from booking join customer on booking.customer = customer.code where (checkin between curdate() and date_add(curdate(),interval 1 month)) or (checkout between curdate() and date_add(curdate(),interval 1 month)); // na mas episterpsei to eidos ths hmerominias pou theloume oxi me amrikaniko tropo select date_format(checkin ,'%d - %m - %y') from booking;