July 5, 2012

DB script

Posted in Uncategorized at 7:23 am by mrrohit007

USE mst; DESC CUSTOMERS;

CREATE TABLE CUSTOMERS( CUSTOMERID INT NOT NULL, NAME VARCHAR(200), AGE INT, ADDRESS VARCHAR(200), PRIMARY KEY(CUSTOMERID) );

CREATE TABLE CUSTOMERSTRACK( CUSTOMERTRACKID INT NOT NULL, NAME VARCHAR(200), AGE INT, ADDRESS VARCHAR(200), PRIMARY KEY(CUSTOMERTRACKID) );

CREATE TRIGGER `Customers_Track_Insert` AFTER INSERT ON `customers` FOR EACH ROW BEGIN insert into CUSTOMERSTRACK(CUSTOMERTRACKID,NAME,AGE,ADDRESS,CUSTOMERID)  values((select uuid()),NEW.NAME,NEW.AGE,NEW.ADDRESS,NEW.CUSTOMERID) END

GRANT ALL PRIVILEGES ON mst.* TO ‘root’@’localhost’ IDENTIFIED BY ‘root’; GRANT ALL PRIVILEGES ON mst.* TO ‘root’@’%’ WITH GRANT OPTION;

DROP TRIGGER Customers_Track_Insert

ALTER TABLE CUSTOMERSTRACK add CUSTOMERID INT NOT NULL

CREATE TABLE CUSTOMERS1( CUSTOMERID INT NOT NULL, NAME VARCHAR(200), AGE INT, ADDRESS VARCHAR(200), PRIMARY KEY(CUSTOMERID) );

TRUNCATE table CUSTOMERS1

INSERT INTO CUSTOMERS1 VALUES (1, ‘Ramesh’, 32, ‘Ahmedabad’ );

DROP table CUSTOMERS1

UPDATE CUSTOMERS SET NAME=’Ramu’ where NAME=’RAmesh’;

DELETE FROM CUSTOMERS where CUSTOMERID=2;

SELECT * FROM CUSTOMERS WHERE NAME LIKE ‘k%’

SELECT * FROM CUSTOMERS WHERE NAME LIKE ‘%k’

SELECT * FROM CUSTOMERS WHERE NAME LIKE ‘_a%’

SELECT * FROM CUSTOMERS WHERE NAME LIKE ‘R__u’

SELECT * FROM CUSTOMERS GROUP BY AGE ORDER BY AGE DESC

SELECT DISTINCT AGE FROM CUSTOMERS

SELECT * FROM CUSTOMERS WHERE AGE BETWEEN 22 AND 30

CREATE UNIQUE INDEX index_1 ON CUSTOMERS(CUSTOMERID,NAME);

ALTER TABLE CUSTOMERS DROP INDEX index_1

ALTER TABLE customer1 RENAME TO customers

ALTER TABLE customers add SALARY DECIMAL(18,2)

ALTER TABLE customers modify SALARY INT

ALTER TABLE customers drop SALARY

Select * from customers where salary is null

Select * from customers where age <=27

Select * from customers where age in (20,30,22)

Select * from customers where age between 20 and 30

SELECT AGE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS WHERE AGE > 25);

SELECT AGE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUSTOMERS WHERE CUSTOMERID > 3);

SELECT AGE FROM CUSTOMERS WHERE EXISTS (SELECT AGE FROM CUSTOMERS WHERE AGE < 20);

SELECT * FROM CUSTOMERS WHERE AGE > ALL (SELECT AGE FROM CUSTOMERS WHERE SALARY > 2000);

SELECT * FROM CUSTOMERS WHERE AGE >= ALL (SELECT AGE FROM CUSTOMERS);

SELECT * FROM CUSTOMERS WHERE AGE > ANY (SELECT AGE FROM CUSTOMERS WHERE SALARY > 2000);

SELECT * FROM CUSTOMERS WHERE AGE > ANY (SELECT AGE FROM CUSTOMERS);

