SQL Basic PT 2 Flashcards
SELECT DISTINCT c1, c2 FROM tbl
Select distinct rows by columns c1 and c2 from table tbl
SELECT c1, aggregate(expr)
FROM tbl
GROUP BY c1
Select c1 and use aggregate function on expression expr; group columns by column c1
SELECT c1, aggregate(expr)
FROM tbl
GROUP BY c1
HAVING c2 > v
Select c1 and use aggregate function on expression expr; group columns by column c1. Filter group of records with c2 greater than value v.
INSERT INTO tbl(c1,c2,…)
VALUES(v1,v2,…)
Insert data into table tbl
INSERT INTO tbl(c1,c2,…)
SELECT c1,c2… FROM tbl2
WHERE conditions
Insert data from tbl2 into tbl
UPDATE t
SET c1 = v1, c2 = v2…
WHERE conditions
Update data in table tbl
DELETE FROM tbl
WHERE conditions
Delete records from table tbl based on WHERE conditions
TRUNCATE TABLE tbl
Drop table tbl and re-create it, all data is lost
CREATE TABLE tbl( c1 datatype(length) c2 datatype(length) ... PRIMARY KEY(c1) 0
creates table tbl with primary key as c1
DROP TABLE tbl
Remove table tbl from database
ALTER TABLE tbl
ADD COLUMN c1 dataype(length)
Add column c1 to table tbl
ALTER TABLE tbl
DROP COLUMN c1
Drop column c1 from table tbl
SELECT * FROM tbl1
INNER JOIN tbl 2 ON join-conditions
Inner join table tbl1 with tbl2 based on join-conditions
SELECT * FROM tbl1
RIGHT JOIN tbl 2 ON join-conditions
Right join table tbl1 with tbl2 based on join-conditions
The COUNT Function?
Returns the number of input rows that match a specific condition of a query
SELECT COUNT(DISTINCT title) FROM film;
provides the distinct number of titles in film table
WHERE statement
allows us to select conditions on columns, appears immediately after the FROM clause of the SELECT statement
SELECT COUNT(*) FROM payment WHERE amount NOT IN (0.99, 1.98, 1.99)
counts payments that are NOT .99, 1.98 and 1.99
SELECT COUNT(*) FROM payment WHERE amount IN (0.99, 1.98, 1.99)
counts payments that are .99, 1.98 and 1.99
SELECT COUNT(*) FROM payment WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-15'
counts payments made between 2/1 and 1/15 (note depending on how inclusive is counted this may count 2007-02-15-00:00 as outside of range
SELECT COUNT(*) FROM payment WHERE amount NOT BETWEEN 8 AND 9
count payments NOT between $8 and $9 EXCLUSIVE
SELECT COUNT(*) FROM payment WHERE amount BETWEEN 8 AND 9
count payments between $8 and $9 INCLUSIVE
%
MATCHES ANY SEQUENCE OF CHARACTERS
_
matches any single character
WHERE name LIKE ‘_her%’
Returns (Cheryl, Sheryl)
SELECT * FROM customer
WHERE first_name LIKE ‘J%’
pulls rows from customer where first name starts with ‘J’
SELECT * FROM customer
WHERE first_name NOT ILIKE ‘%a%’ AND last_name NOT ILIKE ‘%a%’
select all columns from customer tbl where first name doesn’t include any a’s (case-insensitive) and last name doesn’t include any a’s (case-insensitive)
