MySQL/Language

Browsing the databases

List databases

SHOW DATABASES;

List tables

USE `database`;
SHOW TABLES;

SHOW TABLES FROM `database`;

List fields

DESCRIBE `table`;
DESCRIBE `database`.`table`;

EXPLAIN `table`; --synonym
SHOW FIELDS FROM `table`; -- synonym
SHOW COLUMNS FROM `table`; --synonym

information_schema

information_schema is a virtual database provided by MySQL 5 and later, that contains metadata about the server and the databases.

Specifying table names

In this book, we will quote the MySQL identifiers (tables names, fields, etc.) using backquotes (`).

Most often, this is optional. However, this allows better error messages from MySQL. For example, this error is not very helpful:

mysql> SELECT user_id, group_id FROM user,group LIMIT 1;
ERROR 1064 (42000): You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version
 for the right syntax to use near 'group LIMIT 1' at line 1

But this one is better:

mysql> SELECT user_id, group_id FROM `user`,`group` LIMIT 1;
ERROR 1146 (42S02): Table 'savannah.group' doesn't exist

Ok, it was just a missing s:

mysql>  SELECT user_id, group_id FROM `user`,`groups` LIMIT 1;
+---------+----------+
| user_id | group_id |
+---------+----------+
|     100 |        2 |
+---------+----------+
1 row in set (0.02 sec)

However, this is not a portable syntax. The SQL standard recommends the user of a double quote ("). If you want to write portable SQL quote, do not quote the identifiers. But is there something like portable SQL, even remotely? ;)

Template:Mergeto

Definitions: what are DDL, DML and DQL?

• DDL (Data Definition Language) refers to the CREATE, ALTER and DROP TABLE statements
• DML (Data Manipulation Language) refers to the INSERT, UPDATE and DELETE statements
• DQL (Data Query Language) refers to the SELECT statements
• DCL (Data Control Language) refers to the GRANT and REVOKE statements

User Variables

• The ability to set variables in a statement with the := assignment operator:
• For e.g. (@total) to calculate the total in an example, you have to have the total column first because it must be calculated before the individual percentage calculations
• User variables are set for the duration of the thread.
• In the vast majority of cases you'd use a programming language to do this sort of thing.
• Mysql variables can be useful when working on the Mysql command line.
• If no records are returned, the user variable will not be set for that statement.
• A user variable set in the field list cannot be used as a condition.

select @test := 2;
select @test + 1

• The value of a variable is set with the SET statement or in a SELECT statement with :=

set @startdate='some_start_date', @enddate='some_end_date'

SELECT @toremember:=count(*) FROM membros;

select @numzero := count(*) from table1 where field=0; 
select @numdistinct := count(distinct field) from table1 where field <> 0 ; 
select @numzero @numdistinct;

Alias

A select expression may be given an alias using AS. The alias is used as the expression's column name and can be used with order by or having clauses. For e.g.

SELECT 
    CONCAT(last_name,' ', first_name) AS full_name 
FROM
    mytable 
ORDER BY
    full_name

A table name can have a shorter name for reference using AS. You can omit the AS word and still use aliasing. For e.g.

SELECT
    COUNT(B.Booking_ID), U.User_Location 
FROM
    Users U
LEFT OUTER JOIN
    Bookings AS B
ON 
    U.User_ID    = B.Rep_ID AND
    B.Project_ID = '10'
GROUP BY
    (U.User_Location)

Aliasing plays a crucial role while you are using self joins. For e.g. people table has been referred to as p and c aliases!

SELECT
    p.name                                   AS parent,
    c.name                                   AS child,
    MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) AS minage
FROM
    people AS p 
LEFT JOIN
    people AS c 
ON
    p.name=c.parent WHERE c.name IS NOT NULL
GROUP BY
    parent HAVING minage > 50 ORDER BY p.dob;

Queries

SELECT

select syntax is as follows:

SELECT *
FROM table
WHERE condition

IN and NOT IN

SELECT id
FROM stats
WHERE position IN ('Manager', 'Staff')

SELECT ownerid, 'is in both orders & antiques'
FROM orders, antiques WHERE ownerid = buyerid
UNION
SELECT buyerid, 'is in antiques only'
FROM antiques WHERE buyerid NOT IN (SELECT ownerid FROM orders)

EXISTS and ALL

(Compatible: Mysql 4+)

SELECT ownerfirstname, ownerlastname
FROM owner 
WHERE EXISTS (SELECT * FROM antiques WHERE item = 'chair')

SELECT buyerid, item 
FROM antiques
WHERE price = ALL (SELECT price FROM antiques)

UNION and UNION All

(Compatible: Mysql 4+)

Following query will return all the records from both tables.

SELECT * FROM english 
UNION ALL
SELECT * FROM hindi

UNION is the same as UNION DISTINCT.
If you type only UNION, then it is considered that you are asking for distinct records. If you want all records, you have to use UNION ALL.

SELECT word FROM word_table WHERE id = 1
UNION
SELECT word FROM word_table WHERE id = 2

(SELECT magazine FROM pages)
UNION DISTINCT
(SELECT magazine FROM pdflog)
ORDER BY magazine

(SELECT ID_ENTRY FROM table WHERE ID_AGE = 1)
UNION DISTINCT 
(SELECT ID_ENTRY FROM table WHERE ID_AGE=2)

Joins

The Most important aspect of SQL is its relational features. You can query, compare and calculate two different tables having entirely different structure. Joins and subselects are the two methods to join tables. Both methods of joining tables should give the same results. The natural join is faster on most SQL platforms.

In the following example a student is trying to learn what the numbers are called in hindi.

CREATE TABLE english (Tag int, Inenglish varchar(255));
CREATE TABLE hindi (Tag int, Inhindi varchar(255));

INSERT INTO english (Tag, Inenglish) VALUES (1, 'One');
INSERT INTO english (Tag, Inenglish) VALUES (2, 'Two');
INSERT INTO english (Tag, Inenglish) VALUES (3, 'Three');

INSERT INTO hindi (Tag, Inhindi) VALUES (2, 'Do');
INSERT INTO hindi (Tag, Inhindi) VALUES (3, 'Teen');
INSERT INTO hindi (Tag, Inhindi) VALUES (4, 'Char');

	select * from english		select * from hindi
Tag	Inenglish	Tag	Inhindi
1	One	2	Do
2	Two	3	Teen
3	Three	4	Char

Cartesian join

A Cartesian join is when you join every row of one table to every row of another table.

SELECT * FROM english, hindi

Tag	Inenglish	Tag	Inhindi
1	One	2	Do
2	Two	2	Do
3	Three	2	Do
1	One	3	Teen
2	Two	3	Teen
3	Three	3	Teen
1	One	4	Char
2	Two	4	Char
3	Three	4	Char

Inner Join

SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english, hindi
WHERE english.Tag = hindi.Tag

Tag	Inenglish	Inhindi
2	Two	Do
3	Three	Teen

You can also write the same query as

SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english INNER JOIN hindi
ON english.Tag = hindi.Tag

Natural Joins using "using" (Compatible: MySQL 4+; but changed in MySQL 5) The following statement using "USING" method will display the same results.

SELECT hindi.tag, hindi.Inhindi, english.Inenglish
FROM hindi NATURAL JOIN english
USING (Tag)

Outer Joins

Tag	Inenglish	Tag	Inhindi
1	One
2	Two	2	Do
3	Three	3	Teen
		4	Char

LEFT JOIN / LEFT OUTER JOIN

The syntax is as follows:

SELECT field1, field2 FROM table1 LEFT JOIN table2 ON field1=field2

SELECT e.Inenglish as English, e.Tag, '--no row--' as Hindi
FROM english AS e LEFT JOIN hindi AS h
ON e.Tag=h.Tag 
WHERE h.Inhindi IS NULL

English  tag   Hindi
One      1     --no row-

Right Outer Join

SELECT '--no row--' AS English, h.tag, h.Inhindi AS Hindi
FROM english AS e RIGHT JOIN hindi AS h
ON e.Tag=h.Tag
WHERE e.Inenglish IS NULL

English tag Hindi --no row-- 4 Char

• Make sure that you have the same name and same data type in both tables.
• The keywords LEFT and RIGHT are not absolute, they only operate within the context of the given statement: we can reverse the order of the tables and reverse the keywords, and the result would be the same.
• If the type of join is not specified as inner or outer then it will be executed as an INNER JOIN.

Full Outer Join

As for v5.1, MySQL does not provide FULL OUTER JOIN. You may emulate this using a series of UNIONed SELECT statements.

Multiple joins

It is possible to join more than just two tables:

SELECT ... FROM a JOIN (b JOIN c on b.id=c.id) ON a.id=b.id

Here is an example from Savane:

mysql> SELECT group_type.type_id, group_type.name, COUNT(people_job.job_id) AS count 
       FROM group_type
        JOIN (groups JOIN people_job ON groups.group_id = people_job.group_id) 
        ON group_type.type_id = groups.type
       GROUP BY type_id ORDER BY type_id
+---------+--------------------------------------+-------+
| type_id | name                                 | count |
+---------+--------------------------------------+-------+
|       1 | Official GNU software                |   148 |
|       2 | non-GNU software and documentation   |   268 |
|       3 | www.gnu.org portion                  |     4 |
|       6 | www.gnu.org translation team         |     5 |
+---------+--------------------------------------+-------+
4 rows in set (0.02 sec)

Subqueries

(Compatible: Mysql 4.1 and later...Bold text)

• SQL subqueries let you use the results of one query as part of another query.
• Subqueries are often natural ways of writing a statement.
• Let you break a query into pieces and assemble it.
• Allow some queries that otherwise can't be constructed. Without using a subquery, you have to do it in two steps.
• Subqueries always appear as part of the WHERE (or HAVING) clause.
• Only one field can be in the subquery SELECT. It means Subquery can only produce a single column of data as its result.
• ORDER BY is not allowed; it would not make sense.
• Usually refer to name of a main table column in the subquery.
• This defines the current row of the main table for which the subquery is being run. This is called an outer reference.

For e.g. If RepOffice= OfficeNbr from Offices table, list the offices where the sales quota for the office exceeds the sum of individual salespersons' quotas

SELECT City FROM Offices WHERE Target > ???

??? is the sum of the quotas of the salespeople, i.e.

SELECT SUM(Quota)
FROM SalesReps 
WHERE RepOffice = OfficeNbr

We combine these to get

SELECT City FROM Offices 
WHERE Target > (SELECT SUM(Quota) FROM SalesReps 
WHERE RepOffice = OfficeNbr)

Display all customers with orders or credit limits > $50,000. Use the DISTINCT word to list the customer just once.

SELECT DISTINCT CustNbr 
FROM Customers, Orders 
WHERE CustNbr = Cust AND (CreditLimit>50000 OR Amt>50000);

Data manipulation

INSERT

The syntax is as follows:

Insert value1 into Column1, value2 into Column2, and value3 into Column3:

INSERT INTO TableName (Column1, Column2, Column3)
VALUES (value1, value2, value3)

Insert one record (values are inserted in the order that the columns appear in the database):

INSERT INTO TableName
VALUES (value1, value2, value3)

Insert two records:

INSERT INTO TableName
VALUES (value1, value2, value3), (value4, value5, value6)

INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00);
INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman');

You can also insert records 'selected' from other table.

INSERT INTO table1(field1, field2)
SELECT field1, field2
FROM table2

INSERT INTO World_Events SELECT * FROM National_Events

UPDATE

The syntax is:

UPDATE table SET field = newvalue WHERE criteria

Examples are:

UPDATE owner SET ownerfirstname = 'John'
  WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase');

UPDATE antiques SET price = 500.00 WHERE item = 'Chair';

UPDATE order SET discount=discount * 1.05

UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID
  SET tbl1.col1 = tbl1.col1 + 1
  WHERE tbl2.status='Active'

UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz')

UPDATE products_categories AS pc
  INNER JOIN products AS p ON pc.prod_id = p.id
  SET pc.prod_sequential_id = p.sequential_id

UPDATE table_name SET col_name =
  REPLACE(col_name, 'host.domain.com', 'host2.domain.com')

It is currently not possible to update a table while performing a subquery on the same table. For example, if I want to reset a password I forgot in SPIP:

mysql> UPDATE spip_auteurs SET pass =
 (SELECT pass FROM spip_auteurs WHERE login='paul') where login='admin';
ERROR 1093 (HY000): You can't specify target table 'spip_auteurs' for update in FROM clause

TODO: [1] describes a work-around that I couldn't make to work with MySQL 4.1. Currently the work-around is not use 2 subqueries, possibly with transactions.

REPLACE

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort.

Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that would cause duplicate-key violations.

DELETE

DELETE FROM table1
TRUNCATE table1

• If you don't use a WHERE clause with DELETE, all records will be deleted.
• It can be very slow in a large table.
• TRUNCATE will do it quickly by DROPping and reCREATE-ing the table.
• DELETE informs you how many rows have been removed, but TRUNCATE doesn't.

DELETE FROM antiques
  WHERE item = 'Ottoman'

DROP

DROP TABLE products

will completely delete the records as well as the columns.

Table manipulation

CREATE TABLE

Create table syntax is: Create table tablename (FieldName1 DataType,
FieldName2 DataType)

The rows returned by the "select" query can be saved as a new table. The datatype will be the same as the old table. For e.g. CREATE TABLE LearnHindi
select english.tag, english.Inenglish as english, hindi.Inhindi as hindi
FROM english, hindi
WHERE english.tag = hindi.tag

ALTER TABLE

alter table command can be used when you want to add / delete the columns or change the data type.

ALTER TABLE awards
ADD COLUMN AwardCode int(2)

ALTER TABLE awards
ALTER COLUMN AwardCode VARCHAR(2) NOT NULL

ALTER TABLE awards
DROP COLUMN AwardCode

Using NULL

Null is a special logical value in SQL. Most programming languages have 2 values of logic: True and False. SQL also has NULL which means Unknown. A NULL value can be set.

INSERT into Singer
       (F_Name, L_Name, Birth_place, Language) 
       values 
       ("", "Homer", NULL, "Greek"),
       ("", "Sting", NULL, "English"),
       ("Jonny", "Five", NULL, "Binary");

Do not quote the NULL. If you quote a Null then you name the person NULL. For some strange reason, NULLs do not show visually on windows XP in Varchar fields but they do in Fedora's version, so versions of mysql can give different outputs. Here we set the value of Sting and Homer's first name to a zero length string "", because we KNOW they have NO first name, but we KNOW we do not know the place they were born. To check for a NULLs use

SELECT * from Singer WHERE Birth_place IS NULL;
or
SELECT * from Singer WHERE Birth_place IS NOT NULL;

Remember, COUNT never counts NULLS.

select count(Birth_place) from Singer;
0
and sum(NULL) gives a NULL answer.

Dealing with NULL

The function 'COALESCE' can simplify working with null values. for example, to avoid showing null values by treating null as zero, you can type:

SELECT COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

In a date field, to treat NULL as the current date:

ORDER BY (COALESCE(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))

EXP(SUM(LOG(COALESCE(*the field you want to multiply*,1))) 

The coalesce() function is there to guard against trying to calculate the logarithm of a null value and may be optional depending on your circumstances.

SELECT t4.gene_name, COALESCE(g2d.score,0), 
COALESCE(dgp.score,0), COALESCE(pocus.score,0) 
FROM t4 
LEFT JOIN g2d ON t4.gene_name=g2d.gene_name 
LEFT JOIN dgp ON t4.gene_name=dgp.gene_name 
LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;

Use of IFNULL() in your SELECT statement is to make the NULL any value you wish.

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.

IFNULL() returns a numeric or string value, depending on the context in which it is used:

mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'

Null handling can be very counter intuitive and could cause problems if you have an incorrect function in a delete statement that returns null. For example the following query will delete all entries.

DELETE FROM my_table WHERE field > NULL (or function returning NULL)

If you want to have NULL values presented last when doing an ORDER BY, try this:

SELECT * FROM my_table ORDER BY ISNULL(field), field [ ASC | DESC ]

Reserved Words

Difficult Column Names, Like `DATE` -- use backtick. If using "date" as a column name, enclose it in backticks ` as follows:

CREATE TABLE IF NOT EXISTS stocks (
  pkey int NOT NULL auto_increment,
  `date` date,
  ticker varchar(5),
  open decimal (9,2),
  high decimal (9,2),
  low decimal (9,2),
  close decimal (9,2),
  volume int,
  timeEnter timestamp(14),
  PRIMARY KEY (pkey)
);

Data Types

varchar

VARCHAR is shorthand for CHARACTER VARYING. 'n' represents the maximum column length (upto 255 characters) char(n) is similar to varchar(n) with the only difference that char will occupy fixed length of space in the database whereas varchar will need the space to store the actual text. For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

text

A BLOB or TEXT column with a maximum length of 65,535 characters.

integer

Specifying an n value has no effect whatsoever. Regardless of a supplied value for n, maximum (unsigned) value stored is 429 crores. If you want to add negative numbers, add the "signed" keyword next to it.

decimal

decimal(n,m) decimal(4,2) means numbers upto 99.99 (and NOT 9999.99 as you may expect) can be saved. Four digits with the last 2 reserved for decimal.

Dates

Out of the three types DATETIME, DATE, and TIMESTAMP, the DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The DATETIME type is used when you need values that contain both date and time information. The difference between DATETIME and TIMESTAMP is that the TIMESTAMP range is limited to 1970-2037 (see below).

TIME can be used to only store the time of day (HH:MM:SS), without the date. It can also be used to represent a time interval (for example: -02:00:00 for "two hours in the past"). Range: '-838:59:59' => '838:59:59'.

YEAR can be used to store the year number only.

If you manipulate dates, you have to specify the actual date, not only the time - that is, MySQL will not automagically use today as the current date. On the contrary, MySQL will even interpret the HH:MM:SS time as a YY:MM:DD value, which will probably be invalid.

The following examples show the precise date range for Unix-based timestamps, which starts at the Unix Epoch and stops just before the first new year before the $2^{31}-1$ usual limit (2038).

mysql> SET time_zone = '+00:00'; -- GMT
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT FROM_UNIXTIME(-1);
+-------------------+
| FROM_UNIXTIME(-1) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(0); -- "Epoch"
+---------------------+
| FROM_UNIXTIME(0)    |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916799);
+---------------------------+
| FROM_UNIXTIME(2145916799) |
+---------------------------+
| 2037-12-31 23:59:59       |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916800);
+---------------------------+
| FROM_UNIXTIME(2145916800) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

