Database Fundamentals: SQL, Data Types, and Table Structures

What is data?

Raw facts that describe real-world objects, events, or activities

What is a database?

An organized collection of structured data

What is a DBMS?

Software that manages storing, retrieving, securing, and maintaining data

What are 3 responsibilities of a DBMS?

Security, consistency, availability

What is NOT a responsibility of a DBMS?

Creating user interfaces

What is a table?

A collection of rows and columns

What is a row (tuple)?

An ordered collection of values representing one record

What is a column?

A named attribute with a data type

What is a set?

An unordered collection of elements

What does SQL stand for?

Structured Query Language

What does SELECT do?

Chooses columns

What does FROM do?

Specifies the table

What does WHERE do?

Filters rows

What is DDL?

Data Definition Language (CREATE, ALTER, DROP)

What is DML?

Data Manipulation Language (INSERT, UPDATE, DELETE)

What is DQL?

Data Query Language (SELECT)

Which SQL type retrieves data?

DQL

What is INT used for?

Whole numbers

What is TINYINT?

Small integer values

What does UNSIGNED mean?

No negative values allowed

What is VARCHAR?

Variable-length string

What is CHAR?

Fixed-length string

What is ENUM?

A data type with predefined allowed values

What is a primary key?

A column that uniquely identifies each row

Can a primary key be NULL?

No

Can a primary key repeat?

No

Why is a primary key important?

Ensures uniqueness and allows rows to be identified

How do you create a database?

CREATE DATABASE DatabaseName;

How do you view all databases?

SHOW DATABASES;

How do you select a database?

USE DatabaseName;

What is the correct SQL structure?

SELECT columns FROM table WHERE conditions;

Where do conditions go?

WHERE clause

Can conditions go in SELECT?

No

Correct INSERT syntax

INSERT INTO Students (StudentID, Fullname, GradeLevel, Age, CreditScore, Sex) VALUES (21, 'John Davis', 'FR', 19, 710, 'Male');

What does SELECT (relational) do?

Retrieves rows

What does PROJECT do?

Retrieves columns

What does JOIN do?

Combines tables (columns)

What does UNION do?

Combines rows

What is NULL?

A value representing unknown or missing data

What does `= NULL` return?

Nothing (invalid comparison)

How do you correctly check NULL?

IS NULL, IS NOT NULL

What happens if a WHERE condition evaluates to NULL?

Row is NOT returned

What does OR do?

Returns row if ANY condition is TRUE

What does AND do?

Returns row only if ALL conditions are TRUE

What's wrong with this SQL? SELECT Name AND Age FROM Students;

Use comma, not AND

What's wrong with this SQL? WHERE Age = NULL

Must use IS NULL

What's wrong with this SQL? WHERE Age >= 20 AND Sex

Missing comparison (Sex = 'Male')

Write a query: students age ≥ 20

SELECT * FROM Students WHERE Age >= 20;

Write a query: female students with CreditScore > 700

SELECT Fullname, GradeLevel FROM Students WHERE Sex = 'Female' AND CreditScore > 700;