CREATE TABLE CUSTOMER_COPY AS( SELECT NAME,AGE FROM CUSTOMERS )

drop table CUSTOMER_COPY

SELECT * FROM CUSTOMERS where NAME LIKE ‘%a%’ GROUP BY CUSTOMERID HAVING AGE>25

ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL(18,2) DEFAULT 1000.00

Alter TABLE CUSTOMERS ALTER COLUMN SALARY SET DEFAULT 1000.00

ALTER TABLE CUSTOMERS ALTER COLUMN SALARY DROP DEFAULT

ALTER TABLE CUSTOMERS ADD CONSTRAINT unqConstr UNIQUE(NAME,ADDRESS)

ALTER TABLE CUSTOMERS DROP CONSTRAINT unqConstr

INSERT INTO CUSTOMERS(CUSTOMERID,NAME,AGE,ADDRESS) VALUES (7, ‘test’, 33, ‘Ahmedabad’ );

INSERT INTO CUSTOMERS(CUSTOMERID,NAME,AGE,ADDRESS) VALUES (8, ‘test’, 33, ‘Ahmedabad’ );

ALTER TABLE CUSTOMERS DROP PRIMARY KEY

ALTER TABLE CUSTOMERS ADD CONSTRAINT PK PRIMARY KEY(CUSTOMERID)

ALTER TABLE CUSTOMERSTRACK ADD CONSTRAINT FK FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMERS(CUSTOMERID)

ALTER TABLE CUSTOMERS ADD CONSTRAINT chk CHECK(AGE>=20)

INSERT INTO CUSTOMERS(CUSTOMERID,NAME,AGE,ADDRESS) VALUES (9, ‘test1’, 17, ‘Ahmedabad’ );

DROP TABLE CUSTOMERSTRACK

CREATE TABLE CUSTOMERSTRACK( CUSTOMERTRACKID INT, NAME VARCHAR(255), AGE INT(3), CUSTOMERID INT, CONSTRAINT PK PRIMARY KEY(CUSTOMERTRACKID), CONSTRAINT FK FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMERS(CUSTOMERID) );

INSERT INTO CUSTOMERSTRACK values(11,’Ram’,24,1)

INSERT INTO CUSTOMERSTRACK values(12,’SAM’,22,3)

INSERT INTO CUSTOMERSTRACK values(13,’John’,22,3)

INSERT INTO CUSTOMERSTRACK values(14,’Rob’,23,4)

SELECT CUSTOMERS.CUSTOMERID,CUSTOMERS.NAME FROM CUSTOMERS INNER JOIN CUSTOMERSTRACK ON CUSTOMERS.CUSTOMERID=CUSTOMERSTRACK.CUSTOMERID

SELECT CUSTOMERS.CUSTOMERID,CUSTOMERS.NAME FROM CUSTOMERS LEFT JOIN CUSTOMERSTRACK ON CUSTOMERS.CUSTOMERID=CUSTOMERSTRACK.CUSTOMERID

SELECT CUSTOMERS.CUSTOMERID,CUSTOMERS.NAME FROM CUSTOMERS RIGHT JOIN CUSTOMERSTRACK ON CUSTOMERS.CUSTOMERID=CUSTOMERSTRACK.CUSTOMERID

SELECT CUSTOMERS.CUSTOMERID,CUSTOMERS.NAME,CUSTOMERSTRACK.CUSTOMERTRACKID  FROM CUSTOMERS FULL JOIN CUSTOMERSTRACK ON CUSTOMERS.CUSTOMERID=CUSTOMERSTRACK.CUSTOMERID ORDER BY CUSTOMERS.AGE

SELECT AGE,NAME FROM CUSTOMERS UNION SELECT AGE,NAME FROM CUSTOMERSTRACK

CREATE TABLE CUSTOMERS_BACKUP( CUSTOMERID INT NOT NULL, NAME VARCHAR(200), AGE INT, ADDRESS VARCHAR(200), PRIMARY KEY(CUSTOMERID) );

