Home

Topic: Hello World

Learning objective

1. Core concepts

2. Getting started with pgAdmin

3. Hello world in SQL

4. SQL style guide

5. Debugging in SQL

---

Core concepts

1. What is data?

Data are individual facts, statistics, or information that are collected for reference or analysis. In its raw form, data itself is useless. However the knowledge underlying the data is of unmeasurable value.

2. What is database?

A database is a (usually large) shared collection of logically related, persistent data used by a set of application systems. The database name is usually a topical description of that data.

3. What is DBMS?

DBMS is short for database management system. It is a set of computer programs that enables users to define, create, and maintain a database and make its information available appropriately. Today, DBMS serves as an interface for users to retrieve information stored in databases. Most DBMS are relational, hence they are also referred to as RDBMS.

4. What is SQL?

SQL, short for Structural Query Language, is a RDBMS programming language that defines relational constructs (such as schemas and tables) and provide data manipulation capabilities (e.g. data control). SQL by itself cannot create stand-alone programs because it can only be used inside RDBMSs. It is a declarative type of language that leaves the details of implementation (how to do it) to the RDBMS itself.

5. What is a data model?

A model is a representation of reality that retains only selected details. A data model is a notation for describing and organizing data. This includes structure of data, operation on the data, and constraints on the data. There are many different types of data models, for example:

• hierarchical (tree-oriented) model

• network (graph-oriented) model

• relational model (e.g. an entity-relational (ER) model)

• object-oriented model

• semi-structured model

6. Basics of a relational model

1. Relational model represents data in a multitude of two-dimensional tables.

2. Each table consists of similar information, stored in columns and rows.

3. Each column of a table are named to describe the data contained in the column (e.g. attributes).

4. The rows of each table contains the values (e.g. tuples).

5. The schema contains the meta information of the table, the columns, and the values contained in the rows.

For more theoretical content, checkout Database Systems The Complete Book (second edition) by Hector Garcia-Molina Jeffrey D. Ullman Jennifer Widom. The content above is referencing the textbook and also adopted with permission from Professors Lei Xie, Di He, and Alvin Lam’s courses.

---

Getting started with pgAdmin

First, make sure that you have completed the set up guide: Getting started with pgAdmin.

Exercise:

Grab a partner and find the answers to the following questions:

1. How many databases are there on this server?

2. In the each of the databases below, how many tables are there in each database?
   • employees
   • dvdrental
   • menagerie
   • shakespeare

3. How many different types of login / group roles are there total in this server? (Hint: look under Login/Group Roles)

4. For the user instructor, what kind of permissions (e.g. Privileges) does this user have?

---

Hello world in SQL

CRUD and SQL SELECT

CRUD stands for create, read, update, and delete, which are the four major operations implemented by databases.

Mapping to SQL statements, this means:

CRUD	SQL
Create	INSERT
Read	SELECT
Update	UPDATE
Delete	DELETE

We will be spending our time on Read only, which means SELECT SQL statements.

Writing our first SQL SELECT statement:

SELECT *
FROM employees
;

The basics:

SELECT *
FROM <tablename>
;

• This SQL query is an example of the most basic building block of a SQL SELECT statement for extracting and reading data.

• Every SQL query for pulling data must have a SELECT ... followed by a FROM ....

• The FROM clause tells us the location of the data, aka the table name.

• The SELECT clause tells us which columns we need from that table.

• The asterisk (*) (we verbally refer to it as “star”) is a wildcard that is a shorthand for returning all the columns in the table.

To execute a SQL SELECT query in pgAdmin:

1. Write the SQL inside an editor (Query Tool file), and
2. Execute by either pressing the Play button or F5.
3. If there are multiple SELECT queries in the editor, highlight the specific query to run, or else pgAdmin will only run the last query.

---

Exercise

1. Fill in the SELECT statement below to re-create the SQL query which will return all rows and all columns in the departments table.

   SELECT ???
   FROM ???
   ;
   

2. Save your SQL script as a hello_world.sql file on your local machine.
3. Save the data output in a file called departments.csv on your local machine.

---

SQL style guide

A quick overview of the SQL style guide.

---

SQL Debugging

Some common SQL syntax errors:

1. Did you spell the table name correctly?

2. Did you include a semi colon at the end of the SQL statement? (optional in pgAdmin, necessary in psql)

3. Did you spell the SQL reserve keywords correctly? (a good text editor can help you with this)

4. Did you preserve the order of SELECT .... FROM ...?

Order of debugging

One interesting thing to note is that SQL debugger will only return the error message for the first error it encounters. If you fix that error, then and only then, the second error in the same SQL statement will be printed. Although we write the SQL statement in the order of SELECT ... followed by FROM ..., SQL actually evaluates the FROM ... clause first.

Exercise:

Debug the following SQL SELECT statement which queries a table that conatins employee titles inside the employees database and fix all the syntax errors until it correctly runs:

SLECT *
FROM title

---

Questions?