MySQL practical Tutorials part 6- SQL min, max, sum, average functions. Difference between char and varchar in SQL. Decimal data type in SQL.

Below are the some practical queries based on Mysql important SQL min, max, sum, average functions. Difference between char and varchar in SQL. Decimal data type in SQL.

=======================================================================================================================

Using Min and Max with Group By

SELECT author_fname, 

       author_lname, 

       Min(released_year) 

FROM   books 

GROUP  BY author_lname, 

          author_fname;

 

SELECT

  author_fname,

  author_lname,

  Max(pages)

FROM books

GROUP BY author_lname,

         author_fname;

 

SELECT

  CONCAT(author_fname, ' ', author_lname) AS author,

  MAX(pages) AS 'longest book'

FROM books

GROUP BY author_lname,

         author_fname;

=======================================================================================================================

The Sum Function

SELECT SUM(pages)

FROM books;

 

SELECT SUM(released_year) FROM books;

 

SELECT author_fname,

       author_lname,

       Sum(pages)

FROM books

GROUP BY

    author_lname,

    author_fname;

 

SELECT author_fname,

       author_lname,

       Sum(released_year)

FROM books

GROUP BY

    author_lname,

    author_fname;

=======================================================================================================================

The Avg Function

SELECT AVG(released_year) 

FROM books;

 

SELECT AVG(pages) 

FROM books;

 

SELECT AVG(stock_quantity) 

FROM books 

GROUP BY released_year;

 

SELECT released_year, AVG(stock_quantity) 

FROM books 

GROUP BY released_year;

 

SELECT author_fname, author_lname, AVG(pages) FROM books

GROUP BY author_lname, author_fname;

=======================================================================================================================

Aggregate Functions Challenges Solution

SELECT COUNT(*) FROM books;

 

SELECT COUNT(*) FROM books GROUP BY released_year;

 

SELECT released_year, COUNT(*) FROM books GROUP BY released_year;

 

SELECT Sum(stock_quantity) FROM BOOKS;

 

SELECT AVG(released_year) FROM books GROUP BY author_lname, author_fname;

 

SELECT author_fname, author_lname, AVG(released_year) FROM books GROUP BY author_lname, author_fname;

 

SELECT CONCAT(author_fname, ' ', author_lname) FROM books

WHERE pages = (SELECT Max(pages) FROM books);

 

SELECT CONCAT(author_fname, ' ', author_lname) FROM books

ORDER BY pages DESC LIMIT 1;

 

SELECT pages, CONCAT(author_fname, ' ', author_lname) FROM books

ORDER BY pages DESC;

 

SELECT released_year AS year,

    COUNT(*) AS '# of books',

    AVG(pages) AS 'avg pages'

FROM books

    GROUP BY released_year;

=======================================================================================================================

Note about CHAR and VARCHAR

Hi Everyone,

There was a small discrepancy in the last lecture regarding the truncation of VARCHAR inputs, please see here for the correction.

thanks,

CHAR and VARCHAR

CREATE TABLE dogs (name CHAR(5), breed VARCHAR(10));

 

INSERT INTO dogs (name, breed) VALUES ('bob', 'beagle');

 

INSERT INTO dogs (name, breed) VALUES ('robby', 'corgi');

 

INSERT INTO dogs (name, breed) VALUES ('Princess Jane', 'Retriever');

 

SELECT * FROM dogs;

 

INSERT INTO dogs (name, breed) VALUES ('Princess Jane', 'Retrievesadfdsafdasfsafr');

 

SELECT * FROM dogs;

=======================================================================================================================

DECIMAL

CREATE TABLE items(price DECIMAL(5,2));

 

INSERT INTO items(price) VALUES(7);

 

INSERT INTO items(price) VALUES(7987654);

 

INSERT INTO items(price) VALUES(34.88);

 

INSERT INTO items(price) VALUES(298.9999);

 

INSERT INTO items(price) VALUES(1.9999);

 

SELECT * FROM items;

=======================================================================================================================