SQL Part 1

Question 1

What keyword is used for alias’s?

Answer 1

AS

Question 2

Can you use string literals? If so, what would you use?

Answer 2

Yes, single quotes

Question 3

What happens with the following line: SELECT 0 as id FROM Student

Answer 3

All student ID’s will show up as 0’s

Question 4

What is the syntax for pattern matching?

Answer 4

LIKE ‘%_%’ where _ is any one character and % can mean any character. % can be placed between, before, and after characters.

Question 5

List all students, sort them by GPA (descending) then name (ascending). The Student table has columns name and GPA.

Answer 5

SELECT GPA, name FROM Student ORDER BY GPA DESC, name;

Question 6

Show me the names and phone numbers of passengers whose phone numbers have exactly two 7’s. Table is PASSENGER with columns passengername, phone

Answer 6

SELECT passengername, phone
FROM passenger
WHERE phone LIKE ‘%7%7%’ AND NOT phone LIKE ‘%7%7%7%’

Question 7

Which semantics are set to default by SQL, Set or Bag?

Answer 7

Bag

Question 8

What keyword is used to remove duplicates?

Answer 8

DISTINCT

Question 9

What is used to access qualifying names and specific data structures? What is the format?

Answer 9

The ‘.’ symbol. Format: schema_name.table_name.column_name

Question 10

How do you explicitly state there is a single quote in your equivalency statement?

Answer 10

Use another single quote, so ‘’

Question 11

From Beers(name, manf), find all pairs of beers made by the same manufacturer.

Answer 11

SELECT b1.name, b2.name
FROM Beers b1, Beers b2
WHERE b1.manf = b2.manf and b1.name <> b2.name;

Question 12

How do you say NOT EQUAL in SQL?

Answer 12

<> or !=

Question 13

What is the syntax of a join statement?

Answer 13

Table1 JOIN Table2 ON Table1.Attribute = Table2.Attribute

Question 14

Using Likes(drinker, beer), Sells(bar, beer, price), and Frequents(drinker, bar), find the drinkers and beers such that:
1. The drinker likes the beer
2. The drinker frequents at least one bar that sells the beer

Answer 14

(SELECT * FROM Likes) INTERSECT (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar = Sells.bar);

Question 15

What subquery keywords are used for combination/omission of their sets with each other?

Answer 15

UNION, INTERSECT, DIFFERENCE

Question 16

What are all aggregate SQL functions?

Answer 16

MIN(), MAX(), COUNT(), AVG(), SUM()

Question 17

How do NULL’s work in operations?

Answer 17

The result will become NULL when operating on it.

Question 18

Whats the difference between INNER JOIN and OUTER JOIN?

Answer 18

INNER JOIN (JOIN) only includes the rows that match between the two tables. OUTER JOIN includes all rows in both tables, even if there was no match between the two.

Question 19

Using Sells(bar, beer, price), find the bars that serve Miller for the same price Joe’s Bar charges for Bud.

Answer 19

SELECT bar
FROM Sells
WHERE beer = ‘Miller’ AND
price = (SELECT price
FROM Sells
WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’)

Question 19

What symbol is used to query all rows?

Answer 19

*

Question 20

Find beers liked by at least one person who frequents Joe’s Bar. Use the following tables: Likes(drinker, beer) Frequents(drinker, bar)

Answer 20

SELECT beer
FROM Likes, (SELECT drinker FROM Frequents WHERE bar = ‘Joe’’s Bar’)JD
WHERE Likes.drinker = JD.drinker;

Question 21

Find the department names that have no employees. departments(id, name) employees(id, name, department_id)

Answer 21

SELECT name FROM departments WHERE id NOT IN (SELECT department_id FROM employees)