USEFUL QUERIES [ MYSQL ]

CREATE TABLE WITH PRIMARY KEY
———————————————————
create table persons (p_id int(3) not null , fname varchar(20), lname varchar(20),constraint pmkey_p_id primary key(p_id));

CREATE TABLE WITH PRIMARY KEY AND CHECK CONSTRAINTS
—————————————————————————————————-
create table persons (p_id int(3) not null check(p_id>0), fname varchar(20), lname varchar(20),constraint pmkey_p_id primary key(p_id));

CREATE TABLE WITH FOREIGN KEY AND PRIMARY KEY
—————————————————————————————-
create table orders (o_id int(3) not null check (o_id>0), order_no int(5),p_id int(3), constraint pmkey_o_id primary key (o_id),constraint frkey_p_id foreign key (o_id) references persons(p_id));

ADD COLUMN WITH CONSTRAINTS :
——————————————————–
alter table orders add amount int(7) not null check (amount>0);

MODIFY COLUMN :
——————————
alter table orders modify column order_no int(5) not null;

MODIFY COLUMN
—————————-
alter table orders modify column p_id int(3) not null;

DELETE FOREIGN KEY
————————————
 alter table orders drop foreign key frkey_p_id;

ADD CONSTRAINT
—————————
alter table orders ADD CONSTRAINT frkey_p_id foreign key  (p_id) references persons(p_id);

LEFT JOIN
—————-
select * from persons left join orders on orders.p_id=persons.p_id;

RIGHT JOIN
—————–
select * from persons right join orders on orders.p_id=persons.p_id order by persons.p_id;

GROUP BY TO CALCULATE TOTAL AMOUNT OF  EACH EMPOYEES FROM ORDERS TABLE
——————————————————————————————————————————————–
select fname,sum(amount) as total_cost from persons join orders on persons.p_id=orders.p_id group by orders.p_id;

INSERT VALUES :
—————————–
insert into orders values (1,101,2,1000);

Advertisements

About rajkirpal

i am a software engineer. and i have decent knowledge about java technologies like ANDROID, Servlet, Jsp, Struts, Hibernet, Spring, J2ME, Java Script, Ajax, JQuery, CSS. Further i am Preparing Easy understandable materials/documents upon the same java technologies and developing project in core java , advanced java and j2me , ANDROID as per the requirements Also designing the web sites and logos Thank You.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s