GETTING STARTED WITH SQL E-Book

Rémy Lentzner

• Herausgeber: Remylent
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2023

Seitenzahl: 73

Beschreibung

SQL (Structured Query Language) is a query language for relational database management. In this book, you will discover the basics of its organization and its characteristics. Using the PhpMyAdmin environment, you will learn how to create a database, tables, columns, constraints, keys (primary and foreign), joins and many other specificities. 

SQL has a small set of statements and enables you to select records from one or more tables using the universal SELECT command.  

Other expressions like UPDATE, INSERT or DELETE will help you to update, insert or delete records. You will also study several functions that manipulate text, dates and numbers.

Thanks to the exercises in this book, you will understand how a small set of statements can perform accurate actions and enhance your skills.

Enjoy your reading!


ABOUT THE AUTHOR

Rémy Lentzner has been an IT trainer since 1985. Specialized in mastering office automation tools, he supports companies in the professional training of their employees. Self-taught, he has several computer books to his credit.

Leseprobe

REMY LENTZNER

Getting started with SQL

French original title : Bien débuter avec SQL

EDITIONS REMYLENT, Paris, 1ère édition, 2023

R.C.S. 399 397 892 Paris

25 rue de la Tour d’Auvergne - 75009 Paris

[email protected]

www.REMYLENT.FR

ISBN EPUB : 978-2-38518-037-9

The Intellectual Property Code prohibits copies or reproductions intended for collective use. Any representation or reproduction in whole or in part by any means whatsoever, without the consent of the author or his successors in title or cause, is unlawful and constitutes an infringement, pursuant to articles L.335-2 and following of Intellectual Property Code.

This book is dedicated to Anna and Tama

I could not have written it without their support, advice, encouragements and proofreading.

Graphic illustration : Anna LENTZNER

In the same collection

Improve your PivotTables with Excel

Upgrading your skills with Excel

Improve your skills with Google Sheets

Programming macros with Google Sheets

Getting started with HTML

Getting started with JavaScript

Getting started with PHP & MySQL

Google Docs

Google Slides

Google Gmail

Macros & VBA with Excel

Getting started with WordPress

Getting started with Programming

Getting started with Numbers

Getting started with Pages

Getting started with Keynotes

Upgrading your skills with Word

Upgrading your skills with PowerPoint

Upgrading your skills with Outlook

Getting started with OpenOffice Calc

Getting started with OpenOffice Writer

Getting started with OpenOffice Impress

Getting started with OpenOffice Base

Macros & Basic with OpenOffice Calc

Getting started with Adobe Acrobat Pro

Getting started with Sparkle

INTRODUCTION

Numerous business people use spreadsheets to enter numbers and formulas. These numerical data are placed in cells within a group of columns and rows. When it comes to manipulating values, a spreadsheet is the ideal tool. But the amount of stored information will be limited by the maximum number of rows allowed. For instance, Excel Mac version 16 offers you 1,048,676 rows and moreover 10,000 columns. This may seem like a lot, but if you have to manage millions of parts like ship or spacecraft manufacturers, one spreadsheet won't be enough.

Applications that enable you to store huge amounts of information are called databases. These are computer tools that manage information stored in many tables and linked together by relationships. In this way, data can be pooled, retrieved, queried or added to. The overall system is designed to secure the information. For instance, a bank manages all its customers by assigning them with an individual user number and a password. Each customer can consult his account, withdraw funds, make transfers and perform many other operations. Thanks to the Internet network from his phone, banking operations can be carried out at home, at the office or while travelling. Each operation is considered as a transaction that can succeed or fail. To manage these countless transactions, the computer application must be able to provide an extremely high degree of security. This is the purpose of a database that links numerous pieces of information and is called a RDBMS or Relational Database Management System.

There are several of them in the market such as Oracle, Sybase, SQL server, MySQL, PostgreSQL and many others.The common language used by all these information management applications is SQL (Structured Query Language). It has evolved over time with the development of operating systems.

SQL is platform independent and has a small instruction set. In 1970, the professor and mathematician Edgar Frank Codd (1924-2003) was working as a researcher for the IBM company at the San Jose laboratory. He wrote a theoretical paper (A relational model of data for large shared data banks) published in the Association for Computing Machinery journal. The article laid the foundation for a simple programming language in English that could handle data stored on any computer platform.

A few years later, IBM created a prototype for a relational database called System/R with SEQUEL or Structured English Query Language, which was later renamed SQL. It is the Oracle Corp. that produced the first marketable version.

SQL is a standardized language that has evolved over time: SQL86, SQL89, SQL92, SQL1999, SQL2003, SQL2006, SQL2008, SQL2011 and SQL2016. It has adapted to Internet applications that manage huge volumes of information.

The SQL language is based on relational algebra and is broken down into different subsets: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language ) and TCL (Transaction Control Language).

The DDL or Data Definition Language groups together the commands that allow the creation, modification or deletion of table structures, indexes, views, etc.

A table is the physical information support and the index is a feature that accelerates searches and permits relations between tables. The view enables the extraction of data in a flexible and efficient way.

The DML or Data Manipulation Language contains the commands that are used to select and manipulate the data contained in the database (SELECT, INSERT, DELETE, UPDATE, etc).

The DCL or Data Control Language contains the orders that manage the security of data access, such as GRANT or REVOKE.

The TCL or Transaction Control Language groups the commands that manage the validation (or not) of the transactions, such as COMMIT, or ROLLBACK.

It would be tedious to describe all the SQL commands one after the other. In my opinion, it is more interesting to discover the most used commands by doing progressive exercises.

In this book, you will practice with the free MySQL language accessible to everyone and thanks to the PhpMyAdmin database management environment, you will be able to manipulate data. I have chosen the free local MAMP server which you can download on PC or Mac.

The book is divided into 6 chapters.

Chapter 1 describes how to install the local MAMP server, the PhpMyAdmin relational database management feature and how to create a database with its different objects.

Chapter 2 shows how to create a table, enter data, search for information, and query it using advanced criteria or groupings. You will learn how to use the SELECT command, that enables you to select information from one or more tables. With the UPDATE command, you will perform updates and calculations. You will learn how to create views, stored procedures and triggers.

Chapter 3 explains the techniques that allow you to define relationships between tables. You will be able to define foreign keys and constraints.

Chapter 4 focuses on functions that manipulate text, dates, or numbers and can be used in SELECT queries. You will learn about the aggregate functions that are used with the GROUP BY clause.

Chapter 5 looks at more advanced SQL queries. You will discover how to integrate a SELECT command into another SELECT command. You will learn how to sort in substrings. You will manipulate several set queries with the UNION operator as well as the left and right outer joins between a primary key and a foreign key from two tables.

A glossary summarizes the principal key words of the language

I hope that this book will interest you and enhance your SQL knowledge.

Do not hesitate to contact me at [email protected] if you have any comments or questions about this book.

Enjoy your reading.

The author

TABLE OF CONTENTS

Chapter 1 The working environment

1.1 Installing the local MAMP server

1.2 Starting the phpMyAdmin tool

1.3 Creating a database

Chapter 2 Tables

2.1 The table, the fields and the primary key

2.1.1 Creating a table

2.1.2 Workshop

2.1.3 Adding information to a table

2.1.4 Finding information in a table

2.1.5 Multiple operators

2.1.6 Miscellaneous operators

2.2 The SELECT command

2.2.1 Examples with SELECT

2.2.2 Sorting a column with Order By

2.2.3 Grouping with GROUP By

2.2.4 The HAVING clause

2.3 Managing records

2.3.1 Deleting a record

2.3.2 Copying a record

2.3.3 The UPDATE command

2.4 Calculating

2.4.1 Grouping and totaling

2.4.2 Statistics

2.4.3 Calculating with a condition WHERE

2.4.4 Calculating with UPDATE

2.4.5 Duplicating a table

2.5 The routines

2.6 The views

2.7 The triggers

2.8 The console

2.9 The privileges

Chapter 3 Relationships

3.1 Defining relationships

3.1.1 The BOOKS table

3.1.2 The SHOPS table

3.1.3 Displaying the schema

3.1.4 Creating a foreign key

3.1.5 Creating a relationship

3.1.6 The table constraints

3.1.7 The UPDATE or DELETE constraints

3.2 Multi-table queries

3.2.1 The query window

3.2.2 Performing a query by example

Chapter 4 Functions

4.1 The text functions

4.2 The date functions

4.2.1 Creating a table with dates

4.2.2 Calculations with dates

A) Displaying the number of days between two dates

B) Displaying the number of months between two dates

C) Displaying the number of years between two dates

D) Adding 13 days to a date

E) Subtracting 10 days from a date

F) Subtracting 3 months from a date

G) Adding years to a date

4.2.3 The current date and time

4.2.4 Extracting parts of the date

4.2.5 Day of month, week and year

4.3 Functions about numbers

4.4 The aggregate functions

Chapter 5 Advanced SQL Queries

5.1 A CASE expression in a SELECT