SQL Basic PT 2

Question 1

SELECT DISTINCT c1, c2 FROM tbl

Answer 1

Select distinct rows by columns c1 and c2 from table tbl

Question 2

SELECT c1, aggregate(expr)
FROM tbl
GROUP BY c1

Answer 2

Select c1 and use aggregate function on expression expr; group columns by column c1

Question 3

SELECT c1, aggregate(expr)
FROM tbl
GROUP BY c1
HAVING c2 > v

Answer 3

Select c1 and use aggregate function on expression expr; group columns by column c1. Filter group of records with c2 greater than value v.

Question 4

INSERT INTO tbl(c1,c2,…)

VALUES(v1,v2,…)

Answer 4

Insert data into table tbl

Question 5

INSERT INTO tbl(c1,c2,…)
SELECT c1,c2… FROM tbl2
WHERE conditions

Answer 5

Insert data from tbl2 into tbl

Question 6

UPDATE t
SET c1 = v1, c2 = v2…
WHERE conditions

Answer 6

Update data in table tbl

Question 7

DELETE FROM tbl

WHERE conditions

Answer 7

Delete records from table tbl based on WHERE conditions

Question 8

TRUNCATE TABLE tbl

Answer 8

Drop table tbl and re-create it, all data is lost

Question 9

CREATE TABLE tbl(
c1 datatype(length)
c2 datatype(length)
...
PRIMARY KEY(c1)
0

Answer 9

creates table tbl with primary key as c1

Question 10

DROP TABLE tbl

Answer 10

Remove table tbl from database

Question 11

ALTER TABLE tbl

ADD COLUMN c1 dataype(length)

Answer 11

Add column c1 to table tbl

Question 12

ALTER TABLE tbl

DROP COLUMN c1

Answer 12

Drop column c1 from table tbl

Question 13

SELECT * FROM tbl1

INNER JOIN tbl 2 ON join-conditions

Answer 13

Inner join table tbl1 with tbl2 based on join-conditions

Question 14

SELECT * FROM tbl1

RIGHT JOIN tbl 2 ON join-conditions

Answer 14

Right join table tbl1 with tbl2 based on join-conditions

Question 15

The COUNT Function?

Answer 15

Returns the number of input rows that match a specific condition of a query

Question 16

SELECT COUNT(DISTINCT title) FROM film;

Answer 16

provides the distinct number of titles in film table

Question 17

WHERE statement

Answer 17

allows us to select conditions on columns, appears immediately after the FROM clause of the SELECT statement

Question 18

SELECT COUNT(*) FROM payment
WHERE amount NOT IN (0.99, 1.98, 1.99)

Answer 18

counts payments that are NOT .99, 1.98 and 1.99

Question 19

SELECT COUNT(*) FROM payment
WHERE amount IN (0.99, 1.98, 1.99)

Answer 19

counts payments that are .99, 1.98 and 1.99

Question 20

SELECT COUNT(*) FROM payment
WHERE payment_date BETWEEN '2007-02-01'  AND '2007-02-15'

Answer 20

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

Question 21

SELECT COUNT(*) FROM payment
WHERE amount NOT BETWEEN 8 AND 9

Answer 21

count payments NOT between $8 and $9 EXCLUSIVE

Question 22

SELECT COUNT(*) FROM payment
WHERE amount BETWEEN 8 AND 9

Answer 22

count payments between $8 and $9 INCLUSIVE

Question 23

%

Answer 23

MATCHES ANY SEQUENCE OF CHARACTERS

Question 24

_

Answer 24

matches any single character

Question 25

WHERE name LIKE ‘_her%’

Answer 25

Returns (Cheryl, Sheryl)

Question 26

SELECT * FROM customer

WHERE first_name LIKE ‘J%’

Answer 26

pulls rows from customer where first name starts with ‘J’

Question 27

SELECT * FROM customer

WHERE first_name NOT ILIKE ‘%a%’ AND last_name NOT ILIKE ‘%a%’

Answer 27

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)