Ch. 1 Simple Database Commands

OFFSET Clause

If we want to skip a number of rows before returning the rows, we can use the _____clause that is placed after the LIMIT clause.

SELECT * FROM customer WHERE email like '%@gmail.com';

If we wanted to list all of the customers who have their email in the domain gmail.com

SMALLINT

A small integer is a 2-byte signed integer that has a range between -32,768 to 32,767.

BETWEEN

an operator that allows us to check if an attribute is within a range of values. It would be similar to using two separate conditions using the logical operators but helps to simplify this further

Where

Which SQL clause applies conditions to filter the dataset? a.) SELECT b.) FROM c.) WHERE

FROM

Which SQL clause identifies one or more tables as the source for a query? a) WHERE b.) FROM c.) SELECT

IN

allows you to search using a variety of values for a single column. This can simplify some queries by avoiding having to write a separate condition using the OR operator

INT

An integer is a 4-byte integer that has a range between -2,147,483,648 to 2,147,483,647

No

Are nulls counted in the avg clause?

What is the default order of ORDER BY? Ascending or Descending?

Ascending

Define Ascending and Descending

Ascending is increasing in size or importance. Descending is opposite.

SEQUENCE parameters

CREATE SEQUENCE mysequence START 10 INCREMENT 10 START: The value that the sequence starts with. The default is to start with 1. INCREMENT: The value that should be added to the current sequence value to create a new value. MINVALUE: The minimum value that is set to a sequence. The default is 1. MAXVALUE: The maximum value that is set to a sequence. The default maximum value is the maximum value of the data type of the sequence.

CREATE TABLE <tablename> ( <column1> <datatype>[constraint], <column2> <datatype>[constraint]); CREATE TABLE newsletter( email VARCHAR(50) );

CREATE TABLE SYNTAX

CREATE table contact (contact_id SERIAL PRIMARY KEY); ALTER TABLE contact ADD username VARCHAR(50); ALTER TABLE contact ADD CONSTRAINT username_unique UNIQUE (username); ALTER TABLE contact ALTER COLUMN contact_id TYPE Varchar(100); alter table contact drop contact_id

Create a table with a primary key then add a column and then add a constraint to that column then switch that column's data type and then drop that column

25 SELECT COUNT (DISTINCT state) FROM customer;

Return the total count of each distinct state from customer

SELECT *FROM invoice ORDER BY total DESC LIMIT 5;

Returns top 5 rows of invoice based on total

How could we improve the below with IN? SELECT * FROM customer WHERE country = 'Brazil' OR country = 'Belgium' OR country = 'Norway' OR country = 'Austria';

SELECT * FROM customer WHERE country IN ('Brazil', 'Belgium', 'Norway', 'Austria');

MIN or MAX syntax

SELECT MIN(<columnname>) FROM <tablename>; same for MAX

yyyy-mm-dd

SQL date format

h:mi:ss.mmm where mmm are in milliseconds.

SQL time format

True. Simply by inserting a row into the table without specifying the contact_id, the nextval function is automatically called for us if the primary key is using serial CREATE TABLE contact( contact_id SERIAL, username VARCHAR(50), password VARCHAR(50) ); INSERT INTO contact (username,password)VALUES ('roda','Password99');

True or False? We can add rows into a table using the INSERT statement with an auto-incremented column without passing in any values.

Miller

USING the SELECT statement, query the customer table ordered by the customer_id. Set the LIMIT to 10 and OFFSET to 10. What is the last name of the last row returned?

2 select album_id,count(*) from track group by album_id having count(*) > 30

Using the GROUP BY and HAVING clause, filter the track table by album_id. How many groups have more than 30 tracks?

26 SELECT max(total) FROM invoice where billing_country <> 'USA';

Using the SELECT statement, query the invoice table to find the max total where the billing_country is not equal to USA.

Query failed

What will the below return? SELECT * FROM customer WHERE first_name = Helena;

an error because group by must come after from

What would the below return? select billing_country from invoice having sum(total) > 50; group by billing_country

single quotes around the string

When filtering for strings with the WHERE clause what must we remember to use?

select

Which SQL clause retrieves zero or more rows from one or more database columns? A) WHERE b.) SELECT c.) FROM

A. but its debatable and this is a dumb question

Which of the following ALTER TABLE statements would create work without errors? a.)ALTER TABLE customer ALTER COLUMN postal_code TYPE text; b.)ALTER TABLE customer ALTER COLUMN state TYPE VARCHAR (100); c.)ALTER TABLE customer ALTER COLUMN postal_code TYPE VARCHAR (100); d.)ALTER TABLE customer ALTER COLUMN city TYPE INT;

A

Which of the following is a correctly formatted INSERT statement that will successfully add a new record that uses the auto-incremented primary key into the following table?CREATE TABLE book( book_id SERIAL PRIMARY KEY, book_name VARCHAR NOT NULL); a.)insert into book (book_name) values ('Lord of the Rings' ) b.)insert into book (book_id, book_name) values (nextval, 'Lord of the Rings') c.)insert into book (book_id, book_name) values (1, 'Lord of the Rings' ) d.)insert into book (book_name) values (Lord of the Rings)

B

Which of the following statement(s) would successfully delete the invoice_id 353 from the invoice table where invoice_line is the source of the foreign key? a.)DELETE FROM invoice WHERE invoice_id = 353; b.)DELETE FROM invoice_line WHERE invoice_id = 353; DELETE FROM invoice WHERE invoice_id = 353; c.)DELETE FROM invoice_line WHERE invoice_id = 353; d.)DELETE FROM invoice WHERE invoice_id = 353; DELETE FROM invoice_line WHERE invoice_id = 353;

C

Which of these SELECT statements would successfully display exactly three columns of data from the Invoice table? a)SELECT * FROM invoice b.) SELECT customer_id total billing_address FROM invoice c.) SELECT customer_id, invoice_id, invoice_date FROM invoice d.) SELECT invoice_id, total, billing_state FROM inuoice

ALTER TABLE tablename ALTER COLUMN columnname TYPE newdatatype;

changes the data type of a column, note you cannot alter a foreign key unless you go to its source table and alter it there

NOT NULL

constraint ensures that a value has to exist for this column

UNIQUE constraint

creates a unique index on the column

ALTER TABLE invoice ADD CONSTRAINT invoice_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customer (customer_id);

creating a foreign key

SELECT now();

current date and time

DELETE from playlist_track WHERE playlist_id = 16;DELETE from playlist WHERE playlist_id = 16;

delete the playlist_id 16 from the playlist id and keep in mind playlist_id is a foreign key linked to playlist_track

DESC

descending order

SELECT AVG(total)::numeric(10,2) FROM invoice; ^ casting it to 10 digits (to be safe) with 2 decimals

find the avg total from the invoice table but cast the result to 2 decimals

SELECT billing_country, billing_state, SUM(total), ROUND(AVG(total),2) FROM invoiceGROUP BY billing_country, billing_stateORDER BY billing_country, billing_state; Notice above, we have added the ORDER BY clause so that they are sorted in an order that makes logical sense

find the total and average of each invoice based on the state and country

SELECT * FROM customer WHERE email like 'm%@a%.%';

get all of the email addresses that start with the letter m and have a domain starting with the letter a.

SELECT country, COUNT(*) FROM customer GROUP BY country;

get the number of customers that we have in each country

LIMIT clause

helps to constrain the number of rows that are returned by the query. It is an optional clause added to the end of the statement.

DEFAULT constraint

one that assigns a value to an attribute whenever a new row is added to a table if a value is not set for it. This can be useful to set a base value to an attribute

CHECK constraint

one that can be used to validate data when an attribute is entered. fee INT CHECK (fee > 0),

SELECT now()::date;

only returns the date not time

SELECT customer_id, ROUND(AVG(total),2) FROM invoice group by cusomter_id

return customer_id and the avg of total to 2 decimals from the invoice table

SELECT * FROM invoice ORDER BY total DESC LIMIT 5 OFFSET 5;

returns 5 rows after the first 5

SEQUENCE

special type of object that creates a sequence of integers. All databases will have the option of an auto-incrementing column to use as the primary key column in a table

query

to ask, inquire

UPDATE albumSET title = 'Out of Exile [Disc 1]'WHERE album_id = 11

update the album table where the album_id is 11 to set the name to Out of Exile

