Programmer's Notebook

In this case, I intend to introduce a complicated SQL query problem: row column transform. In the last chapter, about the fundamental SQL syntax , I used a MySQL sample database named sakila . In this tutorial, I still use it. transform rows to columns Task #1: Take table actor inside sakila for example, How to query the most popular last_names? let limit the condition to the last_names repeat at least more than 3 times. USE sakila ; SELECT last_name , COUNT ( first_NAME ) as size FROM actor GROUP BY last_name having size > 3; We got the result: + -----------+------+ | last_name | size | + -----------+------+ | KILMER | 5 | | NOLTE | 4 | | TEMPLE | 4 | + -----------+------+ Task #2: Convert above result from row to column format. + --------+-------+--------+ | KILMER | NOLTE | TEMPLE | + --------+-------+--------+ | 5 | 4 | 4 | + --------+-------+--------+ Use CASE WHEN an...

This is my learning notes about the SQL (based on  MySql ). As far as I known, Mysql was acquired by Oracle , whose mainly product is also the enterprise highly professional database, and the founder of the Mysql worried the future of the MySQL, then he started another open sourced database named MariaDB . I consider the Mysql and MariaDB the same thing, they even have the same command line tool name. And with no hesitate, I use MariaDB as the alternative to Mysql in the tutorial. Database Creation The operation of Database include Change, Create, Alter, Drop database. USE db_name ; CREATE DATABASE IF NOT EXIST db_name CHARACTER SET = utf - 8 ; ALTER DATABASE db_name CHARACTER SET = utf - 8 ; DROP DATABASE IF EXIST db_name ; Table Creation The basic composition of a database is its tables, table schema definition is the beginning stage in the database design. There is two import aspect in define a table schema, the column data types and the constric...