Write a sql statements for rollback commit and save points

CREATE TABLE mytr (url_id int, url_addr varchar(100), host_date date);

INSERT INTO mytr VALUES (1, 'www.google.com', '2012-10-01');

savepoint a;

INSERT INTO mytr VALUES (2, 'www.microsoft.com', '2012-10-12');

savepoint b;

INSERT INTO mytr VALUES (3, 'www.apple.com', '2012-10-17');

savepoint c;

INSERT INTO mytr VALUES (4, 'www.google.com', '2012-10-11');

savepoint d;

INSERT INTO mytr VALUES (5, 'www.cnn.com', '2012-10-21');

savepoint e;

INSERT INTO mytr VALUES (6, 'www.apple.com', '2012-10-30');

savepoint f;

rollback to c; //rollback upto savepoint c

commit; //whole transaction is committed and all savepoints are removed

Read more

Show all websites which were hosted in the first half of the month

CREATE TABLE mytr (url_id int, url_addr varchar(100), host_date date);

INSERT INTO mytr VALUES (1, 'www.google.com', '2012-10-01');

INSERT INTO mytr VALUES (2, 'www.microsoft.com', '2012-10-12');

INSERT INTO mytr VALUES (3, 'www.apple.com', '2012-10-17');

INSERT INTO mytr VALUES (4, 'www.google.com', '2012-10-11');

INSERT INTO mytr VALUES (5, 'www.cnn.com', '2012-10-21');

INSERT INTO mytr VALUES (6, 'www.apple.com', '2012-10-30');

SELECT * FROM mytr

WHERE host_date

BETWEEN '2012-10-01' AND '2012-10-15'

Read more

Display the ‘url_addr’, which start with g, c or m

CREATE TABLE mytb (url_id int, url_addr varchar(100));

INSERT INTO mytb VALUES (1, 'google.com');

INSERT INTO mytb VALUES (2, 'microsoft.com');

INSERT INTO mytb VALUES (3, 'apple.com');

INSERT INTO mytb VALUES (4, 'google.com');

INSERT INTO mytb VALUES (5, 'cnn.com');

INSERT INTO mytb VALUES (6, 'apple.com');

            SELECT * FROM mytb 

                        WHERE url_addr IS LIKE 

                        g% OR c% OR m%

Read more

Display the 3rd, 4th, 6th rows from table

CREATE TABLE mytb (url_id int, url_addr varchar(100));

INSERT INTO mytb VALUES (1, 'www.google.com');

INSERT INTO mytb VALUES (2, 'www.microsoft.com');

INSERT INTO mytb VALUES (3, 'www.apple.com');

INSERT INTO mytb VALUES (4, 'www.google.com');

INSERT INTO mytb VALUES (5, 'www.cnn.com');

INSERT INTO mytb VALUES (6, 'www.apple.com');

3^rdrow and 4th row

SELECT * from names LIMIT 2,2;

6^throw

SELECT * from names LIMIT 5,1;

Read more

Find the third highest and third lowest ‘url_id’

CREATE TABLE mytb (url_id int, url_addr varchar(100));

INSERT INTO mytb VALUES (1, 'www.google.com');

INSERT INTO mytb VALUES (2, 'www.microsoft.com');

INSERT INTO mytb VALUES (3, 'www.apple.com');

INSERT INTO mytb VALUES (4, 'www.google.com');

INSERT INTO mytb VALUES (5, 'www.cnn.com');

INSERT INTO mytb VALUES (6, 'www.apple.com');

Third hightest url_id

SELECT max(url_id) FROM myta WHERE url_id < (

SELECT max(url_id) FROM myta WHERE url_id < (

          SELECT max(url_id) FROM myta

)

)

Third lowest url_id

SELECT min(url_id) FROM myta WHERE url_id > (

SELECT min(url_id) FROM myta WHERE url_id > (

          SELECT min(url_id) FROM myta

)

)

Read more