Home

Lecture 3: SELECT, LIMIT, aliasing columns with AS

Learning objective

1. The LIMIT clause
2. SELECT-ing one or more columns
3. Aliasing column names with AS
4. Creating new columns with uniform values

---

Warm up

• ssh into eniac, a lab machine, and the PostgreSQL server
• choose the employees database (e.g.\c employees)
• run the following:

SELECT *
FROM employees
;

---

The LIMIT clause

Unlike the SELECT clause and the FROM clause, the LIMIT clause is an optional part of a SQL statement that limits the number of rows (n) returned.

SELECT *
FROM employees
LIMIT 10
;

Each SQL clause and its function:

• SELECT: Selects the columns to return.
• FROM: Points to the table the SQL is querying from.
• LIMIT: (optional) Limits the number of rows returned (in the case above, n = 10)

Worth noting:

• LIMIT only accepts positive integers for number of rows to return (n).
• The scripting ordering matters! When SELECT, FROM, and LIMIT all appear in the same SQL statement, the order must be written in this order!

Further to note:

• The evaluation order goes like this: FROM -> SELECT -> LIMIT, which means that despite only printing out a subset of rows, a SQL query with LIMIT clause still uses up the same amount of memory as the same SQL query without the LIMIT clause.
• In some databases, LIMIT returns a random sample of n rows, so the output will not always be the same every time. Therefore, LIMIT should only be used as a way to preview data.
• There are many SQL dialects (e.g. PostgreSQL). In other variants, instead of LIMIT, TOP is used. For more information, read this Wikipedia article.

Exercises

On your own, please do the following:

1. Write a SQL query that returns all columns from the employees table, but only 100 rows.

2. Write a SQL query that returns all columns from the departments table, but only 100 rows. Why does the output not add up to 100 rows?

3. What’s wrong with the SQL statement below? Please debug until it runs correctly.

   SELECT *
   FROM employees
   LIMIT 1,000
   ;
   

---

SELECT-ing one or more columns

To return all columns from a table, exactly in the order they are stored:

SELECT *
FROM table_name
;

To return a single column from a table:

SELECT column_name
FROM table_name
;

To return multiple columns from a table, use commas to separate the columns:

SELECT
  column_name,
  column_name,
  column_name,
  ...
  column_name
FROM table_name
;

Worth noting:

1. The column order will appear exactly in the same order as listed in the SELECT clause.
2. Use commas to separate multiple column names and a new line per column specified. See the SQL style guide for more details.

Exercises

On your own:

1. Write the SQL query that returns only the column for storing first_name in the employees table.

2. Write the SQL query that returns two columns from the employees table: first_name and last_name.

3. Further add on to the query in question 2 by limiting the output to 20 rows.

---

Aliasing column names with AS

We can give existing columns new names using another SQL reserved keyword AS. The syntax is as follows:

SELECT
  column_name AS new_column_name
FROM table_name
;

Worth noting:

1. Some SQL dialects will actually not allow two columns to be named exactly the same in the same output. PostgreSQL is more forgiving of that and will not give you an error.
2. If the AS is omitted, the aliasing will still work. However, this is not proper SQL syntax and should be avoided.

Exercises

On your own:

1. Select both columns in the table departments. However, rename the column dept_no to department_number and rename the column dept_name to department_name.

---

Creating new columns with uniform values

There is a somewhat trivial use case for creating new columns:

SELECT
  1,
  'one'
FROM departments  
;

The number of rows returned equals to how many rows there are originally in the departments table.

We can verify this by selecting more columns from the same table and also renaming the newly created columns:

SELECT
  1 AS my_first_numeric_column,
  'one' AS my_first_text_column,
  dept_no,
  dept_name
FROM departments  
;

From the trivial example above, we learn many things:

1. When a newly created column is unnamed, PostgreSQL by default displays it as ?column?.

2. When we want something evaluated as a number, we enter it without quotation marks (e.g. 1).

3. When we want something evaluated as text, we enter it with single quotation marks (e.g. ‘one’).

4. For more naming conventions, see the SQL style guide for more details.

---

Debugging

Some common debugging methods:

1. Make sure that entities (e.g. table names and column names) are spelled correctly! SQL will try its best to be helpful in its error messages to correct misspellings.

2. Make sure that SQL reserve words (e.g. SELECT, FROM, LIMIT) are spelled correctly! If you are using a text editor like Atom or Sublime, only correctly spelled SQL reserve words will change color when editing a file saved as a .sql file.

3. Make sure that SQL statements ending in semicolon ; or else the statement will not run.

4. Make sure that the order of the clauses are correct.

5. Are you missing a comma somewhere? This is common when referring to multiple columns in the SELECT statement. Having cleanly styled SQL will help make the debugging (and comma chasing) easier.

---

More practice

In your breakout rooms, please do the following exercises together:

Write the SQL query that generates exactly the following output using the employees table:

Debug the following SQL statement so that it returns 3 columns including the first name, last name, and employee number from the employees table:

  SELECT
    first_name
    last_name
    employee_no
  FROM employee
  LIMIT 10

The output should look like this:

How many columns will the following SQL query return? Why?

  SELECT *,
    dept_name
  FROM departments
  ;

Why is the following SQL statement syntactically correct, but not returning the expected 2 columns?

  SELECT
    first_name
    last_name
  FROM employees
  LIMIT 10
  ;

---

Wrapping up

• Reminder: Homework 2 is still due next Wednesday, even though our next class won’t be until September 13th.
• Have a good Labor Day!