SELECT AGE,NAME INTO CUSTOMERS_BACKUP  FROM CUSTOMERS

ALTER TABLE CUSTOMERS ADD CONSTRAINT chk CHECK(AGE>20)

INSERT INTO CUSTOMERS values(10,’JAck’,10,’Delhi’,2100.00)

INSERT INTO CUSTOMERS values(20,’Sean’,45,’Delhi’,2100.00)

CREATE VIEW CUSTOMER_VIEW AS SELECT NAME FROM CUSTOMERS WHERE AGE>30

SELECT * FROM CUSTOMER_VIEW

UPDATE CUSTOMER_VIEW SET NAME=’Rex’ where NAME=’Sean’;

SELECT NOW(),CURDATE(),CURTIME() SELECT 1 AS BLAH

SELECT UCASE(NAME) as NAME FROM CUSTOMERS

SELECT C1.NAME,C2.AGE,C1.CUSTOMERID FROM CUSTOMERS C1,CUSTOMERS C2

SELECT AGE,NAME FROM CUSTOMERS UNION ALL SELECT AGE,NAME FROM CUSTOMERSTRACK

SELECT AGE,NAME FROM CUSTOMERS INTERSECT SELECT AGE,NAME FROM CUSTOMERSTRACK

DELETE FROM CUSTOMERS WHERE NAME=’Rex’ ROLLBACK;

SELECT * FROM CUSTOMERS

select LAST_INSERT_ID();

SELECT MAX(AGE) FROM CUSTOMERS WHERE AGE NOT IN(SELECT MAX(AGE) FROM CUSTOMERS)

SELECT * FROM CUSTOMERS C1 WHERE 4 in (SELECT COUNT(*) FROM CUSTOMERS C2 WHERE C2.AGE<=C1.AGE)

SELECT COUNT(DISTINCT AGE) AS AGE FROM CUSTOMERS

SELECT * FROM CUSTOMERS ORDER BY NAME DESC

SELECT * FROM CUSTOMERS GROUP BY NAME DESC HAVING SUM(AGE) > 30

SELECT C1.AGE,C2.NAME FROM CUSTOMERS C1,CUSTOMERS C2 WHERE C2.CUSTOMERID = C1.CUSTOMERID

SELECT CHAR(65) SELECT SUBSTRING(‘abx’,1,2) SELECT UPPER(‘abx’) SELECT UCASE(‘abx’)

SELECT * FROM CUSTOMERS LIMIT 0,3

USE STUDENT CREATE TABLE STUDENTDTL( STUDENTID INT NOT NULL, STUDENTNAME VARCHAR(255), CONSTRAINT PK PRIMARY KEY(STUDENTID) );

CREATE TRIGGER STUDENT_DTL_TRACK AFTER UPDATE ON STUDENTDTL FOR EACH ROW BEGIN END

USE MST CREATE TRIGGER CUSTOMERS_TRACK AFTER INSERT ON CUSTOMERS FOR EACH ROW BEGIN INSERT INTO CUSTOMERSTRACK(CUSTOMERTRACKID,NAME,AGE,CUSTOMERID) VALUES((SELECT UUID()),NEW.NAME,NEW.AGE,NEW.CUSTOMERID); END;

DROP TRIGGER CUSTOMERS_TRACK

DELIMITER $$;

DROP TRIGGER `mst`.`CUSTOMERS_TRACK`$$

create trigger `CUSTOMERS_TRACK` AFTER INSERT on `customers` for each row BEGIN INSERT INTO CUSTOMERSTRACK(CUSTOMERTRACKID,NAME,AGE,CUSTOMERID) VALUES((SELECT UUID()),NEW.NAME,NEW.AGE,NEW.CUSTOMERID); END; $$

DELIMITER ;$$

INSERT INTO CUSTOMERS VALUES(22,’Tom’,29,’delhi’,3200.00)

Advertisements

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

%d bloggers like this: