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
