SQL Essential Training

I followed the “SQL Essential Training” course held by Bill Weinman on the Linkedin Learning platform. The course is very interesting and deals with various issues on relational database programming and the applications based on them. Knowing how to code and manage relational databases is an important skill in a variety of technology sectors.

The course is updated for SQL 2019, designed to understand the core language. The lessons are recorded on SQLiteStudio and include various exercises and verification quizzes. Basic SQL functions are addressed, such as creating tables, defining relationships, manipulating strings, numbers, time values and dates, using triggers and automations with the use of subselections and views.

CRUD and SQL applications

During the course we also deal with a real example of CRUD application as well as all the bases for their creation. CRUD operations are nothing more than the basic operations of a database, that is, Create Read Update Delete. The result is the opening of a transaction to the database and the execution of requests.

In particular, an application written in PHP integrated with a database is used that allows you to keep track of some music albums, their tracks and their duration, modifying and adding elements.

SELECT a.title AS album, a.artist, t.track_number AS seq, t.title, t.duration AS secs
  FROM album AS a
  JOIN track AS t
    ON t.album_id = a.id
  WHERE a.id IN (SELECT DISTINCT album_id FROM track WHERE duration <= 90)
  ORDER BY a.title, t.track_number
;

All files are accessible from the course especially for MacOS you need to start php from the terminal:

php -S 127.0.0.1:8000

Then copy the CRUD and db folders into the php root folder, typically /Users/username/. Then simply open your browser at http://localhost:8000/CRUD/crud.php.

Structured Query Language

SQL is a standardized language for databases based on the relational database management systems model designed for some basic operations:

• Create and modify database schemas
• Enter, modify and manage the stored data
• Query the stored data
• Create and manage data access and control tools

It is therefore a simple query language for database management.

For example:

SELECT DISTINCT surname, name, city
FROM user
WHERE years > = 18
ORDER BY surname