SQL

Question 1

SQL FROM

Answer 1

• Specifies which tables you are using

Question 2

SQL WHERE

Answer 2

• Specifies which rows you are including (SELECTION)

Question 3

SQL SELECT

Answer 3

• Specifies which columns you are including (PROJECTION)

Question 4

SQL LIKE

Answer 4

• % stands for 0 or more arbitrary characters
• _ stands for a single arbitrary character

Question 5

SQL Comparisons Involving NULL

Answer 5

• Any comparison involving NULL using standard operators is always False
• Use “IS” to check for NULL

Question 6

SQL COUNT(*) vs COUNT(attribute)

Answer 6

• COUNT(*) counts the number of tuples in a result
• COUNT(attribute) counts the number of non-NULL values of an attribute in a result

Question 7

SQL DISTINCT

Answer 7

• Removes duplicates

Question 8

SQL GROUP BY

Answer 8

• Group together tuples with a common value
• Apply aggregate functions to the tuples in each subgroup

Question 9

SQL HAVING

Answer 9

• Allows us to apply a selection condition in the subgroups produced through the GROUP BY clause

Question 10

SQL Difference between WHERE and HAVING

Answer 10

• A WHERE clause is applied before grouping
• A HAVING clause is applied after grouping

Question 11

SQL ORDER BY

Answer 11

• Sorts the tuples in the result by one or more attributes
• Ascending by default, use DESC for descending

Question 12

SQL Aggregate Functions

Answer 12

• Perform a computation on a collection of values on an attribute
• Can be included in the SELECT clause
• Examples: MIN, MAX, AVG, SUM, COUNT
• Generally cannot mix aggregate functions with column names in the SELECT clause

Question 13

SQL Subqueries

Answer 13

• Allows us to use the result of one query in the evaluation of another query

Question 14

SQL Set Membership

Answer 14

• Subqueries can be used to test for set membership in conjunction with the IN and NOT IN operators

Question 15

SQL Set Operations

Answer 15

• UNION
• INTERSECTION
• EXCEPT (set difference)

Question 16

SQL Outer Joins

Answer 16

• FROM T1 LEFT OUTER JOIN T2 ON join condition
• FROM T1 RIGHT OUTER JOIN T2 ON join condition
• FROM T1 FULL OUTER JOIN T2 ON join condition

Question 17

SQL CREATE TABLE

Answer 17

• Creates a relation with the specified schema
• CREATE TABLE relation_name(attribute_name attribute_type, …);

Question 18

SQL DROP TABLE

Answer 18

• Removes an entire relation from a database
• A relation cannot be dropped if referred to by a foreign key
• DROP TABLE relation_namel

Question 19

SQL INSERT

Answer 19

• Adds a tuple to a relation
• INSERT INTO relation VALUES (val1, val2, …);
• INSERT INTO relation(attr1, attr2, …) VALUES (val1, val2,…);

Question 20

SQL DELETE

Answer 20

• Removes one or more tuples from a relation
• DELETE FROM table WHERE selection condition;

Question 21

SQL UPDATE

Answer 21

• Modifies one or more tuples in a relation
• UPDATE table SET list of assignments WHERE selection condition;