Database Management: D427

What is a foreign key?

A column that references the primary key of another table to create a relationship.

What is the basic SELECT syntax?

SELECT column1, column2

FROM TableName;

How do you select all columns?

Using the asterisk (*) key with the SELECT clause.

SELECT *

FROM TableName;

How do you filter the SELECT clause to return only specific criteria?

using the WHERE clause, which comes after the FROM Table clause.

SELECT column

FROM TableName

WHERE condition;

exp:

SELECT CourseName

FROM Course

WHERE Capacity > 100;

What are the SQL Comparison operators?

Operator Meaning

= Equals

<> or != Not Equal

> Greater Than

< Less Than

>= Greater or Equal

<= Less or Equal

What are the Logical Operators in SQL?

AND both conditions are true

OR One condition is true

NOT Reverses the condition

(T -> F), (F -> T)

What is a relational database?

A database that stores data in tables (relations) with rows and columns that can be linked using keys.

What is a table row called?

A tuple or record.

What is a table column called?

An attribute.

What is a primary key?

A column (or columns) that uniquely identifies each row in a table

Rules:

1. Cannot contain NULL

2. Must be unique

How do you check for NULL Value?

WHERE column IS NULL.

How do you check for Values which are NOT NULL?

WHERE column IS NOT NULL.

What is the ORDER BY syntax?

SELECT column

FROM TableName

ORDER BY column;

How would you sort by ascending vs sorting by descending?

Ascending (default)

ORDER BY column ASC

Descending

ORDER BY column DESC

What are the SQL aggregate functions?

COUNT() Number of rows

SUM() Total Value

AVG() Average

MIN() Smallest Value

MAX() Largest Value

What is the purpose of using the GROUP BY clause?

Group rows so aggregate functions can be calculated per group.

Example:

SELECT InstructorId, COUNT(*)

FROM Course

GROUP BY InstructorId;

What is the use of the INNER JOIN clause?

Returns matching rows from both tables

SELECT *

FROM Course

JOIN Instructor

ON Course.InstructorId = Instructor.InstructorId;

What is the use of the LEFT JOIN clause?

Returns:

1. all rows from the left table

2. matching rows from the right table

What is the use of the FULL JOIN clause?

Returns:

1. All rows from both tables

2. Matches when possible

3. Null when no match is available

What is a subquery?

A query inside another query.

Example:

SELECT CourseId

FROM Course

WHERE InstructorId =

(SELECT InstructorId

From Instructor

WHERE InstructorName = name);

What is the EXISTS clause purpose?

Returns True if the subquery returns at least one row.

Example:

SELECT CourseId

FROM Course C

WHERE EXISTS

(SELECT *

FROM Instructor

WHERE InstructorId = C.InstructorId);

What is a Correlated Subquery?

A subquery that references the outer query.

Example:

SELECT *

FROM Instructor I

WHERE 2 <=

(SELECT COUNT(*)

FROM Course

WHERE InstructorId = I.InstructorId);

How do you rename a column in a query result?

Use the AS (alias)

Example:

SELECT CourseName AS Name

FROM Course;

What is a Column alias?

A temporary name for a column in the query result.

Can SQL aliases contain spaces?

Yes, if placed in quotations.

Example:

SELECT CourseName AS "Course Title"

FROM Course;

What does DISTINCT do?

Removes duplicate values from the result.

EXAMPLE

SELECT DISTINCT InstructorId

FROM Course;

When is DISTINCT used?

When you want unique values only, remove any duplicates.

What does LIMIT do?

Restricts the number of rows returned.

Example:

SELECT *

FROM Course

LIMIT 5;

What does the LIKE operator do?

Searches for patterns in text values.

Example:

SELECT *

FROM Course

WHERE CourseName LIKE 'Bio%';

What does % mean in LIKE?

Represents any number of characters

'Bio%' --> matches

Biology, Biochemistry, Bioinformatics, etc...

What does _ mean in LIKE?

Represents one character

Example:

'B_o' --> Matches

Bio, Boo, Bxo, etc.

What does the IN operator do?

Checks if a value matches any value in a list.

Example:

SELECT *

FROM Course

WHERE InstructorId IN (1,2,3);

Why use IN instead of OR?

It is shorter and easier to read.

Example:

OR -->

WHERE InstructorId = 1 OR InstructorId = 2

IN -->

WHERE InstructorId IN (1,2)

What does BETWEEN do?

Filters values within a range (inclusive).

SELECT *