set and enum

A SET datatype can hold any number of strings from a predefined list of strings specified during table creation. The SET datatype is similar to the ENUM datatype in that they both work with predefined sets of strings, but where the ENUM datatype restricts you to a single member of the set of predefined strings, the SET datatype allows you to store any of the values together, from none to all of them.

Import / export

Aside from mysqldump (cf. MySQL/Administration), you can also export raw data using:

SELECT ... FROM table INTO OUTFILE 'path'
LOAD DATA INFILE 'path' INTO TABLE table

Examples:

SELECT * FROM destinataire INTO OUTFILE '/tmp/test' WHERE id IN (41, 141, 260, 317, 735, 888, 1207, 2211);
# in another database/computer/etc.:
LOAD DATA INFILE '/tmp/test' INTO TABLE destinataire;

Beware that the MySQL daemon itself will write the file, not the user you run the MySQL client with. The file will be stored on the server, not on your host. Moreover, the server will need write access to the path you specify (usually, the server can _not_ write in your home directory, e.g.). Hence why we (unsecurely) used /tmp in the examples.

Functions

Date and time

SELECT * FROM mytable
 WHERE datetimecol >= (CURDATE() - INTERVAL 1 YEAR)  AND
 datetimecol < (CURDATE() - INTERVAL 1 YEAR) INTERVAL 1 DAY;