ASC

used for ascending order

SELECT

An SQL clause that retrieves zero or more rows of data from one or more database columns.

Real

A 4-byte floating-point number.

CHAR(n)

A fixed-length character of length n with space added. If a string is added to the column that is shorter than the string, PostgreSQL pads extra spaces up to the length (n) of the column. If we try to insert a value that is longer than the length of the column, an error will be generated.

Schema Browser

A list of table names, column names, and data types contained within a database.

Numeric(p,s)

A real number that has p digits with s number of digits that are displayed after the decimal point.

SERIAL

A sequence can be automatically added to a table using the data type _____

Database

A set of records contained within one or more tables.

DELETE FROM Statement

The DELETE statement is quite an easy statement to learn as it uses the same WHERE clause as you have seen with the SELECT and UPDATE statements. The syntax for the DELETE statement looks like the following: DELETE FROM <tablename>WHERE <condition>;

AND

The ____ operator displays a record if all of the conditions are separated by it are true.

VARCHAR(n)

A variable-length character string that can store up to n characters. Note that with VARCHAR, unlike CHAR, PostgreSQL does not pad the spaces when the stored string is shorter than the length of the column

TEXT

A variable-length character string that has unlimited length.

WHERE

An SQL clause that applies conditions to filter the result-set.

FROM

An SQL clause that identifies one or more tables as the source for a database query statement.

insert into product (product_name) values ('Desk' ) remember serial will auto-increment the primary key for us

Given the below table insert a new product called desk: CREATE TABLE product(product_id SERIAL PRIMARY KEY,product_name VARCHAR NOT NULL);

INSERT INTO contact (first_name, last_name, phone) SELECT first_name, last_name, phoneFROM customerWHERE country = 'USA';

Given the below table use insert with select to add the first and last name and phone number from customer table of anyone in the USA: CREATE TABLE contact( contact_id SERIAL, first_name VARCHAR(40), last_name VARCHAR(40), phone VARCHAR(24) );

A

Given the new table, which insert statement would query from the employee table to insert the first name, last name, and email address of all employees in the right columns? CREATE TABLE league(league_id SERIAL PRIMARY KEY,first_name VARCHAR NOT NULL,last_name VARCHAR NOT NULL,email VARCHAR); a.)INSERT INTO league (first_name, last_name, email)SELECT first_name, last_name, email FROM employee; b.)INSERT INTO leagueSELECT first_name, last_name, email FROM employee; c.)INSERT INTO league (email, last_name, first_name)SELECT first_name, last_name, email FROM employee; d.)INSERT INTO league (league_id, first_name, last_name, email)SELECT first_name, last_name, email FROM employee;

A

Given the new table, which insert statement would query from the employee table to insert the first name, last name, and email address of all employees in the right columns? CREATE TABLE league(league_id SERIAL PRIMARY KEY,first_name VARCHAR NOT NULL,last_name VARCHAR NOT NULL,email VARCHAR); a.)INSERT INTO league (first_name, last_name, email)SELECT first_name, last_name, email FROM employee; b.)INSERT INTO leagueSELECT first_name, last_name, email FROM employee; c.)INSERT INTO league (email, last_name, first_name)SELECT first_name, last_name, email FROM employee; d.)INSERT INTO league (league_id, first_name, last_name, email)SELECT first_name, last_name, email FROM employee;

A

Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error? A. DROP TABLE invoice_line;DROP TABLE playlist_track;DROP TABLE invoice; B. DROP TABLE playlist_trackDROP TABLE playlist;DROP TABLE genre; C. DROP TABLE genre;DROP TABLE album;DROP TABLE artist; D. DROP TABLE track;DROP TABLE playlist_track;DROP TABLE playlist;

INSERT INTO syntax

INSERT INTO <tablename> (<column1>) VALUES (<value1>) Note that we must include all columns in a table that has a PRIMARY KEY or NOT NULL constraint in place. The only exceptions are if the column has a DEFAULT value or the column has a sequence attached to it and must be careful when inserting into foreign keys

D

Identify the SQL command that uses an aggregate function that could be used to find the average total in the invoice table. a.)SELECT average total FROM invoice; b.)SELECT sum(total)/max(total) from invoice; c.)SELECT sum(total) from invoice; d.)SELECT avg(total) from invoice;

