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);