SELECT IF(DAYOFMONTH(CURDATE()) <= 15,
 DATE_FORMAT(CURDATE(), '%Y-%m-15'),
 DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15
FROM table;

SELECT YEAR('2002-05-10'), MONTH('2002-05-10'), DAYOFMONTH('2002-05-10')

SELECT PurchaseDate FROM table WHERE YEAR(PurchaseDate) <= YEAR(CURDATE())

SELECT columns FROM table
WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00'

SELECT * FROM t1
WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'

SELECT Start_time, End_time FROM Table
WHERE Start_time >= NOW() - INTERVAL 4 HOUR

SELECT NOW() + INTERVAL 60 SECOND

SELECT UNIX_TIMESTAMP('2007-05-01'); -- 1177970400
SELECT FROM_UNIXTIME(1177970400); -- 2007-05-01 00:00:00

Syntax

When you use DATE_FORMAT(), if I have a space character between DATE_FORMAT and the ( I get an error. Try writing it like: SELECT DATE_FORMAT( "20040601123456", '%Y-%m-%d' );

You can't use a value calculated in the SELECT clause as a constraint in the WHERE clause (its a chicken & egg problem); the WHERE clause is what determines the values in the SELECT clause. What you want is the HAVING clause which is applied *after* all matching rows have been found.

Exercises

Practicing SELECT

Table `list`

ID	Name	Surname	FlatHave	FlatWant
1	Shantanu	Oak	Goregaon
2	Shantanu	Oak	Andheri
3	Shantanu	Oak		Dadar
4	Ram	Joshi		Goregaon
5	Shyam	Sharma		Andheri
6	Ram	Naik	Sion
7	Samir	Shah	Parle
8	Ram	Joshi	Dadar
9	Shyam	Sharma	Dadar

Exercise I - Questions

• Who has a flat in "Goreagon" and who wants to buy one?
• Who has a flat in "Parle" and who wants to buy one?
• Where does "Shantanu Oak" own the flats and where does he want to buy one?
• How many entries have been recorded so far?
• How many flats are there for sale?
• What are the names of our clients?
• How many clients do we have?
• List the customers whose name start with "S"?
• Rearrange the list Alphabetically sorted.

Exercise I - Answers

• select * from list where FlatHave = "Goregaon" or FlatWant = "Goregaon"
• select * from list where FlatHave = "Parle" or FlatWant = "Parle"
• select * from list where Name = "Shantanu" and Surname = "Oak"
• select count(*) from list
• select count(FlatHave) from list where FlatHave is not null
• select distinct Name, Surname from list
• select count(distinct Name, surname) from list
• select * from list where Name like "S%"
• select Surname, Name, FlatHave, FlatWant from list order by Name

Table `grades`

ID	Name	Math	Physics	Literature
1	John	68	37	54
2	Jim	96	89	92
3	Bill	65	12	57
4	Jeri	69	25	82

Exercise II - Questions

• A list of all students who scored over 90 on his or her math paper?
• A list of all students who scored more than 85 in all subjects?
• Declare Results: Print the results of all students with result column.
• Find out total marks of all the students.
• What are the average marks of the class for each subject?
• What are the minimum marks in Math?
• What are the maximum marks in Math?
• Who got the highest marks in Math?

Exercise II - Answers

Note: many problems have more than one correct solution.

• SELECT * FROM grades WHERE math > 90
• SELECT name FROM grades WHERE math > 85 AND physics > 85 AND literature > 85
• SELECT *, IF( (math <= 35 OR physics <= 35 OR literature <= 35), 'fail', 'pass') AS result FROM grades ORDER BY result DESC
• SELECT name, math+physics+literature FROM grades
• SELECT AVG(math), AVG(physics), AVG(literature) FROM grades
• SELECT MIN(math) FROM grades
• SELECT MAX(math) FROM grades
• SELECT * FROM students ORDER BY math DESC LIMIT 1

Examples

Finding Duplicates

SELECT Vendor, ID, Count(1) as dupes
FROM table_name
GROUP BY Vendor, ID HAVING Count(1) >1

SELECT txt, COUNT(*)
FROM dupes
GROUP BY txt HAVING COUNT(*) > 1;

SELECT id, COUNT( id ) AS cnt, 
FROM myTable
GROUP BY id HAVING cnt > 1

Remove duplicate entries.

Assume the following table and data.

CREATE TABLE IF NOT EXISTS dupTest
(pkey int(11) NOT NULL auto_increment,
a int, b int, c int, timeEnter timestamp(14),
PRIMARY KEY (pkey));

insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);

Note, the first two rows contains duplicates in columns a and b. It contains other duplicates; but, leaves the other duplicates alone.

ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);