CIM 1101: Module 1

Module 1 of CIM 1101 (Information Management)

SQL Query for creating multiple tables

SQL Query for creating multiple tables

create table customer(CID int(3) not null, auto_increment, CName varchar(20), Address varchar(20), primary key(CID));\n \ncreate table orders (OID int not null, CID int references customer(CID), primary key(OID));

SQL Query for Inserting records in both tables

insert into customer values (111, 'Michael', 'Caloocan'), (222, 'Lync','Quezon City'), (333,'Tbag','Manila');\n \ninsert into orders values (400, 567),(700,123),(900,222);

SQL Query for Any or All

select CName from customer where CName <> all (Select CName from customer);\n \nselect CName from customer where CName <> all (Select CName from customer);\n \nselect CName from customer where CName = all(select CName from customer);\n \nselect CName from customer where CName = all(select CName from customer where CName ='Tbag');\n \nselect CName from customer where CName = any(select CName from customer);

SQL Query for Having

select * from customer having Address = 'Caloocan';\n \nselect * from customer having Address in (select Address from customer);\n \nselect * from customer having Address in (select Address from customer where Address = 'Caloocan');

SQL Query for Intersect / IN

select * from customer where customer.CID in (select orders.CID from orders);\n \nselect * from customer where customer.CID >200 and customer.CID in (select orders.CID from orders where orders.OID >300);

This is a database object associated with a table that is automatically activated in when an action such as insert, delete and update occurs in the associated table.

MySQL trigger

Row-level trigger

This is automatically invoked activated for each row that is inserted, updated, or deleted in the associated table.

Statement-level trigger

This is executed once for every transaction in the table no matter how many rows are affected by such action.

SQL Query for trigger

DELIMITER //\nCREATE TRIGGER tr_log_inserts AFTER INSERT ON customer FOR EACH ROW\nBEGIN\nINSERT INTO audit_logs(module, remarks, datetimestamp) values ('Customers',concat('INSERT row for CID ',NEW.CID),now());\nEND//\nDELIMITER ;\n \n \n \n \nDELIMITER //\nCREATE TRIGGER tr_log_updates AFTER UPDATE ON customer FOR EACH ROW\nBEGIN\nINSERT INTO audit_logs(module, remarks, datetimestamp) values ('Customers',concat('UPDATE Name=', OLD.CName, '=>', New.CName,' for CID ',OLD.CID),now());\nEND//\nDELIMITER ;

SQL Query for Stored Procedure

DELIMITER //\nCREATE PROCEDURE sp_InsertCustomer(paramCID INT(2), paramName VARCHAR(20), paramAddress VARCHAR(20))\nBEGIN\nINSERT INTO customer(CID, CName, Address) VALUES(paramCID, paramName, paramAddress);\nEND//\nDELIMITER ;\n \n \n \nDELIMITER //\nCREATE PROCEDURE sp_AllCustomers()\nBEGIN\nselect * from customer;\nEND//\nDELIMITER ;