General Notes
— Single line comment
/* Multi line comment */
CREATE DATABASE DATABASENAME; — create a database named DATABASENAME
SELECT * FROM tbl; — Select all columns and rows in the tbl table.
SELECT c1,c2 FROM tbl;
SELECT DISTINCT c1 FROM tbl; — Select only distinct values from c1, ie values that are different from each other.
SELECT c1,c2 — Select column 1 and column 2
FROM tbl — from tbl table
WHERE conditions — Where specific conditions are met
ORDER BY c1 ASC, c2 DESC — Order by …….
SELECT COUNT(*) FROM tbl; — Count number of rows from tbl, using * may not be best practice if a specific column is being counted.
SELECT COUNT(DISTINCT column) FROM table; — Count number of distinct types in a column.
SELECT * FROM tbl LIMIT 5; — Select every row from tbl, but limit it to 5 entries (defaults to first five)
SELECT c1, c2
FROM tbl
ORDER BY c1 asc/desc; — Order by c1, choose either asc/desc
SELECT c1,c2
FROM tbl
WHERE c2 BETWEEN 8 AND 9; — Where the c2 column has values equal or greater than 8 and equal or lesser than 9.
SELECT c1,c2
FROM tbl
WHERE c2 NOT BETWEEN 8 AND 9; — Where the c2 column DOES NOT HAVE values equal or greater than 8 and equal or lesser than 9.
SELECT c1,date1
FROM tbl
WHERE date1 BETWEEN ‘2007-01-03’ AND ‘2007-02-16’; — Where the date column has a date value equal to or between 2007-01-03 and 2007-02-16.
SELECT c1,c2
FROM tbl
WHERE c2 IN (1,2) — Where values in c2 contain values in bracket. Its basically an OR statement (ie where c2 = 1 OR 2)
SELECT c1,c2
FROM tbl
WHERE c2 NOT IN (1,2) — Where values in c2 do not contain values in bracket.
Wildcards:
SELECT c1,name1
FROM tbl
WHERE name1 LIKE ‘jo%’; — Where name1 starts with jo and has anything after that. Wilcard, basically Jonathan would be caught as would john and joe.
SELECT c1,name1
FROM tbl
WHERE name1 NOT LIKE ‘jo%’; — Exclude entries where name1 starts with jo and has anything after that. Wilcard, basically Jonathan would be caught as would john and joe.
SELECT c1,name1
FROM tbl
WHERE name1 LIKE ‘%le%’; — Where name1 contains ‘le’ and has anything before and after that. Alex and Alexander would be captured by this.
SELECT c1,name1
FROM tbl
WHERE name1 LIKE ‘Alex_’; — Where name1 contains ‘Alex’ and has a single character after Alex. Alexa would be captured by this, but Alexander would not.
SELECT c1,name1
FROM tbl
WHERE name1 ILIKE ‘Alex&’; — ILIKE is the same as LIKE but removes case sensitivity, so Alexander and alexander would both be captured by this.
Aggregate Functions:
SELECT AVG(c1) FROM tbl; — average values from c1 column, then display that averaged value.
SELECT ROUND(AVG(c1),2) FROM tbl; — rounds to 2 places the average values from c1 column, then displays that value.
SELECT MIN(c1) FROM tbl; — finds lowest value from c1 column, then displays it.
SELECT MAX(c1) FROM tbl; — finds highest value from c1 column, then displays it.
SELECT COUNT(c1) FROM tbl WHERE c1 = 0; –Find number of c1 values that = 0.
SELECT SUM(c1) FROM tbl; — Add all the values in c1 together.
Group by:
SELECT c1, SUM(c2)
FROM tbl
GROUP BY c1; — In the case where c1 is the same, sum the associated c2 values. Example: if c1 were a staff member, and c2 were that staff members weekly salary then this query would give total amount paid to this staff member. Note: postgre sql is quite flexible regarding group by, other sql engines may not be.
SELECT c1, COUNT(c1)
FROM tbl
GROUP BY c1; — This query will display the distinct values of c1 and the sum of those distinct values next to it.
Having:
SELECT c1, SUM(c2)
FROM tbl
GROUP BY c1
HAVING SUM(c2) > 200; — Having is basically a conditional/where statement for the output of the group by. In this case when THE SUM of c2 is greater than 200 the output displays it grouped by c1. Without being grouped first the sum wouldn’t exist, so a WHERE statement cannot be used.
As:
SELECT c1 AS new_name FROM tbl; — Changes the name of the displayed column title from c1 to new_name. NOTE: this does not change the actual data source, only how it is displayed. Sometimes ‘name’ can work instead of as.
JOINS:
SQL JOIN DIAGRAM???
INNER JOIN – only when there is a complete match, with no null values.
LEFT OUTER JOIN – All values on table A, plus matching values on table B. LEFT JOIN = LEFT OUTER JOIN
FULL OUTER JOIN – All values from both tables, some will be joined if there is a match but if there is not a match a ‘null’ value will be returned.
SELECT tlbA.c1,tlbA.c2,tblB.c5 — Select column c1 from table tblA, c2 from table tblA, c5 from table tblB.
FROM tblA
INNER JOIN tblB on tblA.pka = tblB.fka — Join the two tables (tblA and tblB) using the symmetry of tblA.pka and tblB.fka. For instance, if tblA.pka were an id number then it would look for similar values in tblB.fka and associate them all under tblA.pka. If you write JOIN it will default to INNER JOIN.
SELECT * FROM tblA
FULL OUTER JOIN tblB — All records from both tables, where there is no match null will appear.
ON tblA.c1 = tblB.c1 — The two tables are linked by the c1 field, this could be an ID common to both, a name, etc.
SELECT * FROM tblA
LEFT OUTER JOIN tblB — All values from tblA, but only values from tblB where c1 matches.
ON tblA.c1 = tblB.c1
SELECT * FROM tblA
LEFT OUTER JOIN tblB
ON tblA.c1 = tblB.c1
WHERE tblB.c1 IS null — When the condition is met that the associated values in tblB are null. This is a way to check for values that exist in tlbA but not in tblB.
Union:
SELECT c1,c2
FROM tblA
UNION — Union is like a join, but it only matches data based on column name. c1from tblA matches with c1 from tblB.
SELECT c1,c2
FROM tblB
SELECT c1,c2
FROM tblA
UNION ALL
SELECT c1,c2
FROM tblB
Timestamps:
Timestamps have a specific approach depending on SQL type.
postgre SQL examples:
SELECT extract(day from datec1) FROM tbl; — datec1 is a column containing date values, extract allows just the day without other values, ie day of the month (1-28ish). The extract function basically narrows down a date stamp to some specific unit or unit of time.
SELECT SUM(c1), extract(month from datec2) AS MONTH
FROM tbl
GROUP BY month
ORDER BY SUM(c1) DESC
LIMIT1; — This query is going to find the month in which the highest amount was earned.
Math(PostgreSQL specific, but usually avail universally):
SELECT c1/c2 AS divided_combo FROM tbl; — Select the c1 and c2 column values, then divide and display as divided_combo.
Operators:
= equal
> greater than
< less than
>= greater than or equal
<= less than or equal
<> or != not equal
AND and join
OR or join
MORE AVAILABLE IN DOCUMENTATION FOR SPECIFIC SQL ENGINE
String Functions(PostgreSQL specific, but usually avail universally):
SELECT first_name,char_length(first_name) FROM customer; — displays first name and how many characters are in that name.
SELECT lower(first_name) FROM tbl; — Selects lower case version of first name, changing capitals to lower.
MORE AVAILABLE IN DOCUMENTATION FOR SPECIFIC SQL ENGINE
Subquery:
SELECT c1,c2,c3 FROM tbl
WHERE
c1 > (SELECT AVG (c1) FROM tbl); — compares selects c1 from tbl where c1 is greater than the average of all c1 combined. The subquery is the second query in brackets.
SELECT c1,c2
FROM tbl
WHERE c1 IN
(SELECT tbl1.c1
FROM tbl2
INNER JOIN tbl1 on tbl1.c1 = tbl2.c1
WHERE
date BETWEEN ‘2017-01-01’ AND ‘2017-02-01’;) — this entire thing is a suc queryu.
Self Join:
SELECT tbl.name — display the name of an employee
FROM tbl AS z1, tbl AS z2 –this is creating two copies of tbl, one called z1 and one called z2.
WHERE
z1.c2 = z2.c2 AND tbl.name=”john”; –on the condition that c2 is the same on both tables AND the name is equal to john.
SELECT a.c1, a.c2, b.c1, b.c2
FROM tbl AS a
JOIN tbl AS b
ON a.c1 = b.c2; — The on statement basically replaces the Where statement.
Schema:
SELECT * FROM schema.tbl; — this selects all from the tbl table, under the ‘schema’ schema.
Creating/Inputting:
CREATE TABLE tbl; — Create a table called tbl
CREATE TABLE tbl(
c1 data_type PRIMARY KEY, — c1 is the column name, PRIMARY KEY identifies this as the primary key of the table, the unique identifier for the table’s contents.
c2 data_type;
Basic Syntax for table creation:
CREATE TABLE table_name(
column_name data_type column_constraint
);
Constraints:
UNIQUE — force content to be unique, distinct.
PRIMARY KEY — The unique identifier of the table, like a student id.
CHECK — To check to make sure a condition is met when inputting data
REFERENCES — A value cannot be entered in a column unless it exists in a column in another table, basically a JOIN.
NOT NULL — Check to make sure this field has a value, do not accept entries without it.
!!!!!!!!!! maybe some more detail here in the create table section.
Data Types:
boolean: true, false, null. Values such as 1,yes,y,t will conver to true, same with false.
Views (postgre SQL specific, but might work elsewhere):
CREATE VIEW view_name AS enter_sql_query_here;
Creating the view:
CREATE VIEW view_name AS — Creates a view named view_name
SELECT c1,c2,c3 — Selects c1, c2 and c3 from the ‘tbl’ tbl and stores that query in a view.
FROM tbl;
SELECT * from view_name — Retrieves data from view.
ALTER VIEW view_name RENAME TO new_view_name; — this renames the view
DROP VIEW view_name; — gets rid of the view called view_name
INSERT INTO tbl_name(c1,c2) # insert into the table called tbl_name, under the columns c1 and c2.
VALUES (value1,value2),(value1,value2); # the values to be inserted are value1 and value2 into 1 row, and value1 and value2 into the next row. Make sure to note that strings need to be in quotation marks.
CREATE TABLE tbl_copy(LIKE tbl_name); # this creates a copy of the tbl_name table, and names it tbl_copy. It does not copy the data, just the structure of the table.
INSERT INTO tbl_copy
SELECT * FROM tbl_Name; #This copies all the data from tbl_name over to the tbl_copy table, you can also add a WHERE clause if desired to choose specific data.
UPDATE tbl_name # This updates values in a table.
SET column1 = value1, # Set decides what the value is to be changed to
column2 = value2
WHERE condtionals # Where a value is met, this is where you define which row/entry is to be changed.
RETURNING column1,column2; #This displays the changed values so you can see it, otherwise it just says ‘query successfully run’.
DELETE FROM tbl_name # delete rows from a table called tbl_name
WHERE conditionals # use conditionals to choose specific row, if you omit this all rows will be deleted.
RETURNING *; # returning deleted rows with all columns.
ALTER TABLE tbl_name # Alter table alters the default properties of the table
ACTION # The specific altering action to be applied to the table.
ALTER TABLE tbl_name
DROP COLUMN column1; # Get rid of a column from the table
ALTER TABLE tbl_name
RENAME COLUMN column1 TO new_column_title; # renames column1 to new_column_title
ALTER TABLE tbl_name RENAME TO new_tbl_name # changes the tables name fom tbl_name to new_tbl_name.
DROP TABLE IF EXISTS tbl_name RESTRICT; # Remove this table completely from the database. The IF EXISTS is optional, but best practice. RESTRICT is also optional and prevents the table from being dropped if it has dependant objects.
CREATE TABLE tbl_name(
column1 INTEGER PRIMARY KEY,
column2 VARCHAR(50),
column3 INTEGER NOT NULL, # The not null check makes sure the field has a value, it cannot be empty. Note that 0 does not equal null, null is nothing.
column4 INTEGER CHECK (column4>0) # This checks to see if column4’s entry is greater than 0. If it is not it will not allow an entry.
);
CREATE TABLE tbl_name(
column1 PRIMARY KEY,
column2 VARCHAR(50),
column3 VARCHAR(100) UNIQUE, #This means that column3 MUST be unique.
);
Examples:
SELECT last_name, first_name — Select last_name and first_name columns
FROM customer — from the customer table
WHERE first_name = ‘Jamie’ AND last_name = ‘Rice’; — Where conditions first name = Jamie and last_name = Rice are met.
SELECT customer_id,amount,payment_date
FROM payment
WHERE amount <= 1 OR amount >= 8; — Where amount is equal or less than 1 OR equal or greater than 8.
SELECT COUNT(DISTINCT amount) FROM payment; — Count number of distinct values in amount column of payment table.
SELECT first_name,last_name FROM customer ORDER BY first_name ASC; — Select first name and last name from customer table and order by first name in ascending order. In the case of strings it will be alphabetical.
SELECT first_name,last_name
FROM customer
ORDER BY first_name ASC, last_name DESC; — Order first by first_name ascending and then by last_name decending.
Python
psycopg2 – Library for interacting with python postgreSQL database.
import psycopg2 as pg2 # this imports the library and gives it the call pg2, for simplicity.
conn = pg2.connect(database=’database_name’,user=’postgres’,password=’the_password’) #this connects to the database. Database is the database name/file, user is the username which defaults to postgress and password is the login password.
cur = conn.cursor() # cur stands for cursor, which is the control structure. Basically it allows you to input commands in SQL.
cur.execute(‘SQL_GOES_HERE’) # Whatever you want to do, the SQL goes in here.
cur.fetchmany(10) # this will fetch the first 10 rows, by pressing tab after typing cur. you will get a list of commands.
data = fetchmany(10) # Assigns the contents of fetchmany(10) query to data
data[0][2] #selects a specific aspect of the fetchmany(10) query mentioned. This selects the first[0] row, third[2] item.??????
conn.close() # closes the connection to the database.
Exercises
QUIZ 2:
1)
SELECT * FROM cd.facilities;
2)
SELECT name,membercost FROM cd.facilities;
3)
SELECT facid, name, membercost, monthlymaintenance
FROM cd.facilities
WHERE (membercost>0) AND ((monthlymaintenance/50)>membercost);
4)
SELECT name
FROM cd.facilities
WHERE name ILIKE ‘Tennis’;
5)
SELECT *
FROM cd.facilities
WHERE facid IN (1,5);
6)
SELECT *
FROM cd.facilities
WHERE facid IN (1,5);
7)
SELECT memid,surname,firstname,joindate
FROM cd.members
WHERE joindate > ‘2012-09-01’;
8)
SELECT DISTINCT surname
FROM cd.members
LIMIT 10
ORDER BY surname desc;
9)
SELECT joindate
FROM cd.members
ORDER BY joindate desc
LIMIT 1;
10)
SELECT COUNT (guestcost>5)
FROM cd.facilities;
11)
NOTHING, no question.
12)
SELECT facid, SUM(slots)
FROM cd.bookings
GROUP BY facid
ORDER BY SUM(slots) DESC;
13)
SELECT facid, SUM(slots) FROM cd.bookings GROUP BY facid HAVING SUM(slots)>1000 ORDER BY SUM(slots) DESC;
14) This is wrong but I do not know why.
SELECT * FROM cd.facilities
INNER JOIN cd.bookings ON cd.bookings.facid = cd.facilities.facid
WHERE starttime = ‘2012-09-21’;
15)
SELECT starttime FROM cd.members
INNER JOIN cd.bookings ON cd.members.memid = cd.bookings.memid
WHERE firstname ILIKE ‘David’ AND surname ILIKE ‘Farrell’;
QUIZ 3
