chapter

menu1. Introducing SQL (Structured Query Language)

websql

1 Introducing SQL (Structured Query Language)

Learning Objectives

:

  1. Describe why the ability to work with databases is valuable across professions.
  2. Describe the DBMS and features used throughout this book.
  3. Identify the different types of sidebars used in this book and what each one highlights.

Databases and applications that rely on databases are used in all industries. They are used in small, medium, and large companies. Business people use databases to help with all types of business tasks from marketing to operations and accounting to human resources management. Developers and engineers use databases in the development of new products and computer applications. Legal, medical, and educational professionals use database systems. Truthfully, it would be difficult to find a profession that does not use database systems. Those who learn how to effectively interact with databases will have a significant advantage over those who do not.

Structured query language (SQL) is a popular and powerful tool for interacting with databases. It is relatively easy to learn. Additionally, it can be used with all of the major database systems.

The lessons in this book are designed to teach SQL. They are targeted to beginners and assume no prior database knowledge. The set of lessons is also a comprehensive reference. It covers basic, intermediate and advanced SQL topics. They describe some principles of database design that will complement your understanding of SQL.

1.1 The DBMS Used in This Book

The lessons in this set are written specifically for PostgreSQL, one of the most popular open-source relational database systems. Standard SQL is largely the same across different Database Management Systems (DBMS), but many features — functions, data types, and some syntax — vary from one DBMS to the next. Rather than try to cover every possible DBMS, this book focuses on PostgreSQL exclusively, so that every example can be shown precisely as you'd actually type it, with no "your DBMS may vary" caveats.

1.1.1 Features of This Book

There are a number of examples, practice problem sets, and novel features in the lessons that will help you effectively learn SQL. These features include:

Execute Live Queries in the Book

You will find a number of example SQL statements presented in each lesson. Each example is presented in a special query box that will allow you to execute the query syntax against a live database. An example of a query window is below. The window contains a visual depiction of the database to be queried and the syntax to be executed. When you click on the "Play" button, the query results will be displayed. Give it a try.

SELECT name
FROM  museum;

This example queries a database with five related tables: artist, work, subject, museum, and museum_hours. In the live version of this book, the query box above renders as an interactive editor with a visual schema diagram of those tables and a "Play" button that runs the query and shows the results. That interactive component still needs to be built for this platform — for now the query is shown as a plain code block.

Use Artificial Intelligence to Create SQL Syntax

You can configure the book to teach you how to effectively use artificial intelligence (AI) tools such as ChatGPT to help you to write SQL syntax. The examples in each lesson present an example chat session with an AI tool that ends with the tool generating SQL code. The lessons also describe how to evaluate SQL statements that are AI generated to determine if they will work for your needs. An example AI chat session used is below.

Write a SQL statement to list the names of museumssend

To list the names of museums from a database, you would need to know the specific table and column where the museum names are stored. Assuming there is a table called "Museums" with a column named "Name," you can use the following SQL statement:

SELECT Name
FROM Museums;

This query selects the "Name" column from the "Museums" table and retrieves all the museum names. Replace "Museums" with the actual table name in your database to execute the query successfully.

Receive Feedback on your Query Writing

You may choose to purchase example problem sets that will give you extra practice. These problem sets are scored by an automated grading system which provides immediate feedback on your queries. If you are using these lessons for a formal class, your scores and feedback can be forwarded to your instructor.

Get Additional Help and Tips on Query Writing

Scattered throughout the lessons, you will find information spotlighted in sidebars. There are six different types of sidebars that highlight different types of information. An example of each type of sidebar is below with a description of the type of information that will be highlighted in each.

Used to highlight interesting information that is related to the content presented in the lesson.

Used to highlight a suggested best practice or easier way to do things.

Used to highlight a glossary term and its definition.

Used to highlight common mistakes or issues encountered by SQL programmers.

Used to highlight information that was presented in a prior lesson that is relevant to the current topic.