FROM Course

WHERE Capacity BETWEEN 50 AND 150;

WHAT does BETWEEN include?

Both boundary values.

BETWEEN 50 AND 150

includes:

50 and 150 plus any value in between them.

What is the difference between WHERE and HAVING?

Where --> Filters rows before grouping.

HAVING --> Filters group after aggregation.

SELECT InstructorId, COUNT(*)

FROM Course

GROUP BY InstructorId

HAVING COUNT(*) > 1;

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) --> All rows are included

COUNT(column) --> Non-NULL values only

What is a table alias?

A short temporary name for a table

SELECT C.CourseName

FROM Course C;

Why use table aliases?

Back:

1. Shorter queries

2. Required in self joins

3. Help when joining many tables

Example:

SELECT C.CourseName, I.InstructorName

FROM Couse C

JOIN Instructor I

ON C.InstructorId = I.InstructorId;

What does the ON clause do?

Specifies the matching condition between tables.

Example:

ON Course.InstructorId = Instructor.InstructorId

What happens when tables are listed without a join condition?

SQL produces a Catesian product (cross join).

Example:

SELECT *

FROM A, B;}

result = every row in A combines with every row in B.

Why are foreign keys important?

They enforce referential integrity.

Meaning:

1. Prevents invalid references

2. Maintains relationships between tables

What is referential integrity?

Ensures foreign key values must match a primary key in the referenced table.

What is the difference between EXISTS and IN?

Exist

Checks if row exists.

Stops after first match.

IN

Compares values.

Checks entire list.

What is a data type?

Defines the type of data a column can store

Examples:

INT

VARCHAR

CHAR

DATE

FLOAT

What is the difference between CHAR and VARCHAR?

CHAR(n) A fixed character length of n value

VARCHAR(n) A variable length of n value as the maximum length the char can be.

What data type stores whole numbers?

INT --> integer data type

What data type stores decimal numbers?

FLOAT

DECIMAL

What is the basic CREATE TABLE syntax

CREATE TABLE table_name(

column_name datatype,

column_name datatype,

ect...

);

How to define a PRIMARY KEY in a table?

CREATE TABLE STUDENT(

StudentID INT PRIMARY KEY

);

How to define a FOREIGN KEY?

FOREIGN KEY (InstructorId)

REFERENCES Instructor(InstructorId)

What does ALTER TABLE do?

Modifies an existing table structure.

Examples:

1. Add column

2. Remove column

3. Modify column

How to add a column to an existing table?

ALTER TABLE Student

ADD ColumnName datatype;

How to remove a column in an existing table?

ALTER TABLE Student

DROP COLUMN ColumnName;

What is the basic INSERT syntax?

INSERT INTO table_name

VALUES (value1, value2);

How to INSERT specifying columns?

INSERT INTO Student (StudentID, Name)

VALUES (1, 'John');

What is the UPDATE syntax?

UPDATE table

SET column = value

WHERE condition;

Example:

UPDATE Student

SET Name = 'Bob'

WHERE StudentID = 1;

If no WHERE clause is found, SQL will instead change all Name column values to be equal to 'bob' or whatever the value is.

What is the DELETE syntax?

DELETE FROM TABLE

WHERE condition;

DELETE FROM Student

WHERE StudentID = 5;

What happens if DELETE has no WHERE clause?

All rows are deleted.

Example:

DELETE FROM Student;

What is the SQL query execution order?

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

What is a composite key?

A primary key made of multiple columns.

Example:

PRIMARY KEY (StudentID, CourseID)

What happens if a referenced primary key is deleted?

Depends on foreign key constraints.

possible actions:

1. CASCADE

2. SET NULL

3. RESTRICT

What is a Cartesian Product?

When every row from one table is paired with every row from another table. Occurs when the JOIN condition is missing.

What can a subquery return?

1. Single value

2. Multiple values

3. Entire table

When does ESISTS return TRUE?

When the subquery returns at least one row.

What is a scalar subquery?

A subquery that one value

Example:

SELECT *

FROM Couse

WHERE Capacity >

(SELECT AVG(Capacity) FROM Course);

What is a database index?

A structure that improves query speed. Works like a book index.

Can you compare NULL using equal signs?

No, use IS NULL (is the value NULL) instead or IS NOT NULL (is the value not NULL) instead.

What clause filters grouped data?

The HAVING clause.

What clause filters rows before grouping them?

The WHERE clause?

Still learning (6)

You've started learning these terms. Keep it up!