Skip to main content

Read / Select

Overview​

In this lab we will practice reading from / extracting data from a PostgreSQL database. This lab uses the pubs database.

Reading Data - using Select​

  1. Retrieve all columns from the authors table
  2. Retrieve the au_lname and au_fname columns from the authors table
  3. Retrieve the title and price columns from the titles table
  4. Retrieve the pub_name column from the publishers table
  5. Retrieve the stor_name and city columns from the stores table

Click here to view the sample solution

SELECT * FROM authors;

SELECT au_lname, au_fname FROM authors;

SELECT title, price FROM titles;

SELECT pub_name FROM publishers;

SELECT stor_name, city FROM stores;

Controlling which Columns are Returned - column names​

  1. Retrieve the author ID and last name as 'Author ID' and 'Last Name', respectively, from the authors table
  2. Retrieve the title ID as 'ID' and price as 'Cost' from the titles table
  3. Retrieve the publisher name as 'Publisher Name' from the publishers table
  4. Retrieve the store name as 'Name' and city as 'Location' from the stores table
  5. Retrieve the job description as 'Job Title' from the jobs table

Click here to view the sample solution

SELECT au_id AS "Author ID", au_lname AS "Last Name" FROM authors;

SELECT title_id AS "ID", price AS "Cost" FROM titles;

SELECT pub_name AS "Publisher Name" FROM publishers;

SELECT stor_name AS "Name", city AS "Location" FROM stores;

SELECT job_desc AS "Job Title" FROM jobs;

PostgreSQL uses double quotes for quoted identifiers (like column aliases) and single quotes for string literals - the opposite of what MySQL allows.

Control which Rows are Returned using LIMIT​

  1. Retrieve the first 5 rows from the authors table
  2. Retrieve the top 10 titles from the titles table
  3. Retrieve the first 3 publishers from the publishers table
  4. Retrieve the first 5 stores from the stores table
  5. Retrieve the first 10 jobs from the jobs table

Click here to view the sample solution

SELECT * FROM authors LIMIT 5;

SELECT * FROM titles LIMIT 10;

SELECT * FROM publishers LIMIT 3;

SELECT * FROM stores LIMIT 5;

SELECT * FROM jobs LIMIT 10;

Control which Rows are Returned using DISTINCT​

  1. Retrieve distinct author IDs from the titleauthor table
  2. Retrieve distinct titles from the titles table
  3. Retrieve distinct publisher names from the publishers table
  4. Retrieve distinct store names from the stores table
  5. Retrieve distinct job descriptions from the jobs table

Click here to view the sample solution

SELECT DISTINCT au_id FROM titleauthor;

SELECT DISTINCT title FROM titles;

SELECT DISTINCT pub_name FROM publishers;

SELECT DISTINCT stor_name FROM stores;

SELECT DISTINCT job_desc FROM jobs;

Control which Rows are Returned using WHERE​

  1. Retrieve authors with a contract status of true from the authors table
  2. Retrieve titles with a price greater than 10 from the titles table
  3. Retrieve publishers located in 'New York' from the publishers table
  4. Retrieve stores with a zip code of '90019' from the stores table
  5. Retrieve jobs with a max_lvl greater than or equal to 5 from the jobs table

Click here to view the sample solution

SELECT * FROM authors WHERE contract = true;

SELECT * FROM titles WHERE price > 10;

SELECT * FROM publishers WHERE city = 'New York';

SELECT * FROM stores WHERE zip = '90019';

SELECT * FROM jobs WHERE max_lvl >= 5;

contract is a boolean column in this schema, so it's compared against true/false rather than 1/0.

SQL Comparison Operators​

  1. Retrieve authors with an au_id equal to '267-41-2394' from the authors table
  2. Retrieve titles with a price not equal to 10 from the titles table
  3. Retrieve titles with a price greater than 20 from the titles table
  4. Retrieve titles with a price less than 15 from the titles table
  5. Retrieve titles with a price greater than or equal to 5 from the titles table

Click here to view the sample solution

SELECT * FROM authors WHERE au_id = '267-41-2394';

SELECT * FROM titles WHERE price <> 10;

SELECT * FROM titles WHERE price > 20;

SELECT * FROM titles WHERE price < 15;

SELECT * FROM titles WHERE price >= 5;

