How did you learn SQL and why

Sql tutorial: Learn Sql and practice the corresponding commands interactively using the Sql-interactive-learn

On the following pages you will find a Sql tutorial that uses a simple database to illustrate the central principles of the two parts of SQL, the Data Manipulation Language (DML) and the Data Definition Language (DDL). The sample database, consisting of three small tables (Article.txt, Representative.txt, Sales.txt) can either be downloaded directly and imported into your own database system or you can use the Sql-interactive learning from the freeware tools. This already includes an Access database with the three sample tables. An Access installation is not necessary for use. The .NET1.1 runtime environment is sufficient; the database is accessed via the program. If you want to learn Sql online: As of now (November 13th, 2017) there is the possibility under Sql Online Tool - https://sql.server-daten.de/de/abfragen.html to type in Sql queries and view the result. The three tables Article / Representative / Sales are online. Primary and foreign key columns are usually named with table name + ID within server data. If you want to use the example on an Ms-SqlServer or another comparable database system, you can use the create-sql-interaktiv.sql script. You can run this script with OSQL.exe, you can find an example in the file. A note on the structure of this tutorial: Before you deal too much with the theory - first work out the practical examples for the SELECT command (DML-I). Sql works with sets. If you are familiar with evaluations based on a clear, intentionally small example, you will learn the underlying relational theory in passing.

Selection and aggregation of existing data (Data Manipulation Language - DML-I)

  • SELECT - the basic term for A.Selecting data: As soon as you select data, be it for direct display or for further processing, use the SELECT command. In the simplest version, all or some columns are selected from a table.
  • With W.HERE select a few rows: If only a few rows of a table are not to be returned, reduce the number of rows output with a WHERE clause.
  • With JCombine OIN tables: If the rules for normalizing the data have been adhered to, the rows belonging to a data record are often distributed over several tables. With a JOIN clause, two tables can be joined together to form a new, virtual table. Cells from different tables can be multiplied with one another or missing entries can be searched for in one of the tables.
  • With GROUP BY aggregate and evaluate data: All previous techniques select, but do not change the output lines. If you want to add values ​​of a column from several rows, determine the maximum in a column or receive sales per day and per article, use aggregate functions and the GROUP-BY clause.
  • Subqueries / Subqueries: Subqueries, usually called subqueries, are used to immediately use the result of a query as additional input for a further query. In this way, deeper structures can be determined, for example the question of which articles or representatives have made an above-average contribution to sales.

Basics for relational database systems and design of the storage structure

  • DatabasekBasic terms: In the area of ​​database development, fundamental distinctions are used: database versus database management system, the requirement for freedom from redundancy and the enforcement of data integrity, the difference between desktop and server-based DBMS.
  • OOrganization of the data: table, row and column: When developing a relational database, one single, large table can initially be assumed. Various problems such as redundancy and insert, update and delete anomalies can be observed on this.
  • Primary and foreign key, table B.Relationships: The aim of normalization is to break down the denormalized output table into small tables that are linked to one another. This is done with concepts such as keys, non-keys, primary and foreign keys as well as additional specifications for individual columns.
  • E.First normal form - atomic cells and data types: The first normal form requires atomic attributes and the swapping of groups. In order to be able to carry out special operations on the data later, data types are defined for columns.
  • ZWide normal form - functionally dependent attributes: The second normal form requires that all attributes in a table are functionally dependent on the primary key. If an attribute is already defined by a subset of the primary key, this is to be transferred to a separate table with the primary key subset.
  • D.ritte normal form - no transitive dependency: The third normal form is fulfilled if no attribute is transitively dependent, that there is no non-key attribute which already defines this attribute.
  • Sql as the language of the ve generation: As a programming language, Sql is characterized by a few peculiarities. The DBMS must provide interfaces for the exchange of Sql commands, the language can be embedded in a host language and is set-oriented. The demarcation to other programming languages ​​can be outlined by the five language generations.
  • The HHistorical development of SQL: Some notes on the historical development of Sql, starting with Codd's work on the predecessor SEQUEL, the first implementations up to the current standards Sql-92 and Sql-99.

Entering, changing and deleting data (Data Manipulation Language - DML-II)

  • I.NSERT - Adding new rows to the table: The INSERT command inserts new data into existing tables. A line with VALUES can be specified directly as the source or the values ​​come from the result of a SELECT query.
  • With UUpdate PDATE existing rows: The UPDATE statement enables the updating of existing rows in a table. The rowset to be changed can be determined both with WHERE clauses and via a link with other tables; the new values ​​can also come from other tables.
  • DEL.ETE - delete lines: DELETE removes lines. As with update, the rows to be deleted can be restricted using WHERE or specified using a JOIN link using other tables.
Further pages on creating database objects (DDL) will follow. However, DLL commands cannot be used by users in the main project, so these topics are currently of secondary importance. The current texts are already available so that examples for Sql-interactive learning from the freeware tools exist and can be used. If you find this text interesting, feel free to include a link on your site. Please only link the HTML offer, the Xml version can only be used for readers with IE6. Copy the following html line into the source code of your file or adapt the display to your own needs: Sql tutorial

Contact form:

Write to me and we will build your new web database together!
© 2003-2021 Jürgen Auer, Berlin.