SQL

What are the 2 core functions SQL consists of?

• Data Definition Language (DDL)

  • defining + creating database objects (e.g. tables,relationships,data types,constraints)

• Data Manipulation Language (DML)

  • manipulating + querying data in the db (populating,reading,updating data)

What are literals?

constant values

What must non-numeric literals be enclosed in?

single quotes

Are numeric literals enclosed in quotes?

no

What are the data types that can be used for a character?

• CHAR (length) - fixed length

• VARCHAR (length) - variable length (max)

• TEXT - variable length

What are the data types that can be used for a numeric value?

• INTEGER, SMALLINT - integer

• OID - obejct identifier

• NUMERIC(precision,decimal) - number

• FLOAT, DOUBLE - floating point number

What are the data types that can be used for a temporal value?

• DATE - date

• TIME - time

• TIMESTAMP - date & time

• INTERVAL - interval of time

What is the data type that can be used for a logical value?

BOOLEAN - boolean (e.g. true/fale, yes/no)

Why should you never use a FLOAT or DOUBLE data type for money?

• approximate (inexact) types

• may result in discrepancies - rounding errors

What data type should you use for money?

Numeric OR DBMS’s monetary type

How do you delete database objects in SQL?

DROP (delete child objects first & parent objects last)

How do you create a table in SQL?

CREATE TABLE (create parent tables before child tables)

What do constraints in SQL prevent?

• accidental damage

• changes that may cause data inconsistency

Where are constraints specified in SQL?

specified using clauses in CREATE TABLE statement

OR

specified using database procedures/rules

What are the type of SQL constraints?

• Mandatory Data - NOT NULL

• Entity Integrity - PRIMARY KEY

• Referential Integrity - FOREIGN KEY

• Column value constraints - CHECK,UNIQUE,DEFAULT

• Enterprise constraints

How would you write an SQL constraint?

How would you write a check SQL constraint?

How do you create a table that has a composite key?

• create the key after column definition

What is the referential integrity rule?

value of FK must match existing PK it refers to/be null

When is deletion of a row in a “parent” table not allowed?

if there exists any referenced rows in the “child” table

What happens if the PK in the parent table is deleted/changed?

effects are dependent on actions specified in REFERENCES clause of child table

What are the referential integrity options if a record is deleted in the parent table?

• CASCADE

• SET NULL

• SET DEFAULT

What does “CASCADE” do?

auto delets all referenced records in the child table

What does “SET NULL” do?

sets FK value of all referenced child records to NULL (default option)

What does “SET DEFAULT” do?

auto sets FK of all referenced child records to default value

How do you add a FK in SQL?

What is altering tables used for?

to make changes to columns in existing table

How do you add a new column to a table in SQL?

How do you add a delete an existing column from a table in SQL?

How do you modify an existing column from a table in SQL?

What are the 4 statements for data manipulation in SQL known as CRUD?

• (CREATE) INSERT - add data

• (READ) SELECT - retrieve data

• (UPDATE) UPDATE - modify data

• (DELETE) DELETE - delete data

What are the additional clauses when using the SELECT statement?

How do you select all columns from a table?

How do you select specific column(s) from a table?

How do you select distinct (no duplicates) values of a column?

Give an example of how a SELECT statement can include column expressions to perform calculations

What is the “WHERE” clause used to retrieve?

rows that match a specified filter condition

What are the 5 basic conditions of the “WHERE” clause?

• Comparison - compare values of expression

• Range - does value of expression fall into range?

• Set membership - does value = 1 set of specified values?

• Pattern match - does string value match specified pattern?

• Null - does column have a null value?

What are operators that can be used in the WHERE clause

• = equals

• <> not equal

• > greater than

• < less than

• >= greater than or equal to

• BETWEEN between inclusive range

• IS NULL column expression value is null

• LIKE

• IN

What are the 3 “WHERE” clause compund conditions?

• AND = both 1st & 2nd condition true

• OR = 1st or 2nd condition true

• NOT = negates a condition

How would you test if something is null in sql?

How would you test if something is not null in sql?

What is the “order by” clause used for?

to sort query results

How would you order query results in ascending order in SQL?

How would you order query results in descending order in SQL?

What is the “limit” clause used for?

to limit the number of rows returned by a query to a max value

How would you use a “LIMIT” clause in SQL?

What is meant by aggregating data?

combining data & derive summary info (e.g. sums,counts,averages, etc.)

What are the 5 aggregate functions defined in SQL?

• COUNT - return num of values (in specified column)

• SUM - returns sum of values (in specified column)

• AVG - returns average of values (in specified column)

• MIN - returns smallest value (in specified column)

• MAX - returns largest value (in specified column)

What is the syntax for aggregate functions?

Which of the 5 aggregate fucntions defined in SQL applies to both numeric and non-numeric data?

COUNT, MIN, MAX

Which of the 5 aggregate fucntions defined in SQL applies to numeric data?

SUM, AVG

What does “COUNT(*)” do?

counts all the rows of a table regardless of nulls/duplicates

What is the “GROUP BY” clause used for?

• to get sub-totals/summary data (aggregates) by groups

• specifies attribute(s) to group records by

What is the rule on what must be included when using the GROUP BY clause?

• each item in SELECT list must be single-vaued per group

• results of group by can only contain:

  • grouping attributes

  • constants

  • aggregate function results

• (every column listed in the SELECT statment has to be associated w/ an aggregate function OR it has to appear in the GROUP BY clause)

What does the “HAVING” clause do?

used w/ GROUP BY to reduce groups that appear in the query result (filters groups)

What are the 2 ways to remove ambiguity in a multi-table query result? (e.g. if columns have same name but diff table)

• prefix attribute w/ required table name

  • e.g. Staff.Staff_ID

• use aliases to simplify/shorten length of query

  • e.g. s.Staff_ID (‘s’ alias for staff)

What are the steps when querying multiple tables?

• what tables needed

  • idenitfy + give aliases to each table

• what columns needed for each column

• what are matching columns in tables

  • specify your JOIN condition

• what type of JOIN is needed

• are any other clauses required

What does an inner join return?

rows that have a match in both tables

What does a left outer join return?

• all rows from the left (1st) table & matched from the right table

(NULLs returned for columns in right side table when there is no match)

What does a right outer join return?

• all rows from right table & macthed from left table

(NULLs returned for columns in left side table when there is no match)

What does a full join return?

all rows from both tables whether there’s a match or not

What does a self join return?

rows w/ a match in the same/different columns of the same table

Illustrated types of joins