d.)SELECT min(total) FROM invoice;

Identify the SQL command that uses an aggregate function that could be used to find the lowest total in the invoice table. a.) SELECT lowest(total) FROM invoice; b.) SELECT min total FROM invoice; c.) SELECT total(min) FROM invoice; d.) SELECT min(total) FROM invoice;

B

Identify the correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column user_number with the constraint name user_number_unique on the table user. a.)ALTER TABLE user ADD CONSTRAINT user_number UNIQUE (user_number); b.)ALTER TABLE user ADD CONSTRAINT user_number_unique UNIQUE (user_number); c.)ALTER TABLE user ADD CONSTRAINT user_number UNIQUE (user_number_unique); d.)ALTER TABLE user ADD UNIQUE user_number CONSTRAINT (user_number_unique);

FOREIGN KEY constraint

It is used to link two tables together. The ____ key in one table refers to the primary key in another table.

Timestamp

Stores both the date and time values.

Date

Stores the dates on its own.

Time

Stores the time of day values.

Boolean

Stores true, false, or null (no value).

ALTER TABLE <tablename> DROP <columnname>;

Syntax to drop a column

INSERT with SELECT

The INSERT feature using a SELECT clause can come in quite handy when we need to load data into a table from another table. It can be used to load data into an existing table, a newly created table or into a temporary table that includes some calculations. Anything that can be queried can be subsequently added to an INSERT statement to be inserted into a table.

ALTER TABLE ALTER TABLE customer ADD CONSTRAINT country_unique UNIQUE (country);

The SQL command used to make changes to table structure. When the command is followed by a keyword (ADD or MODIFY), it adds a column or changes column characteristics.

UPDATE a Single Row

The UPDATE statement is used to modify data that is already in a table. The syntax looks like the following: UPDATE <tablename>SET <column1> = <value1>, <column2> = <value2>, ....WHERE <condition>;

OR

The ___ operator displays a record if any of the conditions separated by ___ is true. As long as one of the conditions is met, the result would be returned.

LIKE

The _____ operator in a WHERE clause allows you to check if an attribute's value matches a specific string pattern using wildcards ( % , _ )

ORDER BY

The ______ clause is quite useful when we want to list the records in a specific order based on the column list

SERIAL

The ______ clause is the same as an integer but PostgreSQL will automatically generate and populate the values into this column.

HAVING

The _______ clause allows us to specify a search condition for a group or an aggregate. It is used with the GROUP BY clause that divides rows into groups. You are already familiar with the WHERE clause. The WHERE clause filters individual rows based on a specified condition. The ________ clause filters the groups of rows based on a set of conditions.

GROUP BY

The ________ clause in the SELECT statement can help to divide the rows that are being returned from the SELECT statement into specific groups. For each of those items in the group, we can then apply an aggregate function among that group.

SELECT * FROM customer WHERE first_name like 'L%';

This finds anyone with the first name that starts with L

COALESCE SELECT COALESCE(SUM(total),0) FROM invoice WHERE invoice_id < 1;

This function returns the second argument if the first argument is NULL. If we wanted to return a 0 instead of NULL we would have to use the ______ function.

Float(n)

This is a floating-point number that has the precision that can be up to n bytes, up to 8 bytes.

SELECT * FROM invoice ORDER BY billing_country;

This will show invoice table

5.71 SELECT round(avg(total),2) FROM invoice WHERE customer_id = 1;

Using the SELECT statement, query the invoice table to find the average total cost for all orders purchased by customer_id equal to 1 rounded to the nearest cent.

486 SELECT sum(total) FROM invoice where invoice_date between '2010-01-01' and '2011-01-01';

Using the SELECT statement, query the invoice table to find the total cost for all orders placed between 2010-01-01 and 2011-01-01.

c.)374 SELECT count(*) FROM track where genre_id = 3;

Using the SELECT statement, query the track table to find the number of tracks that are in the genre_id equal to 3.

SELECT customer_id, sum(total) FROM invoice WHERE customer_id in (1,2,3) GROUP BY customer_id HAVING max(total)>10 Use the below to check it and add them up manually select customer_id, total from invoice where customer_id in (1,2,3) order by customer_id max total is really just including all of them so this is a DUMB question

Using the WHERE and HAVING clauses, filter the invoice table to include customer_id values of 1, 2, or 3. Group by the customer_id having the maximum value of the orders greater than 10. Provide the list of customer_id and the sum of the totals that fit these criteria.

SELECT TO_CHAR(now()::date, 'mm/dd/yyyy');

We can change the format of the date using the TO_CHAR function. The TO_CHAR to convert the date takes in two parameters. The first parameter is the value that we want to format. The second is the template that defines the format

Changing Columns

We may want to change the data type of a column. Generally, this is rare once data has been inserted in a column.

Not equal to

What does this signify in SQL <>?

country with the lowest and highest value in alphabetical terms, this also works with dates as well

What would the below return? SELECT MIN(country), MAX(country) FROM customer;

A

Which of the following UPDATE statements would successfully update the album table where the album_id is 11 to set the name to Out of Exile [Disc 1]? a.)UPDATE album SET title = 'Out of Exile [Disc 1]' WHERE album_id = 11 b.)UPDATE album SET album_id = 11 WHERE title = 'Out of Exile [Disc 1]' c.)UPDATE album SET title = Out of Exile [Disc 1] WHERE album_id = 11 d.)UPDATE album WHERE album_id = 11 SET title = 'Out of Exile [Disc 1]'

The reason we could do this is due to the fact that the invoice_id is part of the GROUP BY clause. However, if we needed to filter based on another column like the unit_price to check if it was more than 1 before we grouped them and added it to the HAVING clause we would have an error generated. This is due to the fact that the unit_price column is not part of the GROUP BY field nor a result of an aggregate function. To be valid in the HAVING clause, we can only compare the aggregate functions or the column part of the GROUP BY. For it to be a valid query, the check on the unit_price needs to be moved to the WHERE clause or onto the group by: SELECT invoice_id, SUM(unit_price * quantity) FROM invoice_line WHERE unit_price > 1 GROUP BY invoice_id HAVING SUM(unit_price * quantity) > 1 ORDER BY invoice_id;

Which one works and why? A. SELECT invoice_id, SUM(unit_price * quantity) FROM invoice_line GROUP BY invoice_id HAVING SUM(unit_price * quantity) > 1 AND invoice_id > 100 ORDER BY invoice_id; B. SELECT invoice_id, SUM(unit_price * quantity) FROM invoice_line GROUP BY invoice_id HAVING SUM(unit_price * quantity) > 1 AND unit_price > 1 ORDER BY invoice_id;

The constraint could not be added as the country is repeated for different customers. However, we could add a constraint on the customer's email as it is unique

Why wouldn't the below work? ALTER TABLE customer ADD CONSTRAINT country_unique UNIQUE (country);

NOT

You can also use the ___ operator to negate the conditional expression. Since all of the conditional expressions evaluate to true or false, the ___ operator will get the rows that do not match a certain condition.

SELECT billing_country, SUM(total) FROM invoiceGROUP BY billing_country HAVING SUM(total) > 50;

any country with over 50 in their sum total grouped by the country from inovice

PRIMARY KEY constraint

helps to uniquely identify a row within a table by enforcing entity integrity. In order to do so, it applies two other constraints of the NOT NULL constraint and the UNIQUE constraint

DELETE Foreign keys

if we want to delete a row with a foreign key we must also delete that row from the source FIRST see below: DELETE FROM invoice_line WHERE invoice_id = 1; DELETE FROM invoiceWHERE invoice_id = 1;

insert into album (album_id, title, artist_id) values (400, 'New Album', 5)

insert into the album table a new record with the album_id = 400, title = New Album and the artist_id = 5

insert into playlist (playlist_id, name) values (30, 'New Age Playlist'), (31, 'Oldies'), (32, 'Road Trip')

insert these 3 records into the playlist table: (30, 'New Age Playlist'), (31, 'Oldies'), (32, 'Road Trip') where the number is playlist_id and the name is name in the playlist table

_ (underscore) wildcard

matches a single wildcard character. allows us to query data to look for a specific length of data

% wildcard

searches for zero, one, or more characters as a replacement. It will be the most common operator used in the LIKE clause