SQL queries - 1

Return the a hundred books starting from 25th

SELECT book_title FROM books LIMIT 25, 100

The first number in LIMIT is the offset, the second is the number.

I am retrieving 10 results at a time, but I like to know how many rows there’re total.

SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10;

SELECT FOUND_ROWS();

The second query will tell you how many results there’re total, so you can display a phrase "Found 13,450,600 results, displaying 1-10".

Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.

Select all teams that won either 2, 4, 6 or 8 games

SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8 );

Select all the users, whose phone number is null

SELECT user_name FROM users WHERE ISNULL(phonenumber);

SELECT user_name FROM users WHERE phonenumber IS NULL;

If the value in the column is repeatable, Find out the unique values ?

Use DISTINCT in the query, such as

SELECT DISTINCT user_firstname FROM users;

SELECT COUNT (DISTINCT user_firstname) FROM users;

Find out which auto increment was assigned on the last insert

SELECT LAST_INSERT_ID()

will return the last value assigned by the auto_increment function.

Note that you don’t have to specify the table name.

Delete the most recently posted question in the table questions

DELETE FROM questions ORDER BY timestamp LIMIT 1

See all indexes defined for a table

SHOW INDEX FROM questions

Change a column from VARCHAR(10) to VARCHAR(50)

ALTER TABLE questions

CHANGE content

content VARCHAR(50)

Delete a column

ALTER TABLE answers

DROP answer_id

Find out all databases starting with ‘tech’ to which I have access to

SHOW DATABASES LIKE ‘tech%’;

Get a portion of a string while fetching data

SELECT SUBSTR(title, 1, 10) FROM questions;

Convert a string to UTF-8 while fetching data

SELECT (question USING utf8);

What do % and _ mean inside LIKE statement ?

% corresponds to 0 or more characters while  _ is exactly one character.

Get the month from a timestamp while fetching data

SELECT MONTH(add_date) FROM questions;

Changing date format of a timestamp while fetching data

SELECT DATE_FORMAT(add_date, ‘%Y-%m-%d’) FROM questions;

A similar TIME_FORMAT function deals with time.

Add three minutes to a date while fetching data

SELECT ADDDATE(add_date, INTERVAL 3 MINUTE) FROM questions;

Find 2nd highest salary in random database salary of employer

SELECT salary FROM employee ORDER BY salary DESC LIMIT 1,1

Find three highest salary

SELECT salary FROM employee ORDER BY salary DESC LIMIT 3