Exam 3

Question 1

SQL original name

Answer 1

SEQUEL: Structured English Query Language: Part of System R in 1973

Question 2

SQL stands for

Answer 2

Structured Query Language

Question 3

SQL is based on

Answer 3

Based on relational tuple calculus and some algebra

Question 4

ANSI & ISO Standards for SQL

Answer 4

SQL/86, SQL 89, 92, SQL2, SQL3, Revisions in 2003, 2006, 2008, 2011…

Question 5

SQL is supported by

Answer 5

IBM DB2, ORACLE, SYSBASE, SqlServer, MySQL, many more (sort of all support it)

Question 6

INSERT statement for SQL

Answer 6

Insert into UserInterests(Email, Interest, SinceAge) Values(‘user12@gt.edu’, ‘Reading’, 5);

only will insert a single row at a time

can be rejected if you try to insert a row if the primary key already exists in the table

Question 7

Delete statement in SQL

Answer 7

delete from UserInterests where Interest=”Swimming”;

deletes multiple rows on matching rows

Question 8

Update statement in SQL

Answer 8

update UserInterests set Interest=’Rock Music’ where Email=”user3@gt.edu’ and Interest=’Music’;

can affect multiple rows if the condition succeeds

Question 9

General SQL Query Syntax

Answer 9

Select column1, column2, … columnN from table1, table2, … tableM where condition;

column is the name of a column in some table like BirthYear

table is the name of a table like RegularUser

condition may compare values of columns to constraints or to each other like BirthYear > 1985 or CurrentCity = HomeTown

conditions can be combined with ANT, OR, Not, and ()

Question 10

Selection and the * wildcard

Find all RegularUser’s

Answer 10

SELECT Email, BirthYear, Sex, CurrentCity, HomeTown from RegularUser;

or

Select * from RegularUser;

Question 11

Selection with a WHERE clause

Find all RegularUser’s with HomeTown Atlanta

Answer 11

Select * from RegularUser where HomeTown = ‘Atlanta’;

returns all rows that matches “Atlanta” for hometown

Question 12

Selection with composite WHERE clause

Find all RegularUser’s who have the same CurrentCity and HomeTown or who live in Atlanta

Answer 12

Select * from RegularUser WHERE CurrentCity = HomeTown OR HomeTown=’Atlanta’;

Returns rows that matches any of the two conditions

Question 13

Projection in SQL

Find Email, BirthYear, and Sex for RegularUser’s in Atlanta

Answer 13

Select Email, BirthYear, Sex from RegularUser where HomeTown=’Atlanta’;

includes only the rows that you select. But does include duplicates by default!

Question 14

Distinct - tables may have duplicate rows

Answer 14

Select Distinct(Sex) from RegularUser where HomeTown=’Atlanta’;

Eliminates duplicates like in relational algebra and calculus for all the columns that you mark as distinct

Question 15

Natural Inner Join – and dot notation

Find Email, BirthYear, and Salary for RegularUser’s who have a salary by combining RegularUser data with YearSalary data

Answer 15

SELECT Email, RegularUser.BirthYear, Salary from RegularUser, YearSalary WHERE RegularUser.BirthYear = YearSalary.BirthYear

remember this only returns rows where the join condition is true

the dot notation is only necessary when there’s ambiguity

Remember we can also do NATURAL JOIN!

Question 16

Natural Join in SQL!

Answer 16

SELECT Email, RegularUser.BirthYear, Salary from RegularUser NATURAL JOIN YearSalary;

This works just like using =

Question 17

Natural Inner Join - Aliases

Answer 17

Select Email, R.BirthYear, Salary From RegularUser as R, YearSalary as Y Where R.BirthYear = Y.BirthYear;

Aliases save typing

Aliases are used to disambiguate table references

Aliases must be used when joining a table to itself!

Question 18

Left Outer Join

Find Email, BirthYear, and Salary for RegularUser’s who have a Salary by combining RegularUser data with YearSalary data. Return Email and BirthYear even when the RegularUser has no Salary.

Answer 18

Select Email, RegularUser.BirthYear, Salary From RegularUser LEFT OUTER JOIN YearSalary;

All rows from the first table have NULL for the extra columns

Question 19

String Matching

Find data about RegularUser’s who live in a CurrentCity that starts with “San”

Answer 19

SELECT Email, Sex, Current City FROM RegularUser WHERE CurrentCity LIKE ‘San%’;

% matches any string, including the empty string

Question 20

Wildcard %

Answer 20

matches any string, including the empty string

Question 21

Wildcard _

Answer 21

matches any single character. ‘A____’ A with 4 characters

Question 22

Sorting! (oh no!)

Find data about RegularUser’s who are Males. Sort the data by ascending CurrentCity

Answer 22

Select Email, Sex, CurrentCity From RegularUser Where Sex=’M’ Order By CurrentCity Asc;

sorts it in alphabetical order

Can sort on multiple columns (one by asc, another with desc)

Question 23

Set Operations - Union

Find all CurrentCity’s and HomeTowns without duplicates

Answer 23

Select CurrentCity from RegularUser UNION Select HomeTown From RegularUser;

NO DUPLICATES!

Question 24

What Union command returns duplicates?

Answer 24

Select CurrentCity from RegularUser Union All Select HomeTown from RegularUser;

Question 25

Set Operations - Intersect

Find all cities that are CurrentCity’s for someone and HomeTown’s for someone. Do not include duplicates.

Answer 25

SELECT CurrentCity FROM RegularUser INTERSECT Select HomeTown FROM RegularUser;

Question 26

What Intersection command returns duplicates?

Answer 26

SELECT CurrentCity FROM RegularUser INTERSECT ALL Select HomeTown FROM RegularUser;

If a value intersects and appears twice in the same column, it will be listed twice with ALL

Question 27

Set Operations - Except

Find all cities that are CurrentCity’s but exclude those that are HomeTown’s. Do not include duplicates.

Answer 27

SELECT CurrentCity FROM RegularUser EXCEPT SELECT HomeTown FROM RegularUser;

Question 28

Except with duplicates?

Answer 28

SELECT CurrentCity FROM RegularUser EXCEPT ALL SELECT HomeTown FROM RegularUser;

If a value intersects is in the first column and appears twice in the that column, it will be listed twice with ALL

Question 29

List some built in functions

Answer 29

count, sum, avg, min, max

Question 30

Count the number of RegularUser’s

Answer 30

Select count(*) FROM RegularUser;

Question 31

Find Email and BirthYear for the Youngest Female RegularUser

Answer 31

Select Email, max(BirthYear) from RegularUser where Sex=’F’;

Question 32

Group By

Group UserInterests on Email.

Answer 32

Select Email, UserInterests Group By Email

Question 33

Group By (2)

For each group, return the email, the number of interests, and the average sinceage for each group

group userinterests on email

sort the result by ascending number of interests

Answer 33

SELECT Email, count(*) AS NumInt, avg(SinceAge), AvgAge FROM UserInterests GROUP BY Email ORDER BY NumInt ASC;

Question 34

Having - condition on the group

Answer 34

having is like a “where” but for Group By

Question 35

Nested Queries - In/Not In

Answer 35

IN means in the result rows of the subquery

Question 36

Find email and interest for RegularUser’s in Atlanta using nested queries

Answer 36

Select Email, Interest from UserInterests Where Email IN (Select Email FROM RegularUser WHERE HomeTown = ‘Atlanta’);

As an alternative for this, we can just use a natural join

Question 37

Nested Queries: =, , etc

Find CurentCity’s with at least one RegularUser with a Salary that’s higher than all Salaries of RegularUsers with Hometown Austin

Answer 37

SELECT CurrentCity FROM User R, YearSalary Y WHERE R.BirthYear = Y.BirthYear AND Salary > ALL (SELECT Salary FROM RegularUser R, YearSalary Y Where R.BirthYear = Y.BirthYear AND HomeTown = ‘Austin’);

Question 38

Nested Queries - Correlated

Find Email and BirthYear of RegularUsers who have no Interests

Answer 38

Select R.Email, BirthYear from RegularUser R Where not exist (Select * From UserInterests U Where U.Email = R.Email)

R does not exist in inner query. It is a “reference out of scope”

think of it as a sub-query evaluated once for each row of the outer query