SQL Logical Operators​

  1. Retrieve authors with a contract status of true and a city of 'Oakland' from the authors table
  2. Retrieve titles with a price less than 10 or greater than 100 from the titles table
  3. Retrieve titles that are not published by 'Algodata Infosystems' from the titles table
  4. Retrieve authors with a contract status of false or a city of 'Chicago' from the authors table
  5. Retrieve authors with a contract status of true and not living in 'New York' from the authors table

Click here to view the sample solution

SELECT * FROM authors WHERE contract = true AND city = 'Oakland';

SELECT * FROM titles WHERE price < 10 OR price > 100;

SELECT * FROM titles WHERE pub_id NOT IN (SELECT pub_id FROM publishers WHERE pub_name = 'Algodata Infosystems');

SELECT * FROM authors WHERE contract = false OR city = 'Chicago';

SELECT * FROM authors WHERE contract = true AND city != 'New York';

Pattern Matching Operators​

  1. Retrieve authors with a last name starting with 'S' from the authors table
  2. Retrieve titles with a title containing 'Database' from the titles table
  3. Retrieve publishers located in either 'New York' or 'California' from the publishers table
  4. Retrieve stores with a stor_name starting with 'Book%' from the stores table
  5. Retrieve authors with a last name ending with 'son' from the authors table

Click here to view the sample solution

SELECT * FROM authors WHERE au_lname LIKE 'S%';

SELECT * FROM titles WHERE title LIKE '%Database%';

SELECT * FROM publishers WHERE city IN ('New York', 'California');

SELECT * FROM stores WHERE stor_name LIKE 'Book%';

SELECT * FROM authors WHERE au_lname LIKE '%son';

NULL Comparison Operators​

  1. Retrieve titles with a notes column that is not null from the titles table
  2. Retrieve titles with a price that is not null from the titles table

Click here to view the sample solution

SELECT * FROM titles WHERE notes IS NOT NULL;

SELECT * FROM titles WHERE price IS NOT NULL;

Chain multiple WHERE operators​

  1. Retrieve titles with a price less than 20 and published by 'Algodata Infosystems' (pub_id: '1389') from the titles table
  2. Retrieve titles with a price less than 10 or greater than 100 and not published by 'Algodata Infosystems' (pub_id: '1389') from the titles table

Click here to view the sample solution

SELECT * FROM titles WHERE price < 20 AND pub_id = '1389';

SELECT * FROM titles WHERE (price < 10 OR price > 100) AND pub_id != '1389';

pub_id is a char(4) column, so it must be compared against a quoted string. PostgreSQL is strict about types and will raise an "operator does not exist" error if you compare it against a bare number like 1389.

Order the Result Set​

  1. Retrieve authors from the authors table ordered by last name in ascending order
  2. Retrieve titles from the titles table ordered by price in descending order
  3. Retrieve publishers from the publishers table ordered by city in ascending order and pub_name in descending order
  4. Retrieve stores from the stores table ordered by state in descending order and stor_name in ascending order
  5. Retrieve jobs from the jobs table ordered by max_lvl in ascending order and min_lvl in descending order

Click here to view the sample solution

SELECT * FROM authors ORDER BY au_lname ASC;

SELECT * FROM titles ORDER BY price DESC;

SELECT * FROM publishers ORDER BY city ASC, pub_name DESC;

SELECT * FROM stores ORDER BY state DESC, stor_name ASC;

SELECT * FROM jobs ORDER BY max_lvl ASC, min_lvl DESC;

SQL Aliases​

  1. Retrieve titles with their prices as 'Cost' from the titles table
  2. Retrieve publishers with their names as 'Publisher' from the publishers table
  3. Retrieve stores with their names as 'Store Name' and cities as 'Location' from the stores table
  4. Retrieve jobs with their descriptions as 'Job Description' from the jobs table

Click here to view the sample solution

SELECT price AS "Cost" FROM titles;

SELECT pub_name AS "Publisher" FROM publishers;

SELECT stor_name AS "Store Name", city AS "Location" FROM stores;

SELECT job_desc AS "Job Description" FROM jobs;

Basic Mathematical Operations​

  1. Calculate the total sales quantity and amount for each store
  2. Calculate the profit margin (advance - price) for each title
  3. Calculate the royalty amount for each title

Click here to view the sample solution

SELECT sales.stor_id, SUM(sales.qty) AS total_quantity, SUM(sales.qty * titles.price) AS total_amount
FROM sales JOIN titles ON sales.title_id = titles.title_id
GROUP BY sales.stor_id;
SELECT title_id, (advance - price) AS profit_margin
FROM titles;

SELECT title_id, (ytd_sales * royalty) AS royalty_amount
FROM titles;