Database Concepts and SQL

These flashcards cover key concepts about databases, SQL commands, and data manipulation techniques.

What is a database?

An organized collection of data arranged in a structured way so it can be stored, searched, and updated efficiently.

What is a DBMS?

A Database Management System is software that controls how data is stored, accessed, secured, and managed.

What is a query?

A request to retrieve, insert, update, or delete data from a database.

What does SQL stand for?

Structured Query Language.

What are the 3 parts of the relational model?

Data structures, operations, and rules.

What is a tuple?

An ordered collection of elements written in parentheses ().

What is a set?

An unordered collection of elements written in braces {}.

What is a primary key?

A column (or columns) that uniquely identifies each row and cannot be NULL.

What is a foreign key?

A column that references the primary key of another table.

What is referential integrity?

A rule requiring foreign key values to either match an existing primary key value or be NULL.

What does DDL include?

CREATE, ALTER, DROP.

What does DML include?

INSERT, UPDATE, DELETE.

What does DQL include?

SELECT.

What does DCL include?

GRANT, REVOKE.

What does TCL include?

COMMIT, ROLLBACK, SAVEPOINT.

What does SELECT do?

Retrieves data from a table.

What does WHERE do?

Filters individual rows before grouping.

What does ORDER BY do?

Sorts results.

What does DISTINCT do?

Removes duplicate rows.

What is the correct way to check for NULL?

IS NULL or IS NOT NULL.

What is the wrong way to check for NULL?

' = NULL.'

What does COUNT(*) do?

Counts all rows including rows with NULL values.

What does COUNT(column) do?

Counts only non-NULL values in that column.

What does SUM() do?

Adds values.

What does AVG() do?

Finds the average.

What does MIN() do?

Finds the smallest value.

What does MAX() do?

Finds the largest value.

Do SUM, AVG, MIN, and MAX ignore NULL values?

Yes.

Does COUNT(*) ignore NULL values?

No.

What does GROUP BY do?

Groups rows together for aggregate calculations.

What does HAVING do?

Filters grouped results after GROUP BY.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters groups after grouping.

What is the GROUP BY rule?

Every non-aggregate column in SELECT must appear in GROUP BY.

What is the SQL execution order?

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

What is a JOIN?

A JOIN combines data from two tables using related columns.

What does INNER JOIN do?

Returns only matching rows from both tables.

What does LEFT JOIN do?

Keeps all rows from the left table and matching rows from the right table.

What does RIGHT JOIN do?

Keeps all rows from the right table and matching rows from the left table.

What does CROSS JOIN do?

Returns every possible combination of rows.

What does SELF JOIN do?

A table joins to itself.

What does the ON clause do?

Specifies how tables are matched.

What is a view?

A virtual table created from a saved SQL query.

Does a view store copied physical data?

No.

Why are views useful?

Simplicity, security, reusability, and cleaner reporting.

What is the syntax to create a view?

CREATE VIEW ViewName AS SELECT …

What is a subquery?

A query inside another query.

What is a common use of a subquery?

Comparing values to AVG(), MAX(), or MIN.

Example of a subquery for students taller than average?

SELECT Fullname FROM Students WHERE HeightM > (SELECT AVG(HeightM) FROM Students);

What does ROUND(16.25,1) return?

16.3

What does ABS(-42) return?

42

What does UPPER(‘mysql’) return?

MYSQL

What does LOWER(‘MYSQL’) return?

mysql

What does CONCAT(‘North’,‘Carolina’) return?

NorthCarolina

What does CURDATE() return?

The current date.

What does YEAR(Birthday) do?

Extracts the year from a date.

What does MONTH(Birthday) do?

Extracts the month from a date.

Which is correct: WHERE COUNT() > 2 or HAVING COUNT() > 2?

HAVING COUNT(*) > 2.

Which is correct: WHERE Age = NULL or WHERE Age IS NULL?

WHERE Age IS NULL.

Which is safer: SUM(Salary + Bonus) or SUM(Salary) + SUM(Bonus)?

SUM(Salary) + SUM(Bonus).

Does INNER JOIN show all students?

No, only matching students.

Does LEFT JOIN show all students?

Yes, all students from the left table.

Show Fullname and Age for students older than 20.

SELECT Fullname, Age FROM Students WHERE Age > 20;

Count students in each GradeLevel.

SELECT GradeLevel, COUNT(*) AS NumStudents FROM Students GROUP BY GradeLevel;

Show only grade levels with more than 2 students.

SELECT GradeLevel, COUNT() AS NumStudents FROM Students GROUP BY GradeLevel HAVING COUNT() > 2;

Show student names and ActivityName using INNER JOIN.

SELECT S.Fullname, L.ActivityName FROM Students S INNER JOIN LeisureActivity L ON S.StudentID = L.StudentID;

Show all students even if they have no activities.

SELECT S.Fullname, L.ActivityName FROM Students S LEFT JOIN LeisureActivity L ON S.StudentID = L.StudentID;

Find students taller than average height.

SELECT Fullname, HeightM FROM Students WHERE HeightM > (SELECT AVG(HeightM) FROM Students);

Create a view called StudentSummary.

CREATE VIEW StudentSummary AS SELECT Fullname, Age, GradeLevel FROM Students;

Find the average CreditScore by GradeLevel.

SELECT GradeLevel, AVG(CreditScore) FROM Students GROUP BY GradeLevel;

Find grade levels where average CreditScore is above 700.

SELECT GradeLevel, AVG(CreditScore) FROM Students GROUP BY GradeLevel HAVING AVG(CreditScore) > 700;

Find all female students with CreditScore above 700.

SELECT Fullname, GradeLevel FROM Students WHERE Sex = ‘Female’ AND CreditScore > 700.