Saturday, November 3, 2018

MySql Quaries


You can create and populate the example table with these statements:

CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45), (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

After issuing the statements, the table should have the following contents:

SELECT * FROM shop; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+

The Maximum Value of a Column:

SELECT MAX(article) AS article FROM shop; +---------+ | article | +---------+ | 4 | +---------+

Maximum of Column per Group:

Task: Find the highest price per article.

SELECT article, MAX(price) AS price FROM shop GROUP BY article; +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 |
+---------+-------+

The Rows Holding the Group-wise Maximum of a Certain Column

Task: For each article, find the dealer or dealers with the most expensive price.
This problem can be solved with a subquery like this one:
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article); +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---------+--------+-------+



Related Posts:

  • How can insert the data in mysql using php? step 1:- Firstly create a database name & secondly create a table in MySQL. step 2:-After that create a table structure eg. Column Name1 , Coulmn Name2 , Coulmn NameN... <?php mysql_connect("localhost","root"… Read More
  • How can fetch the records in MySQL using php? Create  a  form.php  for the inserting data & view.php for the fetching data in the  Data base Fetching Records  in the database using Mysql_fetch_array ( ) Function. Select Command use for the … Read More
  • MySql Quaries You can create and populate the example table with these statements: CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, p… Read More
  • PHP Error Types - And their Differences Notices : It will be shown in below condition like if we will try to access a variable which is not defined yet. it will not stop script execution. Warning : It will be shown while using include(), it will not a… Read More
  • Delete data using core php ? <?php if(isset($_REQUEST['del_id'])) {    $del=$_REQUEST['del_id'];     $de="delete from user where uid='$del'";     $ex=mysql_query($del);     header("lo… Read More

0 comments:

Post a Comment